查询分区表

查询分区表

您可以通过以下方式查询分区表：

• 使用 Cloud Console
• 使用 bq 命令行工具的 bq query 命令
• 调用 jobs.insert API 方法并配置查询作业
• 使用客户端库

如需详细了解如何运行查询，请参阅运行交互式查询和批量查询。

准备工作

授予为用户提供执行本文档中的每个任务所需权限的 Identity and Access Management (IAM) 角色。

所需权限

如需查询分区表，您需要拥有 IAM 权限才能查询表和运行查询作业。

查询表的权限

如需查询表，您需要拥有 bigquery.tables.getData IAM 权限。

以下每个预定义 IAM 角色都包含创建视图所需的权限：

• roles/bigquery.dataViewer
• roles/bigquery.dataOwner
• roles/bigquery.dataEditor
• roles/bigquery.admin

此外，如果您拥有 bigquery.datasets.create 权限，则可以在自己创建的数据集中更新查询表和视图。

运行查询作业的权限

如需运行查询作业，您需要拥有 bigquery.jobs.create IAM 权限。

以下每个预定义 IAM 角色都包含运行查询作业所需的权限：

• roles/bigquery.user
• roles/bigquery.jobUser
• roles/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 用于从输入扫描中消除不必要的分区的机制。计算查询扫描的字节时，不包括已删减的分区，从而降低按需分析费用。一般来说，当过滤条件由常量表达式组成时，分区删减有助于降低查询费用，这些过滤条件可以在查询开始时求值。

注意：_PARTITIONTIME 和 _PARTITIONDATE 伪列的某些用法不限制扫描的分区数。如需了解详情，请参阅删减（限制）分区。如需查看包含伪列但不限制扫描分区数的查询示例，请参阅扫描所有分区的伪列查询。

例如，以下查询仅扫描分区表中 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");

查询整数范围分区表

整数范围分区表只能通过标准 SQL 进行查询。在查询整数范围分区表时，如果整数分区列有过滤条件，则这些过滤条件将用于删减分区和降低查询费用。

以下查询将扫描起始值为 30、40 和 50 的 3 个分区，以查找分区规范为 customer_id:0:100:10 的整数范围分区表。

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.mytable WHERE customer_id BETWEEN 30 AND 50'

下面是 bq query 命令的示例输出结果：

+-------------+-------+
| customer_id | value |
+-------------+-------+
|          40 |    41 |
|          45 |    46 |
|          30 |    31 |
|          35 |    36 |
|          50 |    51 |
+-------------+-------+

在本示例中，每个分区有 2 行，每行有 2 个整数列，因此查询会扫描 3 * 2 * 2 * 8 = 96 个字节。您可以检查作业信息：

bq show -j bqjob_r4fce65fa3381528e_000001670994aeb6_1

下面是 bq show 命令的示例输出结果：

  Job Type    State      Start Time      Duration       User Email        Bytes Processed   Bytes Billed   Billing Tier   Labels
 ---------- --------- ----------------- ---------- --------------------- ----------------- -------------- -------------- --------
  query      SUCCESS   24 Sep 12:19:58   0:00:01    [e-mail]      96                10485760       1

支持 DML 语句。例如：

bq query --nouse_legacy_sql \
'DELETE FROM mydataset.mytable WHERE customer_id = 30'

目前，整数范围分区列上的函数不支持分区删减。例如，以下查询会扫描整个表。

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.mytable WHERE customer_id+1 BETWEEN 30 AND 50'

整数范围分区表上的表修饰器

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

以下示例查询起始值为 0 的范围分区。分区列为 customer_id，分区有两行。

bq query --use_legacy_sql=true 'SELECT * FROM mydataset.mytable$0'

+-------------+-------+
| customer_id | value |
+-------------+-------+
|           0 |     1 |
|           5 |     6 |
+-------------+-------+

使用提取时间分区表的伪列创建视图

如需将查询读取的数据量限制为一组分区，请创建一个包含 _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。

注意：必须至少有一个谓词仅引用分区列，才能将过滤条件视为符合分区消除的条件。对于提取时间分区表，这将是 _PARTITIONTIME 或 _PARTITIONDATE 伪列。例如，对于按 partition_id 列（其架构中包含一个附加列 f）进行分区的表，以下两个 WHERE 子句均满足要求：
WHERE partition_id = "foo"
WHERE partition_id = "foo" AND f = "bar"

但是，WHERE (partition_id = "foo" OR f = "bar") 是不足以满足要求的。

如需详细了解如何在创建分区表时添加需要分区过滤条件选项，请参阅创建分区表。

如果在创建分区表时未启用需要分区过滤条件选项，您可以更新表来添加该选项。

删减（限制）分区

如需限制查询中扫描的分区，请在过滤条件中使用常量表达式。如果您在查询过滤条件中使用动态表达式，则 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

后续步骤

• 如需分区表的概览，请参阅分区表简介。
• 如需详细了解如何创建分区表，请参阅创建分区表。