Criar tabelas externas no Cloud Storage

Com o BigQuery, é possível realizar a consulta de dados do Cloud Storage nos seguintes formatos:

  • Valores separados por vírgula (CSV, na sigla em inglês)
  • JSON (delimitado por nova linha)
  • Avro
  • ORC
  • Parquet
  • Exportações do Datastore
  • Exportações do Firestore

O BigQuery é compatível com a consulta de dados do Cloud Storage destas classes de armazenamento:

  • Padrão
  • Nearline
  • Coldline
  • Arquivar

Para consultar uma tabela externa do Cloud Storage, você precisa ter permissões na tabela externa e nos arquivos dele. Recomendamos o uso de uma tabela do BigLake, se possível. As tabelas do BigLake fornecem delegação de acesso. Assim, você só precisa de permissões na tabela do BigLake para consultar os dados do Cloud Storage.

Considere a localização do seu conjunto de dados e do bucket do Cloud Storage ao consultar dados armazenados nele.

Antes de começar

Atribua papéis do Identity and Access Management (IAM) que concedam aos usuários as permissões necessárias para realizar cada tarefa deste documento. As permissões necessárias para executar uma tarefa (se houver) são listadas na seção "Permissões necessárias".

Funções exigidas

Para criar uma tabela externa, você precisa da permissão bigquery.tables.create do BigQuery Identity and Access Management (IAM).

Cada um dos seguintes papéis predefinidos do Identity and Access Management inclui esta permissão:

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

Você também precisa das seguintes permissões para acessar o bucket do Cloud Storage que contém seus dados:

  • storage.buckets.get
  • storage.objects.get
  • storage.objects.list (obrigatório se você estiver usando um caractere curinga de URI)

O papel predefinido de gerenciamento de identidade e acesso do Cloud Storage (roles/storage.admin) inclui essas permissões.

Se você não for um principal em qualquer um desses papéis, peça ao administrador para conceder acesso a você ou criar a tabela externa para você.

Para mais informações sobre os papéis e as permissões do Identity and Access Management no BigQuery, consulte Papéis e permissões predefinidos.

Escopos de acesso a instâncias do Compute Engine

