15 Apr 19 ·  victor in bigquery ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

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 
that can be used for partition elimination


Here's an example table:

CREATE TABLE mydataset.tablename
Col1 INT64 
OPTIONS(require_partition_filter = true);


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


posted on 15 Apr 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds