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


В этом году мы развернули сборку на основе DruidImply 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.


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


<?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
Поделиться с друзьями
-->

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


  1. xhumanoid
    07.11.2016 09:37

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

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

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


    1. sublimity
      07.11.2016 14:28
      +1

      Как только мы приготовились запускать Druid в продакшн, мы успели накопить в нём данные всего лишь за месяц. Druid показал хорошую производительность на данных с глубиной в несколько дней. При запросах большей глубины были заметные тормоза.

      Как только мы увидели ClickHouse в открытом доступе, мы удалили с серверов Druid и поставили ClickHouse. В ClickHouse данные у нас храняться больше 3,5 месяцев.

      Прямого сравнения у нас не было, поэтому в статье мы не приводим бенчмарки.

      Druid много чего не поддерживает по сравнению с ClickHouse. Мы хотели/мечтали о базе с «SQL-подобными запросами». Когда мы анализировали Druid, он не понравился нам запросами в «своем формате». Писать SQL-запросы приятнее и проще, чем огромные «массивы» в postAggregation, hyperUniqueCardinality и т. д.


      1. xhumanoid
        07.11.2016 17:53

        тогда еще один вопрос.

        С druid масштабирование понятно (докинули серверов в кластер, достаточно указать zookeeper-hadoop, там открылись сегменты новых-ребаланс старых)

        С ClickHouse на этапе знакомства я не нашел как по быстрому можно расширить кластер.

        Как вы собираетесь увеличивать емкость ClickHouse когда увидите, что нагрузка увеличилась x2 и нужно докинуть серверов?


        1. sublimity
          08.11.2016 12:30

          В общем случае добавление новых узлов в кластер требует либо тяжелой операции перешардирования (с равномерным перераспределением данных), либо использования механизма весов, когда данные пишутся более активно на новые сервера с более высоким весом, и когда объём данных на новых и старых узлах становится примерно равный, веса выравниваются.


          У каждого из подходов есть плюсы и минусы:


          Перешардирование даёт лучшее распределение данных в кластере и при выполнении запросов узлы кластера будут нагружены примерно одинаково. Минус перешардирования — кроме того, что это очень тяжелая операция, возможно, потребует остановки кластера. Если СУБД, к примеру, является подсистемой, то остановка кластера может быть недопустима.


          Использование весов даёт неравномерное распределение, зато позволяет быстро добавлять узлы без создания дополнительной нагрузки на кластер.


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


          Рекомендую:
          Перешардирование
          bug on groups


    1. leventov
      08.11.2016 07:35

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

      http://druid.io/docs/latest/querying/lookups.html не подходит?


      1. xhumanoid
        08.11.2016 11:32

        нет, в примере в статье говориться «использовать ids из вот этого файла»
        lookup в druid это «я хочу чтобы ты вместо id вернул мне строковое значение из этого словаря» и происходит на этапе уже возврата данных клиенту


        1. leventov
          08.11.2016 21:29

          Вы пробовали пожаловаться здесь: https://groups.google.com/forum/#!forum/druid-user? Возможно есть решение, про которое я не знаю. Или разработчики услышат частые запросы и добавят такую фичу.


          1. xhumanoid
            08.11.2016 22:48

            нет, там даже большой массив на вхождение будет неплохо снижать производительность. поэтому обычно все укладывается в бакеты (1: 1-10, 2: 11-20 и тд)

            отдельно стоит уточнить, что все ключи для dimension когда bitmap строят в string переводят, поэтому использовать float/double как измерение очень не советую (много место сожрет на хранение, а выборку по диапазону все равно не получите)

            В общем для некоторых вещей в лоб он очень неплох, особенно с учетом гибкого масштабирования, но вот модель «как и зачем хранить» как и с любым nosql нужно продумывать досконально


            1. leventov
              08.11.2016 23:04

              Не строковые dimensions пилят.


            1. leventov
              08.11.2016 23:07

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


              1. xhumanoid
                08.11.2016 23:43

                >> Не строковые dimensions пилят.

                это пилили уже пол года назад =) все еще пилят

                >> Возможно, потому что никто не сделал эффективный способ, потому что никто не просил

                у druid в общем случае это обычный отсортированный словарь ключей, по ключу находят строку в bitmap и дальше бегут по ней находя нужные rowid (условно конечно). когда у нас имеется несколько значений в IN, то нельзя просто сканировать один ряд, нужно пачку сразу и результаты результаты клеить.

                дальше учитываем что основной протокол общения это rest/json, соответсвенно и передать большой кусок сложно само по себе.

                кстати уже ловил проблему, когда несколько historical возвращают большие ответы и 90% времени запроса проводим в broker занимаясь «распарсили ответ от historical — merge результатов — генерация json клиенту»

                p.s. это не наезд, из opensource для некоторых задач, особенно в условиях когда нужна доступность и возможность гибко расширять кластер, druid подходит хорошо и в новом проекте собираюсь опять его использовать


                1. leventov
                  09.11.2016 00:20

                  у druid в общем случае это обычный отсортированный словарь ключей, по ключу находят строку в bitmap и дальше бегут по ней находя нужные rowid (условно конечно). когда у нас имеется несколько значений в IN, то нельзя просто сканировать один ряд, нужно пачку сразу и результаты результаты клеить.

                  Опять не понял, но сейчас в InFilter просто запрос в Set, скорее всего HashSet: https://github.com/druid-io/druid/blob/d981a2aa02aedfdf18e0bed5848e704bd9597563/processing/src/main/java/io/druid/segment/filter/InFilter.java#L110


                  дальше учитываем что основной протокол общения это rest/json, соответсвенно и передать большой кусок сложно само по себе.

                  Сейчас да, но запилить "особый" вариант In, который идет за массивом куда-нибудь в сеть или на диск и кеширует этот массив на query ноде ничего не мешает, было бы желание у разработчиков, а желание появляется если появляются клиенты.


                  кстати уже ловил проблему, когда несколько historical возвращают большие ответы и 90% времени запроса проводим в broker занимаясь «распарсили ответ от historical — merge результатов — генерация json клиенту»

                  А можно тут подробнее? Это какой тип запроса?


                  1. xhumanoid
                    09.11.2016 00:39

                    >> Опять не понял, но сейчас в InFilter просто запрос в Set, скорее всего HashSet

                    посыпаю голову пеплом, уже пофиксили (к версии 0.9.2) =) но это было всего 2 марта, если откроете фикс который он делал, то там хорошо написано: Currently, InDimFilter is translated to «or + selector filters». Value matcher can use hash set for faster filtering.

                          List<DimFilter> fields = Lists.<DimFilter>newArrayList(new SelectorDimFilter(dimensionName, value));
                           for (String val : values) {
                             fields.add(new SelectorDimFilter(dimensionName, val));
                           }
                           dimFilter = new OrDimFilter(fields);
                    


                    Or в свою очередь доходит до OrFilter

                    Обратите внимание на ValueMatcher: он последовательно применяет для каждого selector проверку пока кто-то не сработает. На больших объемах в IN это было достаточно плохо.

                    В принципе сейчас действительно уже проще, когда смотрел плотно эту часть, там с интерфейсами конкретно под эту задачу было сложнее. Сейчас же просто проверку вместо Set на что-то еще сделать не проблема, а если добавить какой bloom для больших наборов так вообще может оказаться очень хорошо.

                    >> А можно тут подробнее? Это какой тип запроса?

                    вот так сразу не вспомню точно условия, но насколько помню было «granularity»: «all» и порядочно сегментов.


  1. yusman
    07.11.2016 11:25

    Из платных баз данных мы тестировали HP Vertica и Greenplum

    Эти СУБД из другого сегмента. Если clickHouse для real-time аналитики(грузим и анализируем одновременно) то эти для такого использования: «загрузили данные, закомитились, проанализировали когда захотели».

    Кстати, из приведенных вами запросов вообще не увидел «риалтаймовости» — для исторического анализа real-time особо не нужен.


    1. fuCtor
      07.11.2016 13:45

      Что считать real-time наверно, для исторического анализа каждую секунду на больших объемах, это мне кажется real-time. Т.к. даже сами разработчики рекомендуют делать редкие, но большие запросы, а не единичные и частые.


    1. sublimity
      07.11.2016 14:30

      Под realtime мы подразумевали, что данные достаточно быстро доступны для чтения, по сравнению со стеком Hadoop.

      В видеороликах продемонстрировано, насколько быстро ClickHouse анализирует исторические данные. Демонстировать запросы данных за последние 5-10 минут тяжело, т. к. их выполнение занимает сотые доли секунды.

      Как пример, для чего мы используем запросы, близкие к realtime, могу привести ранжирование статей. Допустим, 1 минутут назад статью просмотрело 100 человек, в промежутке между 2-й и 3-й минутами от текущего времени — 120 человек. Эти данные используются нами для ранжирования статей. У нас порядка 25 тысяч статей. По каждой из них нужно получать состояние по каждой минуте и на основе этой информации ранжировать статьи.


      1. yusman
        07.11.2016 14:41
        -1

        Без обид, но это не real-time аналитика, а просто быстрое выполнение запросов


        1. Aivean
          08.11.2016 00:28
          +1

          А что ты считаешь real-time аналитикой? Например, почему кафка+вертика не может быть real-time?


  1. RusMikle
    07.11.2016 13:01

    Спасибо, было интересно.

    один вопрос, а как выглядит ожидание данных из асинхронного запроса?

    С нетерпением жду продолжениея.

    Спасибо


    1. sublimity
      07.11.2016 16:05

      Спасибо за позитивный комментарий.
      Внутри драйвера мы чуда не сотворили.
      Используется curl_multi.
      Функция execLoopWait ждет в цикле — через проверку состояния curl_multi_exec.
      См. https://github.com/smi2/phpClickHouse/blob/master/src/Transport/CurlerRolling.php#L159


  1. vadamlyuk
    08.11.2016 00:36

    А у меня такой вопрос.
    Вот к примеру user_uuid проверяется как-то на валидность?

    Если да, то делается это средствами ClickHouse или как-то на стороне?
    И если это делается через ClickHouse достаточно быстр он для этого, чтоб делать проверку «в лоб»

    Или лучше для этого, все равно, организовать какую-нибудь очередь, в которую кидать непроверенные данные,
    а проверку уже осуществлять на другом конце очереди?


  1. sublimity
    08.11.2016 01:08

    Самим средствами CH — проверить при вставке валидность uuid думаю не правильно, это просто строка, без типизации.


    Можно отдельно отправить запрос и узнать сколько «плохих» строк, которые не соответствуют длине, не содержат ‘-’ и т/д.


    Лучший кейс — это проверка при сохранении «события» в файл, т/е на стороне «писателя» данных.


    Если «писатель» получил пустой uuid или не валидный — то можно проставить новый рандомный uuid, пометив что он искусственный, через отдельную колонку в таблице типа is_true_uuis = [ 0 | 1 ].


    Теоретически, можно сделать еще так, через новое поле check_uuis DEFAULT (length(uuid)=61 ? 1 : 0 ), но думаю это тоже плохой кейс.


  1. alexeyshockov
    08.11.2016 20:20

    Большое спасибо за статью и за PHP-библиотеку.

    Вопрос касательно последней: почему таки не стали использовать один из готовых HTTP-клиентов? Я видел ремарку об этом в статье, но причины не описаны.

    Объясню, почему я считаю, что лучше взять готовый:
    — Guzzle или HTTPlug (или подобные) поддерживают promises для асинхронных запросов. Этот подход удобнее, чем Ваш, и, в частности, может быть интегрирован с event loop (тот же React). Ваш клиент я не могу интегрировать с event loop, мне придётся блокироваться для выполнения пачки запросов в любом случае.
    — PSR-7 помог бы навешивать всякие middleware на HTTP клиента для библиотеки. К примеру, сейчас мне нужно лезть в кишки, чтобы навесить хитрое журналирование HTTP-запросов, а так бы я просто передал HTTP-клиента, обвешанного моим middleware, при создании клиента и имел бы успех.


    1. sublimity
      08.11.2016 23:33

      Я надеялся избежать этого вопроса )


      • Желание изучить максимально предметную область — все “фичи” CH на самом низком уровне, чтобы реализовать нужный нам ф-ционал, асинхронная отправка с сжатием потока.
      • В нашем приложении под HHVM, использую только максимально легковесный код, в котором можно быть уверенным что все работает так как нужно.
      • Драйвер мы не используем под большой нагрузкой, т/к не представляю у нас такой кейс.

      Есть альтернативный драйвер на Guzzle, но в нем не реализован функционал которые хотелось и выглядит он заброшенным.
      HTTPlug показался неподходящим, т/к не нашел в нем реализации curl_multi_exec, (возможно плохо искал).
      Реализация показалась тяжелой в Guzzle, хотя в нем отлично реализован GuzzleHttp\Handler\CurlMultiHandler


      Примеры странного специфичного кода



      1. alexeyshockov
        09.11.2016 00:47

        Вас понял, всё логично. Но я таки посклоняю :)

        HTTPlug действительно не в кассу, если хочется таких деталей — он больше для простых/средних запросов в публичных библиотеках. Смысл: это проект-обёртка, по сути, чтобы пользователю было удобно подсунуть туда свою текущий HTTP-клиент, который он использует в проекте (Guzzle5, Guzzle6, React HTTP,..). Это удобно для Facebook API SDK, к примеру, чтобы не навязывать в проект Guzzle, к примеру (но и в то же время не писать очередную обёртку над cURL).

        Я бы всё таки рекомендовал посмотреть на Guzzle и попробовал на нём запросы. Ошибки на HHVM связаны в основном с генераторами, а вы их и так не используете. С точки зрения интерфейса там всё довольно просто, как по мне, а производительность нужно измерять, с ней других вариантов нет :) Мне кажется, что Guzzle будет не сильно медленнее, учитывая всё остальные плюсы.

        Странные примеры реализуются передачей CURLOPT_FILE & CURLOPT_INFILE в объект запроса через опции. Guzzle не имеет поддержки этих функций в своём интерфейсе, но всегда позволяет передать что-то cURL-у «напрямую».

        PR не обещаю, т.к. не работаю пока с ClickHouse сам, а переделка глобальная :) Но в деталях помочь могу.

        Ещё раз спасибо за проект!


  1. Serj_By
    08.11.2016 23:41
    +1

    Возможно глупый вопрос, но все же — почему настолько важно производить ранжирование статей каждую минуту? Неужели от этого так сильно зависит объем читающей аудитории и статьи настолько быстро устаревают? Извиняюсь, если вопрос реально глупый, просто в СМИ не работал, поэтому не в теме.


    1. sublimity
      09.11.2016 10:25

      Вопрос хороший, на самом деле в этом отличие СМИ2 от рекламных сетей, у нас высокое требование по доставки свежего контента пользователю – и вообще, и особенно если происходит что-то что чрезвычайно важное (как выборы в Америке сегодня).
      Тогда об этом начинают писать все СМИ, и чем быстрее наша система заметит свежую и важную новость по теме, тем лучше. А новости могут устаревать даже спустя полчаса (если появился апдейт, сообщающий о новых подробностях или изменении суть происходящего)


      Поэтому сейчас мы ранжируем за 5 секунд весь набор активных новостей, плюс-минус 30 тысяч, и пользователь получает каждую минуту свежий набор новостей. Безусловно, это не значит, что обновляются вообще ВСЕ новости – речь о том, что мы стремимся показывать актуальный набор на конкретный момент времени.


  1. l0rda
    09.11.2016 05:19

    Кто-нибудь из достопочтенных донов в курсе, есть ли какие-то дашборды для работы с CH? Очень хочется красивые графики. Может кто-то запилит плагин для grafana?;)


    1. sublimity
      09.11.2016 15:03

      Готовых продуктов/дашбордов мы не знаем, частично поэтому мы реализовали свой GUI для редактора SQL. В котором планируется реализовать отрисовку графиков ( визуализацию данных) после создания запроса.


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


      Для Graphite, в CH предусмотрен отдельный сторедж, который хранит и “сворачивает” данные в виде Graphite схемы данных. GraphiteRollupSorted не документирован, и опыта у нас работы с ним пока нет.


  1. timurkar
    09.11.2016 13:29

    Спасибо, отличная статья. Вопрос по вот этой ремарке

    Для нас не важна транзакционность и отсутствие UPDATE / DELETE. Мы давно привыкли обходить эти проблемы


    К примеру, мы всю аналитику делаем в пределах пользовательских сессий (вешается кука, потом в пределах нее что-то делается). Сессия бывает авторизованной или нет, но иногда происходят события, которые меняют сессию (к примеру сессия авторизовалась, либо зарегистрировалась), т.е. происходит update

    Потом данные периодически выгружаются в ElasticSearch к которому уже идут аналитические запросы вида
    «какие из авторизованных юзеров с сессией > 5 минут посещали такую-то страницу»

    Т.е. там используется изменение ранее вставленной сессии (к примеру user_id прописывается). Как вы подобные задачи решаете? я так понимаю — вы бы в этом случае делали отдельную таблицу на авторизацию и join на нее?


    1. sublimity
      09.11.2016 14:55

      Есть много способов решения задачи. Основная идея в том, что лучше хранить события в одной большой таблице и не использовать JOIN по большим данным. Или дублировать данные при вставке в несколько таблиц, в терминологии статьи это значит "писать в несколько TSV-файлов".


      Также есть вариант, когда вы пишете RAW событий в одну большую таблицу (назовем ее "поток"), к которой прикреплены MaterializedView. Эти MaterializedView будут раскладывать RAW-поток на нужные составляющие.


      Возможно, лучше изменить понятие uuid, и ввести понятие session_id. Сессия меняется, а uuid нет, если это возможно.


      Если без uuid, то тогда можно сделать еще такие варианты:


      • Использовать две колонки uuid: например first_uuid и main_uuid. При изменении uuid вы пишете в разные поля, как изменилась сессия.
      • Использовать отдельное поле, массив сессий.
      • Использовать движок CollapsingMergeTree.
      • Ввести единый uuid и добавить признак типа enum|int — is_login.

      Я бы посоветовал написать прототип по одному из вариантов или сразу несколько вариантов и начать делать запросы SELECT. Придумать несколько штук, которые самые популярные у вас. И исходя из запросов на чтение понять оптимальную структуру.


      В статье мы привели самый простой пример, для упрощения. На самом деле мы используем практически все из перечисленных выше методов. И меняли структуру данных под CH.


      1. timurkar
        09.11.2016 17:31

        Спасибо за подробный ответ
        Да, мы как раз денормализуем в одну большую таблицу чтобы не использовать JOIN. Но как раз из-за этого там иногда возникает потребность в обновлении. Предложенные варианты решения — да, их все можно делать, потребность в них возникает как раз из-за отсутствия UPDATE/DELETE
        Хотя конечно описанные плюсы перекрывают этот минус