Esegui query federate MySQL

In qualità di data analyst, puoi eseguire query sui dati relazionali che risiedono in MySQL direttamente da BigQuery in tempo reale, senza copiare o spostare i dati.

La federazione MySQL di BigQuery utilizza l'API BigQuery Connection per connettersi ai motori di database MySQL. Puoi quindi utilizzare la funzione EXTERNAL_QUERY per inviare la query SQL direttamente a MySQL per l'esecuzione e i risultati vengono trasmessi direttamente in streaming.

Limitazioni

Le query federate MySQL sono soggette alle seguenti limitazioni:

  • Puoi eseguire solo query di sola lettura tramite la funzione EXTERNAL_QUERY. Le istruzioni DDL (Data Definition Language) e DML (Data Manipulation Language) non sono supportate.
  • La federazione è accessibile solo tramite la funzione EXTERNAL_QUERY. La creazione di tabelle esterne o set di dati permanenti di BigQuery che vengono mappati direttamente alle tabelle MySQL non è supportata.
  • La latenza delle query federate potrebbe essere notevolmente superiore rispetto all'esecuzione della query direttamente in MySQL.
  • Il servizio di connessione sottostante potrebbe ibernare le connessioni inattive. La prima query che utilizza una connessione MySQL dopo un periodo di inattività potrebbe richiedere più di un minuto per l'esecuzione durante l'inizializzazione del connettore.
  • Il peering DNS non è supportato. Devi connetterti utilizzando un indirizzo IP o un nome host completamente risolvibile all'interno della tua rete VPC.
  • La query SQL MySQL deve specificare alias di colonna espliciti per tutte le colonne che contengono risultati o espressioni di funzioni (ad esempio SELECT COUNT(*) AS total_count). Le espressioni senza alias producono nomi di colonna con caratteri speciali non validi nelle definizioni dello schema della tabella BigQuery.
  • Quando l'utilizzo dell'API Compute Engine nel progetto di query è limitato dai Controlli di servizio VPC, la query federata non va a buon fine se la connessione utilizza un collegamento di rete. Assicurati che siano configurate regole di traffico in entrata e in uscita dei Controlli di servizio VPC appropriate.

  • Non sono supportati altri pushdown SQL per MySQL. I filtri e le aggregazioni nella query BigQuery esterna non vengono inseriti nella query interna all'interno della funzione EXTERNAL_QUERY. Devi specificare i predicati e le proiezioni delle colonne direttamente all'interno dell'istruzione SQL MySQL interna per limitare le righe e le colonne trasferite sulla rete.

  • È supportata solo l'autenticazione con nome utente e password del database MySQL. I provider di identità di terze parti, Kerberos e l'autenticazione IAM del provider cloud non sono supportati.

  • Le istruzioni CREATE TABLE ... AS SELECT (CTAS) non vanno a buon fine con errori Unsupported type INT32 o Unsupported type FLOAT se la query seleziona colonne di numeri interi a 32 bit (TINYINT, SMALLINT e BOOLEAN/TINYINT(1)) o colonne di numeri in virgola mobile a precisione singola (FLOAT). Per salvare i risultati della query in una nuova tabella BigQuery, esegui il cast esplicito delle colonne di numeri interi in INT64 e delle colonne di numeri in virgola mobile in FLOAT64 nella query:

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(tiny_col AS INT64) AS tiny_col,
CAST(small_col AS INT64) AS small_col,
...
FROM EXTERNAL_QUERY(...);

    L'inserimento in una tabella esistente con INSERT INTO <table> SELECT ... ha esito positivo senza cast manuali perché BigQuery espande automaticamente i valori INT32 e FLOAT in modo che corrispondano alle colonne INT64 e FLOAT64 della tabella di destinazione.

  • Sebbene le colonne MySQL DECIMAL e NUMERIC vengano mappate a BIGNUMERIC(76, 38), MySQL consente una precisione fino a 65. Se un valore contiene più di 38 cifre intere (la precisione meno la scala è maggiore di 38), supera l'intervallo di numeri interi BIGNUMERIC e la query non riesce in fase di runtime. Per eseguire query su valori con più di 38 cifre intere, esegui il cast a CHAR o VARCHAR nell'istruzione EXTERNAL_QUERY.

  • Il tipo di dati TIME tronca i valori frazionari di frazioni di secondo (microsecondi) in secondi interi (ad esempio, 12:34:56.789123 diventa 12:34:56). Inoltre, MySQL TIME supporta durate trascorse da -838:59:59 a 838:59:59, mentre BigQuery TIME supporta solo valori di ora del giorno da 00:00:00 a 23:59:59.999999. I valori al di fuori dell'intervallo compreso tra 00:00:00 e 23:59:59 causano errori di query di runtime. Se sono richiesti valori di precisione in microsecondi o di durata trascorsa, esegui il cast della colonna in CHAR o VARCHAR all'interno dell'istruzione EXTERNAL_QUERY.

  • Le date e i timestamp precedenti al 15 ottobre 1582 sono soggetti a uno spostamento del calendario di 10 giorni a causa della transizione storica al calendario gregoriano. Per conservare le date storiche esatte precedenti al 1582, esegui il cast a CHAR o VARCHAR nell'istruzione EXTERNAL_QUERY.

