Мы решили описать простой и проверенный путь для тех, кто хочет внедрить аналитическую СУБД ClickHouse своими силами или просто испробовать ClickHouse на собственных данных. Именно этот путь прошли мы сами в новостном агрегаторе СМИ2 и добились впечатляющих результатов.

В предисловии статьи — небольшой рассказ о наших попытках внедрить Druid и InfluxDB. Почему после успешного запуска ClickHouse мы смогли отказаться от использования InfiniDB и Cassandra.

Основная часть статьи посвящена продуктам-помощникам для работы с ClickHouse, которые мы сами разработали и выпустили в open-source. Кстати, добро пожаловать в pull requests с предложениями и замечаниями.

Предполагаем, что читатель знаком с официальной документацией ClickHouse.

Кто мы такие и с какими данными работаем

В начале расскажем о том, кто мы такие, и о данных, на примере которых мы будем далее разбирать работу с ClickHouse. СМИ2 — информационный сервис, который с 2008 года круглосуточно поставляет актуальные новости и формирует полноценную информационную картину дня. На сегодняшний день СМИ2 включает в себя новостной агрегатор и обменную сеть с более чем 2500 партнерами, среди которых ведущие федеральные онлайн-СМИ, отраслевые сайты и региональные издания. Месячная аудитория СМИ2 составляет порядка 15 млн человек.

Мы будем разбирать работу с ClickHouse на примере одной из простых частей данных, собираемых с нашего новостного агрегатора, который представлен тремя региональными сайтами: smi2.ru, smi2.ua и smi2.kz. На каждом сайте мы собираем и обрабатываем данные о просмотрах и кликах по новостям. Эти данные используются как в режиме реального времени — для выдачи контента, так и для постанализа эффективности материалов.

В вашем случае анализируемыми данными могут быть, например, логи сервера, статистика по событиям на сайтах, в системах бронирования, электронной рассылки, отслеживания показаний датчиков и т. п. Во всех этих случаях, а также если у вас много данных, ClickHouse стоит того, чтобы попробовать.

Как мы пришли к ClickHouse

Мы определили для себя следующие критические требования к аналитической СУБД:

• скорость обработки запросов в режиме реального времени
• наличие встроенных аналитических функций
• наличие функций для приближенных вычислений
• линейная масштабируемость, т. к. добиться линейной масштабируемости без деградации с ростом числа серверов — довольно сложная техническая задача
• наличие механизмов шардирования и репликации данных «из коробки»
• отсутствие единой точки отказа (в каждый узел в кластере можно писать данные)
• оптимальная стоимость владения (соотношение цена-качество)

В качестве предыстории хотелось бы рассказать о том, какой технологический стек мы использовали ранее, от чего пришлось отказаться и как мы пришли к ClickHouse.

Неудачный опыт с Druid и InfluxDB

В этом году мы развернули сборку на основе Druid — Imply Analytics Platform, а также Tranquility, и уже приготовились запускать в продакшн… Но после выхода ClickHouse сразу отказались от Druid, хотя потратили два месяца на его изучение и внедрение.

Из плюсов отметили для себя следующее:

• Поддержка RT stream из HTTP, Spark, Kafka и т. д.
• Графические инструменты Pivot, Caravel

Однако следующие недостатки перевесили чашу весов:

• Сложность инфраструктуры: требуются отдельные ноды для получения, обработки и хранения данных, для отказоустойчивости необходимо двукратное количество серверов
• Tranquility, предназначенный для realtime обработки данных, содержит ошибки, приводящие к падению всего Tranquility; версии Tranquility не совместимы между собой; для себя мы оценили Tranquility ка к хороший и интересный продукт, но пока в состоянии Beta

Также у нас был пробный подход к системе InfluxDB (см. статью), которую мы планировали использовать для построения и анализа метрик. Проект мы оценили для себя как глубокую Alfa из-за частых потерь данных и падений системы, поэтому работу в этом направлении мы тоже прекратили. Возможно, сейчас состояние продукта изменилось в лучшую сторону.

Cassandra и InfiniDB продержались у нас два года

Cassandra использовалась у нас в продакшне с 2014 по 2016 год:

• Работала на 5 серверах
• Выдерживала нагрузку до 10К событий в секунду на вставку и примерно до 1К событий в секунду на чтение
• Приблизительно 1 раз в 2 месяца случались рассинхронизации схем данных (возможно, это была проблема версии, которую мы использовали)