Em uma instância do Compute Engine, se você precisar consultar uma tabela externa vinculada a uma origem do Cloud Storage, ela precisará ter pelo menos o escopo de acesso somente leitura do Cloud Storage (https://www.googleapis.com/auth/devstorage.read_only).

Os escopos controlam o acesso da instância do Compute Engine aos produtos do Google Cloud, incluindo o Cloud Storage. Os aplicativos executados na instância usam a conta de serviço anexada à instância para chamar APIs do Google Cloud.

Por padrão, se você configurar uma instância do Compute Engine para ser executada como a conta de serviço padrão do Compute Engine, ela terá vários escopos padrão, incluindo o escopo https://www.googleapis.com/auth/devstorage.read_only.

Se, em vez disso, você configurar a instância com uma conta de serviço personalizada, conceda explicitamente o escopo https://www.googleapis.com/auth/devstorage.read_only à instância.

Para mais informações sobre como aplicar escopos a uma instância do Compute Engine, consulte Como alterar a conta de serviço e os escopos de acesso de uma instância. Acesse Contas de serviço para saber mais sobre esse tipo de conta do Compute Engine.

Criar tabelas externas em dados não particionados

É possível criar uma tabela permanente vinculada à sua fonte de dados externa da seguinte forma:

Selecione uma das seguintes opções:

Console

  1. Acessar a página do BigQuery.

    Ir para o BigQuery

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

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

  4. Na seção Origem, especifique os seguintes campos:

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

    2. Em Selecionar arquivo do bucket do GCS ou usar um padrão de URI, procure para selecionar um bucket e um arquivo a ser usado ou digite o caminho no formato gs://bucket_name/[folder_name/]file_name.

      Não é possível especificar vários URIs no console do Google Cloud, mas é possível selecionar vários arquivos especificando um caractere curinga de asterisco (*). Por exemplo, gs://mybucket/file_name*. Para mais informações, consulte Compatibilidade de caracteres curinga com URIs do Cloud Storage.

      O bucket do Cloud Storage precisa estar no mesmo local que o conjunto de dados que contém a tabela que você está criando.

    3. Em Formato do arquivo, selecione o formato que corresponde ao seu arquivo.

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

    1. Em Projeto, selecione o projeto em que a tabela será criada.

    2. Em Conjunto de dados, selecione o conjunto de dados em que a tabela será criada.

    3. Em Tabela, insira o nome da tabela que você está criando.

    4. Em Tipo de tabela, selecione Tabela externa.

  6. Na seção Esquema, é possível ativar a detecção automática de esquema ou especificar manualmente um esquema se você tiver um arquivo de origem. Se você não tiver um arquivo de origem, especifique um esquema manualmente.

    • Para ativar a detecção automática de esquema, selecione a opção Detectar automaticamente.

    • Para especificar um esquema manualmente, deixe a opção Detectar automaticamente desmarcada. Ative Editar como texto e insira o esquema da tabela como uma matriz JSON.

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

  8. Clique em Criar tabela.

Após a criação da tabela permanente, é possível executar uma consulta na tabela como se ela fosse nativa do BigQuery. Após a conclusão da consulta, será possível exportar os resultados como arquivos CSV ou JSON, salvá-los como uma tabela ou nas Planilhas Google.

SQL

É possível criar uma tabela externa permanente executando a instrução DDL CREATE EXTERNAL TABLE. É possível especificar o esquema explicitamente ou usar a detecção automática de esquema para inferir o esquema a partir dos dados externos.

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

    Ir para o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

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

    Substitua:

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

      É possível selecionar vários arquivos do bucket especificando um caractere curinga de asterisco (*) no caminho. Por exemplo, ['gs://mybucket/file_name*']. Para mais informações, consulte Compatibilidade de caracteres curinga com URIs do Cloud Storage.

      É possível especificar vários buckets para a opção uris fornecendo múltiplos caminhos.

      Os exemplos a seguir mostram valores uris válidos:

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

      Quando você especifica valores uris voltados para vários arquivos, todos eles precisam compartilhar um esquema compatível.

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

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

Exemplos

No exemplo a seguir, a detecção automática de esquema é usada para criar uma tabela externa chamada sales vinculada a um arquivo CSV armazenado no Cloud Storage:

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

O próximo exemplo especifica um esquema explicitamente e ignora a primeira linha no arquivo 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 sinalização --external_table_definition. Essa sinalização contém um caminho para um arquivo de definição de tabelas ou uma definição de tabela in-line.

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

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

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_PATH > DEFINITION_FILE

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

Substitua:

  • SOURCE_FORMAT: o formato da fonte de dados externa, por exemplo, CSV.

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

    É possível selecionar vários arquivos do bucket especificando um caractere curinga de asterisco (*) no file_pattern. Por exemplo, gs://mybucket/file00*.parquet. Para mais informações, consulte Suporte a caracteres curinga para URIs do Cloud Storage.

    É possível especificar vários buckets para a opção uris fornecendo múltiplos caminhos.

    Os exemplos a seguir mostram valores uris válidos:

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

    Quando você especifica valores uris voltados para vários arquivos, todos eles precisam compartilhar um esquema compatível.

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

  • DEFINITION_FILE: o caminho para o arquivo de definição de tabelas na máquina local.

  • DATASET_NAME: o nome do conjunto de dados onde está a tabela

  • TABLE_NAME: o nome da tabela que você está criando;

  • SCHEMA especifica um caminho para um arquivo de esquema JSON ou especifica o esquema no formato 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 detecção automática de esquema, defina a sinalização --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 da tabela in-line

Em vez de criar um arquivo de definição de tabela, é possível transmitir a definição da tabela diretamente para o comando bq mk:

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

Substitua:

  • SOURCE_FORMAT: o formato da fonte de dados externa.

    Por exemplo, CSV.

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

    É possível selecionar vários arquivos do bucket especificando um caractere curinga de asterisco (*) no file_pattern. Por exemplo, gs://mybucket/file00*.parquet. Para mais informações, consulte Suporte a caracteres curinga para URIs do Cloud Storage.

    É possível especificar vários buckets para a opção uris fornecendo múltiplos caminhos.

    Os exemplos a seguir mostram valores uris válidos:

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

    Quando você especifica valores uris voltados para vários arquivos, todos eles precisam compartilhar um esquema compatível.

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

  • DATASET_NAME: o nome do conjunto de dados onde está a tabela.

  • TABLE_NAME: o nome da tabela que você está criando;

  • SCHEMA especifica um caminho para um arquivo de esquema JSON ou especifica o esquema no formato field:data_type,field:data_type,.... Para usar a detecção automática de esquema, omita esse 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 de API tables.insert e crie um ExternalDataConfiguration no recurso Table que você transmite.

Especifique a propriedade schema ou defina a propriedade autodetect como true para ativar a detecção automática de esquema para fontes de dados compatíveis.

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar 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 testar esta amostra, siga as instruções de configuração do Node.js no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Node.js.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar 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 testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar 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}"
)

