查询分区表

本文档介绍了在 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

使用旧版 SQL 查询整数范围分区表

不能使用旧版 SQL 查询整个整数范围分区表。而是会返回如下错误：

Querying tables partitioned on a field is not supported in Legacy SQL

但是，旧版 SQL 支持使用表修饰器来处理整数范围分区表中的特定分区。处理范围分区的关键是范围的起始值。

以下示例查询起始值为 30 的范围分区。

SELECT * FROM dataset.table$30

查询写入优化存储空间中的数据

__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 伪列。

如需详细了解如何在创建分区表时添加需要分区过滤条件选项，请参阅创建分区表。 您还可以对现有表更新此设置。

后续步骤