Requêtes fédérées Cloud SQL

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

La fédération de BigQuery et Cloud SQL permet à BigQuery d'interroger les données résidant dans Cloud SQL en temps réel, sans avoir à copier ni déplacer de données. La fédération de requêtes est compatible aussi bien avec les instances MySQL (deuxième génération) que PostgreSQL dans Cloud SQL.

Pour répliquer des données dans BigQuery, vous pouvez également utiliser Cloud Data Fusion ou Datastream. Pour en savoir plus sur l'utilisation de Cloud Data Fusion, consultez la page Répliquer des données de MySQL vers BigQuery.

Avant de commencer

Interroger les données

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

Supposons que vous stockez une table de clients dans BigQuery ainsi qu'une table de ventes dans Cloud SQL 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 Cloud SQL nommée orders et joint les résultats à une table BigQuery nommée mydataset.customers.

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;

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 PostgreSQL opérationnelle 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élection des informations client et de la date de la première commande.

Afficher un schéma de table Cloud SQL

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 pour répertorier l'ensemble des tables de la base de données ou afficher le schéma d'une table. Les exemples de requête "information_schema" suivants fonctionnent à la fois avec MySQL et PostgreSQL. Pour en savoir plus, consultez les pages concernant les tables "information_schema" dans MySQL et les tables "information_schema" dans PostgreSQL.

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

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

Détails de connexion

Le tableau suivant indique les propriétés de connexion Cloud SQL :

Nom de propriété Valeur Description
name chaîne Nom de la ressource de connexion au format : project_id.location_id.connection_id.
location chaîne Emplacement de la connexion, identique à l'emplacement de l'instance Cloud SQL, à quelques exceptions près : Cloud SQL us-central1 est mappé sur BigQuery US, tandis que Cloud SQL europe-west1 est mappé sur BigQuery EU.
friendlyName chaîne Nom d'affichage facile à lire pour la connexion.
description chaîne Description de la connexion.
cloudSql.type chaîne Valeurs possibles : "POSTGRES" ou "MYSQL".
cloudSql.instanceId chaîne Nom de l'instance Cloud SQL, généralement au format :

Project-id:location-id:instance-id

Vous trouverez l'ID de l'instance sur la page de détails de l'instance Cloud SQL.
cloudSql.database chaîne Base de données Cloud SQL à laquelle vous souhaitez vous connecter.
cloudSql.serviceAccountId chaîne Compte de service configuré pour accéder à la base de données Cloud SQL.

Le tableau suivant indique les propriétés des identifiants de l'instance Cloud SQL :

Nom de propriété Valeur Description
username chaîne Nom d'utilisateur de la base de données
password chaîne Mot de passe de la base de données

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

Lorsque vous exécutez une requête fédérée sur Cloud SQL, 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 sur une base de données MySQL ou PostgreSQL, 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="cloudsql_database"
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
*/

Dépannage

Cette section vous aide à résoudre les problèmes que vous pouvez rencontrer lors de l'envoi d'une requête fédérée à Cloud SQL.

Problème : échec de connexion au serveur de base de données. Si vous interrogez une base de données MySQL, vous pouvez rencontrer l'erreur suivante :

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

Si vous interrogez une base de données PostgreSQL, vous pouvez également rencontrer l'erreur suivante :

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.
Résolution : Assurez-vous que des identifiants valides ont été utilisés et que toutes les conditions préalables ont été suivies pour créer la connexion pour Cloud SQL. Vérifiez si le compte de service créé automatiquement lors de la création d'une connexion à Cloud SQL possède le rôle "Client Cloud SQL" (roles/cloudsql.client). Le compte de service est au format suivant : service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. Pour obtenir des instructions détaillées, consultez la page Accorder l'accès au compte de service.

Étapes suivantes