Вы пришли из мира 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 = 1000 WHERE order_id = 1;
-- видим, что в MV ничего не поменялось.
select * from mv_orders_summary limit 100;
Из рекомендаций — использовать только INSERT + версионирование данных. Звучит совсем печально.
А что если я не хочу танцевать с бубном?
Скажу сразу — это плохой совет, но все же: в 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, то есть полная замена данных в конечной таблице, что является привычным аналогом классических СУБД. Подробнее об этом есть в документации, углубляться нет смысла, лучше отметить следующее: не смотря не наличие RMV, баловаться командами DML в ClickHouse не стоит. Подробнее это будет раскрыто в следующих статьях.
Итого:
Как вы уже поняли, ClickHouse — штука мощная, но со своим характером. Подводных камней здесь хватает.
Осваивать этого зверя можно двумя способами. Первый — героический: месяцами вчитываться в документацию, собирать грабли по крупицам из форумов (привет Хабр) и всевозможных чатов. Второй — прагматичный: пройти бесплатный курс от автора этой статьи, где все шишки уже набиты, а опыт упакован в понятные уроки.
Выбирайте путь умного. Вот он:
ClickHouse: быстрый старт