ClickHouse: полезные лайфхаки

в 15:57, , рубрики: analytics, big data, BigData, clickhouse, data engineering, open source, opensourse, sql

Я начала пользоваться 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

Одна из моих многочисленных од 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://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js