ETL inversé de Snowflake vers Spanner à l'aide de BigQuery

1. Créer un pipeline ETL inversé de Snowflake vers Spanner à l'aide de Google Cloud Storage et BigQuery

Introduction

Dans cet atelier de programmation, un pipeline ETL inversé est créé de Snowflake à Spanner. Traditionnellement, les pipelines ETL (Extract, Transform, Load) déplacent les données des bases de données opérationnelles vers un entrepôt de données comme Snowflake pour l'analyse. Un pipeline ETL inversé fait le contraire : il transfère les données traitées et organisées depuis l'entrepôt de données vers les systèmes opérationnels, où elles peuvent alimenter des applications, servir des fonctionnalités destinées aux utilisateurs ou être utilisées pour la prise de décision en temps réel.

L'objectif est de transférer un ensemble de données agrégées d'une table Snowflake Iceberg vers Spanner, une base de données relationnelle distribuée à l'échelle mondiale, idéale pour les applications à haute disponibilité.

Pour ce faire, Google Cloud Storage (GCS) et BigQuery sont utilisés comme étapes intermédiaires. Voici une présentation du flux de données et de la logique de cette architecture :

1. Snowflake vers Google Cloud Storage (GCS) au format Iceberg :

• La première étape consiste à extraire les données de Snowflake dans un format ouvert et bien défini. La table est exportée au format Apache Iceberg. Ce processus écrit les données sous-jacentes sous la forme d'un ensemble de fichiers Parquet et les métadonnées de la table (schéma, partitions, emplacements des fichiers) sous la forme de fichiers JSON et Avro. En stockant cette structure de table complète dans GCS, les données deviennent portables et accessibles à tout système compatible avec le format Iceberg.

2. Convertir des tables Iceberg dans GCS en tables externes BigLake BigQuery :

• Au lieu de charger les données directement depuis GCS dans Spanner, BigQuery est utilisé comme intermédiaire puissant. Vous allez créer une table externe BigLake dans BigQuery qui pointe directement vers le fichier de métadonnées Iceberg dans GCS. Cette approche présente plusieurs avantages :
• Aucune duplication des données : BigQuery lit la structure de la table à partir des métadonnées et interroge les fichiers de données Parquet sur place sans les ingérer, ce qui permet de gagner beaucoup de temps et de réduire considérablement les coûts de stockage.
• Requêtes fédérées : elles permettent d'exécuter des requêtes SQL complexes sur des données GCS comme s'il s'agissait d'une table BigQuery native.

3. BigQuery vers Spanner :

• La dernière étape consiste à transférer les données de BigQuery vers Spanner. Pour ce faire, vous allez utiliser une fonctionnalité puissante de BigQuery appelée requête EXPORT DATA, qui correspond à l'étape "Reverse ETL".
• Aptitude opérationnelle : Spanner est conçu pour les charges de travail transactionnelles, offrant une cohérence forte et une haute disponibilité pour les applications. En transférant les données dans Spanner, vous les rendez accessibles aux applications destinées aux utilisateurs, aux API et aux autres systèmes opérationnels qui nécessitent des recherches ponctuelles à faible latence.
• Évolutivité : ce modèle permet d'exploiter la puissance analytique de BigQuery pour traiter de grands ensembles de données, puis de diffuser les résultats efficacement grâce à l'infrastructure évolutive à l'échelle mondiale de Spanner.

Services et terminologie

• Snowflake : plate-forme de données cloud qui fournit un entrepôt de données en tant que service.
• Spanner : base de données relationnelle entièrement gérée et distribuée à l'échelle mondiale.
• Google Cloud Storage : offre de stockage d'objets blob de Google Cloud.
• BigQuery : entrepôt de données sans serveur entièrement géré pour l'analyse.
• Iceberg : format de table ouvert défini par Apache, qui fournit une abstraction sur les formats de fichiers de données Open Source courants.
• Parquet : format de fichier de données binaires en colonnes Open Source d'Apache.

Points abordés

• Charger des données dans Snowflake
• Créer un bucket GCS
• Exporter une table Snowflake vers GCS au format Iceberg
• Configurer une instance Spanner
• Charger des tables externes BigLake dans BigQuery vers Spanner

2. Configuration, exigences et limites

Prérequis

• Un compte Snowflake
• Pour exporter des données de BigQuery vers Spanner, vous devez disposer d'un compte Google Cloud avec une réservation BigQuery de niveau Enterprise ou supérieur.
• Accès à la console Google Cloud via un navigateur Web
• Un terminal pour exécuter les commandes Google Cloud CLI
• Si le règlement iam.allowedPolicyMemberDomains est activé dans votre organisation Google Cloud, un administrateur devra peut-être accorder une exception pour autoriser les comptes de service provenant de domaines externes. Nous aborderons ce point ultérieurement, le cas échéant.

Limites

Il est important de connaître certaines limites et incompatibilités de types de données qui peuvent survenir dans ce pipeline.

Snowflake vers Iceberg

Les types de données des colonnes diffèrent entre Snowflake et Iceberg. Pour en savoir plus sur la traduction entre ces types de données, consultez la documentation Snowflake.

Iceberg vers BigQuery

Lorsque vous utilisez BigQuery pour interroger des tables Iceberg, certaines limites s'appliquent. Pour obtenir la liste complète, consultez la documentation BigQuery. Notez que les types tels que BIGNUMERIC, INTERVAL, JSON, RANGE ou GEOGRAPHY ne sont actuellement pas acceptés.

BigQuery vers Spanner

La commande EXPORT DATA de BigQuery vers Spanner n'est pas compatible avec tous les types de données BigQuery. L'exportation d'une table avec les types suivants générera une erreur :

• STRUCT
• GEOGRAPHY
• DATETIME
• RANGE
• TIME

De plus, si le projet BigQuery utilise le dialecte GoogleSQL, les types numériques suivants ne sont pas non plus compatibles avec l'exportation vers Spanner :

• BIGNUMERIC

Pour obtenir la liste complète et à jour des limites, consultez la documentation officielle : Limites d'exportation vers Spanner.

Snowflake

Pour cet atelier de programmation, vous pouvez utiliser un compte Snowflake existant ou configurer un compte d'essai sans frais.

Autorisations Google Cloud Platform IAM

Le compte Google doit disposer des autorisations suivantes pour exécuter toutes les étapes de cet atelier de programmation.

Comptes de service
iam.serviceAccountKeys.create	Permet de créer des comptes de service.
Spanner
spanner.instances.create	Permet de créer une instance Spanner.
spanner.databases.create	Permet d'exécuter des instructions LDD pour créer
spanner.databases.updateDdl	Permet d'exécuter des instructions LDD pour créer des tables dans la base de données.
Google Cloud Storage
storage.buckets.create	Permet de créer un bucket GCS pour stocker les fichiers Parquet exportés.
storage.objects.create	Permet d'écrire les fichiers Parquet exportés dans le bucket GCS.
storage.objects.get	Permet à BigQuery de lire les fichiers Parquet à partir du bucket GCS.
storage.objects.list	Permet à BigQuery de lister les fichiers Parquet dans le bucket GCS.
Dataflow
Dataflow.workitems.lease	Permet de revendiquer des éléments de travail à partir de Dataflow.
Dataflow.workitems.sendMessage	Permet au nœud de calcul Dataflow de renvoyer des messages au service Dataflow.
Logging.logEntries.create	Permet aux nœuds de calcul Dataflow d'écrire des entrées de journal dans Google Cloud Logging.

Pour plus de commodité, vous pouvez utiliser des rôles prédéfinis contenant ces autorisations.

roles/resourcemanager.projectIamAdmin

roles/iam.serviceAccountKeyAdmin

roles/spanner.instanceAdmin

roles/spanner.databaseAdmin

roles/storage.admin

roles/dataflow.serviceAgent

roles/dataflow.worker

roles/dataflow.serviceAgent

Configurer des propriétés réutilisables

