How to get the List of all the partitions from BigQuery partitioned table

When you create table with require_partition_filter = true  in Bigquery, you will have to mention the partition column as part of the filter condition, otherwise the query will fail with the below error message:

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

Example:

Here's an example table:

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

Solution:

In order to find out the list of all the available partitions in a table, you can use the below query:

SELECT * FROM `mydataset.tablename$__PARTITIONS_SUMMARY__`

The bigquery.jobs.create permission is required to run this query

victor

posted on 15 Apr 19