Crie tabelas externas do Cloud Storage

O BigQuery suporta a consulta de dados do Cloud Storage nos seguintes formatos:

  • Valores separados por vírgulas (.csv)
  • JSON (delimitado por newline)
  • Avro
  • ORC
  • Parquet
  • Exportações do Datastore
  • Exportações do Firestore

O BigQuery suporta a consulta de dados do Cloud Storage a partir destas classes de armazenamento:

  • Standard
  • Nearline
  • Coldline
  • Arquivar

Para consultar uma tabela externa do Cloud Storage, tem de ter autorizações na tabela externa e nos ficheiros do Cloud Storage. Recomendamos que use uma tabela do BigLake, se possível. As tabelas do BigLake oferecem delegação de acesso, pelo que só precisa de autorizações na tabela do BigLake para consultar os dados do Cloud Storage.

Certifique-se de que considera a localização do seu conjunto de dados e contentor do Cloud Storage quando consulta dados armazenados no Cloud Storage.

Antes de começar

Conceda funções de gestão de identidade e acesso (IAM) que dão aos utilizadores as autorizações necessárias para realizar cada tarefa neste documento. As autorizações necessárias para realizar uma tarefa (se aplicável) estão listadas na secção "Autorizações necessárias" da tarefa.

Funções necessárias

Para criar uma tabela externa, precisa da autorização de gestão de identidade e de acesso (IAM) do BigQuery.bigquery.tables.create

Cada uma das seguintes funções de gestão de identidade e acesso predefinidas inclui esta autorização:

  • Editor de dados do BigQuery (roles/bigquery.dataEditor)
  • Proprietário de dados do BigQuery (roles/bigquery.dataOwner)
  • Administrador do BigQuery (roles/bigquery.admin)

Também precisa das seguintes autorizações para aceder ao contentor do Cloud Storage que contém os seus dados:

  • storage.buckets.get
  • storage.objects.get
  • storage.objects.list (obrigatório se estiver a usar um caráter universal de URI)

A função predefinida de gestão de identidade e de acesso (IAM) de administrador do Cloud Storage (roles/storage.admin) inclui estas autorizações.

Se não for um principal em nenhuma destas funções, peça ao seu administrador para lhe conceder acesso ou criar a tabela externa por si.

Para mais informações sobre as funções e as autorizações de gestão de identidades e acessos no BigQuery, consulte o artigo Funções e autorizações predefinidas.

Âmbitos de acesso para instâncias do Compute Engine

