Como consultar dados particionados externamente

Com o BigQuery, é possível consultar dados particionados externamente em formatos Avro, Parquet, ORC, JSON e CSV que estejam armazenados no Cloud Storage. Para isso, você precisa usar um layout padrão de particionamento do Hive. Para ativar a compatibilidade com o particionamento do Hive, configure as opções apropriadas no arquivo de definição da tabela. É possível criar e modificar tabelas usando o Console do Cloud, a ferramenta de linha de comando bq e a API BigQuery.

Para instruções sobre como consultar tabelas particionadas gerenciadas, consulte Introdução às tabelas particionadas.

Limitações

• A compatibilidade com o particionamento do Hive é feita por meio de um prefixo de origem comum aplicado em todos os URIs que terminam imediatamente antes da codificação da partição, como mostrado a seguir: gs://BUCKET/PATH_TO_TABLE/.
• Na estrutura de diretórios de uma tabela particionada do Hive, as chaves de particionamento dela aparecem na mesma ordem, com até dez chaves por tabela.
• Os dados precisam seguir um layout padrão de particionamento do Hive.
• As chaves de particionamento do Hive e as colunas nos arquivos subjacentes não podem se sobrepor.
• Todas as limitações das consultas a fontes de dados externas armazenadas no Cloud Storage são aplicadas.
• A compatibilidade é somente com o SQL padrão.

Layouts de dados compatíveis

Os dados precisam estar em conformidade com um layout padrão de particionamento do Hive. Por exemplo, os seguintes arquivos seguem o layout padrão: os pares de chave-valor são configurados como diretórios com um sinal = como separador, e as chaves de partição estão sempre na mesma ordem:

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/dt=2018-10-31/lang=fr/bar

O prefixo de URI de origem comum neste exemplo é gs://myBucket/myTable.

Layouts de dados incompatíveis

Se os nomes das chaves de partição não forem codificados no caminho do diretório, a detecção do esquema de partição falhará. Por exemplo, veja o caminho a seguir, que não codifica os nomes das chaves de partição:

gs://myBucket/myTable/2019-10-31/en/foo

A detecção também falha nos arquivos em que o esquema não está em uma ordem consistente. Por exemplo, veja os dois arquivos a seguir com codificações de chaves de partição invertidas:

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/lang=fr/dt=2018-10-31/bar

Para esses arquivos, a detecção do esquema de partição falhará.

Modos de detecção de esquemas de partição

As chaves de partição do Hive são exibidas como colunas normais ao carregar dados do Cloud Storage. O BigQuery é compatível com três modos de detecção de esquemas de partição do Hive:

• AUTO: nomes e tipos de chaves são detectados automaticamente. É possível detectar estes tipos: STRING, INTEGER, DATE e TIMESTAMP.
• STRINGS: os nomes de chave são convertidos automaticamente para tipo STRING.
• CUSTOM: o esquema da chave de partição é codificado conforme especificado no prefixo de URI de origem.

Como fornecer um esquema de chave de partição CUSTOM

Para usar um esquema CUSTOM, especifique o esquema no campo de prefixo de URI de origem. Usar um esquema CUSTOM permite especificar o tipo de cada chave de partição. Os valores precisam ser analisados como o tipo especificado, caso contrário a consulta falhará.

Por exemplo, se você definir a sinalização source_uri_prefix como gs://myBucket/myTable/{dt:DATE}/{val:STRING}, o BigQuery tratará val como STRING, dt como DATE e usará gs://myBucket/myTable como o prefixo de URI de origem dos arquivos correspondentes.

Remoção de partições

No BigQuery, é possível remover partições quando necessário usando predicados em consulta nas chaves. Isso permite que o BigQuery evite a leitura de arquivos desnecessários, o que ajuda a melhorar o desempenho.

Exigir filtros de predicado em chaves de partição em consultas

Ao criar uma tabela particionada externamente, é possível exigir o uso de filtros de predicado em chaves de partição ativando a opção requirePartitionFilter em HivePartitioningOptions.

