PARTITIONS view

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

TheINFORMATION_SCHEMA.PARTITIONS view contains one row for each partition.

Querying theINFORMATION_SCHEMA.PARTITIONS view is limited to 1000tables. To get the data about partitions at the project level, you can split thequery into multiple queries and then join the results. If you exceed the limit,you might encounter an error similar to the following. To narrow down yourresults, you can use filters with theWHERE statement, for example,table_name = 'mytable' andtotal_logical_bytes IS NOT NULL.

INFORMATION_SCHEMA.PARTITIONS query attempted to read too many tables. Please add more restrictive filters.

Required permissions

To query theINFORMATION_SCHEMA.PARTITIONS view, you need the followingIdentity and Access Management (IAM) permissions:

  • bigquery.tables.get
  • bigquery.tables.list

Each of the following predefined IAM roles includes the precedingpermissions:

  • roles/bigquery.admin
  • roles/bigquery.dataEditor
  • roles/bigquery.dataViewer

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.PARTITIONS view, the query resultstypically contain one row for each partition. The exception is when there isa combination of long-term and active storage tier data in the__UNPARTITIONED__ partition. In that case,the view returns two rows for the__UNPARTITIONED__ partition, one for eachstorage tier.

TheINFORMATION_SCHEMA.PARTITIONS view has the following schema:

Column nameData typeValue
table_catalogSTRINGThe project ID of the project that contains the table.
table_schemaSTRINGThe name of the dataset that contains the table, also referred to as thedatasetId.
table_nameSTRINGThe name of the table, also referred to as thetableId.
partition_idSTRINGA single partition's ID. For unpartitioned tables, the value isNULL. For partitioned tables that contain rows withNULL values in the partitioning column, the value is__NULL__.
total_rowsINTEGERThe total number of rows in the partition.
total_logical_bytesINTEGERThe total number of logical bytes in the partition.
total_billable_bytesINTEGERThe total number of billable bytes in the partition. If billing for your storage is based on physical (compressed) bytes, this value will not match theTOTAL_LOGICAL_BYTES number.
last_modified_timeTIMESTAMPThe most recent time that data was written to the partition. It is used to calculate a partition's eligibility for long-term storage. After 90 days, the partition automatically transitions from active storage to long-term storage. For more information, seeBigQuery storage pricing. This field is updated when data is inserted, loaded, streamed, or modified within the partition. Modifications that involve record deletions might not be reflected.
storage_tierSTRINGThe partition's storage tier:

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include a dataset qualifier. Forqueries with a dataset qualifier, you must have permissions for the dataset.For more informationseeSyntax.The following table explains the region and resource scopes for this view:

View nameResource scopeRegion scope
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.PARTITIONSDataset levelDataset location
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

Examples

Example 1

The following example calculates the number of logical bytes used by eachstorage tier in all of the tables in a dataset namedmydataset:

SELECTstorage_tier,SUM(total_logical_bytes)ASlogical_bytesFROM`mydataset.INFORMATION_SCHEMA.PARTITIONS`GROUPBYstorage_tier;
Note:INFORMATION_SCHEMA view names are case-sensitive.

The results look similar to the following:

+--------------+----------------+| storage_tier | logical_bytes  |+--------------+----------------+| LONG_TERM    |  1311495144879 || ACTIVE       |    66757629240 |+--------------+----------------+

Example 2

The following example creates a column that extracts the partition type from thepartition_id field and aggregates partition information at the table levelfor the publicbigquery-public-data.covid19_usafacts dataset:

SELECTtable_name,CASEWHENregexp_contains(partition_id,'^[0-9]{4}$')THEN'YEAR'WHENregexp_contains(partition_id,'^[0-9]{6}$')THEN'MONTH'WHENregexp_contains(partition_id,'^[0-9]{8}$')THEN'DAY'WHENregexp_contains(partition_id,'^[0-9]{10}$')THEN'HOUR'ENDASpartition_type,min(partition_id)ASearliest_partition,max(partition_id)ASlatest_partition_id,COUNT(partition_id)ASpartition_count,sum(total_logical_bytes)ASsum_total_logical_bytes,max(last_modified_time)ASmax_last_updated_timeFROM`bigquery-public-data.covid19_usafacts.INFORMATION_SCHEMA.PARTITIONS`GROUPBY1,2;

The results look similar to the following:

+-----------------+----------------+--------------------+---------------------+-----------------+-------------------------+--------------------------------+| table_name      | partition_type | earliest_partition | latest_partition_id | partition_count | sum_total_logical_bytes | max_last_updated_time          |+--------------+-------------------+--------------------+---------------------+-----------------+-------------------------+--------------------------------+| confirmed_cases | DAY            | 20221204           | 20221213            | 10              | 26847302                | 2022-12-13 00:09:25.604000 UTC || deaths          | DAY            | 20221204           | 20221213            | 10              | 26847302                | 2022-12-13 00:09:24.709000 UTC || summary         | DAY            | 20221204           | 20221213            | 10              | 241285338               | 2022-12-13 00:09:27.496000 UTC |+-----------------+----------------+--------------------+---------------------+-----------------+-------------------------+--------------------------------+

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.