Introduzione alle query federate

Questa pagina introduce l'utilizzo delle query federate e fornisce indicazioni su come eseguire query sui dati di Spanner, AlloyDB e Cloud SQL da BigQuery.

Le query federate ti consentono di inviare un'istruzione di query ai database AlloyDB, Spanner o Cloud SQL e di ricevere il risultato come tabella temporanea. Le query federate utilizzano l'API BigQuery Connection per stabilire una connessione con AlloyDB, Spanner o Cloud SQL. Nella query, utilizzi la funzione EXTERNAL_QUERY per inviare un'istruzione di query al database esterno, utilizzando il dialetto SQL di quel database. I risultati vengono convertiti in tipi di dati GoogleSQL.

Datastore supportati

Puoi utilizzare le query federate con i seguenti datastore:

Flusso di lavoro

  • Identifica il Google Cloud progetto che include l'origine dati che vuoi interrogare.
  • Un utente bigquery.admin crea una risorsa di connessione in BigQuery.
  • L'utente amministratore concede l'autorizzazione a utilizzare la risorsa di connessione all'utente B.
    • Se l'amministratore e l'utente B sono la stessa persona, non è necessario concedere l'autorizzazione.
  • L'utente B scrive una query in BigQuery con la nuova funzione SQL EXTERNAL_QUERY.

Alternative alle query federate: tabelle e set di dati esterni

Un'altra opzione per eseguire query su database operativi come Bigtable, Spanner, Cloud Storage, Google Drive e Salesforce Data Cloud consiste nell'utilizzare tabelle e set di dati esterni. I set di dati e le tabelle esterni consentono di visualizzare le tabelle e i relativi schemi ed eseguire query senza utilizzare una funzione SQL EXTERNAL_QUERY. Non devi riportare i dati in BigQuery e puoi utilizzare la sintassi BigQuery anziché scrivere nel dialetto specifico del database SQL.

Aree geografiche supportate

Per un elenco delle località supportate, consulta le sezioni seguenti:

AlloyDB e Cloud SQL

Le query federate sono supportate solo nelle regioni che supportano sia l'origine dati esterna sia BigQuery.

Puoi creare una connessione ed eseguire una query federata tra le regioni in base alle seguenti regole:

Regioni singole

Una singola regione BigQuery può eseguire query solo su una risorsa nella stessa regione.

Ad esempio, se il tuo set di dati si trova in us-east4, puoi eseguire query sulle istanze Cloud SQL o AlloyDB che si trovano in us-east4. La località di elaborazione della query è la singola regione BigQuery.

Più regioni

Una regione multiregionale BigQuery può eseguire query su qualsiasi regione di origine dati nella stessa area geografica di grandi dimensioni (Stati Uniti, UE). Le posizioni multiregionali non sono disponibili per le istanze Cloud SQL, perché vengono utilizzate solo per i backup.

  • Una query eseguita nella multiregione BigQuery US può eseguire query su qualsiasi singola regione nell'area geografica degli Stati Uniti, ad esempio us-central1, us-east4 o us-west2.

  • Una query eseguita nella multiregione BigQuery EU può interrogare qualsiasi singola regione negli stati membri dell'Unione Europea, ad esempio europe-north1 o europe-west3.

  • La località in cui viene eseguita la query deve corrispondere a quella della risorsa di connessione. Ad esempio, le query eseguite dalla multi-regione degli Stati Uniti devono utilizzare una connessione che si trova nella multi-regione degli Stati Uniti.

Il rendimento della query varia in base alla vicinanza tra il set di dati e l'origine dati esterna. Ad esempio, una query federata tra un set di dati nella regione multiregionale degli Stati Uniti e un'istanza Cloud SQL in us-central1 è veloce. Tuttavia, se esegui la stessa query tra la multiregione degli Stati Uniti e un'istanza Cloud SQL in us-east4, le prestazioni potrebbero essere più lente.

La località di elaborazione delle query è la località multi-regione, ovvero US o EU.

Spanner

Per Spanner, sono supportate le configurazioni regionali e multiregionali. Una singola regione/più regioni BigQuery può eseguire query su un'istanza Spanner in qualsiasi regione Spanner supportata. Per maggiori dettagli, consulta la sezione Query tra regioni.

Mappature dei tipi di dati

Quando esegui una query federata, i dati dell'origine dati esterna vengono convertiti in tipi GoogleSQL. Per ulteriori informazioni, consulta Query federate Cloud SQL.

