Listing all the partitions from BigQuery partitioned table with require_partition_filter

Question

I am trying to find a way to list the partitions of a table created with require_partition_filter = true however I am not able to find the way yet.

This is table creation script

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

Some test rows

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;

As expected, If a try the following query to get the partitions, I am getting an error because I need to user a filter on top of the partitioning column

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

Error

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

any ideas how to list the partitions?

EDIT: I know that it is possible to add the filter, but I am looking for a solution like "SHOW PARTITIONS TABLENAME" of Hive to list all the partitions (which are essentially metadata)

Thanks!

hlagos hlagos · Accepted Answer · 2019-04-15T14:55:37

Here is the way how to do it

SELECT * FROM `mydataset.partitionedtable_partitiontime$__PARTITIONS_SUMMARY__`

The bigquery.jobs.create permission is required for it

EDIT: Now is possible to get this information from using standard sql from the metadata table INFORMATION_SCHEMA.PARTITIONS.

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

Listing all the partitions from BigQuery partitioned table with require_partition_filter

2 Answers