Carregar dados com operações entre nuvens

Como administrador ou analista do BigQuery, é possível carregar dados de um bucket do Amazon Simple Storage Service (Amazon S3) ou do Armazenamento de Blobs do Azure em tabelas do BigQuery. É possível mesclar os dados transferidos com os dados presentes nas regiões do Google Cloud ou aproveitar os recursos do BigQuery, como o BigQuery ML.

É possível transferir dados para o BigQuery das seguintes maneiras:

Cotas e limites

Para informações sobre cotas e limites, consulte Cotas e limites de jobs de consulta.

Preços

A cobrança pelos bytes transferidos entre nuvens é feita usando a instrução LOAD. Para informações sobre preços, consulte a seção sobre Transferência de dados em várias nuvens do Omni em Preços do BigQuery Omni.

A cobrança é feita pelos bytes transferidos entre nuvens usando a instrução CREATE TABLE AS SELECT ou a instrução INSERT INTO SELECT e a de capacidade de computação.

As instruções LOAD e CREATE TABLE AS SELECT exigem slots nas regiões do BigQuery Omni para verificar os arquivos do Amazon S3 e do Blob Storage para carregá-los. Para mais informações, consulte os Preços do BigQuery Omni.

Antes de começar

Para fornecer ao Google Cloud acesso de leitura aos arquivos em outras nuvens, peça ao administrador para criar uma conexão e compartilhá-la com você. Para informações sobre como criar conexões, consulte Conectar-se ao Amazon S3 ou Armazenamento de Blobs.

Papel necessário

Para receber as permissões necessárias para carregar dados usando transferências entre nuvens, peça ao administrador para conceder a você o papel do IAM de Editor de dados do BigQuery (roles/bigquery.dataEditor) no conjunto de dados. Para mais informações sobre como conceder papéis, consulte Gerenciar acesso.

Esse papel predefinido contém as permissões necessárias para carregar dados usando transferências entre nuvens. Para conferir as permissões exatas necessárias, expanda a seção Permissões necessárias:

Permissões necessárias

As permissões a seguir são necessárias para carregar dados usando transferências entre nuvens:

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

Essas permissões também podem ser concedidas com papéis personalizados ou outros papéis predefinidos.

Para mais informações sobre os papéis do IAM no BigQuery, consulte Papéis e permissões predefinidos.

Carregar dados

É possível carregar dados no BigQuery com a instrução LOAD DATA [INTO|OVERWRITE].

Limitações

  • Não é possível carregar dados em uma tabela de destino com partição de tempo de ingestão.
  • Jobs LOAD DATA não são executados em reservas. Os jobs utilizam slots sob demanda gerenciados pelo Google Cloud.
  • A conexão e o conjunto de dados de destino precisam pertencer ao mesmo projeto. Não é possível carregar dados entre projetos.
  • LOAD DATA só é compatível quando você transfere dados de um Amazon Simple Storage Service (Amazon S3) ou de um Armazenamento de Blobs do Azure para uma região colocalizada do BigQuery. Saiba mais em Locais.
    • É possível transferir dados de qualquer região US para uma multirregião US. Também é possível transferir de qualquer região EU para uma multirregião EU.

Exemplo

Exemplo 1

O exemplo a seguir carrega um arquivo parquet chamado sample.parquet de um bucket do Amazon S3 na tabela test_parquet com um esquema de detecção automática:

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

Exemplo 2

O exemplo a seguir carrega um arquivo CSV com o prefixo sampled* do Armazenamento de Blobs na tabela test_csv com particionamento de colunas predefinido por tempo:

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`

Exemplo 3

O exemplo a seguir substitui a tabela test_parquet existente por dados de um arquivo chamado sample.parquet com um esquema de detecção automática:

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

Filtrar dados

É possível filtrar os dados antes de transferi-los para o BigQuery usando a instrução CREATE TABLE AS SELECT e a instrução INSERT INTO SELECT.

Limitações

  • Se o resultado da consulta SELECT exceder 60 GiB em bytes lógicos, a consulta falhará. A tabela não é criada e os dados não são transferidos. Para saber como reduzir o tamanho dos dados que são verificados, consulte Reduzir os dados processados em consultas.

  • Tabelas temporárias não são compatíveis.

  • A transferência do formato de dados geoespaciais de Binário conhecido (WKB) não é compatível.

  • A instrução INSERT INTO SELECT não é compatível com a transferência de dados para uma tabela em cluster.

  • Na instrução INSERT INTO SELECT, se a tabela de destino for igual à tabela de origem na consulta SELECT, a instrução INSERT INTO SELECT não modificará nenhuma linha na tabela de destino. do Google Analytics. A tabela de destino não é modificada porque o BigQuery não pode ler dados entre regiões.

  • CREATE TABLE AS SELECT e INSERT INTO SELECT só são compatíveis quando você transfere dados de um Amazon S3 ou Blob Storage para uma região colocalizada do BigQuery. Saiba mais em Locais.

    • É possível transferir dados de qualquer região US para uma multirregião US. Também é possível transferir de qualquer região EU para uma multirregião EU.

Exemplo

Exemplo 1

Suponha que você tenha uma tabela do BigLake chamada myawsdataset.orders que faz referência a dados do Amazon S3. Você quer transferir dados dessa tabela para uma tabela do BigQuery myotherdataset.shipments na multirregião EUA.

Primeiro, exiba informações sobre a tabela myawsdataset.orders:

    bq show myawsdataset.orders;

O resultado será assim:

  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

Em seguida, exiba informações sobre a tabela myotherdataset.shipments:

  bq show myotherdataset.shipments

A resposta será semelhante a esta: Algumas colunas são omitidas para simplificar a saída.

  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

Agora, usando a instrução CREATE TABLE AS SELECT, é possível carregar dados de maneira seletiva na tabela myotherdataset.orders na multirregião US:

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;

Depois, você pode executar uma operação de mesclagem com a tabela recém-criada:

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 novos dados estiverem disponíveis, anexe os dados do ano de 1993 à tabela de destino usando a instrução INSERT INTO SELECT:

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

Exemplo 2

O exemplo a seguir insere dados em uma tabela particionada por tempo de ingestão:

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

Depois de criar uma tabela particionada, insira dados na tabela particionada por tempo de ingestão:

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

Práticas recomendadas

A seguir