借助通配符表,您可以使用简洁的 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 托管的公共数据集 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'
使用 _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 查询参考。