Getting reservations metadata using INFORMATION_SCHEMA

INFORMATION_SCHEMA is a series of views that provide access to metadata about datasets, routines, tables, views, jobs, and reservations.

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_TIMELINE_BY_PROJECT requires bigquery.reservations.list for the project and is available to the BigQuery User, BigQuery Resource Admin and BigQuery Admin roles.
  • RESERVATION_CHANGES_BY_PROJECT requires 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 requires bigquery.capacityCommitments.list for the project and is available to the BigQuery User, BigQuery Resource Admin and BigQuery Admin roles.
  • ASSIGNMENT_CHANGES_BY_PROJECT requires 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.RESERVATION_TIMELINE_BY_PROJECT is a timeline view for all reservations within the administration project. For each unique change_timestamp, this view lists the state of all active reservations at that time.
  • 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.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.

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.

The INFORMATION_SCHEMA.RESERVATION_TIMELINE_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.
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.

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 INTEGER 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.

Data retention

Currently, only the last 180 days of reservation history is retained in INFORMATION_SCHEMA reservations views.

Regionality

The BigQuery INFORMATION_SCHEMA reservations views are regionalized. To query these views, you must prefix a supported region name in the format `region-region-name`.INFORMATION_SCHEMA.view.

For example:

  • To query data in the US multi-region, use `region-us`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
  • To query data in the EU multi-region, use `region-eu`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
  • To query data in the asia-northeast1 region, use `region-asia-northeast1`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT

For a list of available regions, see Dataset locations.

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 web UI.

    Go to the Cloud Console

  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 web UI.

    Go to the Cloud Console

  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;'

Next steps