PARTITIONS view

The INFORMATION_SCHEMA.PARTITIONS view contains one row for each partition.

Querying the INFORMATION_SCHEMA.PARTITIONS view is limited to 1000 tables. To get the data about partitions at the project level, you can split the query into multiple queries and then join the results. If you exceed the limit, you can encounter an error similar to the following:

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

Required permissions

To query the INFORMATION_SCHEMA.PARTITIONS view, you need the following Identity and Access Management (IAM) permissions:

bigquery.tables.get
bigquery.tables.list

Each of the following predefined IAM roles includes the preceding permissions:

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

For more information about BigQuery permissions, see Access control with IAM .

Schema

When you query the INFORMATION_SCHEMA.PARTITIONS view, the query results typically contain one row for each partition. The exception is when there is a 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 each storage tier.

The INFORMATION_SCHEMA.PARTITIONS view has the following schema:

Column name

Data type

Value

TABLE_CATALOG

STRING

The project ID of the project that contains the table

TABLE_SCHEMA

STRING

The name of the dataset that contains the table, also referred to as the datasetId

TABLE_NAME

STRING

The name of the table, also referred to as the tableId

PARTITION_ID

STRING

A single partition's ID. For unpartitioned tables, the value is NULL . For partitioned tables that contain rows with NULL values in the partitioning column, the value is __NULL__ .

TOTAL_ROWS

INTEGER

The total number of rows in the partition

TOTAL_LOGICAL_BYTES

INTEGER

The total number of logical bytes in the partition

LAST_MODIFIED_TIME

TIMESTAMP

The 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, see BigQuery 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_TIER

STRING

The partition's storage tier:

ACTIVE : the partition is billed as active storage
LONG_TERM : the partition is billed as long-term storage

Scope and syntax

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

View name	Resource scope	Region scope
`[ PROJECT_ID .] DATASET_ID .INFORMATION_SCHEMA.PARTITIONS`	Dataset level	Dataset 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, see Dataset qualifier .

Examples

Example 1

The following example calculates the number of logical bytes used by each storage tier in all of the tables in a dataset named mydataset :

 SELECT 
  
 storage_tier 
 , 
  
 SUM 
 ( 
 total_logical_bytes 
 ) 
  
 AS 
  
 logical_bytes 
 FROM 
  
 ` mydataset 
.INFORMATION_SCHEMA.PARTITIONS` 
 GROUP 
  
 BY 
  
 storage_tier 
 ;

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 the partition_id field and aggregates partition information at the table level for the public bigquery-public-data.covid19_usafacts dataset:

 SELECT 
  
 table_name 
 , 
  
 CASE 
  
 WHEN 
  
 regexp_contains 
 ( 
 partition_id 
 , 
  
 '^[0-9]{4}$' 
 ) 
  
 THEN 
  
 'YEAR' 
  
 WHEN 
  
 regexp_contains 
 ( 
 partition_id 
 , 
  
 '^[0-9]{6}$' 
 ) 
  
 THEN 
  
 'MONTH' 
  
 WHEN 
  
 regexp_contains 
 ( 
 partition_id 
 , 
  
 '^[0-9]{8}$' 
 ) 
  
 THEN 
  
 'DAY' 
  
 WHEN 
  
 regexp_contains 
 ( 
 partition_id 
 , 
  
 '^[0-9]{10}$' 
 ) 
  
 THEN 
  
 'HOUR' 
  
 END 
  
 AS 
  
 partition_type 
 , 
  
 min 
 ( 
 partition_id 
 ) 
  
 AS 
  
 earliest_partition 
 , 
  
 max 
 ( 
 partition_id 
 ) 
  
 AS 
  
 latest_partition_id 
 , 
  
 COUNT 
 ( 
 partition_id 
 ) 
  
 AS 
  
 partition_count 
 , 
  
 sum 
 ( 
 total_logical_bytes 
 ) 
  
 AS 
  
 sum_total_logical_bytes 
 , 
  
 max 
 ( 
 last_modified_time 
 ) 
  
 AS 
  
 max_last_updated_time 
 FROM 
  
 `bigquery-public-data.covid19_usafacts.INFORMATION_SCHEMA.PARTITIONS` 
 GROUP 
  
 BY 
  
 1 
 , 
  
 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 |
+-----------------+----------------+--------------------+---------------------+-----------------+-------------------------+--------------------------------+