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 doGoogle Cloud ou aproveitar os recursos do BigQuery como o BigQuery ML. Você também pode criar réplicas de visualizações materializadas de determinadas fontes externas para disponibilizar esses dados no BigQuery.

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

• Transfira dados de arquivos no Amazon S3 e no Azure Blob Storage para tabelas do BigQuery usando a instrução LOAD DATA.

• Filtre os dados dos arquivos no Amazon S3 ou no Blob Storage antes de transferir os resultados para tabelas do BigQuery usando a instrução CREATE TABLE AS SELECT. Para anexar dados à tabela de destino, use a instrução INSERT INTO SELECT. A manipulação de dados é aplicada nas tabelas externas que fazem referência a dados do Amazon S3 ou do Blob Storage.

• Crie réplicas de visualização materializada de dados externos do Amazon S3, do Apache Iceberg ou do Salesforce Data Cloud em um conjunto de dados do BigQuery para que eles fiquem disponíveis localmente no BigQuery.

Observação: se você quiser transferir arquivos grandes do bucket do Amazon Simple Storage Service (Amazon S3) ou do Armazenamento de Blobs do Azure para tabelas do BigQuery em programaticamente, use o serviço de transferência de dados do BigQuery. Se você quiser ler e processar dados antes de transferir dados para tabelas do BigQuery, use a instrução CREATE TABLE AS SELECT.

Cotas e limites

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

Antes de começar

Para fornecer ao Google Cloud o acesso de leitura necessário para carregar ou filtrar dados em outras nuvens, peça ao administrador para criar uma conexão e compartilhar 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 a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

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 funções personalizadas ou outros papéis predefinidos.

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

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.

Para réplicas de visualizações materializadas de fontes de dados externas, os custos também podem incluir os preços das visualizações materializadas.

Práticas recomendadas para opções de carga e filtro

• Evite transferir vários arquivos pequenos com menos de 5 MB. Em vez disso, crie uma tabela externa para o arquivo e exporte o resultado da consulta para o Amazon S3 ou Armazenamento de Blobs para criar um arquivo maior. Esse método ajuda a melhorar o tempo de transferência dos dados.
Para informações sobre o limite para o resultado máximo de consulta, consulte Tamanho máximo do resultado de consulta do BigQuery Omni.
• Se os dados de origem estiverem em um arquivo compactado gzip, defina a opção external_table_options.compression como GZIP ao criar tabelas externas.

Carregar dados

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

Limitações

• 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;

Observação: se você receber um erro ResourceExhausted, tente de novo mais tarde. Se o problema persistir, entre em contato com o suporte.

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 INT64)
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;

Réplicas de visualizações materializadas

Uma réplica de visualização materializada é uma replicação de dados externos do Amazon Simple Storage Service (Amazon S3), do Apache Iceberg ou do Salesforce Data Cloud em um conjunto de dados do BigQuery para que os dados fiquem disponíveis localmente no BigQuery. Isso pode ajudar você a evitar custos de saída de dados e melhorar o desempenho das consultas. O BigQuery permite criar visualizações materializadas em tabelas de metadados ativadas por cache do BigLake com base em dados do Amazon Simple Storage Service (Amazon S3), do Apache Iceberg ou do Salesforce Data Cloud.

Uma réplica de visualização materializada permite o uso dos dados da visualização materializada do Amazon S3, do Iceberg ou do Data Cloud em consultas, o que evita custos de saída de dados e melhora o desempenho da consulta. Para isso, uma réplica de visualização materializada replica os dados do Amazon S3, Iceberg ou Data Cloud em um conjunto de dados em uma região do BigQuery com suporte, para que eles fiquem disponíveis localmente no BigQuery.

Antes de começar

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. Verifique se você tem as permissões necessárias do Identity and Access Management (IAM) para realizar as tarefas desta seção.

Funções exigidas

Para receber as permissões necessárias para realizar as tarefas desta seção, peça ao administrador para conceder a você o papel do IAM de Administrador do BigQuery (roles/bigquery.admin). Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Esse papel predefinido contém as permissões necessárias para executar as tarefas nesta seção. 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 realizar as tarefas nesta seção:

• bigquery.tables.create
• bigquery.tables.get
• bigquery.tables.getData
• bigquery.tables.replicateData
• bigquery.jobs.create

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

Para mais informações sobre o BigQuery IAM, consulte Papéis e permissões do BigQuery IAM.

Preparar um conjunto de dados para réplicas de visualização materializadas

Antes de criar uma réplica de visualização materializada, conclua as seguintes tarefas:

