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

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

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

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

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

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

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

    для работы с БД для удобной работы с БД, реализующая возможности 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 со следующей структурой:

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

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

  • 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Стоит отметить, что работа с 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’) .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Что дальше

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

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

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

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

📎📎📎📎📎📎📎📎📎📎