使用通配符表查询多个表

借助通配符表，您可以使用简洁的 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 查询参考文档。