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

Этот пример демонстрирует, как создать материализованное представление, а затем как каскадировать второе материализованное представление к первому. На этой странице вы увидите, как это сделать, многие возможности и ограничения. Разные случаи использования могут быть решены, создавая материализованное представление, используя второе материализованное представление в качестве источника.

Пример:

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

Наша цель

Нам нужны данные, агрегированные по месяцам для каждого доменного имени,
Нам также нужны данные, агрегированные по годам для каждого доменного имени.

Вы можете выбрать один из этих вариантов:

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

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

Исходная таблица для материализованных представлений

Создайте исходную таблицу, поскольку наши цели связаны с отчетностью по агрегированным данным, а не по отдельным строкам, мы можем разобрать их, передать информацию в материализованные представления и удалить входящие данные. Это соответствует нашим целям и экономит место, поэтому мы будем использовать движок таблицы Null.

CREATE DATABASE IF NOT EXISTS analytics;

CREATE TABLE analytics.hourly_data
(
    `domain_name` String,
    `event_time` DateTime,
    `count_views` UInt64
)
ENGINE = Null

примечание

Вы можете создать материализованное представление на таблице Null. Таким образом, данные, записанные в таблицу, повлияют на представление, но исходные необработанные данные все равно будут отброшены.

Ежемесячная агрегированная таблица и материализованное представление

Для первого материализованного представления нам нужно создать таблицу Target, для этого примера она будет analytics.monthly_aggregated_data, и мы будем хранить сумму просмотров по месяцам и доменным именам.

