22 Nov 19 · npack · #Bigquery ·   Bookmark   ×

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

npack

posted on 22 Nov 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

Copied