使用通配符表查询多个表

借助通配符表,您可以使用简洁的 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_SUFFIXREGEXP_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'
    

准备工作

何时应该使用通配符表

如果一个数据集包含多个具有兼容架构和相似名称的表,则使用通配符表会有帮助。通常情况下,此类数据集包含多个表,其中每个表均代表某一天、某个月或某年的数据。例如,由 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 为前缀并后接一个四位数年份。表名称分别为 gsod1929gsod1930gsod1931 等。

要查询使用某个相同前缀的一组表,请在 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 伪列包含 09 范围内的值,表示表 gsod1940 到表 gsod1949。您可以在 WHERE 子句中使用这些 _TABLE_SUFFIX 值来过滤特定的表。

例如,如需过滤 1940 年至 1944 年期间的最高温度,您可以对 _TABLE_SUFFIX 使用值 04

#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 费用

后续步骤