RESERVATIONS view

The INFORMATION_SCHEMA.RESERVATIONS view 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. For more information about reservation, see Introduction to reservations.

Required permission

To query the INFORMATION_SCHEMA.RESERVATIONS view, you need the bigquery.reservations.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.RESERVATIONS view has the following schema:

Column name	Data type	Value
`ddl`	`STRING`	The DDL statement used to create this reservation.
`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.
`autoscale`	`STRUCT`	Information about the autoscale capacity of the reservation. Fields include the following: `current_slots`: the current slots assigned to the reservation. `max_slots`: the maximum number of slots that could be added to the reservation by autoscaling.
`edition`	`STRING`	The edition associated with this reservation. For more information about editions, see Introduction to BigQuery editions.

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.RESERVATIONS[_BY_PROJECT]	Project level	`REGION`

Replace the following:

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

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. The following example shows an example of a using the JOIN clause between the reservation and the job views.

Example

The following example shows slot usage, slot capacity, and assigned reservation for a project with a reservation assignment, over the past hour. Slot usage is given in units of slot milliseconds per second.

WITH
  job_data AS (
  SELECT
    job.period_start,
    job.reservation_id,
    job.period_slot_ms,
    job.job_id,
    job.job_type
  FROM
    `my-project.region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE AS job
  WHERE
    job.period_start > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
SELECT
  reservation.reservation_name AS reservation_name,
  job.period_start,
  reservation.slot_capacity,
  job.period_slot_ms,
  job.job_id,
  job.job_type
FROM
  job_data AS job
INNER JOIN
  `reservation-admin-project.region-us`.INFORMATION_SCHEMA.RESERVATIONS AS reservation
ON
  (job.reservation_id = CONCAT(reservation.project_id, ":", "US", ".", reservation.reservation_name));

The output is similar to the following:

+------------------+---------------------+---------------+----------------+------------------+----------+
| reservation_name |    period_start     | slot_capacity | period_slot_ms |           job_id | job_type |
+------------------+---------------------+---------------+----------------+------------------+----------+
| my_reservation   | 2021-04-30 17:30:54 |           100 |          11131 | bquxjob_66707... | QUERY    |
| my_reservation   | 2021-04-30 17:30:55 |           100 |          49978 | bquxjob_66707... | QUERY    |
| my_reservation   | 2021-04-30 17:30:56 |           100 |           9038 | bquxjob_66707... | QUERY    |
| my_reservation   | 2021-04-30 17:30:57 |           100 |          17237 | bquxjob_66707... | QUERY    |

This query uses the RESERVATIONS 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 joins RESERVATIONS with JOBS_TIMELINE to associate the job timeslices with the reservation information.