> ## Content Index > Fetch the complete content index at: https://www.sfeir.dev/llms.txt > Use this file to discover other available public pages before exploring further. # bq2dbt : un outil pour migrer vers dbt des tables BigQuery existantes - URL: https://www.sfeir.dev/data/bq2dbt-un-outil-pour-migrer-vers-dbt-des-tables-bigquery-existantes/ - Published: 2024-07-03T09:29:20.000Z - Updated: 2024-07-03T09:29:20.000Z - Description: Cet article présente bq2dbt, un script open source qui automatise la génération de fichiers SQL et YAML pour dbt en se basant sur les métadonnées des tables BigQuery. - Author: Henri-Maxime Ducoulombier - Tags: Data, dbt, opensource, Tools Depuis que j’utilise le couple [Google BigQuery](https://www.sfeir.dev/cloud/bigquery-devient-la-plateforme-data-to-ai-ultime/) et [*dbt*](https://www.getdbt.com/?ref=sfeir.dev) chez mes clients, il m’est souvent arrivé de travailler sur des projets existants qui produisent des tables ou des vues avec des systèmes legacy. Dans l’optique d’uniformiser nos outils et de mettre en place les bonnes pratiques, nous migrons régulièrement des anciens pipelines vers des projets *dbt* et enprofitons pour mettre en place certaines fonctionnalités intéressantes de l’outil. Parmi elles, nous démocratisons la mise en place des contrats (“[model contracts](https://docs.getdbt.com/docs/collaborate/govern/model-contracts?ref=sfeir.dev)”) et parfois les versions, ainsi que l’intégration de la documentation dans le YAML *dbt* et le renommage / l’application de policies sur les champs. Les tables ou les vues existantes ont généralement déjà des descriptions sur les champs, et même parfois des contraintes ou des clés déclarées (BigQuery supporte ce [genre de choses](https://cloud.google.com/bigquery/docs/information-schema-table-constraints?ref=sfeir.dev) depuis peu). Concernant leur schéma, ces tables ont parfois des dizaines ou des centaines de champs, et même des champs structurés. Ecrire les YAML ou le [SQL ](https://www.sfeir.dev/tag/sql/)explicite (sans utiliser le SELECT \*) à la main s’avère rapidement être un cauchemar sans y mettre un peu d’automatisation. Alors un beau matin, devant la tâche colossale qui nous attendait, j’ai décidé de prendre les choses en main et d’écrire un script python sobrement intitulé [*bq2dbt*](https://github.com/H-Max/bq2dbt?ref=sfeir.dev) pour faire la plus grosse partie du travail. Investir quelques heures pour en économiser des dizaines me semblait judicieux. ![](https://storage.ghost.io/c/0b/31/0b31478e-a66a-46f1-8c34-d44388e21192/content/images/2024/07/article2-1.png) La genèse en pixel art ## La genèse du script L’idée de départ est simple : générer automatiquement le SQL et le YAML pour modéliser dans *dbt* une table ou une vue qui existe déjà dans BigQuery. Comme nous migrons généralement les tables une par une, nous avons fait le choix de créer un script en ligne de commande qui prend comme unique argument un identifiant de table BigQuery (*projet:dataset.table*), et de générer les 2 fichiers correspondants. Techniquement le problème est assez simple à résoudre : il suffit d’aller lire les metadonnées de la table et des colonnes aux différents emplacements où ces informations sont stockées dans BigQuery (car oui, tout n’est pas au même endroit). On retrouve ces dernières dans des vues spéciales de BigQuery nommées [INFORMATION\_SCHEMA](https://cloud.google.com/bigquery/docs/information-schema-intro?hl=fr&ref=sfeir.dev) qui contiennent entre autres : - Les définitions de colonnes : [COLUMNS](https://cloud.google.com/bigquery/docs/information-schema-columns?hl=fr&ref=sfeir.dev) - Les chemins complets des colonnes : [COLUMN\_FIELD\_PATHS](https://cloud.google.com/bigquery/docs/information-schema-column-field-paths?hl=fr&ref=sfeir.dev) - Les clés / contraintes sur les colonnes : [KEY\_COLUMN\_USAGE](https://cloud.google.com/bigquery/docs/information-schema-key-column-usage?hl=fr&ref=sfeir.dev) Dès la première version et parce qu’il s’agissait de fonctionnalités essentielles, le support des champs de types RECORD / REPEATED et la récupération des datatypes pour les contrats étaient de la partie. La récupération des descriptions des champs et des tables furent ajoutées rapidement, de même que les infos liées aux contraintes de *nullité* (champ “NULLABLE”) : la première version était sur les rails ! Prenons un exemple avec cette table qui existe déjà dans BigQuery, créée spécialement pour l’occasion avec des données classiques, des données structurées, des descriptions, et même une contrainte de clé primaire. ![](https://lh7-us.googleusercontent.com/docsz/AD_4nXdmu67fYuzp57bkRL4xDSrD8wrTSNG8p_kvbJNVQMSfgk8nll3lLf4D5_iOeKxbmshYeLt1LIHkAnO3miah3OuYIjUYxSYdCeNCDIlZEwhg9KpMbMbWG4qGy-tp26vK_eusZ4GQkbZ6NbzF0ixAB6UoTjo?key=isFWXtMLOHpd7SAOH5kJjw) Un exemple de table BigQuery avec des champs de différents types La commande pour générer le SQL et le YAML de cette table, si elle était dans le dataset *demo-dbt.bq2dbt* serait simplement : bq2dbt demo-dbt.bq2dbt.example\_table Ce qui nous donnerait les 2 fichiers suivants : - le SQL, où il faudra remplacer la clause “FROM” par un ref() ou source() évidemment : ```SQL SELECT `string_field` , `int_field` , `boolean_field` , `repeated_field` , `struct_field` , `repeated_struct_field` FROM `uc-gcp-sandbox-dev.GREEN_TEAM.example_table` ``` - le YAML : ```YAML models: - name: example_table description: Demonstration table for bq2dbt config: contract: enforced: true constraints: - type: primary_key columns: - int_field columns: - name: string_field data_type: STRING description: This is a string field - name: int_field data_type: INT64 description: This is an integer field constraints: - type: not_null - name: boolean_field data_type: BOOL description: This is a boolean field constraints: - type: not_null - name: repeated_field data_type: ARRAY description: This is a repeated string field - name: struct_field data_type: STRUCT description: This is a struct field - name: struct_field.nested_string data_type: STRING description: This is a nested string field - name: struct_field.nested_int data_type: INT64 description: This is a nested integer field - name: repeated_struct_field data_type: ARRAY description: This is a repeated struct field - name: repeated_struct_field.nested_string data_type: STRING description: Nested string field in a repeated struct - name: repeated_struct_field.nested_int data_type: INT64 description: Nested integer field in a repeated struct ``` ## Personnalisation et options avancées Le résultat de la première mouture, déjà très positif, nous incitait donc à aller encore plus loin en ajoutant des options pour personnaliser le code généré. L’objectif restait identique : minimiser le plus possible les tâches manuelles et répétitives, chronophages et sans valeur ajoutée pour les équipes chargées de la migration. Sont donc arrivées les options permettant de renommer les colonnes d’output au format snake\_case, et d’ajouter un préfixe ou un suffixe (pratique pour générer des vues d’expositions qui ne font que du changement de “case” ou du renommage). Certains préfèrent aussi avoir les types de champs en minuscules dans les contrats, et pouvoir choisir à quel emplacement les fichiers sont générés (ils le sont par défaut dans le dossier ./target, car on lance généralement le script au même endroit que *dbt*). Toutes ces options sont d’ores et déjà disponibles et décrites dans la [documentation de l’outil](https://github.com/H-Max/bq2dbt?tab=readme-ov-file&ref=sfeir.dev#cli-arguments). ![](https://storage.ghost.io/c/0b/31/0b31478e-a66a-46f1-8c34-d44388e21192/content/images/2024/07/article4-1.png) Un tas des pièces mécaniques graisseuses ## [Open source](https://www.sfeir.dev/open-source-pourquoi-faire/) Après quelques semaines d’utilisation, et pour gagner encore plus de temps, une des dernières options ajoutées dans l’outil permet de générer les fichiers pour toutes tables et vues d’un dataset. Dans ce cas précis, le script va lister les tables et les vues et générer les fichiers pour chacune d'entre elles. Tout récemment également, l'ajout de la récupération des informations de *clustering* et de *partitioning* des tables automatise encore plus la génération de YAML complet de définition des tables. Complètement open source (please contribute!), il y a déjà quelques todos à prendre, notamment le fait de pouvoir fusionner le YAML généré avec un fichier existant qui contiendrait des définitions de test *dbt* par exemple, générer uniquement le SQL ou le YAML directement vers stdout (plutôt que vers un fichier, afin de pouvoir le “piper” facilement vers d’autres outils) et bien sûr une meilleure gestion des erreurs et des tests unitaires ! Enfin, il est prévu d’utiliser uniquement le SDK BigQuery en [Python ](https://www.sfeir.dev/kesaco-python/)plutôt qu’aller interroger manuellement les tables INFORMATION\_SCHEMA, à condition que toutes les informations nécessaires soient disponibles. Facile à installer, facile à utiliser : si vous avez du code *dbt* à générer dans un projet de migration depuis des tables existantes, essayez-le !