Criar tabelas externas em dados particionados

É possível criar uma tabela externa para dados particionados do Hive que residem no Cloud Storage. Depois de criar uma tabela particionada externamente, não será possível alterar a chave de partição. Você precisa recriar a tabela para alterar a chave de partição.

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

Console

  1. No console do Google Cloud, acesse o BigQuery.

    Acessar o BigQuery

  2. No painel Explorer, expanda o projeto e selecione um conjunto de dados.
  3. Clique em Acessar ações e depois em Criar tabela. O painel Criar tabela será aberto.
  4. Na seção Origem, especifique os seguintes campos:
    1. Em Criar tabela de, selecione Google Cloud Storage.
    2. Em Selecionar arquivo do bucket do Cloud Storage, insira o caminho para a pasta do Cloud Storage usando caracteres curinga. Por exemplo, my_bucket/my_files*. O bucket do Cloud Storage precisa estar no mesmo local que o conjunto de dados que contém a tabela que você quer criar, anexar ou substituir.
    3. Na lista Formato de arquivo, selecione o tipo de arquivo.
    4. Marque a caixa de seleção Particionamento de dados de origem e, em Selecionar prefixo de URI de origem, insira o prefixo de URI do Cloud Storage. Por exemplo, gs://my_bucket/my_files.
    5. Na seção Modo de inferência de partição, selecione uma das seguintes opções:
      • Inferir automaticamente os tipos: para definir o modo de detecção do esquema de partição como AUTO.
      • Todas as colunas são strings para definir o modo de detecção de esquema de partição como STRINGS.
      • Forneça meu próprio: para definir o modo de detecção de esquema de partição como CUSTOM e insira manualmente as informações do esquema para as chaves de partição. Para mais informações, consulte Oferecer um esquema personalizado de chave de partição.
    6. Opcional: Para exigir um filtro de partição em todas as consultas desta tabela, marque a caixa de seleção Exigir filtro de partição. A exigência de um filtro de partição pode reduzir custos e melhorar o desempenho. Para mais informações, consulte Como exigir filtros de predicado em chaves de partição em consultas.
  5. Na seção Destino, especifique os seguintes campos:
    1. Em Projeto, selecione o projeto em que você quer criar a tabela.
    2. Em Conjunto de dados, selecione o conjunto de dados em que você quer criar a tabela.
    3. Em Tabela, insira o nome da tabela que você quer criar.
    4. Em Tipo de tabela, selecione Tabela externa.
  6. Na seção Esquema, insira a definição do esquema.
  7. Para ativar a detecção automática de esquema, selecione Detecção automática.
  8. Para ignorar linhas com valores de coluna extras que não correspondem ao esquema, expanda a seção Opções avançadas e selecione Valores desconhecidos.
  9. Selecione Criar tabela.

SQL

Use a instrução DDL CREATE EXTERNAL TABLE.

O exemplo a seguir usa a detecção automática de chaves de particionamento 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:

  • SOURCE_FORMAT: o formato da fonte de dados externa, como PARQUET
  • GCS_URIS: o caminho para a pasta do Cloud Storage, usando o formato de caractere curinga
  • GCS_URI_SHARED_PREFIX: o prefixo de URI de origem sem o caractere curinga
  • BOOLEAN: especifica se um filtro de predicado é necessário no momento da consulta. Essa flag é opcional. O valor padrão é false.

O exemplo a seguir usa chaves e tipos de particionamento 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:

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

No exemplo a seguir, criamos uma tabela particionada externamente. Ela usa a detecção automática de esquema para detectar o esquema de arquivo e o layout de particionamento do Hive. Se o caminho externo for gs://bucket/path/field_1=first/field_2=1/data.parquet, as colunas de partição serão detectadas 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 a seguir cria uma tabela particionada externamente especificando explicitamente as colunas de partição. Neste exemplo, consideramos que o caminho do arquivo 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 arquivo 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:

  • SOURCE_FORMAT: o formato da fonte de dados externa. Por exemplo, CSV.
  • PARTITIONING_MODE: o modo de particionamento do Hive. Use um dos seguintes valores:
    • AUTO: detecta automaticamente os nomes e tipos de chaves.
    • STRINGS: converte automaticamente os nomes das chaves em strings.
    • CUSTOM: codifique o esquema da chave no prefixo do URI de origem.
  • GCS_URI_SHARED_PREFIX: o prefixo de URI de origem.
  • BOOLEAN: especifica se um filtro de predicado é necessário no momento da consulta. Essa flag é opcional. O valor padrão é false.
  • GCS_URIS: o caminho para a pasta do Cloud Storage, usando o formato de caractere curinga
  • DEFINITION_FILE: o caminho para o arquivo de definição de tabelas na máquina local.

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

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

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

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