В этот же период мы использовали и InfiniDB. Из положительных моментов хотелось бы отметить следующие:

• Поддержка оконных функций
• Простота интеграции с существующим MySQL через движок Federated
• Встроенный движок MyISAM и InnoDB, что позволяло делать выгрузки из движка InfiniDB в движок InnoDB внутри одного сервера
• Возможность удаления партиций данных по каждому дню, по определенным колонкам

Однако не обошлось и без отрицательных моментов:

• Отсутствие нормального кластера и репликации данных. Приходилось делать горячую копию данных, т. е. клон сервера
• Первые версии приходилось регулярно перегружать из-за утечек памяти и зависаний сервиса
• Зависание процессов на запись или запросов на чтение. Приходилось убивать долгие процессы через event handlers nagios
• Сложность загрузки данных. Есть только отдельный консольный инструмент cpimport. Пришлось реализовывать обертку, которая разбирает вывод утилиты в stdout на ошибки и статистику результата выполнения вставки
• Условная однопоточность: или пишем, или читаем. Потребляется большой объем системных ресурсов

И тут «Яндекс» выложил в открытый доступ ClickHouse

Из-за недостатков и проблем с используемыми у нас для аналитики СУБД мы регулярно смотрели по сторонам в поисках альтернатив. В том числе мы обратили внимание на внутреннюю разработку «Яндекса», которая подкупала своим невероятным быстродействием и в целом соответствовала нашим ожиданиям от аналитической СУБД (см. выше).

В настоящий момент на рынке нет бесплатных или недорогих аналитических баз данных для обработки больших данных в режиме реального времени уровня, подобного ClickHouse. Во всяком случае, мы о таких не знаем. Из платных баз данных мы тестировали HP Vertica и Greenplum. Аналитику можно считать и с помощью MapReduce на Hadoop, но не в режиме, близком к реальному времени. Кстати, в самом «Яндексе» есть YT («Ыть», как они сами ее называют) — MapReduce-платформа для работы с большими данными, но она тоже не работает в режиме реального времени, хотя активно используется. То есть для аналитики в режиме реального времени, по нашему мнению, больше всего подходит ClickHouse. Поэтому, когда «Яндекс» опубликовал летом ClickHouse в открытый доступ, мы однозначно решили его попробовать.

Как нам помог ClickHouse

Мы можем уверенно утверждать, что процесс запуска ClickHouse прошел у нас быстрее и проще, чем с другими СУБД. Надеемся, что наша статья позволит вам сделать это существенно быстрее :)

Если пропустить историю о том, как мы запускали ClickHouse и в итоге успешно запустили, то стоит отметить следующие результаты запуска ClickHouse.

Выгоды в разработке. В относительно короткий срок нам удалось закрыть 80 % задач, связанных с анализом данных, а этих задач накопилось много. Новые задачи по аналитике стали выполняться гораздо проще и быстрее.

Выгоды в железе. По сравнению с тем же Druid, требования к железу у ClickHouse оказались существенно ниже, поэтому нам удалось сэкономить на железе. Плюс, мы отказались от 5 нод под Cassandra, 4 нод под InfiniDB и 2 нод под MySQL (исторически оставшейся аналитики). Итого мы отказались от 11 серверов, за которыми нужно было постоянно присматривать и не пропускать алерты о проблемах от nagios.

Выгоды в хранении данных. ClickHouse хранит данные с использованием различных механизмов сжатия. За счет поддержки шардирования и репликации ClickHouse способен хранить и обрабатывать данные распределенно. Репликация не только повышает надежность хранения данных, но и оптимизирует операции чтения в рамках кластера.

Выгоды в скорости. ClickHouse реально быстрый, мы убедились в этом на своих задачах, скорость возросла в несколько раз!

Здесь многие подумают, что неплохо было бы привести для примера бенчмарки… Предлагаем обратиться к бенчмаркам «Яндекса» и посмотреть наши ролики с запросами на реальных наборах данных. Статистика собираемых и анализируемых нами с помощью ClickHouse данных на текущий момент такова:

• регистрируется до 8 000—12 000 событий в секунду
• приблизительно 21,5 млрд событий за месяц
• примерно 10 млрд строк в базе за месяц

Данные хранятся на 6 серверах SX131 от Hetzner с 3 шардами по 2 реплики.

Особенности ClickHouse

Как у любого продукта для работы с данными, у ClickHouse есть свои особенности. Вот некоторые из них:

