/** * */ public function testSelectWhereIn() { $file_data_names = [$this->tmp_path . '_testInsertCSV_clickHouseDB_test.1.data']; $file_name_where_in1 = $this->tmp_path . '_testSelectWhereIn.1.data'; $file_name_where_in2 = $this->tmp_path . '_testSelectWhereIn.2.data'; foreach ($file_data_names as $file_name) { $this->create_fake_csv_file($file_name, 2); } $this->db->insertBatchFiles('summing_url_views', $file_data_names, ['event_time', 'url_hash', 'site_id', 'views', 'v_00', 'v_55']); $st = $this->db->select('SELECT sum(views) as sum_x, min(v_00) as min_x FROM summing_url_views'); $this->assertEquals(2136, $st->fetchOne('sum_x')); $whereIn_1 = [[85, 'x85x2'], [69, 'x69x2'], [20, 'x20x2'], [11, 'xxxxx'], [12, 'zzzzz']]; $whereIn_2 = [[11, 'x11x2'], [12, 'x12x1'], [13, 'x13x2'], [14, 'xxxxx'], [15, 'zzzzz']]; $this->make_csv_SelectWhereIn($file_name_where_in1, $whereIn_1); $this->make_csv_SelectWhereIn($file_name_where_in2, $whereIn_2); $whereIn = new \ClickHouseDB\WhereInFile(); $whereIn->attachFile($file_name_where_in1, 'whin1', ['site_id' => 'Int32', 'url_hash' => 'String'], \ClickHouseDB\WhereInFile::FORMAT_CSV); $whereIn->attachFile($file_name_where_in2, 'whin2', ['site_id' => 'Int32', 'url_hash' => 'String'], \ClickHouseDB\WhereInFile::FORMAT_CSV); $result = $this->db->select(' SELECT url_hash, site_id, sum(views) as views FROM summing_url_views WHERE (site_id,url_hash) IN (SELECT site_id,url_hash FROM whin1) or (site_id,url_hash) IN (SELECT site_id,url_hash FROM whin2) GROUP BY url_hash,site_id ', [], $whereIn); $result = $result->rowsAsTree('site_id'); $this->assertEquals(11, $result['11']['site_id']); $this->assertEquals(20, $result['20']['site_id']); $this->assertEquals(24, $result['13']['views']); $this->assertEquals('x20x2', $result['20']['url_hash']); $this->assertEquals('x85x2', $result['85']['url_hash']); $this->assertEquals('x69x2', $result['69']['url_hash']); // --- drop foreach ($file_data_names as $file_name) { unlink($file_name); } }
// Конфигурация $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); // $sql = "\n SELECT \n article_id, \n countIf(event_type = 'CLICKS') as count_clicks, \n countIf(event_type = 'VIEWS') as count_views \n FROM \n articles.events\n WHERE \n article_id IN (SELECT article_id FROM namex)\n GROUP BY \n article_id\n ORDER BY \n count_views DESC\n"; $statement = $client->select($sql, [], $whereIn); print_r($statement->rows());
echo "\n----------------------- ASYNC ------------ \n"; $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) GROUP BY site_id, group ORDER BY views DESC LIMIT {limit} '; $bindings['limit'] = 3; $statements = []; $whereIn = new \ClickHouseDB\WhereInFile(); $whereIn->attachFile($file_name_data1, 'namex', ['site_id' => 'Int32', 'site_hash' => 'String'], \ClickHouseDB\WhereInFile::FORMAT_CSV); $statements[0] = $db->selectAsync($sql, $bindings, $whereIn); // change data file - for statement two $whereIn = new \ClickHouseDB\WhereInFile(); $whereIn->attachFile($file_name_data2, 'namex', ['site_id' => 'Int32', 'site_hash' => 'String'], \ClickHouseDB\WhereInFile::FORMAT_CSV); $statements[1] = $db->selectAsync($sql, $bindings, $whereIn); $db->executeAsync(); foreach ($statements as $statement) { print_r($statement->rows()); } /* Не перечисляйте слишком большое количество значений (миллионы) явно. Если множество большое - лучше загрузить его во временную таблицу (например, смотрите раздел "Внешние данные для обработки запроса"), и затем воспользоваться подзапросом. Внешние данные для обработки запроса При использовании HTTP интерфейса, внешние данные передаются в формате multipart/form-data. Каждая таблица передаётся отдельным файлом. Имя таблицы берётся из имени файла. В query_string передаются параметры name_format, name_types, name_structure, где name - имя таблицы, которой соответствуют эти параметры. Смысл параметров такой же, как при использовании клиента командной строки. Пример: cat /etc/passwd | sed 's/:/\t/g' > passwd.tsv curl -F 'passwd=@passwd.tsv;' 'http://localhost:8123/?query=SELECT+shell,+count()+AS+c+FROM+passwd+GROUP+BY+shell+ORDER+BY+c+DESC&passwd_structure=login+String,+unused+String,+uid+UInt16,+gid+UInt16,+comment+String,+home+String,+shell+String'