Query federate di Cloud SQL

Le query federate in BigQuery consentono di eseguire query sui dati che risiedono in Cloud SQL in tempo reale senza copiare o spostare i dati. La federazione delle query supporta le istanze Cloud SQL per MySQL e Cloud SQL per PostgreSQL in Cloud SQL.

In alternativa, per replicare i dati in BigQuery, puoi utilizzare Cloud Data Fusion o Datastream. Per ulteriori informazioni sull'utilizzo di Cloud Data Fusion, consulta Replica dei dati da MySQL a BigQuery.

Prima di iniziare

Per eseguire query federate sui dati in Cloud SQL, segui questi passaggi:

  1. Assicurati che l'amministratore di BigQuery abbia creato una connessione Cloud SQL e l'abbia condivisa con te.
  2. Verifica che all'agente di servizio BigQuery Connection sia stato concesso il ruolo Cloud SQL Client (roles/cloudsql.client). Per saperne di più, consulta Concedi l'accesso all'agente di servizio.
  3. Se l'istanza Cloud SQL utilizza un indirizzo IP privato, assicurati che sia stato abilitato un percorso privato al momento della creazione dell'istanza. Per saperne di più, consulta Connettiti a Cloud SQL e Configura l'IP privato.

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per eseguire query su un'istanza Cloud SQL, chiedi all'amministratore di concederti il ruolo IAM BigQuery Connection User (roles/bigquery.connectionUser) nel tuo progetto. Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Potresti anche riuscire a ottenere le autorizzazioni richieste tramite i ruoli personalizzati o altri ruoli predefiniti.

Esegui query sui dati

Per inviare una query federata a Cloud SQL da una query GoogleSQL, utilizza la funzione EXTERNAL_QUERY.

Formato dell'identificatore di connessione

Il primo argomento della funzione EXTERNAL_QUERY è l'identificatore della connessione. Puoi specificare l'identificatore della connessione utilizzando uno dei seguenti formati:

  • Formato breve: [LOCATION.]CONNECTION_ID (ad esempio, 'us.my-connection' o 'my-connection')
  • Formato completo: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
  • Formato del percorso descrittivo: PROJECT_ID.LOCATION.CONNECTION_ID (ad esempio, 'my-project.us.my-connection')

Se esegui una query in una vista condivisa tra più progetti, utilizza sempre l'identificatore di connessione completo per evitare di connetterti al progetto sbagliato.

Esegui una query federata

Supponiamo che tu archivi una tabella dei clienti in BigQuery e una tabella degli ordini in Cloud SQL e che tu voglia unire le due tabelle in una singola query. L'esempio seguente esegue una query federata su una tabella Cloud SQL denominata orders e unisce i risultati a una tabella BigQuery denominata mydataset.customers.

Seleziona una delle seguenti opzioni per eseguire la query federata:

Console

  1. Nella console Google Cloud , vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nell'editor di query, inserisci la query:

    SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.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;

    Sostituisci CONNECTION_ID con l'ID della tua connessione BigQuery.

  3. Fai clic su Esegui.

    I risultati della query vengono visualizzati nel riquadro Risultati query.

