Fédération de données sans ETL de BigQuery vers AlloyDB

1. Présentation

Dans la partie 1, nous avons réussi à transformer des PDF chaotiques et non structurés en tables propres, intelligentes et structurées dans BigQuery à l'aide de Knowledge Catalog et DataScan. Nous disposons désormais d'un entrepôt de données robuste.

Pour rappel, dans l'atelier de la partie 1, nous avons pris l'exemple d'une franchise fictive de yaourts glacés et converti 400 de ses fichiers PDF non structurés (comprenant du texte, des tableaux et des images) en tables BigQuery clairement structurées, avec des relations automatiquement déduites entre elles à l'aide de BigQuery Knowledge Catalog et Dataplex.

Ce que vous allez faire

Dans cette session, nous allons configurer AlloyDB pour PostgreSQL et faire quelque chose de magique : fédérer nos données BigQuery directement dans AlloyDB. Cela signifie que notre application transactionnelle peut interroger les données de notre entrepôt en temps réel, sans les copier ni les dupliquer.

En tant que développeur, vous devez vous poser la question suivante à ce stade :

"Si les données sont déjà dans BigQuery, pourquoi utiliser AlloyDB ? Pourquoi l'application n'exécute-t-elle pas simplement une instruction SELECT directement sur BigQuery ?"

Voici pourquoi :

Avec la fédération Lakehouse, vous pouvez utiliser le moteur de requêtes d'AlloyDB pour alimenter les charges de travail transactionnelles et analytiques de votre application à partir de la même interface. Vous pouvez également matérialiser ou importer ces données sur AlloyDB pour y accéder plus rapidement dans vos applications, ce qui vous permet d'utiliser AlloyDB AI et le moteur de données en colonnes.

Vous pouvez utiliser AlloyDB comme base de données transactionnelle et stocker de grandes quantités de données dans BigQuery ou BigLake. Vos applications s'intègrent généralement de manière indépendante à ces deux systèmes pour accéder aux données de ces différents services Google Cloud. La fédération Lakehouse pour AlloyDB vous permet d'utiliser la compatibilité des requêtes fédérées d'AlloyDB implémentée en tant que wrapper de données externes pour accéder aux données BigQuery et AlloyDB à l'aide d'une interface SQL dans AlloyDB.

Au lieu de créer un pipeline ETL fragile pour interroger les données BigQuery à partir d'AlloyDB, nous allons utiliser des requêtes fédérées. AlloyDB agira comme un point de terminaison unifié, accédant à BigQuery de manière transparente si nécessaire.

Commençons à créer !

Points abordés

• Configurer un cluster, une instance et un réseau AlloyDB en un clic
• Configurer l'extension pour se préparer à la fédération
• Configurer la fédération de BigQuery vers AlloyDB
• Effectuer un test

Conditions requises

• Un navigateur (Chrome ou Firefox, par exemple)
• Un projet Google Cloud avec facturation activée.
• Connaître les bases de SQL

2. Avant de commencer

Créer un projet

1. Dans la console Google Cloud, sur la page du sélecteur de projet, sélectionnez ou créez un projet Google Cloud.
2. Assurez-vous que la facturation est activée pour votre projet Cloud. Découvrez comment vérifier si la facturation est activée sur un projet.

Cet atelier propose des fonctionnalités limitées que vous pouvez tester même sans compte de facturation. Si vous souhaitez explorer les exercices pratiques en tant que tel, vous trouverez des notes, le cas échéant, dans les qualificatifs "VOUS N'AVEZ PAS DE COMPTE DE FACTURATION ?". Faites attention à eux.

3. Vous allez utiliser Cloud Shell, un environnement de ligne de commande exécuté dans Google Cloud. Cliquez sur "Activer Cloud Shell" en haut de la console Google Cloud.

4. Une fois connecté à Cloud Shell, vérifiez que vous êtes déjà authentifié et que le projet est défini sur votre ID de projet à l'aide de la commande suivante :

gcloud auth list

5. Exécutez la commande suivante dans Cloud Shell pour vérifier que la commande gcloud connaît votre projet.

gcloud config list project

6. Si vous souhaitez vous authentifier

gcloud auth login

7. Si votre projet n'est pas défini, utilisez la commande suivante pour le définir :

export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project <YOUR_PROJECT_ID>

8. Activez les API requises : exécutez cette commande pour activer toutes les API requises :

gcloud services enable alloydb.googleapis.com

Problèmes et dépannage

