Vues matérialisées

La matérialisation materialized_view doit être un SELECT à partir d’une table source existante. Contrairement à PostgreSQL, une vue matérialisée dans ClickHouse n’est pas « statique » (et ne dispose pas d’opération REFRESH correspondante). Elle agit plutôt comme un déclencheur à l’insertion : elle insère de nouvelles lignes dans une table cible en appliquant la transformation SELECT définie aux lignes insérées dans la table source. Consultez la documentation ClickHouse sur les vues matérialisées pour en savoir plus sur le fonctionnement des vues matérialisées dans ClickHouse.

Lorsque vous utilisez la matérialisation materialized_view, dbt-clickhouse doit créer à la fois une vue matérialisée et une table cible dans laquelle les lignes transformées sont insérées. Il existe deux façons de gérer la table cible :

L’approche choisie a une incidence sur la gestion des changements de schéma, des rafraîchissements complets et des configurations avec plusieurs MV. Les sections suivantes décrivent chaque approche en détail.

Il s’agit du comportement par défaut. Lorsque vous définissez un modèle materialized_view, l’adapter va :

1. Créer une table cible portant le nom du modèle
2. Créer une vue matérialisée ClickHouse nommée <model_name>_mv

Le schéma de la table cible est inféré à partir des colonnes de l’instruction SELECT de la MV. Toutes les ressources (table cible + MV) partagent la même configuration de modèle.

-- 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

Voir le fichier de test pour d’autres exemples.

ClickHouse permet à plusieurs vues matérialisées d’écrire des enregistrements dans la même table cible. Pour prendre cela en charge dans dbt-clickhouse avec l’approche de cible implicite, vous pouvez construire un UNION dans votre fichier de modèle, en encadrant le SQL de chaque vue matérialisée par des commentaires de la forme --my_mv_name:begin et --my_mv_name:end.

Par exemple, ce qui suit créera deux vues matérialisées qui écrivent toutes deux des données dans la même table de destination du modèle. Les noms des vues matérialisées auront la forme <model_name>_mv1 et <model_name>_mv2 :

--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

À partir de la version 1.9.8 de dbt-clickhouse, vous pouvez contrôler la façon dont le schéma de la table cible évolue lorsque dbt run rencontre des colonnes différentes dans le SQL de la MV.

{{config(
    materialized='materialized_view',
    engine='MergeTree()',
    order_by='(id)',
    on_schema_change='fail'  # this setting
)}}

Par défaut, dbt n’applique aucune modification à la table cible (valeur du paramètre ignore), mais vous pouvez modifier ce paramètre pour adopter le même comportement que la config on_schema_change dans les modèles incrémentiels.

Vous pouvez également utiliser ce paramètre comme mécanisme de sécurité. Si vous le définissez sur fail, le build échouera si les colonnes du SQL de la MV diffèrent de celles de la table cible créée lors du premier dbt run.

Par défaut, lors de la création ou de la recréation d'une vue matérialisée (MV), la table cible est d'abord remplie avec les données historiques avant la création de la MV elle-même (catchup=True). Vous pouvez désactiver ce comportement en définissant la config catchup sur False.

{{config(
    materialized='materialized_view',
    engine='MergeTree()',
    order_by='(id)',
    catchup=False  # this setting
)}}

Par défaut, dbt-clickhouse crée et gère à la fois la table cible et les vues matérialisées au sein d’un même modèle (l’approche de cible implicite décrite ci-dessus). Cette approche présente certaines limites :

• Toutes les ressources (table cible + MV) partagent la même configuration. Si plusieurs MV pointent vers la même table cible, elles doivent être définies ensemble à l’aide de la syntaxe UNION ALL.
• Aucune de ces ressources ne peut être gérée séparément ; elles doivent toutes être administrées à l’aide du même fichier de modèle.
• Vous ne pouvez pas facilement contrôler le nom de chaque MV.
• Tous les paramètres sont partagés entre la table cible et les MV, ce qui complique la configuration individuelle de chaque ressource et l’identification de la configuration associée à chacune.

La fonctionnalité de cible explicite vous permet de définir séparément la table cible comme une matérialisation table classique, puis d’y faire référence depuis vos modèles de vue matérialisée.