Se, a partir de uma instância do Compute Engine, precisar de consultar uma tabela externa associada a uma origem do Cloud Storage, a instância tem de ter, pelo menos, o âmbito de acesso de leitura do Cloud Storage (https://www.googleapis.com/auth/devstorage.read_only).

Os âmbitos controlam o acesso da instância do Compute Engine aos Google Cloud produtos, incluindo o Cloud Storage. As aplicações executadas na instância usam a conta de serviço anexada à instância para chamar as APIs do Google Cloud .

Se configurar uma instância do Compute Engine para ser executada como a conta de serviço predefinida do Compute Engine, a instância recebe, por predefinição, vários âmbitos predefinidos, incluindo o âmbito https://www.googleapis.com/auth/devstorage.read_only.

Se, em alternativa, configurar a instância com uma conta de serviço personalizada, certifique-se de que concede explicitamente o âmbito https://www.googleapis.com/auth/devstorage.read_only à instância.

Para ver informações sobre a aplicação de âmbitos a uma instância do Compute Engine, consulte o artigo Alterar a conta de serviço e os âmbitos de acesso de uma instância. Para mais informações sobre as contas de serviço do Compute Engine, consulte o artigo Contas de serviço.

Crie tabelas externas em dados não particionados

Pode criar uma tabela permanente associada à sua origem de dados externa:

Selecione uma das seguintes opções:

Consola

  1. Aceda à página do BigQuery.

    Aceda ao BigQuery

  2. No painel Explorador, expanda o projeto e selecione um conjunto de dados.

  3. Expanda a opção Ações e clique em Criar tabela.

  4. Na secção Origem, especifique os seguintes detalhes:

    1. Em Criar tabela a partir de, selecione Google Cloud Storage

    2. Para Selecionar ficheiro do contentor do GCS ou usar um padrão de URI, procure selecione um contentor e um ficheiro a usar ou escreva o caminho no formato gs://bucket_name/[folder_name/]file_name.

      Não pode especificar vários URIs na consola Google Cloud , mas pode selecionar vários ficheiros especificando um caráter universal (*). Por exemplo, gs://mybucket/file_name*. Para mais informações, consulte o artigo Suporte de carateres universais para URIs do Cloud Storage.

      O contentor do Cloud Storage tem de estar na mesma localização que o conjunto de dados que contém a tabela que está a criar.

    3. Em Formato de ficheiro, selecione o formato que corresponde ao seu ficheiro.

  5. Na secção Destino, especifique os seguintes detalhes:

    1. Para Projeto, escolha o projeto no qual criar a tabela.

    2. Para Conjunto de dados, escolha o conjunto de dados no qual criar a tabela.

    3. Em Tabela, introduza o nome da tabela que está a criar.

    4. Para Tipo de tabela, selecione Tabela externa.

  6. Na secção Esquema, pode ativar a deteção automática de esquemas ou especificar manualmente um esquema se tiver um ficheiro de origem. Se não tiver um ficheiro de origem, tem de especificar manualmente um esquema.

    • Para ativar a deteção automática do esquema, selecione a opção Deteção automática.

    • Para especificar manualmente um esquema, desmarque a opção Deteção automática. Ative a opção Editar como texto e introduza o esquema de tabela como uma matriz JSON.

  7. Para ignorar linhas com valores de colunas adicionais que não correspondem ao esquema, expanda a secção Opções avançadas e selecione Valores desconhecidos.

  8. Clique em Criar tabela.

Depois de criar a tabela permanente, pode executar uma consulta na tabela como se fosse uma tabela nativa do BigQuery. Depois de a consulta ser concluída, pode exportar os resultados como ficheiros CSV ou JSON, guardar os resultados como uma tabela ou guardar os resultados no Google Sheets.

SQL

Pode criar uma tabela externa permanente executando a declaração DDL CREATE EXTERNAL TABLE. Pode especificar o esquema explicitamente ou usar a deteção automática de esquemas para inferir o esquema dos dados externos.

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

    Aceda ao BigQuery

  2. No editor de consultas, introduza a seguinte declaração:

    CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  OPTIONS (
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'[,...]]
    );

    Substitua o seguinte:

    • PROJECT_ID: o nome do seu projeto no qual quer criar a tabela, por exemplo, myproject
    • DATASET: o nome do conjunto de dados do BigQuery no qual quer criar a tabela, por exemplo, mydataset
    • EXTERNAL_TABLE_NAME: o nome da tabela que quer criar, por exemplo, mytable
    • TABLE_FORMAT: o formato da tabela que quer criar, por exemplo, PARQUET
    • BUCKET_PATH: o caminho para o contentor do Cloud Storage que contém os dados da tabela externa, no formato ['gs://bucket_name/[folder_name/]file_name'].

      Pode selecionar vários ficheiros do contentor especificando um caráter universal de asterisco (*) no caminho. Por exemplo, ['gs://mybucket/file_name*']. Para mais informações, consulte o artigo Suporte de carateres universais para URIs do Cloud Storage.

      Pode especificar vários contentores para a opção uris fornecendo vários caminhos.

      Os exemplos seguintes mostram valores uris válidos:

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      Quando especifica valores uris que segmentam vários ficheiros, todos esses ficheiros têm de partilhar um esquema compatível.

      Para mais informações sobre a utilização de URIs do Cloud Storage no BigQuery, consulte o artigo Caminho de recurso do Cloud Storage.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, consulte o artigo Execute uma consulta interativa.

Exemplos

O exemplo seguinte usa a deteção automática do esquema para criar uma tabela externa denominada sales que está associada a um ficheiro CSV armazenado no Cloud Storage:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
  OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']);

O exemplo seguinte especifica um esquema explicitamente e ignora a primeira linha no ficheiro CSV:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales (
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
) OPTIONS (
    format = 'CSV',
    uris = ['gs://mybucket/sales.csv'],
    skip_leading_rows = 1);

bq

Para criar uma tabela externa, use o comando bq mk com a flag --external_table_definition. Esta flag contém um caminho para um ficheiro de definição de tabela ou uma definição de tabela inline.

Opção 1: ficheiro de definição da tabela

Use o comando bq mkdef para criar um ficheiro de definição de tabela e, em seguida, transmita o caminho do ficheiro para o comando bq mk da seguinte forma:

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_PATH > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Substitua o seguinte:

  • SOURCE_FORMAT: o formato da origem de dados externa. Por exemplo, CSV.

  • BUCKET_PATH: o caminho para o contentor do Cloud Storage que contém os dados da tabela, no formato gs://bucket_name/[folder_name/]file_pattern.

    Pode selecionar vários ficheiros do contentor especificando um caráter universal (*) no file_pattern. Por exemplo, gs://mybucket/file00*.parquet. Para mais informações, consulte o artigo Suporte de carateres universais para URIs do Cloud Storage.

    Pode especificar vários contentores para a opção uris fornecendo vários caminhos.

    Os exemplos seguintes mostram valores uris válidos:

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*, gs://bucket1/path1/*

    Quando especifica valores uris que segmentam vários ficheiros, todos esses ficheiros têm de partilhar um esquema compatível.

    Para mais informações sobre a utilização de URIs do Cloud Storage no BigQuery, consulte o caminho de recurso do Cloud Storage.

  • DEFINITION_FILE: o caminho para o ficheiro de definição da tabela no seu computador local.

  • DATASET_NAME: o nome do conjunto de dados que contém a tabela.

  • TABLE_NAME: o nome da tabela que está a criar.

  • SCHEMA: especifica um caminho para um ficheiro de esquema JSON, ou especifica o esquema no formulário field:data_type,field:data_type,....

Exemplo:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Para usar a deteção automática de esquemas, defina a flag --autodetect=true no comando mkdef e omita o esquema:

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

Opção 2: definição de tabela inline

Em vez de criar um ficheiro de definição de tabela, pode transmitir a definição de tabela diretamente para o comando bq mk:

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_PATH \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Substitua o seguinte:

  • SOURCE_FORMAT: o formato da origem de dados externa

    Por exemplo, CSV.

  • BUCKET_PATH: o caminho para o contentor do Cloud Storage que contém os dados da tabela, no formato gs://bucket_name/[folder_name/]file_pattern.

    Pode selecionar vários ficheiros do contentor especificando um caráter universal (*) no file_pattern. Por exemplo, gs://mybucket/file00*.parquet. Para mais informações, consulte o artigo Suporte de carateres universais para URIs do Cloud Storage.

    Pode especificar vários contentores para a opção uris fornecendo vários caminhos.

    Os exemplos seguintes mostram valores uris válidos:

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*, gs://bucket1/path1/*

    Quando especifica valores uris que segmentam vários ficheiros, todos esses ficheiros têm de partilhar um esquema compatível.

    Para mais informações sobre a utilização de URIs do Cloud Storage no BigQuery, consulte o caminho de recurso do Cloud Storage.

  • DATASET_NAME: o nome do conjunto de dados que contém a tabela.

  • TABLE_NAME: o nome da tabela que está a criar.

  • SCHEMA: especifica um caminho para um ficheiro de esquema JSON, ou especifica o esquema no formulário field:data_type,field:data_type,.... Para usar a deteção automática do esquema, omita este argumento.

Exemplo:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

Chame o método tables.insert da API e crie um ExternalDataConfiguration no recurso Table que transmite.

Especifique a propriedade schema ou defina a propriedade autodetect como true para ativar a deteção automática do esquema para origens de dados suportadas.

Java

Antes de experimentar este exemplo, siga as Javainstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Java BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure a autenticação para bibliotecas de cliente. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

Antes de experimentar este exemplo, siga as Node.jsinstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Node.js BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure a autenticação para bibliotecas de cliente. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

Antes de experimentar este exemplo, siga as Pythoninstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Python BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure a autenticação para bibliotecas de cliente. 

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "your-project.your_dataset.your_table_name"

# TODO(developer): Set the external source format of your table.
# Note that the set of allowed values for external data sources is
# different than the set used for loading data (see :class:`~google.cloud.bigquery.job.SourceFormat`).
external_source_format = "AVRO"

# TODO(developer): Set the source_uris to point to your data in Google Cloud
source_uris = [
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/a-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/c-twitter.avro",
]

# Create ExternalConfig object with external source format
external_config = bigquery.ExternalConfig(external_source_format)
# Set source_uris that point to your data in Google Cloud
external_config.source_uris = source_uris

# TODO(developer) You have the option to set a reference_file_schema_uri, which points to
# a reference file for the table schema
reference_file_schema_uri = "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro"

external_config.reference_file_schema_uri = reference_file_schema_uri

table = bigquery.Table(table_id)
# Set the external data configuration of the table
table.external_data_configuration = external_config
table = client.create_table(table)  # Make an API request.

print(
    f"Created table with external source format {table.external_data_configuration.source_format}"
)

Crie tabelas externas em dados particionados

Pode criar uma tabela externa para dados particionados do Hive que residam no Cloud Storage. Depois de criar uma tabela particionada externamente, não pode alterar a chave de partição. Tem de recriar a tabela para alterar a chave de partição.

Para criar uma tabela externa para dados particionados do Hive, escolha uma das seguintes opções:

Consola

  1. Na Google Cloud consola, aceda ao BigQuery.

    Aceda ao BigQuery

  2. No painel Explorador, expanda o projeto e selecione um conjunto de dados.
  3. Clique em Ver ações, e, de seguida, clique em Criar tabela. É apresentado o painel Criar tabela.
  4. Na secção Origem, especifique os seguintes detalhes:
    1. Em Criar tabela a partir de, selecione Google Cloud Storage.
    2. Para Selecionar ficheiro do contentor do Cloud Storage, introduza o caminho para a pasta do Cloud Storage, usando caracteres universais. Por exemplo, my_bucket/my_files*. O contentor do Cloud Storage tem de estar na mesma localização que o conjunto de dados que contém a tabela que quer criar, acrescentar ou substituir.
    3. Na lista Formato de ficheiro, selecione o tipo de ficheiro.
    4. Selecione a caixa de verificação Particionamento de dados de origem e, de seguida, em Selecionar prefixo do URI de origem, introduza o prefixo do URI do Cloud Storage. Por exemplo, gs://my_bucket/my_files.
    5. Na secção Modo de inferência de partição, selecione uma das seguintes opções:
      • Inferir automaticamente tipos: defina o modo de deteção do esquema de partição para AUTO.
      • Todas as colunas são strings: defina o modo de deteção do esquema de partição como STRINGS.
      • Fornecer as minhas: defina o modo de deteção do esquema de partição como CUSTOM e introduza manualmente as informações do esquema para as chaves de partição. Para mais informações, consulte o artigo Forneça um esquema de chave de partição personalizado.
    6. Opcional: para exigir um filtro de partição em todas as consultas para esta tabela, selecione a caixa de verificação Exigir filtro de partição. A exigência de um filtro de partição pode reduzir o custo e melhorar o desempenho. Para mais informações, consulte o artigo Exigir filtros de predicados em chaves de partição nas consultas.
  5. Na secção Destino, especifique os seguintes detalhes:
    1. Para Projeto, selecione o projeto no qual quer criar a tabela.
    2. Para Conjunto de dados, selecione o conjunto de dados no qual quer criar a tabela.
    3. Para Tabela, introduza o nome da tabela que quer criar.
    4. Para Tipo de tabela, selecione Tabela externa.
  6. Na secção Esquema, introduza a definição do esquema.
  7. Para ativar a deteção automática do esquema, selecione Deteção automática.
  8. Para ignorar linhas com valores de colunas adicionais que não correspondem ao esquema, expanda a secção Opções avançadas e selecione Valores desconhecidos.
  9. Clique em Criar tabela.

SQL

Use a CREATE EXTERNAL TABLE declaração DDL.

O exemplo seguinte usa a deteção automática de chaves de partição do Hive:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Substitua o seguinte:

  • SOURCE_FORMAT: o formato da origem de dados externa, como PARQUET
  • GCS_URIS: o caminho para a pasta do Cloud Storage, usando o formato de caráter universal
  • GCS_URI_SHARED_PREFIX: o prefixo do URI de origem sem o caráter universal
  • BOOLEAN: se deve exigir um filtro de predicado no momento da consulta. Esta flag é opcional. O valor predefinido é false.

O exemplo seguinte usa chaves e tipos de partições do Hive personalizados, listando-os na cláusula WITH PARTITION COLUMNS:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS (PARTITION_COLUMN_LIST)
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Substitua o seguinte:

  • PARTITION_COLUMN_LIST: uma lista de colunas seguindo a mesma ordem no caminho da pasta do Cloud Storage, no formato:
KEY1 TYPE1, KEY2 TYPE2

O exemplo seguinte cria uma tabela particionada externamente. Usa a deteção automática de esquemas para detetar o esquema de ficheiros e a estrutura de partição do Hive. Se o caminho externo for gs://bucket/path/field_1=first/field_2=1/data.parquet, as colunas de partição são detetadas como field_1 (STRING) e field_2 (INT64).

CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

O exemplo seguinte cria uma tabela particionada externamente especificando explicitamente as colunas de partição. Este exemplo pressupõe que o caminho do ficheiro externo tem o padrão gs://bucket/path/field_1=first/field_2=1/data.parquet.

CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64)
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