Quando essa opção está ativada, as tentativas de consultar a tabela particionada externamente sem especificar uma cláusula WHERE produzem o seguinte erro: Cannot query over table <table_name> without a filter over column(s) <partition key names> that can be used for partition elimination.

Observação: é preciso haver pelo menos um predicado que faça referência a uma ou mais chaves de partição para que o filtro seja considerado qualificado para a eliminação de partição. Por exemplo, para uma tabela com chave de partição val e coluna f no arquivo, as duas cláusulas WHERE a seguir atendem à exigência:
    WHERE val = "foo"
    WHERE val = "foo" AND f = "bar"

No entanto, WHERE (val = "foo" OR f = "bar") não é suficiente.

Como criar uma tabela externa para dados particionados do Hive

É possível criar uma tabela externa para dados particionados do Hive das seguintes maneiras:

• usando o Console do Cloud.
• usando a ferramenta de linha de comando bq;

• usando bibliotecas de cliente.

Console

1. Abra a página do BigQuery no Console do Cloud.

   Acesse a página do BigQuery

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

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

4. No painel de detalhes, clique em Criar tabela .

5. Na página Criar tabela, na seção Origem, faça o seguinte:

   1. Na lista suspensa Criar tabela a partir do, selecione Cloud Storage.
   2. No campo Selecionar arquivo do bucket do Cloud Storage, insira o caminho para a pasta do Cloud Storage usando o formato de caractere curinga. Por exemplo, my_bucket/my_files*.
   3. Na lista suspensa Formato do arquivo, selecione o tipo de arquivo.
   4. Marque a caixa de seleção Particionamento de dados de origem.
   5. No campo Selecionar prefixo de URI de origem, insira o prefixo do URI do Cloud Storage. Por exemplo, gs://my_bucket/my_files.
   6. Selecione um Modo de inferência de partição. Se você selecionar Fornecer o meu próprio, insira as informações do esquema das chaves de partição.
   7. Opcional: para exigir que todas as consultas nesta tabela usem um filtro de predicado, marque Exigir filtro de partição. Para mais informações, consulte Como exigir filtros de predicado em chaves de partição em consultas.

6. Na seção Destino da página Criar tabela, faça o seguinte:

   1. Selecione o nome do projeto e o nome do conjunto de dados.
   2. Na lista suspensa Tipo de tabela, selecione Tabela externa.
   3. No campo Nome da tabela, insira um nome para a tabela externa.

7. Na seção Esquema, na página Criar tabela, insira as informações do esquema. O BigQuery é compatível com a detecção automática de esquema em alguns formatos. Para mais informações, consulte Como usar a detecção automática de esquema.

8. Clique em Criar tabela.

bq

• Para definir a detecção automática de chave de partição, defina a sinalização --hive_partitioning_mode como AUTO. Exemplo:

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE

• Para definir a detecção de chave de partição do tipo string, defina a sinalização --hive_partitioning_mode como STRINGS. Exemplo:

bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE

• Para especificar um esquema de chave de partição personalizada usando a sinalização --source_uri_prefix, defina --hive_partitioning_mode como CUSTOM. Exemplo:

bq mkdef --source_format=NEWLINE_DELIMITED_JSON --hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/CUSTOM_SCHEMA_ENCODING \
GCS_URIS FILE_SCHEMA > TABLE_DEF_FILE

Para a sinalização --hive_partitioning_source_uri_prefix, especifique a parte <var>CUSOM_SCHEMA_ENCODING</var> do argumento imediatamente após a parte <var>GCS_URI_SHARED_PREFIX</var>, da seguinte maneira:

GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Exemplo:

--hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING}

• Para exigir o uso de um filtro de predicado no momento da consulta, adicione --require_hive_partition_filter=True ao comando bq mkdef. Por exemplo:

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=True \
GCS_URIS > TABLE_DEF_FILE

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.

Ver no GitHub Feedback

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());
    }
  }
}

Como atualizar tabelas particionadas externamente

As chaves de partição precisam ser invariantes ao atualizar tabelas externas permanentes.