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 and RESERVATIONS_BY_PROJECT, you need the following:

    • bigquery.reservations.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 CAPACITY_COMMITMENT_CHANGES_BY_PROJECT and CAPACITY_COMMITMENTS_BY_PROJECT, you need the following:

    • bigquery.capacityCommitments.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 ASSIGNMENT_CHANGES_BY_PROJECT and ASSIGNMENTS_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