Ambienti e versioni MySQL supportati

La federazione delle query supporta i database MySQL nei seguenti ambienti di deployment:

  • Database MySQL on-premise o ospitati nel cloud accessibili tramite indirizzi IP pubblici o collegamenti di rete (tramite Cloud VPN o Cloud Interconnect)
  • Istanze MySQL self-hosted su Compute Engine o un'altra infrastruttura
  • MySQL 8.4 LTS e 8.0

Prima di iniziare

Assicurati che l'amministratore di BigQuery abbia creato una connessione MySQL e l'abbia condivisa con te.

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per eseguire query su MySQL, chiedi all'amministratore di concederti il ruolo IAM BigQuery Connection User (roles/bigquery.connectionUser) nel 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 MySQL da una query GoogleSQL, utilizza la funzione EXTERNAL_QUERY.

L'esempio seguente è una query federata che unisce i record dei clienti in BigQuery denominati mydataset.customers con gli ordini di vendita transazionali in MySQL:

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/mysql_prod_conn',
  '''SELECT CUSTOMER_ID, MIN(ORDER_DATE) AS first_order_date
     FROM ORDERS
     GROUP BY CUSTOMER_ID''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

Visualizza lo schema di una tabella MySQL

Puoi eseguire query sulle viste MySQL INFORMATION_SCHEMA tramite EXTERNAL_QUERY per ispezionare i metadati del database, scoprire le tabelle ed esaminare gli schemi delle colonne prima di eseguire l'analisi.

Elenca tutti i database

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
     FROM INFORMATION_SCHEMA.SCHEMATA''');

Elenca le tabelle di un database

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DATABASE_NAME'""");

Visualizza le colonne e i tipi di dati in una tabella

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_TYPE
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME'""");

Mappatura dei tipi di dati

Quando esegui una query federata, BigQuery converte i dati da MySQL in tipi di dati GoogleSQL in base alla seguente tabella:

Tipo di dati MySQL Tipo di dati BigQuery
Tipi numerici
BOOLEAN, BOOL, TINYINT(1) INT64 (query interattive; consulta le limitazioni per CREATE TABLE ... AS SELECT)
TINYINT, SMALLINT INT64 (query interattive; consulta le limitazioni per CREATE TABLE ... AS SELECT)
MEDIUMINT, INT, INTEGER INT64
BIGINT INT64
BIGINT UNSIGNED, BIGINT ZEROFILL, SERIAL BIGNUMERIC(76, 38)
DECIMAL, DEC, NUMERIC, FIXED BIGNUMERIC(76, 38)
FLOAT FLOAT64 (query interattive; consulta le limitazioni per CREATE TABLE ... AS SELECT)
DOUBLE, DOUBLE PRECISION, REAL FLOAT64
Tipi di dati relativi a data e ora
DATE DATE
DATETIME DATETIME
TIMESTAMP TIMESTAMP
TIME TIME
Tipi di stringhe e testo
CHAR STRING
VARCHAR STRING
BINARY BYTES
VARBINARY BYTES
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTES
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT STRING
ENUM STRING
SET STRING

Prezzi

Il costo dell'esecuzione di una query federata si basa su tre fattori:

  • Il costo di calcolo dell'esecuzione della query in MySQL (addebitato dal provider del database MySQL o dall'ambiente di hosting)
  • Il costo della larghezza di banda per il trasferimento dei risultati delle query da MySQL a BigQuery (costi di rete in uscita del provider host del database)
  • Il costo di computing per l'esecuzione della query in BigQuery

I costi di computing e di traffico in uscita di MySQL vengono fatturati direttamente dal tuo provider MySQL o dall'host dell'infrastruttura, non da Google. Per limitare i costi della larghezza di banda, ti consigliamo di scrivere la query nell'istruzione EXTERNAL_QUERY in modo da escludere tutte le colonne e le righe non necessarie per calcolare il risultato finale.

Non sono previsti costi aggiuntivi per l'esecuzione di query federate in BigQuery. Per saperne di più sui prezzi di BigQuery, consulta Prezzi.

Se la tua connessione utilizza componenti di networking Google Cloud , potrebbero essere applicati anche i seguenti prezzi:

Passaggi successivi