Requêtes fédérées AlloyDB

En tant qu'analyste de données, vous pouvez interroger des données dans AlloyDB pour PostgreSQL à partir de BigQuery en utilisant les requêtes fédérées.

La fédération de BigQuery AlloyDB permet à BigQuery d'interroger les données résidant dans AlloyDB en temps réel sans avoir à copier ni déplacer les données.

Avant de commencer

Interroger les données

Pour envoyer une requête fédérée à AlloyDB à partir d'une requête GoogleSQL, utilisez la fonction EXTERNAL_QUERY.

Supposons que vous stockez une table de clients dans BigQuery ainsi qu'une table de ventes dans AlloyDB et que vous souhaitez joindre les deux tables dans une même requête. L'exemple suivant envoie une requête fédérée à une table AlloyDB nommée orders et joint les résultats à une table BigQuery nommée mydataset.customers.

L'exemple de requête comprend trois parties :

  1. Grâce à la fonction EXTERNAL_QUERY, exécution de la requête externe SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id dans la base de données AlloyDB afin d'obtenir la date de première commande pour chaque client.

  2. Jointure par customer_id de la table des résultats de la requête externe avec la table des clients dans BigQuery.

  3. Sélectionnez les informations client et la date de la première commande dans l'ensemble de résultats final.

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

Afficher un schéma de table AlloyDB

Vous pouvez utiliser la fonction EXTERNAL_QUERY pour interroger les tables information_schema afin d'accéder aux métadonnées de la base de données. Par exemple, vous pouvez lister toutes les tables de la base de données ou afficher le schéma de la table. Pour en savoir plus, consultez la section Tables "information_schema" dans PostgreSQL.

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("region.connection_id",
"select * from information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("region.connection_id",
"select * from information_schema.columns where table_name='x';");

Suivre les requêtes fédérées BigQuery

Lorsque vous exécutez une requête fédérée sur AlloyDB, BigQuery l'annote avec un commentaire semblable au suivant :

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

Si vous surveillez les journaux d'utilisation des requêtes, l'annotation suivante peut vous aider à identifier les requêtes provenant de BigQuery.

  1. Accédez à la page Explorateur de journaux.

    Accéder à l'explorateur de journaux

  2. Dans l'onglet Requête, saisissez la requête suivante :

    resource.type="alloydb.googleapis.com/Instance"
textPayload=~"Federated query from BigQuery"

  3. Cliquez sur Exécuter la requête.

    Si des enregistrements sont disponibles pour les requêtes fédérées BigQuery, une liste d'enregistrements semblable à la suivante s'affiche dans les résultats de la requête.

    YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

    Pour en savoir plus sur Cloud Logging, consultez la page Cloud Logging.

Dépannage

Cette section décrit les erreurs potentielles que vous pouvez rencontrer lors de l'envoi d'une requête fédérée à AlloyDB et fournit les solutions de dépannage possibles.

Problème : échec de connexion au serveur de base de données avec le message d'erreur suivant : Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

Solution : Assurez-vous d'avoir utilisé des identifiants valides et d'avoir suivi toutes les conditions préalables lors de la création de la connexion à AlloyDB. Vérifiez si le compte de service créé automatiquement lors de la création d'une connexion à AlloyDB possède le rôle Client AlloyDB (roles/alloydb.client). Pour en savoir plus, consultez la section Accorder l'accès au compte de service.

Étapes suivantes