Materialized views

1. Создаёт целевую таблицу с именем модели
2. Создаёт в ClickHouse materialized view с именем <model_name>_mv

-- models/events_mv.sql
{{
    config(
        materialized='materialized_view',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)'
    )
}}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'events') }}
GROUP BY event_date, event_type

--mv1:begin
select a,b,c from {{ source('raw', 'table_1') }}
--mv1:end
union all
--mv2:begin
select a,b,c from {{ source('raw', 'table_2') }}
--mv2:end

{{config(
    materialized='materialized_view',
    engine='MergeTree()',
    order_by='(id)',
    on_schema_change='fail'  # этот параметр
)}}

{{config(
    materialized='materialized_view',
    engine='MergeTree()',
    order_by='(id)',
    catchup=False  # этот параметр
)}}

• Все ресурсы (целевая таблица + MV) используют одну и ту же конфигурацию. Если несколько MV указывают на одну и ту же целевую таблицу, их нужно определять вместе с помощью синтаксиса UNION ALL.
• Этими ресурсами нельзя управлять по отдельности — все они должны описываться в одном и том же файле модели.
• Нельзя легко задать имя для каждой MV.
• Все настройки общие для целевой таблицы и MV, поэтому сложно настраивать каждый ресурс отдельно и понимать, какая конфигурация к какому ресурсу относится.

• Полностью разделённые ресурсы: Теперь каждый ресурс можно определять отдельно, что улучшает читаемость
• Соответствие ресурсов 1:1 между dbt и CH: Теперь вы можете использовать инструменты dbt, чтобы управлять ими и изменять их по отдельности.
• Теперь доступны разные конфигурации: Теперь к каждому из них можно применять отдельную конфигурацию.
• Больше не нужно придерживаться соглашений об именовании: Теперь все ресурсы создаются с использованием заданного вами имени, а не пользовательского имени с добавлением _mv для MV.

• Определение целевой таблицы не вполне естественно для dbt: это не SQL-запрос, который читает из исходной таблицы, поэтому здесь вы лишаетесь проверок dbt. SQL materialized view по-прежнему будет проверяться с помощью утилит dbt, а его совместимость со столбцами целевой таблицы — на уровне CH.
• Мы обнаружили несколько проблем, связанных с ограничениями функции ref(): она нужна, чтобы ссылаться на модели друг из друга, но её можно использовать только для ссылок на вышестоящие модели, а не на нижестоящие. Это создаёт определённые сложности для данной реализации. Мы создали issue в репозитории dbt-core и сейчас обсуждаем с их командой возможные решения (dbt-labs/dbt-core#12319):
  • Когда ref() вызывается внутри блока config, она возвращает текущую модель, а не ту, на которую должна ссылаться. Из-за этого мы не можем определить её в секции config() и вынуждены использовать комментарий, чтобы добавить эту зависимость. Мы придерживаемся того же подхода, что описан в документации dbt: вариант с “—depends_on:”.
  • ref() нам подходит, поскольку заставляет сначала создать целевую таблицу, но на графе зависимостей в сгенерированной документации целевая таблица будет показана как ещё одна вышестоящая зависимость, а не как нижестоящая, из-за чего это немного сложнее понять.
  • unit-test также вынуждает нас задавать данные для целевой таблицы, даже если по задумке чтения из неё быть не должно. Обходной путь здесь простой — оставить данные для этой таблицы пустыми.

{{
    config(
        materialized='table',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)',
        partition_by='toYYYYMM(event_date)'
    )
}}

SELECT
    toDate(now()) AS event_date,
    '' AS event_type,
    toUInt64(0) AS total
WHERE 0  -- Создаёт пустую таблицу с правильной схемой

{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'page_events') }}
GROUP BY event_date, event_type

{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'mobile_events') }}
GROUP BY event_date, event_type

-- models/page_events_aggregator.sql
{{ config(
    materialized='materialized_view',
    catchup=True  -- это значение по умолчанию, поэтому его не нужно указывать явно.
) }}
{{ materialization_target_table(ref('events_daily')) }}
...

1. Будет создана новая временная таблица
2. Будет выполнен INSERT-SELECT с использованием SQL каждой MV
3. Таблицы будут атомарно обменены местами

-- models/events_daily.sql
{{
    config(
        materialized='table',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)',
        repopulate_from_mvs_on_full_refresh=True
    )
}}
...

1. Обновите вызов materialization_target_table()
2. Выполните dbt run --full-refresh -s your_mv_model