Syndrome du projet fantôme	Vous avez exécuté gcloud config set project, mais vous consultez en fait un autre projet dans l'interface utilisateur de la console. Vérifiez l'ID du projet dans le menu déroulant en haut à gauche.
Barricade de facturation	Vous avez activé le projet, mais vous avez oublié le compte de facturation. AlloyDB est un moteur hautes performances. Il ne démarrera pas si le "réservoir" (la facturation) est vide.
Décalage de la propagation de l'API	Vous avez cliqué sur "Activer les API", mais la ligne de commande indique toujours Service Not Enabled. Laissez-lui 60 secondes. Le cloud a besoin d'un instant pour réveiller ses neurones.
Quags de quota	Si vous utilisez un tout nouveau compte d'essai, vous pouvez atteindre un quota régional pour les instances AlloyDB. Si us-central1 échoue, essayez us-east1.

3. Récapitulatif rapide des données de la partie 1

Dans cette section, vous devez vous assurer que les données structurées que nous avons extraites des PDF non structurés sont disponibles dans BigQuery. Si vous avez manqué la partie 1 ou si vous n'avez pas de compte de facturation, pas de problème. Vous pouvez suivre les étapes ci-dessous pour commencer :

Accédez à la console Google Cloud depuis votre compte Gmail personnel, puis cliquez sur le bouton "Activer Cloud Shell" en haut à droite de la console :

Suivez ensuite les étapes de la section "Aucun compte de facturation" ci-dessous :

VOUS N'AVEZ PAS DE COMPTE DE FACTURATION ?

Si vous avez parcouru cet atelier de programmation pratique, mais de haut niveau sans compte de facturation, vous n'avez probablement pas pu configurer le job Datascan ni effectuer l'inférence Knowledge Catalog. Si vous avez manqué l'atelier de la partie 1, voici les données qui ont été déduites des 400 PDF de recettes et de fournisseurs à l'aide des étapes ci-dessus : https://github.com/AbiramiSukumaran/raw-data-to-gold/tree/main/data.

Voici comment continuer à accéder aux données avec le compte de facturation :

1. Récupérez les fichiers csv (données BigQuery) à partir du lien du dépôt GitHub ci-dessus.
2. Exécutez la commande ci-dessous à partir du terminal Cloud Shell :

____________________________________________

bq mk --location us-central1 --dataset froyo_data

____________________________________________

3. Exécutez les commandes suivantes une par une pour créer ces tables avec les données de l'ensemble de données que vous venez de créer.

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.allergen \

./froyo_data.allergen.csv allergen_name:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.consistsof \

./froyo_data.consistsof.csv product_id:STRING,ingredient_id:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.containsallergen \

./froyo_data.containsallergen.csv ingredient_id:STRING,allergen_name:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.froyo_data_materialized \

./froyo_data.froyo_data_materialized.csv allergen:STRING,containsallergen:STRING,ingredient:STRING,product:STRING,suppliedby:STRING,supplier:STRING,ref:STRING,md5_hash:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.ingredient \

./froyo_data.ingredient.csv ingredient_id:STRING,ingredient_name:STRING,purity:STRING,moisture_content:STRING,ph_range:STRING,specific_gravity_range:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.product \

./froyo_data.product.csv product_id:STRING,product_name:STRING,category:STRING,stability:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.suppliedby \

./froyo_data.suppliedby.csv ingredient_id:STRING,supplier_id:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data1.supplier \

./froyo_data.supplier.csv supplier_id:STRING,supplier_name:STRING,production_site_id:STRING,facility_grade:STRING

-----------------------------------------------------------------

Une fois l'ensemble de données, les tables et les données créés, vous pouvez tester et découvrir les données dont nous venons de parler.

VOUS N'AVEZ PAS DE COMPTE DE FACTURATION ?

Maintenant que les données sont dans BigQuery, passons aux étapes suivantes.

4. Configurer un cluster, une instance et un réseau AlloyDB

Une application de démarrage rapide basée sur le Web vous aidera à configurer le cluster et l'instance AlloyDB, ainsi que d'autres dépendances. Vous pouvez suivre les étapes 2 à 4 de cet atelier pour le configurer en un clic :

https://codelabs.developers.google.com/quick-alloydb-setup

Une fois votre cluster créé, accédez à la page "Présentation du cluster" et copiez les informations du compte de service.

5. Configurer les autorisations

Accorder des autorisations BigQuery à ce compte de service

1. Accédez à IAM et administration > IAM.
2. Cliquez sur "Accorder l'accès".
3. Collez l'adresse du compte de service AlloyDB dans le champ "Nouveaux comptes principaux".
4. Attribuez les rôles suivants :

• Lecteur de données BigQuery (roles/bigquery.dataViewer) : permet de lire les données.
• Utilisateur BigQuery (roles/bigquery.user) : permet d'exécuter les requêtes.
• (Facultatif, mais recommandé) Utilisateur de sessions de lecture BigQuery (roles/bigquery.readSessionUser) : permet d'optimiser la lecture des grands ensembles de données via l'API Storage Read.

6. Se connecter à AlloyDB et activer l'extension BigQuery

Nous allons maintenant nous connecter à notre nouvelle instance AlloyDB pour configurer l'extension de fédération. Pour ce faire, nous allons utiliser AlloyDB Studio.