Vous aurez besoin de certaines valeurs à plusieurs reprises au cours de cet atelier. Pour faciliter cette opération, nous allons définir ces valeurs sur des variables shell à utiliser ultérieurement.

• GCP_REGION : région spécifique dans laquelle les ressources GCP seront situées. Pour consulter la liste des régions, cliquez ici.
• GCP_PROJECT : ID du projet GCP à utiliser.
• GCP_BUCKET_NAME : nom du bucket GCS à créer et dans lequel les fichiers de données seront stockés.

Les noms des buckets doivent être uniques. Un schéma courant est le suivant : [organization_name]-[project_id]-[environment]-[purpose].

export GCP_REGION = <GCP REGION HERE> 
export GCP_PROJECT= <GCP PROJECT HERE>
export GCS_BUCKET_NAME = <GCS BUCKET NAME HERE>
export SPANNER_INSTANCE = <SPANNER INSTANCE ID HERE>
export SPANNER_DB = <SPANNER DATABASE ID HERE>

Notez que ces variables d'environnement ne seront définies que dans l'instance de shell dans laquelle elles sont exécutées. Vous devrez les définir à nouveau si vous exécutez les commandes de cet atelier dans une autre instance de shell ultérieurement.

Projet Google Cloud

Un projet est une unité d'organisation de base dans Google Cloud. Si un administrateur vous en a fourni un, vous pouvez ignorer cette étape.

Vous pouvez créer un projet à l'aide de la CLI comme suit :

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

Pour en savoir plus sur la création et la gestion de projets, cliquez ici.

Configurer Spanner

Pour commencer à utiliser Spanner, vous devez provisionner une instance et une base de données. Pour en savoir plus sur la configuration et la création d'une instance Spanner, cliquez ici.

Créer l'instance

gcloud spanner instances create $SPANNER_INSTANCE \
--config=regional-$GCP_REGION \
--description="Codelabs Snowflake RETL" \
--processing-units=100 \
--edition=ENTERPRISE

Créer la base de données

gcloud spanner databases create $SPANNER_DB \
--instance=$SPANNER_INSTANCE

3. créer un bucket Google Cloud Storage ;

Google Cloud Storage (GCS) sera utilisé pour stocker les fichiers de données Parquet et les métadonnées Iceberg générés par Snowflake. Pour ce faire, vous devrez d'abord créer un bucket à utiliser comme destination des fichiers. Suivez ces étapes dans une fenêtre de terminal sur une machine locale.

Créer le bucket

Utilisez la commande suivante pour créer un bucket de stockage dans une région spécifique (par exemple, us-central1).

gcloud storage buckets create gs://$GCS_BUCKET_NAME --location=$GCP_REGION

Vérifier la création du bucket

Une fois cette commande exécutée, vérifiez le résultat en listant tous les buckets. Le nouveau bucket doit apparaître dans la liste des résultats. Les références de bucket sont généralement précédées du préfixe gs://.

gcloud storage ls | grep gs://$GCS_BUCKET_NAME

Tester les autorisations d'écriture

Cette étape permet de s'assurer que l'environnement local est correctement authentifié et qu'il dispose des autorisations nécessaires pour écrire des fichiers dans le bucket nouvellement créé.

echo "Hello, GCS" | gcloud storage cp - gs://$GCS_BUCKET_NAME/hello.txt

Vérifier le fichier importé

Répertoriez les objets du bucket. Le chemin d'accès complet du fichier que vous venez d'importer devrait s'afficher.

gcloud storage ls gs://$GCS_BUCKET_NAME

Vous devriez obtenir le résultat suivant :

gs://$GCS_BUCKET_NAME/hello.txt

Pour afficher le contenu d'un objet dans un bucket, vous pouvez utiliser gcloud storage cat.

gcloud storage cat gs://$GCS_BUCKET_NAME/hello.txt

Le contenu du fichier doit être visible :

Hello, GCS

Nettoyer le fichier de test

Le bucket Cloud Storage est maintenant configuré. Vous pouvez maintenant supprimer le fichier de test temporaire.

