列出 BigQuery 分区表中的所有分区，并附require_partition_filter

Question

我正在尝试找到一种方法来列出用require_partition_filter = true创建的表的分区，但是我还找不到方法。

这是表创建脚本

CREATE TABLE mydataset.partitionedtable_partitiontime
(
x INT64 
)
PARTITION BY DATE(_PARTITIONTIME)
OPTIONS(
require_partition_filter = true
);

一些测试行

INSERT INTO mydataset.partitionedtable_partitiontime (_PARTITIONTIME, x) SELECT TIMESTAMP("2017-05-01"), 10;
INSERT INTO mydataset.partitionedtable_partitiontime (_PARTITIONTIME, x) SELECT TIMESTAMP("2017-04-01"), 20;
INSERT INTO mydataset.partitionedtable_partitiontime (_PARTITIONTIME, x) SELECT TIMESTAMP("2017-03-01"), 30;

正如预期的那样，如果尝试以下查询来获取分区，我会收到错误，因为我需要在分区列顶部使用过滤器

SELECT _PARTITIONTIME as pt, FORMAT_TIMESTAMP("%Y%m%d", _PARTITIONTIME) as partition_id
FROM `mydataset.partitionedtable_partitiontime`
GROUP BY _PARTITIONTIME
ORDER BY _PARTITIONTIME

错误

Cannot query over table 'mydataset.partitionedtable_partitiontime' without a filter over column(s) '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME' that can be used for partition elimination

任何想法如何列出分区？

编辑：我知道可以添加过滤器，但我正在寻找像Hive的"显示分区表名"这样的解决方案来列出所有分区(本质上是元数据(

谢谢！

Answer 1

这是这样做的方法：

SELECT * FROM `mydataset.partitionedtable_partitiontime$__PARTITIONS_SUMMARY__`

bigquery.jobs.create权限是必需的。

编辑：现在可以使用标准SQL获取此信息：

SELECT * FROM `myproject.mydataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'partitionedtable'

Answer 2

正如 hlagos 所提到的，如果您使用的是标准 SQL，则可以通过查询_PARTITIONTIME伪列或旧版 SQL 的__PARTITIONS_SUMMARY__元表来获取此数据。

您可以查看此 GCP 文档，其中包含有关此分区表元数据使用情况的详细信息。