How to partition by month/quarter/year to get over the partition limit in Bigquery ?
Bigquery allows you to create partitioned tables. And partitioned tables can have a maximum of 4000 partitions per table. If you exceed that limit bigquery throws the below error message:
Too many partitions produced by query, allowed 4000, query produces at least 12384 partitions
Try partitioning based on month / quarter / year. And use cluster keys to prune the data efficiently.
CREATE TABLE `dataset.tablename` PARTITION BY FlightDate_year CLUSTER BY Origin, Dest AS SELECT *, DATE_TRUNC(FlightDate, YEAR) FlightDate_year FROM `flights.raw_load_fixed`
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