Bigquery: Partitioning data past 2000 limit (Update: Now 4000 limit)

user_95396 0
Viewed 1242

From the BigQuery page on partitioned tables:

Each table can have up to 2,000 partitions.

We planned to partition our data by day. Most of our queries will be date based but we have about 5 years historical data and plan to collect more each day from now. With only 2000 partitions: 2000/365 gives us about 5.5 years worth of data.

What is the best practice for tables wanting more than 2000 partitions?

  • Create a different table per year and join tables when required?
  • Is it possible to partition by week or month instead?
  • Can that 2000 partition limit be increased if you ask support?

Update: Table limit is now 4000 partitions.

google-bigquery
3 Answers

The limit is now 4,000 partitions which is just over 10 years of data. However if you have more than 10 years of data and would like it partitioned by day one workaround we have used is splitting your table into decades and then writing a view on top to union the decade tables together.

When querying the view with the date partitioned field in the where clause BigQuery knows to only process the required partitions even if this is across multiple or within a single table.

We have used this approach to ensure business users (data analysts and report developers) only need to worry about a single table but still access the performance and cost benefits of partitioned tables.

user_96472480
Related