Quote e limiti

  • Query federate tra regioni. Se la località di elaborazione della query BigQuery e la località dell'origine dati esterna sono diverse, si ha una query tra regioni. Puoi eseguire fino a 1 TB di query tra regioni per progetto al giorno. Di seguito è riportato un esempio di query tra regioni.
    • L'istanza Cloud SQL si trova in us-west1, mentre la connessione BigQuery si trova nella regione costituita da più regioni USA. La località di elaborazione delle query BigQuery è US.
  • Quota. Gli utenti devono controllare la quota di query nell'origine dati esterna, come Cloud SQL o AlloyDB. Non è prevista un'impostazione di quota aggiuntiva per le query federate. Per ottenere l'isolamento del workload, è consigliabile eseguire query solo su una replica di lettura del database.
  • Numero massimo di byte fatturati consentito. Questo campo non è supportato per le query federate. Non è possibile calcolare i byte fatturati prima di eseguire effettivamente le query federate.
  • Numero di connessioni. Una query federata può avere al massimo 10 connessioni uniche.
  • Cloud SQL MySQL e PostgreSQL. Si applicano quote e limitazioni.

Limitazioni

Le query federate sono soggette alle seguenti limitazioni:

  • Prestazioni. È probabile che una query federata non sia veloce come l'esecuzione di query solo sull'archiviazione BigQuery. BigQuery deve attendere che il database di origine esegua la query esterna e sposti temporaneamente i dati dall'origine dati esterna a BigQuery. Inoltre, il database di origine potrebbe non essere ottimizzato per query analitiche complesse.

    Il rendimento delle query varia anche in base alla vicinanza tra il set di dati e l'origine dati esterna. Per ulteriori informazioni, consulta Regioni supportate.

  • Le query federate sono di sola lettura. La query esterna eseguita nel database di origine deve essere di sola lettura. Pertanto, le istruzioni DML o DDL non sono supportate.

  • Tipi di dati non supportati. Se la query esterna contiene un tipo di dati non supportato in BigQuery, la query non viene eseguita immediatamente. Puoi convertire il tipo di dati non supportato in un altro tipo di dati supportato.

  • Chiavi di crittografia gestite dal cliente (CMEK). CMEK viene configurata separatamente per BigQuery e per le origini dati esterne. Se configuri il database di origine in modo che utilizzi CMEK ma non BigQuery, la tabella temporanea che contiene i risultati di una query federata viene criptata con una Google-owned and Google-managed encryption key.

Prezzi

  • Se utilizzi il modello di prezzi on demand, ti viene addebitato il numero di byte restituiti dalla query esterna quando esegui query federate da BigQuery. Per maggiori informazioni, consulta la sezione Prezzi dell'analisi on demand.

  • Se utilizzi le versioni BigQuery, i costi vengono addebitati in base al numero di slot che utilizzi. Per saperne di più, consulta la pagina Prezzi di Capacity Compute.

Pushdown SQL

Le query federate sono soggette alla tecnica di ottimizzazione nota come pushdown SQL. Migliorano il rendimento di una query delegando operazioni come il filtro all'origine dati esterna anziché eseguirle in BigQuery. La riduzione della quantità di dati trasferiti dall'origine dati esterna può ridurre il tempo di esecuzione delle query e abbassare i costi. I pushdown SQL includono l'eliminazione delle colonne (clausole SELECT) e dei filtri (clausole WHERE).

Quando utilizzi la funzioneEXTERNAL_QUERY, i pushdown SQL funzionano riscrivendo la query originale. Nell'esempio seguente, la funzione EXTERNAL_QUERY viene utilizzata per comunicare con un database Cloud SQL:

