Caricare i dati con operazioni cross-cloud

In qualità di amministratore o analista BigQuery, puoi caricare i dati da un bucket Amazon Simple Storage Service (Amazon S3) o da Archiviazione BLOB di Azure nelle tabelle BigQuery. Puoi unire i dati trasferiti con quelli presenti nelle regioniGoogle Cloud o sfruttare le funzionalità di BigQuery come BigQuery ML.

Puoi trasferire i dati in BigQuery nei seguenti modi:

  • Trasferisci i dati dai file in Amazon S3 e Archiviazione BLOB di Azure nelle tabelle BigQuery utilizzando l'istruzione LOAD DATA.

  • Filtra i dati dai file in Amazon S3 o Blob Storage prima di trasferire i risultati nelle tabelle BigQuery, utilizzando l'istruzione CREATE TABLE AS SELECT. Per aggiungere i dati alla tabella di destinazione, utilizza l'istruzione INSERT INTO SELECT.

    La manipolazione dei dati viene applicata alle tabelle esterne che fanno riferimento ai dati di Amazon S3 o Blob Storage.

Quote e limiti

Per informazioni su quote e limiti, consulta Quote e limiti per i job di query.

Prezzi

Ti vengono addebitati i byte trasferiti tra i cloud utilizzando l'istruzione LOAD. Per informazioni sui prezzi, consulta la sezione Trasferimento di dati cross-cloud di Omni nella pagina Prezzi di BigQuery Omni.

Ti vengono addebitati i byte trasferiti tra i cloud utilizzando l'estratto conto CREATE TABLE AS SELECT o l'estratto conto INSERT INTO SELECT e la capacità di calcolo.

Le istruzioni LOAD e CREATE TABLE AS SELECT richiedono slot nelle regioni BigQuery Omni per analizzare i file Amazon S3 e Blob Storage per caricarli. Per saperne di più, consulta la pagina relativa ai prezzi di BigQuery Omni.

Prima di iniziare

Per fornire Google Cloud l'accesso in lettura ai file in altri cloud, chiedi all'amministratore di creare una connessione e condividerla con te. Per informazioni su come creare connessioni, consulta Connessione ad Amazon S3 o Blob Storage.

Ruolo richiesto

Per ottenere le autorizzazioni necessarie per caricare i dati utilizzando i trasferimenti cross-cloud, chiedi all'amministratore di concederti il ruolo IAM Editor dati BigQuery (roles/bigquery.dataEditor) sul set di dati. Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Questo ruolo predefinito contiene le autorizzazioni necessarie per caricare i dati utilizzando i trasferimenti cross-cloud. Per vedere quali sono esattamente le autorizzazioni richieste, espandi la sezione Autorizzazioni obbligatorie:

Autorizzazioni obbligatorie

Per caricare i dati utilizzando i trasferimenti cross-cloud sono necessarie le seguenti autorizzazioni:

  • bigquery.tables.create
  • bigquery.tables.get
  • bigquery.tables.updateData
  • bigquery.tables.update
  • bigquery.jobs.create
  • bigquery.connections.use

Potresti anche ottenere queste autorizzazioni con ruoli personalizzati o altri ruoli predefiniti.

Per saperne di più sui ruoli IAM in BigQuery, vedi Ruoli e autorizzazioni predefiniti.

Carica dati

Puoi caricare i dati in BigQuery con l'istruzione LOAD DATA [INTO|OVERWRITE].

Limitazioni

  • La connessione e il set di dati di destinazione devono appartenere allo stesso progetto. Il caricamento dei dati tra i progetti non è supportato.
  • LOAD DATA è supportato solo quando trasferisci dati da Amazon Simple Storage Service (Amazon S3) o Azure Blob Storage a una regione BigQuery colocalizzata. Per ulteriori informazioni, consulta la sezione Località.
    • Puoi trasferire i dati da qualsiasi regione US a una regione US multipla. Puoi anche eseguire il trasferimento da qualsiasi regione EU a una multiregione EU.

Esempio

Esempio 1

L'esempio seguente carica un file Parquet denominato sample.parquet da un bucket Amazon S3 nella tabella test_parquet con uno schema di rilevamento automatico:

LOAD DATA INTO mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

Esempio 2

Il seguente esempio carica un file CSV con il prefisso sampled* da Blob Storage nella tabella test_csv con partizionamento delle colonne predefinito in base all'ora:

LOAD DATA INTO mydataset.test_csv (Number INT64, Name STRING, Time DATE)
  PARTITION BY Time
  FROM FILES (
    format = 'CSV', uris = ['azure://test.blob.core.windows.net/container/sampled*'],
    skip_leading_rows=1
  )
  WITH CONNECTION `azure-eastus2.test-connection`

Esempio 3

L'esempio seguente sovrascrive la tabella esistente test_parquet con i dati di un file denominato sample.parquet con uno schema di rilevamento automatico:

LOAD DATA OVERWRITE mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

Filtra dati

Puoi filtrare i dati prima di trasferirli in BigQuery utilizzando l'istruzione CREATE TABLE AS SELECT e l'istruzione INSERT INTO SELECT.