gcloud storage rm gs://$GCS_BUCKET_NAME/hello.txt

Le résultat doit confirmer la suppression :

Removing gs://$GCS_BUCKET_NAME/hello.txt...
/ [1 objects]
Operation completed over 1 objects.

4. Exporter des données de Snowflake vers GCS

Pour cet atelier, vous utiliserez l'ensemble de données TPC-H, qui est une référence standard de l'industrie pour les systèmes d'aide à la décision. Son schéma modélise un environnement commercial réaliste avec des clients, des commandes, des fournisseurs et des pièces, ce qui le rend idéal pour illustrer un scénario d'analyse et de transfert de données réel. Cet ensemble de données est disponible par défaut dans tous les comptes Snowflake.

Au lieu d'utiliser les tables TPC-H brutes et normalisées, vous allez créer une table agrégée. Cette nouvelle table joindra les données des tables orders, customer et nation pour produire une vue dénormalisée et récapitulative des totaux des ventes nationales. Cette étape de pré-agrégation est une pratique courante dans l'analyse, car elle prépare les données pour un cas d'utilisation spécifique (dans ce scénario, pour la consommation par une application opérationnelle).

Autoriser Snowflake à accéder à Google Cloud Storage

Pour permettre à Snowflake d'écrire des données dans le bucket GCS, vous devez créer un volume externe et les autorisations nécessaires.

• Un volume externe est un objet Snowflake qui fournit un lien sécurisé vers un emplacement spécifique dans un bucket GCS. Il ne stocke pas de données lui-même, mais contient la configuration nécessaire à Snowflake pour accéder au stockage cloud.
• Pour des raisons de sécurité, les buckets de stockage cloud sont privés par défaut. Lorsqu'un volume externe est créé, Snowflake génère un compte de service dédié. Ce compte de service doit être autorisé à lire et à écrire dans le bucket.

Créer une base de données

1. Dans le menu latéral de gauche, sous Catalogue Horizon, pointez sur Catalogue, puis cliquez sur Explorateur de base de données.
2. Une fois sur la page Bases de données, cliquez sur le bouton + Base de données en haut à droite.
3. Nommez la nouvelle base de données codelabs_retl_db.

Créer une feuille de calcul

Pour exécuter des commandes SQL sur la base de données, vous aurez besoin de feuilles de calcul.

Pour créer une feuille de calcul :

1. Dans le menu de gauche, sous Travailler avec des données, pointez sur Projets, puis cliquez sur Espaces de travail.
2. Dans la barre latérale Mes espaces de travail, cliquez sur le bouton + Ajouter, puis sélectionnez Fichier SQL.

Toutes les autres commandes SQL à Snowflake peuvent être exécutées en remplaçant le contenu de ce fichier. Pour exécuter des sections de paragraphes du fichier, placez le curseur sur le paragraphe en question et appuyez sur Ctrl+Entrée sur votre clavier.

Créer un volume externe

Exécutez la commande suivante dans une feuille de calcul Snowflake pour créer le volume.

Notez que Snowflake utilise le préfixe gcs:// pour les chemins Google Cloud Storage, et non gs://.

Étant donné que le code SQL suivant ne sera pas exécuté dans l'instance de shell avec les variables d'environnement définies, veillez à remplacer les valeurs de votre nom de bucket GCS où elles apparaissent.

CREATE EXTERNAL VOLUME codelabs_retl_ext_vol
STORAGE_LOCATIONS = 
(
  (
    NAME = 'codelabs_retl_ext_vol'
    STORAGE_PROVIDER = 'GCS'
    STORAGE_BASE_URL = 'gcs://<Your bucket name>/snowflake_extvol'
  )
); 

Obtenir le compte de service Snowflake

DESC (describe) le volume externe nouvellement créé pour obtenir le compte de service unique que Snowflake a généré pour celui-ci.

DESC EXTERNAL VOLUME codelabs_retl_ext_vol;

