ClickHouse для аналитика: что это и как писать запросы

Коротко

• ClickHouse — колоночная СУБД для аналитических запросов: агрегатов по миллионам и миллиардам строк. Создана в Яндексе для Метрики, открыта в 2016 году.
• Скорость даёт хранение по столбцам, сжатие и сортировка данных на диске по ключу таблицы. Поэтому запрос читает только нужные столбцы и диапазоны строк.
• Обновлять и удалять отдельные строки в ClickHouse дорого, а транзакций на несколько таблиц в привычном виде нет. Это хранилище для чтения.
• SQL похож на PostgreSQL, но различается в деталях: в LEFT JOIN вместо NULL по умолчанию приходят нули и пустые строки, 7 / 2 = 3.5, дубли в некоторых таблицах исчезают только после фонового слияния.
• У ClickHouse много своих функций для аналитики: uniq, countIf, argMax, LIMIT BY. С ними запросы заметно короче.

Что такое ClickHouse

ClickHouse — система управления базами данных с открытым исходным кодом, рассчитанная на OLAP — аналитическую нагрузку. Типичный запрос к ней проходит по огромной таблице событий и считает агрегаты: сколько уникальных пользователей открыло приложение по дням, какая выручка по каналам за квартал. В рабочей базе продукта такой запрос мешал бы оплатам и регистрациям, а в ClickHouse он идёт секунды.

Систему разработали в Яндексе для Яндекс Метрики, где хранятся миллиарды визитов, и в 2016 году открыли под лицензией Apache 2.0. С 2021 года её развивает отдельная компания ClickHouse Inc. В России ClickHouse стоит в хранилищах многих продуктовых компаний и доступен как управляемый сервис в Yandex Cloud.

Для аналитика ClickHouse — чаще всего место, куда загружены события продукта, логи и витрины. Данные приходят туда из рабочих баз и трекеров через ETL, а дашборды в DataLens, Superset или Metabase читают их оттуда же.

Чем ClickHouse отличается от PostgreSQL

Обе системы понимают SQL, поэтому разницу легко недооценить. Она в том, под какую работу каждая оптимизирована. PostgreSQL бережно обслуживает тысячи коротких операций продукта и гарантирует транзакции. ClickHouse жертвует этим ради скорости больших агрегатов.

Как устроена таблица: MergeTree и ключ сортировки

Почти все рабочие таблицы ClickHouse используют движок из семейства MergeTree. Его поведение определяют два параметра. ORDER BY задаёт порядок строк на диске и одновременно служит первичным ключом. PARTITION BY делит таблицу на части, обычно по месяцам, чтобы запрос за сентябрь не читал остальные месяцы.

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

Новые данные пишутся отдельными кусками, и ClickHouse в фоне сливает их в более крупные. От этого зависит поведение дублей, о котором ниже. Блок создаёт таблицу событий в той же форме, что в учебной базе «Маяк». В песочнице курса его не выполнить: там DuckDB, у которого нет движков ClickHouse.

CREATE TABLE events
(
    event_id    UInt64,
    user_id     UInt64,
    event_time  DateTime,
    event_name  LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_name, event_time);

Первый запрос: активные пользователи по месяцам

Задача: по каждому месяцу посчитать уникальных активных пользователей, число событий и тех, кто хотя бы раз создал отчёт. В ClickHouse это делают функции uniqExact и uniqExactIf, а также count без аргумента. Окончание -If — комбинатор: оно добавляет к любой агрегатной функции условие. sumIf, countIf, avgIf работают так же.

Рядом тот же расчёт на переносимом SQL. Он выполняется в песочнице курса и в PostgreSQL и даёт одинаковые числа: 1042 активных в июне, 2688 в июле, 4109 в августе.

SELECT
    toStartOfMonth(event_time)                        AS month,
    uniqExact(user_id)                                AS active_users,
    count()                                           AS events,
    uniqExactIf(user_id, event_name = 'report_created') AS reporters
FROM events
GROUP BY month
ORDER BY month;

Тот же расчёт на переносимом SQL

FILTER (WHERE …) — стандартный способ задать условие агрегату. Он работает в PostgreSQL и DuckDB, а в ClickHouse его заменяет комбинатор -If. Если запрос живёт в нескольких системах, переносимую версию проще поддерживать.

select
  cast(date_trunc('month', event_time) as date) as month,
  count(distinct user_id) as active_users,
  count(*) as events,
  count(distinct user_id) filter (where event_name = 'report_created') as reporters
from events
group by 1
order by 1;

uniq или uniqExact: когда приближённого числа достаточно

В ClickHouse три способа посчитать уникальных пользователей. uniq считает приближённо: хранит компактную выборку вместо полного списка значений и потому работает быстрее и требует меньше памяти. uniqExact считает точно. count(DISTINCT user_id) по умолчанию выполняется как uniqExact.

Для графика DAU на дашборде расхождение в доли процента не играет роли, и uniq экономит время. Для числа, которое уйдёт в отчёт финансистам или в расчёт конверсии эксперимента, берите uniqExact: иначе две выгрузки одного дня могут разойтись на несколько пользователей, и вопросы будут к вам.

JOIN в ClickHouse: главная ловушка с NULL

Задача: сколько пользователей каждого канала ни разу не платили. В PostgreSQL её решают LEFT JOIN и проверкой p.user_id is null. В ClickHouse тот же запрос вернёт ноль во всех строках. Столбцы по умолчанию не допускают NULL, и при join_use_nulls = 0 — это значение по умолчанию — для строк без пары LEFT JOIN подставляет значение типа по умолчанию: 0 для чисел, пустую строку для текста, 1970-01-01 для дат.

