Esta página foi traduzida pela API Cloud Translation.

Consulte várias tabelas com uma tabela de caráter universal

As tabelas de caráter universal permitem-lhe consultar várias tabelas através de declarações SQL concisas. As tabelas com carateres universais só estão disponíveis no GoogleSQL. Para uma funcionalidade equivalente no SQL antigo, consulte as funções de caráter universal de tabelas.

Uma tabela com carateres universais representa uma união de todas as tabelas que correspondem à expressão com carateres universais. Por exemplo, a cláusula FROM seguinte usa a expressão de caráter universal 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 de carateres universais contém uma coluna especial, _TABLE_SUFFIX, que contém o valor correspondente ao caráter universal.

Limitações

As consultas de tabelas com carateres universais estão sujeitas às seguintes limitações.

A funcionalidade de tabela com carateres universais não suporta visualizações. Se a tabela com carateres universais corresponder a qualquer vista no conjunto de dados, a consulta devolve um erro, mesmo que a consulta contenha uma cláusula WHERE na pseudocoluna _TABLE_SUFFIX para filtrar a vista.
Não é possível colocar em cache os resultados das consultas de várias tabelas que usem um caráter universal, mesmo que a opção Usar resultados em cache esteja selecionada. Se executar várias vezes a mesma consulta com um caráter universal, tem de pagar cada consulta.
As tabelas com carateres universais só suportam o armazenamento do BigQuery integrado. Não pode usar carateres universais para consultar uma tabela externa ou uma vista.
Não pode usar consultas com carateres universais em tabelas com particionamento incompatível ou uma combinação de tabelas particionadas e não particionadas. As tabelas consultadas também têm de ter especificações de clustering idênticas.
Pode usar tabelas com carateres universais com tabelas particionadas, e a eliminação de partições e a eliminação de clusters são suportadas. No entanto, as tabelas agrupadas, mas não particionadas, não beneficiam totalmente da eliminação de clusters com a utilização de carateres universais.
As consultas que contêm declarações de linguagem de manipulação de dados (DML) não podem usar uma tabela com carateres universais como destino da consulta. Por exemplo, pode usar uma tabela de caráter universal na cláusula FROM de uma consulta UPDATE, mas não pode usar uma tabela de caráter universal como destino da operação UPDATE.
Os filtros nas pseudocolunas _TABLE_SUFFIX ou _PARTITIONTIME que incluem funções definidas pelo utilizador em JavaScript não limitam o número de tabelas analisadas numa tabela com carateres universais.
As consultas com carateres universais não são suportadas para tabelas protegidas por chaves de encriptação geridas pelo cliente (CMEK).
Todas as tabelas referenciadas numa consulta com carateres universais têm de ter exatamente o mesmo conjunto de chaves e valores de etiquetas.
Quando usa tabelas de caráter universal, todas as tabelas no conjunto de dados que começam com o nome da tabela antes de * são analisadas, mesmo que _TABLE_SUFFIX seja usado em combinação com REGEXP_CONTAINS e seja fornecida 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 analisada tiver uma incompatibilidade de esquema (ou seja, uma coluna com o mesmo nome é de um tipo diferente), a consulta falha com o erro Não é possível ler o campo do tipo X como Y Campo: nome_da_coluna. Todas as tabelas são correspondentes, mesmo que esteja a usar o operador de igualdade =. Por exemplo, na consulta seguinte, a tabela my_dataset.my_table_03_backup também é analisada. Assim, a consulta pode falhar devido a uma incompatibilidade de esquema. No entanto, se não houver uma incompatibilidade de esquema, os resultados provêm apenas da tabela my_dataset.my_table_03, como esperado.
```
SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'
```

Antes de começar

