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

Елена Дворядкина

Материализованное представление (materialized view) вычисляет промежуточные агрегатные состояния для данных другой таблицы (source table) согласно запросу SELECT, который указывается в описании представления, и сохраняет результаты в свою внутреннюю или отдельную таблицу (target table). Обновление агрегатных состояний происходит автоматически при каждой вставке новых данных в исходную таблицу. Использование материализованных представлений позволяет оптимизировать время выполнения сложных запросов (с объединениями и агрегатами), обращающихся к большому количеству записей — за счет предварительного вычисления итоговых значений сокращается объем данных, которые необходимо обработать для выполнения запросов SELECT.

Создание

Обзор синтаксиса

Чтобы создать материализованное представление, используйте запрос CREATE MATERIALIZED VIEW. Общий синтаксис:

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [<db_name>.]<mat_view_name> [ON CLUSTER <cluster_name>]
[TO [<db_name>.]<target_table>]
[ENGINE = <engine>]
[POPULATE]
AS SELECT
    ...
FROM [<db_name>.]<source_table>
...;

Для описания материализованного представления используются следующие выражения.

TO

Целевая таблица (target table), в которую будут сохраняться данные из материализованного представления

ENGINE

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

POPULATE

Указывает, что при создании материализованного представления в него будут добавлены данные, уже содержащиеся в исходной таблице. Использовать POPULATE не рекомендуется, так как в представление не попадут данные, добавляемые в исходную таблицу во время создания представления. Кроме того, ключевое слово POPULATE не указывается, если описание материализованного представления включает выражение TO — в этом случае представление будет обрабатывать и сохранять только данные, добавляемые в исходную таблицу после создания представления

AS SELECT …​ FROM …​

Запрос, который считывает новые данные, вставляемые в исходную таблицу, и определяет, как эти данные преобразовывать для загрузки в материализованное представление (или целевую таблицу, если она указана). Запрос SELECT может содержать выражения DISTINCT, GROUP BY, ORDER BY, LIMIT — соответствующие преобразования будут выполняться независимо для каждого блока вставляемых данных. Например, при наличии GROUP BY данные будут агрегироваться при вставке в рамках только одного блока вставляемых данных. Далее данные не будут доагрегированы. Исключением является использование табличного движка, указанного в описании представления через ENGINE, который выполняет агрегацию данных самостоятельно (например, SummingMergeTree).

Изменить запрос SELECT после создания материализованного представления можно с помощью запроса ALTER TABLE … MODIFY QUERY — но учитывайте, что в настоящее время этот тип запроса является экспериментальным и имеет некоторые ограничения
ПРИМЕЧАНИЕ

Изменения существующих данных в исходной таблице (например, обновление, удаление и т.д.) не влияют на материализованное представление.

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

Чтобы удалить материализованное представление, выполните запрос DROP VIEW или DROP TABLE.

Пример

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

  1. Создайте таблицу, которая будет хранить исходные данные о просмотрах страниц:

    CREATE TABLE page_views
(
    time DateTime,
    page_name String,
    time_on_page UInt64
)
ENGINE = MergeTree
ORDER BY time;

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

    CREATE TABLE page_views_monthly
(
    page_name String,
    month Date,
    page_view_count UInt64,
    avg_time_on_page AggregateFunction(avg, UInt64)
)
ENGINE = SummingMergeTree()
ORDER BY (page_name, month);

    Для колонки со средним значением времени просмотра страниц используется тип данных AggregateFunction, который позволяет хранить частично агрегированные данные. Табличный движок SummingMergeTree, который используется в примере, требует этот тип данных для всех агрегатов, кроме sum и count. Для работы с материализованными представлениями можно использовать и другие движки таблиц. Например, движок AggregatingMergeTree — этот движок работает только с агрегатными функциями (то есть если бы в запросе выше использовался движок AggregatingMergeTree, для колонки page_view_count также нужно было бы использовать тип AggregateFunction).

  3. Создайте материализованное представление:

    CREATE MATERIALIZED VIEW page_views_monthly_mv
TO page_views_monthly
AS SELECT
    toDate(toStartOfMonth(time)) AS month,
    page_name,
    count() as page_view_count,
    avgState(time_on_page) AS avg_time_on_page
FROM page_views
GROUP BY page_name, month
ORDER BY page_name, month;

    При вставке данных в исходную таблицу page_views материализованное представление преобразует вставляемые данные, как указано в запросе SELECT, и поместит результат в целевую таблицу page_views_monthly. Для сохранения промежуточных агрегатных состояний в столбец типа AggregateFunction (avg_time_on_page) в запросе SELECT должна использоваться соответствующая агрегатная функция с суффиксом State (avgState).

    Если на этом этапе выполнить запрос SHOW TABLES, вывод на экран будет включать созданные таблицы и материализованное представление:

    ┌─name──────────────────┐