SELECT COUNT(*)
FROM (
  SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Sostituisci CONNECTION_ID con l'ID della connessione BigQuery.

Senza pushdown SQL, la seguente query viene inviata a Cloud SQL:

SELECT *
FROM operations_table

Quando viene eseguita questa query, l'intera tabella viene restituita a BigQuery, anche se sono necessarie solo alcune righe e colonne.

Con i pushdown SQL, la seguente query viene inviata a Cloud SQL:

SELECT `a`, `b`
FROM (
  SELECT * FROM operations_table) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED')))

Quando viene eseguita questa query, a BigQuery vengono inviate solo due colonne e le righe che corrispondono al predicato di filtro.

I pushdown SQL vengono applicati anche quando vengono eseguite query federate con set di dati esterni di Spanner.

Puoi esaminare i pushdown applicati (se presenti) nel piano di query.

Limitazioni

I pushdown SQL presentano vari limiti che variano a seconda dell'origine dati esterna e del modo in cui esegui query sui dati.

Limitazioni per la federazione delle query quando si utilizza EXTERNAL_QUERY

  • I pushdown SQL vengono applicati solo alle query federate del modulo SELECT * FROM T.
  • Sono supportati solo l'eliminazione delle colonne e il push-down dei filtri. In particolare, i pushdown di calcolo, unione, limite, ordinamento e aggregazione non sono supportati.
  • Per i pushdown dei filtri, i valori letterali devono essere di uno dei seguenti tipi: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP. I valori letterali che sono struct non sono supportati.
  • I pushdown delle funzioni SQL vengono applicati solo alle funzioni supportate sia da BigQuery sia da un database di destinazione.
  • I pushdown SQL sono supportati solo per AlloyDB, Cloud SQL e Spanner.
  • I pushdown SQL non sono supportati per SAP Datasphere.

Limitazioni per la federazione di query quando si utilizzano set di dati esterni di Spanner

  • Sono supportati il pruning delle colonne, il filtro, il calcolo e i pushdown dell'aggregazione parziale. In particolare, non sono supportati join, limiti e ordinamenti per aggregazione.
  • Per i pushdown dei filtri, i valori letterali devono essere di uno dei seguenti tipi: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE o array. I valori letterali che sono struct non sono supportati.
  • Il pushdown delle funzioni SQL viene applicato solo alle funzioni supportate sia da BigQuery che da Spanner.

Funzioni supportate per origine dati

Di seguito sono riportate le funzioni SQL supportate per origine dati. Nessuna funzione è supportata per SAP Datasphere.

Cloud SQL MySQL

  • Operatori logici:AND, OR, NOT.
  • Operatori di confronto:=, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Operatori aritmetici:+, -, * (solo per INT64 e FLOAT64).

Cloud SQL PostgreSQL e AlloyDB

  • Operatori logici:AND, OR, NOT.
  • Operatori di confronto:=, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Operatori aritmetici:+, -, *, / (solo per i tipi INT64, FLOAT64 e DATE, ad eccezione della sottrazione DATE).

Spanner - Dialetto PostgreSQL

  • Operatori logici:AND, OR, NOT.
  • Operatori di confronto:=, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Operatori aritmetici:+, -, *, / (solo per INT64, FLOAT64, NUMERIC).

Spanner - Dialetto GoogleSQL

  • Operatori logici:AND, OR, NOT.
  • Operatori di confronto:=, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Operatori aritmetici:+, -, *, / (solo per INT64, FLOAT64, NUMERIC).
  • Operatori aritmetici sicuri:SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE (solo per INT64, FLOAT64, NUMERIC).
  • Quando utilizzi set di dati esterni, inoltre:
    • Push-down del calcolo,
    • Partial Aggregate pushdown,
    • Funzioni String,
    • Funzioni matematiche,
    • Funzioni Cast,
    • Funzioni array.

Utilizzare le regole di confronto nelle origini dati esterne

Un'origine dati esterna potrebbe avere una collation impostata su una colonna (ad esempio, senza distinzione tra maiuscole e minuscole). Quando esegui una query federata, il database remoto tiene conto delle regole di confronto configurate.

Considera il seguente esempio in cui hai una colonna flag con regole di confronto senza distinzione tra maiuscole e minuscole nell'origine dati esterna:

SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table where flag = 'Y'")

Sostituisci CONNECTION_ID con l'ID della connessione BigQuery.

La query precedente restituisce righe in cui flag è y o Y perché la query viene eseguita sull'origine dati esterna.

Tuttavia, per la federazione di query con Cloud SQL, SAP Datasphere o origini dati AlloyDB, se aggiungi un filtro alla query principale, la query viene eseguita sul lato BigQuery con le regole di confronto predefinite. Vedi la seguente query:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE flag = 'Y'

A causa delle regole di confronto predefinite sensibili alle maiuscole e minuscole in BigQuery, la query precedente restituisce solo le righe in cui il flag è Y e filtra le righe in cui il flag è y. Per rendere la clausola WHERE senza distinzione tra maiuscole e minuscole, specifica le regole di confronto nella query:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE COLLATE(flag, 'und:ci') = 'Y'

Passaggi successivi