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
No Console do Cloud, acesse o BigQuery.
- No painel Explorer, expanda o projeto e selecione um conjunto de dados.
- Clique em Ver ações e em Abrir.
- No painel de detalhes, clique em Criar tabela.
- Na página Criar tabela, especifique os seguintes detalhes:
- Na seção Origem, em Criar tabela de, selecione
Google Cloud Storage. Em seguida, faça o seguinte:
- 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. - Na lista Formato de arquivo, selecione o tipo de arquivo.
- 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
. - 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.
- Inferir automaticamente os tipos: para definir o modo de detecção do esquema de partição como
- 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.
- Em Selecionar arquivo do bucket do Cloud Storage, insira o caminho para a
pasta do Cloud Storage usando caracteres curinga.
Por exemplo,
- Na seção Destino, especifique os seguintes campos:
- Em nome de conjunto de dados, selecione o conjunto de dados em que você quer criar a tabela.
- No campo Tabela, insira o nome da tabela que você quer criar.
- Verifique se o campo Tipo de tabela está definido como Tabela nativa.
- Na seção Esquema, insira a definição do esquema.
- Para ativar a detecção automática de esquema, selecione Detecção automática.
- 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.
Como atualizar tabelas particionadas externamente
As chaves de partição precisam ser invariantes ao atualizar tabelas externas permanentes.