Intégration de dbt et ClickHouse

L’adaptateur dbt-clickhouse

dbt (data build tool) permet aux ingénieurs data spécialisés en analytics de transformer les données dans leurs entrepôts de données en écrivant simplement des instructions SELECT. dbt se charge de matérialiser ces instructions SELECT en objets de la base de données, sous forme de tables et de vues, assurant ainsi le T de Extract Load and Transform (ELT). Vous pouvez créer un modèle défini par une instruction SELECT. Dans dbt, ces modèles peuvent être référencés entre eux et organisés en couches afin de construire des concepts de plus haut niveau. Le code SQL répétitif nécessaire pour relier les modèles est généré automatiquement. De plus, dbt identifie les dépendances entre les modèles et veille à ce qu’ils soient créés dans le bon ordre à l’aide d’un graphe orienté acyclique (DAG). dbt est compatible avec ClickHouse grâce à un adaptateur pris en charge par ClickHouse.

dbt OSS, dbt v2 et la plateforme dbt. ClickHouse fonctionne désormais avec dbt OSS (bêta), dbt v2 (bêta) et la plateforme dbt (bêta privée ; voir comment demander l’accès). L’intégration n’est pas encore prête pour la production. Consultez la page dbt OSS, dbt v2 et plateforme dbt pour connaître l’état actuel et les limitations connues. Le reste de cette documentation s’applique également à chacun d’eux, sous réserve des limitations indiquées dans le tableau de parité de cette page.

Page	Description
Fonctionnalités et configurations	Description des fonctionnalités et des configurations générales disponibles
Matérialisations	Matérialisations disponibles et leurs configurations
Vues matérialisées	Documentation spécifique sur la matérialisation materialized_view
Guides	Guides d’utilisation de dbt avec ClickHouse

Fonctionnalités prises en charge

Liste des fonctionnalités prises en charge : Les fonctionnalités jusqu’à dbt-core 1.12 sont prises en charge, y compris l’option --sample, et tous les avertissements de dépréciation ont été corrigés pour les versions ultérieures. Les intégrations de catalogue (par exemple, Iceberg) introduites dans dbt 1.10 ne sont pas encore prises en charge nativement par l’adaptateur, mais des solutions de contournement sont disponibles. Voir la section Prise en charge du catalogue pour plus de détails.

Concepts dbt et matérialisations prises en charge

dbt introduit le concept de modèle. Celui-ci est défini comme une instruction SQL, pouvant éventuellement joindre de nombreuses tables. Un modèle peut être « matérialisé » de plusieurs façons. Une matérialisation représente une stratégie de construction pour la requête select du modèle. Le code sous-jacent à une matérialisation est du SQL standard qui encapsule votre requête SELECT dans une instruction afin de créer une nouvelle relation ou de mettre à jour une relation existante. dbt propose 5 types de matérialisation. Tous sont pris en charge par dbt-clickhouse :

view (par défaut) : Le modèle est créé sous forme de vue dans la base de données. Dans ClickHouse, cela correspond à une vue.
table : Le modèle est créé sous forme de table dans la base de données. Dans ClickHouse, cela correspond à une table.
ephemeral : Le modèle n’est pas créé directement dans la base de données, mais est intégré aux modèles dépendants sous forme de CTE (Common Table Expressions).
incrémentiel : Le modèle est d’abord matérialisé sous forme de table et, lors des exécutions suivantes, dbt insère de nouvelles lignes et met à jour les lignes modifiées dans la table.
materialized view : Le modèle est créé sous forme de vue matérialisée dans la base de données. Dans ClickHouse, cela correspond à une vue matérialisée.

Une syntaxe et des clauses supplémentaires définissent la manière dont ces modèles doivent être mis à jour si leurs données sous-jacentes changent. dbt recommande généralement de commencer par la matérialisation de vue jusqu’à ce que les performances deviennent un enjeu. La matérialisation en table améliore les performances à l’exécution des requêtes en enregistrant les résultats de la requête du modèle dans une table, au prix d’un stockage plus important. L’approche incrémentiel va plus loin en permettant de répercuter dans la table cible les mises à jour ultérieures des données sous-jacentes. Voici les fonctionnalités expérimentales de dbt-clickhouse :

Type	Prise en charge ?	Détails
matérialisation de vue matérialisée	Oui. La création avec cible explicite est en Beta	Crée une vue matérialisée.
matérialisation de table distribuée	Oui, Experimental	Crée une table distribuée.
Distributed incrémentiel matérialisation	Oui, Experimental	Modèle incrémentiel reposant sur le même principe qu’une table distribuée. Notez que toutes les stratégies ne sont pas prises en charge ; consultez la section correspondante de la documentation pour plus d’informations.
matérialisation de dictionnaire	Oui, Experimental	Crée un dictionnaire.

