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

Cette page explique comment interroger des données dans BigQuery et Cloud SQL à l'aide d'une requête fédérée.

Présentation

Il est fréquent que les données soient dispersées dans plusieurs emplacements. Vous pouvez stocker une table de clients dans BigQuery tout en hébergeant une table des ventes dans Cloud SQL, et vous pouvez être amené à effectuer une jointure entre ces deux tables au sein d'une même requête.

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 est compatible avec les instances MySQL (deuxième génération) aussi bien que PostgreSQL dans Cloud SQL.

Une fois la configuration initiale unique effectuée, vous pouvez rédiger une requête à l'aide de la nouvelle fonction SQL EXTERNAL_QUERY().

Workflow

Syntaxe des requêtes fédérées

Les requêtes fédérées introduisent une nouvelle fonction : EXTERNAL_QUERY.

Syntaxe

    SELECT * FROM EXTERNAL_QUERY(connection_id, external_database_query[, options]);
  • connection_id (chaîne) : le nom de la ressource de connexion à la base de données que vous créez dans Cloud Console, l'outil de ligne de commande bq ou l'API BigQuery.

    Exemple d'ID de connexion : bigquery-federation-test.us.test-mysql

  • external_database_query (chaîne) : requête en lecture seule dans le dialecte SQL (MySQL ou PostgreSQL) de la base de données externe. La requête est exécutée dans la base de données externe dans Cloud SQL.

  • options (chaîne) : chaîne facultative d'un mappage au format JSON avec des paires clé/valeur de nom et de valeur d'option (tous deux sensibles à la casse).

    Exemples d'options {"default_type_for_decimal_columns":"numeric"}

    Options compatibles :

    Nom de l'option Description
    "default_type_for_decimal_columns" Valeurs possibles : "float64", "numeric", "bignumeric" ou "string". Avec cette option, le type décimal MySQL ou le type numérique PostgreSQL est mappé avec le type BigQuery fourni. Lorsque cette option n'est pas spécifiée, le type décimal MySQL ou le type numérique PostgreSQL est mappé avec le type BigQuery NUMERIC.

Description

EXTERNAL_QUERY exécute la requête dans Cloud SQL et renvoie les résultats sous forme de table temporaire. Le type de données de la base de données source (MySQL ou PostgreSQL) est converti en type de données BigQuery dans la table de résultats temporaire, en suivant le mappage des types de données décrit ci-dessous. La fonction EXTERNAL_QUERY est généralement utilisée dans une clause FROM. Cette fonction n'est disponible que dans le langage SQL standard de BigQuery.

Type renvoyé

Une table BigQuery.

Exemple de requête

Supposons que vous ayez besoin de la date de la première commande pour chacun de vos clients, afin de l'inclure dans le rapport décrit dans la section de présentation. Ces données ne figurent pas actuellement dans BigQuery, mais elles sont disponibles dans votre base de données PostgreSQL opérationnelle hébergée dans Cloud SQL. L'exemple de requête fédérée ci-dessous permet de réaliser cette opération.

