Como consultar tabelas particionadas
Para consultar tabelas particionadas:
- Como usar o Console do Cloud
- Use o comando
bq query
da ferramenta de linha de comandobq
. - 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.
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
.
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.