Funzioni di query federate

GoogleSQL per BigQuery supporta le seguenti funzioni di query federate.

Elenco funzioni

Nome Riepilogo
EXTERNAL_QUERY Esegue una query su un database esterno e restituisce i risultati come tabella temporanea.

EXTERNAL_QUERY

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

Description

Esegue una query su un database esterno e restituisce i risultati sotto forma di tabella temporanea. Il tipo di dati del database esterno viene convertito in un tipo di dati GoogleSQL nella tabella dei risultati temporanea con queste mappature dei tipi di dati.

  • external_database_query: la query da eseguire sul database esterno.

  • connection_id: l'ID della risorsa di connessione. La risorsa di connessione contiene le impostazioni per la connessione tra il database esterno e BigQuery. Se non hai configurato un progetto predefinito, anteponi l'ID progetto all'ID connessione nel seguente formato:

    projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

    Sostituisci quanto segue:

    • PROJECT_ID: l'ID progetto.
    • LOCATION: la località della connessione.
    • CONNECTION_ID: l'ID connessione.

    Ad esempio, projects/example-project/locations/us/connections/sql-bq. Per ulteriori informazioni, consulta Creare una risorsa di connessione.

+ options: una stringa facoltativa di una mappa in formato JSON con coppie chiave-valore di nome e valore dell'opzione (entrambe sono sensibili alle maiuscole).

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.

Note aggiuntive

  • La funzione EXTERNAL_QUERY viene generalmente utilizzata in una clausola FROM.
  • Puoi usare la funzione EXTERNAL_QUERY() per accedere ai metadati relativi al database esterno.
  • EXTERNAL_QUERY() non rispetterà l'ordine del risultato della query esterna, anche se la query esterna include ORDER BY.

Tipo di dati sui resi

Tabella BigQuery

Esempi

Supponiamo che tu abbia bisogno della data del primo ordine da includere in un report per ciascuno dei tuoi clienti. Questi dati non sono attualmente in BigQuery, ma sono disponibili nel tuo database operativo PostgreSQL in Il seguente esempio di query federata può risolvere questo problema e include tre parti:

  1. Esegui la query esterna SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id nel database operativo PostgreSQL per ottenere la data del primo ordine per ciascun cliente tramite la funzione EXTERNAL_QUERY().
  2. Unisci la tabella del risultato della query esterna alla tabella dei clienti in BigQuery per customer_id.
  3. Seleziona i dati del cliente e la data del primo ordine.
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;

Puoi utilizzare la funzione EXTERNAL_QUERY() per eseguire query sulle tabelle information_schema per accedere ai metadati del database, ad esempio elencare tutte le tabelle nel database o mostrare lo schema della tabella. L'esempio di query information_schema di seguito funziona sia in MySQL che in 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() non rispetterà l'ordine del risultato della query esterna, anche se la tua query esterna include ORDER BY. La query nell'esempio seguente ordina le righe in base all'ID cliente nel database esterno, ma BigQuery non restituirà le righe dei risultati in quell'ordine.

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

Mappature dei tipi di dati

Quando esegui una query federata, i dati del database esterno vengono convertiti in tipi di GoogleSQL. Di seguito sono riportate le mappature dei tipi di dati da MySQL a BigQuery e PostgreSQL a BigQuery.

Aspetti da considerare sulla mappatura:

  • La maggior parte dei tipi di dati MySQL può essere abbinata allo stesso tipo di dati BigQuery, con alcune eccezioni come decimal, timestamp e time.
  • PostgreSQL supporta molti tipi di dati non standard che non sono supportati in BigQuery, ad esempio money, path, uuid, boxer e altri.
  • Per impostazione predefinita, i tipi di dati numerici in MySQL e PostgreSQL verranno mappati al valore NUMERIC di BigQuery. L'intervallo di valori NUMERIC di BigQuery è inferiore a quello di MySQL e PostgreSQL. Può anche essere mappato a BIGNUMERIC, FLOAT64 o STRING con "default_type_for_decimal_columns" nelle opzioni EXTERNAL_QUERY.

Gestione degli errori

Se la tua query esterna contiene un tipo di dati non supportato in BigQuery, l'operazione non andrà a buon fine immediatamente. Puoi trasmettere il tipo di dati non supportato a un tipo di dati MySQL / PostgreSQL diverso supportato. Per ulteriori informazioni sulla trasmissione, consulta la pagina relativa ai tipi di dati non supportati.

Mappatura dei tipi da MySQL a BigQuery

