Max number of partitions that can be created on bigquery table

Question

Im getting below error while moving data from unpartitioned to partitioned table.

"Resources exceeded during query execution: Table mydataset.mytable$20101213 will have 2501 partitions when the job finishes, exceeding limit 2500..'}]"

--> Whether bigquery limits on the max number of partitions that can be created on a table ? or whether this is just a day quota ?

Moreover, this limit is not mentioned in their BigQuery "Quotas and Limits" pages

Having this many partitions seems to have a foul smell to me. Can you share the query/data with us? — Tim Biegeleisen, Dec 15 '17 at 02:09
Im moving data from 2010 to 2017 using airflow bigquery jobs . So, basically Im trying to move 7 years of data into destination table partitioned by each day , which will be around 2555 (365 * 7) partitions — Mangai, Dec 15 '17 at 02:18
Can you include the query and maybe some data? Perhaps there is a workaround which won't crash Big Query. — Tim Biegeleisen, Dec 15 '17 at 02:24
See the following question to see how to get around this limit by partitioning by week/month/year: https://stackoverflow.com/a/56125049/132438 — Felipe Hoffa, May 14 '19 at 08:07

score 9 · Accepted Answer · edited May 07 '18 at 17:36

9

It's mentioned in the docs. Currently, you cannot have more than 4000 partitions per table. You'll need to split your data/partitions across multiple tables (each of around 7 years of data).

Each partitioned table can have up to 4000 partitions.

https://cloud.google.com/bigquery/docs/partitioned-tables#partitioned_table_limits

edited May 07 '18 at 17:36

Pentium10

204,586
122
423
502

answered Dec 15 '17 at 05:10

Graham Polley

14,393
4
44
80

2

What happens when you exceed that bound? – WJA Apr 14 '20 at 13:00
2

BigQuery will return an error and no more data is inserted in the table! @JohnAndrews – Silver Ringvee Jan 31 '21 at 17:40

score 0 · Answer 2 · edited May 04 '19 at 16:10

Once your data is split across multiple tables you can then write a view to union the two tables together. Whenever the view is queried with the partitioned field in the where clause only the relevant partitions will be processed by BigQuery.

This results in a view with 4,000+ partitions which business users can query without worrying about which version of a table they need to use or union-ing the tables themselves.

Max number of partitions that can be created on bigquery table

2 Answers2

Linked