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

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

Console

1. No Console do Cloud, acesse o BigQuery.

   Ir para o BigQuery

2. No painel Explorer, expanda o projeto e selecione um conjunto de dados.
3. Clique em Ver ações e em Abrir.
4. No painel de detalhes, clique em Criar tabela.
5. Na página Criar tabela, especifique os seguintes detalhes:
1. Na seção Origem, em Criar tabela de, selecione Google Cloud Storage. Em seguida, faça o seguinte:
   1. 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.
   2. Na lista Formato de arquivo, selecione o tipo de arquivo.
   3. 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.
   4. 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.
   5. 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.
2. Na seção Destino, especifique os seguintes campos:
   1. Em nome de conjunto de dados, selecione o conjunto de dados em que você quer criar a tabela.
   2. No campo Tabela, insira o nome da tabela que você quer criar.
   3. Verifique se o campo Tipo de tabela está definido como Tabela nativa.
3. Na seção Esquema, insira a definição do esquema.
4. Para ativar a detecção automática de esquema, selecione Detecção automática.
5. Selecione Criar tabela.

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

Em que:

• 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 do URI de origem;
• BOOLEAN especifica se um filtro de predicado é necessário no momento da consulta. Essa sinalização é 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

Em que:

• DEFINITION_FILE é o caminho para o arquivo 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 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.

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.