bq

Primeiro, use o comando bq mkdef para criar um ficheiro de definição de tabela:

bq mkdef \
--source_format=SOURCE_FORMAT \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
 GCS_URIS > DEFINITION_FILE

Substitua o seguinte:

  • SOURCE_FORMAT: o formato da origem de dados externa. Por exemplo, CSV.
  • PARTITIONING_MODE: o modo de partição do Hive. Use um dos seguintes valores:
    • AUTO: detetar automaticamente os nomes e os tipos das chaves.
    • STRINGS: converta automaticamente os nomes das chaves em strings.
    • CUSTOM: codifique o esquema de chaves no prefixo do URI de origem.
  • GCS_URI_SHARED_PREFIX: o prefixo do URI de origem.
  • BOOLEAN: especifica se é necessário um filtro de predicado no momento da consulta. Esta flag é opcional. O valor predefinido é false.
  • GCS_URIS: o caminho para a pasta do Cloud Storage, usando o formato de carateres universais.
  • DEFINITION_FILE: o caminho para o ficheiro de definição da tabela no seu computador local.

Se PARTITIONING_MODE for CUSTOM, inclua o esquema da chave de partição no prefixo do URI de origem, usando o seguinte formato:

--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Depois de criar o ficheiro de definição da tabela, use o comando bq mk para criar a tabela externa:

