// Подключаем драйвер
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
site_id=1
}
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;
foreach ($file_data_names as $file_name) {
$c++;
$shift_days = -1 * $c * 3;
makeSomeDataFileBig($file_name, 23 * $c, $shift_days);
}
echo "----------------------------------------------------------------------------------------------------\n";
echo "insert ALL file async + GZIP:\n";
$db->enableHttpCompression(true);
$time_start = microtime(true);
$result_insert = $db->insertBatchFiles('summing_url_views_cityHash64_site_id', $file_data_names, ['event_time', 'url_hash', 'site_id', 'views', 'v_00', 'v_55']);
echo "use time:" . round(microtime(true) - $time_start, 2) . "\n";
$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);
$result_insert = $db->insertBatchFiles('summing_partions_views', $file_data_names, ['event_time', 'site_id', 'hash_id', 'views']);
echo "use time:" . round(microtime(true) - $time_start, 2) . " sec.\n";
foreach ($result_insert as $fileName => $state) {
echo "{$fileName} => " . json_encode($state->info_upload()) . "\n";
<?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']);