• Отсутствие UPDATE и производных: INSERT UPDATE и DELETE
• Отсутствие транзакционности
• Удаление данных по месяцу через удаление партиций

Кроме этого, ClickHouse не умеет строить графики «из коробки», для этого нужны дополнительные инструменты.

Для нас не важна транзакционность и отсутствие UPDATE / DELETE. Мы давно привыкли обходить эти проблемы. Однако нам очень хотелось бы иметь возможность хранить данные только за несколько дней. В планах «Яндекса» — добавить возможность удаления партиций по дням.

Наши проекты для ClickHouse

В процессе освоения и внедрения ClickHouse мы столкнулись с некоторыми неудобствами и отсутствием нужных нам «плюшек». Поэтому, не став ждать милостей от «Яндекса» природы, мы решили облегчить себе работу сами. Еще одним мотиватором было то, что нам хотелось внести свой вклад в развитие перспективного open-source проекта. Плюс — это был наш первый опыт участия в open-source разработке.

Так родились два наших open-source проекта, которые позволили нам самим существенно ускорить и упростить процесс внедрения ClickHouse и работу с ним:

1. Графический клиент для работы с БД
2. Обертка на PHP для удобной работы с БД, реализующая возможности ClickHouse

Ниже описаны основные возможности каждого проекта.

Наш графический клиент для ClickHouse: возможности и особенности

• Просмотр списка баз данных и таблиц
• Просмотр содержимого таблицы
• Подсветка функций ClickHouse, названий таблиц и полей
• Автодополнение для названий таблиц, колонок и встроенных функций
• Выполнение выделенного / текущего / нескольких запросов в редакторе
• Автоматическое определение типа запроса: CREATE TABLE / INSERT / SELECT
• Удобная вставка значений словарей
• Темы оформления для редактора запросов, темы оформления для всего редактора (светлая и темная)
• Горячие клавиши

Клиент написан полностью на JavaScript, без использования server side.

Вы можете спокойно использовать наш последний опубликованный билд.

Наш PHP-драйвер для ClickHouse: возможности и особенности

• Отсутствие зависимостей, требуются только модули curl и json
• Работа с кластером ClickHouse, автоматическое определение необходимых нод при разных конфигурациях
• Выполнение запроса на каждой ноде в кластере (см. наш отдельный проект, посвященный миграциям на ClickHouse)
• Асинхронное выполнение запросов на чтение данных и вставку данных
• Поддержка сжатия на лету при записи данных в ClickHouse из локального файла без создания временных файлов
• Поддержка запросов на чтение с использованием локального CSV-файла для выполнения запроса вида select * from X where id in (local_csv_file)
• Работа с партициями таблиц
• Вставка массива в колонку
• Запись результата запроса напрямую в файл с поддержкой сжатия без создания временных файлов
• Получение размера таблицы, базы и списка процессов на каждой ноде
• Получение статистики выполнения запроса SELECT

Драйвер протестирован на PHP 5.6 и 7, HHVM 3.9.

Хотим сразу предупредить читателей, что драйвер не использует готовые решения вроде Guzzle (и PSR-7 вообще), а реализован через файл include.php. Надеемся, что этот факт не отпугнет вас от дальнейшего чтения.

Примеры работы с ClickHouse

Рассмотрим на примере, как работать ClickHouse из PHP и с помощью нашего графического клиента.

Считаем, что вы успешно установили ClickHouse из deb-пакета последней версии и ознакомились с Quick start guide.

Пусть у сайта smi2.ru site_id = 1, у smi2.ua site_id = 2, а у smi2.kz site_id = 3.

На каждом сайте совершаются события, связанные со статьями (новостями). Мы будем регистрировать данные о показах статей (views) и кликах по каждой статье (clicks).

По каждому событию мы будем фиксировать несколько атрибутов:

• IP-адрес пользователя
• город пользователя
• referer
• UTM-метку из referer
• уникальный ID пользователя

Подключение к серверу ClickHouse, создание БД и таблицы

Для записи данных о событиях создадим на сервере ClickHouse базу данных articles и внутри — нее таблицу events со следующей структурой:

    event_date  Date
    event_time  DateTime
    event_type  Enum8('VIEWS' = 1, 'CLICKS' = 2)
    site_id     Int32
    article_id  Int32
    ip          String
    city        String
    user_uuid   String
    referer     String
    utm         String

