BigQuery Increases Maximum Partitions Per Table from 4000 to 10000

May 31, 2024 at 8:58:15 AM

TL;DR Now, the maximum number of partitions by table in BigQuery has increased from 4000 to 10000. Partitioned tables are divided into segments called partitions. Specifying a partition column allows you to run queries over those partitions to scan only the relevant data, improving performance and reducing costs. BigQuery supports three types of partitions: Integer Range Partitioning, Time-Unit Column Partitioning, and Ingestion Time Partitioning.

BigQuery Increases Maximum Partitions Per Table from 4000 to 10000

The maximum number of partitions per table in BigQuery has increased from 4,000 to 10,000. Partitioned tables in BigQuery are divided into segments called partitions, which improve query performance and reduce costs by scanning only relevant parts of the data. There are three types of partitions:

Types of Partitions

  1. Integer Range Partitioning

    • Partitions based on ranges of integer values in a specified column.
  2. Time-Unit Column Partitioning

    • Partitions based on DATE, TIMESTAMP, or DATETIME columns.
    • Granularity can be HOUR, DAY, MONTH, or YEAR.
  3. Ingestion Time Partitioning

    • Partitions based on the time when data is ingested into BigQuery.
    • Granularity can be HOUR, DAY, MONTH, or YEAR.
    • Adds pseudocolumns _PARTITIONTIME and _PARTITIONDATE.

When to Use Partitioning

  • Improve query performance by scanning only a portion of a table.
  • Exceed standard table quotas by scoping operations to specific partition column values.
  • Determine query costs before execution using query cost estimates.
  • Utilize partition-level management features like setting expiration times, writing data to specific partitions, or deleting specific partitions.

When to Use Clustering Instead

  • Need more granularity than partitioning allows.
  • Queries commonly use filters or aggregation against multiple columns.
  • High cardinality of values in a column or group of columns.
  • No need for strict cost estimates before query execution.
  • Small amount of data per partition (less than 10 GB).
  • Large number of partitions exceeding limits.
  • Frequent DML operations modifying most partitions.

Combining Clustering and Partitioning

Combining table partitioning with clustering achieves finer-grained sorting and query optimization. Data is first segmented into partitions and then clustered within each partition based on user-defined sort properties.

Partition Decorators

Partition decorators allow referencing a specific partition in a table. Formats vary by partitioning type:

  • Hourly: yyyymmddhh
  • Daily: yyyymmdd
  • Monthly: yyyymm
  • Yearly: yyyy
  • Integer range: range_start

Limitations

  • Cannot use legacy SQL for partitioned tables.
  • Only one column can be used for partitioning.
  • Specific limitations for time-unit and integer-range partitioned tables regarding column types and field levels.

Quotas, Limits, and Pricing

Partitioned tables have defined limits and quotas for various operations like loading, exporting, querying, and copying data. Charges are based on data storage and queries run against the data. Many partitioned table operations are free but subject to quotas and limits.

Table Security

Access control for partitioned tables is the same as for standard tables. For more information, see the relevant BigQuery documentation.

Have more questions on this topic? Ask our AI assistant for in-depth insights.

The Only Digital Marketing Feed You'll Ever Need.

Stay informed your way. Tailored updates when and how you want them. 100% Free.

10,000+ Users

500+ Sources

1000+ Tools

Or

Related Posts

Automate Your Marketing Audits - Say Goodbye to Manual Checklists

Automate Your Marketing Audits - Say Goodbye to Manual Checklists

Featured
Google Sheets Integrates BigQuery Saved Queries

Google Sheets Integrates BigQuery Saved Queries

Google
Google

Official Source

Official Source

Google is a Official Source. The source has been verified by Swipe Insight team.

Official Source
BigQuery Expands Search Index Capabilities with INT64 and TIMESTAMP Support

BigQuery Expands Search Index Capabilities with INT64 and TIMESTAMP Support

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source
Google Ends Free Access to Gemini in BigQuery, Announces Paid Plans

Google Ends Free Access to Gemini in BigQuery, Announces Paid Plans

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source
Google Cloud Enhances Looker with Major User Experience Updates

Google Cloud Enhances Looker with Major User Experience Updates

Sean Zinsmeister
Sean Zinsmeister

Official Source

Official Source

Sean Zinsmeister is a Official Source. The source has been verified by Swipe Insight team.

Official Source
BigQuery Launches Gemini-Enhanced SQL Translation Features

BigQuery Launches Gemini-Enhanced SQL Translation Features

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source
BigQuery Data Transfer Service Launches Data Source Change Log for Schema Updates

BigQuery Data Transfer Service Launches Data Source Change Log for Schema Updates

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source
BigQuery Introduces AI-Augmented Data Preparation with Gemini

BigQuery Introduces AI-Augmented Data Preparation with Gemini

Google Cloud
Google Cloud

Official Source

Official Source

Google Cloud is a Official Source. The source has been verified by Swipe Insight team.

Official Source

Related Tools

Marketing Auditor logo

Marketing Auditor

Verified Tool

Verified Tool

Marketing Auditor is a Verified Tool. Want to get this badge? Contact us.

Verified Tool

Automated audits for Google Ads and Analytics.

Get Featured Here

Showcase your tool in this list.

Contact Us
Databricks logo

Databricks

Generative AI-powered data intelligence platform

Data Engineering
GA4 SQL logo

GA4 SQL

Verified Tool

Verified Tool

GA4 SQL is a Verified Tool. Want to get this badge? Contact us.

Verified Tool

Generate GA4 BigQuery queries easily

Data Analysis
TapClicks logo

TapClicks

Automated marketing solutions powered by your data

Data Engineering
Stitch logo

Stitch

Automated cloud data pipelines, no coding needed

Data Engineering
Akkio logo

Akkio

AI-powered analytics for agencies

Data Analysis
NinjaCat logo

NinjaCat

AI-powered marketing data and analytics platform

Reporting
Funnel logo

Funnel

Aggregate and analyze marketing data seamlessly

Reporting
Fivetran logo

Fivetran

Effortlessly centralize and move data from any source

Data Engineering
Power My Analytics logo

Power My Analytics

Automate and integrate your marketing data

Reporting

Get Featured Here

Showcase your tool in this list.

Contact Us