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 |
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 |
- 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.
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.
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 | +-----------------+----------------+--------------------+---------------------+-----------------+-------------------------+--------------------------------+