<?php
include_once __DIR__ . '/../include.php';
include_once __DIR__ . '/lib_example.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$db->write("DROP TABLE IF EXISTS summing_url_views");
$db->write('
CREATE TABLE IF NOT EXISTS summing_url_views (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
url_hash String,
site_id Int32,
views Int32,
v_00 Int32,
v_55 Int32
)
ENGINE = SummingMergeTree(event_date, (site_id, url_hash, event_time, event_date), 8192)
');
echo "Table EXISTS:" . json_encode($db->showTables()) . "\n";
// ------------------------------------------------------------------------------------------------------
echo "----------------------------------- CREATE big csv file -----------------------------------------------------------------\n";
$file_data_names = ['/tmp/clickHouseDB_test.b.1.data', '/tmp/clickHouseDB_test.b.2.data', '/tmp/clickHouseDB_test.b.3.data', '/tmp/clickHouseDB_test.b.4.data', '/tmp/clickHouseDB_test.b.5.data'];
$c = 0;
foreach ($file_data_names as $file_name) {
$c++;
makeSomeDataFileBig($file_name, 40 * $c);
}
echo "----------------------------------------------------------------------------------------------------\n";
echo "insert ALL file async NO gzip:\n";
$db->settings()->max_execution_time(200);
<?php
include_once __DIR__ . '/../include.php';
include_once __DIR__ . '/lib_example.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$_flag_create_table = false;
$size = $db->tableSize('summing_url_views_cityHash64_site_id');
echo "Site table summing_url_views_cityHash64_site_id : " . (isset($size['size']) ? $size['size'] : 'false') . "\n";
if (!isset($size['size'])) {
$_flag_create_table = true;
}
if ($_flag_create_table) {
$db->write("DROP TABLE IF EXISTS summing_url_views_cityHash64_site_id");
$re = $db->write('
CREATE TABLE IF NOT EXISTS summing_url_views_cityHash64_site_id (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
url_hash String,
site_id Int32,
views Int32,
v_00 Int32,
v_55 Int32
)
ENGINE = SummingMergeTree(event_date, cityHash64(site_id,event_time),(site_id, url_hash, event_time, event_date,cityHash64(site_id,event_time)), 8192)
');
echo "Table EXISTS:" . print_r($db->showTables()) . "\n";
// ------------------------------------------------------------------------------------------------------
echo "----------------------------------- CREATE big csv file -----------------------------------------------------------------\n";
$file_data_names = ['/tmp/clickHouseDB_test.big.1.data', '/tmp/clickHouseDB_test.big.2.data', '/tmp/clickHouseDB_test.big.3.data'];
$c = 0;
<?php
include_once __DIR__ . '/../include.php';
include_once __DIR__ . '/lib_example.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$db->enableHttpCompression(true);
$db->write("DROP TABLE IF EXISTS summing_url_views");
$db->write('
CREATE TABLE IF NOT EXISTS summing_url_views (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
url_hash String,
site_id Int32,
views Int32,
v_00 Int32,
v_55 Int32
)
ENGINE = SummingMergeTree(event_date, (site_id, url_hash, event_time, event_date), 8192)
');
echo "Table EXISTS: " . json_encode($db->showTables()) . "\n";
echo $db->showCreateTable('summing_url_views');
exit;
// -------------------------------- CREATE csv file ----------------------------------------------------------------
$file_data_names = ['/tmp/clickHouseDB_test.1.data', '/tmp/clickHouseDB_test.2.data'];
foreach ($file_data_names as $file_name) {
makeSomeDataFile($file_name, 2);
}
// ----------------------------------------------------------------------------------------------------
echo "insert ALL file async:\n";
$time_start = microtime(true);
<?php
include_once __DIR__ . '/../include.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$input_params = ['select_date' => ['2000-10-10', '2000-10-11', '2000-10-12'], 'limit' => 5, 'from_table' => 'table'];
$db->enableQueryConditions();
$select = '
SELECT * FROM {from_table}
WHERE
{if select_date}
event_date IN (:select_date)
{else}
event_date=today()
{/if}
{if limit}
LIMIT {limit}
{/if}
';
$statement = $db->selectAsync($select, $input_params);
echo $statement->sql();
echo "\n";
/*
SELECT * FROM table
WHERE
event_date IN ('2000-10-10','2000-10-11','2000-10-12')
LIMIT 5
FORMAT JSON
*/
$input_params['select_date'] = false;
$statement = $db->selectAsync($select, $input_params);
<?php
include_once __DIR__ . '/../include.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
//$db->verbose();
$db->settings()->readonly(false);
$result = $db->select('SELECT 12 as {key} WHERE {key} = :value', ['key' => 'ping', 'value' => 12]);
if ($result->fetchOne('ping') != 12) {
echo "Error : ? \n";
}
print_r($result->fetchOne());
echo 'elapsed :' . $result->statistics('elapsed') . "\n";
echo 'rows_read :' . $result->statistics('rows_read') . "\n";
echo 'bytes_read:' . $result->statistics('bytes_read') . "\n";
//
$result = $db->select("SELECT 12 as ping");
print_r($result->statistics());
/*
"statistics":
{
"elapsed": 0.000029702,
"rows_read": 1,
"bytes_read": 1
}
*/
<?php
// Подключаем драйвер
include_once __DIR__ . '/../include.php';
// Конфигурация
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$client = new \ClickHouseDB\Client($config);
// Проверяем соединение с базой
$client->ping();
// Создаём таблицу
$client->write('CREATE DATABASE IF NOT EXISTS articles');
$client->write('DROP TABLE IF EXISTS articles.events');
$client->write("\n CREATE TABLE articles.events (\n event_date Date DEFAULT toDate(event_time),\n event_time DateTime,\n event_type Enum8('VIEWS' = 1, 'CLICKS' = 2),\n site_id Int32,\n article_id Int32,\n ip String,\n city String,\n user_uuid String,\n referer String,\n utm String DEFAULT extractURLParameter(referer, 'utm_campaign')\n ) ENGINE = MergeTree(event_date, (site_id, event_type, article_id), 8192)\n");
// Выбираем default базу
$client->database('articles');
// Получим список таблиц
print_r($client->showTables());
// Для упрощения выставляем принудительно таймзону
date_default_timezone_set('Europe/Moscow');
// Простая вставка данных `$db->insert(имя_таблицы, [данные], [колонки]);`
$client->insert('events', [[time(), 'CLICKS', 1, 1234, '192.168.1.11', 'Moscow', 'user_11', ''], [time(), 'CLICKS', 1, 1235, '192.168.1.11', 'Moscow', 'user_11', 'http://yandex.ru?utm_campaign=abc'], [time(), 'CLICKS', 1, 1236, '192.168.1.11', 'Moscow', 'user_11', 'http://smi2.ru?utm_campaign=abc'], [time(), 'CLICKS', 1, 1237, '192.168.1.11', 'Moscow', 'user_11', ''], [time(), 'CLICKS', 1, 1237, '192.168.1.13', 'Moscow', 'user_13', ''], [time(), 'CLICKS', 1, 1237, '192.168.1.14', 'Moscow', 'user_14', ''], [time(), 'VIEWS', 1, 1237, '192.168.1.11', 'Moscow', 'user_11', ''], [time(), 'VIEWS', 1, 1237, '192.168.1.12', 'Moscow', 'user_12', ''], [time(), 'VIEWS', 1, 1237, '192.168.1.1', 'Rwanda', 'user_55', 'http://smi2.ru?utm_campaign=abc'], [time(), 'VIEWS', 1, 1237, '192.168.1.1', 'Banaadir', 'user_54', 'http://smi2.ru?utm_campaign=abc'], [time(), 'VIEWS', 1, 1237, '192.168.1.1', 'Tobruk', 'user_32', 'http://smi2.ru?utm_campaign=CM1'], [time(), 'VIEWS', 1, 1237, '192.168.1.1', 'Gisborne', 'user_12', 'http://smi2.ru?utm_campaign=CM1'], [time(), 'VIEWS', 1, 1237, '192.168.1.1', 'Moscow', 'user_43', 'http://smi2.ru?utm_campaign=CM3']], ['event_time', 'event_type', 'site_id', 'article_id', 'ip', 'city', 'user_uuid', 'referer']);
// Достанем результат вставки данных
print_r($client->select('SELECT * FROM events')->rows());
// Допустим нам нужно посчитать сколько уникальных пользователей просмотрело за сутки
print_r($client->select('
SELECT
event_date,
uniqCombined(user_uuid) as count_users
FROM
events
WHERE
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x'];
$db = new ClickHouseDB\Client($config);
try {
$db->ping();
} catch (ClickHouseDB\QueryException $E) {
echo "ERROR:" . $E->getMessage() . "\nOK\n";
}
// ------------------
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'x', 'password' => 'x'];
$db = new ClickHouseDB\Client($config);
try {
$db->ping();
} catch (ClickHouseDB\QueryException $E) {
echo "ERROR:" . $E->getMessage() . "\nOK\n";
}
// ------------------
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
try {
$db->ping();
echo "PING : OK!\n";
} catch (ClickHouseDB\QueryException $E) {
echo "ERROR:" . $E->getMessage() . "\nOK\n";
}
try {
$db->select("SELECT xxx as PPPP FROM ZZZZZ ")->rows();
} catch (ClickHouseDB\DatabaseException $E) {
echo "ERROR : DatabaseException : " . $E->getMessage() . "\n";
// Table default.ZZZZZ doesn't exist.
}
// ----------------------------
/**
*
*/
public function testSettings()
{
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x', 'settings' => ['max_execution_time' => 100]];
$db = new ClickHouseDB\Client($config);
$this->assertEquals(100, $db->settings()->getSetting('max_execution_time'));
// settings via constructor
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x'];
$db = new ClickHouseDB\Client($config, ['max_execution_time' => 100]);
$this->assertEquals(100, $db->settings()->getSetting('max_execution_time'));
//
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x'];
$db = new ClickHouseDB\Client($config);
$db->settings()->set('max_execution_time', 100);
$this->assertEquals(100, $db->settings()->getSetting('max_execution_time'));
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x'];
$db = new ClickHouseDB\Client($config);
$db->settings()->apply(['max_execution_time' => 100, 'max_block_size' => 12345]);
$this->assertEquals(100, $db->settings()->getSetting('max_execution_time'));
$this->assertEquals(12345, $db->settings()->getSetting('max_block_size'));
}
<?php
// Подключаем драйвер
include_once __DIR__ . '/../include.php';
// Конфигурация
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$client = new \ClickHouseDB\Client($config);
// Проверяем соединение с базой
$client->ping();
// Создаём таблицу
$client->write('CREATE DATABASE IF NOT EXISTS articles');
$client->write('DROP TABLE IF EXISTS articles.events');
$client->write("\n CREATE TABLE articles.events (\n event_date Date DEFAULT toDate(event_time),\n event_time DateTime,\n event_type Enum8('VIEWS' = 1, 'CLICKS' = 2),\n site_id Int32,\n article_id Int32,\n ip String,\n city String,\n user_uuid String,\n referer String,\n utm String DEFAULT extractURLParameter(referer, 'utm_campaign')\n ) ENGINE = MergeTree(event_date, (site_id, event_type, article_id), 8192)\n");
// Выбираем default базу
$client->database('articles');
// Получим список таблиц
print_r($client->showTables());
// Для упрощения выставляем принудительно таймзону
date_default_timezone_set('Europe/Moscow');
// Простая вставка данных `$db->insert(имя_таблицы, [данные], [колонки]);`
$client->insert('events', [[time(), 'CLICKS', 1, 1234, '192.168.1.11', 'Moscow', 'user_11', ''], [time(), 'CLICKS', 1, 1235, '192.168.1.11', 'Moscow', 'user_11', 'http://yandex.ru?utm_campaign=abc'], [time(), 'CLICKS', 1, 1236, '192.168.1.11', 'Moscow', 'user_11', 'http://smi2.ru?utm_campaign=abc'], [time(), 'CLICKS', 1, 1237, '192.168.1.11', 'Moscow', 'user_11', ''], [time(), 'CLICKS', 1, 1237, '192.168.1.13', 'Moscow', 'user_13', ''], [time(), 'CLICKS', 1, 1237, '192.168.1.14', 'Moscow', 'user_14', ''], [time(), 'VIEWS', 1, 1237, '192.168.1.11', 'Moscow', 'user_11', ''], [time(), 'VIEWS', 1, 1237, '192.168.1.12', 'Moscow', 'user_12', ''], [time(), 'VIEWS', 1, 27, '192.168.1.1', 'Rwanda', 'user_55', 'http://smi2.ru?utm_campaign=abc'], [time(), 'VIEWS', 1, 27, '192.168.1.1', 'Banaadir', 'user_54', 'http://smi2.ru?utm_campaign=abc'], [time(), 'VIEWS', 1, 27, '192.168.1.1', 'Tobruk', 'user_32', 'http://smi2.ru?utm_campaign=CM1'], [time(), 'VIEWS', 1, 28, '192.168.1.1', 'Gisborne', 'user_12', 'http://smi2.ru?utm_campaign=CM1'], [time(), 'VIEWS', 1, 26, '192.168.1.1', 'Moscow', 'user_43', 'http://smi2.ru?utm_campaign=CM3']], ['event_time', 'event_type', 'site_id', 'article_id', 'ip', 'city', 'user_uuid', 'referer']);
print_r($client->select('SELECT count() as count_rows FROM articles.events')->fetchOne());
// Для WHERE IN - создаем файл InsertRow
$hand = fopen('/tmp/articles_list.csv', 'w');
foreach ([1237, 27, 1234] as $article_id) {
fputcsv($hand, [$article_id]);
}
fclose($hand);
//
$whereIn = new \ClickHouseDB\WhereInFile();
$whereIn->attachFile('/tmp/articles_list.csv', 'namex', ['article_id' => 'Int32'], \ClickHouseDB\WhereInFile::FORMAT_CSV);
<?php
include_once __DIR__ . '/../include.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$db->write("DROP TABLE IF EXISTS arrays_test");
$res = $db->write('
CREATE TABLE IF NOT EXISTS arrays_test (
s_key String,
s_arr Array(UInt8)
) ENGINE = Memory
');
//------------------------------------------------------------------------------
echo "Insert\n";
$stat = $db->insert('arrays_test', [['HASH1', [11, 22, 33]], ['HASH1', [11, 22, 55]]], ['s_key', 's_arr']);
echo "Insert Done\n";
print_r($db->select('SELECT s_key, s_arr FROM arrays_test ARRAY JOIN s_arr')->rows());
$db->write("DROP TABLE IF EXISTS arrays_test_string");
$res = $db->write('
CREATE TABLE IF NOT EXISTS arrays_test_string (
s_key String,
s_arr Array(String)
) ENGINE = Memory
');
echo "Insert\n";
$stat = $db->insert('arrays_test_string', [['HASH1', ["a", "dddd", "xxx"]], ['HASH1', ["b'\tx"]]], ['s_key', 's_arr']);
echo "Insert Done\n";
print_r($db->select('SELECT s_key, s_arr FROM arrays_test_string ARRAY JOIN s_arr')->rows());
echo "\ntestRFCCSVWrite>>>>\n";
$fileName = '/tmp/testRFCCSVWrite.CSV';
date_default_timezone_set('Europe/Moscow');
<?php
include_once __DIR__ . '/../include.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
//$db->verbose();
$db->settings()->readonly(false);
$result = $db->select('SELECT 12 as {key} WHERE {key} = :value', ['key' => 'ping', 'value' => 12]);
if ($result->fetchOne('ping') != 12) {
echo "Error : ? \n";
}
print_r($result->fetchOne());
// ---------------------------- ASYNC SELECT ----------------------------
$state1 = $db->selectAsync('SELECT 1 as {key} WHERE {key} = :value', ['key' => 'ping', 'value' => 1]);
$state2 = $db->selectAsync('SELECT 2 as ping');
$db->executeAsync();
print_r($state1->fetchOne());
print_r($state1->rows());
print_r($state2->fetchOne('ping'));
//----------------------------------------//----------------------------------------
include_once __DIR__ . '/../include.php';
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x', 'settings' => ['max_execution_time' => 100]];
$db = new ClickHouseDB\Client($config);
if ($db->settings()->getSetting('max_execution_time') !== 100) {
throw new Exception("Bad work settings");
}
// settings via constructor
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x'];
$db = new ClickHouseDB\Client($config, ['max_execution_time' => 100]);
if ($db->settings()->getSetting('max_execution_time') !== 100) {
throw new Exception("Bad work settings");
}
// set method
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x'];
$db = new ClickHouseDB\Client($config);
$db->settings()->set('max_execution_time', 100);
if ($db->settings()->getSetting('max_execution_time') !== 100) {
throw new Exception("Bad work settings");
}
// apply array method
$config = ['host' => 'x', 'port' => '8123', 'username' => 'x', 'password' => 'x'];
$db = new ClickHouseDB\Client($config);
$db->settings()->apply(['max_execution_time' => 100, 'max_block_size' => 12345]);
if ($db->settings()->getSetting('max_execution_time') !== 100) {
throw new Exception("Bad work settings");
}
if ($db->settings()->getSetting('max_block_size') !== 12345) {
throw new Exception("Bad work settings");
}
echo "getSetting - OK\n";
<?php
include_once __DIR__ . '/../include.php';
include_once __DIR__ . '/lib_example.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$db->write("DROP TABLE IF EXISTS summing_url_views");
$db->write('
CREATE TABLE IF NOT EXISTS summing_url_views (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
url_hash String,
site_id Int32,
views Int32,
v_00 Int32,
v_55 Int32
)
ENGINE = SummingMergeTree(event_date, (site_id, url_hash, event_time, event_date), 8192)
');
echo "Table EXISTSs:" . json_encode($db->showTables()) . "\n";
// -------------------------------- CREATE csv file ----------------------------------------------------------------
$file_data_names = ['/tmp/clickHouseDB_test.1.data', '/tmp/clickHouseDB_test.2.data'];
foreach ($file_data_names as $file_name) {
makeSomeDataFile($file_name, 1);
}
// ----------------------------------------------------------------------------------------------------
echo "insert ONE file:\n";
$time_start = microtime(true);
$version_test = 3;
if ($version_test == 1) {
$statselect1 = $db->selectAsync('SELECT * FROM summing_url_views LIMIT 1');
<?php
include_once __DIR__ . '/../include.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
// ---------------------------- Write ----------------------------
echo "\n-----\ntry write:create_table\n";
$db->database('default');
//------------------------------------------------------------------------------
echo 'Tables EXISTS: ' . json_encode($db->showTables()) . PHP_EOL;
$db->write('DROP TABLE IF EXISTS summing_url_views');
echo 'Tables EXISTS: ' . json_encode($db->showTables()) . PHP_EOL;
$db->write('
CREATE TABLE IF NOT EXISTS summing_url_views (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
url_hash String,
site_id Int32,
views Int32,
v_00 Int32,
v_55 Int32
)
ENGINE = SummingMergeTree(event_date, (site_id, url_hash, event_time, event_date), 8192)
');
echo 'Table EXISTS: ' . json_encode($db->showTables()) . PHP_EOL;
/*
Table EXISTS: [{"name": "summing_url_views"}]
*/
//------------------------------------------------------------------------------
echo "Insert\n";
$stat = $db->insert('summing_url_views', [[time(), 'HASH1', 2345, 22, 20, 2], [time(), 'HASH2', 2345, 12, 9, 3], [time(), 'HASH3', 5345, 33, 33, 0], [time(), 'HASH3', 5345, 55, 0, 55]], ['event_time', 'url_hash', 'site_id', 'views', 'v_00', 'v_55']);
<?php
include_once __DIR__ . '/../include.php';
include_once __DIR__ . '/lib_example.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$db->write("DROP TABLE IF EXISTS summing_url_views");
$db->write('
CREATE TABLE IF NOT EXISTS summing_url_views (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
url_hash String,
site_id Int32,
views Int32,
v_00 Int32,
v_55 Int32
)
ENGINE = SummingMergeTree(event_date, (site_id, url_hash, event_time, event_date), 8192)
');
echo "Table EXISTS: " . json_encode($db->showTables()) . "\n";
// -------------------------------- CREATE csv file ----------------------------------------------------------------
// ----------------------------------------------------------------------------------------------------
$file_data_names = ['/tmp/clickHouseDB_test.1.data', '/tmp/clickHouseDB_test.2.data', '/tmp/clickHouseDB_test.3.data', '/tmp/clickHouseDB_test.4.data', '/tmp/clickHouseDB_test.5.data'];
foreach ($file_data_names as $file_name) {
makeSomeDataFile($file_name, 5);
}
// ----------------------------------------------------------------------------------------------------
echo "insert ONE file:\n";
$time_start = microtime(true);
$stat = $db->insertBatchFiles('summing_url_views', ['/tmp/clickHouseDB_test.1.data'], ['event_time', 'url_hash', 'site_id', 'views', 'v_00', 'v_55']);
echo "use time:" . round(microtime(true) - $time_start, 2) . "\n";
<?php
$fileName = '/tmp/__articles.big.events_version1.csv';
$count_rows = 500000;
// Подключаем драйвер
include_once __DIR__ . '/../include.php';
// Для упрощения выставляем принудительно таймзону
date_default_timezone_set('Europe/Moscow');
// класс userevent
include_once 'article_01_userevent.php';
// Конфигурация
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$client = new \ClickHouseDB\Client($config);
//$client->write('DROP TABLE IF EXISTS articles.events');
if (!$client->isExists('articles', 'events')) {
$client->write('DROP TABLE IF EXISTS articles.events');
$client->write('CREATE DATABASE IF NOT EXISTS articles');
$client->write("\n CREATE TABLE articles.events (\n event_date Date DEFAULT toDate(event_time),\n event_time DateTime,\n event_type Enum8('VIEWS' = 1, 'CLICKS' = 2),\n site_id Int32,\n article_id Int32,\n ip String,\n city String,\n user_uuid String,\n referer String,\n utm String DEFAULT extractURLParameter(referer, 'utm_campaign')\n ) ENGINE = MergeTree(event_date, (site_id,event_type, article_id), 8192)\n");
// ---------------------------- создадим тестовый набор данных ---------------
$userEvent = new UserEvent();
@unlink($fileName);
echo "Write data to : " . $fileName . "\n\n";
for ($z = 0; $z < $count_rows; $z++) {
$row = ['event_date' => $userEvent->getDate(), 'event_time' => $userEvent->getTime(), 'event_type' => $userEvent->getType(), 'site_id' => $userEvent->getSiteId(), 'article_id' => $userEvent->getArticleId(), 'ip' => $userEvent->getIp(), 'city' => $userEvent->getCity(), 'user_uuid' => $userEvent->getUserUuid(), 'referer' => $userEvent->getReferer(), 'utm' => $userEvent->getUtm()];
file_put_contents($fileName, \ClickHouseDB\FormatLine::TSV($row) . "\n", FILE_APPEND);
if ($z % 100 == 0) {
echo "{$z}\r";
}
}
// Включаем сжатие
$client->setTimeout(300);
<?php
include_once __DIR__ . '/../include.php';
include_once __DIR__ . '/lib_example.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$create = false;
if ($create) {
$db->write("DROP TABLE IF EXISTS summing_partions_views");
$db->write('
CREATE TABLE IF NOT EXISTS summing_partions_views (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
site_id Int32,
hash_id Int32,
views Int32
)
ENGINE = SummingMergeTree(event_date, (site_id,hash_id, event_time, event_date), 8192)
');
echo "Table EXISTS:" . json_encode($db->showTables()) . "\n";
echo "----------------------------------- CREATE csv file -----------------------------------------------------------------\n";
$file_data_names = ['/tmp/clickHouseDB_test.part.1.data', '/tmp/clickHouseDB_test.part.2.data', '/tmp/clickHouseDB_test.part.3.data'];
$c = 0;
foreach ($file_data_names as $file_name) {
$c++;
makeSomeDataFileBigOldDates($file_name, $c);
}
echo "--------------------------------------- insert -------------------------------------------------------------\n";
echo "insert ALL file async + GZIP:\n";
$db->enableHttpCompression(true);
$time_start = microtime(true);
<?php
include_once __DIR__ . '/../include.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
$db->enableLogQueries()->enableHttpCompression();
//----------------------------------------
print_r($db->select('SELECT * FROM system.query_log')->rows());
//----------------------------------------
<?php
include_once __DIR__ . '/../include.php';
include_once __DIR__ . '/lib_example.php';
class CustomDegeneration implements \ClickHouseDB\Query\Degeneration
{
private $bindings = [];
public function bindParams(array $bindings)
{
$this->bindings = $bindings;
}
public function process($sql)
{
if (sizeof($this->bindings)) {
foreach ($this->bindings as $key => $value) {
$sql = str_ireplace('%' . $key . '%', $value, $sql);
}
}
return str_ireplace('XXXX', 'SELECT', $sql);
}
}
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$db = new ClickHouseDB\Client($config);
print_r($db->select('SELECT 1 as ping')->fetchOne());
// CustomConditions
$db->addQueryDegeneration(new CustomDegeneration());
// strreplace XXXX=>SELECT
print_r($db->select('XXXX 1 as ping')->fetchOne());
// SELECT 1 as ping
print_r($db->select('XXXX 1 as %ZX%', ['ZX' => 'ping'])->fetchOne());
<?php
include_once __DIR__ . '/../include.php';
include_once __DIR__ . '/lib_example.php';
include_once __DIR__ . '/../../_clickhouse_config_product.php';
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$start_time = microtime(true);
$db = new ClickHouseDB\Client($config);
$db->database('aggr');
print_r($db->select('
SELECT event_date, site_id, group, SUM(views) as views FROM aggr.summing_url_views
WHERE event_date = today() AND site_id = 14776
GROUP BY event_date, site_id, group
ORDER BY views DESC
LIMIT 3
')->rows());
$sql = '
SELECT site_id, group, SUM(views) as views FROM aggr.summing_url_views
WHERE
event_date = today()
AND (
site_id IN (SELECT site_id FROM namex)
OR
site_id IN (SELECT site_id FROM site_keys)
)
GROUP BY site_id, group
ORDER BY views DESC
LIMIT 5
';
// some file names to data
$file_name_data1 = "/tmp/temp_csv.txt";