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.
Before you begin
Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.
Required permissions
To retrieve reservations metadata by using INFORMATION_SCHEMA
tables, you need the following IAM permissions:
For
RESERVATION_CHANGES_BY_PROJECT
andRESERVATIONS_BY_PROJECT
, you need the following:bigquery.reservations.list
for the projectEach of the following predefined IAM roles includes the preceding permissions:
roles/bigquery.resourceAdmin
roles/bigquery.resourceEditor
roles/bigquery.resourceViewer
roles/bigquery.user
roles/bigquery.admin
For
CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
andCAPACITY_COMMITMENTS_BY_PROJECT
, you need the following:bigquery.capacityCommitments.list
for the projectEach of the following predefined IAM roles includes the preceding permissions:
roles/bigquery.resourceAdmin
roles/bigquery.resourceEditor
roles/bigquery.resourceViewer
roles/bigquery.user
roles/bigquery.admin
For
ASSIGNMENT_CHANGES_BY_PROJECT
andASSIGNMENTS_BY_PROJECT
, you need the following:bigquery.reservationAssignments.list
for the project.Each of the following predefined IAM roles includes the preceding permissions:
roles/bigquery.resourceAdmin
roles/bigquery.resourceEditor
roles/bigquery.resourceViewer
roles/bigquery.user
roles/bigquery.admin
For more information about granular BigQuery permissions, see Predefined roles and permissions.
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 |
---|---|---|
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. |
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 |
---|---|---|
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. |
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 |
---|---|---|
ddl |
STRING |
The DDL statement used to create this assignment. |
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
The following examples show various uses of the reservation views.
Current reservation assignment for a project
The following example gets a 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.
SELECT reservation.reservation_name, reservation.slot_capacity FROM `reservation-admin-project.region-us`. INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT assignment INNER JOIN `reservation-admin-project.region-us`. INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT AS reservation ON (assignment.reservation_name = reservation.reservation_name) WHERE assignment.assignee_id = "my-project" AND job_type = "QUERY";
Reservation history
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, such as creating or deleting the reservation.
SELECT * FROM `reservation-admin-project.region-us`. INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT WHERE reservation_name = "my-reservation" ORDER BY change_timestamp DESC;
Slot usage over time
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_BY_PROJECT 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_BY_PROJECT 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_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 joins RESERVATIONS_BY_PROJECT
with
JOBS_TIMELINE_BY_PROJECT
to
associate the job timeslices with the reservation information.
What's next
- For an overview of
INFORMATION_SCHEMA
, see Introduction to BigQueryINFORMATION_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.