Исправить можно тремя способами: проверять p.user_id = 0, если нулевого id в данных точно нет; включить SETTINGS join_use_nulls = 1, и тогда пропуски станут NULL; или считать через NOT IN по подзапросу. Ловушка коварна тем, что запрос не падает: просто доля «никогда не платили» становится нулевой, а средний чек по пользователям без оплат — нулевым вместо пустого.

Вторая особенность JOIN: правую таблицу ClickHouse по умолчанию загружает в память целиком. Ставьте справа меньшую таблицу или заранее агрегированный подзапрос, как в примере ниже.

SELECT
    u.channel,
    count()                  AS users,
    countIf(p.user_id = 0)   AS never_paid   -- countIf(p.user_id IS NULL) вернёт 0
FROM users AS u
LEFT JOIN (SELECT DISTINCT user_id FROM payments) AS p
    ON p.user_id = u.user_id
GROUP BY u.channel
ORDER BY users DESC;

Тот же JOIN на переносимом SQL

В PostgreSQL и DuckDB пропуск в LEFT JOIN — NULL, поэтому привычная проверка работает. Из 4613 пользователей учебной базы платили 912. Больше всего неплатящих в paid_search: 929 из 1015.

select
  u.channel,
  count(*) as users,
  count(*) filter (where p.user_id is null) as never_paid
from users as u
left join (select distinct user_id from payments) as p
  on p.user_id = u.user_id
group by u.channel
order by users desc;

Даты, деление и типы

Функции дат в ClickHouse свои, и их стоит держать под рукой. Тип DateTime хранит момент времени, а показывает его в часовом поясе сервера. Если сервер в UTC, а отчёт нужен по московскому времени, переведите явно: toDate(toTimeZone(event_time, 'Europe/Moscow')). Без этого события около полуночи окажутся в соседнем дне.

Деление / в ClickHouse всегда возвращает дробное число: 7 / 2 = 3.5. Целочисленное деление делает функция intDiv. Это удобнее, чем в PostgreSQL, где деление двух целых молча отбрасывает дробную часть. Но при переносе запроса из ClickHouse в PostgreSQL доли могут обнулиться: проверяйте деление при каждом переезде.

LIMIT BY и argMax: короче, чем окна

Задача «последние три события каждого пользователя» в PostgreSQL решается оконной функцией row_number и фильтром. В ClickHouse для неё есть LIMIT N BY: после сортировки он оставляет первые N строк для каждого значения выражения.

Похожая задача — «тариф последней оплаты каждого пользователя». В ClickHouse её решает argMax(plan, paid_at): функция возвращает значение plan из строки с максимальным paid_at. В PostgreSQL для этого нужен DISTINCT ON или окно.

SELECT user_id, event_time, event_name
FROM events
WHERE user_id IN (8, 9)
ORDER BY user_id, event_time DESC
LIMIT 3 BY user_id;

То же через оконную функцию

Переносимая версия длиннее, но работает везде, где есть оконные функции, включая сам ClickHouse. У обоих пользователей последние три события — открытия приложения в июле.

with ranked as (
  select
    user_id,
    event_time,
    event_name,
    row_number() over (partition by user_id order by event_time desc) as rn
  from events
  where user_id in (8, 9)
)
select user_id, event_time, event_name
from ranked
where rn <= 3
order by user_id, event_time desc;

Обновления и дубли: ReplacingMergeTree и FINAL

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

Проблема в слове «фоновом». Пока слияние не прошло, в таблице лежат обе версии, и запрос посчитает подписку дважды. Модификатор FINAL доводит схлопывание до конца прямо во время запроса. Он даёт правильный ответ ценой скорости. Альтернатива без FINAL — выбрать последнюю версию самому через argMax и GROUP BY по ключу.

CREATE TABLE subscriptions
(
    subscription_id UInt64,
    user_id         UInt64,
    status          LowCardinality(String),
    updated_at      DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY subscription_id;

-- Без FINAL подписка с двумя версиями до слияния посчитается дважды
SELECT status, count() AS subscriptions
FROM subscriptions FINAL
GROUP BY status;

Частые ошибки аналитика в ClickHouse

• SELECT * по большой таблице. Колоночная СУБД читает каждый перечисленный столбец, поэтому звёздочка — самый дорогой запрос. Перечисляйте только нужные поля.
• Фильтр не по ключу сортировки и не по партиции. Запрос без условия на дату проходит по всей истории.
• Проверка на NULL после LEFT JOIN. При настройках по умолчанию пропуск — ноль или пустая строка.
• uniq в финансовом отчёте. Приближённое число разойдётся с выгрузкой из другой системы.
• Подсчёт строк в ReplacingMergeTree без FINAL или argMax. Дубли до слияния завышают метрику.
• Большая таблица справа в JOIN. Правая часть грузится в память и может уронить запрос по лимиту памяти.
• Даты без часового пояса. День в отчёте сдвигается относительно продукта.

Где потренироваться

Логику запросов — группировки, соединения, окна, условия в агрегатах — удобнее всего отрабатывать на переносимом SQL. Она одинакова в ClickHouse и PostgreSQL, а различаются функции, которые легко найти в документации. Все переносимые запросы статьи выполняются в песочнице симулятора SQL-аналитика на той же учебной базе.

Для диалекта ClickHouse есть публичная песочница ClickHouse Playground с открытыми наборами данных, а для экспериментов на своих файлах — clickhouse-local: он выполняет SQL ClickHouse над CSV и Parquet без установки сервера.

Частые вопросы

Запустите переносимые запросы статьи в песочнице, а затем перепишите их на диалект ClickHouse в ClickHouse Playground: так разница станет видна на практике.