Introdução a tabelas particionadas

Nesta página, você encontra uma visão geral sobre tabelas particionadas no BigQuery.

Uma tabela particionada é uma tabela especial dividida em segmentos, chamados partições, que facilitam o gerenciamento e a consulta aos dados. Ao dividir uma tabela grande em partições menores, é possível melhorar o desempenho da consulta, além de reduzir o número de bytes lidos, o que ajuda a controlar os custos.

É possível particionar tabelas do BigQuery da seguinte forma:

  • Coluna de unidade de tempo: as tabelas são particionadas com base em uma coluna de TIMESTAMP, DATE ou DATETIME na tabela.

  • Tempo de processamento: as tabelas são particionadas com base no carimbo de data/hora quando o BigQuery processa os dados.

  • Intervalo de números inteiros: as tabelas são particionadas com base em uma coluna de números inteiros.

Se uma consulta filtrar o valor da coluna de particionamento, o BigQuery poderá verificar as partições correspondentes ao filtro e ignorar as partições restantes. Esse processo é chamado de remoção.

Particionamento de colunas por unidade de tempo

É possível particionar uma tabela em uma coluna DATE, TIMESTAMP ou DATETIME na tabela. Quando você grava dados na tabela, o BigQuery os coloca automaticamente na partição correta, com base nos valores da coluna.

Para as colunas TIMESTAMP e DATETIME, as partições podem ter granularidade por hora diária, mensal ou anual. Para as colunas DATE, as partições podem ter granularidade diária, mensal ou anual. Os limites de partições são baseados no horário UTC.

Por exemplo, se você particionar uma tabela em uma coluna DATETIME com particionamento mensal. Se você inserir os seguintes valores na tabela, as linhas serão gravadas nas seguintes partições:

Valor da coluna Partição (mensal)
DATETIME("2019-01-01") 201901
DATETIME("2019-01-15") 201901
DATETIME("2019-04-30") 201904

Além disso, duas partições especiais são criadas:

  • __NULL__: contém linhas com valores NULL na coluna de particionamento.
  • __UNPARTITIONED__: contém linhas em que o valor da coluna de particionamento é anterior a 01/01/1960 ou posterior a 31/12/2159.

Particionamento por tempo de processamento:

Quando você cria uma tabela particionada por tempo de processamento, o BigQuery atribui automaticamente linhas às partições com base na hora em que o BigQuery processa os dados. É possível escolher granularidade por hora, diária, mensal ou anual para as partições. Os limites de partições são baseados no horário UTC.

Uma tabela particionada por tempo de processamento tem uma pseudocoluna chamada _PARTITIONTIME. O valor dessa coluna é o tempo de processamento de cada linha, truncado para o limite de partição (como por hora ou dia). Por exemplo, suponha que você crie uma tabela particionada por tempo de processamento com particionamento por hora e envie dados nos seguintes horários:

Tempo de ingestão _PARTITIONTIME Partição (por hora)
05/07/2021 17:22:00 05/07/2021 17:00:00 2021050717
05/07/2021 17:40:00 05/07/2021 17:00:00 2021050717
05/07/2021 18:31:00 05/07/2021 18:00:00 2021050718

Como a tabela neste exemplo usa particionamento por hora, o valor de _PARTITIONTIME é truncado para um limite de hora. O BigQuery usa esse valor para determinar a partição correta para os dados.

Também é possível gravar dados em uma partição específica. Por exemplo, talvez você queira carregar dados históricos ou ajustar os fusos horários. É possível usar qualquer data válida entre 01/01/0001 e 31/12/9999. No entanto, as instruções DML não podem referenciar datas anteriores a 01/01/1970 ou posteriores a 31/12/2159. Para mais informações, consulte Gravar dados em uma partição específica.

Particionamento por intervalo de números inteiros:

É possível particionar uma tabela com base em intervalos de valores em uma coluna INTEGER específica. Para criar uma tabela particionada por intervalo de números inteiros, forneça:

  • A coluna de particionamento.
  • O valor inicial do particionamento de intervalo (inclusive).
  • O valor final do particionamento de intervalo (exclusivo).
  • O intervalo de cada intervalo dentro da partição.

Por exemplo, suponha que você crie uma partição por intervalo de números inteiros com a seguinte especificação:

Argumento Valor
column name customer_id
start 0
end 100
interval 10

A tabela é particionada na coluna customer_id em intervalos de 10. Os valores de 0 a 9 vão para uma partição, os valores de 10 a 19 para a próxima partição e assim por diante até 99. Valores fora desse intervalo vão para uma partição chamada __UNPARTITIONED__ As linhas em que customer_id é NULL vão para uma partição chamada __NULL__.

Escolha o particionamento diário, por hora, mensal ou anual.

Ao particionar uma tabela por coluna de unidade de tempo ou tempo de processamento, você escolhe se as partições têm granularidade diária, por hora, mensal ou anual.

  • Particionamento diário é o tipo de particionamento padrão. O particionamento diário é uma boa opção quando os dados são distribuídos por um grande intervalo de datas ou se os dados são adicionados continuamente ao longo do tempo.

  • Opte pelo particionamento por hora se as tabelas tiverem um grande volume de dados que abranja um intervalo curto (geralmente menos de seis meses de valores de carimbo de data/hora). Se você escolher o particionamento por hora, verifique se a contagem permanecerá dentro dos limites de partição.

  • Escolha o particionamento mensal ou anual se suas tabelas tiverem uma quantidade de dados relativamente pequena para cada dia, mas abrangerem um período amplo. Essa opção de particionamento também é recomendada se seu fluxo de trabalho exigir atualização frequente ou adição de linhas que abrangem um período amplo (por exemplo, mais de 500 datas). Nesses cenários, use o particionamento mensal ou anual, além do clustering na coluna de particionamento para obter um melhor desempenho. Para mais informações, consulte Particionamento versus clustering nesta página.

