Matérialisations

Cette section présente toutes les matérialisations disponibles dans dbt-clickhouse, y compris les fonctionnalités expérimentales.

Le tableau suivant présente les configurations partagées par certaines des matérialisations disponibles. Pour des informations détaillées sur les configurations générales des modèles dbt, consultez la documentation dbt :

Remarque : pour les vues matérialisées, tous les moteurs de la famille *MergeTree sont pris en charge.

Si vous rencontrez des problèmes de connexion à ClickHouse depuis dbt avec l'un des moteurs ci-dessus, veuillez signaler le problème ici.

ClickHouse propose plusieurs types/niveaux de « settings ». Dans la configuration du modèle ci-dessus, deux de ces types sont configurables. settings désigne la clause SETTINGS utilisée dans les instructions DDL de type CREATE TABLE/VIEW ; il s’agit donc généralement de paramètres propres au moteur de table ClickHouse concerné. Le nouveau query_settings permet d’ajouter une clause SETTINGS aux requêtes INSERT et DELETE utilisées pour la matérialisation du modèle ( y compris les matérialisations incrémentales). Il existe des centaines de paramètres ClickHouse, et il n’est pas toujours évident de savoir lequel est un paramètre de « table » et lequel est un paramètre « utilisateur » (bien que ces derniers soient généralement disponibles dans la table system.settings.) En règle générale, il est recommandé de conserver les valeurs par défaut, et toute utilisation de ces propriétés doit être soigneusement étudiée et testée.

REMARQUE : Les options de configuration des colonnes ci-dessous nécessitent l’application des contrats de modèle.

models:
  - name: table_column_configs
    description: 'Testing column-level configurations'
    config:
      contract:
        enforced: true
    columns:
      - name: ts
        data_type: timestamp
        codec: ZSTD
      - name: x
        data_type: UInt8
        ttl: ts + INTERVAL 1 DAY

dbt détermine automatiquement le type de données de chaque colonne en analysant le SQL utilisé pour créer le modèle. Cependant, dans certains cas, ce processus peut ne pas identifier correctement le type de données, ce qui entraîne des conflits avec les types spécifiés dans la propriété de contrat data_type. Pour y remédier, nous recommandons d’utiliser la fonction CAST() dans le SQL du modèle afin de définir explicitement le type souhaité. Par exemple :

{{
    config(
        materialized="materialized_view",
        engine="AggregatingMergeTree",
        order_by=["event_type"],
    )
}}

select
  -- event_type may be infered as a String but we may prefer LowCardinality(String):
  CAST(event_type, 'LowCardinality(String)') as event_type,
  -- countState() may be infered as `AggregateFunction(count)` but we may prefer to change the type of the argument used:
  CAST(countState(), 'AggregateFunction(count, UInt32)') as response_count, 
  -- maxSimpleState() may be infered as `SimpleAggregateFunction(max, String)` but we may prefer to also change the type of the argument used:
  CAST(maxSimpleState(event_type), 'SimpleAggregateFunction(max, LowCardinality(String))') as max_event_type
from {{ ref('user_events') }}
group by event_type

Un modèle dbt peut être créé sous forme de vue ClickHouse et configuré à l’aide de la syntaxe suivante :

Fichier du projet (dbt_project.yml) :

models:
  <resource-path>:
    +materialized: view

Ou bloc de configuration (models/<model_name>.sql) :

{{ config(materialized = "view") }}

Un modèle dbt peut être créé comme une table ClickHouse et configuré à l’aide de la syntaxe suivante :

Fichier de projet (dbt_project.yml) :

models:
  <resource-path>:
    +materialized: table
    +order_by: [ <column-name>, ... ]
    +engine: <engine-type>
    +partition_by: [ <column-name>, ... ]

Ou bloc de configuration (models/<model_name>.sql) :

{{ config(
    materialized = "table",
    engine = "<engine-type>",
    order_by = [ "<column-name>", ... ],
    partition_by = [ "<column-name>", ... ],
      ...
    ]
) }}

Vous pouvez ajouter des index de saut de données aux matérialisations table à l’aide de la configuration indexes :