1. Dans le volet des résultats, recherchez les propriétés JSON et l'entrée property_value contenant une chaîne JSON commençant par "NAME":"codelabs_retl_ext_vol".
2. Recherchez la propriété STORAGE_GCP_SERVICE_ACCOUNT dans l'objet JSON et copiez sa valeur (elle ressemble à une adresse e-mail). Il s'agit de l'identifiant du compte de service qui doit avoir accès au bucket GCS.
3. Stockez ce compte de service dans une variable d'environnement dans votre instance de shell pour une réutilisation ultérieure.

export GCP_SERVICE_ACCOUNT=<Your service account>

Remarque : Si votre organisation Google Cloud applique la contrainte iam.allowedPolicyMemberDomains, l'administrateur de sécurité devra ajouter le domaine de ce compte de service (la partie après le symbole @) à la liste des domaines autorisés.

Accorder des autorisations GCS à Snowflake

À présent, le compte de service Snowflake doit être autorisé à écrire dans le bucket GCS.

gcloud storage buckets add-iam-policy-binding gs://$GCS_BUCKET_NAME \
    --member="serviceAccount:$GCP_SERVICE_ACCOUNT" \
    --role="roles/storage.objectAdmin"

gcloud storage buckets add-iam-policy-binding gs://$GCS_BUCKET_NAME \
    --member="serviceAccount:$GCP_SERVICE_ACCOUNT" \
    --role="roles/storage.legacyBucketReader"

Vérifier l'accès dans Snowflake

De retour dans la feuille de calcul Snowflake, exécutez cette commande pour vérifier que Snowflake peut désormais se connecter au bucket GCS.

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('codelabs_retl_ext_vol');

Le résultat doit être un objet JSON contenant "success":true.

Pour en savoir plus sur les volumes externes dans Snowflake, consultez la documentation officielle.

Exporter des exemples de données de commande

Vous pouvez maintenant créer une table Iceberg dans Snowflake. La commande suivante indique à Snowflake d'exécuter une requête et de stocker les résultats dans GCS au format Iceberg. Les fichiers de données seront au format Parquet, et les métadonnées seront au format Avro et JSON. Tous seront stockés à l'emplacement défini par le volume externe codelabs_retl_ext_vol.

Créer une base de données

1. Dans le menu latéral de gauche, sous Catalogue Horizon, pointez sur Catalogue, puis cliquez sur Explorateur de base de données.
2. Une fois sur la page Bases de données, cliquez sur le bouton + Base de données en haut à droite.
3. Nommez la nouvelle base de données codelabs_retl_db.

USE DATABASE codelabs_retl_db; 

CREATE ICEBERG TABLE REGIONAL_SALES_ICEBERG (
    NATION_NAME STRING,
    MARKET_SEGMENT STRING,
    ORDER_YEAR INTEGER,
    ORDER_PRIORITY STRING,
    TOTAL_ORDER_COUNT INTEGER,
    TOTAL_REVENUE NUMBER(24,2),
    UNIQUE_CUSTOMER_COUNT INTEGER
) 
EXTERNAL_VOLUME = 'codelabs_retl_ext_vol'
CATALOG = 'SNOWFLAKE'
BASE_LOCATION = 'regional_sales_iceberg'
AS (
    SELECT 
        n.n_name AS nation_name,
        c.c_mktsegment AS market_segment,
        YEAR(o.o_orderdate) AS order_year,
        o.o_orderpriority AS order_priority,
        COUNT(o.o_orderkey) AS total_order_count,
        ROUND(SUM(o.o_totalprice), 2) AS total_revenue,
        COUNT(DISTINCT c.c_custkey) AS unique_customer_count
    FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.orders AS o
    INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.customer AS c 
        ON o.o_custkey = c.c_custkey
    INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.nation AS n
        ON c.c_nationkey = n.n_nationkey
    GROUP BY 
        n.n_name, 
        c.c_mktsegment, 
        YEAR(o.o_orderdate), 
        o.o_orderpriority
);

