CAPACITY_COMMITMENT_CHANGES view

The INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES view contains a list of all changes to capacity commitments within the administration project. Each row represents a single change to a single capacity commitment. For more information about reservation, see Commitments.

Required permission

To query the INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES 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_COMMITMENT_CHANGES view has the following schema:

Column name	Data type	Value
`change_timestamp`	`TIMESTAMP`	Time when the change occurred.
`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.
`action`	`STRING`	Type of event that occurred with the capacity commitment. Can be `CREATE`, `UPDATE`, or `DELETE`.
`user_email`	`STRING`	Email address of the user or subject of the workforce identity federation that made the change. `google` for changes made by Google. `NULL` if the email address is unknown.
`commitment_start_time`	`TIMESTAMP`	The start of the current commitment period. Only applicable for `ACTIVE` capacity commitments, otherwise this is `NULL`.
`commitment_end_time`	`TIMESTAMP`	The end of the current commitment period. Only applicable for `ACTIVE` capacity commitments, otherwise this is `NULL`.
`failure_status`	`RECORD`	For a `FAILED` commitment plan, provides the failure reason, otherwise this is `NULL`. `RECORD` consists of `code` and `message`.
`renewal_plan`	`STRING`	The plan this capacity commitment is converted to after `commitment_end_time` passes. After the plan is changed, the committed period is extended according to the commitment plan. Only applicable for `ANNUAL` and `TRIAL` commitments, otherwise this is `NULL`.
`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.

Data retention

This view contains current capacity commitments and the deleted capacity commitments that are kept for a maximum of 41 days after which they are removed from the view.

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_COMMITMENT_CHANGES[_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 query displays the user who has made the latest capacity commitment update to the current project within the specified date.

SELECT
  user_email,
  change_timestamp
FROM
  `region-us`.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES
WHERE
  change_timestamp BETWEEN '2021-09-30' AND '2021-10-01'
ORDER BY
  change_timestamp DESC
LIMIT 1;

The result is similar to the following:

+--------------------------------+-------------------------+
|           user_email           |     change_timestamp    |
+--------------------------------+-------------------------+
|     222larabrown@gmail.com     | 2021-09-30 09:30:00 UTC |
+--------------------------------+-------------------------+