CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Amazing Developer
Amazing Developer

Reputation: 19

Big Query table partitions count limit

I am currently working with Big Query and understand that there is a partition limit of up to 4,000 partitions.

Does anyone know if this limit apply to Active Storage Tier only or both Active & Long Term Storage Tier?

Reason for asking because I have a partitioned table, partitioned by hour and have been using it for more than 6 months already but we don't get any error prompting partition limit exceed 4,000 when we insert new data.

I have did a count on the number of partition attached image below:

enter image description here

As we can see the total partitions is 6,401 and we are still able to insert new data.

At the same we also create a new partitioned table and try moving data into this newly created partitioned table but we encountered some error saying we have exceeded the limit of 4,000.

enter image description here

In addition, I also tried to insert data incrementally but I still get error as follow:

enter image description here

Steps to reproduce error:

  1. Create a partitioned table (partition by hour)
  2. Start moving data by month from another table

My finding:

The mentioned partition limit is only applicable to active storage tier.

Can anyone help to confirm on this?

Upvotes: 1

Views: 943

Answers (1)

kelyen
kelyen

Reputation: 242

As I understood the limitation, you can't modify more than 4000 partitions in one job. Your jobs that you describe first are supposedly working because they are modifying only a few partitions. When you try to move more than 4000 partitions in one go, you will hit the limitation as you described.

I noticed I was hitting this limitation on both Active Storage and Long Term Storage. This is a BigQuery-wide limitation.

Upvotes: 1

Related Questions