Substitua:

  • DEFINITION_FILE: o caminho para o arquivo de definição da tabela.
  • DATASET_NAME: o nome do conjunto de dados onde está a tabela
  • TABLE_NAME: o nome da tabela que você está criando;
  • SCHEMA especifica um caminho para um arquivo de esquema JSON ou especifica o esquema no formato field:data_type,field:data_type,.... Para usar a detecção automática de esquema, omita esse argumento.

Exemplos

O exemplo a seguir usa o modo de particionamento 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 a seguir usa o modo de particionamento 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 a seguir usa o modo de particionamento 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 o particionamento do Hive usando a API BigQuery, inclua um hivePartitioningOptions no objeto ExternalDataConfiguration quando você criar oarquivo de definição de tabela.

Se você definir o campo hivePartitioningOptions.mode como CUSTOM, será necessário codificar o esquema da chave de partição no campo hivePartitioningOptions.sourceUriPrefix da seguinte maneira: gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Para aplicar o uso de um filtro de predicado no momento da consulta, defina o campo hivePartitioningOptions.requirePartitionFilter como true.

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar 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 Consultar dados do Cloud Storage em tabelas externas.

Fazer upgrade de tabelas externas para o BigLake

É possível fazer upgrade com base no Cloud Storage para tabelas do BigLake associando a tabela externa a uma conexão. Se você quiser usar o armazenamento em cache de metadados com a tabela do BigLake, especifique as configurações para isso ao mesmo tempo. Para acessar detalhes como o formato e o URI de origem da tabela, consulte Receber informações da tabela.

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

SQL

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

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

    Ir para o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

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

    Substitua:

    • PROJECT_ID: o nome do projeto que contém a tabela
    • DATASET: o nome do conjunto de dados onde está a tabela
    • EXTERNAL_TABLE_NAME: o nome da tabela
    • REGION: a região que contém a conexão
    • CONNECTION_ID: o nome da conexão a ser usada
    • TABLE_FORMAT: o formato usado pela tabela

      Não é possível mudar isso durante a atualização da tabela.

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

      É possível selecionar vários arquivos do bucket especificando um caractere curinga de asterisco (*) no caminho. Por exemplo, ['gs://mybucket/file_name*']. Para mais informações, consulte Compatibilidade de caracteres curinga com URIs do Cloud Storage.

      É possível especificar vários buckets para a opção uris fornecendo múltiplos caminhos.

      Os exemplos a seguir mostram valores uris válidos:

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

      Quando você especifica valores uris voltados para vários arquivos, todos eles precisam compartilhar um esquema compatível.

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

    • STALENESS_INTERVAL: especifica se os metadados em cache são usados pelas operações na tabela e quando eles precisam ser atualizados para que a operação possa usá-los.

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

      Para desativar o armazenamento em cache de metadados, especifique 0. Esse é o padrão.

      Para ativar o armazenamento em cache de metadados, especifique um valor de literal de intervalo entre 30 minutos e 7 dias. Por exemplo, especifique INTERVAL 4 HOUR para um intervalo de inatividade de 4 horas. Com esse valor, as operações na tabela usarão metadados em cache se tiverem sido atualizados nas últimas 4 horas. Se os metadados em cache forem anteriores a isso, a operação recuperará os metadados do Cloud Storage.

    • CACHE_MODE: especifica se o cache de metadados é atualizado de forma automática ou manual.

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

      Defina como AUTOMATIC para que o cache de metadados seja atualizado em um intervalo definido pelo sistema, geralmente entre 30 e 60 minutos.

      Defina como MANUAL se quiser atualizar o cache de metadados com uma programação que você determinar. Nesse caso, chame o procedimento do sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para atualizar o cache.

      Defina CACHE_MODE se STALENESS_INTERVAL estiver definido como um valor maior que 0.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar 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 descreva os aspectos da tabela a serem alterados:

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

    Substitua:

    • PROJECT_ID: o nome do projeto que contém a conexão.
    • REGION: a região que contém a conexão.
    • CONNECTION_ID: o nome da conexão a ser usada.
    • TABLE_FORMAT: o formato usado pela tabela. Não é possível mudar isso durante a atualização da tabela.

    • CACHE_MODE: especifica se o cache de metadados é atualizado de forma automática ou manual. Para mais informações sobre considerações de armazenamento em cache de metadados, consulte Armazenamento em cache de metadados para desempenho.

      Defina como AUTOMATIC para que o cache de metadados seja atualizado em um intervalo definido pelo sistema, geralmente entre 30 e 60 minutos.

      Defina como MANUAL se quiser atualizar o cache de metadados com uma programação que você determinar. Nesse caso, chame o procedimento do sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para atualizar o cache.

      Defina CACHE_MODE se STALENESS_INTERVAL estiver definido como um valor maior que 0.

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

      É possível limitar os arquivos selecionados no bucket especificando um caractere curinga de asterisco (*) no caminho. Por exemplo, gs://mybucket/file_name*. Para mais informações, consulte Compatibilidade de caracteres curinga com URIs do Cloud Storage.

      É possível especificar vários buckets para a opção uris fornecendo múltiplos caminhos.

      Os exemplos a seguir mostram valores uris válidos:

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

      Quando você especifica valores uris voltados para vários arquivos, todos eles precisam compartilhar um esquema compatível.

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

    • DEFINITION_FILE: o nome do arquivo de definição de tabela que você está criando.

  2. Atualize a tabela usando 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:

    • STALENESS_INTERVAL: especifica se os metadados em cache são usados pelas operações na tabela e quando eles precisam ser atualizados para que a operação possa usá-los. Para saber mais sobre considerações de armazenamento em cache de metadados, consulte Armazenamento em cache de metadados para desempenho.

      Para desativar o armazenamento em cache de metadados, especifique 0. Esse é o padrã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 inatividade de 4 horas. Com esse valor, as operações na tabela usarão metadados em cache se tiverem sido atualizados nas últimas 4 horas. Se os metadados em cache forem anteriores a isso, a operação recuperará os metadados do Cloud Storage.

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

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

    • DATASET: o nome do conjunto de dados onde está a tabela

    • EXTERNAL_TABLE_NAME: o nome da tabela

