Esegui query federate PostgreSQL

In qualità di data analyst, puoi eseguire query sui dati relazionali che risiedono in PostgreSQL direttamente da BigQuery in tempo reale, senza copiare o spostare i dati.

La federazione PostgreSQL di BigQuery utilizza l'API BigQuery Connection per connettersi ai motori di database PostgreSQL. Puoi quindi utilizzare la funzione EXTERNAL_QUERY per inviare la query SQL direttamente a PostgreSQL per l'esecuzione e i risultati vengono trasmessi direttamente in streaming.

Limitazioni

Le query federate PostgreSQL sono soggette alle seguenti limitazioni:

  • Puoi eseguire solo query di sola lettura tramite la funzione EXTERNAL_QUERY. Le istruzioni DDL (Data Definition Language) e DML (Data Manipulation Language) non sono supportate.
  • La federazione è accessibile solo tramite la funzione EXTERNAL_QUERY. La creazione di tabelle esterne o set di dati permanenti di BigQuery che vengono mappati direttamente alle tabelle PostgreSQL non è supportata.
  • La latenza delle query federate potrebbe essere notevolmente superiore rispetto all'esecuzione della query direttamente in PostgreSQL.
  • Il servizio di connessione sottostante potrebbe ibernare le connessioni inattive. La prima query che utilizza una connessione PostgreSQL dopo un periodo di inattività potrebbe richiedere più di un minuto per l'esecuzione durante l'inizializzazione del connettore.
  • Il peering DNS non è supportato. Devi connetterti utilizzando un indirizzo IP o un nome host completamente risolvibile all'interno della tua rete VPC.
  • La query SQL PostgreSQL deve specificare alias di colonna espliciti per tutte le colonne che contengono risultati o espressioni di funzioni (ad esempio SELECT COUNT(*) AS total_count). Le espressioni senza alias producono nomi di colonna con caratteri speciali non validi nelle definizioni dello schema della tabella BigQuery.
  • Quando l'utilizzo dell'API Compute Engine nel progetto di query è limitato dai Controlli di servizio VPC, la query federata non va a buon fine se la connessione utilizza un collegamento di rete. Assicurati che siano configurate regole di traffico in entrata e in uscita dei Controlli di servizio VPC appropriate.

  • Non sono supportati altri pushdown SQL per PostgreSQL. I filtri e le aggregazioni nella query BigQuery esterna non vengono inseriti nella query interna all'interno della funzione EXTERNAL_QUERY. Devi specificare i predicati e le proiezioni delle colonne direttamente all'interno dell'istruzione SQL PostgreSQL interna per limitare le righe e le colonne trasferite sulla rete.

  • È supportata solo l'autenticazione con nome utente e password del database PostgreSQL. I provider di identità di terze parti, Kerberos e l'autenticazione IAM del provider cloud non sono supportati.

  • Le istruzioni CREATE TABLE ... AS SELECT (CTAS) non vanno a buon fine con errori Unsupported type INT32 o Unsupported type FLOAT se la query seleziona colonne di numeri interi a 16 bit (int2, smallint, smallserial) o colonne di numeri in virgola mobile a precisione singola (float4, real). Per salvare i risultati della query in una nuova tabella BigQuery, esegui il cast esplicito delle colonne di numeri interi in INT64 e delle colonne di numeri in virgola mobile in FLOAT64 nella query:

    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'inserimento in una tabella esistente con INSERT INTO <table> SELECT ... ha esito positivo senza cast manuali perché BigQuery espande automaticamente i valori INT32 e FLOAT in modo che corrispondano alle colonne INT64 e FLOAT64 della tabella di destinazione.

  • Sia time [ without time zone ] che time with time zone (timetz) troncano i valori frazionari dei secondi (microsecondi) in secondi interi. Se è richiesta la precisione al microsecondo, formatta o esegui il cast della colonna in text o varchar nell'istruzione EXTERNAL_QUERY.

  • Tutte le colonne PostgreSQL numeric e decimal vengono mappate a BIGNUMERIC(76, 38). Le colonne con una scala maggiore di 38 vengono arrotondate a 38 cifre decimali e i valori che superano l'intervallo BIGNUMERIC (più di 38 cifre intere complete) non vanno a buon fine con un errore di valore fuori intervallo. Inoltre, i valori speciali NaN, Infinity e -Infinity non sono supportati e non funzionano in fase di runtime. Per eseguire query su numeri con parti intere più grandi, precisione frazionaria più elevata o valori speciali, esegui il cast della colonna in text nell'istruzione EXTERNAL_QUERY.

  • I valori character(n)/char(n) (bpchar) di lunghezza fissa conservano gli spazi finali fino alla lunghezza n quando vengono mappati a STRING di BigQuery.

  • Le date e i timestamp precedenti al 15 ottobre 1582 sono soggetti a uno spostamento del calendario di 10 giorni a causa della transizione storica al calendario gregoriano. Esegui il cast delle date storiche in text nell'istruzione EXTERNAL_QUERY per conservare le date esatte.

