Intégration de dbt et 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.

Liste des fonctionnalités prises en charge :

• Matérialisation en table
• Matérialisation de vue
• Matérialisation incrémentielle
• Matérialisation incrémentielle Microbatch
• Matérialisations vue matérialisée (utilise la forme TO de MATERIALIZED VIEW, expérimentale)
• Seeds
• Sources
• Génération de la documentation
• Tests
• Snapshots
• La plupart des macros dbt-utils (désormais incluses dans dbt-core)
• Matérialisation éphémère
• Matérialisation de table distribuée (expérimentale)
• Matérialisation incrémentielle distribuée (expérimentale)
• Matérialisation Dictionary (expérimentale)
• Contrats
• Configurations de colonnes spécifiques à ClickHouse (codec, TTL…)
• Paramètres de table spécifiques à ClickHouse (indexes, projections…)

Toutes les fonctionnalités jusqu’à dbt-core 1.10 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.

Cet adaptateur n’est pas encore disponible dans dbt Cloud, mais nous prévoyons de le proposer prochainement. Veuillez contacter le support pour obtenir plus d’informations à ce sujet.

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 :

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

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)

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'

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.

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.

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.

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.

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

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.

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.

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.

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.
• Les modèles/CTE éphémères ne fonctionnent pas s’ils sont placés avant le INSERT INTO dans une instruction insert ClickHouse, voir https://github.com/ClickHouse/ClickHouse/issues/30323. Cela ne devrait pas affecter la plupart des modèles, mais il faut faire attention à l’emplacement d’un modèle éphémère dans les définitions de modèles et les autres instructions SQL.

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.