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

Les requêtes fédérées dans BigQuery vous permettent d'interroger les données résidant dans Cloud SQL en temps réel, sans avoir à les copier ni à les déplacer. La fédération de requêtes est compatible aussi bien avec les instances Cloud SQL pour MySQL que Cloud SQL pour 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

Pour exécuter des requêtes fédérées sur vos données dans Cloud SQL, procédez comme suit :

  1. Assurez-vous que votre administrateur BigQuery a créé une connexion Cloud SQL et l'a partagée avec vous.
  2. Vérifiez que l'agent de service de connexion BigQuery dispose du rôle "Client Cloud SQL" (roles/cloudsql.client). Pour en savoir plus, consultez Accorder l'accès à l'agent de service.
  3. Si votre instance Cloud SQL utilise une adresse IP privée, assurez-vous qu'un chemin d'accès privé a été activé lors de la création de l'instance. Pour en savoir plus, consultez Se connecter à Cloud SQL et Configurer une adresse IP privée.

Rôles requis

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 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.

Format de l'identifiant de connexion

Le premier argument de la fonction EXTERNAL_QUERY est l'identifiant de connexion. Vous pouvez spécifier l'identifiant de connexion dans l'un des formats suivants :

  • Format court : [LOCATION.]CONNECTION_ID (par exemple, 'us.my-connection' ou 'my-connection')
  • Format complet : projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
  • Format de chemin d'accès convivial : PROJECT_ID.LOCATION.CONNECTION_ID (par exemple, 'my-project.us.my-connection')

Si vous exécutez une requête dans une vue partagée entre plusieurs projets, utilisez toujours l'identifiant de connexion complet pour éviter de vous connecter au mauvais projet.

Exécuter une requête fédérée

Supposons que vous stockez une table de clients dans BigQuery ainsi qu'une table de commandes 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.

Sélectionnez l'une des options suivantes pour exécuter la requête fédérée :

Console

  1. Dans la console Google Cloud , accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez votre requête :

    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;

    Remplacez CONNECTION_ID par l'ID de votre connexion BigQuery.

  3. Cliquez sur Exécuter.

    Les résultats de la requête s'affichent dans le volet Résultats de la requête.

bq

  2. Dans la console Google Cloud , activez Cloud Shell.

    Activer Cloud Shell

    En bas de la console Google Cloud , une session Cloud Shell démarre et affiche une invite de ligne de commande. Cloud Shell est un environnement de shell dans lequel la Google Cloud CLI est déjà installée, et dans lequel des valeurs sont déjà définies pour votre projet actuel. L'initialisation de la session peut prendre quelques secondes.

  3. Exécutez la commande bq query avec l'option --use_legacy_sql=false :

    bq query \
    --use_legacy_sql=false \
    '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;'

    Remplacez CONNECTION_ID par l'ID de votre connexion BigQuery.

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 Cloud SQL 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 vues INFORMATION_SCHEMA afin d'accéder aux métadonnées de la base de données, par exemple pour lister toutes les tables de la base de données ou afficher les schémas de 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';");

Remplacez CONNECTION_ID par l'ID de votre connexion BigQuery.

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 vous aide à identifier les requêtes provenant de BigQuery.

  1. Dans la console Google Cloud , 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étails de connexion

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

Nom de propriété Valeur Description
name string Nom de la ressource de connexion au format projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID.
location string Emplacement de la connexion, qui doit correspondre à l'emplacement de l'instance Cloud SQL ou être un emplacement multirégional de la juridiction correspondante. Par exemple, une instance Cloud SQL dans us-east4 peut utiliser US, tandis qu'une instance Cloud SQL dans europe-north1 peut utiliser EU. Seules les requêtes BigQuery exécutées dans cet emplacement peuvent utiliser cette connexion.
friendlyName string Nom à afficher convivial pour la connexion.
description chaîne Description de la connexion.
cloudSql.type string Il peut s'agir de POSTGRES ou MYSQL.
cloudSql.instanceId string Nom de l'instance Cloud SQL, généralement au format PROJECT_ID:LOCATION:INSTANCE_ID. Vous pouvez trouver l'ID d'instance sur la page Instances Cloud SQL.
cloudSql.database string Base de données Cloud SQL à laquelle se connecter.
cloudSql.serviceAccountId string 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 string Nom d'utilisateur de la base de données.
password string Mot de passe de la base de données.

Performances des requêtes et bonnes pratiques

