查询分区表
本文档介绍了在 BigQuery 中查询分区表时的一些特定注意事项。
如需了解有关在 BigQuery 中运行查询的一般信息,请参阅运行交互式查询和批量查询。
概览
如果查询对分区列的值使用符合条件的过滤条件,则 BigQuery 可以扫描与过滤条件匹配的分区并跳过其余分区。此过程称为分区删减。
分区删减是 BigQuery 用于从输入扫描中消除不必要的分区的机制。计算查询扫描的字节时,不包括已删减的分区。通常,分区删减有助于降低查询费用。
对于不同类型的分区,删减行为会有所不同,因此在查询按不同方式分区但内容相同的表时,您可能会看到处理的字节数存在差异。如需估算查询将处理多少字节,请执行试运行。
查询时间单位列分区表
如需在查询时间单位列分区表时删减分区,请在分区列上添加过滤条件。
在以下示例中,假设 dataset.table
按 transaction_date
列进行分区。示例查询会删减 2016-01-01
之前的日期。
SELECT * FROM dataset.table WHERE transaction_date >= '2016-01-01'
查询注入时间分区表
提取时间分区表包含名为 _PARTITIONTIME
的伪列,即分区列。列的值是每行的 UTC 注入时间,按照分区边界(例如每小时或每天)进行截断,作为 TIMESTAMP
值。
例如,如果您在 2021 年 4 月 15 日 08:15:00 UTC 附加数据,则这些行的 _PARTITIONTIME
列包含以下值:
- 每小时分区表:
TIMESTAMP("2021-04-15 08:00:00")
- 每日分区表:
TIMESTAMP("2021-04-15")
- 每月分区表:
TIMESTAMP("2021-04-01")
- 每年分区表:
TIMESTAMP("2021-01-01")
如果分区粒度不是每日,则表还包含一个名为 _PARTITIONDATE
的伪列。该值等于按照 DATE
值截断的 _PARTITIONTIME
。
这两个伪列名称已预留。您无法在任何表中创建使用任一名称的列。
如需删减分区,请过滤以下任一列。例如,以下查询仅扫描 2016 年 1 月 1 日到 2016 年 1 月 2 日之间的分区:
SELECT column FROM dataset.table WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')
如需选择 _PARTITIONTIME
伪列,必须使用别名。例如,以下查询通过将别名 pt
分配给伪列来选择 _PARTITIONTIME
:
SELECT _PARTITIONTIME AS pt, column FROM dataset.table
对于按日分区表,您可以按相同的方式选择 _PARTITIONDATE
伪列:
SELECT _PARTITIONDATE AS pd, column FROM dataset.table
SELECT *
语句不会返回 _PARTITIONTIME
和 _PARTITIONDATE
伪列。您必须明确选择它们:
SELECT _PARTITIONTIME AS pt, * FROM dataset.table
处理注入时间分区表中的时区
_PARTITIONTIME
的值基于填充字段时的 UTC 日期。如果要查询基于非 UTC 时区的数据,请选择以下选项之一:
- 调整 SQL 查询中的时区差异。
- 使用分区修饰器,根据与 UTC 不同的时区将数据加载到特定注入时间分区中。
使用伪列提高性能
如需提高查询性能,请在比较运算符的左侧单独使用 _PARTITIONTIME
伪列。
例如,以下两个查询是等效的。根据标的大小,第二个查询的性能可能更好,因为它将 _PARTITIONTIME
单独放置在 >
运算符的左侧。两个查询处理的数据量相同。
-- Might be slower. SELECT field1 FROM dataset.table1 WHERE TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15"); -- Often performs better. SELECT field1 FROM dataset.table1 WHERE _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY);
如需限制查询中扫描的分区,请在过滤条件中使用常量表达式。以下查询限制了根据 WHERE
子句中的第一个过滤条件删减哪些分区。但是,第二个过滤条件不会限制扫描的分区,因为它使用动态表值。
SELECT column FROM dataset.table2 WHERE -- This filter condition limits the scanned partitions: _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01') -- This one doesn't, because it uses dynamic table values: AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)
如需限制扫描的分区,请不要在 _PARTITIONTIME
过滤条件中包含任何其他列。例如,以下查询不限制扫描的分区,因为 field1
是表中的列。
-- Scans all partitions of table2. No pruning. SELECT field1 FROM dataset.table2 WHERE _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');
如果您经常查询特定时间范围,请考虑创建一个过滤 _PARTITIONTIME
伪列的视图。例如,以下语句会创建一个视图,其中仅包含名为 dataset.partitioned_table
的表中最近七天的数据:
-- This view provides pruning. CREATE VIEW dataset.past_week AS 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);
如需了解如何创建视图,请参阅创建视图。
查询整数范围分区表
如需在查询整数范围分区表时删减分区,请在整数分区列中添加过滤条件。
在以下示例中,假设 dataset.table
是分区范围为 customer_id:0:100:10
的整数范围分区表。此示例查询扫描从 30、40 和 50 开始的三个分区。
SELECT * FROM dataset.table WHERE customer_id BETWEEN 30 AND 50 +-------------+-------+ | customer_id | value | +-------------+-------+ | 40 | 41 | | 45 | 46 | | 30 | 31 | | 35 | 36 | | 50 | 51 | +-------------+-------+
整数范围分区列上的函数不支持分区删减。例如,以下查询会扫描整个表。
SELECT * FROM dataset.table WHERE customer_id + 1 BETWEEN 30 AND 50
查询写入优化存储空间中的数据
__UNPARTITIONED__
分区会暂时保存在写入优化存储空间中流式传输到分区表中的数据。直接流式插入到分区表的特定分区的数据不使用 __UNPARTITIONED__
分区。但是,该数据会直接流式插入到该分区。
写入优化存储空间中的数据在 _PARTITIONTIME
和 _PARTITIONDATE
列具有 NULL
值。
如需查询 __UNPARTITIONED__
分区中的数据,请使用值为 NULL
的 _PARTITIONTIME
伪列。例如:
SELECT column FROM dataset.table WHERE _PARTITIONTIME IS NULL
如需了解详情,请参阅流式传输到分区表。
删减分区的最佳做法
使用常量过滤条件表达式
如需限制查询中扫描的分区,请在过滤条件中使用常量表达式。如果您在查询过滤条件中使用动态表达式,则 BigQuery 必须扫描所有分区。
例如,由于过滤条件包含常量表达式,以下查询会删减分区:
SELECT t1.name, t2.category FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id_field = t2.field2 WHERE t1.ts = CURRENT_TIMESTAMP()
但是,以下查询不会删减分区,因为过滤条件 WHERE t1.ts = (SELECT timestamp from table where key = 2)
不是常量表达式;它取决于 timestamp
和 key
字段的动态值:
SELECT t1.name, t2.category FROM table1 AS t1 INNER JOIN table2 AS 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
要求在查询中使用分区过滤条件
创建分区表时,您可以启用需要分区过滤条件选项来要求使用谓词过滤条件。应用此选项时,如果试图在不指定 WHERE
子句的情况下查询分区表,则会产生以下错误:
Cannot query over table 'project_id.dataset.table' without a filter that can be
used for partition elimination
。
必须至少有一个谓词仅引用分区列,才能将过滤条件视为符合分区消除的条件。例如,对于按 partition_id
列(其架构中包含一个附加列 f
)进行分区的表,以下两个 WHERE
子句均满足要求:
WHERE partition_id = "20221231"
WHERE partition_id = "20221231" AND f = "20221130"
但是,WHERE (partition_id = "20221231" OR f = "20221130")
是不足以满足要求的。
对于提取时间分区表,请使用 _PARTITIONTIME
或 _PARTITIONDATE
伪列。
如需详细了解如何在创建分区表时添加需要分区过滤条件选项,请参阅创建分区表。 您还可以对现有表更新此设置。