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.

Visão geral

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

Usar uma expressão de filtro constante

Para limitar as partições verificadas em uma consulta, use uma expressão constante no filtro. Se você usar expressões dinâmicas no filtro de consulta, o BigQuery precisará verificar todas as partições.

Por exemplo, a consulta a seguir remove partições porque o filtro, contém uma expressão constante:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON t1.id_field = t2.field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

No entanto, a consulta a seguir não remove partições, porque o filtro, WHERE t1.ts = (SELECT timestamp from table where key = 2), não é uma expressão constante; ela depende dos valores dinâmicos dos campos timestamp e key:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

Isolar a coluna da partição no filtro

Isolar a coluna da partição ao expressar um filtro. Os filtros que exigem dados de vários campos para calcular não removem partições. Por exemplo, uma consulta com uma comparação de data que usa a coluna de particionamento e um segundo campo ou consultas que contêm algumas concatenações de campo não removerão partições.

Por exemplo, o filtro a seguir não remove partições porque requer um cálculo baseado no campo de particionamento ts e um segundo campo ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

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.

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

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

No entanto, WHERE (partition_id = "20221231" OR f = "20221130") não é suficiente.

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