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

• Choisissez un projet Google Cloud qui inclut l'instance Cloud SQL à interroger.
• Un utilisateur bigquery.admin crée une ressource de connexion dans BigQuery.
• L'administrateur accorde l'autorisation d'utiliser la ressource de connexion à l'utilisateur B.
  • Si l'administrateur et l'utilisateur B sont la même personne, il n'est pas nécessaire d'accorder une autorisation.
• L'utilisateur B écrit une requête dans BigQuery à l'aide de la nouvelle fonction EXTERNAL_QUERY().

Attention : Les requêtes fédérées peuvent avoir des performances inférieures aux requêtes effectuées sur des données résidant dans le stockage BigQuery.

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.

Point clé : Vous pouvez également utiliser la fonction EXTERNAL_QUERY() pour accéder aux métadonnées sur la base de données externe. Pour en savoir plus, consultez la page Afficher un schéma de table Cloud SQL.

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.

     

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:[e-mail]. Pour ajouter un groupe, indiquez l'option --member au format group:[e-mail].

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:[e-mail] \
--role roles/bigquery.admin

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

    bindings:
    - members:
      - group:[e-mail]
        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.

Remarque : Vous pouvez ajouter une connectivité IP publique SANS adresse autorisée. Bien que le niveau de sécurité soit inférieur à l'utilisation d'une adresse IP privée uniquement, votre instance restera inaccessible depuis l'Internet public mais pourra être interrogée depuis BigQuery.

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

Point clé : Créez votre connexion dans le projet Google Cloud contenant l'instance Cloud SQL que vous souhaitez interroger.

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.

   

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.
     Remarque : Si les mêmes identifiants utilisateur sont valides pour d'autres bases de données de l'instance Cloud SQL, cet utilisateur peut les interroger via la même 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

Les options suivantes sont facultatives :

• --display_name : nom descriptif de la connexion.
• --description : description de la connexion.

L'ID de connexion est un paramètre facultatif qui peut être ajouté en tant que dernier argument de la commande utilisée pour le stockage en interne. Si aucun ID de connexion n'est fourni, un ID unique est généré automatiquement. L'ID de connexion peut contenir des lettres, des chiffres et des traits de soulignement.

    bq mk --connection --display_name='friendly name' --connection_type='CLOUD_SQL' \
      --properties=PROPERTIES --connection_credential=CREDENTIALS \
      --project_id=PROJECT_ID --location=LOCATION \
      CONNECTION_ID

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.
• CREDENTIALS : les paramètres username et password.
• PROJECT_ID : ID de votre projet.
• LOCATION : région où se trouve votre instance Cloud SQL.
• CONNECTION_ID : identifiant de la connexion.

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 --display_name='friendly name' --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_connection_id

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.

Afficher sur GitHub Commentaires

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.

Remarque : Les journaux d'audit pour les connexions BigQuery sont en version bêta.

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.

Attention :Depuis le 30 septembre 2020, les requêtes provenant de régions multiples ne peuvent plus faire référence à des connexions dans des régions uniques. Si une connexion est affectée, recréez-la dans le même emplacement multirégional que votre requête.

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.

Attention : Bien que les requêtes fédérées entre régions soient compatibles, les performances varient en fonction de la proximité. Par exemple, comme l'emplacement BigQuery multirégional US est proche de la région us-central1, une requête fédérée entre ces deux régions est rapide. Cependant, si vous exécutez la même requête de BigQuery US vers Cloud SQL us-east4, les performances risquent d'être considérablement ralenties.

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
	Varsovie	europe-central2	europe-central2	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, 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 à [phone].	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 à [phone] 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