Query federate di Cloud SQL
In qualità di analista di dati, puoi eseguire query sui dati di Cloud SQL da BigQuery utilizzando query federate.
La federazione di BigQuery Cloud SQL consente a BigQuery di eseguire query sui dati presenti in Cloud SQL in tempo reale, senza copiare o spostare dati. La federazione delle query supporta sia le istanze MySQL (2a generazione) sia 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 Replica dei dati da MySQL a BigQuery.
Prima di iniziare
- Assicurati che l'amministratore BigQuery abbia creato una connessione Cloud SQL e l'abbia condivisa con te.
-
Per ottenere le autorizzazioni necessarie per eseguire query su un'istanza Cloud SQL, chiedi all'amministratore di concederti il ruolo IAM Utente connessione BigQuery (
roles/bigquery.connectionUser
) per il tuo progetto. Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso.Potresti anche essere in grado di ottenere le autorizzazioni richieste tramite i ruoli personalizzati o altri ruoli predefiniti.
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 cliente in BigQuery e di archiviare una tabella di vendita in Cloud SQL e di voler unire le due tabelle in una singola query. L'esempio seguente crea una query federata a 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:
- Esegui la query esterna
SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id
nel database PostgreSQL operativo per ottenere la data del primo ordine per ogni cliente mediante la funzioneEXTERNAL_QUERY()
. - Unisci la tabella dei risultati della query esterna con la tabella dei clienti in BigQuery entro il giorno
customer_id
. - Seleziona i dati del cliente e la data del primo ordine.
Visualizza uno schema di tabella Cloud SQL
Puoi utilizzare la funzione EXTERNAL_QUERY()
per eseguire query sulle tabelle information_schema e accedere ai metadati del database, ad esempio per elencare tutte le tabelle o mostrare lo schema della tabella. Le query information_schema di esempio riportate di seguito funzionano sia in MySQL che in PostgreSQL. Puoi scoprire di più nelle tabelle information_schema MySQL e nelle tabelle information_schema 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 località della connessione, che corrisponde alla località dell'istanza Cloud SQL, con le seguenti eccezioni: Cloud SQL us-central1 corrisponde a BigQuery US, Cloud SQL europe-west1 corrisponde a BigQuery EU. |
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, generalmente 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 | L'account di servizio configurato per accedere al database Cloud SQL. |
La tabella seguente mostra le proprietà delle credenziali dell'istanza Cloud SQL:
Nome proprietà | Valore | Descrizione |
---|---|---|
username |
string | Nome utente database |
password |
string | Password database |
Monitorare 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 stai monitorando i log per l'utilizzo delle query su un database MySQL o PostgreSQL, la seguente annotazione può aiutarti a identificare le query provenienti da BigQuery.
Vai alla pagina Esplora log.
Nella scheda Query, inserisci la seguente query:
resource.type="cloudsql_database" textPayload=~"Federated query from BigQuery"
Fai clic su Esegui query.
Se sono disponibili record per le query federate di BigQuery, in Risultati delle 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 ha il ruolo Client Cloud SQL (
roles/cloudsql.client
). L'account di servizio è nel seguente formato:service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com
. Per istruzioni dettagliate, consulta Concedere l'accesso all'account di servizio.
Passaggi successivi
- Scopri di più sulle query federate.
- Scopri di più sulla mappatura dei tipi di dati da MySQL a BigQuery.
- Scopri di più sulla mappatura dei tipi di dati da PostgreSQL a BigQuery.
- Scopri di più sui tipi di dati non supportati.