ClickHouse: автоматизируем расчеты с помощью Materialized View

ivannatarov12 часов назад

ClickHouse: автоматизируем расчеты с помощью Materialized View

Уровень сложностиПростойВремя на прочтение6 минОхват и читатели4.6KБазы данных*Data Engineering*FAQПривет, Хабр!

В прошлой статье мы заглянули под капот ClickHouse и разобрались, как работает движок MergeTree. Мы узнали, как хранятся данные и что такое парт, зачем нужен разреженный индекс и как работает фоновое слияние.

В этой статье мы рассмотрим один из мощнейших инструментов ClickHouse — Materialized View.

1. Что такое Materialized View

Представьте, что у нас есть сложный и ресурсозатратный запрос, который мы выполняем снова и снова. Например, ежедневный отчёт по выручке с группировками и объединениями нескольких таблиц. Каждый раз, когда пользователь открывает дашборд, ClickHouse вынужден заново обрабатывать гигабайты сырых данных.

Materialized View — это механизм, который выполняет этот запрос заранее и сохраняет (материализует) его результат в отдельной, целевой таблице. Когда мы делаем запрос к целевой таблице, ClickHouse не выполняет сложные вычисления заново, а просто отдаёт уже готовый, предрассчитанный результат. Это как если бы бухгалтер не пересчитывал все чеки за месяц каждый раз, когда его просят назвать итоговую сумму, а держал бы на столе листок с уже посчитанным результатом.В ClickHouse существует два фундаментально разных типа Materialized View: инкрементные и обновляемые. Выбор между ними — ключ к построению эффективной аналитической системы.

2. Инкрементные Materialized View

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

По своей сути, инкрементное Materialized View — это триггер на INSERT, который выполняет одно конкретное действие: он преобразует вставляемые данные согласно своему SELECT-запросу и помещает результат в целевую таблицу.Когда мы вставляем новый блок данных INSERT в исходную (source) таблицу, ClickHouse «на лету» применяет к этому новому блоку SQL-запрос из представления и дописывает результат в целевую (target) таблицу.

Схематично процесс можно представить следующим образом:

Инкрементное Materialized ViewТаким образом, вычислительная нагрузка переносится с момента чтения SELECT на момент записи INSERT и распределяется по времени. Запросы к целевой таблице выполняются молниеносно, так как они работают с уже агрегированными, компактными данными.

Использовать инкрементные представления рекомендуется, когда:

• Нам нужны результаты в реальном времени, обновляемые при каждой новой партии данных.
• Мы выполняем частые агрегации или фильтрацию на больших объёмах данных (логи, метрики, события).
• Наши запросы — это в основном агрегации по одной таблице GROUP BY.Для корректной работы с агрегатами (например, uniq, avg) в целевой таблице рекомендуется использовать движок AggregatingMergeTree.Рассмотрим практический пример.

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

У нас есть таблица-источник с сырыми логами logs:

event_dateurluser_id2025-09-12/home1012025-09-12/products2542025-09-12/home1012025-09-13/contact3122025-09-13/products1012025-09-14/home450Давайте посмотрим, как мы можем решить задачу, используя инкрементное Materialized View.

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

CREATE TABLE daily_page_stats (
event_date Date,
url String,
page_views AggregateFunction(count),
unique_users AggregateFunction(uniq, UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_date, url)Схематично первый шаг можно представить следующим образом:

Шаг 1Затем создадим Materialized View, которое свяжет таблицу logs и daily_page_stats. Оно будет срабатывать при каждом INSERT в таблицу logs, брать пачку вставляемых данных, применять к ней SQL-запрос и записывать данные в таблицу daily_page_stats.

-- Создаем мат. представление и указываем целевую таблицу
CREATE MATERIALIZED VIEW daily_page_stats_mv TO daily_page_stats AS
-- Указываем запрос, который будет обрабатывать данные
SELECT
event_date,
url,
countState() AS page_views,
uniqState(user_id) AS unique_users
FROM logs
GROUP BY event_date, urlИтоговый процесс схематично будет выглядеть следующим образом:

Шаг 2Теперь мы можем обращаться к целевой таблице daily_page_stats для получения итогового результата.

SELECT
event_date,
url,
countMerge(page_views) AS total_page_views,
uniqMerge(unique_users) AS total_unique_users
FROM daily_page_stats
GROUP BY event_date, urlevent_dateurltotal_page_viewstotal_unique_users2025-09-12/home212025-09-12/products112025-09-13/contact112025-09-13/products112025-09-14/home11Этот запрос выполнится практически мгновенно, даже если в исходной таблице logs миллиарды записей.

Инкрементное представление «слушает» вставки только в ту таблицу, которая стоит в блоке FROM. Если вы добавите в запрос JOIN со справочником, то при обновлении данных в самом справочнике представление не сработает. 

3. Обновляемые Materialized View

Этот тип представлений появился в ClickHouse после инкрементных и работает по принципу, более знакомому пользователям традиционных баз данных, таких как PostgreSQL. Они выполняют запрос по расписанию и перезаписывают результат в целевой таблице.

Мы задаём расписание (REFRESH EVERY ...), и ClickHouse с указанной периодичностью выполняет весь SQL-запрос представления с нуля, а затем атомарно заменяет старые данные в целевой таблице новыми.

Схематично процесс можно представить следующим образом:

Обновляемое Materialized ViewИспользовать обновляемые Materialized View рекомендуется, когда:

• Нам нужна максимальная скорость чтения (миллисекунды), и мы готовы мириться с небольшой задержкой данных, например, мы анализируем данные за прошлый час или день.
• Нам нужно кешировать результаты сложных запросов, например с JOIN нескольких таблиц.
• Объём результата запроса не растёт бесконечно со временем (например, «топ-100 товаров за последние 24 часа» или продажи за прошлый день, месяц).Будьте осторожны с частотой обновления. Если сам запрос выполняется 10 секунд, не стоит ставить ему обновление каждые 5 секунд. Это создаст избыточную нагрузку.Рассмотрим практический пример.

Вернёмся к нашим логам. Теперь задача — создать дашборд с топ-10 самых популярных страниц за последние 7 дней, обогатив их заголовками из справочника. Дашборд должен открываться мгновенно, данные могут обновляться раз в 15 минут.

Пример справочника pages_metadata:

urltitle/contactКонтакты/homeГлавная страница/productsНаши продуктыИсходная таблица logs и справочник у нас уже есть. Нам необходимо создать новую целевую таблицу для хранения топа страниц. В данной задаче нам подойдёт обычный движок MergeTree, так как данные каждый раз будут полностью перезаписываться.

CREATE TABLE top_10_pages_enrich (
title String,
page_views UInt32
)
ENGINE = MergeTree()
ORDER BY titleСхематично первый шаг можно представить следующим образом:

Шаг 1Следующим шагом создадим обновляемое Materialized View, которое будет каждые 15 минут выполнять запрос и перезаписывать данные в таблице top_10_pages_enrich.

CREATE MATERIALIZED VIEW top_10_pages_enrich_mv
-- Задаем время обновления
REFRESH EVERY 15 MINUTE TO top_10_pages_enrich AS
SELECT
pm.title AS page_title,
count(*) AS page_views
FROM logs AS l
JOIN pages_metadata AS pm
ON l.url = pm.url
-- Получаем данные за 7 дней от текущей даты
WHERE l.event_date >= today() - 7
GROUP BY
l.url,
pm.title
ORDER BY page_views DESC
LIMIT 10Итоговый процесс схематично будет выглядеть следующим образом:

Шаг 2Теперь для получения топа страниц за последние 7 дней мы можем выполнить простой, сверхбыстрый запрос (в таблице всегда хранится не более 10 записей) к таблице top_10_pages_enrich:

SELECT *
FROM top_10_pages_enrichtitlepage_viewsГлавная страница3Наши продукты2Контакты1Также стоит отметить, что иногда необходимо принудительно обновить Materialized View или изменить частоту обновления. Для этого можно использовать следующие команды:

-- Принудительное обновление SYSTEM REFRESH VIEW top_10_pages_enrich_mv

-- Изменение времени обновления
ALTER TABLE top_10_pages_enrich_mv
MODIFY REFRESH EVERY 10 MINUTE

Итог

Чтобы эффективно автоматизировать расчёты в ClickHouse, помните:

• Инкрементные Materialized View : Это триггер на INSERT. Данные агрегируются в реальном времени пачками, представление «видит» обновления только в целевой таблице.
• Обновляемые Materialized View : Работают по расписанию (REFRESH EVERY). Незаменимы для кэширования тяжелых запросов с JOIN справочников, когда допустима небольшая задержка.
• AggregatingMergeTree: Главный движок для инкрементных представлений, позволяющий корректно хранить и дособирать агрегации (например, уникальных пользователей) при чтении.Надеюсь, эта статья помогла вам лучше понять механику работы Materialized View и то, как с их помощью ускорить аналитику.

P.S. Если вы хотите систематизировать знания и получить прочную теоретическую базу для дальнейшего освоения ClickHouse на практике, буду рад видеть вас на моем бесплатном курсе ClickHouse с нуля который охватывает все самое необходимое для уверенного старта в работе с технологией.

Удачи в изучении!Теги:• clickhouse
• sql
• СУБД
• materialized view
• материализованные представленияХабы:• Базы данных
• Data Engineering