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 consulta UPDATE, mas não pode ser usada como o destino da operação UPDATE.
• 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 com REGEXP_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 tabela my_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 tabela my_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

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 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 bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%'), porque é uma expressão dinâmica:

#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.