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

06.04.2022 15:45

antgubarev 0 653 Источник

В первой части я прошелся по основным понятиям по работе с материализованным представлением и 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 │
└─────────────────────┴────────┴────────┴───────┘

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

Комментарии (0)