• Для materialized view может быть задано catchup=False, или для целевой таблицы может быть задано repopulate_from_mvs_on_full_refresh=False, поэтому при создании materialized views или пересоздании целевой таблицы дозагрузка не выполняется. Это ожидаемое поведение, поэтому, если вы хотите повторно вставить данные с помощью SQL materialized views, убедитесь, что для materialized view установлено catchup=True (это значение по умолчанию) или что для целевой таблицы задано repopulate_from_mvs_on_full_refresh=True. Не включайте оба параметра одновременно, чтобы избежать дубликатов. Подробнее см. в разделе конфигурации.
• Во время выполнения dbt run --full-refresh, если materialized views используют значение по умолчанию catchup=True, целевая таблица будет пересоздана, а MV последовательно повторно вставят данные. Чтобы избежать этой ситуации, см. раздел Полное обновление с явными целевыми таблицами.

• Одновременно могут быть включены и catchup=True для materialized view, и repopulate_from_mvs_on_full_refresh=True для целевой таблицы: оставьте только один из этих параметров в зависимости от того, какие операции вы хотите выполнять. Подробнее см. в разделе конфигурации.
• Целевая таблица определена без WHERE 0: целевая таблица должна создаваться пустой, но внутренний запрос может вставить данные, если не указано WHERE 0. Убедитесь, что это условие добавлено.

SELECT
    name as mv_name,
    replaceRegexpOne(
        create_table_query,
        '.*TO\\s+`?([^`\\s(]+)`?\\.`?([^`\\s(]+)`?.*',
        '\\1.\\2'
    ) AS target_table
FROM system.tables
WHERE database = 'your_schema'
  AND engine = 'MaterializedView'

Таблица <table_name> используется как целевая таблица для materialized view, управляемого dbt. Чтобы предотвратить потерю данных, для mv_on_schema_change по умолчанию устанавливается значение “fail”.

• Модель materialized view корректно задаёт {{ materialization_target_table(ref('your_target')) }}
• В config модели materialized view задано materialized='materialized_view'
• И materialized view, и целевая таблица были выполнены как минимум один раз

-- models/events_daily.sql
{{
    config(
        materialized='table',
        engine='MergeTree()',
        order_by='(event_date, event_type)'
    )
}}

SELECT
    toDate(now()) AS event_date,
    '' AS event_type,
    toUInt64(0) AS total
WHERE 0

• если была определена только одна MV, она должна называться: <old_model_name>_mv
• если было определено несколько MV, каждая должна называться: <old_model_name>_mv_<name_in_comments>

--mv1:begin
select a, b, c from {{ source('raw', 'table_1') }}
--mv1:end
union all
--mv2:begin
select a, b, c from {{ source('raw', 'table_2') }}
--mv2:end

-- models/my_model_mv_mv1.sql
{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

select a, b, c from {{ source('raw', 'table_1') }}

-- models/my_model_mv_mv2.sql
{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

select a, b, c from {{ source('raw', 'table_2') }}

• Поскольку materialized view в ClickHouse работают как триггеры вставки, они захватывают данные только пока существуют. Если materialized view удаляется и создаётся заново (например, во время --full-refresh), любые строки, вставленные в исходную таблицу в этот промежуток, не будут обработаны materialized view. Это состояние называют «слепотой» materialized view.
• Все процессы catchup основаны на операциях INSERT INTO ... SELECT, использующих SQL materialized view, и не зависят от того, как работают сами materialized view. После запуска INSERT новые данные в него уже не попадают, но будут захвачены присоединённой materialized view.

{{
    config(
        materialized='materialized_view',
        engine='MergeTree()',
        order_by='(event_date)',
        refreshable={
            "interval": "EVERY 5 MINUTE",
            "randomize": "1 MINUTE",
            "append": True,
            "depends_on": ['schema.depend_on_model'],
            "depends_on_validation": True
        }
    )
}}

SELECT
    toStartOfDay(event_time) AS event_date,
    count() AS total
FROM {{ source('raw', 'events') }}
GROUP BY event_date

{{
    config(
        materialized='materialized_view',
        refreshable={
            "interval": "EVERY 1 HOUR",
            "append": False
        }
    )
}}
{{ materialization_target_table(ref('events_daily')) }}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'events') }}
GROUP BY event_date, event_type

• При создании в ClickHouse refreshable materialized view (MV) с зависимостью ClickHouse не генерирует ошибку, если указанная зависимость не существует на момент создания. Вместо этого refreshable MV остается в неактивном состоянии и ожидает, пока зависимость не будет удовлетворена, прежде чем начнет обрабатывать обновления или обновляться. Такое поведение является штатным, но оно может приводить к задержкам в доступности данных, если требуемая зависимость не будет своевременно создана. Перед созданием refreshable materialized view следует убедиться, что все зависимости корректно определены и существуют.
• На данный момент фактическая «связь dbt» между mv и его зависимостями отсутствует, поэтому порядок создания не гарантируется.
• Возможность refreshable не тестировалась для нескольких mv, направляющих данные в одну и ту же целевую модель.