CAPACITY_COMMITMENTS view

The INFORMATION_SCHEMA.CAPACITY_COMMITMENTS view contains a near real-time 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.

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