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 curinga contém uma coluna especial 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.
• Consultas em que há instruções DML (em linguagem de manipulação de dados) não podem ter como destino uma tabela curinga. Por exemplo, uma tabela curinga pode ser usada na cláusula FROM de uma consulta UPDATE, mas não pode ser usada como o destino da operação UPDATE.
• 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).

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

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.

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 e assim por diante.

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 a consulta para que ela consulte um conjunto arbitrário de tabelas, use a pseudocoluna _TABLE_SUFFIX na cláusula WHERE. 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 diminui o custo da execução de suas consultas.

Os filtros em _TABLE_SUFFIX que incluem subconsultas não podem ser usados para limitar o número de tabelas digitalizadas para uma tabela curinga. Por exemplo, a consulta a seguir não limita as tabelas verificadas para a tabela curinga bigquery-public-data.noaa_gsod.gsod19*.

#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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

A consulta a seguir limita a verificação com base na condição de filtro, _TABLE_SUFFIX BETWEEN '40' and '60', mas não com base na condição que envolve a subconsulta.

#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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

Para limitá-lo com base na condição envolvendo a subconsulta, você pode executar duas consultas separadas.

Primeira consulta:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id 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'

Observação: as consultas de exemplo usam o __TABLES__SUMMARY meta-table.

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 processamento, 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')

Como consultar 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. Se o esquema for inconsistente nas tabelas que correspondem à tabela curinga, o BigQuery retorna um erro.

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

A seguir

• Para mais informações sobre o SQL padrão, veja Referência de consultas do SQL padrão.