{{ config(
        materialized='table',
        indexes=[{
          'name': 'your_index_name',
          'definition': 'your_column TYPE minmax GRANULARITY 2'
        }]
) }}

Vous pouvez ajouter des projections aux matérialisations table et distributed_table à l’aide de la configuration projections. Chaque entrée de projection nécessite une clé query ou une clé index (mais pas les deux).

Remarque : Pour les tables distribuées, la projection s’applique aux tables _local, et non à la table distribuée servant de proxy. Remarque : Spécifier à la fois query et index dans la même entrée de projection génère une erreur lors de la compilation.

Utilisez query pour définir une requête de projection complète :

{{ config(
       materialized='table',
       projections=[
           {
               'name': 'your_projection_name',
               'query': 'SELECT department, avg(age) AS avg_age GROUP BY department'
           }
       ]
) }}

Utilisez index comme raccourci syntaxique pour des projections d’index légères utilisant la colonne virtuelle _part_offset. Indiquez un nom de colonne ou une liste de colonnes selon laquelle effectuer le tri :

{{ config(
       materialized='table',
       projections=[
           {
               'name': 'proj_by_age',
               'index': 'age'
           }
       ]
) }}

{{ config(
       materialized='table',
       projections=[
           {
               'name': 'proj_by_dept_age',
               'index': ['department', 'age']
           }
       ]
) }}

dbt-clickhouse génère automatiquement le DDL adapté à la version :

Le modèle de type table sera reconstruit à chaque exécution de dbt. Cela peut s’avérer irréaliste et extrêmement coûteux pour des jeux de résultats volumineux ou des transformations complexes. Pour relever ce défi et réduire le temps de build, un modèle dbt peut être créé en tant que table ClickHouse incrémentielle et se configure à l’aide de la syntaxe suivante :

Définition du modèle dans dbt_project.yml :

models:
  <resource-path>:
    +materialized: incremental
    +order_by: [ <column-name>, ... ]
    +engine: <engine-type>
    +partition_by: [ <column-name>, ... ]
    +unique_key: [ <column-name>, ... ]
    +inserts_only: [ True|False ]

Ou le bloc de configuration dans models/<model_name>.sql :

{{ config(
    materialized = "incremental",
    engine = "<engine-type>",
    order_by = [ "<column-name>", ... ],
    partition_by = [ "<column-name>", ... ],
    unique_key = [ "<column-name>", ... ],
    inserts_only = [ True|False ],
      ...
    ]
) }}

Les configurations spécifiques à ce type de matérialisation sont listées ci-dessous :

dbt-clickhouse prend en charge trois stratégies de modèles incrémentaux.

Historiquement, ClickHouse ne prenait en charge les mises à jour et les suppressions que de façon limitée, sous la forme de « mutations » asynchrones. Pour reproduire le comportement attendu de dbt, dbt-clickhouse crée par défaut une nouvelle table temporaire contenant tous les anciens enregistrements non affectés (non supprimés, non modifiés), ainsi que les enregistrements nouveaux ou mis à jour, puis permute ou échange cette table temporaire avec la relation incremental existante du modèle. C'est la seule stratégie qui préserve la relation d'origine si quelque chose tourne mal avant la fin de l'opération ; toutefois, comme elle implique une copie complète de la table d'origine, son exécution peut s'avérer coûteuse et lente.

La stratégie delete+insert utilise les suppressions légères pour supprimer les lignes concernées, puis insérer les nouvelles. Comme elle ne copie pas l’intégralité de la table, elle est nettement plus performante que la stratégie « legacy ». Définir use_lw_deletes: true dans votre profil fait de delete+insert la stratégie incrémentielle par défaut.

Cette stratégie comporte d’importantes mises en garde :

• Elle agit directement sur la table concernée sans créer de tables intermédiaires ou temporaires. Par conséquent, en cas de problème lors de l’opération, les données du modèle incrémentiel risquent de se retrouver dans un état non valide.
• Elle nécessite le paramètre ClickHouse allow_nondeterministic_mutations. L’adaptateur l’active automatiquement pour ses propres sessions lorsque cela est possible. Lorsqu’il ne peut pas être activé (par exemple, parce qu’il est en lecture seule pour votre utilisateur dbt), le comportement dépend de la manière dont la stratégie a été choisie : les modèles s’appuyant sur la stratégie par défaut basculent silencieusement vers la stratégie legacy, les modèles qui définissent explicitement delete+insert ou microbatch échouent à l’exécution, et use_lw_deletes: true dans le profil échoue lors de la connexion.
• Dans de très rares cas, l’utilisation de incremental_predicates non déterministes peut entraîner une condition de concurrence pour les éléments mis à jour ou supprimés. Pour garantir des résultats cohérents, les prédicats incrémentiels ne doivent inclure que des sous-requêtes portant sur des données qui ne seront pas modifiées lors de la matérialisation incrémentielle.

La stratégie incrémentale microbatch est une fonctionnalité de dbt-core depuis la version 1.9, conçue pour traiter efficacement de grandes transformations de données chronologiques. Dans dbt-clickhouse, elle s’appuie sur la stratégie incrémentale delete_insert existante en scindant l’incrément en lots chronologiques prédéfinis, selon les configurations de modèle event_time et batch_size.

Au-delà de la gestion de transformations volumineuses, microbatch permet de :

• Retraiter les lots en échec.
• Détecter automatiquement l’exécution parallèle des lots.
• Éliminer le besoin d’une logique conditionnelle complexe pour le chargement rétroactif.

Pour plus de détails sur l’utilisation de microbatch, consultez la documentation officielle.

Cette stratégie remplace le paramètre inserts_only dans les versions précédentes de dbt-clickhouse. Cette approche ajoute simplement de nouvelles lignes à la relation existante. Par conséquent, les lignes en double ne sont pas éliminées et il n’y a ni table temporaire ni table intermédiaire. C’est l’approche la plus rapide si les doublons sont soit autorisés dans les données, soit exclus par la requête incrémentielle via la clause/le filtre WHERE.

[IMPORTANT] Actuellement, la stratégie insert_overwrite n'est pas entièrement fonctionnelle avec les matérialisations distribuées.

Elle exécute les étapes suivantes :

1. Crée une table de staging (temporaire) avec la même structure que la relation du modèle incrémental : CREATE TABLE <staging> AS <target>.
2. Insère uniquement les nouveaux enregistrements (produits par SELECT) dans la table de staging.
3. Remplace uniquement les nouvelles partitions (présentes dans la table de staging) dans la table cible.

Cette approche présente les avantages suivants :

• Elle est plus rapide que la stratégie par défaut, car elle ne copie pas l'intégralité de la table.
• Elle est plus sûre que les autres stratégies, car elle ne modifie pas la table d'origine tant que l'opération INSERT n'est pas terminée avec succès : en cas d'échec intermédiaire, la table d'origine n'est pas modifiée.
• Elle met en œuvre la bonne pratique d'ingénierie des données dite de « l'immutabilité des partitions », ce qui simplifie le traitement incrémental et parallèle des données, les rollbacks, etc.

La stratégie nécessite que partition_by soit défini dans la configuration du modèle. Elle ignore tous les autres paramètres du modèle spécifiques aux stratégies.

La matérialisation materialized_view crée une vue matérialisée dans ClickHouse, qui fait office de déclencheur d’insertion en transformant et en insérant automatiquement les nouvelles lignes d’une table source vers une table cible. Il s’agit de l’une des matérialisations les plus puissantes disponibles dans dbt-clickhouse.

Compte tenu de sa complexité, cette matérialisation dispose de sa propre page dédiée. Consultez le guide des vues matérialisées pour accéder à la documentation complète

Un modèle dbt peut être créé sous la forme d’un dictionnaire ClickHouse. À chaque dbt run, le dictionnaire est remplacé par la définition actuelle du modèle à l’aide de CREATE OR REPLACE DICTIONARY.

Le SQL du modèle devient la requête de la source du dictionnaire :

{{ config(
       materialized='dictionary',
       fields=[
           ('id', 'UInt64'),
           ('name', 'String'),
       ],
       primary_key='id',
       layout='HASHED()',
       lifetime='MIN 0 MAX 300'
) }}

select id, name from {{ source('raw', 'people') }}

Avec source_type='http' (ou l’option table), le SQL du modèle n’est pas utilisé comme source, mais dbt exige tout de même un corps : utilisez select 1 comme espace réservé.

{{ config(
       materialized='dictionary',
       fields=[
           ('LocationID', 'UInt16 DEFAULT 0'),
           ('Borough', 'String'),
           ('Zone', 'String'),
       ],
       primary_key='LocationID',
       layout='HASHED()',
       lifetime='MIN 0 MAX 0',
       source_type='http',
       url='https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv',
       format='CSVWithNames'
) }}

select 1

Consultez les tests de dictionnaires pour d’autres exemples, notamment des dictionnaires à plage et directs.

Une table distribuée est créée selon les étapes suivantes :

1. Création d’une vue temporaire avec une requête SQL afin d’obtenir la bonne structure
2. Création de tables locales vides à partir de la vue
3. Création d’une table distribuée à partir des tables locales.
4. Les données sont insérées dans la table distribuée, puis réparties entre les shards sans duplication.

Remarques :

• Les requêtes dbt-clickhouse incluent désormais automatiquement le paramètre insert_distributed_sync = 1 afin de garantir que les opérations de matérialisation incrémentielle en aval s’exécutent correctement. Cela peut ralentir davantage que prévu certaines insertions dans des tables distribuées.

{{
    config(
        materialized='distributed_table',
        order_by='id, created_at',
        sharding_key='cityHash64(id)',
        engine='ReplacingMergeTree'
    )
}}

select id, created_at, item
from {{ source('db', 'table') }}

CREATE TABLE db.table_local on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = ReplacingMergeTree
    ORDER BY (id, created_at);

CREATE TABLE db.table on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = Distributed ('cluster', 'db', 'table_local', cityHash64(id));

Les configurations propres à ce type de matérialisation sont indiquées ci-dessous :

Modèle incrémental fondé sur le même principe qu’une table distribuée ; la principale difficulté consiste à traiter correctement toutes les stratégies incrémentales.

1. La stratégie Append se contente d’insérer les données dans la table distribuée.
2. La stratégie Delete+Insert crée une table temporaire distribuée pour travailler avec l’ensemble des données sur chaque shard.
3. La stratégie Default (Legacy) crée des tables temporaires et intermédiaires distribuées pour la même raison.

Seules les tables de shard sont remplacées, car la table distribuée ne stocke pas les données. La table distribuée n’est rechargée que lorsque le mode full_refresh est activé ou que la structure de la table a pu changer.

{{
    config(
        materialized='distributed_incremental',
        engine='MergeTree',
        incremental_strategy='append',
        unique_key='id,created_at'
    )
}}

select id, created_at, item
from {{ source('db', 'table') }}

CREATE TABLE db.table_local on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = MergeTree;

CREATE TABLE db.table on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = Distributed ('cluster', 'db', 'table_local', cityHash64(id));

Les snapshots dbt permettent de conserver un historique des modifications apportées à un modèle mutable au fil du temps. Cela permet ensuite d’effectuer des requêtes à un instant donné sur les modèles, afin que les analystes puissent « remonter dans le temps » jusqu’à l’état antérieur d’un modèle. Cette fonctionnalité est prise en charge par le ClickHouse Connector et se configure à l’aide de la syntaxe suivante :

Bloc de config dans snapshots/<model_name>.sql:

{{
   config(
     schema = "<schema-name>",
     unique_key = "<column-name>",
     strategy = "<strategy>",
     updated_at = "<updated-at-column-name>",
   )
}}

Pour en savoir plus sur la configuration, consultez la page de référence snapshot configs.

Seuls les contrats correspondant exactement au type de la colonne sont pris en charge. Par exemple, un contrat avec une colonne de type UInt32 échouera si le modèle renvoie un UInt64 ou un autre type entier. ClickHouse ne prend également en charge que les contraintes CHECK sur l’ensemble de la table/du modèle. Les contraintes de clé primaire, de clé étrangère, d’unicité et les contraintes CHECK au niveau des colonnes ne sont pas prises en charge. (Voir la documentation ClickHouse sur les clés primaires et les clés ORDER BY.)