Présentation

Requêtes fédérées BigQuery : dans cet atelier, vous allez découvrir comment les requêtes fédérées permettent d'obtenir des informations en temps réel en interrogeant directement une base de données opérationnelle dans BigQuery. Vous allez apprendre à connecter BigQuery à une base de données AlloyDB pour PostgreSQL, ce qui vous permettra d'associer des données analytiques historiques à des données transactionnelles en temps réel sans avoir besoin de pipelines ETL complexes et chronophages. Cet exercice pratique illustre l'une des fonctionnalités clés d'un data lakehouse moderne : la capacité à unifier les données provenant de systèmes disparates pour une analyse complète.

Vous commencerez par configurer une connexion externe dans BigQuery qui établit un lien sécurisé avec une instance AlloyDB contenant des données de journaux Web. Ensuite, vous autoriserez la connexion que vous venez de créer à interroger AlloyDB. Enfin, vous créerez une requête SQL en utilisant la fonction EXTERNAL_QUERY. Cette fonction puissante vous permet d'exécuter une requête sur la base de données AlloyDB et de traiter ses résultats comme une table temporaire dans votre environnement BigQuery.

La tâche principale consiste à écrire une requête fédérée unique qui joint un ensemble de données client stocké de façon native dans BigQuery à des données de journaux Web en temps réel dans AlloyDB. En maîtrisant cette technique, vous pouvez l'appliquer à des scénarios sophistiqués en temps réel, qui combinent un contexte historique complexe et des informations opérationnelles immédiates. Par exemple, Cymbal E-commerce pourrait utiliser cette approche pour répondre à la question suivante : "Que consultent sur notre site Web nos clients ayant dépensé le plus historiquement ?"

Objectifs de l'atelier

• Créer une connexion à AlloyDB dans BigQuery
• Accorder au compte de service de la connexion l'autorisation d'accéder à AlloyDB à l'aide d'un rôle IAM
• Écrire une requête avec la fonction SQL EXTERNAL_QUERY
• Exécuter la requête et examiner les résultats

Préparation

Avant de cliquer sur le bouton "Démarrer l'atelier"

Lisez ces instructions. Les ateliers sont minutés, et vous ne pouvez pas les mettre en pause. Le minuteur, qui démarre lorsque vous cliquez sur Démarrer l'atelier, indique combien de temps les ressources Google Cloud resteront accessibles.

Cet atelier pratique vous permet de suivre vous-même les activités dans un véritable environnement cloud, et non dans un environnement de simulation ou de démonstration. Nous vous fournissons des identifiants temporaires pour vous connecter à Google Cloud le temps de l'atelier.

Conditions requises

Pour réaliser cet atelier, vous devez :

• avoir accès à un navigateur Internet standard (nous vous recommandons d'utiliser Chrome) ;
• disposer de suffisamment de temps pour effectuer l'atelier en une fois.

Se connecter à la console Google Cloud

1. Dans l'onglet ou la fenêtre de navigateur que vous utilisez pour cet atelier, copiez les données de Username (Nom d'utilisateur) indiqué dans le panneau Connection Details (Détails de connexion), puis cliquez sur le bouton Open Google Console (Ouvrir la console Google).