Сначала рассмотрим создание базы данных и таблицы с помощью нашего графического клиента. Подключаемся через графический клиент к серверу ClickHouse и выполняем запрос на создание новой базы данных и новой таблицы:

CREATE DATABASE articles
;
CREATE TABLE articles.events (
    event_date  Date,
    event_time  DateTime,
    event_type  Enum8('VIEWS' = 1, 'CLICKS' = 2),
    site_id     Int32,
    article_id  Int32,
    ip          String,
    city        String,
    user_uuid   String,
    referer     String,
    utm         String
) engine=MergeTree(event_date, (site_id, event_type, article_id), 8192)

Поясним некоторые параметры этого запроса:

• MergeTree — это движок таблицы. Также существуют Log, CollapsingMergeTree, SummingMergeTree, ReplacingMergeTree и другие.
• Первый параметр event_date указывает на имя столбца типа Date, содержащего дату.
• (site_id, event_type, article_id) — кортеж, определяющий первичный ключ таблицы (индекс).

В большинстве запросов на чтение планируется указывать, по какому сайту нам нужны данные, поэтому первым в индексе используется site_id.

Теперь попробуем создать подключение к серверу ClickHouse, базу данных и таблицу через наш драйвер PHP. Для этого сначала установим драйвер.

Установку стабильной сборки драйвера можно выполнить через composer:
composer require smi2/phpclickhouse

либо клонировать драйвер из основной (master) ветки Git-репозитория:
git clone https://github.com/smi2/phpClickHouse.git

Более подробная информация по установке драйвера доступна в документации к драйверу, которая также содержит описание функций драйвера и ChangeLog.

После того как драйвер был успешно установлен, выполняем запрос на подключение к серверу, создание БД и таблицы:

<?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("CREATE TABLE IF NOT EXISTS articles.events (
    event_date  Date,
    event_time  DateTime,
    event_type  Enum8('VIEWS' = 1, 'CLICKS' = 2),
    site_id     Int32,
    article_id  Int32,
    ip          String,
    city        String,
    user_uuid   String,
    referer     String,
    utm         String
    ) 
    engine=MergeTree(event_date, (site_id, event_type, article_id), 8192)
");

// Выбираем default базу
$client->database('articles');
// Получаем список таблиц
print_r($client->showTables());

Обращаем внимание, что запросы в драйвере разделены на следующие:

• запись
• вставку данных
• чтение

Операции вставки и чтения данных могут выполняться параллельно.

Запросы на запись и вставку данных не содержат ответа, выполняется только проверка, что ответ сервера был положительным. Запросы на чтение ответ содержат (исключением является прямая запись ответа в файл).

Вставка данных, в том числе из TSV-файла

Вставим данные, которые будем использовать для тестирования:

$client->insert('events',
[
    [date('Y-m-d'), time(), 'CLICKS', 1, 1234, '192.168.1.1', 'Moscow', 'xcvfdsazxc', '', ''],
    [date('Y-m-d'), time(), 'CLICKS', 1, 1235, '192.168.1.1', 'Moscow', 'xcvfdsazxc', 'http://yandex.ru', ''],
    [date('Y-m-d'), time(), 'CLICKS', 1, 1236, '192.168.1.1', 'Moscow', 'xcvfdsazxc', '', ''],
    [date('Y-m-d'), time(), 'CLICKS', 1, 1237, '192.168.1.1', 'Moscow', 'xcvfdsazxc', '', ''],
],
[
    'event_date', 'event_time', 'event_type', 'site_id', 'article_id', 'ip', 'city', 'user_uuid', 'referer', 'utm'
]
);

Такой метод вставки подходит только для маленьких таблиц или таблиц справочников, так как в этом случае будет выполняться преобразование массива в строку.

Получим результат вставки данных:

print_r(
        $client->select('SELECT * FROM events')->rows()
    );

Подробнее про чтение данных написано ниже. Для вставки большего количества строк воспользуемся прямой загрузкой TSV-файла, который будет генерироваться при событии. Для этого будем записывать TSV-файл на сервере, где происходят события, и для упрощения отправлять его в ClickHouse.

Допустим, что у нас есть некий класс UserEvent, который позволяет получить все необходимые данные для вставки, данные проверены на валидность внутри класса:

$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(),
        ];

Запись будем производить в файл, ротируемый ежеминутно следующим способом (допускаем все недостатки — ошибки записи, блокировки, и т. д. — строка всегда записывается):

