Bigquery : Too many partitions produced by query, allowed 2000, query produces at least xxx partitions

Partitioning allows you to effectively prune a table and also bring down the querying costs in Bigquery. However, In Bigquery, we are limited to 2000 partitions per table

Example Query:

CREATE TABLE `deleting.day_partition`
PARTITION BY FlightDate 
AS 
SELECT *
FROM `flights.original` 

You will get an error like:

Too many partitions produced by query, allowed 2000, query produces 
at least 11384 partitions

Solution:

Instead of partitioning by day, you could partition by week/month/year. Note that we created the extra column DATE_TRUNC(FlightDate, YEAR) AS FlightDate_year in the process.

For this, I'll create a year date column, and partition by it:

CREATE TABLE `fh-bigquery.flights.ontime_201903`
PARTITION BY FlightDate_year
CLUSTER BY Origin, Dest 
AS
SELECT *, DATE_TRUNC(FlightDate, YEAR) FlightDate_year
FROM `fh-bigquery.flights.raw_load_fixed`

Alternatively, you can also check with Google to see if they might increase the limits on your account

Mike-Barn

posted on 22 Nov 19