Installation de dbt et de l’adaptateur ClickHouse

Si vous préférez partir d’un projet déjà fonctionnel, notre fork Jaffle Shop for ClickHouse du projet d’exemple de dbt est livré avec des profils pour une instance ClickHouse locale et pour ClickHouse Cloud, des données d’exemple ainsi qu’un README couvrant dbt Core 1.x, dbt OSS, dbt v2 et la plateforme dbt. La page des guides s’appuie sur ce projet.

Installer dbt-core et dbt-clickhouse

dbt propose plusieurs options pour installer l’interface en ligne de commande (CLI), détaillées ici. Nous recommandons d’utiliser pip pour installer dbt et dbt-clickhouse.

pip install dbt-core dbt-clickhouse

Fournissez à dbt les paramètres de connexion de notre instance ClickHouse.

Configurez le profil clickhouse-service dans le fichier ~/.dbt/profiles.yml et renseignez les propriétés schema, host, port, user et password. La liste complète des options de configuration de la connexion est disponible sur la page Fonctionnalités et configurations :

clickhouse-service:
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema: [ default ] # ClickHouse database for dbt models

      # Optional
      host: [ localhost ]
      port: [ 8123 ]  # Defaults to 8123, 8443, 9000, 9440 depending on the secure and driver settings
      user: [ default ] # User for all database operations
      password: [ <empty string> ] # Password for the user
      secure: True  # Use TLS (native protocol) or HTTPS (http protocol)

Créer un projet dbt

Vous pouvez désormais utiliser ce profil dans l’un de vos projets existants ou en créer un nouveau à l’aide de :

dbt init project_name

Dans le répertoire project_name, mettez à jour votre fichier dbt_project.yml afin d’y indiquer un nom de profil pour vous connecter au serveur ClickHouse.

profile: 'clickhouse-service'

Tester la connexion

Exécutez dbt debug avec l’outil CLI afin de vérifier si dbt parvient à se connecter à ClickHouse. Vérifiez que la réponse contient Connection test: [OK connection ok], ce qui indique que la connexion a réussi. Consultez la page des guides pour en savoir plus sur l’utilisation de dbt avec ClickHouse.

Tester et déployer vos modèles (CI/CD)

Il existe de nombreuses façons de tester et de déployer votre projet dbt. dbt propose quelques recommandations sur les bonnes pratiques de workflow et les jobs CI. Nous allons passer en revue plusieurs stratégies, mais gardez à l’esprit qu’elles devront შესაძლოა être adaptées en profondeur à votre cas d’usage.

CI/CD avec des tests de données simples et des tests unitaires

Pour démarrer simplement votre pipeline de CI, vous pouvez exécuter un cluster ClickHouse dans votre job, puis y exécuter vos modèles. Vous pouvez insérer des données de démonstration dans ce cluster avant d’exécuter vos modèles. Vous pouvez simplement utiliser un seed pour alimenter l’environnement de préproduction avec un sous-ensemble de vos données de production. Une fois les données insérées, vous pouvez alors exécuter vos tests de données et vos tests unitaires. Votre étape de CD peut être aussi simple que d’exécuter dbt build sur votre cluster ClickHouse de production.

Étape CI/CD plus complète : utiliser des données récentes, ne tester que les modèles concernés

Une stratégie courante consiste à utiliser des jobs Slim CI, dans lesquels seuls les modèles modifiés (ainsi que leurs dépendances en amont et en aval) sont redéployés. Cette approche s’appuie sur les artefacts de vos exécutions en production (c.-à-d. le manifest dbt) pour réduire le temps d’exécution de votre projet et garantir l’absence de dérive de schéma entre les environnements. Pour garder vos environnements de développement synchronisés et éviter d’exécuter vos modèles sur des déploiements obsolètes, vous pouvez utiliser clone ou même defer. Dans ClickHouse, dbt clone copie les tables MergeTree à l’aide d’une instruction CLONE zero-copy ; consultez Clonage de modèles avec dbt clone ci-dessous pour plus de détails. Nous recommandons d’utiliser un cluster ou un service ClickHouse dédié pour l’environnement de test (c.-à-d. un environnement de préproduction) afin d’éviter d’affecter le fonctionnement de votre environnement de production. Pour que l’environnement de test soit représentatif, il est important d’utiliser un sous-ensemble de vos données de production et d’exécuter dbt d’une manière qui empêche toute dérive de schéma entre les environnements.

Si vous n’avez pas besoin de données fraîches pour vos tests, vous pouvez restaurer une sauvegarde de vos données de production dans l’environnement de préproduction.
Si vous avez besoin de données fraîches pour vos tests, vous pouvez utiliser une combinaison de la table function remoteSecure() et de vues matérialisées actualisables pour insérer des données à la fréquence souhaitée. Une autre option consiste à utiliser le stockage objet comme intermédiaire, à y écrire périodiquement des données depuis votre service de production, puis à les importer dans l’environnement de préproduction à l’aide des table functions de stockage objet ou de ClickPipes (pour l’ingestion continue).

L’utilisation d’un environnement dédié aux tests CI vous permet également d’effectuer des tests manuels sans affecter votre environnement de production. Par exemple, vous pouvez vouloir connecter un outil BI à cet environnement pour vos tests. Pour le déploiement (c.-à-d. l’étape CD), nous recommandons d’utiliser les artefacts de vos déploiements en production pour ne mettre à jour que les modèles qui ont changé. Cela nécessite de configurer le stockage objet (par exemple, S3) comme stockage intermédiaire pour vos artefacts dbt. Une fois cela en place, vous pouvez exécuter une commande telle que dbt build --select state:modified+ --state path/to/last/deploy/state.json afin de reconstruire de manière sélective le nombre minimal de modèles nécessaires en fonction de ce qui a changé depuis la dernière exécution en production.

Clonage de modèles avec `dbt clone`

À partir de dbt-clickhouse 1.10.1, la commande dbt clone utilise l’instruction zero-copy CREATE OR REPLACE TABLE ... CLONE AS ... de ClickHouse pour cloner les modèles matérialisés sous forme de tables utilisant un moteur de la famille MergeTree. Cette opération crée une copie de la table sans dupliquer les data parts sous-jacentes, ce qui permet de synchroniser les environnements rapidement et à moindre coût, par exemple lors de la mise en place d’un environnement de développement ou de Slim CI à partir de l’état de production. Les modèles qui ne peuvent pas être clonés de cette manière reviennent au comportement par défaut de dbt, qui consiste à créer une vue pointant vers la relation source :

Tables utilisant des moteurs autres que MergeTree
Matérialisations Distributed

Pour les modèles de vues matérialisées, seule la table cible est clonée ; la vue matérialisée elle-même est créée sur la table clonée.

Dépannage des problèmes courants

Connexions

Si vous rencontrez des problèmes de connexion à ClickHouse depuis dbt, assurez-vous que les critères suivants sont remplis :

Le moteur doit faire partie des moteurs pris en charge.
Vous devez disposer des permissions nécessaires pour accéder à la base de données.
Si vous n’utilisez pas le moteur de table par défaut de la base de données, vous devez spécifier un moteur de table dans la configuration de votre modèle.

Comprendre les opérations de longue durée

Certaines opérations peuvent prendre plus de temps que prévu en raison de requêtes ClickHouse spécifiques. Pour mieux identifier les requêtes qui prennent le plus de temps, augmentez le niveau de journalisation à debug — cela affichera le temps d’exécution de chaque requête. Par exemple, vous pouvez le faire en ajoutant --log-level debug aux commandes dbt.

Corréler les exécutions dbt aux requêtes ClickHouse

Pour obtenir des informations côté serveur, à partir de dbt-clickhouse 1.10.1, chaque instruction exécutée par l’adaptateur reçoit son propre ID de requête (un UUID4), qui est transmis à ClickHouse. L’ID de l’instruction principale d’un modèle est renvoyé dans l’adapter_response de son résultat dbt ; il est donc disponible dans des artefacts dbt tels que run_results.json. Vous pouvez le rechercher dans la table system.query_log pour examiner la durée d’exécution et l’utilisation des ressources de cette instruction :

SELECT query_id, query, query_duration_ms, read_rows, memory_usage
FROM system.query_log
WHERE query_id = '<query_id from run_results.json>'
  AND type = 'QueryFinish'

Notez qu’une matérialisation exécute généralement plusieurs instructions par modèle (DDL, insertions, etc.), chacune avec son propre ID de requête ; celui figurant dans run_results.json n’identifie que l’instruction principale du modèle. Pour retrouver toutes les instructions d’une exécution, filtrez plutôt system.query_log à l’aide du commentaire de requête dbt intégré au texte de chaque requête. L’ID de requête permet également aux outils d’observabilité qui exploitent des artefacts dbt (par exemple, Elementary) d’associer automatiquement les exécutions de modèles dbt aux entrées de system.query_log.

Limitations

L’adaptateur ClickHouse actuel pour dbt présente plusieurs limitations dont vous devez être conscient :

Le plugin utilise une syntaxe qui nécessite ClickHouse version 25.3 ou ultérieure. Nous ne testons pas les versions plus anciennes de ClickHouse. Nous ne testons pas non plus actuellement les tables Replicated.
Différentes exécutions de dbt-adapter peuvent entrer en conflit si elles sont lancées en même temps, car elles peuvent utiliser en interne les mêmes noms de table pour les mêmes opérations. Pour plus d’informations, consultez l’issue #420.
L’adaptateur matérialise actuellement les modèles sous forme de tables à l’aide d’un INSERT INTO SELECT. Cela entraîne effectivement une duplication des données si l’exécution est relancée. Des jeux de données très volumineux (PB) peuvent provoquer des temps d’exécution extrêmement longs, au point de rendre certains modèles peu viables. Pour améliorer les performances, utilisez des vues matérialisées ClickHouse en implémentant la vue comme materialized: materialization_view. En outre, efforcez-vous de réduire au minimum le nombre de lignes renvoyées par chaque requête en utilisant GROUP BY lorsque c’est possible. Préférez les modèles qui résument les données à ceux qui se contentent de les transformer tout en conservant le même nombre de lignes que la source.
Pour utiliser des tables distribuées pour représenter un modèle, vous devez créer manuellement les tables replicated sous-jacentes sur chaque nœud. La table Distributed peut ensuite être créée au-dessus de celles-ci. L’adaptateur ne gère pas la création du cluster.
Lorsque dbt crée une relation (table/view) dans une base de données, il la crée généralement comme suit : {{ database }}.{{ schema }}.{{ table/view id }}. ClickHouse n’a pas de notion de schéma. L’adaptateur utilise donc {{schema}}.{{ table/view id }}, où schema correspond à la base de données ClickHouse.

Fivetran

Le connecteur dbt-clickhouse peut également être utilisé dans les transformations Fivetran, ce qui permet une intégration fluide et des capacités de transformation directement au sein de la plateforme Fivetran à l’aide de dbt.

Sources de données

Visualisation des données

Ingestion de données

Intégrations de données

Clients SQL

Outils

L’adaptateur dbt-clickhouse

Fonctionnalités prises en charge

Concepts dbt et matérialisations prises en charge

Installation de dbt et de l’adaptateur ClickHouse

Installer dbt-core et dbt-clickhouse

Fournissez à dbt les paramètres de connexion de notre instance ClickHouse.

Créer un projet dbt

Tester la connexion

Tester et déployer vos modèles (CI/CD)

CI/CD avec des tests de données simples et des tests unitaires

Étape CI/CD plus complète : utiliser des données récentes, ne tester que les modèles concernés

Clonage de modèles avec `dbt clone`

Dépannage des problèmes courants

Connexions

Comprendre les opérations de longue durée

Corréler les exécutions dbt aux requêtes ClickHouse

Limitations

Fivetran

​L’adaptateur dbt-clickhouse

​Pages connexes

​Fonctionnalités prises en charge

​Concepts dbt et matérialisations prises en charge

​Installation de dbt et de l’adaptateur ClickHouse

​Installer dbt-core et dbt-clickhouse

​Fournissez à dbt les paramètres de connexion de notre instance ClickHouse.

​Créer un projet dbt

​Tester la connexion

​Tester et déployer vos modèles (CI/CD)

​CI/CD avec des tests de données simples et des tests unitaires

​Étape CI/CD plus complète : utiliser des données récentes, ne tester que les modèles concernés

​Clonage de modèles avec dbt clone

​Dépannage des problèmes courants

​Connexions

​Comprendre les opérations de longue durée

​Corréler les exécutions dbt aux requêtes ClickHouse

​Limitations

​Fivetran

L’adaptateur dbt-clickhouse

Pages connexes

Fonctionnalités prises en charge

Concepts dbt et matérialisations prises en charge

Installation de dbt et de l’adaptateur ClickHouse

Installer dbt-core et dbt-clickhouse

Fournissez à dbt les paramètres de connexion de notre instance ClickHouse.

Créer un projet dbt

Tester la connexion

Tester et déployer vos modèles (CI/CD)

CI/CD avec des tests de données simples et des tests unitaires

Étape CI/CD plus complète : utiliser des données récentes, ne tester que les modèles concernés

Clonage de modèles avec `dbt clone`

Dépannage des problèmes courants

Connexions

Comprendre les opérations de longue durée

Corréler les exécutions dbt aux requêtes ClickHouse

Limitations

Fivetran