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

Мы решили описать простой и проверенный путь для тех, кто хочет внедрить аналитическую СУБД 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 и работу с ним:


  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)

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.


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


'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

Комментарии (1)

  • 7 ноября 2016 в 09:37

    0

    ↑

    ↓

    А можно подробней про сравнение Druid vs ClickHouse именно в части выборки данных и запросов? (скорость выборки и агрегаций, пускай и в попугаях)

    ещё вижу сложности для Druid в случае «Использование внешних данных для обработки запроса», скормить файл на данном этапе в него нельзя.

    p.s. Tranquility не использовал, обычно realtime nodes из kafka вычитывали данные самостоятельно.

© Habrahabr.ru