SELECT c.customer_id, c.name, SUM(t.amount) AS total_revenue,
rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN EXTERNAL_QUERY(
  '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.

L'ordre n'est pas préservé

EXTERNAL_QUERY() ne respecte pas l'ordre des résultats de la requête externe, même si votre requête externe inclut une clause ORDER BY. L'exemple de requête suivant trie les lignes par identifiant client dans Cloud SQL, mais BigQuery ne les affichera pas dans cet ordre.

SELECT * FROM EXTERNAL_QUERY(
'connection_id',
'''SELECT * FROM customers AS c ORDER BY c.customer_id'');

Avant de commencer

Activer le service de connexion BigQuery

  1. Ouvrez la page API de connexion BigQuery dans la bibliothèque d'API.
  2. Dans le menu déroulant, sélectionnez le projet contenant votre instance Cloud SQL.
  3. Cliquez sur le bouton ACTIVER.

    API de connexion BigQuery

Compte de service

BigQuery utilise un compte de service pour se connecter à votre instance Cloud SQL. Lorsque vous activez l'API BigQuery Connection, un compte de service Cloud IAM (Cloud Identity and Access Management) géré par Google Cloud est automatiquement créé en votre nom. Le compte de service possède les rôles suivants :

Rôle Description
cloudsql.client Connexion à une instance Cloud SQL
logging.logWriter Écriture dans cloud-logging
metrics.metricWriter Écriture dans cloud-monitoring

Autorisations

  • Pour créer et gérer une ressource de connexion, l'utilisateur doit disposer du rôle IAM prédéfini bigquery.admin.

  • Le rôle bigquery.admin inclut les autorisations suivantes sur le service de connexion BigQuery :

    • bigquery.connections.create
    • bigquery.connections.get
    • bigquery.connections.list
    • bigquery.connections.update
    • bigquery.connections.use
    • bigquery.connections.delete

Pour accorder des autorisations à un autre utilisateur et lui permettre d'utiliser la ressource de connexion pour effectuer des requêtes Cloud SQL, consultez la section Partager une ressource de connexion.

Accorder un accès bigquery.admin

Pour accorder le rôle bigquery.admin, procédez comme suit :

Console

  1. Ouvrez la page "IAM" dans Cloud Console.

    Ouvrir la page IAM

  2. Cliquez sur Sélectionner un projet.

  3. Sélectionnez un projet et cliquez sur Ouvrir.

  4. Cliquez sur Ajouter pour ajouter de nouveaux membres au projet et définir leurs autorisations.

  5. Dans la boîte de dialogue Ajouter des membres :

    • Dans le champ Membres, entrez l'adresse e-mail de l'utilisateur ou du groupe.
    • Dans la liste déroulante Sélectionner un rôle, cliquez sur BigQuery > Administrateur BigQuery.
    • Cliquez sur Ajouter.

      Accorder des droits d'administrateur

gcloud

Vous pouvez accorder le rôle bigquery.admin à un utilisateur ou à un groupe à l'aide de l'outil de ligne de commande gcloud.

Pour ajouter une liaison unique à la stratégie IAM de votre projet, saisissez la commande suivante. Pour ajouter un utilisateur, indiquez l'option --member au format user:user@example.com. Pour ajouter un groupe, indiquez l'option --member au format group:group@example.com.

gcloud projects add-iam-policy-binding project_id \
--member group/user:address \
--role roles/bigquery.admin

Où :

  • project_id est l'ID de votre projet.
  • group/user correspond à group ou user.
  • address est l'adresse e-mail de l'utilisateur ou du groupe.

Exemple :

gcloud projects add-iam-policy-binding myproject \
--member group:group@example.com \
--role roles/bigquery.admin

La commande génère la règle mise à jour :

    bindings:
    - members:
      - group:group@example.com
        role: roles/bigquery.admin
    

Pour en savoir plus sur les rôles IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Adresse IP publique

La fédération de BigQuery et Cloud SQL ne fonctionne qu'avec les instances Cloud SQL disposant d'une connectivité IP publique. Veuillez configurer la connectivité IP publique sur votre instance Cloud SQL.

Configurer les connexions à la base de données Cloud SQL

Pour éviter de faire apparaître les identifiants d'accès à la base de données sous forme de texte clair dans une requête fédérée, vous devez d'abord créer dans BigQuery une ressource de connexion pour chaque base de données, puis référencer la ressource de connexion dans votre requête fédérée.

La ressource de connexion dispose d'un ensemble d'autorisations IAM, que vous pouvez accorder à d'autres utilisateurs. La ressource de connexion est chiffrée et stockée de manière sécurisée dans le service de connexion BigQuery et ne peut être utilisée que pour des requêtes fédérées.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Créer une ressource de connexion

Console

  1. Pour créer une ressource de connexion, accédez à la page BigQuery dans Cloud Console.

    Accéder à BigQuery

  2. Dans le menu Ajouter des données, sélectionnez Source de données externe.

    Créer une ressource de connexion

  3. Dans le volet Source de données externes, saisissez les informations suivantes :

    • Dans le champ Type de connexion, sélectionnez MySQL ou Postgres.
    • Dans le champ ID de connexion, saisissez un identifiant pour la ressource de connexion. Les lettres, les chiffres et les traits de soulignement sont autorisés.
    • Pour Emplacement de connexion, sélectionnez un emplacement (ou une région) BigQuery compatible avec votre région d'instance Cloud SQL.
    • (Facultatif) Dans le champ Nom descriptif, saisissez un nom clair pour identifier la connexion, tel que My connection resource. Le nom descriptif peut être n'importe quelle valeur permettant d'identifier la ressource de connexion si vous devez la modifier par la suite.
    • (Facultatif) : Dans le champ Description, saisissez une description de la ressource de connexion.
    • Dans le champ ID d'instance Cloud SQL, saisissez le nom complet de l'instance Cloud SQL, généralement au format project-id:location-id:instance-id. Vous pouvez trouver l'ID d'instance sur la page des détails de l'instance Cloud SQL que vous souhaitez interroger.
    • Dans le champ Nom de la base de données, saisissez le nom de la base de données.
    • Comme Nom d'utilisateur, saisissez le nom d'utilisateur de la base de données.
    • Dans le champ Mot de passe, renseignez le mot de passe d'accès à la base de données.

      • (Facultatif) Cochez la case Afficher le mot de passe pour révéler le mot de passe.

      Nouvelle ressource de connexion

  4. Cliquez sur Create connection (Créer une connexion).

bq

Saisissez la commande bq mk, puis indiquez l'option de connexion --connection. Les paramètres suivants sont également requis :

  • --connection_type
    • --connection_type est toujours CLOUD_SQL.
  • --properties
  • --connection_credential
  • --project_id
  • --location
  • Nom de la connexion. Renseignez un nom pour identifier la connexion, en utilisant uniquement des lettres, des chiffres et des traits de soulignement.

    bq mk --connection --connection_type='CLOUD_SQL' --properties=PROPERTIES --connection_credential=CREDENTIALS --project_id=PROJECT_ID --location=LOCATION new_connection
    

Remplacez l'élément suivant :

  • PROPERTIES : les paramètres de la connexion créée au format JSON. Exemple : --properties='{"param":"param_value"}'. Pour créer une ressource de connexion, vous devez fournir les paramètres instanceID, database et type. Les paramètres friendly_name et description sont facultatifs.
  • CREDENTIALS : les paramètres username et password.
  • PROJECT_ID : ID de votre projet.
  • LOCATION : région où se trouve votre instance Cloud SQL.

Par exemple, la commande suivante crée une ressource de connexion nommée "my_new_connection" (nom descriptif : "My new connection") dans un projet ayant l'ID federation-test.

bq mk --connection --connection_type='CLOUD_SQL' --properties='{"instanceId":"federation-test:us-central1:mytestsql","database":"mydatabase","type":"MYSQL"}' --connection_credential='{"username":"myusername", "password":"mypassword"}' --project_id=federation-test --location=us my_new_connection

API

Au sein de l'API BigQuery Connection, vous pouvez appeler CreateConnection dans ConnectionService pour instancier une connexion. Pour en savoir plus, consultez la page sur les bibliothèques clientes.

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Java.

import com.google.cloud.bigquery.connection.v1.CloudSqlCredential;
import com.google.cloud.bigquery.connection.v1.CloudSqlProperties;
import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.CreateConnectionRequest;
import com.google.cloud.bigquery.connection.v1.LocationName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to create a connection with cloud MySql database
public class CreateConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    String database = "MY_DATABASE";
    String instance = "MY_INSTANCE";
    String instanceLocation = "MY_INSTANCE_LOCATION";
    String username = "MY_USERNAME";
    String password = "MY_PASSWORD";
    String instanceId = String.format("%s:%s:%s", projectId, instanceLocation, instance);
    CloudSqlCredential cloudSqlCredential =
        CloudSqlCredential.newBuilder().setUsername(username).setPassword(password).build();
    CloudSqlProperties cloudSqlProperties =
        CloudSqlProperties.newBuilder()
            .setType(CloudSqlProperties.DatabaseType.MYSQL)
            .setDatabase(database)
            .setInstanceId(instanceId)
            .setCredential(cloudSqlCredential)
            .build();
    Connection connection = Connection.newBuilder().setCloudSql(cloudSqlProperties).build();
    createConnection(projectId, location, connectionId, connection);
  }

  public static void createConnection(
      String projectId, String location, String connectionId, Connection connection)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      LocationName parent = LocationName.of(projectId, location);
      CreateConnectionRequest request =
          CreateConnectionRequest.newBuilder()
              .setParent(parent.toString())
              .setConnection(connection)
              .setConnectionId(connectionId)
              .build();
      Connection response = client.createConnection(request);
      System.out.println("Connection created successfully :" + response.getName());
    }
  }
}

Dépannage

Cette section vous aide à résoudre les problèmes que vous pouvez rencontrer lors de la configuration d'une nouvelle connexion. Elle n'inclut pas tous les messages d'erreur ou problèmes possibles.

Lors du diagnostic des problèmes de connexion généraux, vérifiez les points suivants :

  • Vous avez terminé toutes les étapes de la section Avant de commencer.
  • Les propriétés de configuration de la connexion sont correctes.
  • Vous disposez des autorisations appropriées pour créer une connexion.

Si vos propriétés de connexion sont correctes et que les autorisations appropriées sont accordées, reportez-vous à la section suivante pour obtenir des solutions aux problèmes courants.

Problème : BigQuery et Cloud SQL ne sont pas co-localisés.
Solution : Les requêtes fédérées Cloud SQL ne sont possibles que dans les régions acceptant à la fois Cloud SQL et BigQuery. L'ensemble de données BigQuery et l'instance Cloud SQL doivent se trouver dans la même région, ou l'ensemble de données BigQuery doit être situé dans un emplacement multirégional tel que US et EU dans la même zone géographique compatible avec la région Cloud SQL. Consultez la section Régions compatibles pour plus d'informations sur les régions et la compatibilité des régions.
Problème : la connexion nouvellement créée ne s'affiche pas dans le projet.
Résolution : un léger délai peut être nécessaire avant que les nouvelles connexions n'apparaissent dans Cloud Console. Passez à un autre projet et attendez jusqu'à 30 secondes, puis revenez en arrière. Votre nouvelle connexion devrait s'afficher.
Problème : les performances sont plus lentes que prévu.
Solution : Les requêtes fédérées ne sont pas aussi performantes que les requêtes sur des données stockées dans BigQuery, car elles doivent interroger Cloud SQL en externe, renvoyer les données dans une table BigQuery temporaire, mapper les données avec le type de données BigQuery, puis exécuter la requête dans BigQuery. Bien que les performances de la requête ne soient pas aussi élevées, les données n'ont pas besoin d'être copiées, déplacées ou stockées à nouveau.
Problème : quel format utiliser pour le nom de la connexion ?
Solution : Le nom de la connexion doit inclure le projet, l'emplacement et le code de connexion. Le code de connexion doit suivre ce modèle : project_id.location_id.connection_idExemple : federation-test.us.my_new_connection

Gérer les ressources de connexion

Pour en savoir plus sur l'affichage, la liste, le partage, la mise à jour et la suppression des ressources de connexion, consultez la section Utiliser les connexions.

Journaux d'audit

Pour la journalisation d'audit des ressources de connexion, consultez la section Présentation des journaux d'audit BigQuery.

Régions où le service est disponible

Les requêtes fédérées Cloud SQL ne sont possibles que dans les régions gérant à la fois Cloud SQL et BigQuery.

Types d'emplacements ou de régions

Il existe deux types de zones :

  • Une région est un emplacement géographique spécifique, par exemple Londres.

  • Une zone multirégionale correspond à un secteur géographique de grande étendue, par exemple les États-Unis, et comporte au moins deux lieux géographiques.

Vous pouvez créer une connexion et exécuter une requête fédérée sur plusieurs régions en suivant les règles suivantes.

Zones multirégionales

Une zone multirégionale BigQuery peut interroger n'importe quelle région Cloud SQL de la même zone géographique (États-Unis, UE), par exemple :

  • La zone multirégionale BigQuery États-Unis peut interroger n'importe quelle région Cloud SQL de la zone géographique des États-Unis, telle que us-central1, us-east4, us-west2, etc.
  • La zone multirégionale BigQuery UE peut interroger n'importe quelle région Cloud SQL unique des États membres de l'Union européenne, telle que europe-north1, europe-west3, etc.
  • La connexion utilisée dans la requête doit se trouver au même emplacement que l'emplacement de la requête. Par exemple, les requêtes exécutées à partir de l'emplacement multirégional US doivent faire référence à une connexion située dans l'emplacement multirégional US.

La zone de traitement de la requête correspond à la zone multirégionale, US ou EU.

Pour en savoir plus sur les régions et les zones multirégionales, consultez la page Emplacements des ensembles de données.

Régions uniques

Une région unique BigQuery peut interroger Cloud SQL uniquement dans la même région. Exemple :

  • La région unique BigQuery us-east4 ne peut interroger Cloud SQL que dans us-east4.

Dans cet exemple, l'emplacement de traitement des requêtes correspond à la région unique BigQuery.

Consultez le tableau suivant pour obtenir un mappage détaillé des régions disponibles pour chaque produit.

Zones régionales

Description de la région Région Cloud SQL Région BigQuery compatible Multirégional BigQuery compatible
Amériques
Iowa us-central Non compatible : dans cette région, les instances Cloud SQL sont en V1.
Les requêtes fédérées ne sont compatibles qu'avec les instances V2 de Cloud SQL.
Iowa us-central1 us-central1 US
Las Vegas us-west4 us-west4 US
Los Angeles us-west2 us-west2 US
Montréal northamerica-northeast1 northamerica-northeast1 US
Virginie du Nord us-east4 us-east4 US
Oregon us-west1 us-west1 US
Salt Lake City us-west3 us-west3 US
São Paulo southamerica-east1 southamerica-east1
Caroline du Sud us-east1 us-east1 US
Europe
Belgique europe-west1 europe-west1 EU
Finlande europe-north1 europe-north1 EU
Francfort europe-west3 europe-west3 EU
Londres europe-west2 europe-west2 EU
Pays-Bas europe-west4 europe-west4 EU
Zurich europe-west6 europe-west6 EU
Asie-Pacifique
Hong Kong asia-east2 asia-east2
Jakarta asia-southeast2 asia-southeast2
Mumbai asia-south1 asia-south1
Osaka asia-northeast2 asia-northeast2
Séoul asia-northeast3 asia-northeast3
Singapour asia-southeast1 asia-southeast1
Sydney australia-southeast1 australia-southeast1
Taïwan asia-east1 asia-east1
Tokyo asia-northeast1 asia-northeast1

Zones multirégionales

Les emplacements multirégionaux ne sont pas disponibles pour les instances Cloud SQL. Les requêtes multirégionales Cloud SQL ne peuvent pas être utilisées pour les requêtes fédérées.

Les données situées dans la zone multirégionale EU ne sont pas stockées dans les centres de données des régions europe-west2 (Londres) ou europe-west6 (Zurich).

Limites

Instances Cloud SQL limitées

Les requêtes fédérées ne sont compatibles qu'avec l'instance Cloud SQL V2 avec adresse IP publique (et non privée).

Performances

La requête fédérée risque de ne pas être aussi rapide que d'interroger uniquement le stockage BigQuery. BigQuery doit attendre que la base de données source exécute la requête externe et déplace les données de façon temporaire de Cloud SQL vers BigQuery. Les bases de données sources telles que MySQL ou PostgreSQL ne sont généralement pas optimisées pour des requêtes analytiques complexes.

Les requêtes externes sont en lecture seule

La requête externe qui sera exécutée dans la base de données source doit être en lecture seule. Par conséquent, les instructions LMD ou LDD ne sont pas compatibles.

Types de données non compatibles

Si votre requête externe contient un type de données non géré dans BigQuery, la requête échoue immédiatement. Vous pouvez convertir le type de données non géré vers un autre type de données MySQL ou PostgreSQL compatible.

  • Type de données MySQL non compatible

    • Message d'erreur : Invalid table-valued function external_query Found unsupported MySQL type in BigQuery at [1:15]
    • Types non compatibles : GEOMETRY, BIT
    • Solution : Convertissez le type de données non compatible en STRING.
    • Exemple : SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); Cette commande convertit le type de données GEOMETRY, non compatible, en STRING.
  • Type de données PostgreSQL non compatible

    • Message d'erreur : Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • Types non compatibles : money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • Solution : Convertissez le type de données non compatible en STRING.
    • Exemple : SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); Cette commande convertit le type de données money, non compatible, en STRING.

Quotas et limites

  • Requête fédérée interrégionale : si l'emplacement de traitement des requêtes BigQuery et l'emplacement de l'instance Cloud SQL sont différents, il s'agit d'une requête interrégionale. Vous pouvez exécuter jusqu'à 1 To de requêtes interrégionales par projet et par jour.
    • Voici un exemple de requête interrégionale :
      • L'instance Cloud SQL se trouve dans us-west1, tandis que la connexion BigQuery est basée dans l'emplacement multirégional US. L'emplacement de traitement de la requête BigQuery est US.
  • Quotas : les utilisateurs doivent contrôler les quotas de requêtes dans Cloud SQL. Il n'y a pas de paramètres de quotas distincts pour les requêtes fédérées. Pour isoler la charge de travail, il est recommandé de n'interroger qu'une instance dupliquée de base de données avec accès en lecture.
  • Nombre maximal d'octets facturés autorisé : ce champ n'est pour le moment pas disponible dans le cadre des requêtes fédérées. À l'heure actuelle, il n'est pas possible de calculer les octets facturés avant l'exécution effective des requêtes fédérées.
  • Les quotas et limites Cloud SQL MySQL et PostgreSQL s'appliquent.
  • Nombre de connexions : une requête fédérée peut compter au plus 10 connexions uniques.

Tarifs

Lorsque vous interrogez Cloud SQL depuis BigQuery, le nombre d'octets renvoyés par la requête externe vous est facturé. Pour en savoir plus, consultez la section Tarifs d'analyse à la demande.

Référence

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 la ressource de connexion

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.

Détails de la ressource des identifiants de connexion

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

Mappages des types de données

Lorsque vous exécutez une requête fédérée Cloud SQL, les données de Cloud SQL (qui possèdent des types de données MySQL ou PostgreSQL) sont converties vers les types SQL standards de BigQuery. Vous trouverez ci-dessous les mappages des types de données de MySQL vers BigQuery et de PostgreSQL vers BigQuery.

Voici quelques points à retenir concernant ces mappages :

  • La plupart des types de données MySQL peuvent être mis en correspondance avec le même type de données BigQuery, à quelques exceptions près, telles que decimal, timestamp et time.
  • PostgreSQL accepte de nombreux types de données non standards, qui ne sont pas gérés dans BigQuery, par exemple money,path, uuid, boxer, etc.
  • Les types de données numériques dans MySQL et PostgreSQL sont mappés par défaut sur la valeur NUMERIC BigQuery. La plage de valeurs NUMERIC de BigQuery est moins étendue que celles de MySQL et PostgreSQL. Ils peuvent également être mappés sur les valeurs BIGNUMERIC (Aperçu), FLOAT64 ou STRING avec l'option "default_type_for_decimal_columns" dans les options EXTERNAL_QUERY.

Traiter les erreurs

Si votre requête externe contient un type de données non géré dans BigQuery, la requête échoue immédiatement. Vous pouvez convertir le type de données non géré vers un autre type de données MySQL/PostgreSQL compatible. Pour en savoir plus sur la conversion, consultez la section Types de données non compatibles située sous Dépannage.

Mappage des types MySQL vers les types BigQuery

Type MySQL Description pour MySQL Type BigQuery Différence entre les types
Entier
ENT 4 octets, 2^32 - 1 INT64
TINYINT 1 octet, 2^8 - 1 INT64
SMALLINT 2 octets, 2^16 - 1 INT64
MEDIUMINT 3 octets, 2^24 - 1 INT64
BIGINT 8 octets, 2^64 - 1 INT64
UNSIGNED BIGINT 8 octets, 2^64 - 1 NUMERIC
Valeurs numériques exactes
DECIMAL (M,D) Un nombre décimal représenté (M,D), où M est le nombre total de chiffres avant la virgule et D le nombre de décimales.
M <= 65
NUMERIC, BIGNUMERIC, FLOAT64 ou STRING

DECIMAL (M,D) est mappé sur NUMERIC par défaut, ou peut être mappé sur BIGNUMERIC, FLOAT64 ou STRING avec l'option default_type_for_decimal_columns.
Valeurs numériques approchées
FLOAT (M,D) 4 octets, M <= 23 FLOAT64
DOUBLE (M,D) 8 octets, M <= 53 FLOAT64
Date et heure
TIMESTAMP De '1970-01-01 00:00:01'UTC à ' 2038-01-19 03:14:07'UTC TIMESTAMP Une valeur TIMESTAMP de MySQL est récupérée au fuseau horaire UTC, quelle que soit l'origine de l'appel BigQuery.
DATETIME '1000-01-01 00:00:00' à '9999-12-31 23:59:59'. DATETIME
DATE De '1000-01-01' à '9999-12-31' DATE
TIME Heure au format "HH:MM:SS"
De "-838:59:59" à "838:59:59"
TIME
La plage de valeurs TIME de BigQuery est moins étendue, de 00:00:00 à 23:59:59.
ANNÉE INT64
Caractères et chaînes de caractères
ENUM Objet chaîne ayant une valeur sélectionnée parmi une liste de valeurs autorisées. STRING
CHAR (M) Chaîne de longueur fixe comprise entre 1 et 255 caractères. STRING
VARCHAR (M) Chaîne de longueur variable comprise entre 1 et 255 caractères. STRING
TEXT Champ d'une longueur maximale de 65 535 caractères. STRING
TINYTEXT Colonne TEXT d'une longueur maximale de 255 caractères. STRING
MEDIUMTEXT Colonne TEXT d'une longueur maximale de 16 777 215 caractères. STRING
LONGTEXT Colonne TEXT d'une longueur maximale de 4 294 967 295 caractères. STRING
Binary
Blob Objet binaire de grande taille (Binary Large Object) d'une longueur maximale de 65 535 caractères. BYTES
MEDIUM_BLOB BLOB d'une longueur maximale de 16 777 215 caractères. BYTES
LONG_BLOB BLOB d'une longueur maximale de 4 294 967 295 caractères. BYTES
TINY_BLOB BLOB d'une longueur maximale de 255 caractères BYTES
BINARY Chaîne binaire de longueur fixe comprise entre 1 et 255 caractères BYTES
VARBINARY Chaîne binaire de longueur variable comprise entre 1 et 255 caractères BYTES
Autre
SET Lorsque vous déclarez la colonne SET, prédéfinissez certaines valeurs. Utilisez ensuite INSERT pour ajouter tout ensemble de valeurs prédéfinies à cette colonne. STRING
GEOMETRY GEOGRAPHY PAS ENCORE COMPATIBLE
BIT INT64 PAS ENCORE COMPATIBLE

Mappage des types PostgreSQL vers les types BigQuery

Nom Description Type BigQuery Différence entre les types
Entier
smallint 2 octets, de -32 768 à +32 767. INT64
smallserial Voir smallint. INT64
Entier 4 octets, de -2 147 483 648 à +2 147 483 647. INT64
serial Voir integer. INT64
bigint 8 octets, de -9 223 372 036 854 775 808 à 9 223 372 036 854 775 807. INT64
bigserial Voir bigint. INT64
Valeurs numériques exactes
numeric [ (p, s) ] Précision jusqu'à 1 000 NUMERIC, BIGNUMERIC, FLOAT64 ou STRING numeric [ (p, s) ] est mappé par défaut sur NUMERIC, ou peut être mappé sur BIGNUMERIC, FLOAT64 ou STRING avec l'option default_type_for_decimal_columns.
decimal [ (p, s) ] Voir numeric. NUMERIC Voir numeric.
argent 8 octets, 2 chiffres décimaux d'échelle, de -92 233 720 368 547 758.08 à +92 233 720 368 547 758.07. NON COMPATIBLE.
Valeurs numériques approchées.
real 4 octets, nombre à virgule flottante à simple précision. FLOAT64
double precision 8 octets, nombre à virgule flottante à double précision. FLOAT64
Date et heure
date Date du calendrier (année, mois, jour). DATE
time [ (p) ] [ sans fuseau horaire ] Heure de la journée (sans fuseau horaire). TIME
time [ (p) ] avec fuseau horaire Heure de la journée, fuseau horaire compris. NON COMPATIBLE
timestamp [ (p) ] [ sans fuseau horaire ] Date et heure (sans fuseau horaire). DATETIME
timestamp [ (p) ] avec fuseau horaire Date et heure, fuseau horaire compris. TIMESTAMP Une valeur TIMESTAMP de PostGreSQL est récupérée au fuseau horaire UTC, quelle que soit l'origine de l'appel BigQuery.
interval Durée. NON COMPATIBLE
Caractères et chaînes de caractères
character [ (n) ] Chaîne de caractères de longueur fixe. STRING
character varying [ (n) ] Chaîne de caractères de longueur variable. STRING
text Chaîne de caractères de longueur variable. STRING
Binary
bytea Données binaires ("tableau d'octets"). BYTES
bit [ (n) ] Chaîne de bits de longueur fixe. BYTES
bit varying [ (n) ] Chaîne de bits de longueur variable. BYTES
Autre
boolean Valeur logique booléenne (true/false). BOOL
inet Adresse d'hôte, au format IPv4 ou IPv6. NON COMPATIBLE
chemin d'accès Chemin géométrique dans un plan. NON COMPATIBLE
pg_lsn Numéro de séquence de journal PostgreSQL. NON COMPATIBLE
point Point géométrique dans un plan. NON COMPATIBLE
polygon Chemin géométrique fermé dans un plan. NON COMPATIBLE
tsquery Requête de recherche de texte. NON COMPATIBLE
tsvector Document de recherche de texte. NON COMPATIBLE
txid_snapshot Instantané d'ID de transaction au niveau utilisateur. NON COMPATIBLE
uuid identifiant universel unique. NON COMPATIBLE
xml Données XML. STRING
box Cadre rectangulaire dans un plan. NON COMPATIBLE
cidr Adresse réseau, au format IPv4 ou IPv6. NON COMPATIBLE
cercle Cercle dans un plan. NON COMPATIBLE
interval [ fields ] [ (p) ] Période. NON COMPATIBLE
json Données JSON textuelles. STRING
jsonb Données JSON binaires, décomposées. NON COMPATIBLE
line Ligne infinie dans un plan. NON COMPATIBLE
lseg Segment dans un plan. NON COMPATIBLE
macaddr Adresse MAC (Media Access Control). NON COMPATIBLE
macaddr8 Adresse MAC (Media Access Control) (format EUI-64). NON COMPATIBLE