Як запустити ClickHouse своїми силами і виграти джекпот

Ми вирішили описати простий і перевірений шлях для тих, хто хоче впровадити аналітичну СУБД 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 і похідних: UPDATE, INSERT та 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->підсумки());
// Отримати всі рядки у вигляді масиву 
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
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.