Вы пришли из мира PostgreSQL, Oracle или MSSQL. Вы знаете: материализованное представление — это «замороженный» результат запроса. Удобно. Предсказуемо.

Вы открываете документацию ClickHouse. Видите знакомые слова. Радуетесь. Пишете свой первый MATERIALIZED VIEW. Запускаете. И... получаете не то, что ожидали.

Потому что в ClickHouse материализованные представления работают СОВСЕМ не так, как везде.

И сегодня я расскажу, в чём подвох.

Как это работает в классических СУБД

Напоминание для тех, кто пришёл не из мира БД:

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

• Это результат SELECT, сохранённый на диске

• Данные хранятся физически, запросы к нему работают быстро

Пример (мысленный, из PostgreSQL):

CREATE MATERIALIZED VIEW sales_summary AS
SELECT category, SUM(amount) FROM sales GROUP BY category;

Потом вы делаете REFRESH MATERIALIZED VIEW, и данные обновляются.

Всё логично. Предсказуемо.

Но вот в ClickHouse это работает не так.

Как это работает в ClickHouse

В ClickHouse есть два вида представлений — инкрементальные (по умолчанию) и обновляемые.

Команда CREATE MATERIALIZED VIEW вызовет создание инкрементального представления, поэтому прочитаем в документации, что же на самом деле отработает:

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

Перевожу: вы пишете INSERT в таблицу. ClickHouse берёт эти новые строки, прогоняет их через ваш SELECT и результат вставляет в отдельную целевую таблицу.

Никакого «пересчёта всего представления». Никакого REFRESH. Только новые данные.

Схематично:

-- создаем таблицу с тестовыми данными
CREATE TABLE orders (
    order_id UInt64,
    price UInt64,
    event_time DateTime
) ENGINE = MergeTree()
ORDER BY order_id
SETTINGS index_granularity = 8192;

-- вставляем данные
INSERT INTO orders values
(1, 100, now()),
(1, 200, now()),
(1, 300, now()),
(2, 400, now()),
(2, 500, now()),
;

-- материализованное представление
CREATE MATERIALIZED VIEW mv_orders_summary
ENGINE = SummingMergeTree() ORDER BY order_id
AS SELECT order_id, SUM(price) as total FROM orders GROUP BY order_id;

-- видим, что в MV ничего не появилось. Потому, что вставка данных была ДО создания MV.
select * from mv_orders_summary limit 100;

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

-- для того, чтобы это исправить, необходимо в вставить данные именно в MV без поля event_time
INSERT INTO mv_orders_summary values
(1, 100),
(1, 200),
(1, 300),
(2, 400),
(2, 500),
;

-- видим, что в MV появились результаты.
select * from mv_orders_summary limit 100;

А теперь вставим данные в исходную таблицу, чтобы убедиться, что MV отработает:

-- а теперь вставялем данные в исходную таблицу при уже существующей MV
INSERT INTO orders values
(3, 600, now()),
(3, 700, now())
;

-- видим, что в MV появились результаты.
select * from mv_orders_summary limit 100;

Отлично. Мы поняли, как это работает — никакого пересчёта всей таблицы orders не происходит. Только новые строки и только insert. Отсюда следует, что

UPDATE и DELETE не работают

Это боль.

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

-- обновляем данные по первому заказу
alter table orders update price = 100 WHERE order_id = 1;

-- видим, что в MV ничего не поменялось.
select * from mv_orders_summary limit 100;

А что если я не хочу танцевать с бубном?

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

• необходимо создать конечную таблицу

• необходимо указать ключевую конструкцию REFRESH EVERY [NUMBER] [INTERVAL], где NUMBER - число, задающее периодичность обновления, а INTERVAL - временной градиент (часы/минуты/секунды и т.д.)

Перейдем к примеру. Создадим конечную таблицу такую же, как и у MV в экспериментах выше, и создадим RMV с обновлением каждую минуту:

-- создаем конечную таблицу, в которую MV будет записывать данные
CREATE TABLE default.sum_orders
(
    `order_id` UInt64,
    `total` UInt64
)
ENGINE = SummingMergeTree
ORDER BY order_id
SETTINGS index_granularity = 8192;

-- создаем обновляемое MV
CREATE MATERIALIZED VIEW mv_orders_summary_refresh
REFRESH EVERY 1 MINUTE TO sum_orders
AS SELECT order_id, SUM(price) as total FROM orders GROUP BY order_id
;

-- смотрим, что результаты обновляются каждую минуту
select * from sum_orders;

Теперь можно делать любые манипуляции с данными и через минуту (если запрос легкий и данные не большие) видеть изменения в MV.

Стоит упомянуть, что по умолчанию у RMV используется концепция REPLACE, то есть полная замена данных в конечной таблице, что является привычным аналогом MV классических СУБД. Подробнее об этом есть в документации, углубляться нет смысла, лучше отметить следующее: не смотря на наличие RMV, баловаться командами DML в ClickHouse не стоит. Подробнее это будет раскрыто в следующих статьях.

Итого:

Как вы уже поняли, ClickHouse — штука мощная, но со своим характером. Подводных камней здесь хватает.

Осваивать этого зверя можно двумя способами. Первый — героический: месяцами вчитываться в документацию, собирать грабли по крупицам из форумов (привет Хабр) и всевозможных чатов. Второй — прагматичный: пройти бесплатный курс от автора этой статьи, где все шишки уже набиты, а опыт упакован в понятные уроки.

Выбирайте путь умного.
ClickHouse: быстрый старт