Pour en savoir plus sur la création et la gestion des tables Iceberg avec Snowflake, consultez la documentation officielle.

Vérifier les données dans GCP

Vérifiez maintenant le bucket GCS. Les fichiers créés par Snowflake devraient être visibles. Cela confirme que l'exportation a réussi. Les métadonnées Iceberg se trouvent dans le dossier metadata et les données réelles sous forme de fichiers Parquet dans le dossier data.

gcloud storage ls "gs://$GCS_BUCKET_NAME/snowflake_extvol/**"

Les noms de fichiers exacts varient, mais la structure doit ressembler à ceci :

gs://$GCS_BUCKET_NAME/snowflake_extvol/
gs://$GCS_BUCKET_NAME/snowflake_extvol/regional_sales_iceberg.snvrAWuR/data/snow_cbsKIRmdDmo_wLg128fugxg_0_2_009.parquet
...
gs://$GCS_BUCKET_NAME/snowflake_extvol/regional_sales_iceberg.snvrAWuR/metadata/00001-62f831ff-6708-4494-94c5-c891b7ad447f.metadata.json
...

Les données ont maintenant été copiées depuis Snowflake vers Google Cloud Storage au format Iceberg.

Pendant que nous avons cette liste, enregistrons le fichier metadata.json dans une variable d'environnement, car nous en aurons besoin plus tard.

export GCS_METADATA_JSON=$(gcloud storage ls "gs://$GCS_BUCKET_NAME/snowflake_extvol/**" | grep .metadata.json)

5. Configurer une table externe BigQuery

Maintenant que la table Iceberg se trouve dans Google Cloud Storage, l'étape suivante consiste à la rendre accessible à BigQuery. Pour ce faire, créez une table externe BigLake.

BigLake est un moteur de stockage qui permet de créer des tables dans BigQuery qui lisent les données directement à partir de sources externes telles que Google Cloud Storage. Pour cet atelier, il s'agit de la technologie clé qui permet à BigQuery de comprendre la table Iceberg qui vient d'être exportée sans avoir à ingérer les données.

Pour que cela fonctionne, deux composants sont nécessaires :

1. Connexion à une ressource cloud : il s'agit d'un lien sécurisé entre BigQuery et GCS. Il utilise un compte de service spécial pour gérer l'authentification, ce qui garantit que BigQuery dispose des autorisations nécessaires pour lire les fichiers du bucket GCS.
2. Définition d'une table externe : elle indique à BigQuery où trouver le fichier de métadonnées de la table Iceberg dans GCS et comment l'interpréter.

Configurer une connexion à Google Cloud Storage

Vous allez d'abord créer la connexion qui permet à BigQuery d'accéder à GCS. Cette commande crée une ressource de connexion dans BigQuery.

bq mk \
  --connection \
  --project_id=$GCP_PROJECT \
  --location=$GCP_REGION \
  --connection_type=CLOUD_RESOURCE \
  codelabs-retl-connection

Si l'opération réussit, vous obtiendrez un résultat semblable à ceci :

Connection 12345678.region.codelabs-retl-connection successfully created

Pour en savoir plus sur les connexions de ressources Cloud dans BigQuery, consultez la documentation Google Cloud.

Autoriser la connexion BigQuery à lire les données

La nouvelle connexion BigQuery possède son propre compte de service, qui doit être autorisé à lire les données du bucket Google Cloud Storage.

1. Obtenir le compte de service de la connexion

Commencez par obtenir l'ID du compte de service à partir de la connexion que vous venez de créer :

bq show \
  --location $GCP_REGION \
  --connection codelabs-retl-connection

Les résultats affichent un tableau des connexions correspondantes.

Définissons serviceAccountId sur une variable d'environnement à utiliser ultérieurement.

export GCP_BQ_SERVICE_ACCOUNT=<Your service account email>

2. Octroyer des autorisations

Autorisez le compte de service à afficher les données du bucket GCS en exécutant la commande suivante.

gcloud storage buckets add-iam-policy-binding \
  gs://$GCS_BUCKET_NAME \
  --member serviceAccount:$GCP_BQ_SERVICE_ACCOUNT \
  --role roles/storage.objectViewer

