Introduzione alle query federate

Questa pagina illustra come utilizzare le query federate e fornisce indicazioni su come che eseguono query su 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 visualizzerai i risultati sotto forma di tabella temporanea. Le query federate utilizzano 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 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 il nuovo EXTERNAL_QUERY funzione SQL.

Set di dati esterni per Spanner

Un'altra opzione per eseguire query sui dati nel database Spanner è creare set di dati esterni Spanner. I set di dati 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 è necessario creare una connessione quando utilizzi i set di dati esterni di Spanner.

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à, consulta le sezioni seguenti:

Puoi creare una connessione ed eseguire una query federata tra regioni diverse alle seguenti regole:

Regioni singole

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

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

Più regioni

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

  • Una query eseguita nella località multiregionale 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 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 a quella del server risorsa di connessione. Ad esempio, query eseguite dalla località multiregionale degli Stati Uniti deve utilizzare una connessione che si trova in più regioni degli Stati Uniti.

Le prestazioni delle query variano in base alla vicinanza tra il set di dati e un'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 provenienti dall'origine dati esterna viene convertito in un codice GoogleSQL di testo. Per ulteriori informazioni, consulta Query federate Cloud SQL.

Quote e limiti

  • Query federate tra regioni. Se Località di elaborazione delle query BigQuery e la località dell'origine dati esterna è diversa, si tratta di un query. Puoi eseguire fino a 1 TB di query tra regioni per progetto 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 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 al database di origine per eseguire la query esterna e spostare temporaneamente i dati dall'origine dati esterna a BigQuery. Inoltre, il database di origine potrebbe non essere ottimizzato per query di analisi complesse.

    Le prestazioni delle query variano anche in base alla vicinanza tra il set di dati e l'origine dati esterna. Per ulteriori informazioni, consulta le sezioni Supportate regioni.

  • Le query federate sono di sola lettura. La query esterna che viene eseguita nel database di origine deve essere di sola lettura. Di conseguenza, le istruzioni DML o DDL non supportati.

  • 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 di l'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 Calcolo della capacità prezzi.

Push-down SQL

Le query federate sono soggette alla tecnica di ottimizzazione nota come push-down SQL. Migliorano le prestazioni di una query delegando operazioni come l'applicazione di filtri al filtro all'origine dati esterna invece di eseguirle in BigQuery. La riduzione della quantità di dati trasferiti dall'origine dati esterna può ridurre tempi di esecuzione delle query e costi inferiori. Le creatività push-down SQL includono l'eliminazione delle colonne (SELECT clausole) e filtra le clausole push-down (WHERE clausole).

Quando utilizzi la funzione EXTERNAL_QUERY, le creatività push-down 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 a BigQuery anche se sono necessarie solo alcune righe e colonne.

Con i pushdown di 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, solo due colonne e le righe corrispondenti al filtro vengono restituiti a BigQuery.

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 di 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.
  • Le azioni push-down delle funzioni SQL vengono applicate solo alle funzioni supportate sia da BigQuery sia da un database di destinazione.
  • Le creatività push-down SQL sono supportate 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 l'eliminazione delle colonne, i filtri, il calcolo e le aggregazioni push-down parziali. Nello specifico, le operazioni di join, limit e order by aggregation non sono supportate.
  • Per i 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.
  • Le azioni push-down delle funzioni SQL vengono applicate solo alle funzioni supportate sia da BigQuery che da Spanner.

Funzioni supportate per origine dati

Di seguito sono riportate le funzioni SQL supportate dall'origine dati. Nessuna funzione è supportate per SAP DataSfera.

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 INT64, FLOAT64 e tipi DATE, tranne che per la 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:
    • Compute push-down,
    • Push-down Aggregate parziale,
    • le funzioni stringa,
    • Funzioni matematiche,
    • Funzioni Cast,
    • Funzioni array.

Passaggi successivi