Getting reservations metadata using INFORMATION_SCHEMA

INFORMATION_SCHEMA is a series of views that provide access to metadata about datasets, routines, tables, views, jobs, reservations, and streaming data.

You can query the INFORMATION_SCHEMA reservation views to retrieve real-time metadata about BigQuery reservations. These views contain a list of changes to reservations, assignments, and capacity commitments, along with a timeline of reservations.

Required permissions

Retrieving reservations metadata by using INFORMATION_SCHEMA tables requires appropriately scoped permissions:

RESERVATION_CHANGES_BY_PROJECT and RESERVATIONS_BY_PROJECT require bigquery.reservations.list for the project and is available to the BigQuery User, BigQuery Resource Admin and BigQuery Admin roles.
CAPACITY_COMMITMENT_CHANGES_BY_PROJECT and CAPACITY_COMMITMENTS_BY_PROJECT require bigquery.capacityCommitments.list for the project and is available to the BigQuery User, BigQuery Resource Admin and BigQuery Admin roles.
ASSIGNMENT_CHANGES_BY_PROJECT and ASSIGNMENTS_BY_PROJECT require bigquery.reservationAssignments.list for the project and is available to the BigQuery User, BigQuery Resource Admin and BigQuery Admin roles.

Schemas

When you query the INFORMATION_SCHEMA reservations views, the query results contain information about BigQuery reservations. For more information about BigQuery reservations, see the reservations concepts page.

INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT contains a list of all changes to reservations within the administration project. Each row represents a change to a single reservation.
INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT contains a list of all current reservations within the administration project. Each row represents a single, current reservation. A current reservation is a reservation that has not been deleted.
INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT contains a list of all changes to capacity commitments within the administration project. Each row represents a single change to a single capacity commitment.
INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT 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.
INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT contains a list of all changes to assignments within the administration project. Each row represents a single change to a single assignment.
INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT contains a list of all current assignments within the administration project. Each row represents a single, current assignment. A current assignment is either pending or active and has not been deleted.

The INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT 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.
`reservation_name`	`STRING`	User provided reservation name.
`ignore_idle_slots`	`BOOL`	If false, any query using this reservation can use unused idle slots from other capacity commitments.
`action`	`STRING`	Type of event that occurred with the reservation. Can be `CREATE`, `UPDATE`, or `DELETE`.
`slot_capacity`	`INTEGER`	Slot capacity associated of the reservation.
`user_email`	`STRING`	Email address of the user that made the change. `google` for changes made by Google. `NULL` if the email address is unknown.

The INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT view has the following schema:

Column name	Data type	Value
`project_id`	`STRING`	ID of the administration project.
`project_number`	`INTEGER`	Number of the administration project.
`reservation_name`	`STRING`	User provided reservation name.
`ignore_idle_slots`	`BOOL`	If false, any query using this reservation can use unused idle slots from other capacity commitments.
`slot_capacity`	`INTEGER`	Slot capacity associated of the reservation.

The INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT 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 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`.

The INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT view has the following schema:

Column name	Data type	Value
`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.

The INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT 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.
`assignment_id`	`STRING`	ID that uniquely identifies the assignment.
`reservation_name`	`STRING`	Name of the reservation that the assignment uses.
`job_type`	`STRING`	The type of job that can use the reservation. Can be `PIPELINE` or `QUERY`.
`assignee_id`	`STRING`	ID that uniquely identifies the assignee resource.
`assignee_number`	`INTEGER`	Number that uniquely identifies the assignee resource.
`assignee_type`	`STRING`	Type of assignee resource. Can be `organization`, `folder` or `project`.
`action`	`STRING`	Type of event that occurred with the assignment. Can be `CREATE` or `DELETE`.
`user_email`	`STRING`	Email address of the user that made the change. `google` for changes made by Google. `NULL` if the email address is unknown.
`state`	`STRING`	State of the assignment. Can be `PENDING` or `ACTIVE`.

The INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT view has the following schema:

Column name	Data type	Value
`project_id`	`STRING`	ID of the administration project.
`project_number`	`INTEGER`	Number of the administration project.
`assignment_id`	`STRING`	ID that uniquely identifies the assignment.
`reservation_name`	`STRING`	Name of the reservation that the assignment uses.
`job_type`	`STRING`	The type of job that can use the reservation. Can be `PIPELINE` or `QUERY`.
`assignee_id`	`STRING`	ID that uniquely identifies the assignee resource.
`assignee_number`	`INTEGER`	Number that uniquely identifies the assignee resource.
`assignee_type`	`STRING`	Type of assignee resource. Can be `organization`, `folder` or `project`.

Data retention

Current reservations, capacity commitments, and assignments are kept in the reservation views until they are deleted. Deleted reservations, capacity commitments, and assignments are kept in the RESERVATION_CHANGES_BY_PROJECT, CAPACITY_COMMITMENT_CHANGES_BY_PROJECT, and ASSIGNMENT_CHANGES_BY_PROJECT views (respectively) for a maximum of 41 days, whereupon they are removed from the views.

Regionality

The BigQuery INFORMATION_SCHEMA reservations views are regionalized. To query these views, you must use a region qualifier.

Joining between the reservation views and the job views

The job views contain the column reservation_id. If your job ran in a project with a reservation assigned to it, reservation_id would follow this format: reservation-admin-project:reservation-location.reservation-name.

