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 ingestão

Quando você cria uma tabela particionada por tempo de ingestão, o BigQuery carrega automaticamente dados em partições diárias baseadas em data que refletem a data de chegada ou data de ingestão. 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 ingestão, 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 carga ou de consulta.

Tabelas particionadas por data ou carimbo de data/hora

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

Cada partição em uma tabela particionada de data e carimbo de data/hora pode ser considerada um intervalo em que o início do intervalo é o início de um dia e o intervalo é um dia.

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

Ao criar tabelas particionadas de data e carimbo de data/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 de data e carimbo de data/hora versus fragmentação

Como alternativa às tabelas particionadas de data e carimbo de data/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 de data e carimbo de data/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 de data e carimbo de data/hora em vez de tabelas fragmentadas por data.

Como comparar entre opções de particionamento

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

Capacidade Tabelas fragmentadas Tabelas particionadas por tempo de ingestão 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. Particionado com base em dados em uma coluna TIMESTAMP ou DATE especificada.
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 ou TIMESTAMP 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. Valores NULL ficam em uma partição NULL explícita.
Limitação de dados digitalizados 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 Um buffer global para a tabela. Com sufixos de partição, é possível fazer streaming para partições dos últimos 31 dias e próximos 16 dias em relação à data atual, com base no horário UTC atual. Você pode fazer streaming de dados do último ano e dos próximos seis meses. Os dados fora desse intervalo são rejeitados. No processo, os dados dos últimos sete dias e próximos três dias são colocados no buffer de streaming e, em seguida, são extraídos para as partições correspondentes. Os dados fora desse período (mas dentro do intervalo de um ano, seis meses) são colocados na partição UNPARTITIONED. Quando há dados não particionados suficientes, eles são carregados nas partições correspondentes.
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 do número de intervalos possíveis entre os valores inicial e final é 100.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 e carimbo de data/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 e carimbo de data/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

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.