CREATE TABLE analytics.monthly_aggregated_data
(
    `domain_name` String,
    `month` Date,
    `sumCountViews` AggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (domain_name, month)

Материализованное представление, которое будет передавать данные в целевую таблицу, будет выглядеть следующим образом:

CREATE MATERIALIZED VIEW analytics.monthly_aggregated_data_mv
TO analytics.monthly_aggregated_data
AS
SELECT
    toDate(toStartOfMonth(event_time)) AS month,
    domain_name,
    sumState(count_views) AS sumCountViews
FROM analytics.hourly_data
GROUP BY
    domain_name,
    month

Ежегодная агрегированная таблица и материализованное представление

Теперь мы создадим второе материализованное представление, которое будет связано с нашей предыдущей целевой таблицей monthly_aggregated_data.

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

CREATE TABLE analytics.year_aggregated_data
(
    `domain_name` String,
    `year` UInt16,
    `sumCountViews` UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (domain_name, year)

Этот шаг определяет каскад. Оператор FROM будет использовать таблицу monthly_aggregated_data, это означает, что поток данных будет следующим:

Данные поступают в таблицу hourly_data.
ClickHouse передаст полученные данные в первое материализованное представление monthly_aggregated_data,
Наконец, данные, полученные на шаге 2, будут переданы в year_aggregated_data.

CREATE MATERIALIZED VIEW analytics.year_aggregated_data_mv
TO analytics.year_aggregated_data
AS
SELECT
    toYear(toStartOfYear(month)) AS year,
    domain_name,
    sumMerge(sumCountViews) as sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
    domain_name,
    year

примечание

Общее заблуждение при работе с материализованными представлениями заключается в том, что данные считываются из таблицы. Это не так, как работают материализованные представления; передаваемые данные — это вставленный блок, а не конечный результат в вашей таблице.

Представьте, что в этом примере движок, используемый в monthly_aggregated_data, — это CollapsingMergeTree, данные, переданные во второе материализованное представление year_aggregated_data_mv, не будут конечным результатом сложенной таблицы; они будут передавать блок данных с полями, определенными в SELECT ... GROUP BY.

Если вы используете CollapsingMergeTree, ReplacingMergeTree или даже SummingMergeTree и планируете создать каскадное материализованное представление, вам необходимо понимать описанные здесь ограничения.

Пример данных

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

INSERT INTO analytics.hourly_data (domain_name, event_time, count_views)
VALUES ('clickhouse.com', '2019-01-01 10:00:00', 1),
       ('clickhouse.com', '2019-02-02 00:00:00', 2),
       ('clickhouse.com', '2019-02-01 00:00:00', 3),
       ('clickhouse.com', '2020-01-01 00:00:00', 6);

Если вы выполните SELECT содержимого analytics.hourly_data, вы увидите следующее, потому что движок таблицы — Null, но данные были обработаны.

SELECT * FROM analytics.hourly_data

Ok.

0 rows in set. Elapsed: 0.002 sec.

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

Результаты

Если вы попробуете выполнить запрос к целевой таблице, выбрав поле sumCountViews, вы увидите двоичное представление (в некоторых терминалах), так как значение не хранится как число, а как тип AggregateFunction. Для получения окончательного результата агрегации вам следует использовать суффикс -Merge.

Вы можете увидеть специальные символы, хранящиеся в AggregateFunction, с помощью следующего запроса:

SELECT sumCountViews FROM analytics.monthly_aggregated_data

┌─sumCountViews─┐
│               │
│               │
│               │
└───────────────┘

3 rows in set. Elapsed: 0.003 sec.

Вместо этого давайте попробуем использовать суффикс Merge, чтобы получить значение sumCountViews:

SELECT
   sumMerge(sumCountViews) as sumCountViews
FROM analytics.monthly_aggregated_data;

┌─sumCountViews─┐
│            12 │
└───────────────┘

1 row in set. Elapsed: 0.003 sec.

В AggregatingMergeTree мы определили AggregateFunction как sum, поэтому мы можем использовать sumMerge. Когда мы используем функцию avg на AggregateFunction, мы будем использовать avgMerge и так далее.

SELECT
    month,
    domain_name,
    sumMerge(sumCountViews) as sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
    domain_name,
    month

Теперь мы можем проверить, что материализованные представления отвечают нашим целям.

Теперь, когда у нас есть данные, хранящиеся в целевой таблице monthly_aggregated_data, мы можем получить данные, агрегированные по месяцам для каждого доменного имени:

SELECT
   month,
   domain_name,
   sumMerge(sumCountViews) as sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
   domain_name,
   month

┌──────month─┬─domain_name────┬─sumCountViews─┐
│ 2020-01-01 │ clickhouse.com │             6 │
│ 2019-01-01 │ clickhouse.com │             1 │
│ 2019-02-01 │ clickhouse.com │             5 │
└────────────┴────────────────┴───────────────┘

3 rows in set. Elapsed: 0.004 sec.

Данные, агрегированные по годам для каждого доменного имени:

SELECT
   year,
   domain_name,
   sum(sumCountViews)
FROM analytics.year_aggregated_data
GROUP BY
   domain_name,
   year

┌─year─┬─domain_name────┬─sum(sumCountViews)─┐
│ 2019 │ clickhouse.com │                  6 │
│ 2020 │ clickhouse.com │                  6 │
└──────┴────────────────┴────────────────────┘

2 rows in set. Elapsed: 0.004 sec.

Объединение нескольких исходных таблиц в одно целевое представление

Материализованные представления также могут использоваться для комбинирования нескольких исходных таблиц в одну и ту же целевую таблицу. Это полезно для создания материализованного представления, сходного по логике с UNION ALL.

Сначала создайте две исходные таблицы, представляющие разные наборы метрик:

CREATE TABLE analytics.impressions
(
    `event_time` DateTime,
    `domain_name` String
) ENGINE = MergeTree ORDER BY (domain_name, event_time)
;

CREATE TABLE analytics.clicks
(
    `event_time` DateTime,
    `domain_name` String
) ENGINE = MergeTree ORDER BY (domain_name, event_time)
;

Затем создайте целевую таблицу с объединенным набором метрик:

CREATE TABLE analytics.daily_overview
(
    `on_date` Date,
    `domain_name` String,
    `impressions` SimpleAggregateFunction(sum, UInt64),
    `clicks` SimpleAggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree ORDER BY (on_date, domain_name)

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

CREATE MATERIALIZED VIEW analytics.daily_impressions_mv
TO analytics.daily_overview
AS
SELECT
    toDate(event_time) AS on_date,
    domain_name,
    count() AS impressions,
    0 clicks         ---<<<--- если вы это пропустите, это будет то же самое 0
FROM
    analytics.impressions
GROUP BY
    toDate(event_time) AS on_date,
    domain_name
;

CREATE MATERIALIZED VIEW analytics.daily_clicks_mv
TO analytics.daily_overview
AS
SELECT
    toDate(event_time) AS on_date,
    domain_name,
    count() AS clicks,
    0 impressions    ---<<<--- если вы это пропустите, это будет то же самое 0
FROM
    analytics.clicks
GROUP BY
    toDate(event_time) AS on_date,
    domain_name
;

Теперь, когда вы вставляете значения, эти значения будут агрегироваться по соответствующим столбцам в целевой таблице:

INSERT INTO analytics.impressions (domain_name, event_time)
VALUES ('clickhouse.com', '2019-01-01 00:00:00'),
       ('clickhouse.com', '2019-01-01 12:00:00'),
       ('clickhouse.com', '2019-02-01 00:00:00'),
       ('clickhouse.com', '2019-03-01 00:00:00')
;

INSERT INTO analytics.clicks (domain_name, event_time)
VALUES ('clickhouse.com', '2019-01-01 00:00:00'),
       ('clickhouse.com', '2019-01-01 12:00:00'),
       ('clickhouse.com', '2019-03-01 00:00:00')
;

Объединенные показы и клики вместе в целевой таблице:

SELECT
    on_date,
    domain_name,
    sum(impressions) AS impressions,
    sum(clicks) AS clicks
FROM
    analytics.daily_overview
GROUP BY
    on_date,
    domain_name
;

Этот запрос должен вернуть что-то вроде:

┌────on_date─┬─domain_name────┬─impressions─┬─clicks─┐
│ 2019-01-01 │ clickhouse.com │           2 │      2 │
│ 2019-03-01 │ clickhouse.com │           1 │      1 │
│ 2019-02-01 │ clickhouse.com │           1 │      0 │
└────────────┴────────────────┴─────────────┴────────┘

3 rows in set. Elapsed: 0.018 sec.

Исходная таблица для материализованных представлений​

Ежемесячная агрегированная таблица и материализованное представление​

Ежегодная агрегированная таблица и материализованное представление​

Пример данных​

Результаты​

Объединение нескольких исходных таблиц в одно целевое представление​