Esegui query federate Oracle

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

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

Limitazioni

Le query federate di Oracle 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 BigQuery o di set di dati permanenti che vengono mappati direttamente alle tabelle Oracle non è supportata.
  • La latenza delle query federate potrebbe essere notevolmente superiore rispetto all'esecuzione della query direttamente in Oracle.
  • Il servizio di connessione sottostante potrebbe ibernare le connessioni inattive. La prima query che utilizza una connessione Oracle 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 Oracle 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 Oracle. 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 Oracle interna per limitare le righe e le colonne trasferite sulla rete.

  • È supportata solo l'autenticazione con nome utente e password del database Oracle. I provider di identità di terze parti, Kerberos, RADIUS e l'autenticazione IAM di Oracle Cloud Infrastructure (OCI) non sono supportati.

  • I tipi Oracle complessi o proprietari, come i tipi definiti dall'utente (UDT), i tipi spaziali (SDO_GEOMETRY), i tipi di raccolta (VARRAY, tabelle nidificate), SYS.ANYDATA, REF, BFILE e le colonne JSON binarie (OSON) non sono supportati. Per eseguire una query su una colonna JSON binaria, serializzala in testo nella query (ad esempio con JSON_SERIALIZE(column_name) o JSON_QUERY).

  • Le colonne NUMBER non vincolate e le espressioni numeriche calcolate vengono mappate a BigQuery STRING per preservare la precisione completa senza troncamento o overflow. Le colonne NUMBER(p, s) con vincoli vengono mappate a NUMERIC (quando la precisione è inferiore o uguale a 38 e la scala è inferiore o uguale a 9) o BIGNUMERIC (quando la scala è maggiore di 9 o le cifre intere sono maggiori di 29).

  • Le colonne CHAR(n) e NCHAR(n) di lunghezza fissa conservano il padding degli spazi vuoti finali fino alla lunghezza n quando vengono mappate a STRING di BigQuery.

  • Le date e i timestamp precedenti al 15 ottobre 1582 potrebbero subire 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 in VARCHAR2 nell'istruzione EXTERNAL_QUERY.

Ambienti e versioni Oracle supportati

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

  • Oracle Database @ Google Cloud
  • Oracle Exadata Database Service
  • Istanze Oracle on-premise connesse a Google Cloud tramite Cloud VPN, Cloud Interconnect o IP pubblico su internet

La federazione delle query supporta le seguenti versioni di Oracle:

  • Oracle Database 23.x (23ai)
  • Oracle Database 21.x
  • Oracle Database 19.x (19c)

Prima di iniziare

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

Ruoli obbligatori

Per ottenere le autorizzazioni necessarie per eseguire query su Oracle, 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 Oracle 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 i record degli ordini in tempo reale di un database Oracle:

SELECT
  bq_cust.customer_id,
  bq_cust.customer_name,
  oracle_orders.order_id,
  oracle_orders.order_status,
  oracle_orders.order_total
FROM
  mydataset.customers AS bq_cust
JOIN
  EXTERNAL_QUERY(
    'projects/my-project/locations/us-central1/connections/oracle_prod_conn',
    '''SELECT customer_id, order_id, order_status, order_total
       FROM orders
       WHERE order_date >= SYSDATE - 1''') AS oracle_orders
  ON bq_cust.customer_id = oracle_orders.customer_id;

Visualizza lo schema di una tabella Oracle

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

Elenca tutte le tabelle accessibili

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT owner, table_name, tablespace_name, status
     FROM all_tables
     ORDER BY owner, table_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, data_length, data_precision, data_scale, nullable
     FROM all_tab_columns
     WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY column_id''');

Mappatura dei tipi di dati

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

Tipo di dati Oracle Tipo di dati BigQuery
Tipi numerici
NUMBER(p, s) (p ≤ 38, s ≤ 9) NUMERIC
NUMBER(p, s) (s > 9 o p > 38) BIGNUMERIC
NUMBER (scala non vincolata o mobile) STRING
FLOAT, FLOAT(p) FLOAT64
BINARY_FLOAT FLOAT64
BINARY_DOUBLE FLOAT64
Tipi di caratteri e stringhe
CHAR(n) STRING
NCHAR(n) STRING
VARCHAR2(n) STRING
NVARCHAR2(n) STRING
CLOB STRING
NCLOB STRING
LONG STRING
Tipi di file binari
RAW(n) BYTES
BLOB BYTES
LONG RAW BYTES
Tipi di data, ora e intervallo
DATE DATETIME
TIMESTAMP, TIMESTAMP(p) DATETIME
TIMESTAMP WITH TIME ZONE TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP
INTERVAL YEAR TO MONTH STRING
INTERVAL DAY TO SECOND STRING
Tipi di identificazione delle righe
ROWID STRING
UROWID STRING
Tipi semistrutturati, vettoriali e XML
JSON (testuale/OSON con serializzazione del testo) STRING
VECTOR STRING
XMLTYPE STRING

Prezzi

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

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

I costi relativi a Oracle dipendono dal tipo di servizio Oracle e dall'ambiente di hosting che utilizzi. Per limitare i costi della larghezza di banda, ti consigliamo di scrivere la query nell'istruzione EXTERNAL_QUERY in modo che escluda 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