使用通配符表查询多个表

借助通配符表，您可以使用简洁的 SQL 语句查询多个表。只有标准 SQL 才提供通配符表。如需在旧版 SQL 中使用等效功能，请参阅，请参阅表通配符函数。

通配符表代表与通配符表达式匹配的所有表的联合。例如，以下 FROM 子句使用通配符表达式 gsod* 来匹配 noaa_gsod 数据集内以字符串 gsod 开头的所有表。

FROM
  `bigquery-public-data.noaa_gsod.gsod*`

通配符表中的每一行都包含一个特殊列（即“_TABLE_SUFFIX”），该列包含由通配符匹配的值。

如需了解通配符表的语法，请参阅标准 SQL 参考中的通配符表。

限制

通配符表查询存在以下限制。

• 通配符表不支持视图。如果通配符表与数据集中的任何视图匹配，查询会返回错误。无论您的查询是否包含 _TABLE_SUFFIX 伪列上 WHERE 的子句以过滤掉视图，都是如此。
• 目前，使用通配符对多个表执行查询时，不支持缓存的结果（即使选中了使用缓存的结果选项）。如果多次运行相同的通配符查询，您要为每个相应查询付费。
• 通配符表仅支持原生 BigQuery 存储。在查询外部表或视图时，不能使用通配符。
• 包含数据操纵语言 (DML) 语句的查询不能将通配符表用作查询的目标。例如，可以在 UPDATE 查询的 FROM 子句中使用通配符表，但不能将通配符表用作 UPDATE 操作的目标。
• 受客户管理的加密密钥 (CMEK) 保护的表不支持通配符查询。

准备工作

• 确保您使用的是 BigQuery 标准 SQL。如需了解详情，请参阅切换 SQL 方言。
• 如果您使用的是旧版 SQL，请参阅表通配符函数。
• 本页面上的许多示例都使用美国国家海洋和大气管理局 (NOAA) 的公开数据集。如需详细了解这些数据，请参阅 NOAA 全球地面日间天气数据摘要。

何时应该使用通配符表

如果一个数据集包含多个具有兼容架构和相似名称的表，则使用通配符表会有帮助。通常情况下，此类数据集包含多个表，其中每个表均代表某一天、某个月或某年的数据。例如，由 BigQuery 托管的公共数据集 NOAA 全球地面日间天气数据摘要就包含多个表，其中每个表分别代表从 1929 年至今每一年的数据。

某个查询需要扫描从 1929 年至 1940 年这个期间内的所有表 ID，如果您在 FROM 子句中指定全部 12 个表（本示例中省略了大多数表），那么查询会十分冗长：

#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

同样的查询在使用通配符表之后就简洁很多：

#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

通配符表仅支持原生 BigQuery 存储。在查询外部表或视图时，不能使用通配符。

使用通配符表查询表集

借助通配符表，您可以通过精简的方式查询多个表。 例如，由 BigQuery 托管的公共数据集 NOAA 全球地面日间天气数据摘要就包含多个表，其中每个表分别代表从 1929 年至今每一年的数据。这些表均以 gsod 为前缀并后接一个四位数年份。表名称分别为 gsod1929、gsod1930、gsod1931，依此类推。

如需查询使用某个相同前缀的一组表，请在 FROM 语句内的表前缀后使用表通配符符号 (*)。例如，以下查询会查找 20 世纪 40 年代期间报告的最高温度：

#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

使用 _TABLE_SUFFIX 过滤选定表

要对查询进行限制，以使其仅扫描一组指定的表，请在带有条件（常量表达式）的 WHERE 子句中使用 _TABLE_SUFFIX 伪列。

_TABLE_SUFFIX 伪列包含利用通配符匹配的值。例如，先前介绍的扫描 20 世纪 40 年代所有表的示例查询使用表通配符来表示年份的最后一位数：

FROM
  `bigquery-public-data.noaa_gsod.gsod194*`

相应的 _TABLE_SUFFIX 伪列包含 0 到 9 范围内的值，表示表 gsod1940 到表 gsod1949。您可以在 WHERE 子句中使用这些 _TABLE_SUFFIX 值来过滤特定的表。

例如，如需过滤 1940 年至 1944 年期间的最高温度，您可以对 _TABLE_SUFFIX 使用值 0 和 4：

#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

使用 _TABLE_SUFFIX 能显著减少扫描的字节数，从而降低运行查询的费用。

但是，对包含非常量表达式的条件的 _TABLE_SUFFIX 进行过滤不会限制在通配符表中扫描的表的数量。例如，以下查询不会限制根据通配符表 bigquery-public-data.noaa_gsod.gsod19* 扫描的表数量，因为过滤条件使用 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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

再举一个例子，以下查询基于第一个过滤条件 _TABLE_SUFFIX BETWEEN '40' and '60' 对扫描进行限制，因为它是一个常量表达式。但是，以下查询不会基于第二个过滤条件 _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__ WHERE table_id 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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

要解决此问题，您可以改为执行两个单独的查询；例如：

第一条查询：

#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%'

第二条查询：

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

注意：此示例查询使用的是 __TABLES__SUMMARY 元表。

使用 _TABLE_SUFFIX 扫描一系列表

要扫描一系列表，您可以配合 BETWEEN 子句使用 _TABLE_SUFFIX 伪列。例如，如需查找 1929 年至 1935 年（含 1929 年和 1935 年）期间报告的最高温度，您可以使用表通配符来表示年份中的最后两位数：

#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

使用 _PARTITIONTIME 扫描一系列提取时间分区表

如需扫描一系列提取时间分区表，请将 _PARTITIONTIME 伪列与 _TABLE_SUFFIX 伪列配合使用。例如，以下查询会扫描表 my_dataset.mytable_id1 中日期为 2017 年 1 月 1 日的分区。

#standardSQL
SELECT
  field1,
  field2,
  field3
FROM
  `my_dataset.mytable_*`
WHERE
  _TABLE_SUFFIX = 'id1'
  AND _PARTITIONTIME = TIMESTAMP('2017-01-01')

查询数据集中的所有表

如需扫描数据集中的所有表，您可以使用空前缀和表通配符，也就是说，_TABLE_SUFFIX 伪列包含完整的表名称。例如，以下 FROM 子句扫描 GSOD 数据集中的所有表：

FROM
  `bigquery-public-data.noaa_gsod.*`

使用空前缀时，_TABLE_SUFFIX 伪列包含完整的表名称。举例来说，以下查询与先前的示例同样查找 1929 年至 1935 年期间的最高温度，但不同之处在于，以下查询在 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

但请注意，较长的前缀通常具有更好的效果。如需了解详情，请参阅最佳做法。

查询执行详情

用于查询评估的架构

为执行使用通配符表的标准 SQL 查询，BigQuery 会自动推断该表的架构。 BigQuery 使用与通配符匹配的最新创建的表的架构，将其作为通配符表的架构。如果通过通配符表所匹配到的多个表的架构不一致，BigQuery 会返回错误。

最佳做法

较长的前缀的效果通常要优于较短的前缀。例如，以下查询使用长前缀 (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

以下查询使用了空前缀，因此效果通常较差：

#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

后续步骤

• 如需详细了解标准 SQL，请参阅标准 SQL 查询参考。