│ page_views            │
│ page_views_monthly    │
│ page_views_monthly_mv │
└───────────────────────┘

  4. Заполните исходную таблицу тестовыми данными. Например, сгенерируйте случайным образом 1 миллион просмотров для 4 страниц:

    INSERT INTO page_views SELECT
    toDate('2023-01-01 00:00:00') + (rand() % 182) AS time,
    ['landing', 'products', 'docs', 'about'][toInt32(randBinomial(4, 0.01)) + 1],
    rand() % 15
FROM numbers(1000000);

  5. Сравните скорость выполнения запроса SELECT при выборке данных из исходной таблицы с необработанными входящими данными и таблицы с предварительно агрегированными данными по месяцам.

     
    Сделайте выборку данных из таблицы page_views:

    SELECT
    page_name,
    toDate(toStartOfMonth(time)) AS month,
    count() AS page_view_count,
    round(avg(time_on_page), 2) AS avg_time_on_page
FROM page_views
GROUP BY page_name, month
ORDER BY page_name, month;
    ┌─page_name─┬──────month─┬─page_view_count─┬─avg_time_on_page─┐
│ about     │ 2023-02-01 │               2 │                7 │
│ about     │ 2023-03-01 │               1 │                7 │
│ docs      │ 2023-01-01 │              95 │             7.21 │
│ docs      │ 2023-02-01 │              93 │             6.42 │
│ docs      │ 2023-03-01 │             102 │             6.72 │
│ docs      │ 2023-04-01 │              84 │             7.35 │
│ docs      │ 2023-05-01 │             101 │             7.18 │
│ docs      │ 2023-06-01 │              92 │             7.72 │
│ docs      │ 2023-07-01 │               5 │              8.6 │
│ landing   │ 2023-01-01 │          163242 │             7.01 │
│ landing   │ 2023-02-01 │          147695 │                7 │
│ landing   │ 2023-03-01 │          163930 │             6.99 │
│ landing   │ 2023-04-01 │          158155 │             7.01 │
│ landing   │ 2023-05-01 │          163263 │             6.99 │
│ landing   │ 2023-06-01 │          158840 │                7 │
│ landing   │ 2023-07-01 │            5396 │             7.02 │
│ products  │ 2023-01-01 │            6667 │             7.05 │
│ products  │ 2023-02-01 │            6099 │             6.99 │
│ products  │ 2023-03-01 │            6589 │             7.02 │
│ products  │ 2023-04-01 │            6391 │             7.01 │
│ products  │ 2023-05-01 │            6428 │             6.98 │
│ products  │ 2023-06-01 │            6517 │             7.04 │
│ products  │ 2023-07-01 │             213 │             7.48 │
└───────────┴────────────┴─────────────────┴──────────────────┘

23 rows in set. Elapsed: 0.023 sec. Processed 1.00 million rows, 28.04 MB (43.43 million rows/s., 1.22 GB/s.)

    Сделайте выборку из таблицы page_views_monthly — чтение данных из этой таблицы выполнится в 7 раз быстрее, чем из исходной таблицы (на большем объеме данных разница будет еще более заметна). Обратите внимание, в запросе SELECT используется та же агрегатная функция, что и при вставке данных, но с суффиксом Merge — это необходимо, чтобы объединить множество промежуточных агрегатных состояний и вернуть полную агрегацию данных (см. AggregateFunction):

    SELECT
    page_name,
    month,
    sum(page_view_count) AS page_view_count,
    round(avgMerge(avg_time_on_page), 2) AS avg_time_on_page
FROM page_views_monthly
GROUP BY page_name, month
ORDER BY page_name, month;
    ┌─page_name─┬──────month─┬─page_view_count─┬─avg_time_on_page─┐
