Я начала пользоваться 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
ссылка на оригинал статьи https://habr.com/ru/articles/743772/
Добавить комментарий