bq

  2. Nella console Google Cloud , attiva Cloud Shell.

    Attiva Cloud Shell

    Nella parte inferiore della console Google Cloud viene avviata una sessione di Cloud Shell e viene visualizzato un prompt della riga di comando. Cloud Shell è un ambiente shell con Google Cloud CLI già installata e con valori già impostati per il progetto corrente. L'inizializzazione della sessione può richiedere alcuni secondi.

  3. Esegui il comando bq query con il flag --use_legacy_sql=false:

    bq query \
    --use_legacy_sql=false \
    'SELECT c.customer_id, c.name, rq.first_order_date
    FROM mydataset.customers AS c
    LEFT OUTER JOIN EXTERNAL_QUERY(
      "us.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;'

    Sostituisci CONNECTION_ID con l'ID della tua connessione BigQuery.

La query di esempio 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 Cloud SQL operativo per ottenere la data del primo ordine per ogni cliente tramite la funzione EXTERNAL_QUERY().
  2. Unisci la tabella dei risultati della query esterna alla tabella dei clienti in BigQuery in base a customer_id.
  3. Seleziona i dati del cliente e la data del primo ordine.

Visualizza lo schema di una tabella Cloud SQL

Puoi utilizzare la funzione EXTERNAL_QUERY() per eseguire query sulle viste INFORMATION_SCHEMA per accedere ai metadati del database, ad esempio per elencare tutte le tabelle nel database o mostrare gli schemi delle tabelle. Le seguenti query INFORMATION_SCHEMA di esempio funzionano sia in MySQL che in PostgreSQL. Puoi scoprire di più dalle tabelle information_schema di MySQL e dalle tabelle information_schema di 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';");

Sostituisci CONNECTION_ID con l'ID della tua connessione BigQuery.

Monitora le query federate di BigQuery

Quando esegui una query federata su Cloud SQL, BigQuery annota la query con un commento simile al seguente:

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

Se monitori i log per l'utilizzo delle query su un database MySQL o PostgreSQL, l'annotazione ti aiuta a identificare le query provenienti da BigQuery.

  1. Nella console Google Cloud , vai alla pagina Esplora log.

    Vai a Esplora log

  2. Nella scheda Query, inserisci la seguente query:

    resource.type="cloudsql_database"
textPayload=~"Federated query from BigQuery"

  3. Fai clic su Esegui query.

    Se sono disponibili record per le query federate di BigQuery, in Risultati query viene visualizzato un elenco di record simile al seguente:

    YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

Dettagli connessione

La tabella seguente mostra le proprietà della connessione Cloud SQL:

Nome proprietà Valore Descrizione
name string Nome della risorsa di connessione nel formato: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID.
location string La località della connessione, che deve corrispondere alla località dell'istanza Cloud SQL o essere una multi-regione della giurisdizione corrispondente. Ad esempio, un'istanza Cloud SQL in us-east4 può utilizzare US, mentre un'istanza Cloud SQL in europe-north1 può utilizzare EU. Solo le query BigQuery eseguite in questa località possono utilizzare questa connessione.
friendlyName string Un nome visualizzato facile da usare per la connessione.
description string Descrizione della connessione.
cloudSql.type string Può essere POSTGRES o MYSQL.
cloudSql.instanceId string Nome dell'istanza Cloud SQL, di solito nel formato: PROJECT_ID:LOCATION:INSTANCE_ID. Puoi trovare l'ID istanza nella pagina Istanze Cloud SQL.
cloudSql.database string Il database Cloud SQL a cui connettersi.
cloudSql.serviceAccountId string Il account di servizio configurato per accedere al database Cloud SQL.

La tabella seguente mostra le proprietà della credenziale dell'istanza Cloud SQL:

Nome proprietà Valore Descrizione
username string Nome utente database.
password string Password del database.

Prestazioni delle query e best practice

Per ottimizzare le prestazioni delle query federate ed evitare di caricare inutilmente l'istanza Cloud SQL, tieni presente le seguenti best practice:

  • Filtra in anticipo nella query esterna: applica i filtri WHERE e le aggregazioni GROUP BY all'interno dell'istruzione SQL EXTERNAL_QUERY anziché filtrare nella query BigQuery esterna. L'esecuzione dei filtri in Cloud SQL riduce la quantità di dati trasferiti sulla rete a BigQuery.
  • Elimina le colonne: seleziona solo le colonne specifiche necessarie nell'istruzione EXTERNAL_QUERY anziché utilizzare SELECT *. In questo modo si riducono il volume di trasferimento di rete e il tempo di esecuzione delle query.
  • Utilizza le repliche di lettura: per isolare i workload delle query analitiche dal database di produzione principale, crea una replica di lettura Cloud SQL e configura la connessione BigQuery per eseguire query sulla replica.
  • Colloca set di dati e istanze: per una latenza minima, individua il set di dati e la connessione BigQuery nella stessa regione dell'istanza Cloud SQL o nella multiregione corrispondente (ad esempio US o EU). Per ulteriori informazioni, consulta Regioni supportate.

Risoluzione dei problemi

Questa sezione ti aiuta a risolvere i problemi che potresti riscontrare durante l'invio di una query federata a Cloud SQL.

Impossibile connettersi al server di database

Quando esegui una query su un database MySQL, potresti riscontrare il seguente errore:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

Quando esegui una query su un database PostgreSQL, potresti riscontrare il seguente errore:

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Risoluzione

Per risolvere il problema, verifica i seguenti prerequisiti:

  1. Assicurati che siano state utilizzate credenziali valide e che siano stati seguiti tutti i passaggi di configurazione durante la creazione della connessione per Cloud SQL.

  2. Verifica che all'agente di servizio BigQuery Connection sia stato concesso il ruolo Cloud SQL Client (roles/cloudsql.client). L'indirizzo email dell'agente di servizio ha il seguente formato:

    service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.

    Per istruzioni dettagliate, consulta Concedi l'accesso all'agente di servizio.

  3. Se l'istanza Cloud SQL utilizza un indirizzo IP privato, assicurati di aver abilitato un percorso privato quando hai creato l'istanza Cloud SQL. L'abilitazione di un percorso privato consente a BigQuery di accedere ai dati in Cloud SQL ed eseguire query su una connessione privata.

Connessione al database MySQL non riuscita a causa del plug-in di autenticazione

Quando esegui una query federata su un'istanza MySQL, potresti riscontrare il seguente errore:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

Risoluzione

Questo errore si verifica per gli account utente del database configurati con l'autenticazione caching_sha2_password, che non è supportata per le query federate.

Se l'istanza Cloud SQL esegue MySQL versione 8.0 o precedente, puoi modificare il plug-in di autenticazione per l'account utente utilizzato da BigQuery. Esegui questo comando sull'istanza Cloud SQL:

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Sostituisci quanto segue:

  • USERNAME: l'account utente del database utilizzato da BigQuery per connettersi all'istanza Cloud SQL per MySQL.
  • PASSWORD: la password dell'account utente del database.

Se l'istanza Cloud SQL esegue MySQL versione 8.4, il plug-in mysql_native_password è deprecato. Non consigliamo di eseguire il downgrade di un database esistente dalla versione 8.4 alla versione 8.0 per i workload di produzione.

L'upgrade di un'istanza da una versione precedente a MySQL 8.4 non influisce sulle connessioni al database esistenti, a condizione che non modifichi il plug-in di autenticazione utente prima dell'upgrade. Poiché solo gli utenti creati con mysql_native_password possono essere utilizzati per le query federate, le query inviate a MySQL 8.4 devono utilizzare un account utente creato prima dell'upgrade.

Limitazioni

Le query federate in Cloud SQL sono soggette alle seguenti limitazioni:

  • Query di sola lettura: le query federate sono di sola lettura. Le query esterne devono essere istruzioni SELECT; le istruzioni DDL (Data Definition Language) e DML (Data Manipulation Language) non sono supportate.
  • Prestazioni delle query: le query federate non sono veloci quanto le query sullo spazio di archiviazione di BigQuery. BigQuery deve attendere che Cloud SQL esegua la query esterna e trasferisca il set di risultati sulla rete.
  • Vincoli relativi alle regioni: la località di elaborazione delle query BigQuery deve corrispondere o essere compatibile con quella dell'istanza Cloud SQL. Per saperne di più, consulta Regioni supportate.
  • Tipi di dati non supportati: se una query esterna restituisce un tipo di dati non supportato da BigQuery, la query non va a buon fine. Puoi eseguire il cast del tipo di dati non supportato in un tipo di dati supportato all'interno della query esterna. Per saperne di più, consulta Mappature dei tipi di dati.
  • Numero massimo di connessioni: una query federata può fare riferimento a un massimo di 10 connessioni univoche.
  • Limitazione del plug-in di autenticazione: il plug-in MySQL caching_sha2_password non è supportato per le query federate. Per saperne di più, consulta la sezione Risoluzione dei problemi.

Passaggi successivi