Limitazioni

  • Se il risultato della query SELECT supera i 60 GiB in byte logici, la query non riesce. La tabella non viene creata e i dati non vengono trasferiti. Per scoprire come ridurre le dimensioni dei dati scansionati, consulta Ridurre i dati elaborati nelle query.

  • Le tabelle temporanee non sono supportate.

  • Il trasferimento del formato di dati geospaziali Well-known binary (WKB) non è supportato.

  • L'istruzione INSERT INTO SELECT non supporta il trasferimento di dati in una tabella in cluster.

  • Nell'istruzione INSERT INTO SELECT, se la tabella di destinazione è la stessa della tabella di origine nella query SELECT, l'istruzione INSERT INTO SELECT non modifica alcuna riga nella tabella di destinazione. La tabella di destinazione non viene modificata perché BigQuery non può leggere i dati tra le regioni.

  • CREATE TABLE AS SELECT e INSERT INTO SELECT sono supportati solo quando trasferisci dati da Amazon S3 o Blob Storage a una regione BigQuery colocalizzata. Per ulteriori informazioni, consulta la sezione Località.

    • Puoi trasferire i dati da qualsiasi regione US a una regione US multipla. Puoi anche eseguire il trasferimento da qualsiasi regione EU a una multiregione EU.

Esempio

Esempio 1

Supponiamo di avere una tabella BigLake denominata myawsdataset.orders che fa riferimento ai dati di Amazon S3. Vuoi trasferire i dati da questa tabella a una tabella BigQuery myotherdataset.shipments nella multiregione US.

Innanzitutto, visualizza le informazioni sulla tabella myawsdataset.orders:

    bq show myawsdataset.orders;

L'output è simile al seguente:

  Last modified             Schema              Type     Total URIs   Expiration
----------------- -------------------------- ---------- ------------ -----------
  31 Oct 17:40:28   |- l_orderkey: integer     EXTERNAL   1
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_linenumber: integer
                    |- l_returnflag: string
                    |- l_linestatus: string
                    |- l_commitdate: date

Successivamente, visualizza le informazioni sulla tabella myotherdataset.shipments:

  bq show myotherdataset.shipments

L'output è simile al seguente. Alcune colonne vengono omesse per semplificare l'output.

  Last modified             Schema             Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Total Logical
 ----------------- --------------------------- ------------ ------------- ------------ ------------------- ------------------ ---------------
  31 Oct 17:34:31   |- l_orderkey: integer      3086653      210767042                                                         210767042
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_commitdate: date
                    |- l_shipdate: date
                    |- l_receiptdate: date
                    |- l_shipinstruct: string
                    |- l_shipmode: string

Ora, utilizzando l'istruzione CREATE TABLE AS SELECT, puoi caricare selettivamente i dati nella tabella myotherdataset.orders nella regione Stati Uniti (più regioni):

CREATE OR REPLACE TABLE
  myotherdataset.orders
  PARTITION BY DATE_TRUNC(l_commitdate, YEAR) AS
SELECT
  *
FROM
  myawsdataset.orders
WHERE
  EXTRACT(YEAR FROM l_commitdate) = 1992;

Puoi quindi eseguire un'operazione di unione con la tabella appena creata:

SELECT
  orders.l_orderkey,
  orders.l_orderkey,
  orders.l_suppkey,
  orders.l_commitdate,
  orders.l_returnflag,
  shipments.l_shipmode,
  shipments.l_shipinstruct
FROM
  myotherdataset.shipments
JOIN
  `myotherdataset.orders` as orders
ON
  orders.l_orderkey = shipments.l_orderkey
AND orders.l_partkey = shipments.l_partkey
AND orders.l_suppkey = shipments.l_suppkey
WHERE orders.l_returnflag = 'R'; -- 'R' means refunded.

Quando sono disponibili nuovi dati, aggiungi i dati dell'anno 1993 alla tabella di destinazione utilizzando l'istruzione INSERT INTO SELECT:

INSERT INTO
   myotherdataset.orders
 SELECT
   *
 FROM
   myawsdataset.orders
 WHERE
   EXTRACT(YEAR FROM l_commitdate) = 1993;

Esempio 2

L'esempio seguente inserisce i dati in una tabella partizionata per data di importazione:

CREATE TABLE
 mydataset.orders(id String, numeric_id INT64)
PARTITION BY _PARTITIONDATE;

Dopo aver creato una tabella partizionata, puoi inserire i dati nella tabella partizionata in base all'ora di importazione:

INSERT INTO
 mydataset.orders(
   _PARTITIONTIME,
   id,
   numeric_id)
SELECT
 TIMESTAMP("2023-01-01"),
 id,
 numeric_id,
FROM
 mydataset.ordersof23
WHERE
 numeric_id > 4000000;

Best practice

  • Evita di caricare più file di dimensioni inferiori a 5 MB. Crea invece una tabella esterna per il file ed esporta il risultato della query in Amazon S3 o Blob Storage per creare un file più grande. Questo metodo contribuisce a migliorare il tempo di trasferimento dei dati.
    • Per informazioni sul limite per il risultato massimo della query, consulta Dimensioni massime del risultato della query BigQuery Omni.
  • Se i dati di origine si trovano in un file compresso con gzip, durante la creazione di tabelle esterne imposta l'opzione external_table_options.compression su GZIP.

Passaggi successivi