Como consultar tabelas particionadas

Como consultar tabelas particionadas

Para consultar tabelas particionadas:

• Como usar o Console do Cloud
• Use o comando bq query da ferramenta de linha de comando bq.
• chame o método de API jobs.insert e configure um job de consulta;
• use bibliotecas de cliente.

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

Permissões necessárias

Para consultar uma tabela, você precisa ter, no mínimo, as permissões bigquery.tables.getData.

Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.tables.getData:

• bigquery.dataViewer
• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

Além disso, quando um usuário tem permissões bigquery.datasets.create e cria um conjunto de dados, ele recebe o acesso bigquery.dataOwner ao conjunto. O acesso bigquery.dataOwner permite que o usuário consulte tabelas e visualizações no conjunto de dados.

Você também precisa receber as permissões bigquery.jobs.create para executar jobs de consulta. Os seguintes papéis predefinidos do IAM incluem as permissões bigquery.jobs.create:

• bigquery.user
• bigquery.jobUser
• bigquery.admin

Para mais informações sobre papéis e permissões do IAM no BigQuery, consulte Controle de acesso.

Pseudocolunas de tabela particionada por tempo de ingestão

Quando você cria uma tabela particionada por tempo de ingestão, duas pseudocolunas são adicionadas a ela: _PARTITIONTIME e _PARTITIONDATE. A pseudocoluna _PARTITIONTIME contém um carimbo de data/hora baseado em data para os dados que são carregados na tabela. A pseudocoluna _PARTITIONDATE contém uma representação de data. Os dois nomes das pseudocolunas são reservados, o que significa que não é possível criar uma coluna com nenhum dos nomes em nenhuma das suas tabelas.

_PARTITIONTIME e _PARTITIONDATE estão disponíveis somente nas tabelas particionadas por tempo de ingestão. As tabelas particionadas não têm pseudocolunas. Para mais informações sobre como consultar tabelas particionadas, confira o artigo relacionado.

Pseudocoluna _PARTITIONTIME

A pseudocoluna _PARTITIONTIME contém um carimbo de data/hora com base no fuso horário UTC que representa o número de microssegundos desde o início do período UNIX. Por exemplo, se os dados tiverem sido anexados a uma tabela em 15 de abril de 2016, às 08:15:00 UTC, todas as linhas de dados anexadas nesse dia terão a coluna _PARTITIONTIME que contém um dos seguintes valores: + TIMESTAMP("2016-04-15 08:00:00") para tabelas particionadas por hora. + TIMESTAMP("2016-04-15") para tabelas particionadas por dia. + TIMESTAMP("2016-04-01") para tabelas particionadas por mês. + TIMESTAMP("2016-01-01") para tabelas particionadas por ano.

Para consultar 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

Em que:

• column é o nome de uma coluna para consulta. É possível especificar várias colunas como uma lista separada por vírgulas;
• dataset é o conjunto de dados que contém a tabela particionada;
• table é a tabela particionada.

Os dados no buffer de streaming têm valores NULL na coluna _PARTITIONTIME.

Pseudocoluna _PARTITIONDATE

A pseudocoluna _PARTITIONDATE contém a data em UTC correspondente ao valor na pseudocoluna _PARTITIONTIME. Essa coluna não é compatível com tabelas particionadas por hora, mês ou ano.

Para consultar a pseudocoluna _PARTITIONDATE, use um alias. Por exemplo, a consulta a seguir seleciona _PARTITIONDATE atribuindo o alias pd à pseudocoluna:

SELECT
  _PARTITIONDATE AS pd,
  column
FROM
  dataset.table

Em que:

• column é o nome de uma coluna para consulta. É possível especificar várias colunas como uma lista separada por vírgulas;
• dataset é o conjunto de dados que contém a tabela particionada;
• table é a tabela particionada.

Os dados no buffer de streaming têm valores NULL na coluna _PARTITIONDATE.

Como consultar tabelas particionadas por tempo de ingestão usando pseudocolunas

Ao consultar dados em tabelas particionadas por tempo de ingestão, você faz referência a determinadas partições especificando os valores nas pseudocolunas _PARTITIONTIME ou _PARTITIONDATE. Exemplo:

• _PARTITIONTIME >= "2018-01-29 00:00:00" AND _PARTITIONTIME < "2018-01-30 00:00:00"
• _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

ou

• _PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"
• _PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'

Como limitar partições consultadas usando pseudocolunas

Use as pseudocolunas _PARTITIONTIME e _PARTITIONDATE para limitar o número de partições verificadas durante uma consulta. Isso também é 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, reduzindo o custo de análise sob demanda. Em geral, a remoção de partição ajuda a reduzir o custo da consulta quando os filtros consistem em expressões constantes, que podem ser avaliadas no início da consulta.