Créer la table externe

Créez ensuite la table externe BigLake dans BigQuery. Cette commande ne déplace aucune donnée. Il crée simplement un pointeur vers les données existantes dans GCS. Vous aurez besoin du chemin d'accès à l'un des fichiers .metadata.json créés par Snowflake.

Dans la commande suivante, remplacez la valeur external_table_definition par le chemin d'accès approprié à votre fichier metadata.json.

Assurez-vous que cette table est créée dans la même région que la connexion de l'étape précédente.

bq mk --dataset --location=$GCP_REGION codelabs_retl

bq mk \
    --table \
    --location=$GCP_REGION \
--external_table_definition=ICEBERG=$GCS_METADATA_JSON@projects/$GCP_PROJECT/locations/$GCP_REGION/connections/codelabs-retl-connection \
    codelabs_retl.regional_sales

Vérifier les données dans BigQuery

Vous pouvez désormais interroger cette table à l'aide du langage SQL standard, comme n'importe quelle autre table BigQuery. BigQuery utilisera la connexion pour lire les fichiers Parquet depuis GCS à la volée.

bq query \
  --location=$GCP_REGION \
  --nouse_legacy_sql "SELECT * FROM \`$GCP_PROJECT.codelabs_retl.regional_sales\` LIMIT 10;"

6. Importer des données de BigQuery vers Spanner : dernière étape

Nous arrivons à la dernière étape, et la plus importante, du pipeline : déplacer les données de la table BigLake vers Spanner. Il s'agit de l'étape d'ETL inversé, où les données, après avoir été traitées et organisées dans l'entrepôt de données, sont chargées dans un système opérationnel pour être utilisées par les applications.

Spanner est une base de données relationnelle entièrement gérée et distribuée à l'échelle mondiale. Il offre la cohérence transactionnelle d'une base de données relationnelle traditionnelle, mais avec l'évolutivité horizontale d'une base de données NoSQL. Il s'agit donc d'un choix idéal pour créer des applications évolutives et à haute disponibilité.

Voici comment cela se déroulera :

1. Créez un schéma de table dans la base de données Spanner qui correspond à la structure des données.
2. Exécutez une requête EXPORT DATA BigQuery pour charger les données de la table BigLake directement dans la table Spanner.

Créer la table Spanner

Avant de transférer des données depuis BigQuery, vous devez créer une table de destination dans Spanner avec un schéma compatible.

gcloud spanner databases ddl update $SPANNER_DB \
  --instance=$SPANNER_INSTANCE \
  --ddl="$(cat <<EOF
CREATE TABLE regional_sales (
    nation_name STRING(MAX),
    market_segment STRING(MAX),
    order_year INT64,
    order_priority STRING(MAX),
    total_order_count INT64,
    total_revenue NUMERIC,
    unique_customer_count INT64
) PRIMARY KEY (nation_name, market_segment, order_year, order_priority);
EOF
)"

Exporter des données depuis BigQuery

Il s'agit de la dernière étape. Une fois les données sources prêtes dans une table BigLake BigQuery et la table de destination créée dans Spanner, le transfert de données proprement dit est étonnamment simple. Une seule requête SQL BigQuery sera utilisée : EXPORT DATA.

Cette requête est spécialement conçue pour ce type de scénario. Il permet d'exporter efficacement les données d'une table BigQuery (y compris les tables externes comme BigLake) vers une destination externe. Dans ce cas, la destination est la table Spanner.