Pour optimiser les performances des requêtes fédérées et éviter de surcharger inutilement votre instance Cloud SQL, tenez compte des bonnes pratiques suivantes :

  • Filtrer tôt dans la requête externe : appliquez des filtres WHERE et des agrégations GROUP BY dans l'instruction SQL EXTERNAL_QUERY au lieu de filtrer dans la requête BigQuery externe. L'exécution de filtres dans Cloud SQL réduit la quantité de données transférées sur le réseau vers BigQuery.
  • Élaguer les colonnes : sélectionnez uniquement les colonnes spécifiques nécessaires dans l'instruction EXTERNAL_QUERY plutôt que d'utiliser SELECT *. Cela réduit le volume de transfert réseau et le temps d'exécution des requêtes.
  • Utiliser des instances répliquées avec accès en lecture : pour isoler les charges de travail de requêtes analytiques de votre base de données de production principale, créez une instance répliquée Cloud SQL avec accès en lecture et configurez votre connexion BigQuery pour interroger l'instance répliquée.
  • Colocalisez les ensembles de données et les instances : pour une latence minimale, placez votre ensemble de données BigQuery et votre connexion dans la même région que votre instance Cloud SQL, ou dans l'emplacement multirégional correspondant (par exemple, US ou EU). Pour en savoir plus, consultez Régions compatibles.

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.

Échec de la connexion au serveur de base de données

Lorsque 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.

Lorsque vous interrogez une base de données PostgreSQL, vous pouvez 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.

Solution

Pour résoudre ce problème, vérifiez les conditions préalables suivantes :

  1. Assurez-vous que des identifiants valides ont été utilisés et que toutes les étapes de configuration ont été suivies lors de la création de la connexion pour Cloud SQL.

  2. Vérifiez que l'agent de service de connexion BigQuery dispose du rôle "Client Cloud SQL" (roles/cloudsql.client). L'adresse e-mail de l'agent de service est au format suivant :

    service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.

    Pour obtenir des instructions détaillées, consultez Accorder l'accès à l'agent de service.

  3. Si votre instance Cloud SQL utilise une adresse IP privée, assurez-vous d'avoir activé un chemin d'accès privé lorsque vous avez créé l'instance Cloud SQL. L'activation d'un chemin d'accès privé permet à BigQuery d'accéder aux données dans Cloud SQL et d'exécuter des requêtes via une connexion privée.

Échec de la connexion à la base de données MySQL en raison du plug-in d'authentification

Lorsque vous exécutez une requête fédérée sur une instance MySQL, vous pouvez rencontrer l'erreur suivante :

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

Solution

Cette erreur se produit pour les comptes utilisateur de base de données configurés avec l'authentification caching_sha2_password, qui n'est pas compatible avec les requêtes fédérées.

Si votre instance Cloud SQL exécute MySQL 8.0 ou une version antérieure, vous pouvez modifier le plug-in d'authentification pour le compte utilisateur utilisé par BigQuery. Exécutez la commande suivante sur l'instance Cloud SQL :

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Remplacez les éléments suivants :

  • USERNAME : compte utilisateur de base de données que BigQuery utilise pour se connecter à votre instance Cloud SQL pour MySQL.
  • PASSWORD : mot de passe du compte utilisateur de la base de données.

Si votre instance Cloud SQL exécute MySQL version 8.4, le plug-in mysql_native_password est obsolète. Nous vous déconseillons de passer d'une base de données existante de la version 8.4 à la version 8.0 pour les charges de travail de production.

La mise à niveau d'une instance d'une version antérieure vers MySQL 8.4 n'affecte pas les connexions de base de données existantes, à condition de ne pas modifier le plug-in d'authentification des utilisateurs avant la mise à niveau. Étant donné que seuls les utilisateurs créés avec mysql_native_password peuvent être utilisés pour les requêtes fédérées, les requêtes envoyées à MySQL 8.4 doivent utiliser un compte utilisateur créé avant la mise à niveau.

Limites

Les requêtes fédérées dans Cloud SQL sont soumises aux limites suivantes :

  • Requêtes en lecture seule : les requêtes fédérées sont en lecture seule. Les requêtes externes doivent être des instructions SELECT. Les instructions en langage de définition de données (LDD) et en langage de manipulation de données (LMD) ne sont pas acceptées.
  • Performances des requêtes : les requêtes fédérées ne sont pas aussi rapides que les requêtes sur le stockage BigQuery. BigQuery doit attendre que Cloud SQL exécute la requête externe et transfère l'ensemble de résultats sur le réseau.
  • Contraintes de région : l'emplacement de traitement des requêtes BigQuery doit correspondre à l'emplacement de l'instance Cloud SQL ou être compatible avec celui-ci. Pour en savoir plus, consultez la section Régions prises en charge.
  • Types de données non compatibles : si une requête externe renvoie un type de données non compatible avec BigQuery, la requête échoue. Vous pouvez convertir le type de données non géré en un autre type de données compatible dans la requête externe. Pour en savoir plus, consultez Mappages des types de données.
  • Nombre maximal de connexions : une requête fédérée peut référencer au plus 10 connexions uniques.
  • Limitation du plug-in d'authentification : le plug-in MySQL caching_sha2_password n'est pas compatible avec les requêtes fédérées. Pour en savoir plus, consultez Dépannage.

Étapes suivantes