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
- Assurez-vous que votre administrateur BigQuery a créé une connexion Cloud SQL et l'a partagée avec vous.
-
Pour obtenir les autorisations nécessaires pour interroger une instance Cloud SQL, demandez à votre administrateur de vous accorder le rôle IAM Utilisateur de connexion BigQuery (
roles/bigquery.connectionUser
) sur votre projet. Pour en savoir plus sur l'attribution de rôles, consultez la page Gérer l'accès aux projets, aux dossiers et aux organisations.Vous pouvez également obtenir les autorisations requises via des rôles personnalisés ou d'autres rôles prédéfinis.
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 :
- Grâce à la fonction
EXTERNAL_QUERY()
, exécution de la requête externeSELECT 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. - Jointure par
customer_id
de la table des résultats de la requête externe avec la table des clients dans BigQuery. - 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.
Accédez à la page Explorateur de journaux.
Dans l'onglet Requête, saisissez la requête suivante :
resource.type="cloudsql_database" textPayload=~"Federated query from BigQuery"
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
- En savoir plus sur les requêtes fédérées.
- Obtenez plus d'informations sur le mappage des types de données MySQL vers BigQuery.
- Obtenez plus d'informations sur le mappage des types de données PostgreSQL vers BigQuery.
- Obtenez plus d'informations sur les types de données non compatibles.