• Ressources entièrement séparées : chaque ressource peut désormais être définie séparément, ce qui améliore la lisibilité
• Ressources 1:1 entre dbt et CH : vous pouvez désormais utiliser les outils dbt pour les gérer et les faire évoluer séparément.
• Différentes configurations désormais disponibles : une configuration différente peut désormais être appliquée à chacune.
• Plus besoin de respecter les conventions de nommage : toutes les ressources sont désormais créées avec le nom que vous leur donnez, et non avec un nom personnalisé suffixé par _mv pour les MV.

• La définition de la table cible n’est pas naturelle pour dbt : il ne s’agit pas d’une requête SQL qui lira depuis une table source, vous perdez donc ici les validations dbt. Le SQL de la MV sera tout de même validé à l’aide des utilitaires dbt, et sa compatibilité avec les colonnes de la table cible sera validée au niveau de CH.
• Nous avons identifié quelques problèmes liés aux limitations de la fonction ref() : nous devons l’utiliser pour référencer les modèles entre eux, mais elle ne peut servir qu’à référencer des modèles upstream, pas downstream. Cela pose certains problèmes pour cette implémentation. Nous avons créé une issue dans le dépôt dbt-core et échangeons actuellement avec eux pour chercher des solutions possibles (dbt-labs/dbt-core#12319) :
  • Lorsque ref() est appelé depuis l’intérieur du bloc config, il renvoie le modèle courant, et non celui partagé. Cela nous empêche de le définir dans la section config(), et nous oblige à utiliser un commentaire pour ajouter cette dépendance. Nous suivons le même modèle que celui défini dans la documentation dbt avec l’approche "–depends_on:".
  • ref() fonctionne dans notre cas car il force la création de la table cible en premier, mais dans le graphe des dépendances de la documentation générée, la table cible sera représentée comme une autre dépendance upstream, et non downstream, ce qui le rend un peu plus difficile à comprendre.
  • unit-test nous oblige également à définir des données pour la table cible, même si l’idée n’est pas de la lire. Le contournement consiste simplement à laisser les données de cette table vides.

Étape 1 : Définissez la table cible en tant que modèle de table standard

Modèle events_daily.sql:

{{
    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  -- Creates empty table with correct schema

C’est la solution de contournement que nous mentionnons dans la section sur les limitations. Vous risquez d’y perdre certaines validations dbt, mais le schéma sera tout de même vérifié au niveau de ClickHouse.

Étape 2 : Définir des vues matérialisées qui pointent vers la table cible

Par exemple, vous pouvez définir différentes vues matérialisées dans différents modèles de cette manière, y compris en les faisant pointer vers la même table cible. Notez le nouvel appel de macro {{ materialization_target_table(ref('events_daily')) }}, qui configure la table cible pour la vue matérialisée.

Modèle page_events_aggregator.sql:

{{ 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

Modèle mobile_events_aggregator.sql :

{{ 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

Lorsque vous utilisez des tables cibles explicites, en plus des configurations générales de matérialisation et des configurations propres à la table, les configurations suivantes s'appliquent :

Pour la table cible (materialized='table') :

Pour la vue matérialisée (materialized='materialized_view') :

Lors de l’utilisation de --full-refresh, les tables cibles explicites seront recréées (vous risquez donc de perdre des données si une ingestion est en cours pendant ce processus). Le comportement variera selon vos configurations :

Option 1 : comportement par défaut de --full-refresh. Tout est recréé, mais pendant la recréation des MV, la table cible sera vide ou partiellement chargée.

Tout est supprimé puis recréé. Si vous souhaitez réinsérer les données à l’aide du SQL des MV, conservez le paramètre catchup=True :

-- models/page_events_aggregator.sql
{{ config(
    materialized='materialized_view',
    catchup=True  -- this is the default value so you don't need to actually set it.
) }}
{{ materialization_target_table(ref('events_daily')) }}
...

Option 2 : Je veux recréer la table cible et je ne veux pas voir ma table vide pendant la recréation des MV.

Si vous devez d'abord mettre à jour le SQL des MV, vous pouvez y définir catchup=False, puis exécuter un dbt run ou un dbt run --full-refresh sur les MV. Assurez-vous que les MV sont créées avant d'exécuter --full-refresh sur la table cible, car cette opération s'appuie sur les définitions des MV dans ClickHouse.

Définissez repopulate_from_mvs_on_full_refresh=True dans le model de la table cible. Lors d'un dbt run --full-refresh, cela va :

1. Créer une nouvelle table temporaire
2. Exécuter INSERT-SELECT en utilisant le SQL de chaque MV
3. Permuter les tables de manière atomique

Ainsi, vous ne verrez pas votre table vide pendant la recréation des MV.

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

Vous ne pouvez pas modifier la table cible d’une MV sans --full-refresh. Si vous essayez d’exécuter un dbt run normal après avoir modifié la référence materialization_target_table(), l’exécution échouera avec un message d’erreur indiquant que la cible a changé.

Pour modifier la cible :

1. Mettez à jour l’appel à materialization_target_table()
2. Exécutez dbt run --full-refresh -s your_mv_model

Cela peut se produire pour plusieurs raisons :

• Les vues matérialisées peuvent être configurées avec catchup=False, ou la table cible avec repopulate_from_mvs_on_full_refresh=False ; dans ce cas, aucun backfill n’est effectué lors de la création des vues matérialisées ou de la recréation de la table cible. C’est le comportement attendu. Si vous souhaitez réinsérer les données à l’aide du SQL des vues matérialisées, assurez-vous de définir catchup=True dans la vue matérialisée (c’est la valeur par défaut) ou repopulate_from_mvs_on_full_refresh=True dans la table cible. Veillez à ne pas activer les deux en même temps afin d’éviter les doublons. Consultez la section de configuration pour plus de détails.
• Lors de l’exécution de dbt run --full-refresh, si les vues matérialisées utilisent la valeur par défaut catchup=True, la cible sera recréée et les MV réinséreront les données de manière séquentielle. Pour éviter cette situation, consultez la section Full refresh with explicit targets.

repopulate_from_mvs_on_full_refresh=True utilise le SQL MV existant déjà défini dans ClickHouse. Pour garantir que la nouvelle définition de la vue matérialisée est bien utilisée, exécutez un dbt run pour chaque vue matérialisée avant d’exécuter un dbt run --full-refresh sur la table cible.

Raisons possibles :

• catchup=True sur les vues matérialisées et repopulate_from_mvs_on_full_refresh=True sur la table cible peuvent être activés tous les deux : n’en gardez qu’un seul, selon les opérations que vous souhaitez exécuter. Consultez la section de configuration pour plus de détails.
• La table cible n’est pas définie avec WHERE 0 : elle doit être créée vide, mais la requête interne peut insérer des données si WHERE 0 n’est pas inclus. Assurez-vous que la clause est bien présente.

Certaines lignes de la table source sont absentes de la table cible après l’exécution d’un dbt run --full-refresh. Les vues matérialisées de ClickHouse agissent comme des déclencheurs à l’insertion — elles ne capturent les données que tant qu’elles existent. Lors d’un full refresh, il y a un court laps de temps pendant lequel la MV est supprimée puis recréée (la « fenêtre aveugle »). Les lignes insérées dans la table source pendant cette fenêtre ne sont donc pas capturées. Consultez la section Comportement lors d’une ingestion active pour plus de détails.

Interrogez system.tables pour voir dans quelle table une vue matérialisée écrit :

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'

Lors d’une exécution de dbt, recherchez ce message dans le log :

La table <table_name> est utilisée comme table cible par une vue matérialisée gérée par dbt. La valeur par défaut de mv_on_schema_change est définie sur "fail" afin d’éviter toute perte de données.

Si ce message apparaît, dbt a détecté que la table sert de table cible à au moins une vue matérialisée gérée par dbt. Si vous vous attendez à voir ce message mais qu’il n’apparaît pas, vérifiez que :

• Le modèle de vue matérialisée définit correctement {{ materialization_target_table(ref('your_target')) }}
• Le modèle de vue matérialisée a materialized='materialized_view' dans sa config
• La vue matérialisée et la table cible ont toutes deux été exécutées au moins une fois

Si vous avez des modèles de vue matérialisée existants qui utilisent l’approche de cible implicite et que vous souhaitez passer à l’approche de cible explicite, suivez ces étapes :

1. Créez le modèle de table cible

Créez un nouveau fichier de modèle avec materialized='table' qui définit le même schéma que la table cible de la MV actuelle. Utilisez une clause WHERE 0 pour créer une table vide. Utilisez le même nom que le modèle actuel de vue matérialisée implicite. Vous pourrez ensuite utiliser ce modèle pour faire évoluer la table cible.

-- 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

2. Mettez à jour vos modèles de MV

Créez de nouveaux modèles, chacun incluant le SQL de la MV ainsi que l’appel à la macro materialization_target_table() pointant vers la nouvelle table cible. Si vous utilisiez auparavant UNION ALL, supprimez cette partie ainsi que les commentaires.

Pour les noms des modèles, vous devrez suivre cette convention de nommage :

• si une seule MV était définie, elle portera le nom : <old_model_name>_mv
• si plusieurs MV étaient définies, chacune portera le nom : <old_model_name>_mv_<name_in_comments>

Auparavant, dans my_model.sql (table cible implicite, modèle unique avec UNION ALL) :

--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

Après (cible explicite, fichiers de modèles distincts) :

-- 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') }}

3. Modifiez-les selon vos besoins en suivant les instructions de la section cible explicite.

Lorsque vous faites évoluer vos modèles, vous devez garder à l’esprit la manière dont les différentes opérations interagissent avec les données en cours d’insertion :

• Comme les vues matérialisées de ClickHouse agissent comme des déclencheurs d’insertion, elles ne capturent les données que tant qu’elles existent. Si une vue matérialisée est supprimée puis recréée (par exemple lors d’un --full-refresh), les lignes insérées dans la table source pendant cet intervalle ne seront pas traitées par la vue matérialisée. On dit alors que la vue matérialisée est « aveugle ».
• Les différents processus de catchup reposent tous sur des opérations INSERT INTO ... SELECT qui utilisent le SQL des vues matérialisées et sont indépendants de leur fonctionnement. Une fois l’INSERT démarré, les nouvelles données ne sont pas capturées par celui-ci, mais elles le seront par la vue matérialisée attachée.

Le tableau suivant résume le niveau de sécurité de chaque opération lorsque des insertions sont en cours sur la table source.

Modèles de vue matérialisée :

Modèle de table cible :

Les vues matérialisées actualisables sont un type particulier de vue matérialisée dans ClickHouse qui réexécute périodiquement la requête et en stocke le résultat, à l'image du fonctionnement des vues matérialisées dans d'autres bases de données. Cela est utile lorsque vous avez besoin de snapshots ou d'aggregations périodiques plutôt que de déclencheurs d’insertion en temps réel.

Pour utiliser une vue matérialisée actualisable, ajoutez un objet de config refreshable à votre modèle MV avec les options suivantes :

{{
    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

À partir de dbt-clickhouse 1.10.2, les modifications apportées aux paramètres d’actualisation (interval, randomize et depends_on) d’une vue matérialisée actualisable existante sont appliquées directement lors d’un dbt run standard, à l’aide de ALTER TABLE ... MODIFY REFRESH. La vue matérialisée n’est ni supprimée ni recréée : aucune donnée n’est donc perdue et il n’y a pas de « fenêtre aveugle » pendant l’application de la modification.

Certaines transitions ne peuvent pas être appliquées directement. Au lieu de les ignorer silencieusement, dbt fera échouer l’exécution avec une erreur vous demandant d’utiliser dbt run --full-refresh :

• Conversion d’une vue matérialisée standard en vue matérialisée actualisable (ajout de la config refreshable), ou inversement (suppression de celle-ci).
• Modification du paramètre append, car ClickHouse n’autorise pas l’ajout ou la suppression de APPEND via MODIFY REFRESH.

Gardez à l’esprit qu’un --full-refresh supprime et recrée la vue matérialisée, avec les implications décrites dans Comportement lors d’une ingestion active.

• Lors de la création d'une vue matérialisée actualisable (MV) dans ClickHouse avec une dépendance, ClickHouse ne renvoie pas d'erreur si la dépendance spécifiée n'existe pas au moment de la création. À la place, la MV actualisable reste dans un état inactif, en attendant que la dépendance soit disponible avant de commencer à traiter les mises à jour ou à s'actualiser. Ce comportement est intentionnel, mais il peut retarder la disponibilité des données si la dépendance requise n'est pas mise en place rapidement. Assurez-vous que toutes les dépendances sont correctement définies et existent avant de créer une vue matérialisée actualisable.
• À ce jour, il n'existe pas de véritable « liaison dbt » entre la vue matérialisée et ses dépendances, de sorte que l'ordre de création n'est pas garanti.
• La fonctionnalité d'actualisation n'a pas été testée avec plusieurs vues matérialisées pointant vers le même modèle cible.