Como consultar tabelas particionadas

Como consultar tabelas particionadas

Para consultar tabelas particionadas:

  • use o Console do GCP ou a IU da Web clássica do BigQuery;
  • use o comando bq query da ferramenta de linha de comando;
  • chame o método da API jobs.insert e configure um job de consulta.

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

Permissões necessárias

Para consultar uma tabela particionada no nível do conjunto de dados, é necessário ter acesso de READER ao conjunto que a contém.

Em vez de usar permissões no nível do conjunto de dados, utilize um papel do IAM em nível de projeto que inclua as permissões bigquery.tables.getData. As permissões de bigquery.tables.getData são necessárias para leitura dos dados na tabela que está sendo consultada.

Todos os papéis de IAM predefinidos no nível do projeto incluem permissões bigquery.tables.getData, exceto bigquery.user, bigquery.jobUser e bigquery.metadataViewer.

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

Para mais informações sobre os papéis e as permissões do IAM no BigQuery, consulte Controle de acesso. Para mais informações sobre os papéis para conjuntos de dados, consulte este tópico.

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 informações sobre como consultar tabelas particionadas, veja Como consultar tabelas particionadas.

Pseudocoluna _PARTITIONTIME

A pseudocoluna _PARTITIONTIME contém um carimbo de data/hora com base no fuso horário UTC e que representa o número de microssegundos desde o início da era UNIX. Por exemplo, se os dados tiverem sido anexados a uma tabela em 15 de abril de 2016, todas as linhas de dados anexadas nesse dia contêm o valor TIMESTAMP("2016-04-15") na coluna _PARTITIONTIME.

Para consultar a pseudocoluna _PARTITIONTIME, você precisa usar um alias. Por exemplo, na seguinte consulta, o valor _PARTITIONTIME é selecionado por meio da atribuição do 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ários recursos como, por exemplo, 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 do UTC correspondente ao valor na pseudocoluna _PARTITIONTIME.

Para consultar a pseudocoluna _PARTITIONDATE, você precisa usar um alias. Por exemplo, na seguinte consulta, o valor _PARTITIONDATE é selecionado por meio da atribuição do 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ários recursos como, por exemplo, 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. Por 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 reduzirá o custo da consulta quando os filtros puderem ser avaliados no início da consulta sem exigir quaisquer avaliações de subconsulta ou verificação de dados.

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].[TABLE2] 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].[TABLE2] t2
WHERE
  t1.[COLUMN2] = "one"

A consulta a seguir limita algumas partições baseadas em parte da condição do filtro, _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01'). Isto não o limita com base na condição que envolve a subconsulta:

_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 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
  mydataset.table2 t1
CROSS JOIN
  mydataset.table2 t2
WHERE
  t1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field2 = "one"

Não inclua outras colunas em um filtro _PARTITIONTIME. Por exemplo, a consulta a seguir não limita as partições que são verificadas porque o field2 é uma coluna na tabela e o BigQuery não determina antecipadamente quais partições selecionar.

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

Filtros em _PARTITIONTIME que incluem subconsultas não podem ser usados para limitar o número de partições verificadas para uma tabela particionada. Por exemplo, a consulta a seguir não limita as partições verificadas na tabela mydataset.table2

# Scans all partitions of table2
SELECT
  field1
FROM
  mydataset.table2
WHERE
  _PARTITIONTIME = (SELECT MAX(timestamp) FROM mydataset.table1)

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
  mydataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")

SQL legado

#legacySQL
/* Can be slower */
SELECT
  field1
FROM
  mydataset.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
  mydataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY)

SQL legado

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  mydataset.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 08:00:00 PST e 2016-05-05 14:00:00 PST:

SQL padrão

#standardSQL
SELECT
  field1
FROM
  mydataset.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
  mydataset.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 mydataset.partitioned_table:

SQL padrão

#standardSQL
SELECT
  *
FROM
  mydataset.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
  mydataset.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 ingestão

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. Exemplo:

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE]
WHERE
  _PARTITIONTIME IS NULL

Em que:

  • [COLUMN] é o nome de uma coluna para consulta. É possível especificar vários recursos como, por exemplo, 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 ou DATE 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).

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

Expresse o filtro de predicado o mais próximo possível do identificador da tabela. As consultas complexas que exigirem a avaliação de múltiplos estágios de uma consulta para resolver o predicado (como consultas internas ou subconsultas) não removerão partições da consulta.

Por exemplo, a seguinte consulta remove partições:

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

A seguinte consulta não remove partições. Observe o uso de uma subconsulta:

#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 usando a coluna de particionamento e um segundo campo ou consultas contendo algumas concatenações de campo não removerão partições.

Por exemplo, o seguinte filtro não remove partições porque requer uma computação baseada no campo de particionamento ts e um segundo campo ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

A seguir

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

Enviar comentários sobre…

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