查询分区表

查询分区表

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

  • 使用 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 PST2016-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 是分区表。

查询分区表

根据 TIMESTAMPDATEDATETIMEINTEGER 列分区的表没有伪列。如需限制查询分区表时扫描的分区数,请使用谓词过滤条件(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) 不是常量表达式;它取决于 timestampkey 字段的动态值:

#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

后续步骤