CAPACITY_COMMITMENTS view

The INFORMATION_SCHEMA.CAPACITY_COMMITMENTS view contains a list of all current capacity commitments within the administration project. Each row represents a single, current capacity commitment. A current capacity commitment is either pending or active and has not been deleted. For more information about reservation, see Commitments.

Required permission

To query the INFORMATION_SCHEMA.CAPACITY_COMMITMENTS view, you need the bigquery.capacityCommitments.list Identity and Access Management (IAM) permission for the project. Each of the following predefined IAM roles includes the required permission:

roles/bigquery.resourceAdmin
roles/bigquery.resourceEditor
roles/bigquery.resourceViewer
roles/bigquery.user
roles/bigquery.admin

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

Schema

The INFORMATION_SCHEMA.CAPACITY_COMMITMENTS view has the following schema:

Column name	Data type	Value
`ddl`	`STRING`	The DDL statement used to create this capacity commitment.
`project_id`	`STRING`	ID of the administration project.
`project_number`	`INTEGER`	Number of the administration project.
`capacity_commitment_id`	`STRING`	ID that uniquely identifies the capacity commitment.
`commitment_plan`	`STRING`	Commitment plan of the capacity commitment.
`state`	`STRING`	State the capacity commitment is in. Can be `PENDING` or `ACTIVE`.
`slot_count`	`INTEGER`	Slot count associated with the capacity commitment.
`edition`	`STRING`	The edition associated with this reservation. For more information about editions, see Introduction to BigQuery editions.
`is_flat_rate`	`BOOL`	Whether the commitment is associated with the legacy flat-rate capacity model or an edition. If `FALSE`, the current commitment is associated with an edition. If `TRUE`, the commitment is the legacy flat-rate capacity model.
`renewal_plan`	`STRING`	New commitment plan after the end of current commitment plan. You can change the renewal plan for a commitment at any time until it expires.

Scope and syntax

Queries against this view must include a region qualifier. If you do not specify a regional qualifier, metadata is retrieved from all regions. The following table explains the region scope for this view:

View name	Resource scope	Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.CAPACITY_COMMITMENTS[_BY_PROJECT]	Project level	`REGION`

Replace the following:

Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
REGION: any dataset region name. For example, region-us.

Note: You must specify the location to query the INFORMATION_SCHEMA views and the location of the query execution must match the region of the INFORMATION_SCHEMA view.

Example

The following example returns a list of active capacity commitments for the current project:

SELECT
  capacity_commitment_id,
  slot_count
FROM
  `region-us`.INFORMATION_SCHEMA.CAPACITY_COMMITMENTS
WHERE
  state = 'ACTIVE';

The result is similar to the following:

+------------------------+------------+
| capacity_commitment_id | slot_count |
+------------------------+------------+
|    my_commitment_05    |    1000    |
|    my_commitment_06    |    1000    |
|    my_commitment_07    |    1500    |
|    my_commitment_08    |    2000    |
+------------------------+------------+