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

Limitações

As consultas de tabela curinga estão sujeitas às seguintes limitações:

• A funcionalidade de tabela curinga não oferece suporte a visualizações. Se a tabela curinga corresponder a qualquer visualização no conjunto de dados, a consulta retornará um erro, mesmo que ela contenha uma cláusula WHERE na pseudocoluna _TABLE_SUFFIX para filtrar a visualização.
• 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 apenas com o armazenamento integrado do BigQuery. Não é possível usar caracteres curinga para consultar 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. As tabelas consultadas também precisam ter especificações de clustering idênticas.
• É possível usar tabelas curinga com tabelas particionadas, e há suporte para a remoção de partição e cluster. No entanto, as tabelas em cluster que não são particionadas não têm nenhum benefício com a remoção de clusters quando há uso de caracteres curinga.
• 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 verificadas 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).
• Todas as tabelas referenciadas em uma consulta com caractere curinga precisam ter exatamente o mesmo conjunto de chaves e valores de tag.

• 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}$. Por 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 GoogleSQL. 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 apenas com o armazenamento integrado do BigQuery. Não é possível usar caracteres curinga na consulta a uma tabela externa ou visualização.

Sintaxe da tabela curinga

Sintaxe da tabela curinga:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression

<project-id>

ID do projeto do Cloud Platform. É opcional se você usa o ID do projeto padrão.

<dataset-id>

ID do conjunto de dados do BigQuery.

<table-prefix>

Uma string comum em todas as tabelas que corresponde ao caractere curinga. O prefixo da tabela é opcional. Todas as tabelas no conjunto de dados serão correspondentes se o prefixo da tabela for omitido.

* (caractere curinga)

Representa um ou mais caracteres do nome de uma tabela. O caractere curinga só pode ser usado como caractere final do nome de uma tabela curinga.

As consultas com tabelas curinga são compatíveis com a pseudocoluna _TABLE_SUFFIX na cláusula WHERE. Essa coluna contém os valores correspondentes ao caractere curinga, para que as consultas possam filtrar quais tabelas são acessadas. Por exemplo, as seguintes cláusulas WHERE usam operadores de comparação para filtrar as tabelas correspondentes:

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

Para saber mais sobre a pseudocoluna _TABLE_SUFFIX, consulte Como filtrar tabelas selecionadas com _TABLE_SUFFIX.

Colocar nomes de tabela com caracteres curinga entre acentos graves

O nome da tabela curinga contém o caractere especial (*), que indica a necessidade de incluir o nome da tabela curinga entre acentos graves (`). Por exemplo, a consulta a seguir é válida porque usa os acentos graves:

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Por outro lado, a consulta a seguir NÃO é válida porque não usa os acentos graves corretamente:

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

As aspas não funcionam:

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Consultar 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

Filtrar as 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 da tabela 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 ingestão 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')

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 do GoogleSQL 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 o esquema for inconsistente nas tabelas correspondentes à consulta com caractere curinga, o BigQuery retornará um erro. Isso ocorre quando as colunas das tabelas correspondentes têm tipos de dados diferentes ou quando as colunas que não estão presentes em todas as tabelas correspondentes não podem ter um valor nulo.

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 GoogleSQL, consulte a Referência de consultas do GoogleSQL.