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

  1. In the Cloud Console, open the BigQuery page.

    Go to the BigQuery page

  2. 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;
    
  3. 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

  1. In the Cloud Console, open the BigQuery page.

    Go to the BigQuery page

  2. 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;
    
  3. 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

  1. In the Cloud Console, open the BigQuery page.

    Go to the BigQuery page

  2. 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;
    
  3. 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