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