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. No momento, a criação e a modificação de tabelas só são aceitas ao usar o Console do Cloud, a ferramenta de linha de comando bq e a API REST.

Para instruções sobre como consultar tabelas particionadas gerenciadas, consulte Como trabalhar com tabelas particionadas.

Limitações

  • A compatibilidade com o particionamento do Hive é feita por meio de um prefixo de origem comum aplicado a todos os URIs. Esse prefixo fica antes da codificação da partição, que tem este formato: 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.
  • No momento, a criação e a modificação de tabelas estão limitadas ao Console do Cloud, à ferramenta de linha de comando bq e à API REST.
  • Todas as limitações das consultas a fontes de dados externas armazenadas no Cloud Storage são aplicadas.
  • A compatibilidade refere-se somente ao SQL padrão.

Layouts de dados compatíveis

Os dados precisam estar em conformidade com um layout padrão de particionamento do Hive. O layout padrão está presente nos arquivos a seguir, por exemplo. Neles, os pares de chave-valor são definidos como diretórios em que o sinal = serve como separador, e as chaves de partição estão sempre na mesma ordem.

gs://bucket/table/dt=2019-10-31/lang=en/foo
gs://bucket/table/dt=2018-10-31/lang=fr/bar

O prefixo de URI de origem comum neste exemplo é gs://bucket/table.

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://bucket/table/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://bucket/table/dt=2019-10-31/lang=en/foo
gs://bucket/table/lang=fr/dt=2018-10-31/bar

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

As chaves de partição do Hive são exibidas como colunas normais quando você consulta os dados a partir do Cloud Storage. Oferecemos compatibilidade com três modos de detecção de esquemas de partição do Hive:

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

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

A detecção CUSTOM requer a codificação do esquema no campo do prefixo de URI de origem. Assim, é possível usar tipos especificados pelo usuário em cada chave de partição. Se os valores não forem analisados como o tipo especificado, a consulta falhará.

Por exemplo, ao definir "source_uri_prefix" como gs://my_bucket/my_table/{dt:DATE}/{val:STRING}, val é processado como STRING e dt como DATE. Além disso, gs://my_bucket/my_table é extraído como o prefixo de URI de origem dos arquivos correspondentes.

Consulte as instruções detalhadas sobre o Console do Cloud, a ferramenta de linha de comando bq e a API REST.

Remoção de partições

No BigQuery, é possível remover partições quando necessário usando predicados em consulta nas chaves. Isso evita a leitura de arquivos desnecessários. Isso melhora 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.

Para mais informações sobre como adicionar a opção requirePartitionFilter ao criar uma tabela particionada, consulte as instruções para a ferramenta de linha de comando bq e a API REST.

Como definir HivePartitioningOptions usando o Console do Cloud

Para definir as opções de particionamento do Hive no Console do Cloud, siga estas etapas:

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

    Acessar a página do BigQuery

  2. No painel Recursos, selecione o conjunto de dados.

  3. Clique no botão Criar tabela para criar uma nova tabela.

  4. Selecione Cloud Storage na lista suspensa Criar tabela de.

Opções de particionamento do Hive

  1. No campo Selecionar arquivo do bucket do Cloud Storage, insira o caminho do arquivo do conjunto de dados e selecione o tipo de arquivo na lista suspensa.
  2. Marque a caixa Particionamento de dados de origem e insira o prefixo do URI de origem no campo fornecido.
  3. Em Modo de inferência de partição, escolha "Inferir tipos automaticamente" para AUTO ou "Todas as colunas são strings" para STRINGS.
  4. Escolha "Tabela externa" na lista suspensa Tipo de tabela.

Como definir HivePartitioningOptions usando a ferramenta de linha de comando bq

O HivePartitioningOptions é definido no objeto ExternalDataConfiguration durante a criação do arquivo de definição da tabela.

Como solicitar a detecção automática de tipo de chave de particionamento

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
gcs_uris >/tmp/table_def_file

Para solicitar a detecção da chave de partição tipo STRING:

bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
gcs_uris >/tmp/table_def_file

Para fornecer um esquema personalizado de chave de partição codificado via campo "source_uri_prefix":

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 >/tmp/table_def_file

O esquema da chave de partição é codificado imediatamente após o prefixo de URI de origem. Use o seguinte formato para especificar --hive_partitioning_source_uri_prefix:

--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix/{key1:TYPE1}/{key2:TYPE2}/{key3:TYPE3}

Como exigir um filtro de partição

Para aplicar o uso de um filtro de predicado no momento da consulta, adicione --require_hive_partition_filter=True aos comandos bq mkdef acima. Este exemplo usa detecção de tipo de chave de partição AUTO:

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 >/tmp/table_def_file

Como definir HivePartitioningOptions usando a API REST

É possível ter compatibilidade do particionamento do Hive ao definir o HivePartitioningOptions no ExternalDataConfiguration durante a criação de um arquivo de definição de tabela.

Quando hivePartitioningOptions.mode for definido como CUSTOM, será preciso codificar o esquema da chave de partição dentro do sourceUriRefix. Basta definir hivePartitioningOptions.sourceUriPrefix como gs://bucket/path_to_table/{key1:TYPE1}/{key2:TYPE2}/{key3:TYPE3}.

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