bq2dbt : un outil pour migrer vers dbt des tables BigQuery existantes

Depuis que j’utilise le couple Google BigQuery et dbt 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 en profitons 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”) 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 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 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 pour faire la plus grosse partie du travail. Investir quelques heures pour en économiser des dizaines me semblait judicieux.

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 qui contiennent entre autres :

• Les définitions de colonnes : COLUMNS
• Les chemins complets des colonnes : COLUMN_FIELD_PATHS
• Les clés / contraintes sur les colonnes : KEY_COLUMN_USAGE

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.

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 :

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 :

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.

Open source

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