1. Crie um conjunto de dados em uma região compatível com o Amazon S3.
2. Crie uma tabela de origem no conjunto de dados criado na etapa anterior. A tabela de origem pode ser de qualquer um destes tipos:
   • Uma tabela do Amazon S3 BigLake com o armazenamento em cache de metadados ativado e que não usa um formato de arquivo Iceberg.
   • Uma tabela externa do Apache Iceberg.
   • Uma tabela do Data Cloud.

Criar réplicas de visualizações materializadas

Selecione uma das seguintes opções:

Console

1. No console do Google Cloud , acesse a página BigQuery.

   Acessar o BigQuery

2. No painel Explorer, navegue até o projeto e o conjunto de dados em que você quer criar a réplica da visualização materializada e clique em Conferir ações > Criar tabela.

3. Na seção Origem da caixa de diálogo Criar tabela, faça o seguinte:

   1. Em Criar tabela de, selecione Tabela/visualização atual.
   2. Em Projeto, insira o projeto em que a tabela ou visualização de origem está localizada.
   3. Em Conjunto de dados, insira o conjunto de dados em que a tabela ou visualização de origem está localizada.
   4. Em Visualização, insira a tabela ou visualização de origem que você está replicando. Se você escolher uma visualização, ela precisa ser uma visualização autorizada. Caso contrário, todas as tabelas usadas para gerar essa visualização precisam estar localizadas no conjunto de dados dela.

4. Opcional: em Inatividade máxima da visualização materializada local, insira um valor max_staleness para sua visualização materializada local.

5. Na seção Destino da caixa de diálogo Criar tabela, faça o seguinte:

   1. Em Projeto, insira o projeto em que você quer criar a réplica de visualização materializada.
   2. Em Conjunto de dados, insira o conjunto de dados em que você quer criar a réplica de visualização materializada.
   3. Em Nome da visualização materializada da réplica, insira um nome para a réplica.

6. Opcional: especifique tags e opções avançadas para a réplica da visualização materializada. Se você não especificar um conjunto de dados para Conjunto de dados da visualização materializada local, um será criado automaticamente no mesmo projeto e região dos dados de origem e será chamado de bq_auto_generated_local_mv_dataset. Se você não especificar um nome para Nome da visualização materializada local, um será criado automaticamente no mesmo projeto e região dos dados de origem e receberá o prefixo bq_auto_generated_local_mv_.

7. Clique em Criar tabela.

Uma nova visualização materializada local é criada (se não foi especificada) e autorizada no conjunto de dados de origem. Em seguida, a réplica da visualização materializada é criada no conjunto de dados de destino.

SQL

1. Crie uma visualização materializada na tabela base do conjunto de dados que você criou. Também é possível criar a visualização materializada em um conjunto de dados diferente que esteja em uma região do Amazon S3.
2. Autorize a visualização materializada nos conjuntos de dados que contêm as tabelas de origem usadas na consulta que criou a visualização materializada.
3. Se você tiver configurado a atualização manual do cache de metadados para a tabela de origem, execute o procedimento de sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para atualizar o cache de metadados.
4. Execute o procedimento do sistema BQ.REFRESH_MATERIALIZED_VIEW para atualizar a visualização materializada.

5. Para criar réplicas de visualização materializadas, use a instrução CREATE MATERIALIZED VIEW AS REPLICA OF:

   CREATE MATERIALIZED VIEW PROJECT_ID.BQ_DATASET.REPLICA_NAME
   OPTIONS(replication_interval_seconds=REPLICATION_INTERVAL)
   AS REPLICA OF PROJECT_ID.S3_DATASET.MATERIALIZED_VIEW_NAME;

   Substitua:

   • PROJECT_ID: o nome do projeto em que você quer criar a réplica de visualização materializada. Por exemplo, myproject.
   • BQ_DATASET: o nome do conjunto de dados do BigQuery em que você quer criar a réplica de visualização materializada. Por exemplo, bq_dataset. O conjunto de dados precisa estar na região do BigQuery que é mapeada para a região da visualização materializada da origem.
   • REPLICA_NAME: o nome da réplica de visualização materializada que você quer criar, por exemplo, my_mv_replica.
   • REPLICATION_INTERVAL: especifica a frequência de replicação dos dados da visualização materializada da origem para a réplica, em segundos. Precisa ser um valor entre -60 e 3.600 incluindo estes dois valores. O padrão é 300 (5 minutos).
   • S3_DATASET: o nome do conjunto de dados que contém a visualização materializada de origem, por exemplo, s3_dataset.
   • MATERIALIZED_VIEW_NAME: o nome da visualização materializada a ser replicada, por exemplo, my_mv.

   O exemplo a seguir cria uma réplica de visualização materializada denominada mv_replica em bq_dataset:

   CREATE MATERIALIZED VIEW `myproject.bq_dataset.mv_replica`
   OPTIONS(
   replication_interval_seconds=600
   )
   AS REPLICA OF `myproject.s3_dataset.my_s3_mv`

