Fonctions de requête fédérée

GoogleSQL pour BigQuery est compatible avec les fonctions de requête fédérée suivantes.

Liste des fonctions

Nom Résumé
EXTERNAL_QUERY Exécute une requête sur une base de données externe et renvoie les résultats sous forme de table temporaire.

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id', '''external_database_query'''[, 'options'])

Description

Exécute une requête sur une base de données externe et renvoie les résultats sous forme de table temporaire. Le type de données de la base de données externe est converti en type de données GoogleSQL dans la table de résultats temporaire avec ces mappages de types de données.

  • external_database_query : requête à exécuter sur la base de données externe.

  • connection_id : ID de la ressource de connexion. La ressource de connexion contient les paramètres de connexion entre la base de données externe et BigQuery. Si aucun projet par défaut n'est configuré, ajoutez l'ID du projet devant l'ID de connexion, en respectant le format suivant :

    projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

    Remplacez les éléments suivants :

    • PROJECT_ID : ID du projet
    • LOCATION : emplacement de la connexion
    • CONNECTION_ID : ID de connexion

    Par exemple, projects/example-project/locations/us/connections/sql-bq. Pour en savoir plus, consultez la section Créer une ressource de connexion.

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

For example::
``` '{"default_type_for_decimal_columns":"numeric"}' ```

Supported options:

|Option Name | Description
|-------- | -------
|"default_type_for_decimal_columns" | Can be "float64", "numeric", "bignumeric" or "string". With this option, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to the provided BigQuery type. When this option is not provided, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to BigQuery NUMERIC type.
|"query_execution_priority" | Can be "low", "medium" or "high". Only supported in Spanner. Specifies priority for execution of the query. Execution priority is "medium" by default.

Remarques supplémentaires :

  • La fonction EXTERNAL_QUERY est généralement utilisée dans une clause FROM.
  • Vous pouvez employer la fonction EXTERNAL_QUERY() pour accéder aux métadonnées de la base de données externe.
  • 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.

Type de données renvoyé

Table BigQuery

Exemples

Supposons que vous ayez besoin de la date de la première commande pour chacun de vos clients, afin de l'inclure dans un rapport. 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 . L'exemple de requête fédérée suivant permet de réaliser cette opération et 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.
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;

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.

-- 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';'''
  );

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 ID client dans la base de données externe, mais BigQuery ne les affichera pas dans cet ordre.

-- ORDER BY will not order rows.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM customers AS c ORDER BY c.customer_id'''
  );

Mappages des types de données

Lorsque vous exécutez une requête fédérée, les données de la base de données externe sont converties en types GoogleSQL. 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. Elle peut également être mappée sur BIGNUMERIC, FLOAT64 ou STRING avec"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 plus d'informations sur la conversion, consultez la section Types de données non compatibles.

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é par (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 De '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 Lors de la déclaration d'une colonne SET, prédéfinissez certaines valeurs, puis insérez (INSERT) dans cette colonne tout ensemble de valeurs prédéfinies 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
booléen Valeur logique booléenne (true/false) BOOL
inet Adresse d'hôte, au format IPv4 ou IPv6 NON COMPATIBLE
path 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) ] Intervalle de temps 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

Types de données MySQL et PostgreSQL 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/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.

Mappage des types Spanner vers les types BigQuery

Lorsque vous exécutez une requête fédérée Spanner, les données de Spanner sont converties en types GoogleSQL.

Type GoogleSQL sur Spanner Type PostgreSQL sur Spanner Type BigQuery
ARRAY - ARRAY
BOOL bool BOOL
BYTES bytea BYTES
DATE date DATE
FLOAT64 float8 FLOAT64
INT64 bigint INT64
JSON JSONB JSON
NUMERIC numeric* NUMERIC
STRING varchar STRING
STRUCT - Non compatible avec les requêtes fédérées Spanner
TIMESTAMP timestamptz TIMESTAMP avec les nanosecondes tronquées

* Les valeurs numériques PostgreSQL avec une précision supérieure à la précision acceptée par BigQuery sont arrondies. Les valeurs supérieures à la valeur maximale génèrent une erreur Invalid NUMERIC value.

Si votre requête externe contient un type de données non géré pour les requêtes fédérées, la requête échoue immédiatement. Vous pouvez convertir le type de données non géré en un autre type de données compatible.