Introdução a tabelas particionadas

Nesta página, você terá uma visão geral do suporte a 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:

Tabelas particionadas por tempo de processamento

Quando você cria uma tabela particionada por tempo de processamento, o BigQuery carrega automaticamente os dados em partições diárias baseadas em datas que refletem a hora de processamento ou chegada dos dados. Pseudocoluna e identificadores de sufixo permitem redefinir (substituir) e redirecionar dados para partições em um dia específico.

Nas tabelas particionadas por tempo de processamento, há uma pseudocoluna _PARTITIONTIME que contém um carimbo de data/hora baseado em data para os dados carregados nas tabelas. As consultas nas tabelas particionadas por tempo podem restringir os dados lidos fornecendo filtros _PARTITIONTIME que representam a localização de uma partição. Todos os dados na partição especificada são lidos pela consulta, mas o filtro de predicado _PARTITIONTIME restringe o número de partições verificadas.

Quando você cria tabelas particionadas por tempo de ingestão, as partições têm a mesma definição de esquema que a tabela. Se você precisar carregar dados em uma partição com um esquema diferente do da tabela, atualize o esquema da tabela antes de carregá-los. Também é possível usar opções de atualização de esquema para atualizar o esquema da tabela em um job de carregamento ou de consulta.

Tabelas particionadas por data, carimbo de data/hora ou data e hora

O BigQuery também permite o uso de tabelas particionadas com base em uma coluna DATE,TIMESTAMP ou DATETIME específica. Os dados gravados em uma tabela particionada por data, carimbo de data/hora e data e hora são enviados automaticamente à partição apropriada com base no valor da unidade de tempo (expresso em UTC para TIMESTAMP) especificado na coluna de particionamento.

Se a tabela for particionada em uma coluna DATE, crie partições com granularidade diária, mensal ou anual. Cada partição contém um intervalo de valores em que o início do intervalo é o início de um dia, mês ou ano, e o intervalo é de um dia, mês ou ano com base na granularidade do particionamento. Se a tabela for particionada em uma coluna TIMESTAMP ou DATETIME, crie partições com qualquer tipo de granularidade de unidade de tempo, incluindo HOUR.

As tabelas particionadas por data, carimbo de data/hora e data e hora não precisam de uma pseudocoluna _PARTITIONTIME. As consultas nas tabelas particionadas por data, carimbo de data/hora e data e hora podem especificar filtros de predicado com base na coluna de particionamento para reduzir a quantidade de dados verificados.

Quando você cria tabelas particionadas por data, carimbo de data/hora e data e hora, duas partições especiais são criadas:

  • __NULL__: representa linhas com valores NULL na coluna de particionamento.
  • __UNPARTITIONED__: representa dados que existem fora do intervalo permitido de datas.

Com exceção das partições __NULL__ e __UNPARTITIONED__, todos os dados na coluna de particionamento correspondem à data do identificador da partição. Isso permite que uma consulta determine quais partições não contêm dados que satisfaçam às condições do filtro. As consultas que filtram dados na coluna de particionamento podem restringir valores e eliminar partições desnecessárias.

Particionamento por data, carimbo de data/hora e data e hora versus fragmentação

Como alternativa às tabelas particionadas por data, carimbo de data/hora e data e hora, é possível fragmentar tabelas usando uma abordagem de nomeação com base no tempo, como [PREFIX]_YYYYMMDD. Esse processo é chamado de criação de tabelas fragmentadas por data. Usando SQL padrão ou legado, é possível especificar uma consulta com um operador UNION para limitar as tabelas verificadas pela consulta.

As tabelas particionadas por data, carimbo de data/hora e data e hora têm melhor desempenho do que as tabelas compartilhadas por data. Quando você cria tabelas nomeadas por data, o BigQuery precisa manter uma cópia do esquema e dos metadados para cada tabela desse tipo. Além disso, quando as tabelas nomeadas por data são usadas, pode ser exigido que o BigQuery verifique permissões para cada tabela consultada. Essa prática também sobrecarrega a consulta e afeta o desempenho dela. A prática recomendada é usar tabelas particionadas por data, carimbo de data/hora e data e hora, em vez de tabelas fragmentadas por data.