Certifique-se de que está a usar o GoogleSQL. Para mais informações, consulte o artigo Mudar dialetos SQL.
Se estiver a usar SQL antigo, consulte o artigo Funções de carateres universais de tabelas.
Muitos dos exemplos nesta página usam um conjunto de dados público da National Oceanic and Atmospheric Administration (NOAA). Para mais informações acerca dos dados, consulte NOAA Global Surface Summary of the Day Weather Data.

Quando usar tabelas com carateres universais

As tabelas de caráter universal são úteis quando um conjunto de dados contém várias tabelas com nomes semelhantes que têm esquemas compatíveis. Normalmente, estes conjuntos de dados contêm tabelas que representam dados de um único dia, mês ou ano. Por exemplo, um conjunto de dados público alojado pelo BigQuery, o NOAA Global Surface Summary of the Day Weather Data, contém uma tabela para cada ano desde 1929 até ao presente.

Uma consulta que analise todos os IDs das tabelas de 1929 a 1940 seria muito longa se tivesse de nomear todas as 12 tabelas na cláusula FROM (a maioria das tabelas é omitida neste exemplo):

#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 que usa uma tabela de caráter universal é 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 com carateres universais só são compatíveis com o armazenamento integrado do BigQuery. Não pode usar carateres universais quando consulta uma tabela externa ou uma vista.

Sintaxe de tabelas com carateres universais

Sintaxe de tabelas com carateres universais:

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

<project-id>: ID do projeto da Cloud Platform. Opcional se usar o ID do projeto predefinido.
<dataset-id>: ID do conjunto de dados do BigQuery.
<table-prefix>: Uma string comum a todas as tabelas que correspondem ao caráter universal. O prefixo da tabela é opcional. Omitir o prefixo da tabela corresponde a todas as tabelas no conjunto de dados.
* (caráter universal): O caráter universal "*" representa um ou mais carateres de um nome de tabela. O caráter universal só pode aparecer como o caráter final de um nome de tabela com caráter universal.

As consultas com tabelas com carateres universais suportam a pseudocoluna _TABLE_SUFFIX na cláusula WHERE. Esta coluna contém os valores correspondentes ao caráter universal, para que as consultas possam filtrar as tabelas acedidas. 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 mais informações sobre a pseudocoluna _TABLE_SUFFIX, consulte o artigo Filtrar tabelas selecionadas com _TABLE_SUFFIX.

Inclua os nomes das tabelas com carateres universais em acentos graves