Tipo MySQL Descrizione MySQL Tipo di BigQuery Differenza di tipo
Numero intero
INT 4 byte, 2^32 - 1 INT64
TINYINT 1 byte, 2^8 - 1 INT64
PICCOLO INT 2 byte, 2^16 - 1 INT64
MEDIA.INT 3 byte, 2^24 - 1 INT64
GRANDE 8 byte, 2^64 - 1 INT64
BIGINT NON FIRMATA 8 byte, 2^64 - 1 NUMERIC
Numero esatto
DECIMALE (M,D) Un decimale rappresenta (M, D), dove M è il numero totale di cifre e D è il numero di decimali. M <= 65 NUMERIC, BIGNUMERIC, FLOAT64 o STRING

 DECIMAL (M, D) verrà mappato a NUMERIC per impostazione predefinita oppure può essere mappato a BIGNUMERIC, FLOAT64 o STRING con default_type_for_decimal_columns.
Numero approssimativo
FLOAT (M,D) 4 byte, M <= 23 FLOAT64
DOPPIO (M,D) 8 byte, M <= 53 FLOAT64
Data e ora
TIMESTAMP "1970-01-01 00:00:01" fino a "2038-01-19 03:14:07" UTC. TIMESTAMP L'elemento TIMESTAMP di MySQL viene recuperato come fuso orario UTC, indipendentemente dal luogo in cui l'utente chiama BigQuery
DATETIME da "1000-01-01 00:00:00" a "9999-12-31 23:59:59" DATETIME
DATA da "1000-01-01" a "9999-12-31". DATA
TEMPO Ora nel formato "HH:MM:SS"
da "-838:59:59" a "838:59:59".		 ORA
 L'intervallo di tempo di BigQuery è inferiore, da 00:00:00 a 23:59:59
ANNO INT64
Caratteri e stringhe
ENUM oggetto stringa con un valore scelto da un elenco di valori consentiti STRINGA
CHAR (M) Una stringa di lunghezza fissa compresa tra 1 e 255 caratteri STRINGA
VARCHAR (M) Una stringa a lunghezza variabile compresa tra 1 e 255 caratteri. STRINGA
TESTO Un campo con una lunghezza massima di 65.535 caratteri. STRINGA
TESTO TINY Colonna TEXT con una lunghezza massima di 255 caratteri. STRINGA
TESTO MEDIO Colonna TEXT con una lunghezza massima di 16.777.215 caratteri. STRINGA
TESTO LUNGO Colonna TEXT con una lunghezza massima di 4294967295 caratteri. STRINGA
Binario
BLOB Un oggetto binario di grandi dimensioni con una lunghezza massima di 65.535 caratteri. BYTES
MEDIUM_BLOB Un BLOB con una lunghezza massima di 16777215 caratteri. BYTES
LONG_BLOB Un BLOB con una lunghezza massima di 4294967295 caratteri. BYTES
TINY_BLOB Un BLOB con una lunghezza massima di 255 caratteri. BYTES
BINARIO Una stringa binaria a lunghezza fissa compresa tra 1 e 255 caratteri. BYTES
VARBINARIO Una stringa binaria a lunghezza variabile compresa tra 1 e 255 caratteri. BYTES
Altro
IMPOSTA quando dichiari la colonna SET, predefinisci alcuni valori. Quindi, INSERISCI un insieme di valori predefiniti in questa colonna STRING
GEOMETRIA GEOGRAPHY NON ANCORA SUPPORTATO
BIT INT64 NON ANCORA SUPPORTATO

Mappatura dei tipi da PostgreSQL a BigQuery

