Stay organized with collections Save and categorize content based on your preferences.

PARTITIONS view

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

Required permissions

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

  • bigquery.tables.get
  • bigquery.tables.getData
  • 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 contain one row for each partition.

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
TOTAL_BILLABLE_BYTES INTEGER The total number of billable bytes in the partition
LAST_MODIFIED_TIME TIMESTAMP The time when the data was most recently written to the partition
STORAGE_TIER STRING The partition's storage tier:

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 Cloud project. If not specified, the default project is used.
  • DATASET_ID: the ID of your dataset. For more information, see Dataset qualifier.

For queries with a dataset qualifier, you must have permissions for the dataset. For queries with a region qualifier, you must have permissions for the project.

Example

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

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

The results look similar to the following:

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