使用通配符表查询多个表

借助通配符表，您可以使用简洁的 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 操作的目标。
• 对包含 JavaScript 用户定义函数的 _TABLE_SUFFIX 或 _PARTITIONTIME 伪列的过滤条件不会限制通配符表中扫描的表的数量。
• 受客户管理的加密密钥 (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_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

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

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

第一条查询：

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

第二条查询：

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

注意：示例查询使用的是 INFORMATION_SCHEMA.TABLES 视图。

使用 _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 查询参考。