O nome da tabela com carateres universais contém o caráter especial (*), o que significa que tem de colocar o nome da tabela com carateres universais entre carateres acento grave (`). Por exemplo, a seguinte consulta é válida porque usa 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

A seguinte consulta NÃO é válida porque não está corretamente entre aspas invertidas:

#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

Consulte tabelas com tabelas de carateres universais

As tabelas com carateres universais permitem-lhe consultar várias tabelas de forma concisa. Por exemplo, um conjunto de dados público alojado pelo BigQuery, o NOAA Global Surface Summary of the Day Weather Data, contém uma tabela para cada ano desde 1929 até ao presente, que partilham todas o prefixo comum gsod seguido do ano de quatro dígitos. As tabelas têm os nomes gsod1929, gsod1930, gsod1931, etc.

Para consultar um grupo de tabelas que partilham um prefixo comum, use o símbolo de caráter universal de tabela (*) após o prefixo da tabela na sua declaração FROM. Por exemplo, a consulta seguinte encontra a temperatura máxima comunicada 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

Filtre tabelas selecionadas com _TABLE_SUFFIX

Para restringir uma consulta de modo que analise apenas um conjunto especificado de tabelas, use a pseudocoluna _TABLE_SUFFIX numa cláusula WHERE com uma condição que seja uma expressão constante.

A pseudocoluna _TABLE_SUFFIX contém os valores correspondentes ao caráter universal da tabela. Por exemplo, a consulta de amostra anterior, que analisa todas as tabelas da década de 1940, usa um caráter universal de tabela 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, que representam as tabelas gsod1940 a gsod1949. Estes valores _TABLE_SUFFIX podem ser usados numa cláusula WHERE para filtrar tabelas específicas.

Por exemplo, para filtrar a 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

A utilização de _TABLE_SUFFIX pode reduzir significativamente o número de bytes analisados, o que ajuda a reduzir o custo de execução das 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 analisadas numa tabela com carateres universais. Por exemplo, a seguinte consulta não limita as tabelas analisadas para a tabela com carateres universais 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 seguinte consulta limita a análise com base na primeira condição de filtro, _TABLE_SUFFIX BETWEEN '40' and '60', porque é uma expressão constante. No entanto, a seguinte consulta não limita a análise 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 solução alternativa, pode 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 vista INFORMATION_SCHEMA.TABLES. Para mais informações sobre a tabela INFORMATION_SCHEMA, consulte o artigo Obtenção de metadados de tabelas através do INFORMATION_SCHEMA.

Analisar um intervalo de tabelas com _TABLE_SUFFIX

Para analisar um intervalo de tabelas, use a pseudocoluna _TABLE_SUFFIX juntamente com a cláusula BETWEEN. Por exemplo, para encontrar a temperatura máxima comunicada nos anos entre 1929 e 1935 inclusive, use o caráter universal da tabela para representar os dois últimos 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

Analisar um intervalo de tabelas particionadas por tempo de ingestão com _PARTITIONTIME

Para analisar um intervalo de tabelas particionadas por tempo de ingestão, use a pseudocoluna _PARTITIONTIME com a pseudocoluna _TABLE_SUFFIX. Por exemplo, a seguinte consulta analisa 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')

Consultar todas as tabelas num conjunto de dados

Para analisar todas as tabelas num conjunto de dados, pode usar um prefixo vazio e o caráter universal de tabela, o que significa que a pseudocoluna _TABLE_SUFFIX contém nomes de tabelas completos. Por exemplo, a seguinte cláusula FROM analisa 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 seguinte consulta é 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, tenha em atenção que os prefixos mais longos têm geralmente um melhor desempenho. Para mais informações, consulte as práticas recomendadas.

Detalhes da execução da consulta

Esquema usado para a avaliação de consultas

Para executar uma consulta GoogleSQL que use uma tabela com carateres universais, o BigQuery infere automaticamente o esquema dessa tabela. O BigQuery usa o esquema da tabela criada mais recentemente que corresponde ao caráter universal como o esquema da tabela de carateres universais. Mesmo que restrinja o número de tabelas que quer usar da tabela com carateres universais usando a pseudocoluna _TABLE_SUFFIX numa cláusula WHERE, o BigQuery usa o esquema da tabela criada mais recentemente que corresponda ao caráter universal.

Se uma coluna do esquema inferido não existir numa tabela correspondente, o BigQuery devolve valores NULL para essa coluna nas linhas da tabela que não tem a coluna.

Se o esquema for inconsistente nas tabelas correspondentes à consulta com carateres universais, o BigQuery devolve um erro. Este é o caso quando as colunas das tabelas correspondentes têm tipos de dados diferentes ou quando não se pode assumir que as colunas que não estão presentes em todas as tabelas correspondentes têm um valor nulo.

Práticas recomendadas

Geralmente, os prefixos mais longos têm um desempenho melhor do que os prefixos mais curtos. Por exemplo, a seguinte consulta 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 seguinte consulta tem geralmente um desempenho inferior porque usa um prefixo vazio:

#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

Recomendamos a partição em vez da divisão, porque as tabelas particionadas têm um desempenho melhor. A divisão em fragmentos reduz o desempenho ao mesmo tempo que cria mais tabelas para gerir. Para mais informações, consulte o artigo Partição vs. divisão.

Para ver as práticas recomendadas para controlar os custos no BigQuery, consulte o artigo Controlar os custos no BigQuery

O que se segue?

Para mais informações sobre o GoogleSQL, consulte a referência de consultas do GoogleSQL.