bq mk --external_table_definition=DEFINITION_FILE \
DATASET_NAME.TABLE_NAME \
SCHEMA

Substitua o seguinte:

  • DEFINITION_FILE: o caminho para o ficheiro de definição da tabela.
  • DATASET_NAME: o nome do conjunto de dados que contém a tabela.
  • TABLE_NAME: o nome da tabela que está a criar.
  • SCHEMA: especifica um caminho para um ficheiro de esquema JSON, ou especifica o esquema no formulário field:data_type,field:data_type,.... Para usar a deteção automática do esquema, omita este argumento.

Exemplos

O exemplo seguinte usa o modo de partição do Hive AUTO:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

O exemplo seguinte usa o modo de partição do Hive STRING:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=STRING \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

O exemplo seguinte usa o modo de partição do Hive CUSTOM:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=CUSTOM \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING} \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

Para definir a partição do Hive através da API BigQuery, inclua um objeto hivePartitioningOptions no objeto ExternalDataConfiguration quando criar o ficheiro de definição da tabela.

Se definir o campo hivePartitioningOptions.mode como CUSTOM, tem de codificar o esquema da chave de partição no campo hivePartitioningOptions.sourceUriPrefix da seguinte forma: gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Para aplicar a utilização de um filtro de predicado no momento da consulta, defina o campo hivePartitioningOptions.requirePartitionFilter como true.

