Como consultar várias tabelas usando uma tabela curinga
As tabelas curinga permitem consultar várias tabelas usando instruções SQL concisas. Tabelas curinga estão disponíveis apenas no SQL padrão. Para funcionalidade equivalente no SQL legado, consulte Funções de tabelas curinga.
A tabela curinga representa uma união de todas as tabelas correspondentes à expressão curinga. Por exemplo, a cláusula FROM
a seguir usa a expressão curinga gsod*
para corresponder a todas as tabelas no conjunto de dados noaa_gsod
que começam com a string gsod
.
FROM
`bigquery-public-data.noaa_gsod.gsod*`
Cada linha na tabela com caracteres curinga contém uma coluna especial, _TABLE_SUFFIX
, com
o valor correspondente ao caractere curinga.
Para mais informações sobre a sintaxe da tabelas curinga, veja Tabelas curinga na referência do SQL padrão.
Limitações
As consultas de tabela curinga estão sujeitas às seguintes limitações:
- A funcionalidade da tabela curinga não é compatível com visualizações. Se a tabela curinga corresponder a uma visualização no conjunto de dados, um erro será retornado. Isso ocorre independentemente da consulta conter ou não uma cláusula
WHERE
na pseudocoluna_TABLE_SUFFIX
para filtrar a visualização. - Atualmente, não é possível consultar dados armazenados em cache em várias tabelas com um curinga, mesmo que a opção Usar resultados armazenados em cache esteja marcada. Se você executar a mesma consulta de caracteres curinga várias vezes, haverá cobrança por cada consulta.
- As tabelas curinga são compatíveis somente com o armazenamento nativo do BigQuery. Não é possível usar caracteres curinga na consulta a uma tabela externa ou visualização.
- Não é possível usar consultas com caracteres curinga em tabelas com particionamento incompatível ou uma mistura de tabelas particionadas e não particionadas.
- Consultas em que há
instruções de linguagem de manipulação de dados
(DML) não podem ter uma tabela curinga como destino. Por exemplo, uma tabela curinga pode ser usada na cláusula
FROM
de uma consultaUPDATE
, mas não pode ser usada como o destino da operaçãoUPDATE
. - Os filtros nas pseudocolunas
_TABLE_SUFFIX
ou_PARTITIONTIME
que incluem funções JavaScript definidas pelo usuário não limitam o número de tabelas consultadas em uma tabela com caracteres curinga. - As consultas de caracteres curinga não são compatíveis com tabelas protegidas por chaves de criptografia gerenciadas pelo cliente (CMEK, na sigla em inglês).
Ao usar tabelas curinga, todas as tabelas no conjunto de dados que começam com o nome da tabela antes de
*
são verificadas mesmo que_TABLE_SUFFIX
seja usado em combinação comREGEXP_CONTAINS
e recebe uma expressão regular, como^[0-9]{2}$
. Exemplo:SELECT * FROM `my_project.my_dataset.my_table_*` WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');
Se uma única tabela verificada tiver uma incompatibilidade de esquema, ou seja, uma coluna com o mesmo nome for de um tipo diferente, a consulta falhará com o erro Não é possível ler o campo do tipo X como campo Y. : column_name. Todas as tabelas são correspondidas mesmo que você esteja usando o operador de igualdade
=
. Por exemplo, na consulta a seguir, a tabelamy_dataset.my_table_03_backup
também é verificada. Assim, a consulta pode falhar por causa de incompatibilidade de esquema. No entanto, se não houver incompatibilidade de esquema, os resultados serão provenientes da tabelamy_dataset.my_table_03
apenas, conforme esperado.SELECT * FROM
my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'
Antes de começar
- Verifique se você está usando o SQL padrão do BigQuery. Para mais informações, consulte Como alternar dialetos SQL.
- Se você estiver usando o SQL legado, consulte Funções de caracteres curinga da tabela.
- Muitos dos exemplos nesta página usam um conjunto de dados públicos extraídos da Administração Oceânica e Atmosférica Nacional (NOAA, na sigla em inglês). Para mais informações sobre os dados, consulte Resumo global de superfície dos dados climáticos diários da NOAA.
Quando usar tabelas curinga
Tabelas curinga são úteis quando um conjunto de dados contém várias tabelas com nomes parecidos e esquemas compatíveis. Normalmente, cada tabela desses conjuntos representa dados de um único dia, mês ou ano. Por exemplo, o conjunto de dados públicos hospedado pelo BigQuery, o Resumo global de superfície dos dados climáticos diários da NOAA, contém uma tabela referente a cada ano de 1929 até hoje.
Uma consulta para verificar todos os códigos de tabelas de 1929 a 1940 seria muito longa se fosse necessário indicar todas as 12 tabelas na cláusula FROM
. Neste exemplo, a maioria das tabelas foi omitida:
#standardSQL SELECT max, ROUND((max-32)*5/9,1) celsius, mo, da, year FROM ( SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL # ... Tables omitted for brevity SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1940` ) WHERE max != 9999.9 # code for missing data ORDER BY max DESC
A mesma consulta com uma tabela curinga é muito mais concisa:
#standardSQL SELECT max, ROUND((max-32)*5/9,1) celsius, mo, da, year FROM `bigquery-public-data.noaa_gsod.gsod19*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN '29' AND '40' ORDER BY max DESC
Consulta a conjuntos de tabelas usando tabelas curinga
As tabelas curinga permitem consultar várias tabelas de maneira concisa.
Por exemplo, um conjunto de dados público hospedado pelo BigQuery, o Resumo global de superfície dos dados climáticos diários da NOAA, contém uma tabela para cada ano de 1929 até o presente. Todas elas compartilham o prefixo comum gsod
seguido pelo ano com quatro dígitos. As tabelas são chamadas de gsod1929
, gsod1930
, gsod1931
etc.
Para consultar um grupo de tabelas que compartilham um prefixo comum, use o símbolo de caractere curinga (*) da tabela após o prefixo dela em sua declaração FROM
. Por exemplo, a consulta a seguir encontra a temperatura máxima informada durante a década de 1940:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
WHERE
max != 9999.9 # code for missing data
ORDER BY
max DESC
Como filtrar tabelas selecionadas usando _TABLE_SUFFIX
Para restringir uma consulta para que ela verifique somente um conjunto especificado de tabelas, use a
pseudocoluna _TABLE_SUFFIX
em uma cláusula WHERE
com uma condição que seja uma expressão constante.
A pseudocoluna _TABLE_SUFFIX
contém os valores que correspondem ao caractere curinga da tabela. Por exemplo, a
consulta de exemplo anterior, que verifica todas as tabelas referentes à década de 40, usa uma tabela
curinga para representar o último dígito do ano.
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
A pseudocoluna _TABLE_SUFFIX
correspondente contém valores no intervalo de 0
a 9
, representando as tabelas de gsod1940
a gsod1949
. Esses
valores _TABLE_SUFFIX
podem ser usados na cláusula WHERE
para filtrar por tabelas
específicas.
Por exemplo, para filtrar pela temperatura máxima nos anos de 1940 e 1944, use os valores 0
e 4
para _TABLE_SUFFIX
:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
WHERE
max != 9999.9 # code for missing data
AND ( _TABLE_SUFFIX = '0'
OR _TABLE_SUFFIX = '4' )
ORDER BY
max DESC
O uso de _TABLE_SUFFIX
pode reduzir muito o número de bytes verificados, o que ajuda a reduzir o custo de execução de suas consultas.
No entanto, os filtros em _TABLE_SUFFIX
que incluem condições sem expressões constantes não limitam o número de tabelas consultadas em uma tabela com caracteres curinga. Por exemplo, a consulta a seguir não limita as tabelas consultadas na tabela com caracteres curinga bigquery-public-data.noaa_gsod.gsod19*
, porque o filtro usa o valor dinâmico da coluna table_id
:
#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
_TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'gsod194%')
Como outro exemplo, a consulta a seguir limita a verificação com base na primeira
condição de filtro,
_TABLE_SUFFIX BETWEEN '40' and '60'
, porque é uma expressão constante.
No entanto, a consulta a seguir não limita a verificação com base na segunda condição
do filtro, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19')
+ 1) FROM
, porque é uma expressão dinâmica:bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE
'gsod194%')
#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
_TABLE_SUFFIX BETWEEN '40' AND '60'
AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'gsod194%')
Como alternativa, é possível executar duas consultas separadas. Por exemplo:
Primeira consulta:
#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'gsod194%'
Segunda consulta:
#standardSQL
# Construct the second query based on the values from the first query
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'
Estas consultas de exemplo usam a visualização INFORMATION_SCHEMA.TABLES
. Para mais informações sobre a tabela INFORMATION_SCHEMA
, consulte Como receber metadados de tabelas usando INFORMATION_SCHEMA.
Como verificar um intervalo de tabelas usando _TABLE_SUFFIX
Para verificar uma faixa de tabelas, use a pseudocoluna _TABLE_SUFFIX
junto com a cláusula BETWEEN
. Por exemplo, para encontrar a temperatura máxima informada nos anos entre 1929 e 1935, use o caractere curinga da tabela para representar os últimos dois dígitos do ano:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
max DESC
Como verificar um intervalo de tabelas particionadas por tempo de processamento usando _PARTITIONTIME
Para verificar um intervalo de tabelas particionadas por tempo de ingestão, use a pseudocoluna _PARTITIONTIME
junto com a pseudocoluna _TABLE_SUFFIX
. Por exemplo, a consulta a seguir verifica a partição de 1º de janeiro de 2017 na tabela my_dataset.mytable_id1
.
#standardSQL
SELECT
field1,
field2,
field3
FROM
`my_dataset.mytable_*`
WHERE
_TABLE_SUFFIX = 'id1'
AND _PARTITIONTIME = TIMESTAMP('2017-01-01')
Consulta a todas as tabelas em um conjunto de dados
Para verificar todas as tabelas em um conjunto de dados, use um prefixo vazio e o caractere curinga de tabela, o que significa que a pseudocoluna _TABLE_SUFFIX
contém os nomes completos dessas tabelas. Por exemplo, a cláusula FROM
a seguir verifica todas as tabelas no conjunto de dados GSOD:
FROM
`bigquery-public-data.noaa_gsod.*`
Com um prefixo vazio, a pseudocoluna _TABLE_SUFFIX
contém os nomes completos das tabelas. Por exemplo, a consulta a seguir é equivalente ao exemplo anterior que encontra a temperatura máxima entre os anos de 1929 e 1935, mas usa nomes de tabelas completos na cláusula WHERE
:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
max DESC
No entanto, note que prefixos mais longos geralmente têm melhor desempenho. Para saber mais informações, consulte Práticas recomendadas.
Detalhes sobre a execução da consulta
Esquema usado para avaliação da consulta
Para executar uma consulta SQL padrão que usa uma tabela curinga, o BigQuery infere automaticamente o esquema dessa tabela.
O BigQuery usa o esquema para a tabela criada mais recentemente, que corresponde ao caractere curinga do esquema da tabela curinga. Mesmo que você restrinja o número de tabelas que quer usar da tabela curinga usando a pseudocoluna _TABLE_SUFFIX
em uma cláusula WHERE
, o BigQuery usa o esquema para o mais recente. foi criada uma tabela que corresponde ao caractere curinga.
Se uma coluna do esquema inferido não existir em uma tabela correspondente, o
BigQuery retornará valores NULL
para essa coluna nas linhas da
tabela que está sem a coluna.
Se os tipos de dados de uma coluna selecionada forem inconsistentes entre as tabelas correspondentes, o BigQuery retornará um erro para a consulta.
Práticas recomendadas
Normalmente, os prefixos mais longos têm melhor desempenho que os mais curtos. Por exemplo, a consulta a seguir usa um prefixo longo (
gsod200
):#standardSQL SELECT max FROM `bigquery-public-data.noaa_gsod.gsod200*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN '0' AND '1' ORDER BY max DESC
A falta de prefixo geralmente piora o desempenho da consulta a seguir:
#standardSQL SELECT max FROM `bigquery-public-data.noaa_gsod.*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001' ORDER BY max DESC
O particionamento é recomendado em vez da fragmentação da tabela porque as tabelas particionadas têm um desempenho melhor. A fragmentação reduz o desempenho e cria mais tabelas para gerenciar. Para mais informações, consulte Particionamento versus fragmentação.
Para práticas recomendadas sobre como controlar de custos no BigQuery, consulte Como controlar custos no BigQuery.
A seguir
- Para mais informações sobre o SQL padrão, veja Referência de consultas do SQL padrão.