Материализованные представления и ReplacingMergeTree в ClickHouse (ч2)

В первой части я прошелся по основным понятиям по работе с материализованным представлением и ReplacingMergeTree в ClickHouse. Разобрал особенности, основные преимущества и недостатки. В этой части я покажу как это работает вместе.

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

Таблица-источник (максимальное время ответа по-секундно):

CREATE TABLE responses_time (     time DateTime,     app String,     max_time UInt16 ) engine=ReplacingMergeTree() ORDER BY (time, app)

Материализованное представление:

CREATE MATERIALIZED VIEW responses_time_by_day_mat_view TO responses_time_by_day (     day DateTime,     app String,     avg_time UInt16 ) AS SELECT     time AS day,     app,     avg(max_time) as avg_time FROM responses_time GROUP BY     toDateTime((intDiv(toUInt32(time), 86400) * 86400)) as time,     app

Но сразу можно увидеть, что работать это будет только если вставлять данные одной пачкой, которая будет включать в себя целый день, что скорее всего невозможно в реальности. Напомню, что данные будут браться для агрегации не из всей таблицы, а только те, что пришли в текущем INSERT. Я специально привел этот пример, который не подходит под цель статьи, но он отражает, что далеко не всегда можно средствами БД решить все задачи. В данном случае проще всего пойти следующим путем:

• Создать отдельную агрегированную таблицу:

CREATE TABLE responses_time_by_day (     day DateTime,     app String,     avg_time UInt16 ) engine=MergeTree() ORDER BY (day, app)

• Раз в день (допустим в 00:00) запускать запрос:

SELECT     toDateTime((intDiv(toUInt32(time), 86400) * 86400)) AS day,     app,     avg(max_time) as avg_time FROM responses_time WHERE time > yesterday() GROUP BY app

• Результаты вставлять в агрегат.

А теперь более подходящий пример. Представим, что собираем не время ответа, а статусы. Данные по суммарному количеству каждого статуса для каждого приложения необходимо показывать с агрегацией по дням/неделям/месяцам за последние полгода/год. При сотнях или тысячах приложений таблица заполнится очень быстро, поэтому снова требуется агрегация и хочется чтобы она работала автоматически без лишних приседаний. И важное условие: данные попадают в таблицу по крону, который берет какой-то последний промежуток времени из другой системы и перекладывает данные в ClickHouse (суммарно по-секундно и это уже околореальный пример из жизни). Но в этом подходе сразу возникают нюансы, ведь что-то может пойти не так. Например, прошлая итерация упала и образовалась дыра в метриках. Причем для разных приложений последнее сохраненное время оказалось тоже разным. Чтобы об этом всем не беспокоиться можно использовать ReplacingMergeTree для таблицы источника и период синка брать побольше. Но самое интересное это что тогда мы увидим в агрегате, если данные в таблице-источнике повторились? В части первой я описывал, что ClickHouse не сразу удаляет дубликаты.

Итак, таблица-источник:

CREATE TABLE responses_status (     time DateTime,     app String,     status UInt16,     count UInt32 ) engine=ReplacingMergeTree() ORDER BY (time, app, status)

Агрегированная таблица:

CREATE TABLE responses_status_by_min (     time DateTime,     app String,     status UInt16,     count UInt32 ) engine=SummingMergeTree() ORDER BY (time, app, status) 

Здесь уже использован движок SummingMergeTree. Он отлично подходит для текущей задачи так как:

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

То есть будет суммировать поле count и уже не важно в одном запросе это было вставлено или нет.

Ниже показано само мат представление, которое суммирует данные поминутно (это уменьшает размер данных для построения отчетов в десятки и сотни раз).

CREATE MATERIALIZED VIEW responses_status_mat_view TO responses_status_by_min AS SELECT      toDateTime((intDiv(toUInt32(time), 60) * 60)) as time,     app,     status,     sum(count) as count FROM responses_status GROUP BY time, app, status

Вставка данных:

INSERT INTO responses_status (time,app,status,count) VALUES ('2021-10-11 12:00:00', 'search', 200, 3), ('2021-10-11 12:00:00', 'search', 500, 1), ('2021-10-11 12:00:01', 'search', 200, 3), ('2021-10-11 12:00:01', 'search', 500, 2), ('2021-10-11 12:00:00', 'api', 200, 1), ('2021-10-11 12:00:00', 'api', 500, 5), ('2021-10-11 12:00:01', 'api', 200, 1), ('2021-10-11 12:00:01', 'api', 500, 1)

Результат:

SELECT * FROM responses_status_by_min  ┌────────────────time─┬─app────┬─status─┬─count─┐ │ 2021-10-11 12:00:00 │ api    │    200 │     2 │ │ 2021-10-11 12:00:00 │ api    │    500 │     6 │ │ 2021-10-11 12:00:00 │ search │    200 │     6 │ │ 2021-10-11 12:00:00 │ search │    500 │     3 │ └─────────────────────┴────────┴────────┴───────┘

Все прекрасно, данные просуммировались с округлением до минуты. Добавляем еще немного данных вторым запросом:

INSERT INTO responses_status (time,app,status,count) VALUES ('2021-10-11 12:00:02', 'search', 200, 3), ('2021-10-11 12:00:02', 'search', 500, 1), ('2021-10-11 12:00:03', 'search', 200, 3), ('2021-10-11 12:00:03', 'search', 500, 2), ('2021-10-11 12:00:02', 'api', 200, 1), ('2021-10-11 12:00:02', 'api', 500, 5), ('2021-10-11 12:00:03', 'api', 200, 1), ('2021-10-11 12:00:03', 'api', 500, 1)

Результат:

SELECT * FROM responses_status_by_min  ┌────────────────time─┬─app────┬─status─┬─count─┐ │ 2021-10-11 12:00:00 │ api    │    200 │     2 │ │ 2021-10-11 12:00:00 │ api    │    500 │     6 │ │ 2021-10-11 12:00:00 │ search │    200 │     6 │ │ 2021-10-11 12:00:00 │ search │    500 │     3 │ └─────────────────────┴────────┴────────┴───────┘ ┌────────────────time─┬─app────┬─status─┬─count─┐ │ 2021-10-11 12:00:00 │ api    │    200 │     2 │ │ 2021-10-11 12:00:00 │ api    │    500 │     6 │ │ 2021-10-11 12:00:00 │ search │    200 │     6 │ │ 2021-10-11 12:00:00 │ search │    500 │     3 │ └─────────────────────┴────────┴────────┴───────┘

И тут тоже ClickHouse не сразу суммирует. Ок, уже привыкли, но все же хочется увидеть результат:

SELECT * FROM responses_status_by_min FINAL  ┌────────────────time─┬─app────┬─status─┬─count─┐ │ 2021-10-11 12:00:00 │ api    │    200 │     4 │ │ 2021-10-11 12:00:00 │ search │    500 │     6 │ └─────────────────────┴────────┴────────┴───────┘ ┌────────────────time─┬─app────┬─status─┬─count─┐ │ 2021-10-11 12:00:00 │ api    │    500 │    12 │ │ 2021-10-11 12:00:00 │ search │    200 │    12 │ └─────────────────────┴────────┴────────┴───────┘

Все верно. А теперь посмотрим что будет при появлении дубликатов. Для чистоты эксперимента я предварительно сделал TRUNCATE обеих таблиц. После этого сделал две одинаковые вставки:

INSERT INTO responses_status (time,app,status,count) VALUES ('2021-10-11 12:00:00', 'search', 200, 3), ('2021-10-11 12:00:00', 'search', 500, 1), ('2021-10-11 12:00:01', 'search', 200, 3), ('2021-10-11 12:00:01', 'search', 500, 2), ('2021-10-11 12:00:00', 'api', 200, 1), ('2021-10-11 12:00:00', 'api', 500, 5), ('2021-10-11 12:00:01', 'api', 200, 1), ('2021-10-11 12:00:01', 'api', 500, 1)  INSERT INTO responses_status (time,app,status,count) VALUES ('2021-10-11 12:00:00', 'search', 200, 3), ('2021-10-11 12:00:00', 'search', 500, 1), ('2021-10-11 12:00:01', 'search', 200, 3), ('2021-10-11 12:00:01', 'search', 500, 2), ('2021-10-11 12:00:00', 'api', 200, 1), ('2021-10-11 12:00:00', 'api', 500, 5), ('2021-10-11 12:00:01', 'api', 200, 1), ('2021-10-11 12:00:01', 'api', 500, 1)

В обеих таблицах данные ожидаемо пока еще не смержены, но результат говорит, что все получилось:

SELECT * FROM responses_status_by_min FINAL  ┌────────────────time─┬─app────┬─status─┬─count─┐ │ 2021-10-11 12:00:00 │ api    │    200 │     4 │ │ 2021-10-11 12:00:00 │ search │    500 │     6 │ └─────────────────────┴────────┴────────┴───────┘ ┌────────────────time─┬─app────┬─status─┬─count─┐ │ 2021-10-11 12:00:00 │ api    │    500 │    12 │ │ 2021-10-11 12:00:00 │ search │    200 │    12 │ └─────────────────────┴────────┴────────┴───────┘