Comparação entre opções de particionamento

Na tabela a seguir, comparamos tabelas fragmentadas e tabelas particionadas por data, carimbo de data/hora e data e hora.

Capacidade Tabelas fragmentadas Tabelas particionadas por tempo de processamento Tabelas particionadas
Método de particionamento Nenhum: fragmentar tabelas e consultá-las usando um operador UNION pode simular o particionamento. Particionamento com base na data de ingestão ou chegada dos dados. As informações de partição podem ser referenciadas com uma pseudocoluna. Particionamento com base em dados em uma coluna TIMESTAMP, DATE ou DATETIME especificada.

Para tabelas particionadas por hora, apenas colunas TIMESTAMP e DATETIME são compatíveis com particionamento.
Identificadores de partição Nenhum É possível usar qualquer data válida entre 01/01/0001 e 31/12/9999, mas as instruções DML não podem corresponder a datas anteriores a 01/01/1970 ou posteriores a 31/12/2159. Uma entrada válida da coluna DATE, TIMESTAMP ou DATETIME vinculada. No momento, os valores de data anteriores a 01/01/1960 e posteriores a 31/12/2159 são colocados em uma partição UNPARTITIONED compartilhada. Os valores NULL residem em uma partição NULL explícita.

Os identificadores de particionamento precisam seguir os seguintes formatos:
  • yyyyMMddHH para particionamento por hora.
  • yyyyMMdd para particionamento por dia.
  • yyyyMM para particionamento por mês.
  • yyyy para particionamento por ano.
Limitação de dados verificados Faz referência apenas aos fragmentos necessários e limita os dados, excluindo colunas desnecessárias da consulta. Usa a pseudocoluna _PARTITIONTIME para eliminar partições. Usa filtros de predicado na coluna de particionamento.
Número de partições O número de tabelas é irrestrito, mas as consultas só podem fazer referência a até 1.000 tabelas. Até 4.000 partições. Até 4.000 partições.
Operações de atualização O limite é de 1.000 atualizações por dia. Uma operação individual pode estar comprometida com uma única partição. A partição mais recente (por padrão), ou uma especificada usando um decorador de partição, como [TABLE]$[DATE]. Uma operação individual pode enviar dados para até 2.000 partições distintas.
Inserções de streaming É possível fazer streaming para qualquer tabela de fragmentos, mas é necessário especificar manualmente o fragmento. Inicialmente, os dados são colocados na partição UNPARTITIONED e extraídos para a data atual por padrão. Usando sufixos de partição, também é possível fazer streaming diretamente para partições dos últimos 31 dias e próximos 16 dias em relação à data atual, com base na hora UTC atual. É possível fazer streaming de dados dos últimos cinco anos e do próximo ano. Os dados fora desse intervalo são rejeitados. Inicialmente, os dados nesse intervalo são colocados na partição UNPARTITIONED. Quando há dados não particionados suficientes, o BigQuery particiona os dados automaticamente.
Avaliação do fuso horário Definido por semântica de usuários UTC UTC

Tabelas particionadas por intervalo de números inteiros

O BigQuery permite o uso de tabelas particionadas com base em uma coluna INTEGER específica, com sua escolha de valores de início, fim e intervalo. As consultas nas tabelas particionadas por intervalo de números inteiros podem especificar filtros de predicado com base na coluna de particionamento para reduzir a quantidade de dados verificados.

Para criar uma tabela particionada por intervalo de números inteiros, forneça:

  • a coluna usada para criar as partições por intervalo de números inteiros;
  • o início do particionamento de intervalo, inclusivo;
  • o fim do particionamento de intervalo, exclusivo;
  • o intervalo de cada intervalo dentro da partição.

