Я начала пользоваться ClickHouse до того, как это стало мэйнстримом: первый раз я столкнулась c этой базой данных лет 8 назад. C тех пор я уверена, что это лучшая DB для аналитики. Большинство аналитиков, которых я знаю, в восторге от ClickHouse (иногда чтобы проникнуться, требуется немного времени: разобраться и привыкнуть к синтаксису). Однако, я не могу не отметить, что администрирование ClickHouse имеет свои нюансы и подводные камни, но это уже совсем другая история.
В этой статье я расскажу что такое ClickHouse и почему я считаю его идеально подходящим мощным инструментом для аналитики. А также поделюсь tips & tricks из моего опыта. Поехали.
Что такое ClickHouse?
ClickHouse - это колоночная СУБД для OLAP (online-analytical processing). CH можно установить на свой компьютер / сервер и использовать локально. Если вы не хотите заморачиваться и менеджерить СУБД самостоятельно, то можно пойти простым путем и использовать недавно запущенный ClickHouse Cloud.
Наверняка вы задаетесь вопросом, что же значит название "ClickHouse". Имя состоит из двух слов: “Clickstream” и “Data wareHouse”. ClickHouse (или CH как его часто сокращают) был разработан в команде Яндекс.Метрики (системы web аналитики №3 в мире согласно W3Tech). И поэтому clickstream (поток событий с сайта) был основным use case, для которого ClickHouse был изобретен.
ClickHouse вышел в open-source в 2016 году под лицензией Apache 2.0, которая позволяет использовать его в коммерческих проектах. Сейчас огромное количество компаний по всему миру используют CH.
ClickHouse + аналитики = ????
Самое главное достоинство CH (особенно, для аналитиков) - это то, что он потрясающе быстрый. Изначально (до выхода на международный рынок) у DB был слоган "ClickHouse не тормозит". И это не просто слова: внутренние и внешние бенчмарки, и правда, показывают отличную производительность.
ClickHouse быстрый, потому что он разрабатывался и продолжает разрабатываться c ориентацией на скорость и производительность: колоночное хранилище, векторизация вычислений, индексы и компрессия данных помогают ему в этом. В документации можно найти больше деталей, если вам интересна архитектура.
И такая скорость это game-changer для аналитиков. Когда я только начинала работать, я в основном использовала MapReduce. MapReduce - это распространенная технология для работы с Big Data. MapReduce позволяет обработать действительно огромные объемы данных, но совсем не быстро. Обычно расчеты по месяцу данных занимали у меня 3-6 часов.
Мой день выглядел примерно так:
В большинстве случаев после первоначального анализа, возникали дополнительные вопросы (например, добавить разбивку не только по регионам, но и по платформам и операционным системам). Так что, следующий день я часто проводила, занимаясь той же самой задачей. Я привыкла к такому темпу работы и мне казалось, что это ок.
Но потом я узнала про ClickHousе, и моя рабочая рутина полностью перевернулась. C помощью CH можно получить результаты за минуты или даже секунды (а не часы), и поэтому нет необходимости ждать, ходить за кофе или переключать контекст. Так что, аналитики могут проверять больше гипотез и глубже погружаться в данные, поскольку работа становится интерактивной (даже если это терабайты или петабайты).
Еще одна приятная особенность CH - это его SQL диалект (я обычно называю его "SQL на стероидах"). В ClickHouse не было window functions примерно вечность: эта функциональность вышла из beta только в 2021 году (v21.9). Но это никогда особо не мешало мне считать сложные аналитические метрики, потому что в CH достаточно других удобных и мощных инструментов - нужно только про них знать.
Так что, пришло время перейти к лайфхакам.
Things I wish I knew
Разбираемся в структуре данных
В первую очередь полезно знать, какие данные вообще есть в DB. Для этого вы можете использовать запросы SHOW DATABASES и SHOW TABLES from <db>.
Но для того, чтобы писать эффективные запросы, нужно также понимать, как устроены эти данные. Для этого давайте посмотрим как была создана таблица.
SHOW CREATE TABLE raw_events;
CREATE TABLE default.raw_events
(
`profile_id` UInt64,
`action_date` DateTime,
`profile_country` String,
`event` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(action_date)
ORDER BY action_date, intHash32(profile_id)
SAMPLE BY intHash32(profile_id)
SETTINGS index_granularity = 8192В нашей таблице используется один из самых популярных движков - MergeTree. На самом деле, за этим движком стоит целое семейство MergeTree engines (они позволяют настроить репликацию, коллапсирование, агрегирование данных и т.д.), но мы использовали самую базовую версию.
Из запроса видно, что таблица сортирована по полям action_date и profile_id. И это важная информация: мы можем использовать эти колонки для фильтрации. Тогда БД будет считывать меньше информации с диска и запросы будут работать быстрее.
Приблизительные вычисления
Также описание говорит нам, что таблица поддерживает семплирование по profile_id. Семплирование - это killer feature для работы с Big Data.
Если вы работает с миллионами/миллиардами событий, вам необязательно использовать все данные для получения достаточно достоверных результатов. Вы можете использовать меньший семпл данных и сэкономить время и ресурсы. Также семплирования помогает с самой распространённым exception при работе с ClickHouse (по крайней мере по моему опыту) - Memory limit exceeded.
-- looking at all data
SELECT
uniqExact(profile_id) AS total_users,
count() AS total_events
FROM raw_events
┌─total_users─┬─total_events─┐
│ 103398 │ 3957261 │
└─────────────┴──────────────┘
1 row in set. Elapsed: 0.026 sec. Processed 3.96 million rows,
31.66 MB (150.92 million rows/s., 1.21 GB/s.)
-- sample only 1/5th
SELECT
5 * uniqExact(profile_id) AS total_users,
5 * count() AS total_events
FROM raw_events
SAMPLE 1 / 5
┌─total_users─┬─total_events─┐
│ 102735 │ 3789445 │
└─────────────┴──────────────┘
1 row in set. Elapsed: 0.010 sec. Processed 819.20 thousand rows,
6.55 MB (39.13 million rows/s., 313.06 MB/s.)Результаты в запросах немного отличаются: на 0.6% для числа пользователей (ключ семплирования) и на 4.2% для числа событий. Такая небольшая разница ожидаема, поскольку во втором случае мы считали метрики только по 20% данных.
Но также очевиден и выигрыш по производительности: второй запрос отработал в 2.6 раз быстрее и считал с диска в 5 раз меньше данных.
Nice to know: семплирование работает консистентно и вы будете получать одни и те же результаты при разных запусках. Это также значит, что если у вас есть 2 таблицы с одинаковым ключом семплирования, то вы можете их джойнить и будете получать валидные результаты.
Бывают случаи, когда вам все-таки нужно получить точное число (например, нельзя оперировать примерными вычислениями, если вы считаете сколько денег нужно выплатить партнерам). Но даже в этом случае вы можете использовать семплирование, чтобы обходить Memory limit exceeded exceptions. Лайфхак - использование SAMPLE 1/N OFFSET M/N. Вы можете запустить запрос несколько раз с разными offsets (M = 0, 1, …, N-1) и просуммировать результаты - так вы получите результат, эквивалентный полным данным.
Важная вещь, про которую не стоит забывать - это насколько ваша метрика аддитивна при разбивке по ключу семплирования. Например, в нашем случае семплирование не поможет посчитать число уникальных стран.
-- all data
SELECT
uniqExact(profile_id) AS total_users,
count() AS total_events,
uniqExact(profile_country) AS uniq_countries
FROM raw_events
┌─total_users─┬─total_events─┬─uniq_countries─┐
│ 103398 │ 3957261 │ 50 │
└─────────────┴──────────────┴────────────────┘
-- 1% sample
SELECT
100 * uniqExact(profile_id) AS total_users,
100 * count() AS total_events,
100 * uniqExact(profile_country) AS uniq_countries
FROM raw_events
SAMPLE 1 / 100 OFFSET 18 / 100
┌─total_users─┬─total_events─┬─uniq_countries─┐
│ 102800 │ 4465100 │ 3900 │
└─────────────┴──────────────┴────────────────┘В ClickHouse есть агрегатные функции, которые возвращают приблизительные значения, например, uniq vs uniqExact или quantile vs quantileExact. Они также помогают получить результаты быстрее и c использованием меньших ресурсов (например, памяти), но выдают приблизительный результат.
SELECT
uniqExact(profile_id) AS exact_total_users,
uniq(profile_id) AS approx_total_users,
round(100. * (approx_total_users - exact_total_users) / exact_total_users, 2) AS approx_diff
FROM raw_events
┌─exact_total_users─┬─approx_total_users─┬─approx_diff─┐
│ 103398 │ 103809 │ 0.4 │
└───────────────────┴────────────────────┴─────────────┘Расчеты totals и sub-totals
Частенько хочется посмотреть на метрики не только в разбивке по срезам, но и totals. И самый простой способ для этого - использовать WITH TOTALS при группировке.
SELECT
profile_country,
count() AS events,
round((100. * count()) / (
SELECT count()
FROM raw_events
), 2) AS events_share,
round(count() / uniqExact(profile_id), 2) AS events_per_user
FROM raw_events
GROUP BY profile_country
WITH TOTALS
ORDER BY events DESC
LIMIT 3
┌─profile_country─┬─events─┬─events_share─┬─events_per_user─┐
│ United Kingdom │ 538665 │ 34.19 │ 28.25 │
│ France │ 156320 │ 9.92 │ 25.85 │
│ Germany │ 123680 │ 7.85 │ 19.36 │
└─────────────────┴────────┴──────────────┴─────────────────┘
Totals:
┌─profile_country─┬──events─┬─events_share─┬─events_per_user─┐
│ │ 1575625 │ 100 │ 26.52 │
└─────────────────┴─────────┴──────────────┴─────────────────┘Можно использовать ROLLUP, чтобы посчитать sub-totals сразу по нескольким измерениям.
SELECT
profile_country,
toStartOfMonth(action_date) AS event_month,
count() AS events,
round((100. * count()) / (
SELECT count()
FROM raw_events
), 2) AS events_share,
round(count() / uniqExact(profile_id), 2) AS events_per_user
FROM raw_events
WHERE (profile_country IN ('United Kingdom', 'France', 'Germany'))
GROUP BY ROLLUP(profile_country, event_month)
┌─profile_country─┬──────month─┬─events─┬─events_share─┬─events_per_user─┐
│ France │ 2022-10-01 │ 47514 │ 3.02 │ 13.59 │
│ France │ 2022-11-01 │ 51150 │ 3.25 │ 13.54 │
│ France │ 2022-12-01 │ 57656 │ 3.66 │ 14.44 │
│ United Kingdom │ 2022-12-01 │ 200965 │ 12.75 │ 15.76 │
│ United Kingdom │ 2022-11-01 │ 172577 │ 10.95 │ 14.53 │
│ Germany │ 2022-10-01 │ 39540 │ 2.51 │ 10.51 │
│ United Kingdom │ 2022-10-01 │ 165123 │ 10.48 │ 14.7 │
│ Germany │ 2022-11-01 │ 40592 │ 2.58 │ 10.42 │
│ Germany │ 2022-12-01 │ 43548 │ 2.76 │ 10.32 │
└─────────────────┴────────────┴────────┴──────────────┴─────────────────┘
┌─profile_country─┬──────month─┬─events─┬─events_share─┬─events_per_user─┐
│ United Kingdom │ 1970-01-01 │ 538665 │ 34.19 │ 28.25 │
│ Germany │ 1970-01-01 │ 123680 │ 7.85 │ 19.36 │
│ France │ 1970-01-01 │ 156320 │ 9.92 │ 25.85 │
└─────────────────┴────────────┴────────┴──────────────┴─────────────────┘
┌─profile_country─┬──────month─┬─events─┬─events_share─┬─events_per_user─┐
│ │ 1970-01-01 │ 818665 │ 51.96 │ 25.98 │
└─────────────────┴────────────┴────────┴──────────────┴─────────────────┘Функции argMin & argMax
Честно говоря, именно по этим функциям я больше всего скучаю, когда приходится пользоваться классическим SQL. В аналитике нам часто нужно определить значение какого-то аргумента при минимальном/максимальном другом значении, например, посчитать для каждого пользователя самый частотный браузер или какой был источник трафика для первого события. В классическом SQL для этого нужно делать join'ы или подзапросы, но в ClickHouse все гораздо проще - есть агрегатные функции argMin и argMax.
Давайте посчитаем месяцы с минимальной и максимальной активность пользователей (будем измерять ее по числу транзакций на пользователя) в разбивке по странам.
SELECT
profile_country,
max(events_per_user) AS max_epu,
min(events_per_user) AS min_epu,
argMax(event_month, events_per_user) AS month_max_epu,
argMin(event_month, events_per_user) AS month_min_epu
FROM
(
SELECT
profile_country,
toStartOfMonth(action_date) AS event_month,
round(count() / uniqExact(profile_id), 2) AS events_per_user
FROM raw_events
WHERE profile_country IN ('United Kingdom', 'Germany', 'France')
GROUP BY
profile_country,
event_month
)
GROUP BY profile_country
┌─profile_country─┬─max_epu─┬─min_epu─┬─month_max_epu─┬─month_min_epu─┐
│ France │ 14.44 │ 3.06 │ 2022-12-01 │ 2022-01-01 │
│ Germany │ 10.55 │ 2.37 │ 2022-10-01 │ 2022-01-01 │
│ United Kingdom │ 15.76 │ 3.19 │ 2022-12-01 │ 2022-01-01 │
└─────────────────┴─────────┴─────────┴───────────────┴───────────────┘Множественные if-statements
В классическом SQL есть CASE clause. В ClickHouse, для этого можно использовать функцию multiIf, избегая большого числа вложенных if-функций.
SELECT
multiIf(
num_events = 1, '1',
num_events < 5, '2 - 4',
num_events < 10, '5 - 9',
num_events < 100, '10 - 99',
'100+'
) AS num_events_group,
count() AS users,
round((100. * users) / 10050, 2) AS users_share
-- hard-coded total value instead of subquery - ok for adhoc requests
FROM
(
SELECT
profile_id,
count(1) AS num_events
FROM raw_events
GROUP BY profile_id
)
GROUP BY num_events_group
WITH TOTALS
ORDER BY users DESC
┌─num_events_group─┬─users─┬─users_share─┐
│ 2 - 4 │ 3250 │ 32.34 │
│ 1 │ 2951 │ 29.36 │
│ 10 - 99 │ 2309 │ 22.98 │
│ 5 - 9 │ 1277 │ 12.71 │
│ 100+ │ 263 │ 2.62 │
└──────────────────┴───────┴─────────────┘
Totals:
┌─num_events_group─┬──users─┬─users_share─┐
│ │ 10050 │ 100 │
└──────────────────┴────────┴─────────────┘Combinators для агрегатных функций
В СlickHouse довольно много combinators (или modifiers) для агрегатных функций, которые делают их еще более гибким инструментом. Это очень классный инструмент, но к сожалению, его не так просто нагуглить в документации.
Я чаще всего пользуюсь -If combinator. Он может быть применен к любой аггрегатной функции. Этот комбинатор добавляет еще один аргумент типа boolean в конец списка. И затем при расчете значения агрегатной функции будут учитываться только те строки, которые удовлетворяют этому boolean условию.
Для примера, давайте посчитаем stickiness как отношение числа пользователей / событий за последний месяц к данным за весь год в разбивке по странам.
SELECT
profile_country,
count() AS total_events,
countIf(toStartOfMonth(action_date) = '2022-12-01') AS last_month_events,
uniqExact(profile_id) AS total_users,
uniqExactIf(profile_id,
toStartOfMonth(action_date) = '2022-12-01') AS last_month_users,
round((100. * last_month_events) / total_events, 2) AS last_month_events_share,
round((100. * last_month_users) / total_users, 2) AS last_month_users_share
FROM raw_events
GROUP BY profile_country
ORDER BY total_events DESC
┌─profile_country─┬─total_events─┬─last_month_events─┬─total_users─┬─last_month_users─┬─last_month_events_share─┬─last_month_users_share─┐
│ United Kingdom │ 1188049 │ 200965 │ 31598 │ 12753 │ 16.92 │ 40.36 │
│ France │ 326987 │ 57656 │ 9928 │ 3992 │ 17.63 │ 40.21 │
│ Germany │ 276840 │ 43548 │ 10428 │ 4219 │ 15.73 │ 40.46 │
└─────────────────┴──────────────┴───────────────────┴─────────────┴──────────────────┴─────────────────────────┴────────────────────────┘Вы можете найти больше combinators в документации. Иногда мне пригождаются следующие: -ForEach, -Array, -Distinct или -OrDefault.
Системные таблицы
В ClickHouse есть системные таблицы со внутренними или meta-данными, которые иногда бывают полезны.
Вот так можно посмотреть список всех системных таблиц.
SHOW TABLES FROM systemВы можете использовать таблицу system.settings, чтобы понять какие настройки и лимиты существуют и какие значения выставлены для вашего пользователя. Например, если вы встретились с Max memory limit exceeded exception, с помощью запроса ниже можно найти все лимиты на память, которые есть в системе.
В случае Max memory limit exceeded exception, скорее всего, вам нужны параметры max_memory_usage или max_memory_usage_for_user. Вы можете изменить выставленное значение в запросе, если у вашего пользователя есть права на это (параметр readonly равен в 0 или 2).
SELECT
name,
value,
description
FROM system.settings
WHERE name LIKE '%memory%'
LIMIT 10
┌─name───────────────────────────────────────────────┬─value──────┬─description────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ distributed_aggregation_memory_efficient │ 1 │ Is the memory-saving mode of distributed aggregation enabled. │
│ aggregation_memory_efficient_merge_threads │ 0 │ Number of threads to use for merge intermediate aggregation results in memory efficient mode. When bigger, then more memory is consumed. 0 means - same as 'max_threads'. │
│ enable_memory_bound_merging_of_aggregation_results │ 0 │ Enable memory bound merging strategy for aggregation. Set it to true only if all nodes of your clusters have versions >= 22.12. │
│ memory_tracker_fault_probability │ 0 │ For testing of `exception safety` - throw an exception every time you allocate memory with the specified probability. │
│ remerge_sort_lowered_memory_bytes_ratio │ 2 │ If memory usage after remerge does not reduced by this ratio, remerge will be disabled. │
│ max_memory_usage │ 0 │ Maximum memory usage for processing of single query. Zero means unlimited. │
│ memory_overcommit_ratio_denominator │ 1073741824 │ It represents soft memory limit on the user level. This value is used to compute query overcommit ratio. │
│ max_memory_usage_for_user │ 0 │ Maximum memory usage for processing all concurrently running queries for the user. Zero means unlimited. │
│ memory_overcommit_ratio_denominator_for_user │ 1073741824 │ It represents soft memory limit on the global level. This value is used to compute query overcommit ratio. │
└────────────────────────────────────────────────────┴────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Еще одна полезная таблица - system.functions. В ней содержатся все функции, которые имплементированы в базе данных. Это поможет вам вспомнить точное название функции (я все время их забываю) или узнать что-то новое. Для примера, найдем функции для сортировки массивов.
SELECT *
FROM system.functions
WHERE lower(name) LIKE '%sort%'
┌─name─────────────┬─is_aggregate─┬─case_insensitive─┬─alias_to─┬─create_query─┬─origin─┬─description─┐
│ arrayReverseSort │ 0 │ 0 │ │ │ System │ │
│ arraySort │ 0 │ 0 │ │ │ System │ │
└──────────────────┴──────────────┴──────────────────┴──────────┴──────────────┴────────┴─────────────┘And one more thing
Для тех, кто как и я, предпочитает пользоваться консольным клиентом, может быть полезен следующий лайфхак: добавление modifier -m позволит вам писать многострочные запросы и это гораздо удобнее.
./clickhouse client -mЕсли вы предпочитаете для получения данных использовать python и ClickHouse HTTP API, то полезно указывать формат FORMAT TabSeparatedWithNames в конце запросов, что сильно облегчает дальнейший парсинг результатов.
Пока что на этом все
Конечно, в ClickHouse есть еще много классных и удобных функций, но про них в следующий раз. Например,
arrays (функции для работы с массивами как раз позволяют избегать window functions)
dictionaries
применение ML моделей в CH
расчеты funnels
dkirienko
Что, теперь даже кофе нельзя выпить на работе? Точно нужен такой инструмент?