Introduzione alle query federate

Questa pagina spiega 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 ottenere il risultato sotto forma di 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:

Workflow

  • 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 per 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 è 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 senza utilizzare una funzione SQL EXTERNAL_QUERY. Non è necessario riportare i dati in BigQuery e puoi utilizzare la sintassi di BigQuery anziché scrivere nel dialetto SQL 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 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 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 delle query è la singola regione BigQuery.

Multiregione

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

  • Una query eseguita nella località multiregionale US 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 negli stati membri dell'Unione Europea, ad esempio europe-north1 o europe-west3.

  • La località in cui viene eseguita la query deve essere la stessa della risorsa di connessione. Ad esempio, le query eseguite dalla multi-regione US devono utilizzare una connessione situata nella multi-regione US.

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 nella regione multipla degli Stati Uniti e un'istanza Cloud SQL in us-central1 è veloce. Tuttavia, se esegui la stessa query tra la località multiregionale 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.

Spanner

Per Spanner, sono supportate sia le configurazioni regionali che quelle multiregionali. Una singola regione/multiregione BigQuery può eseguire query su un'istanza Spanner in qualsiasi regione Spanner supportata. Per maggiori dettagli, consulta 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 saperne di più, consulta Query federate di Cloud SQL.

Quote e limiti

  • Query federate tra regioni. Se la località di elaborazione delle query BigQuery e la località dell'origine dati esterna sono diverse, si tratta di 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 località multiregionale US. La località di elaborazione delle query BigQuery è US.
  • Quota. Gli utenti devono controllare la quota di query nell'origine dati esterna, ad esempio Cloud SQL o AlloyDB. Non è prevista un'impostazione di quota aggiuntiva per le query federate. Per ottenere l'isolamento del workload, ti consigliamo di 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 sullo spazio di archiviazione di 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.

    Anche le prestazioni delle query variano 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 va a buon fine immediatamente. Puoi eseguire il cast del tipo di dati non supportato in un tipo di dati supportato diverso.

  • Chiavi di crittografia gestite dal cliente (CMEK). La chiave 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 costo per il numero di byte restituiti dalla query esterna durante l'esecuzione di query federate da BigQuery. Per ulteriori informazioni, consulta l'articolo Prezzi dell'analisi on demand.

  • Se utilizzi le versioni di BigQuery, ti viene addebitato un importo in base al numero di slot che utilizzi. Per saperne di più, consulta Prezzi di Compute Engine con capacità.

Pushdown SQL

Le query federate sono soggette alla tecnica di ottimizzazione nota come pushdown SQL. Migliorano le prestazioni 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. I pushdown SQL includono la riduzione delle colonne (clausole SELECT) e i 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_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 inviata di nuovo 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, solo due colonne e le righe che corrispondono al predicato di filtro vengono inviate a BigQuery.

I pushdown SQL vengono applicati anche quando esegui 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 varie limitazioni che variano a seconda dell'origine dati esterna e del modo in cui esegui query sui dati.

Limitazioni per la federazione delle query quando utilizzi EXTERNAL_QUERY

  • I pushdown SQL vengono applicati solo alle query federate nel formato SELECT * FROM T.
  • Sono supportati solo il pruning delle colonne e i pushdown dei filtri. In particolare, i pushdown di calcolo, join, 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 delle query quando si utilizzano set di dati esterni di Spanner

  • Sono supportati il pruning delle colonne, il filtro, il calcolo e i pushdown di aggregazione parziale. In particolare, i pushdown di join, limit e order by 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, BYTE o array. I valori letterali che sono struct non sono supportati.
  • I pushdown delle funzioni SQL vengono applicati 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).

  • Quando utilizzi set di dati esterni, inoltre:

    • Pushdown di computing
    • Pushdown Partial Aggregate
    • Funzioni stringa
    • Funzioni matematiche
    • Funzioni di cast
    • Funzioni array

  • Quando vengono eseguite le query, aspettati la semantica di GoogleSQL, non quella di PostgreSQL. Ad esempio:

    • Per impostazione predefinita, i valori NULL vengono ordinati per primi in ordine crescente, a differenza di PostgreSQL, dove vengono ordinati per ultimi per impostazione predefinita.

    • I valori PostgreSQL NUMERIC letti da Spanner vengono gestiti in conformità alla mappatura dei tipi da Spanner a BigQuery. Ad esempio, se una colonna numerica ha il valore 1.1234567891, la seguente query restituisce 0 righe: 

      SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "SELECT * from
operations_table where numeric_col = 1.123456789")
       Tuttavia, la seguente istruzione restituisce 1 riga in base alla semantica di GoogleSQL: 
      SELECT * from operations_table where numeric_col = 1.123456789

    • La normalizzazione degli oggetti JSON si comporta in modo diverso. In Spanner JSON, le chiavi sono ordinate rigorosamente in ordine lessicografico, mentre in PostgreSQL PG JSONB sono ordinate prima in base alla lunghezza della chiave, poi in ordine lessicografico con lunghezza della chiave equivalente.

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 computing,
    • Partial Aggregate pushdown,
    • Funzioni stringa
    • Funzioni matematiche
    • Funzioni Cast
    • Funzioni Array.

Utilizza le regole di confronto nelle origini dati esterne

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

Considera il seguente esempio in cui hai una colonna flag con una regola 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 le righe in cui flag è y o Y perché la query viene eseguita sull'origine dati esterna.

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

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

A causa della regola di confronto predefinita sensibile alle maiuscole 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 la 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