Depois de criar a réplica de visualização materializada, o processo de replicação pesquisa a visualização materializada de origem em busca de mudanças e replica os dados para a réplica, atualizando os dados no intervalo especificado na opção replication_interval_seconds ou max_staleness. Se você consultar a réplica antes da conclusão do primeiro preenchimento, vai receber um erro backfill in progress. É possível consultar os dados na réplica de visualização materializada após a conclusão da primeira replicação.

Atualização de dados

Depois que você cria a réplica da visualização materializada, o processo de replicação pesquisa a visualização materializada de origem em busca de mudanças e replica os dados na réplica. Os dados são replicados no intervalo especificado na opção replication_interval_seconds da instrução CREATE MATERIALIZED VIEW AS REPLICA OF.

Além do intervalo de replicação, a atualização dos dados da réplica da visualização materializada também é afetada pela frequência de atualização da visualização materializada de origem e do cache de metadados da tabela do Amazon S3, do Iceberg ou do Data Cloud usado pela visualização materializada.

É possível verificar a atualização de dados da réplica da visualização materializada e os recursos em que ela se baseia usando o console Google Cloud :

• Para conferir a atualização da réplica da visualização materializada, consulte o campo Última modificação no painel Detalhes da réplica da visualização materializada.
• Para conferir a atualização da visualização materializada de origem, consulte o campo Última modificação no painel Detalhes da visualização materializada.
• Para conferir a atualização do cache de metadados da tabela do Amazon S3, Iceberg ou Data Cloud de origem, consulte o campo Inatividade máxima no painel Detalhes da visualização materializada.

Regiões compatíveis com réplicas de visualizações materializadas

Use os mapeamentos de local na seguinte tabela ao criar réplicas de visualizações materializadas:

Local da visualização materializada de origem	Local da réplica da visualização materializada
aws-us-east-1	A multirregião US ou qualquer uma destas regiões: northamerica-northeast1 northamerica-northeast2 us-central1 us-east1 us-east4 us-east5 us-south1 us-west1 us-west2 us-west3 us-west4
aws-us-west-2	A multirregião US ou qualquer uma destas regiões: northamerica-northeast1 northamerica-northeast2 us-central1 us-east1 us-east4 us-east5 us-south1 us-west1 us-west2 us-west3 us-west4
aws-eu-west-1	A multirregião EU ou qualquer uma destas regiões: europe-central2 europe-north1 europe-southwest1 europe-west1 europe-west2 europe-west3 europe-west4 europe-west6 europe-west8 europe-west9 europe-west10
aws-ap-northeast-2	Qualquer uma destas regiões: asia-east1 asia-east2 asia-northeast1 asia-northeast2 asia-northeast3 asia-south1 asia-south2 asia-southeast1
aws-ap-southeast-2	Qualquer uma destas regiões: australia-southeast1 australia-southeast2

Limitações das réplicas de visualizações materializadas

• Não é possível criar réplicas de visualizações materializadas baseadas em tabelas que adotam segurança no nível da linha ou segurança no nível da coluna.
• Não é possível usar chaves de criptografia gerenciadas pelo cliente (CMEKs) com a visualização materializada de origem ou a réplica dela.
• Só é possível criar réplicas de visualizações materializadas baseadas em tabelas que usam o armazenamento em cache de metadados.
• Só é possível criar uma réplica de visualização materializada para uma determinada visualização materializada de origem.
• Só é possível criar réplicas de visualizações materializadas autorizadas.

Preços das réplicas de visualizações materializadas

O uso de réplicas de visualizações materializadas gera custos de computação, transferência de dados de saída e armazenamento.

A seguir

• Saiba mais sobre o BigQuery ML.
• Saiba mais sobre o BigQuery Omni.
• Saber como realizar consultas.
• Saiba como configurar o VPC Service Controls para o BigQuery Omni.
• Saiba como programar e gerenciar jobs de carregamento recorrentes do Amazon S3 para o BigQuery e do Blob Storage para o BigQuery.