Обновляемые материализованные представления концептуально схожи с материализованными представлениями в традиционных OLTP базах данных, они хранят результат заданного запроса для быстрого извлечения и снижают необходимость многократного выполнения ресурсоемких запросов. В отличие от инкрементных материализованных представлений в ClickHouse, требуется периодическое выполнение запроса по полному набору данных - результаты которого хранятся в целевой таблице для выполнения запроса. Этот набор результатов, как правило, должен быть меньше оригинального набора данных, что позволяет последующему запросу выполняться быстрее.
Диаграмма объясняет, как работают обновляемые материализованные представления:
Вы также можете посмотреть следующее видео:
Когда следует использовать обновляемые материализованные представления?
Инкрементные материализованные представления ClickHouse обладаются огромной мощностью и, как правило, масштабируются намного лучше, чем подход, используемый обновляемыми материализованными представлениями, особенно в случаях, когда необходимо провести агрегацию над одной таблицей. Путем выполнения агрегации только на каждом блоке данных по мере его вставки и слияния инкрементных состояний в окончательной таблице, запрос выполняется только на подмножестве данных. Этот метод может масштабироваться до потенциальных петабайт данных и, как правило, является предпочтительным методом.
Тем не менее, существуют случаи использования, в которых этот инкрементный процесс не требуется или не применим. Некоторые задачи несовместимы с инкрементным подходом или не требуют обновлений в реальном времени, при этом периодическое восстановление будет более уместным. Например, вы можете периодически выполнять полное пересчитывание представления по полному набору данных, потому что оно использует сложное соединение, что несовместимо с инкрементным подходом.
Обновляемые материализованные представления могут выполнять пакетные процессы, выполняя такие задачи, как денормализация. Можно создать зависимости между обновляемыми материализованными представлениями, при этом одно представление зависит от результатов другого и выполняется только после его завершения. Это может заменить запланированные рабочие процессы или простые DAG, такие как dbt. Чтобы узнать больше о том, как установить зависимости между обновляемыми материализованными представлениями, перейдите в раздел СОЗДАТЬ ПРЕДСТАВЛЕНИЕ, раздел Зависимости.
Как обновить обновляемое материализованное представление?
Обновляемые материализованные представления автоматически обновляются с интервалом, определенным во время создания.
Например, следующее материализованное представление обновляется каждую минуту:
CREATE MATERIALIZED VIEW table_name_mv
REFRESH EVERY 1 MINUTE TO table_name AS
...
Если нужно принудительно обновить материализованное представление, вы можете использовать конструкцию SYSTEM REFRESH VIEW:
Когда последний раз обновлялось обновляемое материализованное представление?
Чтобы узнать, когда в последний раз обновлялось обновляемое материализованное представление, вы можете выполнить запрос к системной таблице system.view_refreshes, как показано ниже:
Функция APPEND позволяет добавлять новые строки в конец таблицы вместо замены всего представления.
Одно из использований этой функции - захват снимков значений в определенный момент времени. Например, предположим, что у нас есть таблица events, заполняемая потоком сообщений из Kafka, Redpanda или другой платформы потоковых данных.
Этот набор данных содержит 4096 значений в колонке uuid. Мы можем написать следующий запрос, чтобы найти те, у кого максимальное общее количество:
SELECT
uuid,
sum(count) AS count
FROM events
GROUP BY ALL
ORDER BY count DESC
LIMIT 10
┌─uuid─┬───count─┐
│ c6f │ 5676468 │
│ 951 │ 5669731 │
│ 6a6 │ 5664552 │
│ b06 │ 5662036 │
│ 0ca │ 5658580 │
│ 2cd │ 5657182 │
│ 32a │ 5656475 │
│ ffe │ 5653952 │
│ f33 │ 5653783 │
│ c5b │ 5649936 │
└──────┴─────────┘
Допустим, мы хотим фиксировать количество для каждого uuid каждые 10 секунд и хранить это в новой таблице под названием events_snapshot. Схема events_snapshot будет выглядеть следующим образом:
CREATE TABLE events_snapshot (
ts DateTime32,
uuid String,
count UInt64
)
ENGINE = MergeTree
ORDER BY uuid;
Затем мы можем создать обновляемое материализованное представление для заполнения этой таблицы:
CREATE MATERIALIZED VIEW events_snapshot_mv
REFRESH EVERY 10 SECOND APPEND TO events_snapshot
AS SELECT
now() AS ts,
uuid,
sum(count) AS count
FROM events
GROUP BY ALL;
Затем мы можем выполнить запрос к events_snapshot чтобы получить количество с течением времени для конкретного uuid:
Руководство по денормализации данных демонстрирует различные техники денормализации данных с использованием набора данных Stack Overflow. Мы заполняем данные в следующие таблицы: votes, users, badges, posts и postlinks.
В этом руководстве мы показали, как денормализовать набор данных postlinks в таблицу posts с помощью следующего запроса:
SELECT
posts.*,
arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
SELECT
PostId,
groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
FROM postlinks
GROUP BY PostId
) AS postlinks ON posts_types_codecs_ordered.Id = postlinks.PostId;
Затем мы показали, как выполнить одноразовую вставку этих данных в таблицу posts_with_links, но в производственной системе мы хотели бы выполнять эту операцию периодически.
Как таблицы posts, так и postlinks могут потенциально обновляться. Поэтому вместо того, чтобы пытаться реализовать это соединение с использованием инкрементных материализованных представлений, может быть достаточно просто запланировать выполнение этого запроса через установленный интервал, например, раз в час, храня результаты в таблице post_with_links.
Здесь обновляемое материализованное представление может помочь, и мы можем создать его с помощью следующего запроса:
CREATE MATERIALIZED VIEW posts_with_links_mv
REFRESH EVERY 1 HOUR TO posts_with_links AS
SELECT
posts.*,
arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
SELECT
PostId,
groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
FROM postlinks
GROUP BY PostId
) AS postlinks ON posts_types_codecs_ordered.Id = postlinks.PostId;
Представление будет выполняться немедленно и каждый час после этого, как настроено, чтобы обеспечить отражение обновлений исходной таблицы. Важно, что при повторном запуске запроса набор результатов атомарно и прозрачно обновляется.
примечание
Синтаксис здесь идентичен инкрементному материализованному представлению, за исключением того, что мы включаем конструкцию REFRESH:
Затем мы можем написать следующий запрос, который можно использовать для вычисления сводки по каждому актеру, отсортированной по количеству появлений в фильмах.
SELECT
id, any(actor_name) AS name, uniqExact(movie_id) AS movies,
round(avg(rank), 2) AS avg_rank, uniqExact(genre) AS genres,
uniqExact(director_name) AS directors, max(created_at) AS updated_at
FROM (
SELECT
imdb.actors.id AS id,
concat(imdb.actors.first_name, ' ', imdb.actors.last_name) AS actor_name,
imdb.movies.id AS movie_id, imdb.movies.rank AS rank, genre,
concat(imdb.directors.first_name, ' ', imdb.directors.last_name) AS director_name,
created_at
FROM imdb.actors
INNER JOIN imdb.roles ON imdb.roles.actor_id = imdb.actors.id
LEFT JOIN imdb.movies ON imdb.movies.id = imdb.roles.movie_id
LEFT JOIN imdb.genres ON imdb.genres.movie_id = imdb.movies.id
LEFT JOIN imdb.movie_directors ON imdb.movie_directors.movie_id = imdb.movies.id
LEFT JOIN imdb.directors ON imdb.directors.id = imdb.movie_directors.director_id
)
GROUP BY id
ORDER BY movies DESC
LIMIT 5;
Не занимает много времени, чтобы вернуть результат, но давайте скажем, что мы хотим, чтобы это было еще быстрее и менее ресурсозатратно. Предположим, что этот набор данных также постоянно обновляется - фильмы постоянно выходят, и новые актеры также появляются.
Пора создавать обновляемое материализованное представление, поэтому давайте сначала создадим целевую таблицу для результатов:
CREATE MATERIALIZED VIEW imdb.actor_summary_mv
REFRESH EVERY 1 MINUTE TO imdb.actor_summary AS
SELECT
id,
any(actor_name) AS name,
uniqExact(movie_id) AS num_movies,
avg(rank) AS avg_rank,
uniqExact(genre) AS unique_genres,
uniqExact(director_name) AS uniq_directors,
max(created_at) AS updated_at
FROM
(
SELECT
imdb.actors.id AS id,
concat(imdb.actors.first_name, ' ', imdb.actors.last_name) AS actor_name,
imdb.movies.id AS movie_id,
imdb.movies.rank AS rank,
genre,
concat(imdb.directors.first_name, ' ', imdb.directors.last_name) AS director_name,
created_at
FROM imdb.actors
INNER JOIN imdb.roles ON imdb.roles.actor_id = imdb.actors.id
LEFT JOIN imdb.movies ON imdb.movies.id = imdb.roles.movie_id
LEFT JOIN imdb.genres ON imdb.genres.movie_id = imdb.movies.id
LEFT JOIN imdb.movie_directors ON imdb.movie_directors.movie_id = imdb.movies.id
LEFT JOIN imdb.directors ON imdb.directors.id = imdb.movie_directors.director_id
)
GROUP BY id
ORDER BY num_movies DESC;
Представление будет выполняться немедленно и каждую минуту после этого, как настроено, чтобы обеспечить отражение обновлений исходной таблицы. Наш предыдущий запрос на получение сводки актеров становится синтаксически проще и значительно быстрее!
SELECT *
FROM imdb.actor_summary
ORDER BY num_movies DESC
LIMIT 5
Предположим, мы добавляем нового актера, "Clicky McClickHouse", в наши исходные данные, который, как оказалось, появился в множестве фильмов!
INSERT INTO imdb.actors VALUES (845466, 'Clicky', 'McClickHouse', 'M');
INSERT INTO imdb.roles SELECT
845466 AS actor_id,
id AS movie_id,
'Himself' AS role,
now() AS created_at
FROM imdb.movies
LIMIT 10000, 910;
Менее чем через 60 секунд наша целевая таблица обновляется, чтобы отразить плодовитую натуру актёрской карьеры Clicky:
SELECT *
FROM imdb.actor_summary
ORDER BY num_movies DESC
LIMIT 5;