Particionamento x clustering

Tanto o particionamento quanto o clustering podem melhorar o desempenho e reduzir o custo das consultas.

Use o clustering nas seguintes circunstâncias:

  • Você não precisa de garantias de custo rígidas antes de executar a consulta.
  • Você precisa de mais granularidade do que o particionamento permite. Para conseguir benefícios de clustering, além das vantagens do particionamento, use a mesma coluna para particionamento e clustering.
  • Suas consultas geralmente usam filtros ou agregação em várias colunas específicas.
  • A cardinalidade do número de valores em uma coluna ou grupo de colunas é grande.

Use o particionamento nas seguintes circunstâncias:

  • Você quer saber os custos de consulta antes de executar uma consulta. A remoção da partição é feita antes da execução da consulta. Assim, é possível saber o custo da consulta após a remoção do particionamento fazendo uma simulação de consulta. A eliminação do cluster é feita quando a consulta é executada, de modo que o custo é conhecido somente após a conclusão da consulta.

  • Você precisa de gerenciamento no nível do particionamento. Por exemplo, você quer definir um prazo de validade para a partição, carregar dados para uma partição específica ou excluir partições.

  • Especifique como os dados serão particionados e quais ficarão em cada particionamento. Por exemplo, você quer definir a granularidade de tempo ou os intervalos usados para particionar a tabela para particionamento por intervalo de números inteiros.

Prefira o clustering ao particionamento nas seguintes circunstâncias:

  • O particionamento resulta em uma pequena quantidade de dados por particionamento (aproximadamente menos de 1 GB).
  • O particionamento resulta em um grande número de particionamentos além dos limites nas tabelas particionadas.
  • O particionamento faz com que as operações de mutação modifiquem a maioria dos particionamentos da tabela com frequência (por exemplo, a cada poucos minutos).

Também é possível combinar o particionamento com o clustering. Primeiro, os dados são particionados e, em seguida, os dados de cada partição são agrupados pelas colunas de clustering.

Quando a tabela for consultada, o particionamento definirá um limite superior do custo da consulta com base na remoção da partição. Há outras possibilidades de economia na consulta quando ela realmente for executada, devido à remoção do cluster.

Particionamento x fragmentação

A fragmentação de tabelas é a prática de armazenar dados em várias tabelas, usando um prefixo de nome como [PREFIX]_YYYYMMDD.

O particionamento é recomendado em vez da fragmentação da tabela porque as tabelas particionadas têm um desempenho melhor. Com tabelas fragmentadas, o BigQuery precisa manter uma cópia do esquema e dos metadados de cada tabela. O BigQuery também pode precisar verificar as permissões para cada tabela consultada. Essa prática também sobrecarrega a consulta e afeta o desempenho dela.

Se você criou tabelas fragmentadas por data anteriormente, é possível convertê-las em uma tabela particionada por tempo de processamento. Para mais informações, consulte Converter tabelas fragmentadas por data em tabelas particionadas por tempo de processamento.

Limitações

Não é possível usar um SQL legado para consultar tabelas particionadas ou gravar resultados da consulta nesse tipo de tabela.

As tabelas particionadas por coluna de unidade de tempo estão sujeitas às seguintes limitações:

  • A coluna de particionamento precisa ser DATE, TIMESTAMP ou DATETIME. O modo da coluna pode ser REQUIRED ou NULLABLE, mas não pode ser REPEATED (baseado em matriz).
  • A coluna de particionamento precisa ser um campo de nível superior. Não é possível usar um campo de folha de um RECORD (STRUCT) como a coluna de particionamento.

As tabelas particionadas por variação em números inteiros estão sujeitas às seguintes limitações:

  • A coluna de particionamento precisa ser INTEGER. O modo da coluna pode ser REQUIRED ou NULLABLE, mas não pode ser REPEATED (baseado em array).
  • A coluna de particionamento precisa ser um campo de nível superior. Não é possível usar um campo de folha de um RECORD (STRUCT) como a coluna de particionamento.

Cotas e limites de tabelas particionadas

Tabelas particionadas definiram limites no BigQuery.

Cotas e limites também se aplicam aos diferentes tipos de jobs executados em tabelas particionadas, incluindo:

Para mais informações, consulte Cotas e limites.

Preços para tabelas particionadas

Ao criar e usar tabelas particionadas no BigQuery, as cobranças são baseadas na quantidade de dados armazenados nas partições e nas consultas de dados executadas:

  • Para saber mais sobre preços de armazenamento, consulte esta página.
  • Para saber mais sobre preços de consulta, consulte esta página.

Muitas operações de tabelas particionadas são gratuitas, incluindo carregamento de dados em partições, cópias de partições e exportação de dados de partições. Essas operações são gratuitas, mas estão sujeitas a cotas e limites do BigQuery. Consulte Operações gratuitas na página de preços para mais informações sobre esse assunto.

Próximas etapas