查询分区表
您可以通过以下方式查询分区表:
- 使用 Cloud Console
- 使用
bq
命令行工具的bq query
命令 - 调用 jobs.insert API 方法并配置查询作业
- 使用客户端库
如需详细了解如何运行查询,请参阅运行交互式查询和批量查询。
所需权限
如需查询表,您至少必须具有 bigquery.tables.getData
权限。
以下预定义的 IAM 角色包含 bigquery.tables.getData
权限:
bigquery.dataViewer
bigquery.dataOwner
bigquery.dataEditor
bigquery.admin
此外,如果用户具有 bigquery.datasets.create
权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner
访问权限。借助 bigquery.dataOwner
访问权限,用户可以查询数据集内的表和视图。
如需运行查询作业,您还必须获得 bigquery.jobs.create
权限。以下预定义的 IAM 角色包含 bigquery.jobs.create
权限:
bigquery.user
bigquery.jobUser
bigquery.admin
如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制。
提取时间分区表伪列
在您创建提取时间分区表时,系统会向表中添加两个伪列:_PARTITIONTIME
伪列和 _PARTITIONDATE
伪列。_PARTITIONTIME
伪列包含基于日期的时间戳,该时间戳用于加载到表中的数据。_PARTITIONDATE
伪列包含日期表示。两个伪列名已预留,这意味着不能使用任何表中的名称创建列。
_PARTITIONTIME
和 _PARTITIONDATE
仅在提取时间分区表中可用。分区表没有伪列。如需了解如何查询分区表,请参阅查询分区表。
_PARTITIONTIME 伪列
_PARTITIONTIME
伪列包含基于 UTC 时间的时间戳,表示自 unix 周期以来的微秒数。例如,如果数据在 UTC 时间 2016 年 4 月 15 日 08:15:00 附加到表中,则这一天附加的所有数据行都具有 _PARTITIONTIME
列,其中包含以下值之一:+ TIMESTAMP("2016-04-15 08:00:00")
(对于每小时分区表)。+ TIMESTAMP("2016-04-15")
(对于每日分区表)。+ TIMESTAMP("2016-04-01")
(对于每月分区表)。+ TIMESTAMP("2016-01-01")
(对于每年分区表)。
要查询 _PARTITIONTIME
伪列,必须使用别名。例如,以下查询通过将别名 pt
分配给伪列来选择 _PARTITIONTIME
:
SELECT _PARTITIONTIME AS pt, column FROM dataset.table
其中:
- column 是要查询的列的名称。您可以采用英文逗号分隔列表的形式指定多个列。
- dataset 是包含分区表的数据集。
- table 是分区表。
流式缓冲区中的数据在 _PARTITIONTIME
列中的值为 NULL
。
_PARTITIONDATE 伪列
_PARTITIONDATE
伪列包含与 _PARTITIONTIME
伪列中的值对应的世界协调时间 (UTC) 日期。每小时、每月或每年分区表不支持此列。
要查询 _PARTITIONDATE
伪列,必须使用别名。例如,以下查询通过将别名 pd
分配给伪列来选择 _PARTITIONDATE
:
SELECT _PARTITIONDATE AS pd, column FROM dataset.table
其中:
- column 是要查询的列的名称。您可以采用英文逗号分隔列表的形式指定多个列。
- dataset 是包含分区表的数据集。
- table 是分区表。
流式缓冲区中的数据在 _PARTITIONDATE
列中的值为 NULL
。
使用伪列查询提取时间分区表
在提取时间分区表中查询数据时,可以指定 _PARTITIONTIME
或 _PARTITIONDATE
伪列中的值来引用特定分区。例如:
_PARTITIONTIME >= "2018-01-29 00:00:00" AND _PARTITIONTIME < "2018-01-30 00:00:00"
_PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')
或
_PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"
_PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'
限制使用伪列查询的分区
您可以使用 _PARTITIONTIME
和 _PARTITIONDATE
伪列,限制查询期间扫描的分区数。这也称为删减分区。分区删减是 BigQuery 用于从输入扫描中消除不必要的分区的机制。计算查询扫描的字节时,不包括已删减的分区,从而降低按需分析费用。通常,如果过滤条件包含常量表达式(这些表达式可以在查询开始时计算结果),则删减分区有助于降低查询费用。
例如,以下查询仅扫描分区表中 2016 年 1 月 1 日到 2016 年 1 月 2 日之间的分区:
_PARTITIONTIME
SELECT column FROM dataset.table WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')
_PARTITIONDATE
SELECT column FROM dataset.table WHERE _PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'
分区删减示例
此示例演示了如何在子查询中使用伪列过滤器限制扫描的分区数量:
_PARTITIONTIME
SELECT column1, column2 FROM ( SELECT column1, column2 FROM dataset.table WHERE _PARTITIONTIME = TIMESTAMP('2016-03-28')) t1 CROSS JOIN dataset.table t2 WHERE t1.column2 = "one"
_PARTITIONDATE
SELECT column1, column2 FROM ( SELECT column1, column2 FROM dataset.table WHERE _PARTITIONDATE = '2016-03-28') t1 CROSS JOIN dataset.table t2 WHERE t1.column2 = "one"
以下查询根据 WHERE
子句中的第一个过滤条件限制查询的分区。但是,WHERE
子句中的第二个过滤条件不会限制查询的分区,因为它使用动态表值。
_PARTITIONTIME
SELECT column FROM dataset.table2 WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01') AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)
_PARTITIONDATE
SELECT column FROM dataset.table2 WHERE _PARTITIONDATE BETWEEN '2017-01-01' AND '2017-03-01' AND _PARTITIONDATE = (SELECT MAX(date) from dataset.table1)
扫描所有分区的伪列查询
以下示例使用伪列,但会扫描时间单位分区表中的所有分区。
在旧版 SQL 中,仅在 _PARTITIONTIME
过滤器指定为尽可能接近表名时,该过滤器才生效。例如,尽管存在 _PARTITIONTIME
过滤器,但以下查询将扫描 table1
中的所有分区:
#legacySQL # Scans all partitions on t1 SELECT t1.field1, t2.field1 FROM dataset.table1 t1 CROSS JOIN dataset.table2 t2 WHERE table1._PARTITIONTIME = TIMESTAMP('2016-03-28') AND t1.field1 = "one"
请勿在 _PARTITIONTIME
过滤器中包含任何其他列。例如,以下查询不限制扫描的分区,因为 field1
是该表中的列,并且 BigQuery 无法预先确定要选择的分区。 如需了解详情,请参阅删减(限制)分区。
# Scans all partitions of table2 SELECT field1 FROM dataset.table2 WHERE _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');
使用伪列提高性能
如需提高查询性能,请在比较运算符的左侧单独使用 _PARTITIONTIME
伪列。例如,以下查询处理相同的数据量,但第二个示例的性能更高。
示例 1:以下查询的速度可能较慢,因为该查询将伪列值与 WHERE
过滤器中的其他运算组合在一起。
标准 SQL
#standardSQL /* Can be slower */ SELECT field1 FROM dataset.table1 WHERE TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")
旧版 SQL
#legacySQL /* Can be slower */ SELECT field1 FROM dataset.table1 WHERE DATE_ADD(_PARTITIONTIME, 5, "DAY") > TIMESTAMP("2016-04-15")
示例 2:以下查询的性能更好,因为它在过滤器比较运算符的左侧单独放置伪列。
标准 SQL
#standardSQL /* Often performs better */ SELECT field1 FROM dataset.table1 WHERE _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY)
旧版 SQL
#legacySQL /* Often performs better */ SELECT field1 FROM dataset.table1 WHERE _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")
第二个查询将 _PARTITIONTIME
单独放置在 >
比较运算符的左侧,可比第一个查询提供更好的性能,具体取决于表的大小。由于查询处理的数据量相同,因此在这两种情况下结算的字节数相同。
使用通配符表查询提取时间分区表
除了使用伪列限制在查询期间扫描的分区数外,还可以通过伪列使用通配符表查询一系列分区表。要了解如何使用通配符表和分区表,请参阅使用 _PARTITIONTIME 扫描一系列分区表。
使用时区查询提取时间分区表
_PARTITIONTIME
的值基于填充字段时的 UTC 日期,这意味着分区是基于凌晨 12:00 UTC 划分的。如果要基于 UTC 以外的时区查询数据,则应在开始向表中加载数据之前,选择以下选项之一。
有两种方法可以使用自定义的非 UTC 时区查询分区表中的数据。您可以创建单独的时间戳列,也可以使用分区修饰器向特定分区加载数据。
如果使用时间戳列,针对 SQL 查询中的时区差异,可以使用默认的基于 UTC 的分区和帐号。或者,如果希望分区按非 UTC 时区分组,请使用分区修饰器将数据加载到基于不同时区的分区中。
使用时间戳列查询时区
如需使用时间戳调整时区,请创建一个单独的列来存储时间戳,以便按小时或分钟处理行。
如需查询基于非 UTC 时区的数据,请同时使用 _PARTITIONTIME
伪列和自定义时间戳列。使用 _PARTITIONTIME
可以将表的扫描限制为相关分区,通过自定义时间戳可以进一步将结果限制为您的时区。例如,查询包含时间戳字段 MY_TIMESTAMP_FIELD 的分区表 (mydataset.partitioned_table
) 中的数据,以获得在 2016-05-01 12:00:00 PST
与 2016-05-05 14:00:00 PST
之间添加到表中的数据:
标准 SQL
#standardSQL SELECT field1 FROM dataset.partitioned_table WHERE _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01") AND TIMESTAMP("2016-05-06") AND TIMESTAMP_ADD(MY_TIMESTAMP_FIELD, INTERVAL 8 HOUR) BETWEEN TIMESTAMP("2016-05-01 12:00:00") AND TIMESTAMP("2016-05-05 14:00:00");
旧版 SQL
#legacySQL SELECT field1 FROM dataset.partitioned_table WHERE _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01") AND TIMESTAMP("2016-05-06") AND DATE_ADD(MY_TIMESTAMP_FIELD, 8, 'HOUR') BETWEEN TIMESTAMP("2016-05-01 12:00:00") AND TIMESTAMP("2016-05-05 14:00:00");
使用提取时间分区表的伪列创建视图
如需将查询读取的数据量限制为一组分区,请创建一个包含 _PARTITIONTIME
或 _PARTITIONDATE
伪列上过滤器的视图。例如,以下查询可用于创建仅包含名为 dataset.partitioned_table
的表中最近七天数据的视图:
标准 SQL
#standardSQL SELECT * FROM dataset.partitioned_table WHERE _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR),DAY) AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY);
旧版 SQL
#legacySQL SELECT * FROM dataset.partitioned_table WHERE _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000)) AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()));
如需了解如何创建视图,请参阅创建视图。
提取时间分区表的 _UNPARTITIONED_ 分区
__UNPARTITIONED__
分区会暂时保存在流式缓冲区中流式插入到分区表中的数据。直接流式插入到分区表的特定分区的数据不使用 __UNPARTITIONED__
分区。但是,该数据会直接流式插入到该分区。如需了解详情,请参阅流式插入到分区表。
要查询 __UNPARTITIONED__
分区中的数据,请使用具有 NULL
值的 _PARTITIONTIME
伪列。例如:
SELECT column FROM dataset.table WHERE _PARTITIONTIME IS NULL
其中:
- column 是要查询的列的名称。您可以采用英文逗号分隔列表的形式指定多个列。
- dataset 是包含分区表的数据集。
- table 是分区表。
查询分区表
根据 TIMESTAMP
、DATE
、DATETIME
或 INTEGER
列分区的表没有伪列。如需限制查询分区表时扫描的分区数,请使用谓词过滤条件(WHERE
子句)。分区列上的过滤条件将用于删减分区和降低查询费用。
每小时、每月和每年分区表只能通过标准 SQL 进行查询。
创建分区表时,您可以启用需要分区过滤条件选项来要求使用谓词过滤条件。应用此选项时,如果试图在不指定 WHERE
子句的情况下查询分区表,则会产生以下错误:Cannot query over table 'project_id.dataset.table' without a
filter that can be used for partition elimination
。
如需详细了解如何在创建分区表时添加需要分区过滤条件选项,请参阅创建分区表。
如果在创建分区表时未启用需要分区过滤条件选项,您可以更新表来添加该选项。
删减(限制)分区
如需限制查询中扫描的分区,请在过滤条件中使用常量表达式。如果您在查询过滤条件中使用动态表达式,则 BigQuery 必须扫描所有分区。
例如,以下查询会删减分区,因为过滤条件 WHERE t1.ts=CURRENT_TIMESTAMP()
包含常量表达式:
#standardSQL SELECT t1.name, t2.category FROM table1 t1 INNER JOIN table2 t2 ON t1.id_field = t2.field2 WHERE t1.ts = CURRENT_TIMESTAMP()
但是,以下查询不会删减分区,因为过滤条件 WHERE t1.ts = (SELECT timestamp from table where key = 2)
不是常量表达式;它取决于 timestamp
和 key
字段的动态值:
#standardSQL SELECT t1.name, t2.category FROM table1 t1 INNER JOIN table2 t2 ON t1.id_field = t2.field2 WHERE t1.ts = (SELECT timestamp from table3 where key = 2)
在过滤器中隔离分区列
表示过滤器时隔离分区列。需要计算多个字段中的数据的过滤器不会删减分区。例如,使用分区列和第二个字段进行日期比较的查询,或包含某些字段串联的查询,将不会删减分区。
例如,以下过滤条件不会删减分区,因为它需要根据分区 ts
字段和第二个字段 ts2
进行计算:
WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2
后续步骤
- 如需分区表的概览,请参阅分区表简介。
- 如需详细了解提取时间分区表,请参阅创建和使用提取时间分区表。
- 如需详细了解基于列的时间分区表,请参阅创建和使用日期/时间戳/日期时间分区表。
- 如需详细了解整数范围分区表,请参阅创建和使用整数范围分区表。