10 May 19 · DataFreak · #bigquery ·   Bookmark   ×

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

Solution

Try partitioning based on month / quarter / year. And use cluster keys to prune the data efficiently.

Example:

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`

DataFreak

posted on 10 May 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