Atenção: alguns usos das pseudocolunas _PARTITIONTIME e _PARTITIONDATE não limitam o número de partições verificadas. Para mais informações, consulte Como remover (limitar) partições. Para exemplos de consultas que incluem as pseudocolunas, mas não limitam o número de partições verificadas, consulte Consultas de pseudocolunas que verificam todas as partições.

Por exemplo, a consulta a seguir verifica somente as partições entre 1 e 2 de janeiro de 2016 na tabela particionada:

_PARTITIONTIME

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

_PARTITIONDATE

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONDATE BETWEEN '2016-01-01'
  AND '2016-01-02'

Exemplos de remoção de partição

Este exemplo demonstra a limitação do número de partições verificadas usando um filtro de pseudocoluna em uma subconsulta:

_PARTITIONTIME

SELECT
  column1,
  column2
FROM (
  SELECT
    column1,
    column2
  FROM
    dataset.table
  WHERE
    _PARTITIONTIME = TIMESTAMP('2016-03-28')) t1
CROSS JOIN
  dataset.table t2
WHERE
  t1.column2 = "one"

_PARTITIONDATE

SELECT
  column1,
  column2
FROM (
  SELECT
    column1,
    column2
  FROM
    dataset.table
  WHERE
    _PARTITIONDATE = '2016-03-28') t1
CROSS JOIN
  dataset.table t2
WHERE
  t1.column2 = "one"

A consulta a seguir limita as partições consultadas com base na primeira condição de filtro da cláusula WHERE. No entanto, a segunda condição de filtro na cláusula WHERE não limita as partições consultadas porque usa valores de tabela, que são dinâmicos.

_PARTITIONTIME

SELECT
  column
FROM
  dataset.table2
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

_PARTITIONDATE

SELECT
  column
FROM
  dataset.table2
WHERE
  _PARTITIONDATE BETWEEN '2017-01-01' AND '2017-03-01'
  AND _PARTITIONDATE = (SELECT MAX(date) from dataset.table1)

Consultas de pseudocolunas que verificam todas as partições

Os exemplos a seguir usam pseudocolunas, mas verificam todas as partições em uma tabela particionada por unidade de tempo.

No SQL legado, o filtro _PARTITIONTIME funciona somente quando está especificado o mais perto possível do nome da tabela. Por exemplo, a consulta a seguir verifica todas as participações na table1, independentemente da presença do filtro _PARTITIONTIME:

#legacySQL
# Scans all partitions on t1
SELECT
  t1.field1,
  t2.field1
FROM
  dataset.table1 t1
CROSS JOIN
  dataset.table2 t2
WHERE
  table1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field1 = "one"

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 e o BigQuery não pode determinar antecipadamente quais partições selecionar. Para mais informações, consulte Como remover (limitar) partições.

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

Melhor desempenho com pseudocolunas

Para aprimorar o desempenho de uma consulta, use a própria pseudocoluna _PARTITIONTIME no lado esquerdo de uma comparação. Por exemplo, nas consultas a seguir, o mesmo volume de dados é processado, mas o desempenho no segundo exemplo é melhor.

Exemplo 1: a consulta a seguir pode ser mais lenta porque combina o valor da pseudocoluna com outras operações no filtro WHERE.

SQL padrão

#standardSQL
/* Can be slower */
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")

SQL legado

#legacySQL
/* Can be slower */
SELECT
  field1
FROM
  dataset.table1
WHERE
  DATE_ADD(_PARTITIONTIME, 5, "DAY") > TIMESTAMP("2016-04-15")

Exemplo 2: o desempenho pode ser melhor na consulta a seguir devido à inserção da própria pseudocoluna no lado esquerdo da comparação do filtro.

SQL padrão

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

SQL legado

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")

Na segunda consulta, em que está presente _PARTITIONTIME no lado esquerdo do operador de comparação >, é possível notar um desempenho melhor do que na primeira, dependendo do tamanho da tabela. Como o mesmo volume de dados é processado em ambas as consultas, o número de bytes cobrados é o mesmo nos dois casos.

Como consultar tabelas particionadas por tempo de ingestão usando uma tabela de caracteres curinga

Além de usar as pseudocolunas para limitar o número de partições verificadas durante uma consulta, também é possível usar as pseudocolunas para consultar um intervalo de tabelas particionadas usando uma tabela de caracteres curinga. Para informações sobre como usar uma tabela de caracteres curinga com tabelas particionadas, consulte Como verificar um intervalo de tabelas particionadas usando _PARTITIONTIME.

Como consultar tabelas particionadas por tempo de ingestão usando fuso horário

