Consultar tabelas particionadas

Este documento descreve algumas considerações específicas para consultar tabelas particionadas no BigQuery.

Para informações gerais sobre a execução de consultas no BigQuery, consulte o artigo Executar consultas interativas e em lote.

Vista geral

Se uma consulta usar um filtro de qualificação no valor da coluna particionada, o BigQuery pode analisar as partições que correspondem ao filtro e ignorar as partições restantes. Este processo é denominado corte de partições.

O corte de partições é o mecanismo que o BigQuery usa para eliminar partições desnecessárias da análise de entradas. As partições cortadas não são incluídas no cálculo dos bytes analisados pela consulta. De um modo geral, o corte de partições ajuda a reduzir o custo das consultas.

Os comportamentos de remoção variam para os diferentes tipos de particionamento, pelo que pode ver uma diferença nos bytes processados quando consulta tabelas que são particionadas de forma diferente, mas que, de resto, são idênticas. Para estimar quantos bytes uma consulta processa, execute um teste de execução.

Consulte uma tabela particionada por colunas de unidades de tempo

Para remover partições quando consulta uma tabela particionada por colunas de unidades de tempo, inclua um filtro na coluna de partição.

No exemplo seguinte, suponha que dataset.table está particionada na coluna transaction_date. A consulta de exemplo remove datas anteriores a 2016-01-01.

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

Consulte uma tabela particionada por tempo de ingestão

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

Por exemplo, se anexar dados a 15 de abril de 2021 às 08:15:00 UTC, a coluna _PARTITIONTIME para essas linhas contém os seguintes valores:

  • Tabela particionada por hora: TIMESTAMP("2021-04-15 08:00:00")
  • Tabela particionada diariamente: TIMESTAMP("2021-04-15")
  • Tabela particionada mensalmente: TIMESTAMP("2021-04-01")
  • Tabela particionada anualmente: TIMESTAMP("2021-01-01")

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

Ambos os nomes de pseudocolunas estão reservados. Não pode criar uma coluna com qualquer um dos nomes em nenhuma das suas tabelas.

Para remover partições, filtre por qualquer uma destas colunas. Por exemplo, a seguinte consulta analisa apenas as partições entre as datas 1 de janeiro de 2016 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, tem de usar um alias. Por exemplo, a seguinte consulta seleciona _PARTITIONTIME atribuindo o alias pt à pseudocoluna:

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

Para tabelas particionadas diariamente, pode selecionar a pseudocoluna _PARTITIONDATE da mesma forma:

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

As pseudocolunas _PARTITIONTIME e _PARTITIONDATE não são devolvidas por uma declaração SELECT *. Tem de selecioná-los explicitamente:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

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

O valor de _PARTITIONTIME baseia-se na data UTC em que o campo é preenchido. Se quiser consultar dados com base num 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ções para carregar dados em partições específicas da hora de carregamento, com base num fuso horário diferente do UTC.

Melhor desempenho com pseudocolunas

Para melhorar o desempenho das consultas, use a pseudocoluna _PARTITIONTIME sozinha no lado esquerdo de uma comparação.

Por exemplo, as duas consultas seguintes são equivalentes. Consoante o tamanho da tabela, a segunda consulta pode ter um melhor desempenho, porque coloca _PARTITIONTIME por si só 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 que são analisadas numa consulta, use uma expressão constante no filtro. A seguinte consulta limita as partições que são removidas com base na primeira condição de filtro na cláusula WHERE. No entanto, a segunda condição do filtro não limita as partições analisadas, porque usa valores de tabelas, 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 analisadas, não inclua outras colunas num filtro _PARTITIONTIME. Por exemplo, a consulta seguinte não limita as partições analisadas, 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 consultar frequentemente um intervalo de tempo específico, considere criar uma vista que filtre a pseudocoluna _PARTITIONTIME. Por exemplo, a seguinte declaração cria uma vista que inclui apenas os sete dias mais recentes de dados de uma tabela denominada 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);

Para obter informações sobre como criar visualizações de propriedade, consulte o artigo Criar visualizações de propriedade.

Consulte uma tabela particionada por intervalo de números inteiros

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

No exemplo seguinte, suponha que dataset.table é uma tabela particionada por intervalo de números inteiros com uma especificação de partição de customer_id:0:100:10. A consulta de exemplo analisa as três partições que começam por 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 |
+-------------+-------+

A eliminação de partições não é suportada para funções sobre uma coluna particionada de intervalo de números inteiros. Por exemplo, a consulta seguinte analisa toda a tabela.

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

Consultar dados no armazenamento otimizado para escrita

A partição __UNPARTITIONED__ contém temporariamente dados transmitidos para uma tabela particionada enquanto está no armazenamento otimizado para escrita. Os dados transmitidos diretamente para uma partição específica de uma tabela particionada não usam a partição __UNPARTITIONED__. Em alternativa, os dados são transmitidos diretamente para a partição.

Os dados no armazenamento otimizado para escrita têm valores NULL nas colunas _PARTITIONTIME e _PARTITIONDATE.

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

SELECT
  column
FROM dataset.table
WHERE
  _PARTITIONTIME IS NULL

Para mais informações, consulte o artigo Streaming para tabelas particionadas.

Práticas recomendadas para a eliminação de partições

Use uma expressão de filtro constante

Para limitar as partições que são analisadas numa consulta, use uma expressão constante no filtro. Se usar expressões dinâmicas no filtro de consulta, o BigQuery tem de analisar todas as partições.

Por exemplo, a seguinte consulta 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 seguinte consulta não remove partições, porque o filtro, WHERE t1.ts = (SELECT timestamp from table where key = 2), não é uma expressão constante; 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 de partição no filtro

Isolar a coluna de partição ao expressar um filtro. Os filtros que requerem dados de vários campos para calcular não vão remover partições. Por exemplo, uma consulta com uma comparação de datas que use a coluna de particionamento e um segundo campo, ou consultas que contenham algumas concatenações de campos não vão remover partições.

Por exemplo, o filtro seguinte não remove partições porque requer um cálculo com base no campo de partição ts e num segundo campo ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

Exija um filtro de partição nas consultas

Quando cria uma tabela particionada, pode exigir a utilização de filtros de predicados ativando a opção Exigir filtro de partição. Quando esta opção é aplicada, as tentativas de consultar a tabela particionada sem especificar uma cláusula WHERE produzem o seguinte erro:

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

Este requisito também se aplica a consultas em vistas e vistas materializadas que fazem referência à tabela particionada.

Tem de existir, pelo menos, um predicado que apenas faça referência a uma coluna de partição para que o filtro seja considerado elegível para eliminação de partições. Por exemplo, para uma tabela particionada na coluna partition_id com uma coluna adicional f no respetivo esquema, ambas as seguintes cláusulas WHERE cumprem o requisito:

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 ingestão, use a pseudocoluna _PARTITIONTIME ou _PARTITIONDATE.

Para mais informações sobre como adicionar a opção Exigir filtro de partição quando cria uma tabela particionada, consulte Criar tabelas particionadas. Também pode atualizar esta definição numa tabela existente.

O que se segue?