Java

Antes de experimentar este exemplo, siga as Javainstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Java BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure a autenticação para bibliotecas de cliente. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

  public static void setHivePartitioningOptions(
      String datasetName, String tableName, String sourceUriPrefix, String sourceUri) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}

Consultar tabelas externas

Para mais informações, consulte o artigo Consultar dados do Cloud Storage em tabelas externas.

Atualize tabelas externas para o BigLake

Pode atualizar tabelas baseadas no Cloud Storage para tabelas BigLake associando a tabela externa a uma ligação. Se quiser usar a colocação em cache de metadados com a tabela BigLake, pode especificar as definições para esta ao mesmo tempo. Para obter detalhes da tabela, como o formato de origem e o URI de origem, consulte o artigo Obtenha informações da tabela.

Para atualizar uma tabela externa para uma tabela do BigLake, selecione uma das seguintes opções:

SQL

Use a declaração DDL CREATE OR REPLACE EXTERNAL TABLE para atualizar uma tabela:

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

    Aceda ao BigQuery

  2. No editor de consultas, introduza a seguinte declaração:

    CREATE OR REPLACE EXTERNAL TABLE
  `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  WITH CONNECTION {`REGION.CONNECTION_ID` | DEFAULT}
  OPTIONS(
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'],
    max_staleness = STALENESS_INTERVAL,
    metadata_cache_mode = 'CACHE_MODE'
    );

    Substitua o seguinte:

    • PROJECT_ID: o nome do projeto que contém a tabela
    • DATASET: o nome do conjunto de dados que contém a tabela
    • EXTERNAL_TABLE_NAME: o nome da tabela
    • REGION: a região que contém a ligação
    • CONNECTION_ID: o nome da ligação a usar

      Para usar uma associação predefinida, especifique DEFAULT em vez da string de associação que contém REGION.CONNECTION_ID.

    • TABLE_FORMAT: o formato usado pela tabela

      Não pode alterar esta opção quando atualiza a tabela.

    • BUCKET_PATH: o caminho para o contentor do Cloud Storage que contém os dados da tabela externa, no formato ['gs://bucket_name/[folder_name/]file_name'].

      Pode selecionar vários ficheiros do contentor especificando um caráter universal de asterisco (*) no caminho. Por exemplo, ['gs://mybucket/file_name*']. Para mais informações, consulte o artigo Suporte de carateres universais para URIs do Cloud Storage.

      Pode especificar vários contentores para a opção uris fornecendo vários caminhos.

      Os exemplos seguintes mostram valores uris válidos:

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      Quando especifica valores uris que segmentam vários ficheiros, todos esses ficheiros têm de partilhar um esquema compatível.

      Para mais informações sobre a utilização de URIs do Cloud Storage no BigQuery, consulte o artigo Caminho de recurso do Cloud Storage.

    • STALENESS_INTERVAL: especifica se os metadados em cache são usados por operações na tabela e qual a atualidade dos metadados em cache para que a operação os use

      Para mais informações sobre considerações relativas ao armazenamento em cache de metadados, consulte o artigo Armazenamento em cache de metadados para desempenho.

      Para desativar a colocação em cache de metadados, especifique 0. Esta é a predefinição.

      Para ativar o armazenamento em cache de metadados, especifique um valor literal de intervalo entre 30 minutos e 7 dias. Por exemplo, especifique INTERVAL 4 HOUR para um intervalo de desatualização de 4 horas. Com este valor, as operações na tabela usam metadados em cache se tiverem sido atualizados nas últimas 4 horas. Se os metadados em cache forem mais antigos, a operação obtém os metadados do Cloud Storage.

    • CACHE_MODE: especifica se a cache de metadados é atualizada automaticamente ou manualmente

      Para mais informações sobre considerações de colocação em cache de metadados, consulte Colocação em cache de metadados para desempenho.

      Definido como AUTOMATIC para que a cache de metadados seja atualizada a um intervalo definido pelo sistema, normalmente entre 30 e 60 minutos.

      Defina como MANUAL se quiser atualizar a cache de metadados de acordo com uma programação que determinar. Neste caso, pode chamar o procedimento do sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para atualizar a cache.

      Tem de definir CACHE_MODE se STALENESS_INTERVAL estiver definido como um valor superior a 0.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, consulte o artigo Execute uma consulta interativa.

bq

Use os comandos bq mkdef e bq update para atualizar uma tabela:

  1. Gere uma definição de tabela externa, que descreve os aspetos da tabela a alterar:

    bq mkdef --connection_id=PROJECT_ID.REGION.CONNECTION_ID \
--source_format=TABLE_FORMAT \
--metadata_cache_mode=CACHE_MODE \
"BUCKET_PATH" > /tmp/DEFINITION_FILE

    Substitua o seguinte:

    • PROJECT_ID: o nome do projeto que contém a associação
    • REGION: a região que contém a ligação
    • CONNECTION_ID: o nome da associação a usar
    • TABLE_FORMAT: o formato usado pela tabela. Não pode alterar esta opção quando atualiza a tabela.

    • CACHE_MODE: especifica se a cache de metadados é atualizada automaticamente ou manualmente. Para mais informações sobre considerações relativas ao armazenamento em cache de metadados, consulte Armazenamento em cache de metadados para desempenho.

      Defina como AUTOMATIC para que a cache de metadados seja atualizada a um intervalo definido pelo sistema, normalmente entre 30 e 60 minutos.

      Defina como MANUAL se quiser atualizar a cache de metadados de acordo com um horário que determinar. Neste caso, pode chamar o procedimento do sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para atualizar a cache.

      Tem de definir CACHE_MODE se STALENESS_INTERVAL estiver definido como um valor superior a 0.

    • BUCKET_PATH: o caminho para o contentor de armazenamento na nuvem que contém os dados da tabela externa, no formato gs://bucket_name/[folder_name/]file_name.

      Pode limitar os ficheiros selecionados do contentor especificando um caráter universal (*) no caminho. Por exemplo, gs://mybucket/file_name*. Para mais informações, consulte o artigo Suporte de carateres universais para URIs do Cloud Storage.

      Pode especificar vários contentores para a opção uris fornecendo vários caminhos.

      Os exemplos seguintes mostram valores uris válidos:

      • gs://bucket/path1/myfile.csv
      • gs://bucket/path1/*.csv
      • gs://bucket/path1/*,gs://bucket/path2/file00*

      Quando especifica valores uris que segmentam vários ficheiros, todos esses ficheiros têm de partilhar um esquema compatível.

      Para mais informações sobre a utilização de URIs do Cloud Storage no BigQuery, consulte o caminho de recurso do Cloud Storage.

    • DEFINITION_FILE: o nome do ficheiro de definição da tabela que está a criar.

  2. Atualize a tabela com a nova definição de tabela externa:

    bq update --max_staleness=STALENESS_INTERVAL \
--external_table_definition=/tmp/DEFINITION_FILE \
PROJECT_ID:DATASET.EXTERNAL_TABLE_NAME

    Substitua o seguinte:

    • STALENESS_INTERVAL: especifica se as operações contra a tabela usam metadados em cache e qual a antiguidade máxima dos metadados em cache para que a operação os use. Para mais informações sobre considerações de colocação em cache de metadados, consulte o artigo Colocação em cache de metadados para desempenho.

      Para desativar a colocação em cache de metadados, especifique 0. Esta é a predefinição.

      Para ativar o armazenamento em cache de metadados, especifique um valor de intervalo entre 30 minutos e 7 dias, usando o formato Y-M D H:M:S descrito na documentação do tipo de dados INTERVAL. Por exemplo, especifique 0-0 0 4:0:0 para um intervalo de desatualização de 4 horas. Com este valor, as operações na tabela usam metadados em cache se tiverem sido atualizados nas últimas 4 horas. Se os metadados em cache forem mais antigos, a operação obtém os metadados do Cloud Storage.

    • DEFINITION_FILE: o nome do ficheiro de definição da tabela que criou ou atualizou.

    • PROJECT_ID: o nome do projeto que contém a tabela

    • DATASET: o nome do conjunto de dados que contém a tabela

    • EXTERNAL_TABLE_NAME: o nome da tabela

Caminho do recurso do Cloud Storage

Quando cria uma tabela externa com base numa origem de dados do Cloud Storage, tem de indicar o caminho para os dados.

O caminho de recurso do Cloud Storage contém o nome do contentor e o objeto (nome do ficheiro). Por exemplo, se o contentor do Cloud Storage se chamar mybucket e o ficheiro de dados se chamar myfile.csv, o caminho do recurso seria gs://mybucket/myfile.csv.

O BigQuery não suporta caminhos de recursos do Cloud Storage que incluam várias barras invertidas consecutivas após a barra invertida dupla inicial. Os nomes de objetos do Cloud Storage podem conter vários carateres de barra (/) consecutivos. No entanto, o BigQuery converte várias barras invertidas consecutivas numa única barra invertida. Por exemplo, o seguinte caminho do recurso, embora seja válido no Cloud Storage, não funciona no BigQuery: gs://bucket/my//object//name.

Para obter o caminho do recurso do Cloud Storage:

  1. Abra a consola do Cloud Storage.

    Consola do Cloud Storage

  2. Procure a localização do objeto (ficheiro) que contém os dados de origem.

  3. Clique no nome do objeto.

    É apresentada a página Detalhes do objeto.

  4. Copie o valor fornecido no campo URI gsutil, que começa com gs://.

Suporte de carateres universais para URIs do Cloud Storage

Se os seus dados estiverem separados em vários ficheiros, pode usar um caráter universal asterisco (*) para selecionar vários ficheiros. A utilização do caráter universal asterisco tem de seguir estas regras:

  • O asterisco pode aparecer no nome do objeto ou no final do nome do objeto.
  • A utilização de vários asteriscos não é suportada. Por exemplo, o caminho gs://mybucket/fed-*/temp/*.csv é inválido.
  • A utilização de um asterisco com o nome do contentor não é suportada.

Exemplos:

  • O exemplo seguinte mostra como selecionar todos os ficheiros em todas as pastas que começam com o prefixo gs://mybucket/fed-samples/fed-sample:

    gs://mybucket/fed-samples/fed-sample*

  • O exemplo seguinte mostra como selecionar apenas ficheiros com a extensão .csv na pasta com o nome fed-samples e quaisquer subpastas de fed-samples:

    gs://mybucket/fed-samples/*.csv

  • O exemplo seguinte mostra como selecionar ficheiros com um padrão de nomenclatura de fed-sample*.csv na pasta denominada fed-samples. Este exemplo não seleciona ficheiros em subpastas de fed-samples.

    gs://mybucket/fed-samples/fed-sample*.csv

Quando usar a ferramenta de linhas de comando bq, pode ter de usar o caráter de escape no asterisco em algumas plataformas.

Não pode usar um caráter universal asterisco quando cria tabelas externas associadas a exportações do Datastore ou do Firestore.

Preços

As seguintes taxas de obtenção e transferência de dados do Cloud Storage aplicam-se a pedidos do BigQuery:

Limitações

Para ver informações sobre as limitações aplicáveis a tabelas externas, consulte o artigo Limitações das tabelas externas.

O que se segue?