sublimity Nov 7 2016 at 07:29

Как запустить ClickHouse своими силами и выиграть джекпот

15 min

79K

СМИ2 corporate blogBig Data*NoSQL*PHP*SQL*

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

Clickhouse-client

В предисловии статьи — небольшой рассказ о наших попытках внедрить 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 и работу с ним:

Графический клиент для работы с БД
Обертка на 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)

Clickhouse GUI example

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

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

Tags:

Hubs: