BigQuery : cache-cache avec vos données

Dans cet article, nous comparons différentes approches pour cartographier votre empreinte BigQuery et vous présentons un script Python maison pour une flexibilité totale.

Dans le monde en perpétuelle évolution de la data analytics, Google BigQuery s'impose comme un data warehouse serverless puissant, capable d'exécuter des requêtes SQL ultra-rapides sur d'immenses volumes de données. Que vous soyez data scientist, data engineer ou expert analytics, explorer l'étendue des fonctionnalités de BigQuery tient parfois de la chasse au trésor. À mesure que votre organisation se développe, votre empreinte BigQuery grandit avec elle, et l'un des défis récurrents consiste à lister efficacement tables et datasets selon différents périmètres.

Cet article a pour objectif de vous guider à travers les différentes méthodes pour lister tables et datasets dans BigQuery, avec pour ambition finale de répertorier ces ressources à l'échelle de votre organisation entière, tout en mettant en lumière les limites propres à chaque approche.

En parcourant ces différentes techniques, des interactions GUI les plus simples aux requêtes SQL et appels d'API plus avancés, vous découvrirez leurs forces et leurs limites. Pour conclure, nous présenterons une solution finale qui offre la plus grande flexibilité, au prix d'un peu plus de travail — sauf que nous avons déjà fait le plus gros pour vous !

Voici les options que nous allons passer en revue :

• L'interface web Google Cloud Console
• BigQuery CLI
• Dataplex (Data Catalog)
• INFORMATION_SCHEMA
• Script personnalisé

Et voici les critères d'évaluation que nous garderons en tête :

• Périmètre du listing
• Niveau de détail
• Flexibilité
• Coût

Le point de départ naturel reste l'interface web Google Cloud Console. En accédant à BigQuery Studio depuis la page produit BigQuery, vous retrouvez datasets et tables de votre projet en cours (et d'autres projets si vous les avez ajoutés) dans le volet Explorer. Vous pouvez aussi rechercher des ressources (dataset, table ou vue) par nom ou par label, et obtenir des résultats couvrant à la fois les projects et les organisations auxquels vous avez accès.

BigQuery UI Datasets (à gauche) et Tables (à droite)

Avantages :

• Accessible directement depuis BigQuery Studio.
• La recherche couvre projects et organisations, sans limite de périmètre.
• L'approche est gratuite.

Inconvénients :

• Elle ne fournit pas de liste exhaustive de tous les datasets ou tables de votre organisation, ni de ceux auxquels vous avez accès.
• Elle ne renvoie aucune autre métadonnée sur vos tables.

Si les interfaces graphiques ne sont pas votre tasse de thé et que vous préférez la ligne de commande, BigQuery CLI vous tentera sans doute davantage.

Avec un PROJECT_ID, vous pouvez lister tous les datasets de ce projet via :

bq ls --project_id $PROJECT_ID

BigQuery CLI Datasets

Ou, avec PROJECT_ID et DATASET_ID, vous pouvez lister toutes les tables de ce dataset via :

bq ls --project_id $PROJECT_ID --dataset_id=$DATASET_ID

BigQuery CLI Tables

Avantages :

• CLI simple à prendre en main.
• La sortie peut alimenter des traitements légers en aval, par exemple avec jq, notamment via le flag --format: <none|json|prettyjson|csv|sparse|pretty>.
• Les opérations sur les métadonnées sont gratuites.

Inconvénients :

• Le périmètre du listing reste limité : un projet pour le niveau dataset, un dataset pour le niveau table.
• Les détails renvoyés sont eux aussi limités.

Quand il s'agit de retrouver toutes les données pertinentes d'une organisation, on pense évidemment à Data Catalog ! Auparavant produit autonome, Data Catalog est devenu une fonctionnalité de Dataplex depuis mi-2022. Dataplex, c'est la plateforme intelligente de gestion des données de Google Cloud : elle automatise l'organisation, la sécurisation et l'analyse des données à travers data lakes, data warehouses et bases de données, et accompagne votre organisation sur la découvrabilité, la gouvernance et la conformité. Data Catalog joue le rôle d'inventaire central des actifs de données de l'organisation.

Il permet de chercher à travers organisations et systèmes, de filtrer par type de données, tags, etc. Pour notre cas d'usage, nous pouvons récupérer tous les datasets et tables d'une organisation donnée en appliquant simplement le filtre adéquat dans l'UI :

Bonne nouvelle : pour celles et ceux qui préfèrent la CLI à l'UI, on obtient le même résultat avec une commande gcloud :

Pour les datasets :

gcloud data-catalog search "type=dataset" --include-organization-ids=YOUR_ORG_ID

Dataplex Datasets

Pour les tables :

gcloud data-catalog search "type=table" --include-organization-ids=YOUR_ORG_ID

Dataplex Tables

Avantages :

• Recherche à travers les organisations ET au-delà du seul périmètre BigQuery.

Inconvénients :

• Data Catalog stocke différents types de métadonnées (métier comme techniques), mais laisse de côté certains détails comme la taille des tables ou le total d'octets stockés.
• Ni Dataplex ni Data Catalog ne sont gratuits, mais la tarification reste raisonnable pour la plupart des cas d'usage. Voir la page de tarification pour plus de détails.

Les habitués de BigQuery connaissent forcément INFORMATION_SCHEMA. Les vues INFORMATION_SCHEMA de BigQuery sont des vues en lecture seule, définies par le système, qui exposent des métadonnées sur vos objets BigQuery. Elles sont nombreuses et variées, consultez la documentation pour un panorama complet.

Pour notre cas d'usage, deux vues retiennent particulièrement notre attention : SCHEMATA et TABLES :

La vue SCHEMATA permet de lister tous les datasets d'une région, par exemple pour us-central1 :

SELECT * FROM `region-us-central1`.INFORMATION_SCHEMA.SCHEMATA;

INFORMATION_SCHEMA Datasets

Ou la vue TABLES pour lister toutes les tables d'une région, par exemple pour us-central1 :

SELECT * FROM `region-us-central1`.INFORMATION_SCHEMA.TABLES;

INFORMATION_SCHEMA Tables

Avantages :

• Accès simple et programmatique en SQL, directement dans BigQuery.
• Informations très détaillées ; par exemple, la [vue TABLES](https://cloud.google.com/bigquery/docs/information-schema-tables#schema) inclut `creation_time`, `ddl`, la vue TABLE_STORAGE renseigne le total_rows ainsi que la taille de la table (stockage physique et logique).

Inconvénients :

• Information limitée à une région au mieux (y compris pour la vue au niveau organisation). La liste des régions étant déjà longue et amenée à évoluer, scripter par-dessus n'est pas idéal.
• Payant, même si la tarification reste raisonnable pour la plupart des cas d'usage.

Comme souvent, nos clients les plus pointus techniquement nous ont mis au défi : ils voulaient une visibilité sur l'organisation entière, tout en récupérant un niveau de détail conséquent sur les tables réparties entre les projets.

Nous avons donc écrit un petit script qui fait exactement cela, disponible sur notre GitHub. Le principe : définir quelques variables d'environnement qui provisionnent un compte de service au niveau organisation, puis itérer sur l'ensemble des projets et datasets de cette organisation pour récupérer les tables qu'ils contiennent ainsi que les détails utiles (ici, la taille de la table). Quelques bases en Python et une certaine familiarité avec l'API BigQuery sont nécessaires, mais cette approche offre la plus grande flexibilité (vous pouvez récupérer d'autres attributs de table en consultant la documentation de l'API), et les données peuvent être restituées dans le format de votre choix pour vos traitements en aval.

Avantages :

• Flexibilité maximale :

- Extraction de toutes les données souhaitées depuis l'API.

- Restitution des résultats dans le format de votre choix.

- Ajout de filtres ou extension du script à volonté.

• Gratuit, offert par DoiT.

Inconvénients :

• Du temps et un effort supplémentaires pour écrire le script — mais nous avons déjà fait le plus gros pour vous !

S'orienter dans BigQuery peut s'avérer complexe, chaque méthode présentant des atouts spécifiques selon les besoins de l'organisation. Là où les outils standards offrent simplicité et accessibilité, un script sur mesure ouvre la voie à une flexibilité et un niveau de détail incomparables, au prix d'un peu de travail en amont.

Avez-vous déjà adopté une autre approche ? N'hésitez pas à partager votre méthode préférée, ou à enrichir le script de base selon vos besoins !