Carregue dados com operações entre nuvens

Como administrador ou analista do BigQuery, pode carregar dados de um contentor do Amazon Simple Storage Service (Amazon S3) ou do Azure Blob Storage para tabelas do BigQuery. Pode juntar os dados transferidos aos dados presentes nas Google Cloud regiões ou tirar partido das funcionalidades do BigQuery, como o BigQuery ML. Também pode criar réplicas de vistas materializadas de determinadas origens externas para disponibilizar esses dados no BigQuery.

Pode transferir dados para o BigQuery das seguintes formas:

• Transfira dados de ficheiros no Amazon S3 e no armazenamento de blobs do Azure para tabelas do BigQuery através da declaração LOAD DATA.

• Filtre dados de ficheiros no Amazon S3 ou no armazenamento de blobs antes de transferir os resultados para tabelas do BigQuery, usando a declaração CREATE TABLE AS SELECT. Para anexar dados à tabela de destino, use a declaraçã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 vistas materializadas de dados externos do Amazon S3, Apache Iceberg ou Salesforce Data Cloud num conjunto de dados do BigQuery para que os dados estejam disponíveis localmente no BigQuery.

Nota: se quiser transferir ficheiros grandes do contentor do Amazon Simple Storage Service (Amazon S3) ou do Armazenamento de blobs do Azure para tabelas do BigQuery de forma agendada, use o Serviço de transferência de dados do BigQuery. Se quiser ler e processar dados antes de transferir dados para tabelas do BigQuery, use a declaração CREATE TABLE AS SELECT.

Quotas e limites

Para ver informações sobre quotas e limites, consulte o artigo Quotas e limites de tarefas de consulta.

Antes de começar

Para fornecer Google Cloud acesso de leitura necessário para carregar ou filtrar dados noutras nuvens, peça ao seu administrador para criar uma associação e partilhá-la consigo. Para obter informações sobre como criar associações, consulte os artigos Ligar ao Amazon S3 ou Armazenamento de blobs.

Função necessária

Para receber as autorizações de que precisa para carregar dados através de transferências entre nuvens, peça ao seu administrador para lhe conceder a função do IAM de editor de dados do BigQuery (roles/bigquery.dataEditor) no conjunto de dados. Para mais informações sobre a atribuição de funções, consulte o artigo Faça a gestão do acesso a projetos, pastas e organizações.

Esta função predefinida contém as autorizações necessárias para carregar dados através de transferências entre nuvens. Para ver as autorizações exatas que são necessárias, expanda a secção Autorizações necessárias:

Autorizações necessárias

São necessárias as seguintes autorizações para carregar dados através de transferências entre nuvens:

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

Também pode conseguir estas autorizações com funções personalizadas ou outras funções predefinidas.

Para mais informações sobre as funções de IAM no BigQuery, consulte o artigo Funções e autorizações de IAM do BigQuery.

Preços

Os bytes transferidos entre nuvens são-lhe faturados através da declaração LOAD. Para informações sobre preços, consulte a secção Transferência de dados entre nuvens do Omni em Preços do BigQuery Omni.

A faturação é feita com base nos bytes transferidos entre nuvens através da declaração CREATE TABLE AS SELECT ou da declaração INSERT INTO SELECT, e com base na capacidade de computação.

As declarações LOAD e CREATE TABLE AS SELECT requerem espaços nas regiões do BigQuery Omni para analisar ficheiros do Amazon S3 e do Blob Storage para os carregar. Para mais informações, consulte os preços do BigQuery Omni.

Para réplicas de vistas materializadas de origens de dados externas, os custos também podem incluir os preços das vistas materializadas.

Práticas recomendadas para opções de carregamento e filtragem

• Evite carregar vários ficheiros com menos de 5 MB. Em alternativa, crie uma tabela externa para o seu ficheiro e exporte o resultado da consulta para o Amazon S3 ou Blob Storage para criar um ficheiro maior. Este método ajuda a melhorar o tempo de transferência dos seus dados.
Para informações sobre o limite do resultado máximo da consulta, consulte o artigo Tamanho máximo do resultado da consulta do BigQuery Omni.
• Se os dados de origem estiverem num ficheiro comprimido com gzip, ao criar tabelas externas, defina a opção external_table_options.compression como GZIP.

Carregue dados

Pode carregar dados para o BigQuery com a declaração LOAD DATA [INTO|OVERWRITE].

Limitações

• A associação e o conjunto de dados de destino têm de pertencer ao mesmo projeto. O carregamento de dados entre projetos não é suportado.
• LOAD DATA só é suportado quando transfere dados de um Amazon Simple Storage Service (Amazon S3) ou de um Azure Blob Storage para uma região do BigQuery colocada. Para mais informações, consulte Localizações.
  • Pode transferir dados de qualquer região US para uma região US multirregional. Também pode transferir de qualquer região EU para uma região múltipla EU.

Exemplo

Exemplo 1

O exemplo seguinte carrega um ficheiro Parquet denominado sample.parquet de um contentor do Amazon S3 para a tabela test_parquet com um esquema de deteçã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 seguinte carrega um ficheiro CSV com o prefixo sampled* do seu armazenamento de blobs para a tabela test_csv com a partição de colunas predefinida 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 seguinte substitui a tabela existente test_parquet pelos dados de um ficheiro denominado sample.parquet com um esquema de deteçã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`

Filtre dados

Pode filtrar os dados antes de os transferir para o BigQuery usando a declaração CREATE TABLE AS SELECT e a declaração INSERT INTO SELECT.

Limitações

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

• As tabelas temporárias não são suportadas.

• A transferência do formato de dados geoespaciais binário conhecido (WKB) não é suportada.

• A declaração INSERT INTO SELECT não suporta a transferência de dados para uma tabela agrupada.

• Na declaração INSERT INTO SELECT, se a tabela de destino for igual à tabela de origem na consulta SELECT, a declaração INSERT INTO SELECT não modifica nenhuma linha na tabela de destino. A tabela de destino não é modificada, uma vez que o BigQuery não consegue ler dados em várias regiões.

• CREATE TABLE AS SELECT e INSERT INTO SELECT só são suportados quando transfere dados de um Amazon S3 ou de um armazenamento de blobs para uma região do BigQuery colocada. Para mais informações, consulte Localizações.

  • Pode transferir dados de qualquer região US para uma região US multirregional. Também pode transferir de qualquer região EU para uma região múltipla EU.

Exemplo

Exemplo 1

Suponhamos que tem uma tabela do BigLake denominada myawsdataset.orders que faz referência a dados do Amazon S3. Quer transferir dados dessa tabela para uma tabela do BigQuery myotherdataset.shipments na multirregião dos EUA.

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

    bq show myawsdataset.orders;

O resultado é semelhante ao seguinte:

  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, apresente informações sobre a tabela myotherdataset.shipments:

  bq show myotherdataset.shipments

O resultado é semelhante ao seguinte. Algumas colunas são omitidas para simplificar o resultado.

  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, com a declaração CREATE TABLE AS SELECT, pode carregar seletivamente dados para a tabela myotherdataset.orders na multirregião dos EUA:

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;

Nota: se receber um erro ResourceExhausted, tente novamente após algum tempo. Se o problema persistir, pode contactar o apoio técnico.

Em seguida, pode executar uma operação de junção com a tabela criada recentemente:

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

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

Exemplo 2

O exemplo seguinte insere dados numa 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, pode inserir 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 vistas materializadas

Uma réplica de vista materializada é uma replicação de dados externos do Amazon Simple Storage Service (Amazon S3), Apache Iceberg ou Salesforce Data Cloud num conjunto de dados do BigQuery para que os dados estejam disponíveis localmente no BigQuery. Isto pode ajudar a evitar custos de saída de dados e melhorar o desempenho das consultas. O BigQuery permite-lhe criar vistas materializadas em tabelas com a cache de metadados do BigLake ativada sobre dados do Amazon Simple Storage Service (Amazon S3), Apache Iceberg ou Salesforce Data Cloud.

Uma réplica da vista materializada permite-lhe usar os dados da vista materializada do Amazon S3, Iceberg ou Data Cloud em consultas, evitando os custos de saída de dados e melhorando o desempenho das consultas. Uma réplica de vista materializada faz isto replicando os dados do Amazon S3, Iceberg ou Data Cloud para um conjunto de dados numa região do BigQuery suportada, para que os dados estejam 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.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

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.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

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. Certifique-se de que tem as autorizações de gestão de identidade e de acesso (IAM) necessárias para realizar as tarefas nesta secção.

Funções necessárias

Para receber as autorizações de que precisa para realizar as tarefas nesta secção, peça ao seu administrador para lhe conceder a função de IAM administrador do BigQuery (roles/bigquery.admin). Para mais informações sobre a atribuição de funções, consulte o artigo Faça a gestão do acesso a projetos, pastas e organizações.

Esta função predefinida contém as autorizações necessárias para realizar as tarefas nesta secção. Para ver as autorizações exatas que são necessárias, expanda a secção Autorizações necessárias:

Autorizações necessárias

São necessárias as seguintes autorizações para realizar as tarefas nesta secção:

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

Também pode conseguir estas autorizações com funções personalizadas ou outras funções predefinidas.

Para mais informações acerca do IAM do BigQuery, consulte o artigo Funções e autorizações do IAM do BigQuery.

Prepare um conjunto de dados para réplicas de vistas materializadas

Antes de criar uma réplica de vista materializada, tem de concluir as seguintes tarefas:

1. Crie um conjunto de dados numa região que suporte o Amazon S3
2. Crie uma tabela de origem no conjunto de dados que criou no passo anterior. A tabela de origem pode ser qualquer um dos seguintes tipos de tabelas:
   • Uma tabela do BigLake do Amazon S3 com a memorização em cache de metadados ativada e que não usa um formato de ficheiro Iceberg.
   • Uma tabela externa do Apache Iceberg.
   • Uma tabela do Data Cloud.

Crie réplicas de vistas materializadas

Selecione uma das seguintes opções:

Consola

1. Na Google Cloud consola, aceda à página BigQuery.

   Aceda ao BigQuery

2. No painel Explorador, navegue para o projeto e o conjunto de dados onde quer criar a réplica da vista materializada e, de seguida, clique em Ações de visualização > Criar tabela.

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

   1. Em Criar tabela a partir de, selecione Tabela/vista existente.
   2. Para Projeto, introduza o projeto onde a tabela ou a vista de origem está localizada.
   3. Para Conjunto de dados, introduza o conjunto de dados onde se encontra a tabela ou a vista de origem.
   4. Para Vista, introduza a tabela de origem ou a vista que está a replicar. Se escolher uma visualização de propriedade, tem de ser uma visualização de propriedade autorizada ou, caso contrário, todas as tabelas usadas para gerar essa visualização de propriedade têm de estar localizadas no conjunto de dados da visualização de propriedade.

4. Opcional: em Atraso máximo da vista materializada local, introduza um max_staleness valor para a vista materializada local.

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

   1. Para Projeto, introduza o projeto no qual quer criar a réplica da vista materializada.
   2. Para Conjunto de dados, introduza o conjunto de dados no qual quer criar a réplica da vista materializada.
   3. Em Nome da vista materializada da réplica, introduza um nome para a réplica.

6. Opcional: especifique etiquetas e opções avançadas para a réplica da vista materializada. Se não especificar um conjunto de dados para o conjunto de dados de visualização materializada local, é criado automaticamente um no mesmo projeto e região que os dados de origem, e é denominado bq_auto_generated_local_mv_dataset. Se não especificar um nome para Local Materialized View Name, é criado automaticamente um no mesmo projeto e região que os dados de origem, e é-lhe atribuído o prefixo bq_auto_generated_local_mv_.

7. Clique em Criar tabela.

É criada uma nova vista materializada local (se não tiver sido especificada) e autorizada no conjunto de dados de origem. Em seguida, a réplica da vista materializada é criada no conjunto de dados de destino.

SQL

1. Crie uma vista materializada sobre a tabela base no conjunto de dados que criou. Também pode criar a vista materializada num conjunto de dados diferente que esteja numa região do Amazon S3.
2. Autorize a vista materializada nos conjuntos de dados que contêm as tabelas de origem usadas na consulta que criou a vista materializada.
3. Se configurou a atualização manual da cache de metadados para a tabela de origem, execute o procedimento do sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para atualizar a cache de metadados.
4. Execute o BQ.REFRESH_MATERIALIZED_VIEW procedimento do sistema para atualizar a vista materializada.

5. Crie réplicas de visualização materializada com a declaraçã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 o seguinte:

   • PROJECT_ID: o nome do projeto no qual quer criar a réplica da vista materializada, por exemplo, myproject.
   • BQ_DATASET: o nome do conjunto de dados do BigQuery no qual quer criar a réplica da vista materializada, por exemplo, bq_dataset. O conjunto de dados tem de estar na região do BigQuery que é mapeada para a região da visualização materializada de origem.
   • REPLICA_NAME: o nome da réplica da vista materializada que quer criar, por exemplo, my_mv_replica.
   • REPLICATION_INTERVAL: especifica a frequência com que os dados da vista materializada de origem são replicados para a réplica, em segundos. Tem de ser um valor entre 60 e 3600, inclusive. A predefinição é 300 (5 minutos).
   • S3_DATASET: o nome do conjunto de dados que contém a vista materializada de origem, por exemplo, s3_dataset.
   • MATERIALIZED_VIEW_NAME: o nome da vista materializada a replicar, por exemplo, my_mv.

   O exemplo seguinte cria uma réplica da vista 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 da vista materializada, o processo de replicação consulta a vista materializada de origem para verificar se existem alterações e replica os dados para a réplica da vista materializada, atualizando os dados no intervalo especificado na opção replication_interval_seconds ou max_staleness. Se consultar a réplica antes da conclusão do primeiro preenchimento, recebe um erro backfill in progress. Pode consultar os dados na réplica da vista materializada após a conclusão da primeira replicação.

Atualidade dos dados

Depois de criar a réplica da vista materializada, o processo de replicação sonda a vista materializada de origem para verificar se existem alterações e replica os dados para a réplica da vista materializada. Os dados são replicados no intervalo especificado na opção replication_interval_seconds da declaração CREATE MATERIALIZED VIEW AS REPLICA OF.

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

Pode verificar a atualização dos dados da réplica da vista materializada e dos recursos nos quais se baseia através da consola: Google Cloud

• Para verificar a atualização da réplica da vista materializada, consulte o campo Última modificação no painel Detalhes da réplica da vista materializada.
• Para ver a atualização da vista materializada de origem, consulte o campo Última modificação no painel Detalhes da vista materializada.
• Para a origem Amazon S3, Iceberg ou a atualidade da cache de metadados da tabela do Data Cloud, consulte o campo Max staleness no painel Detalhes da vista materializada.

Regiões de réplica de vistas materializadas suportadas

Use os mapeamentos de localizações na tabela seguinte quando criar réplicas de vistas materializadas:

Localização da vista materializada de origem	Localização da réplica da vista materializada
aws-us-east-1	A US região múltipla> ou qualquer uma das seguintes 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 US região múltipla> ou qualquer uma das seguintes 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 EU região múltipla> ou qualquer uma das seguintes 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 das seguintes regiões: asia-east1 asia-east2 asia-northeast1 asia-northeast2 asia-northeast3 asia-south1 asia-south2 asia-southeast1
aws-ap-southeast-2	Qualquer uma das seguintes regiões: australia-southeast1 australia-southeast2

Limitações das réplicas de vistas materializadas

• Não pode criar réplicas de visualização materializada para visualizações materializadas que se baseiam em tabelas que usam segurança ao nível da linha ou segurança ao nível da coluna.
• Não pode usar chaves de encriptação geridas pelo cliente (CMEKs) com a vista materializada de origem nem com a réplica da vista materializada.
• Só pode criar réplicas de visualização materializada para visualizações materializadas que se baseiam em tabelas que usam a colocação em cache de metadados.
• Só pode criar uma réplica de vista materializada para uma determinada vista materializada de origem.
• Só pode criar réplicas de vistas materializadas para vistas materializadas autorizadas.

Preços das réplicas de vistas materializadas

A utilização de réplicas de vistas materializadas incorre em custos de computação, transferência de dados de saída e armazenamento.

O que se segue?

• Saiba mais acerca do BigQuery ML.
• Saiba mais acerca do BigQuery Omni.
• Saiba como executar consultas.
• Saiba como configurar o VPC Service Controls para o BigQuery Omni.
• Saiba como agendar e gerir tarefas de carregamento recorrentes do Amazon S3 para o BigQuery e do Blob Storage para o BigQuery.