RESERVATIONS view
The INFORMATION_SCHEMA.RESERVATIONS
view contains a near real-time 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:
ddl
STRING
project_id
STRING
project_number
INTEGER
reservation_name
STRING
ignore_idle_slots
BOOL
slot_capacity
INTEGER
target_job_concurrency
INTEGER
autoscale
STRUCT
Information about the autoscale capacity of the reservation. Fields include the following:
-
current_slots
: the number of slots added to the reservation by autoscaling. -
max_slots
: the maximum number of slots that could be added to the reservation by autoscaling.
edition
STRING
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
|
- 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
.
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.