使用通配符表查询多个表

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

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

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

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

限制

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

通配符表不支持视图。如果通配符表与数据集中的任何视图匹配，即使您的查询在 _TABLE_SUFFIX 伪列上包含 WHERE 子句以过滤掉视图，查询也会返回错误。
使用通配符对多个表执行查询时，不支持缓存的结果（即使选中了使用缓存的结果选项）。如果多次运行相同的通配符查询，您要为每个相应查询付费。
通配符表仅支持内置 BigQuery 存储。您不能使用通配符来查询外部表或视图。
如果表包含不兼容的分区或者将分区表和非分区表混合使用，则不能使用通配符进行查询。查询的表也需要具有相同的聚簇规范。
您可以将通配符表与分区表搭配使用，并且支持分区删减和集群删减。但是，已聚簇但未分区的表不会因使用通配符而受益于任何集群删减操作。
包含数据操纵语言 (DML) 语句的查询不能将通配符表用作查询的目标。例如，可以在 UPDATE 查询的 FROM 子句中使用通配符表，但不能将通配符表用作 UPDATE 操作的目标。
对包含 JavaScript 用户定义函数的 _TABLE_SUFFIX 或 _PARTITIONTIME 伪列的过滤条件不会限制通配符表中扫描的表的数量。
受客户管理的加密密钥 (CMEK) 保护的表不支持通配符查询。
具有标记的表不支持通配符查询。
使用通配符表时，即使 _TABLE_SUFFIX 与 REGEXP_CONTAINS 结合使用，并且具有正则表达式（例如 ^[0-9]{2}$），也仍会扫描数据集中表名后跟 * 的所有表。例如：
```
SELECT *
FROM `my_project.my_dataset.my_table_*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');
```
如果单个扫描表的架构不匹配（即名称相同的列属于其他类型），则查询会失败并显示以下错误：无法读取 X 类型的字段作为 Y 字段：column_name。即使您使用等式运算符 =，所有表也会匹配。例如，在以下查询中，也会扫描表 my_dataset.my_table_03_backup。因此，查询可能会由于架构不匹配而失败。但是，如果没有架构不匹配，则结果仅按预期一样来自表 my_dataset.my_table_03。
```
SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'
```

准备工作

确保您使用的是 GoogleSQL。如需了解详情，请参阅切换 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 存储。在查询外部表或视图时，不能使用通配符。

通配符表语法

通配符表语法：

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

<project-id>: Cloud Platform 项目 ID。如果您使用默认项目 ID，则此参数为可选项。
<dataset-id>: BigQuery 数据集 ID。
<table-prefix>: 所有与通配符匹配的表所共有的字符串。表前缀是可选项。省略表前缀将匹配数据集中的所有表。
*（通配符）: 通配符“*”表示表名中的一个或多个字符。通配符只能作为通配符表名的最后一个字符出现。

使用通配符表的查询支持 WHERE 子句中的 _TABLE_SUFFIX 伪列。此列包含与通配符匹配的值，以便查询可以过滤要访问的表。例如，以下 WHERE 子句使用比较运算符来过滤匹配的表：

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

如需详细了解 _TABLE_SUFFIX 伪列，请参阅使用 _TABLE_SUFFIX 过滤选定表。

用英文反引号将带通配符的表名括住

通配符表名包含特殊字符 (*)，因此您必须将通配符表名用英文反引号 (`) 引起来。例如，以下查询使用了反引号，因而是有效查询：

#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

以下查询没有正确使用英文反引号括住，因而是无效查询：

#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

普通引号不起作用：

#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

使用通配符表查询表

借助通配符表，您可以通过精简的方式查询多个表。例如，由 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 视图。如需详细了解 INFORMATION_SCHEMA 表，请参阅使用 INFORMATION_SCHEMA 获取表元数据。

使用 _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

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

查询执行详情

用于查询评估的架构

为执行使用通配符表的 GoogleSQL 查询，BigQuery 会自动推断该表的架构。 BigQuery 使用与通配符匹配的最新创建的表的架构，将其作为通配符表的架构。即使您在 WHERE 子句中使用 _TABLE_SUFFIX 伪列限制要从通配符表中使用的表的数量，BigQuery 也会对匹配通配符的最新创建的表使用架构。

如果推断架构中的列不存在于匹配表中，则 BigQuery 会返回该列在缺少列的表行中的 NULL 值。

如果通过通配符查询所匹配到的多个表的架构不一致，则 BigQuery 会返回错误。如果匹配表的列具有不同的数据类型，或者不存在于所有匹配表中的列不能假定具有 null 值，则会出现这种情况。

最佳做法

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

更推荐使用分区而不是分片，因为分区表性能更佳。分片会降低性能，同时创建更多表进行管理。如需了解详情，请参阅分区与分片。

如需了解在 BigQuery 中控制费用的最佳实践，请参阅控制 BigQuery 费用

后续步骤

如需详细了解 GoogleSQL，请参阅 GoogleSQL 查询参考文档。