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 \ncolumn(s) '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME' \nthat can be used for partition elimination

Example:

Here's an example table:

CREATE TABLE mydataset.tablename\n(\nCol1 INT64 \n)\nPARTITION BY DATE(_PARTITIONTIME)\nOPTIONS(require_partition_filter = true);\n

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__`\n

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

victor

posted on