Os valores que estão fora do intervalo da tabela ficam na partição UNPARTITIONED.

Por exemplo, se criar uma partição por intervalo de números inteiros com os seguintes valores:

Argumento Valor
column name customer_id
start 0
end 100
interval 10

A tabela será particionada na coluna customer_id em intervalos de 10. Os valores de 0 a 9 estarão em uma partição, os valores de 10 a 19 em outra partição, assim por diante, e os valores 90 a 99 estarão em outra partição. Valores fora do intervalo de 0 a 99 (como -1 ou 100) estarão na partição UNPARTITIONED. Os valores nulos estarão na partição NULL.

Ao criar tabelas particionadas por intervalo de números inteiros, duas partições especiais são criadas:

  • __NULL__: representa linhas com valores NULL na coluna de particionamento.
  • __UNPARTITIONED__: representa dados que estão fora do intervalo permitido de início de números inteiros.

Com exceção das partições __NULL__ e __UNPARTITIONED__, todos os dados na coluna de particionamento estão dentro do intervalo do início de números inteiros. Isso permite que uma consulta determine quais partições não contêm dados que satisfaçam às condições do filtro. As consultas que filtram dados na coluna de particionamento podem restringir valores e eliminar partições desnecessárias.

O limite de número de intervalos possíveis entre os valores inicial e final é 10.000. No entanto, o número de intervalos com dados é limitado a 4.000 partições por tabela, já que cada variação é uma partição.

Particionamento por intervalo de números inteiros versus clustering

O particionamento por intervalo de números inteiros e o clustering podem melhorar o desempenho e reduzir o custo da consulta. Eles têm diferenças e casos de uso importantes.

Use o particionamento por intervalo de números inteiros se quiser:

  • Definir explicitamente os intervalos usados para particionar a tabela. Especificar como os dados serão particionados e quais dados estarão em cada partição.

  • Saber o custo da consulta antes da execução. A remoção da partição é feita antes da execução da consulta. Assim, é possível conseguir o custo da consulta após a remoção do particionamento por meio da 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.

  • Endereçar uma partição, por exemplo, quando quiser carregar dados em uma partição específica ou excluir dados de uma partição específica.

Use o clustering nos seguintes casos:

  • Se não se importar como os dados serão agrupados em cluster, contanto que consiga uma possível melhoria de desempenho e redução de custos. O BigQuery descobrirá automaticamente como os dados precisam ser agrupados para otimizar o desempenho e o custo.

  • Se precisar de mais de 4.000 partições. O BigQuery tem um limite de 4.000 partições para uma tabela particionada. Não há limite para o número de clusters em uma tabela.

Observe que é possível particionar e agrupar em cluster na mesma coluna de números inteiros para ter os benefícios de ambos. Os dados serão primeiro particionados de acordo com os intervalos de números inteiros especificados. Em cada intervalo, se o volume de dados for grande o suficiente, os dados também serão agrupados em clusters. 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.

Como usar require_partitioning_filter

Com o lançamento do particionamento por intervalo de números inteiros, o BigQuery agora é compatível com vários tipos de particionamento:

  • Tempo de processamento
  • Data, carimbo de data/hora e data e hora
  • Intervalo de números inteiros

Para simplificar a API do BigQuery, migramos o parâmetro require_partitioning_filter do nível do tipo de particionamento para o nível da tabela. Para compatibilidade com versões anteriores do particionamento de data, carimbo de data/hora e data e hora, o require_partitioning_filter ainda é compatível com o nível da partição. Ele também pode ser especificado no nível da tabela. Para particionamento por intervalo de números inteiros, é possível especificar require_partitioning_filter somente no nível da tabela. A ferramenta de linha de comando bq já usa a opção de nível de tabela. Portanto, não há nenhuma alteração na forma como você usa o comando bq. Se usar a API do BigQuery, será necessário usar a opção require_partitioning_filter no nível da tabela.

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:

Consulte Cotas e limites para mais informações sobre esse assunto.

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:

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