Consultar tabelas particionadas

Neste documento, você verá algumas considerações específicas sobre como consultar tabelas particionadas no BigQuery.

Para informações gerais sobre como executar consultas no BigQuery, consulte Como executar consultas interativas e em lote.

Informações gerais

Se uma consulta usar um filtro qualificado no valor da coluna de particionamento, o BigQuery poderá verificar as partições que correspondem ao filtro e pular as partições restantes. Esse processo é chamado de remoção de partições.

A remoção de partição é o mecanismo que o BigQuery usa para eliminar partições desnecessárias da verificação de entrada. As partições removidas não são incluídas ao calcular os bytes verificados pela consulta. Em geral, a remoção de partições ajuda a reduzir o custo da consulta.

Os comportamentos de remoção variam de acordo com os diferentes tipos de particionamento, então é possível conferir a diferença nos bytes processados ao consultar tabelas particionadas de formas diferentes, mas que são idênticas. Para estimar quantos bytes uma consulta irá processar, faça uma simulação.

Criar uma tabela particionada por coluna de unidade de tempo

Para remover partições ao consultar uma tabela particionada por coluna de tempo, inclua um filtro na coluna de particionamento.

No exemplo a seguir, suponha que dataset.table esteja particionado na coluna transaction_date. A consulta de exemplo remove as datas antes de 2016-01-01.

SELECT * FROM dataset.table
WHERE transaction_date >= '2016-01-01'

Consultar uma tabela particionada por tempo de ingestão

As tabelas particionadas por tempo de processamento contêm uma pseudocoluna chamada _PARTITIONTIME, que é a coluna de particionamento. O valor da coluna é o tempo de ingestão do UTC de cada linha truncado para o limite de partição (como por hora ou dia), como um valor TIMESTAMP.

Por exemplo, se você anexar dados em 15 de abril de 2021, às 08:15:00 UTC, a coluna _PARTITIONTIME dessas linhas conterá os seguintes valores:

  • Tabela particionada por hora: TIMESTAMP("2021-04-15 08:00:00")
  • Tabela particionada por dia: TIMESTAMP("2021-04-15")
  • Tabela particionada por mês: TIMESTAMP("2021-04-01")
  • Tabela particionada anualmente: TIMESTAMP("2021-01-01")

Se a granularidade da partição for diária, a tabela também conterá uma pseudocoluna chamada _PARTITIONDATE. O valor é igual a _PARTITIONTIME truncado para um valor DATE.

Esses dois nomes de pseudocolunas são reservados. Não é possível criar uma coluna com nenhum dos nomes em nenhuma das suas tabelas.

Para remover partições, filtre uma dessas colunas. Por exemplo, a consulta a seguir verifica somente as partições entre 1 e 2 de janeiro de 2016:

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

Para selecionar a pseudocoluna _PARTITIONTIME, use um alias. Por exemplo, a consulta a seguir seleciona _PARTITIONTIME atribuindo o alias pt à pseudocoluna:

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

Para tabelas particionadas por dia, é possível selecionar a pseudocoluna _PARTITIONDATE da mesma maneira:

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

As pseudocolunas _PARTITIONTIME e _PARTITIONDATE não são retornadas por uma instrução SELECT *. Você precisa selecioná-las explicitamente:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

Processar fusos horários em tabelas particionadas por tempo de ingestão

O valor de _PARTITIONTIME é baseado na data do UTC quando o campo é preenchido. Se você quiser consultar dados com base em um fuso horário diferente do UTC, escolha uma das seguintes opções:

  • Ajuste as diferenças de fuso horário nas suas consultas SQL.
  • Use decoradores de partição para carregar dados em partições de tempo de ingestão específicas, com base em um fuso horário diferente do UTC.

Melhor desempenho com pseudocolunas

Para aprimorar o desempenho de uma consulta, use a própria pseudocoluna _PARTITIONTIME no lado esquerdo de uma comparação.

No exemplo abaixo, as seguintes consultas são equivalentes: Dependendo do tamanho da tabela, a segunda consulta pode ter um desempenho melhor, já que coloca o _PARTITIONTIME sozinha no lado esquerdo do operador >. Ambas as consultas processam a mesma quantidade de dados.

-- Might be slower.
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15");

-- Often performs better.
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY);

Para limitar as partições verificadas em uma consulta, use uma expressão constante no filtro. A consulta a seguir limita as partições removidas com base na primeira condição do filtro na cláusula WHERE. No entanto, a segunda condição de filtro não limita as partições verificadas porque usa valores de tabela, que são dinâmicos.

SELECT
  column
