<?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');
$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 GROUP BY event_date ORDER BY event_date
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']); echo "Insert Done\n"; //------------------------------------------------------------------------------ echo "Try select \n"; $st = $db->select('SELECT * FROM summing_url_views LIMIT 2'); echo "Count select rows:" . $st->count() . "\n"; echo "Count all rows:" . $st->countAll() . "\n"; echo "First row:\n"; print_r($st->fetchOne()); echo "extremes_min:\n"; print_r($st->extremesMin()); echo "totals:\n"; print_r($st->totals()); $st = $db->select('SELECT event_date,url_hash,sum(views),avg(views) FROM summing_url_views WHERE site_id<3333 GROUP BY event_date,url_hash WITH TOTALS'); echo "Count select rows:" . $st->count() . "\n"; /*