1. Sur la page "Présentation du cluster" (console AlloyDB), cliquez sur "AlloyDB Studio".

2. Connectez-vous avec la base de données, le nom d'utilisateur et le mot de passe que vous avez configurés lors de l'étape de configuration rapide d'AlloyDB.
3. Une fois la connexion établie, saisissez les instructions suivantes dans l'onglet "Éditeur de requête" à droite, puis EXÉCUTEZ-les une par une :

CREATE EXTENSION IF NOT EXISTS  bigquery_fdw;

CREATE SERVER bigquery_server FOREIGN DATA WRAPPER bigquery_fdw;

CREATE USER MAPPING FOR postgres SERVER bigquery_server;

4. Une fois l'opération terminée, accédez au volet de l'explorateur sur la gauche et faites défiler la page jusqu'aux tables BigQuery :

5. Cliquez sur les trois points, puis sur "Connecter une table BigQuery".
6. Dans le pop-up "Connect BigQuery Table" (Connecter la table BigQuery) qui s'ouvre, sélectionnez votre project_id et le nom de l'ensemble de données BigQuery (créé dans la partie 1) à partir duquel vous souhaitez interroger les données de votre base de données AlloyDB.

7. Sélectionnez chaque table une par une pour connecter toutes vos données à AlloyDB. Cela nous permet de valider les types de colonnes pour nous assurer qu'ils sont compatibles avec AlloyDB.

Si vous souhaitez faire de même avec une requête SQL au lieu d'utiliser l'approche pointer-cliquer :

CREATE FOREIGN TABLE <<TABLE_NAME>> (
      "cas_number" VARCHAR, "ingredient_name" VARCHAR, "max_moisture_percentage" DOUBLE PRECISION, "ph_range" VARCHAR, "purity_percentage" DOUBLE PRECISION, "shelf_life_months" BIGINT, "specific_gravity_range" VARCHAR
    ) SERVER "bigquery_server" OPTIONS (
      project '<<PROJECT_ID>>',
      dataset 'froyo_data',
      table '<<BQ_TABLE_NAME>>'
    );

La magie !

Nous venons de créer des "tables externes" dans AlloyDB. Elles ressemblent à des tables PostgreSQL normales et se comportent comme telles, mais elles ne stockent aucune donnée. Lorsque vous les interrogez, AlloyDB transmet instantanément la requête à BigQuery, récupère les résultats et vous les renvoie.

7. Tester la fédération dans AlloyDB

Vérifions que nous pouvons interroger notre ensemble de données BigQuery analytiques volumineux directement à partir de notre base de données transactionnelles PostgreSQL.

Toujours dans AlloyDB Studio, exécutons une requête pour savoir quels sont les allergènes présents dans le "Midnight Swirl " (la même question que nous avons posée dans la partie 1, mais cette fois à partir d'AlloyDB) :

SELECT
    p.product_name,
    i.ingredient_name,
    a.allergen_name
FROM
    consistsof c
INNER JOIN product p
    ON c.product_id = p.product_id
INNER JOIN ingredient i
    ON c.ingredient_id = i.ingredient_name
LEFT OUTER JOIN containsallergen a
    ON i.ingredient_id = a.ingredient_id
WHERE
    UPPER(p.product_name) LIKE '%MIDNIGHT%SWIRL%'
    AND a.allergen_name IS NOT NULL;

Et voilà ! Vous devriez obtenir exactement les mêmes résultats que dans BigQuery.

8. Effectuer un nettoyage

Une fois cet atelier terminé, n'oubliez pas de supprimer le cluster et l'instance AlloyDB.

Accédez à https://console.cloud.google.com/alloydb/clusters. Sélectionnez le cluster que vous souhaitez supprimer en cliquant sur les trois points verticaux à côté, puis sur SUPPRIMER.

Il devrait nettoyer le cluster ainsi que ses instances.

9. Félicitations pour votre couche de données unifiée

Réfléchissez à ce que nous venons d'accomplir :

1. Notre application transactionnelle (exécutée sur AlloyDB) peut gérer des sessions utilisateur simultanées et rapides.
2. Lorsqu'il a besoin de données analytiques volumineuses ou d'un contexte historique (comme des informations sur les fournisseurs ou des mappages d'ingrédients complexes), il interroge le froyo_dataschema BigQuery.
3. Zero ETL Aucun pipeline de données n'est interrompu. Aucune base de données n'est désynchronisée. Nous stockons les données une seule fois (dans BigQuery) et les traitons là où nous en avons besoin.

Maintenant que notre base de données (analytiques et transactionnelles) est solide et interconnectée, nous pouvons passer à la partie amusante.

Dans la partie 3, nous allons créer l'application multi-agents qui se trouve au-dessus de cette architecture pour exécuter les opérations commerciales de Froyo.