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.

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.

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.