│ about     │ 2023-02-01 │               2 │                7 │
│ about     │ 2023-03-01 │               1 │                7 │
│ docs      │ 2023-01-01 │              95 │             7.21 │
│ docs      │ 2023-02-01 │              93 │             6.42 │
│ docs      │ 2023-03-01 │             102 │             6.72 │
│ docs      │ 2023-04-01 │              84 │             7.35 │
│ docs      │ 2023-05-01 │             101 │             7.18 │
│ docs      │ 2023-06-01 │              92 │             7.72 │
│ docs      │ 2023-07-01 │               5 │              8.6 │
│ landing   │ 2023-01-01 │          163242 │             7.01 │
│ landing   │ 2023-02-01 │          147695 │                7 │
│ landing   │ 2023-03-01 │          163930 │             6.99 │
│ landing   │ 2023-04-01 │          158155 │             7.01 │
│ landing   │ 2023-05-01 │          163263 │             6.99 │
│ landing   │ 2023-06-01 │          158840 │                7 │
│ landing   │ 2023-07-01 │            5396 │             7.02 │
│ products  │ 2023-01-01 │            6667 │             7.05 │
│ products  │ 2023-02-01 │            6099 │             6.99 │
│ products  │ 2023-03-01 │            6589 │             7.02 │
│ products  │ 2023-04-01 │            6391 │             7.01 │
│ products  │ 2023-05-01 │            6428 │             6.98 │
│ products  │ 2023-06-01 │            6517 │             7.04 │
│ products  │ 2023-07-01 │             213 │             7.48 │
└───────────┴────────────┴─────────────────┴──────────────────┘

23 rows in set. Elapsed: 0.003 sec.

Изменение

Запрос SELECT, который был указан при создании материализованного представления, можно изменить с помощью выражения ALTER TABLE…MODIFY QUERY. Эта команда не меняет структуру и описания столбцов внутренней таблицы материализованного представления, поэтому не рекомендуется ее использование для изменения материализованных представлений, созданных без указания целевой таблицы через выражение TO.

Перед выполнением команды ALTER TABLE…MODIFY QUERY, активируйте опцию: SET allow_experimental_alter_materialized_view_structure=1.

Пример

  1. С помощью выражения ALTER TABLE…​ADD COLUMN добавьте столбец browser в исходную таблицу page_views и целевую таблицу page_views_monthly (столбцы материализованного представления и целевой таблицы не обязательно должны совпадать, поэтому изменение структуры целевой таблицы не нарушит вставку данных):

    ALTER TABLE page_views ADD COLUMN browser String;
    ALTER TABLE page_views_monthly ADD COLUMN browser String after month,
MODIFY ORDER BY (page_name, month, browser);

  2. Вставьте данные в исходную таблицу:

    INSERT INTO page_views SELECT
    toDate('2023-01-01 00:00:00') + (rand() % 182) AS time,
    ['landing', 'products', 'docs', 'about'][toInt32(randBinomial(4, 0.01)) + 1],
    rand() % 15,
    ['firefox', 'safary', 'chrome'][(number % 3) + 1]
FROM numbers(1000000);

    Если на этом этапе сделать выборку данных из целевой таблицы, столбец browser будет пустым:

    SELECT
    page_name,
    month,
    browser,
    sum(page_view_count) AS page_view_count,
    round(avgMerge(avg_time_on_page), 2) AS avg_time_on_page
FROM page_views_monthly
GROUP BY page_name, month, browser
ORDER BY page_name, month ASC, browser;
    ┌─page_name─┬──────month─┬─browser─┬─page_view_count─┬─avg_time_on_page─┐
│ about     │ 2023-01-01 │         │               1 │               14 │
│ about     │ 2023-02-01 │         │               3 │             6.67 │
│ about     │ 2023-03-01 │         │               2 │              8.5 │
│ about     │ 2023-05-01 │         │               1 │               12 │
│ docs      │ 2023-01-01 │         │             203 │             7.13 │
│ docs      │ 2023-02-01 │         │             175 │             6.91 │
│ docs      │ 2023-03-01 │         │             185 │             7.21 │
│ docs      │ 2023-04-01 │         │             175 │             7.15 │
│ docs      │ 2023-05-01 │         │             182 │             7.53 │
│ docs      │ 2023-06-01 │         │             190 │             7.94 │
│ docs      │ 2023-07-01 │         │               8 │             9.88 │
│ landing   │ 2023-01-01 │         │          326986 │             7.01 │
│ landing   │ 2023-02-01 │         │          295510 │             6.99 │
│ landing   │ 2023-03-01 │         │          327021 │                7 │
│ landing   │ 2023-04-01 │         │          316761 │                7 │
│ landing   │ 2023-05-01 │         │          327207 │                7 │
│ landing   │ 2023-06-01 │         │          316732 │             6.99 │
│ landing   │ 2023-07-01 │         │           10702 │             7.05 │
│ products  │ 2023-01-01 │         │           13224 │             7.07 │
│ products  │ 2023-02-01 │         │           12103 │             7.02 │
│ products  │ 2023-03-01 │         │           13225 │             6.99 │
│ products  │ 2023-04-01 │         │           12807 │             6.98 │
│ products  │ 2023-05-01 │         │           13156 │             6.95 │
│ products  │ 2023-06-01 │         │           13000 │             7.01 │
│ products  │ 2023-07-01 │         │             441 │             6.95 │
└───────────┴────────────┴─────────┴─────────────────┴──────────────────┘

  3. Чтобы данные из нового столбца исходной таблицы попадали в соответствующий столбец целевой таблицы, необходимо изменить запрос SELECT материализованного представления. Для этого выполните команду ALTER TABLE…​MODIFY QUERY:

    SET allow_experimental_alter_materialized_view_structure=1;
    ALTER TABLE page_views_monthly_mv
