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

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

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

Limites

Les requêtes fédérées PostgreSQL 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 des tables PostgreSQL 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 PostgreSQL.
  • Le service de connexion sous-jacent peut mettre en veille les connexions inactives. La première requête qui utilise une connexion PostgreSQL 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 PostgreSQL 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 PostgreSQL. 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 PostgreSQL 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 PostgreSQL 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 16 bits (int2, smallint, smallserial) ou des colonnes de nombres à virgule flottante simple précision (float4, real). 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(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_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.

  • time [ without time zone ] et time with time zone (timetz) tronquent les valeurs fractionnaires inférieures à la seconde (microseconde) en secondes entières. Si une précision à la microseconde est requise, formatez ou castez la colonne en text ou varchar dans l'instruction EXTERNAL_QUERY.

  • Toutes les colonnes PostgreSQL numeric et decimal sont mappées sur BIGNUMERIC(76, 38). Les colonnes dont l'échelle est supérieure à 38 sont arrondies à 38 décimales, et les valeurs qui dépassent la plage BIGNUMERIC (plus de 38 chiffres entiers) échouent avec une erreur de dépassement de plage. De plus, les valeurs spéciales NaN, Infinity et -Infinity ne sont pas acceptées et échouent au moment de l'exécution. Pour interroger des nombres avec des parties entières plus grandes, une précision fractionnaire plus élevée ou des valeurs spéciales, castez la colonne en text dans l'instruction EXTERNAL_QUERY.

  • Les valeurs character(n) / char(n) (bpchar) de longueur fixe conservent les espaces de fin jusqu'à la longueur n lorsqu'elles sont mappées à STRING BigQuery.

  • 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. Convertissez les dates historiques en text dans l'instruction EXTERNAL_QUERY pour conserver les dates exactes.

Environnements et versions PostgreSQL compatibles

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

  • Bases de données PostgreSQL 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 PostgreSQL auto-hébergées sur Compute Engine ou une autre infrastructure
  • PostgreSQL 16 (recommandé) et PostgreSQL 15

Avant de commencer

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

Rôles requis

Pour obtenir les autorisations nécessaires pour interroger PostgreSQL, 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 à PostgreSQL à 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 transactionnelles dans PostgreSQL :

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/postgres_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 PostgreSQL

Vous pouvez interroger les tables PostgreSQL information_schema à l'aide de EXTERNAL_QUERY pour récupérer les métadonnées de la base de données :

Lister toutes les tables d'un schéma

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_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
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

Mappage des types de données

Lorsque vous exécutez une requête fédérée, BigQuery convertit les données de PostgreSQL en types de données GoogleSQL, comme indiqué dans le tableau suivant :

Type de données PostgreSQL Type de données BigQuery
Types booléens
bool, boolean BOOL
Types numériques
int2, smallint, smallserial INT64 (requêtes interactives ; consultez la section Limites pour CREATE TABLE ... AS SELECT)
int4, integer, serial INT64
int8, bigint, bigserial INT64
numeric, decimal BIGNUMERIC(76, 38)
float4, real FLOAT64 (requêtes interactives ; consultez la section Limites pour CREATE TABLE ... AS SELECT)
float8, double precision FLOAT64
Types de date et d'heure
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone, timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone, timetz TIME
Types de caractères et de texte
character(n), char(n), bpchar STRING
character varying(n), varchar(n) STRING
text 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 PostgreSQL (facturé par votre fournisseur de base de données PostgreSQL ou votre environnement d'hébergement)
  • Le coût de la bande passante pour le transfert des résultats de la requête de PostgreSQL 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.

Les coûts de calcul et de sortie PostgreSQL sont facturés directement par votre fournisseur PostgreSQL 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