Nome Description Tipo di BigQuery Differenza di tipo
Numero intero
Smallint 2 byte, da -32768 a +32767 INT64
smallserial Visualizza smallint INT64
integer 4 byte, da -2147483648 a +2147483647 INT64
serial Visualizza numero intero INT64
Bigint 8 byte, da -9223372036854775808 a 9223372036854775807 INT64
grandeserial Visualizza bigint INT64
Numero esatto
numerico [ (p, s) ] Precisione fino a 1000. NUMERIC, BIGNUMERIC, FLOAT64 o STRING numerico [ (p, s) ] verrà mappato a NUMERIC per impostazione predefinita o può essere mappato a BIGNUMERIC, FLOAT64 o STRING con default_type_for_decimal_columns.
Decimale [ (p, s) ] Visualizza valori numerici NUMERIC Visualizza valori numerici
denaro 8 byte, scala a 2 cifre, da -92233720368547758,08 a +92233720368547758,07 NON SUPPORTATO
Numero approssimativo
reale 4 byte, numero in virgola mobile a precisione singola FLOAT64
precisione doppia 8 byte, numero in virgola mobile a precisione doppia FLOAT64
Data e ora
date data di calendario (anno, mese, giorno) DATA
ora [ (p) ] [ senza fuso orario ] ora del giorno (nessun fuso orario) TEMPO
ora [ (p) ] con fuso orario ora del giorno, incluso il fuso orario NON SUPPORTATO
timestamp [ (p) ] [ senza fuso orario ] data e ora (nessun fuso orario) DATETIME
timestamp [ (p) ] con fuso orario data e ora, incluso il fuso orario TIMESTAMP L'elemento TIMESTAMP PostgreSQL viene recuperato come fuso orario UTC, indipendentemente dal luogo in cui l'utente chiama BigQuery
intervallo Una durata NON SUPPORTATO
Caratteri e stringhe
carattere [ (n) ] stringa di caratteri a lunghezza fissa STRINGA
carattere variabile [ (n) ] stringa di caratteri a lunghezza variabile STRINGA
testo stringa di caratteri a lunghezza variabile STRINGA
Binario
bytea dati binari ("array di byte") BYTES
bit [ (n) ] stringa di bit a lunghezza fissa BYTES
bit variabile [ (n) ] stringa di bit a lunghezza variabile BYTES
Altro
boolean valore booleano logico (vero/falso) BOOL
Inet Indirizzo host IPv4 o IPv6 NON SUPPORTATO
percorso percorso geometrico su un piano NON SUPPORTATO
pg_lsn Numero di sequenza di log PostgreSQL NON SUPPORTATO
punto punto geometrico su un piano NON SUPPORTATO
poligono percorso geometrico chiuso su un piano NON SUPPORTATO
tsquery query di ricerca testuale NON SUPPORTATO
tsvector documento di ricerca testuale NON SUPPORTATO
txid_snapshot istantanea dell'ID transazione a livello di utente NON SUPPORTATO
uuid identificatore univoco universale NON SUPPORTATO
xml Dati XML STRINGA
casella scatola rettangolare su un aereo NON SUPPORTATO
cidr Indirizzo di rete IPv4 o IPv6 NON SUPPORTATO
cerchio cerchio su un aereo NON SUPPORTATO
intervallo [ campi ] [ (p) ] periodo di tempo NON SUPPORTATO
json dati JSON testuali STRINGA
JSONB dati JSON binari, scomposti NON SUPPORTATO
linea linea infinita su un piano NON SUPPORTATO
lseg segmento su un piano NON SUPPORTATO
Macaddr Indirizzo MAC (Media Access Control) NON SUPPORTATO
macaddr8 Indirizzo MAC (Media Access Control) (formato EUI-64) NON SUPPORTATO

Tipi di dati MySQL e PostgreSQL non supportati

Se la tua query esterna contiene un tipo di dati non supportato in BigQuery, l'operazione non andrà a buon fine immediatamente. Puoi trasmettere il tipo di dati non supportato a un altro tipo di dati MySQL / PostgreSQL supportato.

  • Tipo di dati MySQL non supportato
    • Messaggio di errore: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery. at [1:15]
    • Tipo non supportato: GEOMETRY, BIT
    • Risoluzione: trasmetti il tipo di dati non supportato a STRING.
    • Esempio: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); Questo comando trasmette il tipo di dati non supportato GEOMETRY a STRING.
  • Tipo di dati PostgreSQL non supportato
    • Messaggio di errore: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • Tipo non supportato: 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
    • Risoluzione: trasmetti il tipo di dati non supportato a STRING.
    • Esempio: SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); Questo comando trasmette il tipo di dati non supportato money a string.

Mappatura dei tipi da Spanner a BigQuery

Quando esegui una query federata Spanner, i dati di Spanner vengono convertiti in tipi GoogleSQL.

Tipo GoogleSQL Spanner Tipo di Spanner PostgreSQL Tipo 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 supportata per le query federate di Spanner
TIMESTAMP timestamptz TIMESTAMP con nanosecondi troncati

* I valori numerici PostgreSQL con una precisione superiore a quella supportata da BigQuery vengono arrotondati. I valori che superano il valore massimo generano un errore Invalid NUMERIC value.

Se la tua query esterna contiene un tipo di dati non supportato per le query federate, la query non riesce immediatamente. Puoi trasmettere il tipo di dati non supportato a un tipo di dati supportato.