2. Collez les données de Username (Nom d'utilisateur) et de Password (Mot de passe) lorsque vous y êtes invité :
3. Cliquez sur Next (Suivant).
4. Acceptez les conditions d'utilisation.

Comme il s'agit d'un compte temporaire auquel vous aurez accès uniquement pendant la durée de cet atelier :

• n'ajoutez pas d'options de récupération ;
• ne vous inscrivez pas à des essais sans frais.

5. Une fois la console ouverte, affichez la liste des services en cliquant sur le menu de navigation () en haut à gauche.

Vérifier ou activer les API requises

1. Dans la console Google Cloud, saisissez API BigQuery Connection dans la barre de recherche en haut de l'écran.

2. Cliquez sur le résultat API BigQuery Connection.

3. Si l'API n'est pas déjà activée, cliquez sur Activer pour l'activer.

Tâche 1 : Créer une connexion à AlloyDB

Dans cette tâche, vous allez créer une connexion à AlloyDB que BigQuery pourra utiliser.

Créer la connexion

1. Dans la console Google Cloud, accédez au menu de navigation (), puis à BigQuery > Studio.

2. Sous Explorateur, cliquez sur + Ajouter des données.

3. Dans le menu déroulant à gauche, sous Type de source de données, sélectionnez Bases de données.

4. À droite du menu déroulant, sous Sélection de sources de données, cliquez sur la fiche Google Cloud AlloyDB.

5. Dans la sélection de fiches qui s'affiche, cliquez sur Fédération BigQuery.

6. Dans l'écran de saisie Source de données externe, utilisez les valeurs suivantes :

7. Cliquez sur Créer une connexion.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer une connexion à AlloyDB

Tâche 2 : Définir les autorisations IAM pour le compte de service de la connexion

Pour interroger les données dans AlloyDB, vous devez accorder l'autorisation d'accéder à AlloyDB au compte de service qui a été créé automatiquement lorsque vous avez établi la connexion.

Définir les autorisations IAM

1. Dans le panneau "Explorateur classique" de BigQuery, développez l'entrée correspondant à l'ID du projet de l'atelier.

2. À présent, développez l'entrée Connexions.

3. Cliquez sur l'entrée que vous venez de créer. Les détails de la connexion s'affichent dans le panneau principal à droite. Copiez l'entrée indiquée pour l'ID du compte de service. Voici un exemple de ce à quoi il ressemble : sservice-164632061610@gcp-sa-bigqueryconnection.iam.gserviceaccount.com

4. Dans le menu de navigation, sélectionnez IAM et administration, puis IAM dans le sous-menu qui s'affiche.

5. Cliquez sur + Accorder l'accès.

6. Dans le champ Nouveaux principaux, collez l'ID du compte de service de connexion que vous avez copié à l'étape 3.

7. Cliquez sur le menu déroulant Sélectionner un rôle. Dans la zone Filtrer qui s'affiche, saisissez AlloyDB. Faites défiler l'écran jusqu'à Client AlloyDB et sélectionnez-le.

8. Cliquez sur Ajouter un autre rôle.

9. Cliquez sur le nouveau menu déroulant Sélectionner un rôle. Dans la zone Filtrer qui s'affiche, saisissez BigQuery. Faites défiler l'écran jusqu'à Utilisateur de connexion BigQuery et sélectionnez-le.

10. Cochez la case Enregistrer au bas de l'écran.

Cliquez sur Vérifier ma progression pour valider l'objectif. Définir les autorisations IAM pour le compte de service de la connexion

Tâche 3 : Exécuter une requête fédérée depuis BigQuery

Dans cette tâche, vous allez exécuter une requête qui combine les informations client de Cymbal dans BigQuery avec les informations de ses journaux Web dans AlloyDB.

1. Dans le menu de navigation () de la console Google Cloud, accédez à BigQuery > Studio.

2. Dans le panneau "Explorateur classique" de BigQuery, développez l'entrée correspondant à l'ID du projet de l'atelier.

3. À présent, développez l'entrée Connexions.

4. Cliquez sur les trois points verticaux à droite de la connexion que vous avez créée. Sélectionner Requête

5. Mettez à jour la requête en remplaçant l'instruction SELECT dans la partie EXTERNAL_QUERY de la requête par le code suivant. Ne remplacez pas l'intégralité de la requête, mais uniquement la partie comprise dans le bloc EXTERNAL_QUERY entre les guillemets " " :

   SELECT customer_id, CAST(log_id AS VARCHAR(200)) AS log_id, timestamp, url FROM web_log LIMIT 100

6. Cliquez sur Exécuter. Les données renvoyées proviennent d'AlloyDB, même si vous travaillez dans BigQuery.

7. Nous allons maintenant étendre la requête afin de combiner les données des tables natives BigQuery avec celles d'AlloyDB.

8. Remplacez la requête actuelle par le code suivant :

   WITH log AS ( SELECT customer_id, log_id, timestamp, url FROM EXTERNAL_QUERY("{{{project_0.project_id | Project ID}}}.{{{project_0.default_region | Region}}}.AlloyDB-weblog", "SELECT customer_id, CAST(log_id AS VARCHAR(200)) AS log_id, timestamp, url FROM web_log LIMIT 100")) SELECT log.customer_id , log.timestamp , log.url , C.* FROM customers.customer_details AS C INNER JOIN log ON C.id = log.customer_id ORDER BY C.id LIMIT 100;

9. Cliquez sur Exécuter. Les données renvoyées combinent les données d'AlloyDB et celles de BigQuery.

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter une requête fédérée depuis BigQuery

Félicitations !

Vous avez configuré et utilisé une connexion externe BigQuery pour permettre à BigQuery d'accéder aux données stockées dans AlloyDB et de les traiter.