Introduzione alle query federate

Questa pagina illustra come utilizzare le 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 del 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 progetto Google Cloud che include l'origine dati su cui vuoi eseguire query.
  • Un utente bigquery.admin crea una risorsa di connessione in BigQuery.
  • L'utente amministratore concede l'autorizzazione all'utilizzo della 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 è utilizzare tabelle e set di dati esterni. I set di dati e le tabelle esterni ti consentono di visualizzare le tabelle e i relativi schemi ed eseguire query su di essi senza utilizzare una funzione SQL EXTERNAL_QUERY. Non devi importare nuovamente i dati in BigQuery e puoi utilizzare la sintassi di BigQuery anziché scrivere nel dialetto specifico del database SQL.

Aree geografiche supportate

Le query federate sono supportate solo nelle regioni che supportano sia l'origine dati esterna sia BigQuery. Per un elenco delle località supportate, consulta le sezioni seguenti:

Puoi creare una connessione ed eseguire una query federata tra 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 su istanza Cloud SQL, istanze AlloyDB o database Spanner situati in us-east4. La località di elaborazione della query è la singola regione BigQuery.

Più regioni

Una località BigQuery con più regioni può eseguire query su qualsiasi regione dell'origine dati nella stessa vasta area geografica (USA, UE). Le località multiregionali non sono disponibili per le istanze Cloud SQL, in quanto vengono utilizzate solo per i backup. Una località BigQuery con più regioni può anche eseguire query su un'istanza Spanner nella stessa località multiregionale.

  • Una query eseguita nella località multiregionale degli Stati Uniti di BigQuery 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 località multiregionale dell'UE di BigQuery può eseguire query su qualsiasi singola regione degli stati membri dell'Unione Europea, ad esempio europe-north1 o europe-west3.

  • La posizione in cui viene eseguita la query deve corrispondere alla posizione della risorsa di connessione. Ad esempio, le query eseguite dalla multi-regione degli Stati Uniti devono utilizzare una connessione situata nella multi-regione degli Stati Uniti.

Le prestazioni delle query variano in base alla vicinanza tra il set di dati e l'origine dati esterna. Ad esempio, una query federata tra un set di dati negli Stati Uniti in più regioni e un'istanza Cloud SQL in us-central1 è rapida. Tuttavia, se esegui la stessa query tra più regioni 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à multiregionale US o EU.

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 basa sulla regione costituita da più regioni degli Stati Uniti. 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 carico di lavoro, è consigliabile eseguire query solo su una replica di lettura del database.
  • Numero massimo di byte fatturati consentiti. 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 univoche.
  • 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 quanto 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 di analisi 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 dato non supportato in BigQuery, la query non va a buon fine immediatamente. Puoi eseguire il casting del tipo di dati non supportato in un altro tipo di dati supportato.

  • Progetto. Devi creare la risorsa di connessione nello stesso progetto dell'istanza Cloud SQL o AlloyDB.

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 ulteriori informazioni, consulta l'articolo Prezzi dell'analisi on demand.

  • Se utilizzi le versioni di BigQuery, gli addebiti si basano sul numero di slot che utilizzi. Per ulteriori informazioni, consulta la pagina relativa ai prezzi del calcolo della capacità.

Pushdown di SQL

Le query federate sono soggette alla tecnica di ottimizzazione nota come pushdown SQL. Migliorano il rendimento di una query delegando operazioni come l'applicazione di filtri 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 i costi. Le pushdown di SQL includono l'eliminazione delle colonne (clausole SELECT) e le pushdown dei filtri (clausole WHERE).

Quando utilizzi la funzione EXTERNAL_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>", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

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

SELECT *
FROM operations_table

Quando viene eseguita questa query, l'intera tabella viene inviata nuovamente 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 restituite 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 Spanner.

Puoi esaminare le eventuali appiattimenti applicati nel piano di query.

Limitazioni

Le operazioni pushdown di 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 tipo SELECT * FROM T.
  • Sono supportati solo l'eliminazione delle colonne e il pushdown dei filtri. Nello specifico, non sono supportati i pushdown di calcolo, join, limit, order by e aggregazione.
  • 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 per le 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 delle query quando si utilizzano set di dati esterni di Spanner

  • Sono supportati il pruning delle colonne, i filtri, i calcoli e i pushdown di aggregazione parziale. Nello specifico, l'unione, il limite e l'ordinamento per aggregazione non sono supportati.
  • Per le applicazioni di pushdown dei filtri, i valori letterali devono essere uno dei seguenti tipi: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE o Array. I valori letterali che sono struct non sono supportati.
  • I pushdown delle funzioni SQL vengono applicati solo per le funzioni supportate sia da BigQuery sia da Spanner.

Funzioni supportate per origine dati

Di seguito sono riportate le funzioni SQL supportate in base all'origine dati. Per SAP Datasphere non sono supportate funzioni.

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:
    • Pushdown di calcolo,
    • Pushdown Aggregate parziale,
    • Funzioni di stringa,
    • Funzioni matematiche,
    • Funzioni Esegui,
    • Funzioni array.

Passaggi successivi