Exécuter des requêtes fédérées MySQL

En tant qu'analyste de données, vous pouvez interroger des données relationnelles résidant dans MySQL directement depuis BigQuery en temps réel, sans copier ni déplacer de données.

La fédération BigQuery MySQL utilise l'API BigQuery Connection pour se connecter aux moteurs de base de données MySQL. Vous pouvez ensuite utiliser la fonction EXTERNAL_QUERY pour envoyer votre requête SQL directement à MySQL pour exécution, et les résultats sont directement renvoyés.

Limites

Les requêtes fédérées MySQL sont soumises aux limites suivantes :

  • Vous ne pouvez exécuter que des requêtes en lecture seule via la fonction EXTERNAL_QUERY. Les instructions LDD (langage de définition de données) et LMD (langage de manipulation de données) ne sont pas compatibles.
  • La fédération n'est accessible que via la fonction EXTERNAL_QUERY. La création de tables externes BigQuery ou d'ensembles de données permanents qui mappent directement vers des tables MySQL n'est pas possible.
  • La latence des requêtes fédérées peut être nettement plus élevée que celle de l'exécution de la requête directement dans MySQL.
  • Le service de connexion sous-jacent peut mettre en veille les connexions inactives. La première requête qui utilise une connexion MySQL après une période d'inactivité peut prendre plus d'une minute, le temps que le connecteur s'initialise.
  • L'appairage DNS n'est pas pris en charge. Vous devez vous connecter à l'aide d'une adresse IP ou d'un nom d'hôte entièrement résolvable dans votre réseau VPC.
  • La requête SQL MySQL doit spécifier des alias de colonne explicites pour toutes les colonnes contenant des résultats de fonction ou des expressions (par exemple, SELECT COUNT(*) AS total_count). Les expressions sans alias produisent des noms de colonne avec des caractères spéciaux qui ne sont pas valides dans les définitions de schéma de table BigQuery.
  • Lorsque l'utilisation de l'API Compute Engine dans le projet de requête est limitée par VPC Service Controls, la requête fédérée échoue si la connexion utilise un rattachement de réseau. Assurez-vous que les règles d'entrée et de sortie VPC Service Controls appropriées sont configurées.

  • Aucun pushdown SQL supplémentaire n'est compatible avec MySQL. Les filtres et les agrégations de la requête BigQuery externe ne sont pas transmis à la requête interne dans la fonction EXTERNAL_QUERY. Vous devez spécifier des prédicats et des projections de colonnes directement dans l'instruction SQL MySQL interne pour limiter les lignes et les colonnes transférées sur le réseau.

  • Seule l'authentification par nom d'utilisateur et mot de passe de la base de données MySQL est acceptée. Les fournisseurs d'identité tiers, Kerberos et l'authentification IAM du fournisseur de services cloud ne sont pas acceptés.

  • Les instructions CREATE TABLE ... AS SELECT (CTAS) échouent avec des erreurs Unsupported type INT32 ou Unsupported type FLOAT si la requête sélectionne des colonnes d'entiers 32 bits (TINYINT, SMALLINT et BOOLEAN / TINYINT(1)) ou des colonnes de nombres à virgule flottante simple précision (FLOAT). Pour enregistrer les résultats de la requête dans une nouvelle table BigQuery, castez explicitement les colonnes d'entiers en INT64 et les colonnes de nombres à virgule flottante en FLOAT64 dans votre requête :

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(tiny_col AS INT64) AS tiny_col,
CAST(small_col AS INT64) AS small_col,
...
FROM EXTERNAL_QUERY(...);

    L'insertion dans une table existante avec INSERT INTO <table> SELECT ... réussit sans casts manuels, car BigQuery élargit automatiquement les valeurs INT32 et FLOAT pour qu'elles correspondent aux colonnes INT64 et FLOAT64 de la table cible.

  • Bien que les colonnes MySQL DECIMAL et NUMERIC soient mappées sur BIGNUMERIC(76, 38), MySQL autorise une précision allant jusqu'à 65. Si une valeur contient plus de 38 chiffres entiers (la précision moins l'échelle est supérieure à 38), elle dépasse la plage d'entiers BIGNUMERIC et la requête échoue au moment de l'exécution. Pour interroger des valeurs comportant plus de 38 chiffres entiers, castez-les en CHAR ou VARCHAR dans l'instruction EXTERNAL_QUERY.

  • Le type de données TIME tronque les valeurs fractionnaires inférieures à la seconde (microseconde) en secondes entières (par exemple, 12:34:56.789123 devient 12:34:56). De plus, le type TIME de MySQL accepte les durées écoulées de -838:59:59 à 838:59:59, tandis que le type TIME de BigQuery n'accepte que les valeurs d'heure de la journée de 00:00:00 à 23:59:59.999999. Les valeurs en dehors de 00:00:00 à 23:59:59 entraînent des erreurs de requête d'exécution. Si une précision en microsecondes ou des valeurs de durée écoulée sont requises, castez la colonne en CHAR ou VARCHAR dans l'instruction EXTERNAL_QUERY.

  • Les dates et les codes temporels antérieurs au 15 octobre 1582 sont soumis à un décalage de 10 jours dans le calendrier en raison de la transition historique vers le calendrier grégorien. Pour conserver les dates historiques exactes antérieures à 1582, castez-les en CHAR ou VARCHAR dans l'instruction EXTERNAL_QUERY.