MODIFY QUERY
SELECT
    toDate(toStartOfMonth(time)) AS month,
    page_name,
    browser,
    count() AS page_view_count,
    avgState(time_on_page) AS avg_time_on_page
FROM page_views
GROUP BY page_name, month, browser
ORDER BY page_name, month, browser;

  4. Добавьте еще раз данные в исходную таблицу:

    INSERT INTO page_views SELECT
    toDate('2023-01-01 00:00:00') + (rand() % 182) AS time,
    ['landing', 'products', 'docs', 'about'][toInt32(randBinomial(4, 0.01)) + 1],
    rand() % 15,
    ['firefox', 'safary', 'chrome'][(number % 3) + 1]
FROM numbers(1000000);

    Убедитесь, что данные из столбца browser теперь учитываются в целевой таблице:

    SELECT
    page_name,
    month,
    browser,
    sum(page_view_count) AS page_view_count,
    round(avgMerge(avg_time_on_page), 2) AS avg_time_on_page
FROM page_views_monthly
GROUP BY page_name, month, browser
ORDER BY page_name, month ASC, browser;
    ┌─page_name─┬──────month─┬─browser─┬─page_view_count─┬─avg_time_on_page─┐
│ about     │ 2023-01-01 │         │               1 │               14 │
│ about     │ 2023-02-01 │         │               3 │             6.67 │
│ about     │ 2023-02-01 │ safary  │               1 │                7 │
│ about     │ 2023-03-01 │         │               2 │              8.5 │
│ about     │ 2023-04-01 │ firefox │               1 │                1 │
│ about     │ 2023-05-01 │         │               1 │               12 │
│ about     │ 2023-05-01 │ chrome  │               1 │               14 │
│ about     │ 2023-05-01 │ safary  │               1 │                6 │
│ docs      │ 2023-01-01 │         │             203 │             7.13 │
│ docs      │ 2023-01-01 │ chrome  │              37 │             6.92 │
│ docs      │ 2023-01-01 │ firefox │              38 │             5.58 │
│ docs      │ 2023-01-01 │ safary  │              29 │             5.79 │
│ docs      │ 2023-02-01 │         │             175 │             6.91 │
│ docs      │ 2023-02-01 │ chrome  │              30 │             6.77 │
│ docs      │ 2023-02-01 │ firefox │              26 │             6.65 │
│ docs      │ 2023-02-01 │ safary  │              18 │             5.89 │
│ docs      │ 2023-03-01 │         │             185 │             7.21 │
│ docs      │ 2023-03-01 │ chrome  │              42 │             7.83 │
│ docs      │ 2023-03-01 │ firefox │              31 │             7.65 │
│ docs      │ 2023-03-01 │ safary  │              33 │             5.88 │
│ docs      │ 2023-04-01 │         │             175 │             7.15 │
│ docs      │ 2023-04-01 │ chrome  │              28 │             8.25 │
│ docs      │ 2023-04-01 │ firefox │              25 │             6.32 │
│ docs      │ 2023-04-01 │ safary  │              26 │             6.58 │
│ docs      │ 2023-05-01 │         │             182 │             7.53 │
│ docs      │ 2023-05-01 │ chrome  │              32 │             7.03 │
│ docs      │ 2023-05-01 │ firefox │              20 │             7.45 │
│ docs      │ 2023-05-01 │ safary  │              34 │             6.59 │
│ docs      │ 2023-06-01 │         │             190 │             7.94 │
│ docs      │ 2023-06-01 │ chrome  │              35 │             8.37 │
│ docs      │ 2023-06-01 │ firefox │              29 │             6.69 │
│ docs      │ 2023-06-01 │ safary  │              27 │             7.15 │
│ docs      │ 2023-07-01 │         │               8 │             9.88 │
│ docs      │ 2023-07-01 │ chrome  │               2 │                2 │
│ docs      │ 2023-07-01 │ firefox │               1 │                1 │
│ docs      │ 2023-07-01 │ safary  │               1 │                1 │
│ landing   │ 2023-01-01 │         │          326986 │             7.01 │
│ landing   │ 2023-01-01 │ chrome  │           54763 │             6.99 │
│ landing   │ 2023-01-01 │ firefox │           54665 │             7.02 │
│ landing   │ 2023-01-01 │ safary  │           54294 │                7 │
│ landing   │ 2023-02-01 │         │          295510 │             6.99 │
│ landing   │ 2023-02-01 │ chrome  │           48944 │             7.03 │
│ landing   │ 2023-02-01 │ firefox │           49031 │             7.03 │
│ landing   │ 2023-02-01 │ safary  │           49298 │                7 │
│ landing   │ 2023-03-01 │         │          327021 │                7 │
│ landing   │ 2023-03-01 │ chrome  │           54381 │             6.98 │
│ landing   │ 2023-03-01 │ firefox │           54433 │             7.03 │
│ landing   │ 2023-03-01 │ safary  │           54556 │             7.01 │
│ landing   │ 2023-04-01 │         │          316761 │                7 │
│ landing   │ 2023-04-01 │ chrome  │           52931 │             6.95 │
│ landing   │ 2023-04-01 │ firefox │           52706 │             6.99 │
│ landing   │ 2023-04-01 │ safary  │           53075 │             6.99 │
│ landing   │ 2023-05-01 │         │          327207 │                7 │
│ landing   │ 2023-05-01 │ chrome  │           54538 │             6.99 │
│ landing   │ 2023-05-01 │ firefox │           54733 │             7.02 │
│ landing   │ 2023-05-01 │ safary  │           54618 │             7.02 │
│ landing   │ 2023-06-01 │         │          316732 │             6.99 │
│ landing   │ 2023-06-01 │ chrome  │           52763 │             6.97 │
│ landing   │ 2023-06-01 │ firefox │           52955 │                7 │
│ landing   │ 2023-06-01 │ safary  │           52795 │             7.01 │
│ landing   │ 2023-07-01 │         │           10702 │             7.05 │
│ landing   │ 2023-07-01 │ chrome  │            1762 │             7.08 │
│ landing   │ 2023-07-01 │ firefox │            1747 │             6.98 │
│ landing   │ 2023-07-01 │ safary  │            1767 │             7.09 │
│ products  │ 2023-01-01 │         │           13224 │             7.07 │
│ products  │ 2023-01-01 │ chrome  │            2208 │             7.04 │
│ products  │ 2023-01-01 │ firefox │            2210 │             6.99 │
│ products  │ 2023-01-01 │ safary  │            2273 │             6.99 │
│ products  │ 2023-02-01 │         │           12103 │             7.02 │
│ products  │ 2023-02-01 │ chrome  │            1970 │              7.1 │
│ products  │ 2023-02-01 │ firefox │            1978 │             6.88 │
│ products  │ 2023-02-01 │ safary  │            1870 │             7.01 │
│ products  │ 2023-03-01 │         │           13225 │             6.99 │
│ products  │ 2023-03-01 │ chrome  │            2189 │             6.97 │
│ products  │ 2023-03-01 │ firefox │            2186 │             6.94 │
│ products  │ 2023-03-01 │ safary  │            2251 │             7.18 │
│ products  │ 2023-04-01 │         │           12807 │             6.98 │
│ products  │ 2023-04-01 │ chrome  │            2252 │             6.91 │
│ products  │ 2023-04-01 │ firefox │            2210 │              7.1 │
│ products  │ 2023-04-01 │ safary  │            2089 │             7.04 │
│ products  │ 2023-05-01 │         │           13156 │             6.95 │
│ products  │ 2023-05-01 │ chrome  │            2214 │                7 │
│ products  │ 2023-05-01 │ firefox │            2188 │             6.94 │
│ products  │ 2023-05-01 │ safary  │            2151 │             6.98 │
│ products  │ 2023-06-01 │         │           13000 │             7.01 │
│ products  │ 2023-06-01 │ chrome  │            2134 │             6.89 │
│ products  │ 2023-06-01 │ firefox │            2054 │             7.18 │
│ products  │ 2023-06-01 │ safary  │            2062 │             6.91 │
│ products  │ 2023-07-01 │         │             441 │             6.95 │
│ products  │ 2023-07-01 │ chrome  │              77 │             6.65 │
│ products  │ 2023-07-01 │ firefox │              67 │             7.27 │
│ products  │ 2023-07-01 │ safary  │              64 │             7.16 │
└───────────┴────────────┴─────────┴─────────────────┴──────────────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней