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

Предварительный расчёт, обновление по расписанию и загрузка истории без дублей

#Результат урока

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

#Обзор материализованных представлений

В ClickHouse есть два разных механизма с похожим названием:

инкрементальное материализованное представление обрабатывает блок каждой новой вставки и пишет результат в таблицу назначения;
обновляемое материализованное представление выполняет запрос по расписанию и заменяет либо дополняет сохранённый результат.

Отличие от обычных представлений:

VIEW — виртуальная таблица, запрос выполняется при каждом обращении
MATERIALIZED VIEW — физические данные, обновляются автоматически

#Создание материализованного представления

#Базовый синтаксис

-- Исходная таблица
CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type String,
    value      Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- Материализованное представление
CREATE MATERIALIZED VIEW events_daily_mv
ENGINE = SummingMergeTree()
ORDER BY (date, event_type)
AS SELECT 
    toDate(event_time) AS date,
    event_type,
    count() AS events,
    sum(value) AS total_value
FROM events
GROUP BY date, event_type;

Без TO ClickHouse создаёт внутреннюю таблицу хранения. Для рабочей системы обычно удобнее явная таблица назначения: её DDL, TTL и данные можно обслуживать независимо от определения представления.

#С явной таблицей назначения (TO)

-- Таблица для хранения агрегатов
CREATE TABLE events_daily
(
    date        Date,
    event_type  String,
    events      UInt64,
    total_value Decimal(12, 2)
)
ENGINE = SummingMergeTree()
ORDER BY (date, event_type);

-- MV с указанием таблицы назначения
CREATE MATERIALIZED VIEW events_daily_mv TO events_daily
AS SELECT 
    toDate(event_time) AS date,
    event_type,
    count() AS events,
    sum(value) AS total_value
FROM events
GROUP BY date, event_type;

Преимущества TO:

Явное управление таблицей агрегатов
Можно использовать специализированные движки (AggregatingMergeTree)
Легче модифицировать независимо от MV

#Как работает материализованное представление

#Поток данных

INSERT INTO events
    ↓
Триггер MV
    ↓
Выполнение SELECT из MV
    ↓
INSERT в таблицу назначения

Важно:

MV срабатывает только на INSERT
UPDATE/DELETE не триггерят MV (используйте мутации)
Преобразование выполняется в пути исходного INSERT; ошибки зависимого представления могут привести к ошибке вставки
Повторные попытки требуют отдельно проверить дедупликацию исходной и целевой таблиц для используемой версии

#Пример работы

-- Вставка в исходную таблицу
INSERT INTO events (event_time, user_id, event_type, value)
VALUES 
    ('2026-03-01 10:00:00', 1, 'click', 1.0),
    ('2026-03-01 11:00:00', 2, 'click', 1.5),
    ('2026-03-01 12:00:00', 1, 'view', 0.5);

-- Данные автоматически агрегируются в MV
SELECT * FROM events_daily;

-- Результат:
-- date       | event_type | events | total_value
-- 2026-03-01 | click      | 2      | 2.5
-- 2026-03-01 | view       | 1      | 0.5

#Типы материализованных представлений

#1. Агрегация с SummingMergeTree

CREATE TABLE hourly_stats
(
    hour        DateTime,
    event_type  LowCardinality(String),
    events      UInt64,
    unique_users UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (hour, event_type);

CREATE MATERIALIZED VIEW hourly_stats_mv TO hourly_stats
AS SELECT 
    toStartOfHour(event_time) AS hour,
    event_type,
    count() AS events,
    uniq(user_id) AS unique_users
FROM events
GROUP BY hour, event_type;

#2. Агрегация с AggregatingMergeTree

CREATE TABLE daily_stats
(
    date        Date,
    event_type  String,
    uniq_users  AggregateFunction(uniq, UInt64),
    sum_value   AggregateFunction(sum, Decimal(10, 2)),
    max_value   AggregateFunction(max, Decimal(10, 2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, event_type);

CREATE MATERIALIZED VIEW daily_stats_mv TO daily_stats
AS SELECT 
    toDate(event_time) AS date,
    event_type,
    uniqState(user_id) AS uniq_users,
    sumState(value) AS sum_value,
    maxState(value) AS max_value
FROM events
GROUP BY date, event_type;

-- Чтение с применением агрегации
SELECT 
    date,
    event_type,
    uniqMerge(uniq_users) AS unique_users,
    sumMerge(sum_value) AS total_value,
    maxMerge(max_value) AS max_value
FROM daily_stats
GROUP BY date, event_type;

#3. Фильтрация данных

-- MV только для важных событий
CREATE TABLE important_events
(
    event_time DateTime,
    user_id    UInt64,
    event_type String
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

CREATE MATERIALIZED VIEW important_events_mv TO important_events
AS SELECT 
    event_time,
    user_id,
    event_type
FROM events
WHERE event_type IN ('purchase', 'registration');

-- Теперь в important_events попадают только нужные события

#4. Трансформация данных

-- MV с обогащёнными данными
CREATE TABLE enriched_events
(
    event_time DateTime,
    user_id    UInt64,
    event_type String,
    country    String,
    is_mobile  UInt8
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

CREATE MATERIALIZED VIEW enriched_events_mv TO enriched_events
AS SELECT 
    e.event_time,
    e.user_id,
    e.event_type,
    dictGet('users_dict', 'country', e.user_id) AS country,
    e.user_agent LIKE '%Mobile%' AS is_mobile
FROM events AS e;

#Обновляемые материализованные представления

LIVE VIEW объявлен устаревшим и не должен использоваться в новом проекте. Для периодического полного пересчёта применяйте refreshable materialized view:

CREATE MATERIALIZED VIEW tenant_daily_snapshot
REFRESH EVERY 1 HOUR
ENGINE = MergeTree
ORDER BY (date, tenant_id)
AS SELECT
    toDate(event_time) AS date,
    tenant_id,
    count() AS events,
    sum(revenue) AS revenue
FROM events
GROUP BY date, tenant_id;

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

Режим без APPEND атомарно заменяет прошлый результат после успешного обновления. С APPEND новые строки дописываются; тогда автор схемы отвечает за идемпотентность и границы обрабатываемого интервала.

SELECT
    database,
    view,
    status,
    last_success_time,
    next_refresh_time,
    exception
FROM system.view_refreshes;

Используйте DEPENDS ON для зависимостей между обновляемыми представлениями и проверяйте состояние MissingDependencies. Расписание не означает бесконечную параллельность: для одного представления одновременно выполняется не больше одного обновления.

#Словари (Dictionaries)

Словарь — структура данных для быстрого обогащения запросов.

#Создание словаря

CREATE DICTIONARY users_dict
(
    user_id   UInt64,
    country   String,
    city      String,
    segment   String
)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(
    HOST 'localhost'
    PORT 9000
    USER 'default'
    DB 'default'
    TABLE 'users'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 360);

Параметры:

PRIMARY KEY — ключ для поиска
SOURCE — источник данных
LAYOUT — структура в памяти (HASHED, CACHE, COMPLEX)
LIFETIME — интервал обновления (секунды)

#Использование словаря

-- Получение значения
SELECT 
    user_id,
    dictGet('users_dict', 'country', user_id) AS country
FROM events;

-- Проверка наличия
SELECT 
    user_id,
    dictGetOrDefault('users_dict', 'country', user_id, 'Unknown') AS country
FROM events;

-- Множественные значения
SELECT 
    user_id,
    dictGet('users_dict', 'country', user_id) AS country,
    dictGet('users_dict', 'city', user_id) AS city
FROM events;

#Типы словарей

1. Прямой (Direct):

LAYOUT(DIRECT())

Простой поиск по ключу
Для небольших словарей

2. Хэш (Hashed):

LAYOUT(HASHED())

Хэш-таблица для быстрого поиска
Для средних словарей

3. Кэш (Cache):

LAYOUT(CACHE(SIZE 1000000))

Кэширование часто используемых значений
Для больших словарей

4. Range (Диапазоны):

LAYOUT(RANGE_HASHED())
LIFETIME(MIN 300 MAX 360)
RANGE(MIN start_date MAX end_date)

Поиск по диапазону (например, исторические курсы валют)

#Источники данных

-- ClickHouse таблица
SOURCE(CLICKHOUSE(...))

-- MySQL
SOURCE(MYSQL(
    HOST 'mysql-host'
    PORT 3306
    USER 'user'
    PASSWORD 'pass'
    DB 'database'
    TABLE 'users'
))

-- PostgreSQL
SOURCE(POSTGRES(...))

-- HTTP URL
SOURCE(HTTP(URL 'http://example.com/dict.csv'))

-- Файл
SOURCE(FILE(PATH '/dict.csv', FORMAT 'CSV'))

-- MongoDB
SOURCE(MONGODB(...))

#Управление материализованными представлениями

#Просмотр MV

-- Список MV
SELECT name, database 
FROM system.tables 
WHERE engine LIKE '%MaterializedView%';

-- Определение MV
SHOW CREATE MATERIALIZED VIEW events_daily_mv;

-- Статус MV
SELECT 
    name,
    is_attached,
    metadata_modification_time
FROM system.tables
WHERE database = 'default'
  AND engine LIKE '%MaterializedView%';

#Отключение/Включение MV

-- Отключить MV (данные не будут писаться)
DETACH MATERIALIZED VIEW events_daily_mv;

-- Включить MV
ATTACH MATERIALIZED VIEW events_daily_mv;

#Удаление MV

-- Удалить только MV (таблица назначения останется)
DROP MATERIALIZED VIEW events_daily_mv;

-- Удалить MV и таблицу назначения
DROP TABLE events_daily;
DROP MATERIALIZED VIEW events_daily_mv;

#Пересоздание MV

-- Очистить таблицу назначения
TRUNCATE TABLE events_daily;

-- Пересоздать MV
DROP MATERIALIZED VIEW events_daily_mv;
CREATE MATERIALIZED VIEW events_daily_mv TO events_daily
AS SELECT ...;

-- Заполнить историческими данными
INSERT INTO events_daily
SELECT 
    toDate(event_time) AS date,
    event_type,
    count() AS events,
    sum(value) AS total_value
FROM events
GROUP BY date, event_type;

#Решения перед запуском потока

#1. Используйте TO для явного управления

-- Хорошо: явная таблица
CREATE TABLE agg_table (...) ENGINE = SummingMergeTree();
CREATE MATERIALIZED VIEW mv TO agg_table AS SELECT ...;

-- Плохо: неявная таблица
CREATE MATERIALIZED VIEW mv AS SELECT ...;

#2. Спланируйте загрузку истории до включения живого потока

-- Явно ограничьте историю водоразделом
INSERT INTO agg_table
SELECT ...
FROM source_table
WHERE event_time < '2026-08-10 12:00:00';

Создание MV и загрузка истории без водораздела опасны: новые события могут попасть в витрину дважды. Зафиксируйте границу, предусмотрите повторный запуск и сравните контрольные агрегаты источника и назначения.

#3. Выбирайте правильный движок

SummingMergeTree — для простых SUM/COUNT
AggregatingMergeTree — для uniq, quantile
MergeTree — для фильтрации/трансформации

#4. Учитывайте семантику входного блока

-- Хорошо: простая агрегация
CREATE MATERIALIZED VIEW mv TO target
AS SELECT date, count() FROM events GROUP BY date;

-- Опасно без модели изменений справочника:
CREATE MATERIALIZED VIEW mv TO target
AS SELECT e.*, u.country 
FROM events e 
JOIN users u ON e.user_id = u.id;

Инкрементальное MV запускается на вставляемом блоке исходной таблицы, а не пересчитывает всю историю. Изменение таблицы users само по себе не обновит ранее обогащённые строки. Для актуального снимка рассмотрите словарь или обновляемое представление.

#5. Мониторьте MV

-- Проверка отставания
SELECT 
    name,
    metadata_modification_time,
    toLastModification(name) AS last_update
FROM system.tables
WHERE engine LIKE '%MaterializedView%';

#Практика: витрина без двойного учёта

Создайте инкрементальную часовую витрину для events.
Зафиксируйте время T, включите MV только для событий event_time >= T и отдельно загрузите историю до T.
Повторите историческую загрузку и докажите, что результат не задвоился.
Сравните исходную таблицу и витрину по трём контрольным дням.
Запишите цену решения: дополнительные байты на диске и изменение времени вставки.

Работа принята, если DDL и загрузку истории можно повторить с чистого состояния, граница T названа явно, а контрольные суммы источника и витрины совпадают.

Материализованное представление решает задачу вычисления, но не снимает вопросов о загрузке, исправлениях и сроке хранения. Они разбираются в следующем уроке.