// Имя файла 
$filename='/tmp/articles.events_version1_'.date("YmdHi").'.TSV';
// Преобразование массива в строку TabSeparated
$text=\ClickHouseDB\FormatLine::TSV($row)."\n";

// Также можно преобразовать массив в строку CSV  
// $text=\ClickHouseDB\FormatLine::CSV($row)."\n";

file_put_contents($filename,$text,FILE_APPEND);

На GitHub для тестов сделан эмулятор класса UserEvent и пример использования этого класса с записью в базу.

Допустим, что у нас накопилось 5—10 таких файлов, и мы готовы их отправить в базу:

$file_data_names=
[
    '/tmp/articles.events_version1_201612121201.TSV',
    '/tmp/articles.events_version1_201612121301.TSV',
    '/tmp/articles.events_version1_201612121401.TSV'
]
// Включаем сжатие
$client->enableHttpCompression(true);
// Отправляем TSV-файлы в ClickHouse
$result_insert = $client->insertBatchTSVFiles('events', [$file_data_names], [
        'event_date',
        'event_time',
        'event_type',
        'site_id',
        'article_id',
        'ip',
        'city',
        'user_uuid',
        'referer',
        'utm'
    ]);
// Получаем время, за которое данные были доставлены 
foreach ($file_data_names as $fileName) {
    echo $fileName . " : " . $result_insert[$fileName]->totalTimeRequest() . "\n";
}

Стоит отметить, что работа с CSV-файлами также поддерживается. Для них нужно использовать функцию insertBatchFiles(), аналогичную функции insertBatchTSVFiles(). Однако при использовании TSV-файлов появляется дополнительная возможность вставлять в поле DateTime дату и время в формате unix timestamp. Подробнее о поддержке формата TabSeparated см. в документации ClickHouse.

ClickHouse использует формат CSV, соответствующий RFC 4180. При этом стандартные средства PHP, а именно функция fputcsv(), не полностью соответствует требованиям формата (см. отчет об ошибке).

Для полноценной поддержки форматов TSV и CSV-файлов нами были реализованы преобразователи массива в строку: FormatLine::CSV() и FormatLine::TSV(), которые используют возможность ClickHouse хранить в колонках данные в виде массивов.

При больших объемах вставляемых из файлов данных включаем режим сжатия. В этом случае используется потоковое сжатие без создания временных файлов, что позволяет экономить на сетевых ресурсах сервера, немного увеличивая нагрузку на CPU. Скорость передачи данных возрастает, и суммарное время, затрачиваемое на обработку одного файла, уменьшается в несколько раз.

В нашем примере для каждой строки мы передаем поле event_date, хотя эта же дата передается в поле event_time. Можно сэкономить ресурсы и не передавать каждый раз поля, которые можно вычислить на сервере ClickHouse из другого поля. Подробнее о значениях по умолчанию см. в документации по ClickHouse.

Поле utm будем заполнять из поля referer, если в нем указан utm_campaign, через функцию extractURLParameter(referer,’utm_campaign’).

Пересоздадим таблицу:

CREATE TABLE articles.events (
    event_date  Date DEFAULT toDate(event_time),
    event_time  DateTime,
    event_type  Enum8('VIEWS' = 1, 'CLICKS' = 2),
    site_id     Int32,
    article_id  Int32,
    ip          String,
    city        String,
    user_uuid   String,
    referer     String,
    utm         String DEFAULT extractURLParameter(referer, 'utm_campaign')
) engine=MergeTree(event_date, (site_id, event_type,article_id), 8192)

Изменим запись:

$client->insert('events',
    [
        [time(), 'CLICKS', 1, 1234, '192.168.1.11', 'Moscow', 'user_11', ''],
        [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']
);

Чтение данных

Меньше слов — больше кода!.. Приведем простой пример, как два запроса выполняются параллельно через драйвер:

$state1 = $db->selectAsync('SELECT 1 AS ping');
$state2 = $db->selectAsync('SELECT 2 AS ping');

// Отправка запросов в ClickHouse 
$db->executeAsync();

// Результат 
print_r($state1->rows())
print_r($state2->rows())

Вариант без асинхронности:

$statement = $db->select(''SELECT 33 AS ping'); 

Результат запросов — это объект Statement, который умеет делать следующее:

// Получить количество строк в результирующем наборе 
$statement->count();
// Получить минимальную оценку количества строк до применения LIMIT-а (rows_before_limit_at_least)
$statement->countAll();
// Получить первую строку ответа как массив
$statement->fetchOne();
// Получить "тотальные" значения, если в запросе SELECT используется WITH TOTALS
print_r($statement->totals());
// Получить все строки в виде массива 
print_r($statement->rows());
// Получить суммарное время, потраченное на соединение с базой и получение ответа, данные из curl
print_r($statement->totalTimeRequest());
// Получить полный ответ curl_info 
print_r($statement->responseInfo());
// Получить информацию о выполнении запроса, предоставленную ClickHouse
print_r($result->statistics());

Попробуем прочитать наши данные. Допустим, нам нужно посчитать, сколько уникальных пользователей просмотрело статьи по дням:

       SELECT
            event_date,
            uniqCombined(user_uuid) AS count_users
        FROM
            events
        WHERE
            site_id=1
        GROUP BY
            event_date
        ORDER BY
            event_date
        LIMIT 4

Сколько пользователей, которые просматривали статьи, совершили клики:

       SELECT
            user_uuid,
            count() AS clicks
        FROM
            articles.events
        WHERE
            event_type IN ( 'CLICKS' )
            AND site_id = 1
            AND user_uuid IN  (
                SELECT
                    user_uuid
                FROM
                    articles.events
                WHERE
                    event_type IN ( 'VIEWS' ) AND site_id = 1
                GROUP BY
                    user_uuid
            )
        GROUP BY user_uuid
        LIMIT 5

Какие UTM-метки давали наибольшее количество просмотров и кликов:

       SELECT
            utm,
            countIf(event_type IN('VIEWS')) AS views,
            countIf(event_type IN('CLICKS')) AS clicks
        FROM
            events
        WHERE
            event_date = today()
            AND site_id = 1
        GROUP BY
            utm
        ORDER BY
            views DESC
        LIMIT 15

Использование внешних данных для обработки запроса

Допустим, что нам нужно посчитать, сколько уникальных пользователей просмотрело за сутки статьи X, где в X перечислено несколько идентификаторов статей. Это можно сделать так:

WHERE article_id IN (1,2,3,4,5,6,7,8,9)

В данном примере все будет прекрасно работать. Но что делать, если идентификаторов тысячи или десятки тысяч? В этом случае пригодится функционал ClickHouse, который позволяет использовать внешние данные для обработки запроса.

Рассмотрим эту возможность ClickHouse на примере. Создадим CSV-файл '/tmp/articles_list.csv', в котором перечислим все нужные для запроса article_id, и попросим ClickHouse создать временную таблицу namex, содержащую одну колонку:

$whereIn = new \ClickHouseDB\WhereInFile();
$whereIn->attachFile('/tmp/articles_list.csv', 'namex', ['article_id' => 'Int32'], \ClickHouseDB\WhereInFile::FORMAT_CSV);

Тогда содержимое CSV-файла можно использовать на сервере:

$sql = "
    SELECT 
      article_id, 
      countIf(event_type='CLICKS') AS count_clicks, 
      countIf(event_type='VIEWS') AS count_views 
    FROM articles.events
    WHERE 
          article_id IN (SELECT article_id FROM namex)
    GROUP BY article_id
    ORDER BY count_views DESC
";
$result = $db->select($sql, [], $whereIn);

См. этот пример на GitHub.

Также функцией attachFile() поддерживаются файлы в форматах TabSeparated и TabSeparatedWithNames.

Что дальше

На этом мы, пожалуй, завершим первую часть нашего рассказа о ClickHouse.

Много полезной информации о ClickHouse вы можете узнать в Гугл-группе.

Если у вас есть замечания или вы нашли ошибки, опечатки — добро пожаловать в мир open-source, будем ждать ваших pull request по этой статье. Если вы любите анализ данных и вам интересно поработать с данными и ClickHouse — добро пожаловать к нам в команду ;)

Мы планируем сделать цикл материалов, посвященных нашему опыту работы с ClickHouse.
В планах — следующие темы.

Часть 2:

• Подключение к кластеру ClickHouse из PHP
• Отправка запросов в кластер, реализация миграций на PHP

Часть 3:

• Использование словарей из MySQL в ClickHouse
• Движки таблиц: CollapsingMergeTree, SummingMergeTree, MaterializedView

Часть 4:

• Примеры запросов в ClickHouse на открытых данных СМИ2
• Семплирование данных в ClickHouse