To join between the reservation views and the job views, you can join between the job views column reservation_id and the reservation views columns project_id and reservation_name; see this example.

Examples

Example 1

The following example gets the project's currently assigned reservation and its slot capacity. This information is useful for debugging job performance by comparing the project's slot usage with the slot capacity of the reservation assigned to that project.

The query should run on the admin project that contains reservations. To run the query against a project other than your default project, add the project ID in the following format: `project-id`.`region-region-name`.INFORMATION_SCHEMA.view. For example, `reservation-admin-project`.`region-us`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT.

To run the query:

Console

In the Cloud Console, open the BigQuery page.

Go to the BigQuery page

In the Query editor box, enter the following standard SQL query. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

SELECT
  reservation.reservation_name,
  reservation.slot_capacity
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT assignment
INNER JOIN
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT AS reservation
ON
  (assignment.reservation_name = reservation.reservation_name)
WHERE
  assignment.action = "CREATE"
  AND assignment.assignee_id = "my-project"
  AND job_type = "QUERY" /* can also be "PIPELINE */
ORDER BY
  assignment.change_timestamp DESC,
  reservation.change_timestamp DESC
LIMIT
  1;

Click Run.

gcloud

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
  reservation.reservation_name,
  reservation.slot_capacity
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT assignment
INNER JOIN
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT AS reservation
ON
  (assignment.reservation_name = reservation.reservation_name)
WHERE
  assignment.action = "CREATE"
  AND assignment.assignee_id = "my-project"
  AND job_type = "QUERY" /* can also be "PIPELINE */
ORDER BY
  assignment.change_timestamp DESC,
  reservation.change_timestamp DESC
LIMIT
  1;'

Example 2

The following example gets the history of changes for a given reservation. Use this information to see the list of changes made to a specific reservation.

To run the query against a project other than your default project, add the project ID in the following format: `project-id`.`region-region-name`.INFORMATION_SCHEMA.view. For example, `reservation-admin-project`.`region-us`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT.

To run the query:

Console

In the Cloud Console, open the BigQuery page.

Go to the BigQuery page
In the Query editor box, enter the following standard SQL query. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.
```
SELECT
  *
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
WHERE
  reservation_name = "..."
ORDER BY
  change_timestamp DESC;
```
Note: INFORMATION_SCHEMA view names are case-sensitive.
Click Run.

gcloud

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
  *
FROM
  `reservation-admin-project.region-us`.
  INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
WHERE
  reservation_name = "..."
ORDER BY
  change_timestamp DESC;'

Example 3

The following example joins between the JOBS_BY_PROJECT and the RESERVATIONS_BY_PROJECT views that provide both the slot usage by a given assignee project in the past hour and the slot capacity of each reservation in the given admin project. An assignee project is a project that has a reservation assigned to it, and an admin project is the project that contains reservations. For more information, see the documentation for reservations. This query uses the RESERVATIONS_BY_PROJECT view to get reservation information; if the reservations have changed in the past hour, the reservation_slot_capacity column might not be accurate.

The query should run using the admin project that contains reservations or the assignee project. To run the query against a project other than your default project, add the project ID in the following format: `project-id`.`region-region-name`.INFORMATION_SCHEMA.view. For example, `reservation-admin-project`.`region-us`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT.

To run the query:

Console

In the Cloud Console, open the BigQuery page.

Go to the BigQuery page

In the Query editor field, enter the following standard SQL query. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

 WITH
  job_data AS (
  SELECT
    job.reservation_id,
    job.total_slot_ms
  FROM
    `assignee-project.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS job
  WHERE
    job.start_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) )
SELECT
  reservation.reservation_name AS reservation_name,
  ANY_VALUE(reservation.slot_capacity) AS reservation_slot_capacity,
  SUM(job.total_slot_ms) / (1000 * 60 * 60) AS avg_slots_used_by_hour
FROM
  job_data AS job
INNER JOIN
  `admin-project.region-us`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT AS reservation
ON
  (job.reservation_id = CONCAT(reservation.project_id, ":", "US", ".", reservation.reservation_name))
GROUP BY
  1
ORDER BY
  1 DESC;

Click Run.

gcloud

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'WITH
   job_data AS (
   SELECT
     job.reservation_id,
     job.total_slot_ms
   FROM
     `assignee-project.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS job
   WHERE
     job.start_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) )
 SELECT
   reservation.reservation_name AS reservation_name,
   ANY_VALUE(reservation.slot_capacity) AS reservation_slot_capacity,
   SUM(job.total_slot_ms) / (1000 * 60 * 60) AS avg_slots_used_by_hour
 FROM
   job_data AS job
 INNER JOIN
   `admin-project.region-us`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT AS reservation
 ON
   (job.reservation_id = CONCAT(reservation.project_id, ":", "US", ".", reservation.reservation_name))
 GROUP BY
   1
 ORDER BY
   1 DESC;'

Next steps

For an overview of INFORMATION_SCHEMA, see Introduction to BigQuery INFORMATION_SCHEMA.
Learn how to use INFORMATION_SCHEMA to get job metadata.
Learn how to use INFORMATION_SCHEMA to get streaming metadata.
Learn how to use INFORMATION_SCHEMA to get dataset metadata.
Learn how to use INFORMATION_SCHEMA to get table metadata.
Learn how to use INFORMATION_SCHEMA to get view metadata.