FROM
  dataset.table2
WHERE
  -- This filter condition limits the scanned partitions:
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  -- This one doesn't, because it uses dynamic table values:
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

Para limitar as partições verificadas, não inclua outras colunas em um filtro _PARTITIONTIME. Por exemplo, a consulta a seguir não limita as partições verificadas, porque field1 é uma coluna na tabela.

-- Scans all partitions of table2. No pruning.
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');

Se você costuma consultar um intervalo específico de vezes, crie uma visualização que filtre na pseudocoluna _PARTITIONTIME. Por exemplo, a instrução a seguir cria uma visualização que inclui apenas os sete dias de dados mais recentes de uma tabela chamada dataset.partitioned_table:

-- This view provides pruning.
CREATE VIEW dataset.past_week AS
  SELECT *
  FROM
    dataset.partitioned_table
  WHERE _PARTITIONTIME BETWEEN
    TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 * 24 HOUR), DAY)
    AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY);

Consulte Como criar visualizações.

Consultar uma tabela particionada por intervalo de números inteiros

Para remover partições ao consultar uma tabela particionada por intervalo de números inteiros, inclua um filtro na coluna de particionamento de números inteiros.

No exemplo a seguir, suponha que dataset.table é uma tabela particionada de variação em números inteiros com uma especificação de particionamento de customer_id:0:100:10. A consulta de exemplo verifica as três partições que começam com 30, 40 e 50.

SELECT * FROM dataset.table
WHERE customer_id BETWEEN 30 AND 50

+-------------+-------+
| customer_id | value |
+-------------+-------+
|          40 |    41 |
|          45 |    46 |
|          30 |    31 |
|          35 |    36 |
|          50 |    51 |
+-------------+-------+

Atualmente, a remoção de partição não é suportada para funções em uma coluna particionada de variação em número inteiro. Por exemplo, a consulta a seguir verifica toda a tabela.

SELECT * FROM dataset.table
WHERE customer_id + 1 BETWEEN 30 AND 50

Consultar dados no armazenamento otimizado para gravação

A partição __UNPARTITIONED__ retém temporariamente dados que são transmitidos para uma tabela particionada enquanto estão no armazenamento otimizado para gravação. Os dados que são transmitidos diretamente para uma partição específica de uma tabela particionada não usam a partição __UNPARTITIONED__. Em vez disso, os dados são transmitidos diretamente para a partição.

Os dados no armazenamento otimizado para gravação têm valores NULL nas colunas _PARTITIONTIME e _PARTITIONDATE.

Para consultar dados na partição __UNPARTITIONED__, use a pseudocoluna _PARTITIONTIME com o valor NULL. Exemplo:

SELECT
  column
FROM dataset.table
WHERE
  _PARTITIONTIME IS NULL

Para mais informações, consulte Como fazer streaming em tabelas particionadas.

Práticas recomendadas para remoção de partição

Esta seção descreve as práticas recomendadas para escrever consultas que usam a remoção de partição para otimizar o desempenho e reduzir o custo.

Usar uma expressão de filtro constante

Para limitar as partições verificadas em uma consulta, filtre a coluna de particionamento usando uma expressão constante, em vez de uma expressão dinâmica.

A consulta a seguir remove partições:

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = CURRENT_TIMESTAMP()

Em comparação, a consulta a seguir não remove partições, porque o predicado, WHERE t1.ts = (SELECT timestamp FROM table3 WHERE key = 2), não é uma expressão constante. Essa consulta compara a coluna de particionamento a um valor dinâmico, o que impede a remoção de partição.

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = (SELECT timestamp FROM table3 WHERE key = 2)

Além disso, uma consulta com os seguintes predicados não remove partições porque exige um cálculo com base em uma segunda coluna de tabela não constante ts2 ou duration:

WHERE ts >= ts2

WHERE ts < CURRENT_TIMESTAMP() - duration

Isolar a coluna de particionamento ou usar funções compatíveis

Para remover partições, as condições de filtro precisam ser estruturadas para que o BigQuery possa determinar quais partições verificar sem ler os dados da tabela. Para isso, isole a coluna de particionamento em um lado de um operador de comparação ou encapsule a coluna apenas em uma função integrada compatível. É possível usar a simulação para verificar se a remoção de partição é compatível com sua consulta específica.

As seguintes funções integradas na coluna de particionamento oferecem suporte à remoção de partição, se os argumentos adicionais forem constantes:

Outras funções e operações matemáticas complexas exigirão uma verificação completa da tabela.

Exemplos

As consultas a seguir mostram exemplos de predicados que oferecem suporte à remoção de partição.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour = '2025-03-30 12:00:00';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-03-30'
  AND datehour < TIMESTAMP_ADD('2025-03-30', INTERVAL 1 DAY);
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE DATE(datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(DATE FROM datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE CAST(datehour AS DATE) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-01-01' AND datehour < '2025-02-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_TRUNC(datehour, MONTH) >= '2025-04-01'
  AND TIMESTAMP_TRUNC(datehour, MONTH) < '2025-07-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_DIFF(datehour, '2025-01-01', DAY) < 1;
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_ADD(datehour, INTERVAL 1 DAY) < '2025-01-03';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_SUB(datehour, INTERVAL 1 DAY) < '2025-01-01';

A consulta a seguir pula todas as partições porque o predicado não corresponde a nenhuma linha.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(YEAR FROM datehour) = 1900;

A consulta a seguir seleciona o primeiro dia de cada mês na tabela e oferece suporte à remoção de partição. 

SELECT COUNT(*) FROM bigquery-public-data.wikipedia.pageviews_2025
WHERE DATE(datehour) IN UNNEST(GENERATE_DATE_ARRAY(
  DATE_TRUNC(CURRENT_DATE(), YEAR),
  DATE(DATE_TRUNC(CURRENT_DATE(), YEAR) + INTERVAL 1 YEAR - INTERVAL 1 DAY),
  INTERVAL 1 MONTH
))

As consultas com os seguintes predicados não removem partições porque manipulam a coluna de particionamento com funções não compatíveis:

WHERE FORMAT_DATE('%Y-%m-%d %H', ts) = '2025-03-28 20';

WHERE EXTRACT(MONTH FROM ts) = 3 AND EXTRACT(HOUR FROM ts) = 20

Da mesma forma, uma consulta com o seguinte predicado não remove partições porque manipula a coluna de particionamento com uma operação aritmética: 

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

Para ativar a remoção de partição, é necessário reescrever a expressão isolando a coluna de particionamento ts das funções ou operações aritméticas não compatíveis. Para intervalos de tempo, use >= e < para capturar o intervalo exato. Para aritmética, mova a operação para o outro lado da comparação.

A consulta a seguir permite a remoção de partição isolando a coluna de particionamento ts para um intervalo de tempo:

WHERE ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

A consulta a seguir permite a remoção de partição isolando a coluna de particionamento da operação aritmética:

WHERE ts > CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Filtrar em várias colunas

Um predicado na coluna de particionamento em uma consulta não restringe o que mais você pode filtrar. É possível incluir predicados em outras colunas na mesma cláusula WHERE, e a remoção de partição ainda ocorrerá, desde que a condição que avalia a coluna de particionamento siga as práticas recomendadas. Observe que AND é importante no exemplo a seguir. Se AND for alterado para OR, a remoção de partição não funcionará, porque, mesmo que uma partição não corresponda ao predicado na coluna de particionamento, ela ainda não poderá ser removida. Os dados nessas partições com meter_id = 1234 ainda se qualificam para a consulta.

Os predicados não precisam ser escritos em uma ordem específica. Na consulta de exemplo a seguir, supondo o particionamento na coluna ts, a remoção de partição ainda ocorre, independentemente do posicionamento do predicado.

WHERE meter_id = 1234
  AND ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

Exigir um filtro de partição em consultas

Ao criar uma tabela particionada, é possível exigir o uso de filtros de predicado. Basta ativar a opção Exigir filtro de partição. Quando essa opção é usada, as tentativas de consultar a tabela particionada sem especificar uma cláusula WHERE produzem o erro a seguir: .

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

Esse requisito também se aplica a consultas em visualizações e visualizações materializadas que fazem referência à tabela particionada.

Observação: deve haver pelo menos um predicado que só faça referência a uma coluna de particionamento para que o filtro seja considerado qualificado para a eliminação de partição. Para uma tabela particionada na coluna partition_id com uma coluna adicional f no esquema, as duas cláusulas WHERE a seguir atendem ao requisito:

WHERE partition_id = "20221231"

WHERE partition_id = "20221231" AND f = "20221130"

No entanto, o seguinte não é suficiente e resultará em um erro:

WHERE partition_id = "20221231" OR f = "20221130"

Para tabelas particionadas por tempo de processamento, use a pseudocoluna _PARTITIONTIME ou _PARTITIONDATE.

Para mais informações sobre como adicionar a opção Exigir filtro de partição ao criar uma tabela particionada, consulte Como criar tabelas particionadas. Também é possível atualizar essa configuração em uma tabela existente.

A seguir