Max number of partitions that can be created on bigquery table

1
votes

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

2
google-bigquery
Having this many partitions seems to have a foul smell to me. Can you share the query/data with us? - Tim Biegeleisen
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
Can you include the query and maybe some data? Perhaps there is a workaround which won't crash Big Query. - Tim Biegeleisen
See the following question to see how to get around this limit by partitioning by week/month/year: stackoverflow.com/a/56125049/132438 - Felipe Hoffa

2 Answers

5
votes

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

0
votes

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.