O valor de _PARTITIONTIME é baseado na data do UTC quando o campo é preenchido, o que significa que as partições são divididas com base nas 12h do UTC. Se você quiser consultar dados com base em um fuso horário diferente do UTC, será necessário escolher uma das opções a seguir antes de começar a carregar dados na tabela.

Há duas maneiras de consultar dados em uma tabela particionada usando um fuso horário personalizado diferente do UTC. Crie uma coluna separada de carimbo de data/hora ou use decoradores de partição para carregar dados em uma partição específica.

Caso opte por uma coluna de carimbo de data/hora, use o particionamento padrão baseado no UTC e leve em conta as diferenças de fuso horário nas consultas SQL. Ou então, se preferir agrupar as partições por um fuso horário diferente do UTC, use os decoradores de partição para carregar os dados nessas partições com base em outro fuso horário.

Como consultar fusos horários usando uma coluna de carimbo de data/hora

Para ajustar o fuso horário usando um carimbo de data/hora, crie uma coluna separada para armazenar o carimbo de data/hora que você usará para referenciar as linhas pela hora ou pelo minuto.

Para consultar dados com base em um fuso horário diferente do UTC, use a pseudocoluna _PARTITIONTIME e a coluna de carimbo de data/hora personalizado. Com _PARTITIONTIME, a verificação da tabela é limitada às partições relevantes e, com o carimbo de data/hora personalizado, o resultado é restrito ao seu fuso horário. Por exemplo, para consultar os dados de uma tabela particionada (mydataset.partitioned_table) com um campo de carimbo de data/hora MY_TIMESTAMP_FIELD para dados adicionados à tabela entre 2016-05-01 12:00:00 PST e 2016-05-05 14:00:00 PST:

SQL padrão

#standardSQL
SELECT
  field1
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND TIMESTAMP_ADD(MY_TIMESTAMP_FIELD, INTERVAL 8 HOUR) BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

SQL legado

#legacySQL
SELECT
  field1
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND DATE_ADD(MY_TIMESTAMP_FIELD, 8, 'HOUR') BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

Como criar uma visualização usando pseudocolunas de tabela particionada por tempo de ingestão

Para limitar a quantidade de dados lidos por uma consulta a um conjunto de partições, crie uma visualização contendo um filtro na pseudocoluna _PARTITIONTIME ou _PARTITIONDATE. Por exemplo, a seguinte consulta pode ser usada para criar uma visualização que inclua apenas os dados dos últimos sete dias de uma tabela chamada dataset.partitioned_table:

SQL padrão

#standardSQL
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);

SQL legado

#legacySQL
SELECT
  *
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000))
  AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()));

Consulte Como criar visualizações.

Partição _UNPARTITIONED_ de tabelas particionadas por tempo de processamento

A partição __UNPARTITIONED__ retém temporariamente dados que são transmitidos para uma tabela particionada enquanto estão no buffer de streaming. 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. Para mais informações, consulte Como fazer streaming em tabelas particionadas.

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

Em que:

• column é o nome de uma coluna para consulta. É possível especificar várias colunas como uma lista separada por vírgulas;
• dataset é o conjunto de dados que contém a tabela particionada;
• table é a tabela particionada.

Como consultar tabelas particionadas

Tabelas particionadas com base em uma coluna TIMESTAMP, DATE, DATETIME ou INTEGER não têm pseudocolunas. Para limitar o número de partições verificadas ao consultar tabelas particionadas, use um filtro de predicado (uma cláusula WHERE). Os filtros na coluna de particionamento serão usados para remover as partições e reduzir o custo da consulta.

As tabelas particionadas por hora, mês e ano só podem ser consultadas por meio do SQL padrão.

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. Para tabelas particionadas por tempo de processamento, esta será a pseudocoluna _PARTITIONTIME ou _PARTITIONDATE. 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 = "foo"
    WHERE partition_id = "foo" AND f = "bar"

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

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.

Se você não ativar a opção Exigir filtro de partição ao criar uma tabela particionada, será possível atualizá-la para adicionar a opção.

Como remover partições (limitação)

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, WHERE t1.ts=CURRENT_TIMESTAMP(), contém uma expressão constante:

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 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:

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 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

Próximas etapas

• Para ter uma visão geral das tabelas particionadas, consulte Introdução às tabelas particionadas.
• Para saber mais sobre tabelas particionadas por tempo de ingestão, consulte Como criar e usar tabelas particionadas por tempo de ingestão.
• Para saber mais sobre tabelas particionadas por tempo baseadas em colunas, consulte Como criar e usar tabelas particionadas por data, carimbo de data/hora e data e hora.
• Para saber mais sobre tabelas particionadas por intervalo de números inteiros, consulte Como criar e usar tabelas particionadas por intervalo de números inteiros.