bq query --location=$GCP_REGION --use_legacy_sql=false <<EOF
EXPORT DATA OPTIONS (
uri="https://spanner.googleapis.com/projects/${GCP_PROJECT}/instances/${SPANNER_INSTANCE}/databases/${SPANNER_DB}",
  format='CLOUD_SPANNER',
  spanner_options="""{ 
      "table": "regional_sales", 
      "priority": "HIGH" 
      }"""
) AS 
SELECT * FROM \`${PROJECT_ID}.codelabs_retl.regional_sales\`
EOF

Une fois la requête terminée, le volet "Résultats" doit indiquer "Mise à jour terminée".

7. Vérifier les données dans Spanner

Félicitations ! Un pipeline Reverse ETL complet a été créé et exécuté avec succès. La dernière étape consiste à vérifier que les données sont bien arrivées dans Spanner.

gcloud spanner databases execute-sql \
  --instance=$SPANNER_INSTANCE \
  $SPANNER_DB \
  --sql='SELECT * FROM regional_sales LIMIT 10'

L'échantillon de données importé s'affiche comme demandé :

nation_name  market_segment  order_year  order_priority   total_order_count  total_revenue  unique_customer_count
ALGERIA      AUTOMOBILE      1992        1-URGENT         375                59232423.66    298
ALGERIA      AUTOMOBILE      1992        2-HIGH           328                47371891.08    269
ALGERIA      AUTOMOBILE      1992        3-MEDIUM         346                52823195.87    262
ALGERIA      AUTOMOBILE      1992        4-NOT SPECIFIED  365                52935998.34    288
ALGERIA      AUTOMOBILE      1992        5-LOW            380                54920263.68    293
ALGERIA      AUTOMOBILE      1993        1-URGENT         394                63145618.78    312
ALGERIA      AUTOMOBILE      1993        2-HIGH           340                50737488.4     277
ALGERIA      AUTOMOBILE      1993        3-MEDIUM         383                55871057.46    298
ALGERIA      AUTOMOBILE      1993        4-NOT SPECIFIED  365                56424662.05    291
ALGERIA      AUTOMOBILE      1993        5-LOW            363                54673249.06    283

Le fossé entre les mondes des données analytiques et opérationnelles a été comblé.

8. Effectuer un nettoyage

Nettoyer Spanner

Supprimer la base de données et l'instance Spanner

gcloud spanner instances delete $SPANNER_INSTANCE

Nettoyer GCS

Supprimer le bucket GCS créé pour héberger les données

gcloud storage rm --recursive gs://$GCS_BUCKET_NAME

Nettoyer BigQuery

bq rm -r codelabs_retl
bq rm --connection --location=$GCP_REGION codelabs-retl-connection

Nettoyer Snowflake

Supprimer la base de données

1. Dans le menu latéral de gauche, sous Catalogue Horizon, pointez sur Catalogue,puis cliquez sur Explorateur de base de données.
2. Cliquez sur ... à droite de la base de données CODELABS_RETL_DB pour développer les options, puis sélectionnez Drop (Supprimer).
3. Dans la boîte de dialogue de confirmation qui s'affiche, sélectionnez Drop Database (Supprimer la base de données).

Supprimer des classeurs

4. Dans le menu de gauche, sous Travailler avec des données, pointez sur Projets, puis cliquez sur Espaces de travail.
5. Dans la barre latérale Mon espace de travail, pointez sur les différents fichiers d'espace de travail que vous avez utilisés pour cet atelier afin d'afficher les options supplémentaires ..., puis cliquez dessus.
6. Sélectionnez Supprimer, puis à nouveau Supprimer dans la boîte de dialogue de confirmation qui s'affiche.
7. Faites-le pour tous les fichiers d'espace de travail SQL que vous avez créés pour cet atelier.

Supprimer des volumes externes

8. Dans le menu de gauche, sous Catalogue Horizon, pointez sur Catalogue, puis cliquez sur Données externes.
9. Cliquez sur  à droite de CODELABS_RETL_EXT_VOL, puis sélectionnez Supprimer le volume externe, puis de nouveau sur Supprimer le volume externe dans la boîte de dialogue de confirmation.

9. Félicitations

Bravo ! Vous avez terminé cet atelier de programmation.

Points abordés

• Charger des données dans Snowflake
• Créer un bucket GCS
• Exporter une table Snowflake vers GCS au format CSV
• Configurer une instance Spanner
• Charger des tables CSV dans Spanner avec Dataflow