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
andRESERVATIONS_BY_PROJECT
requirebigquery.reservations.list
for the project and is available to theBigQuery User
,BigQuery Resource Admin
andBigQuery Admin
roles.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
andCAPACITY_COMMITMENTS_BY_PROJECT
requirebigquery.capacityCommitments.list
for the project and is available to theBigQuery User
,BigQuery Resource Admin
andBigQuery Admin
roles.ASSIGNMENT_CHANGES_BY_PROJECT
andASSIGNMENTS_BY_PROJECT
requirebigquery.reservationAssignments.list
for the project and is available to theBigQuery User
,BigQuery Resource Admin
andBigQuery 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
In the Cloud Console, open the BigQuery page.
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;
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
In the Cloud Console, open the BigQuery page.
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;
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
In the Cloud Console, open the BigQuery page.
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;
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
- 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.