Environnements et versions MySQL compatibles

La fédération de requêtes est compatible avec les bases de données MySQL dans les environnements de déploiement suivants :

  • Bases de données MySQL hébergées dans le cloud ou sur site, accessibles via des adresses IP publiques ou des rattachements de réseau (via Cloud VPN ou Cloud Interconnect)
  • Instances MySQL auto-hébergées sur Compute Engine ou une autre infrastructure
  • MySQL 8.4 LTS et 8.0

Avant de commencer

Assurez-vous que votre administrateur BigQuery a créé une connexion MySQL et l'a partagée avec vous.

Rôles requis

Pour obtenir les autorisations nécessaires pour interroger MySQL, demandez à votre administrateur de vous accorder le rôle IAM Utilisateur de connexion BigQuery (roles/bigquery.connectionUser) sur le 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 à MySQL à partir d'une requête GoogleSQL, utilisez la fonction EXTERNAL_QUERY.

L'exemple suivant est une requête fédérée qui joint les enregistrements client dans BigQuery nommés mydataset.customers aux commandes client transactionnelles dans MySQL :

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/mysql_prod_conn',
  '''SELECT CUSTOMER_ID, MIN(ORDER_DATE) AS first_order_date
     FROM ORDERS
     GROUP BY CUSTOMER_ID''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

Afficher un schéma de table MySQL

Vous pouvez interroger les vues MySQL INFORMATION_SCHEMA via EXTERNAL_QUERY pour inspecter les métadonnées de la base de données, découvrir des tables et examiner les schémas de colonnes avant d'exécuter des analyses.

Lister toutes les bases de données

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
     FROM INFORMATION_SCHEMA.SCHEMATA''');

Lister les tables d'une base de données

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DATABASE_NAME'""");

Afficher les colonnes et les types de données d'une table

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_TYPE
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME'""");

Mappage des types de données

Lorsque vous exécutez une requête fédérée, BigQuery convertit les données de MySQL en types de données GoogleSQL, conformément au tableau suivant :

Type de donnée MySQL Type de données BigQuery
Types numériques
BOOLEAN, BOOL, TINYINT(1) INT64 (requêtes interactives ; consultez la section Limites pour CREATE TABLE ... AS SELECT)
TINYINT, SMALLINT INT64 (requêtes interactives ; consultez la section Limites pour CREATE TABLE ... AS SELECT)
MEDIUMINT, INT, INTEGER INT64
BIGINT INT64
BIGINT UNSIGNED, BIGINT ZEROFILL, SERIAL BIGNUMERIC(76, 38)
DECIMAL, DEC, NUMERIC, FIXED BIGNUMERIC(76, 38)
FLOAT FLOAT64 (requêtes interactives ; consultez la section Limites pour CREATE TABLE ... AS SELECT)
DOUBLE, DOUBLE PRECISION, REAL FLOAT64
Types de date et d'heure
DATE DATE
DATETIME DATETIME
TIMESTAMP TIMESTAMP
TIME TIME
Types de chaînes et de texte
CHAR STRING
VARCHAR STRING
BINARY BYTES
VARBINARY BYTES
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTES
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT STRING
ENUM STRING
SET STRING

Tarifs

Le coût d'exécution d'une requête fédérée dépend de trois facteurs :

  • Le coût de calcul lié à l'exécution de la requête dans MySQL (facturé par votre fournisseur de base de données MySQL ou votre environnement d'hébergement)
  • Le coût de la bande passante pour le transfert des résultats de la requête de MySQL vers BigQuery (frais de réseau de sortie de votre fournisseur d'hébergement de base de données)
  • Le coût de calcul lié à l'exécution de la requête dans BigQuery.

Tous les coûts de calcul et de sortie MySQL sont facturés directement par votre fournisseur MySQL ou votre hébergeur d'infrastructure, et non par Google. Pour limiter les coûts en termes de bande passante, nous vous recommandons d'écrire la requête dans l'instruction EXTERNAL_QUERY afin d'exclure toutes les colonnes et les lignes qui ne sont pas nécessaires au calcul du résultat final.

L'exécution de requêtes fédérées dans BigQuery n'entraîne aucuns frais supplémentaires. Pour en savoir plus sur les tarifs de BigQuery, consultez la section Tarifs.

Si votre connexion utilise des composants réseau Google Cloud , les tarifs suivants peuvent également s'appliquer :

Étapes suivantes