Getting reservations metadata using INFORMATION_SCHEMA

Alpha

This feature is covered by the Pre-GA Offerings Terms of the Google Cloud Platform Terms of Service. Pre-GA features may have limited support, and changes to pre-GA features may not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

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;
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

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;
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

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

• For an overview of INFORMATION_SCHEMA, see Introduction to BigQuery INFORMATION_SCHEMA.
• Learn how to use INFORMATION_SCHEMA to get dataset metadata.
• Learn how to use INFORMATION_SCHEMA to get job metadata.
• Learn how to use INFORMATION_SCHEMA to get table metadata.
• Learn how to use INFORMATION_SCHEMA to get view metadata.