Ambienti e versioni PostgreSQL supportati

La federazione delle query supporta i database PostgreSQL nei seguenti ambienti di deployment:

  • Database PostgreSQL ospitati nel cloud o on-premise accessibili tramite indirizzi IP pubblici o collegamenti di rete (tramite Cloud VPN o Cloud Interconnect)
  • Istanze PostgreSQL self-hosted su Compute Engine o altra infrastruttura
  • PostgreSQL 16 (consigliato) e PostgreSQL 15

Prima di iniziare

Assicurati che l'amministratore di BigQuery abbia creato una connessione PostgreSQL e l'abbia condivisa con te.

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per eseguire query su PostgreSQL, chiedi all'amministratore di concederti il ruolo IAM BigQuery Connection User (roles/bigquery.connectionUser) sul progetto. Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Potresti anche riuscire a ottenere le autorizzazioni richieste tramite i ruoli personalizzati o altri ruoli predefiniti.

Esegui query sui dati

Per inviare una query federata a PostgreSQL da una query GoogleSQL, utilizza la funzione EXTERNAL_QUERY.

L'esempio seguente è una query federata che unisce i record dei clienti in BigQuery denominati mydataset.customers con gli ordini di vendita transazionali in 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;

Visualizza uno schema di tabella PostgreSQL

Puoi eseguire query sulle tabelle information_schema di PostgreSQL utilizzando EXTERNAL_QUERY per recuperare i metadati del database:

Elenca tutte le tabelle di uno schema

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

Visualizza le colonne e i tipi di dati in una tabella

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

Mappatura dei tipi di dati

Quando esegui una query federata, BigQuery converte i dati da PostgreSQL in tipi di dati GoogleSQL in base alla tabella seguente:

Tipo di dati PostgreSQL Tipo di dati BigQuery
Tipi booleani
bool, boolean BOOL
Tipi numerici
int2, smallint, smallserial INT64 (query interattive; consulta le limitazioni per CREATE TABLE ... AS SELECT)
int4, integer, serial INT64
int8, bigint, bigserial INT64
numeric, decimal BIGNUMERIC(76, 38)
float4, real FLOAT64 (query interattive; consulta le limitazioni per CREATE TABLE ... AS SELECT)
float8, double precision FLOAT64
Tipi di dati relativi a data e ora
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone, timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone, timetz TIME
Tipi di caratteri e testo
character(n), char(n), bpchar STRING
character varying(n), varchar(n) STRING
text STRING

Prezzi

Il costo dell'esecuzione di una query federata si basa su tre fattori:

  • Il costo di calcolo dell'esecuzione della query in PostgreSQL (addebitato dal provider del database PostgreSQL o dall'ambiente di hosting)
  • Il costo della larghezza di banda per il trasferimento dei risultati della query da PostgreSQL a BigQuery (costi di rete in uscita dal provider host del database)
  • Il costo di computing per l'esecuzione della query in BigQuery

I costi di computing e traffico in uscita di PostgreSQL vengono fatturati direttamente dal tuo provider PostgreSQL o dall'host dell'infrastruttura, non da Google. Per limitare i costi della larghezza di banda, ti consigliamo di scrivere la query nell'istruzione EXTERNAL_QUERY in modo da escludere tutte le colonne e le righe non necessarie per calcolare il risultato finale.

Non sono previsti costi aggiuntivi per l'esecuzione di query federate in BigQuery. Per saperne di più sui prezzi di BigQuery, consulta Prezzi.

Se la tua connessione utilizza componenti di networking Google Cloud , potrebbero essere applicati anche i seguenti prezzi:

Passaggi successivi