Caminho do recurso do Cloud Storage

Ao criar uma tabela externa com base em uma fonte de dados do Cloud Storage, forneça o caminho para os dados.

O caminho do recurso do Cloud Storage contém o nome do bucket e o objeto (nome de arquivo). Por exemplo, se o bucket do Cloud Storage se chamar mybucket e o arquivo de dados for denominado myfile.csv, o caminho de recurso será gs://mybucket/myfile.csv.

O BigQuery não oferece suporte a caminhos de recursos do Cloud Storage que incluam várias barras consecutivas após a barra dupla inicial. Os nomes de objeto do Cloud Storage podem conter vários caracteres de barra ("/") consecutivos. No entanto, o BigQuery os converte em uma única barra. Por exemplo, o caminho de recurso a seguir, ainda que válido no Cloud Storage, não funciona no BigQuery: gs://bucket/my//object//name.

Para recuperar o caminho do recurso do Cloud Storage:

  1. Abra o Console do Cloud Storage.

    Console do Cloud Storage

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

  3. Clique no nome do objeto.

    A página Detalhes do objeto é aberta.

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

Compatibilidade com caracteres curinga para URIs do Cloud Storage

Se os dados estiverem separados em vários arquivos, use um caractere curinga (*) para selecionar vários arquivos. O uso do caractere curinga de asterisco precisa seguir estas regras:

  • O caractere curinga pode ser exibido dentro ou no final do nome do objeto.
  • Não é possível usar vários asteriscos. Por exemplo, o caminho gs://mybucket/fed-*/temp/*.csv é inválido.
  • Não é possível usar um asterisco com o nome do bucket.

Exemplos:

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

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

  • O exemplo a seguir mostra como selecionar apenas arquivos com uma extensão .csv na pasta chamada fed-samples e em qualquer subpasta de fed-samples:

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

  • O exemplo a seguir mostra como selecionar arquivos com um padrão de nomenclatura de fed-sample*.csv na pasta chamada fed-samples. Este exemplo não seleciona arquivos em subpastas de fed-samples.

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

Ao usar a ferramenta de linha de comando bq, talvez seja necessário evitar o asterisco em algumas plataformas.

Não é possível usar um caractere curinga de asterisco ao criar tabelas externas vinculadas a exportações do Datastore ou do Firestore.

Limitações

Para informações sobre limitações aplicáveis a tabelas externas, consulte Limitações de tabelas externas.

A seguir