Query federate Cloud SQL

In qualità di analista dei dati, puoi eseguire query sui dati in Cloud SQL da BigQuery utilizzando le query federate.

La federazione BigQuery Cloud SQL consente a BigQuery di eseguire query sui dati archiviati in Cloud SQL in tempo reale, senza copiare o spostare i dati. La federazione di query supporta le istanze MySQL (2nd gen) e PostgreSQL in Cloud SQL.

In alternativa, per replicare i dati in BigQuery, puoi utilizzare anche Cloud Data Fusion o Datastream. Per saperne di più sull'utilizzo di Cloud Data Fusion, consulta la sezione Replica dei dati da MySQL a BigQuery.

Prima di iniziare

Esegui query sui dati

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

Supponiamo di archiviare una tabella dei clienti in BigQuery, mentre una tabella delle vendite in Cloud SQL e di voler 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.

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;

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 operativo PostgreSQL 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 per 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 tabelle information_schema per accedere ai metadati del database, ad esempio elencare tutte le tabelle nel database o mostrare lo schema della tabella. 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';");

Dettagli connessione

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

Nome proprietà Valore Descrizione
name string Nome della risorsa di connessione nel formato: project_id.location_id.connection_id.
location string La posizione della connessione, che deve corrispondere alla posizione 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à potranno utilizzare questa connessione.
friendlyName string Un nome visualizzato intuitivo 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-id:instance-id

Puoi trovare l'ID istanza nella pagina dei dettagli dell'istanza Cloud SQL.
cloudSql.database string Il database Cloud SQL a cui vuoi connetterti.
cloudSql.serviceAccountId string Il account di servizio configurato per accedere al database Cloud SQL.

La tabella seguente mostra le proprietà per le credenziali dell'istanza Cloud SQL:

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

Monitorare le query federate 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 in un database MySQL o PostgreSQL, la seguente annotazione può aiutarti a identificare le query provenienti da BigQuery.

  1. 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 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
*/

Risoluzione dei problemi

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

Problema:impossibile connettersi al server di database. Se stai eseguendo 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.

In alternativa, se esegui 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.

Soluzione:assicurati che siano state utilizzate credenziali valide e che siano stati seguiti tutti i prerequisiti per creare la connessione per Cloud SQL. Controlla se l'account di servizio creato automaticamente quando viene creata una connessione a Cloud SQL dispone del ruolo Client Cloud SQL (roles/cloudsql.client). Il service account ha il seguente formato: service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. Per istruzioni dettagliate, vedi Concedere l'accesso al service account.

Se l'istanza Cloud SQL utilizza un indirizzo IP privato, assicurati di aver attivato un percorso privato quando hai creato l'istanza Cloud SQL. In questo modo, BigQuery può accedere ai dati in Cloud SQL ed eseguire query su questi dati tramite una connessione privata.

Passaggi successivi