RESERVATIONS_TIMELINE view

The INFORMATION_SCHEMA.RESERVATIONS_TIMELINE view shows time slices of reservation metadata for each reservation administration project for every minute in real time. Additionally, the per_second_details array shows autoscale details for each second.

Required permission

To query the INFORMATION_SCHEMA.RESERVATION_TIMELINE view, you need the bigquery.reservations.list Identity and Access Management (IAM) permission on the project. Each of the following predefined IAM roles includes the required permission:

BigQuery Resource Admin (roles/bigquery.resourceAdmin)
BigQuery Resource Editor (roles/bigquery.resourceEditor)
BigQuery Resource Viewer (roles/bigquery.resourceViewer)
BigQuery User (roles/bigquery.user)
BigQuery Admin (roles/bigquery.admin)

For more information about BigQuery permissions, see BigQuery IAM roles and permissions.

Schema

When you query the INFORMATION_SCHEMA.RESERVATIONS_TIMELINE_BY_* views, the query results contain one row for every minute of every BigQuery reservation in the last 180 days, and one row for every minute with reservation changes for any occurrences older than 180 days. Each period starts on a whole-minute interval and lasts exactly one minute.

The INFORMATION_SCHEMA.RESERVATIONS_TIMELINE_BY_PROJECT view has the following schema:

Column name	Data type	Value
`autoscale`	`STRUCT`	Contains information about the autoscale capacity of the reservation. Fields include the following: `current_slots`: the number of slots added to the reservation by autoscaling. Note: After users reduce `max_slots`, it may take a while before it can be propagated, so `current_slots` may stay in the original value and could be larger than `max_slots` for that brief period (less than one minute). `max_slots`: the maximum number of slots that could be added to the reservation by autoscaling.
`edition`	`STRING`	The edition associated with this reservation. For more information about editions, see Introduction to BigQuery editions.
`ignore_idle_slots`	`BOOL`	False if slot sharing is enabled, otherwise true.
`labels`	`RECORD`	Array of labels associated with the reservation.
`period_start`	`TIMESTAMP`	Start time of this one-minute period.
`per_second_details`	`STRUCT`	Contains information about the reservation capacity and usage at each second. Fields include the following: `start_time`: the exact timestamp of the second. `autoscale_current_slots`: the number of slots added to the reservation by autoscaling at this second. Note: When you reduce `max_slots`, the change might not take effect immediately. During this brief period (under one minute), the `current_slots` might remain at its original value that could be higher than the value of `max_slots`. `autoscale_max_slots`: the maximum number of slots that could be added to the reservation by autoscaling at this second. `slots_assigned`: the number of slots assigned to this reservation at this second. It equals the baseline slot capacity of a reservation. `slots_max_assigned`: the maximum slot capacity for this reservation, including slot sharing at this second. If `ignore_idle_slots` is true, this field is same as `slots_assigned`. Otherwise, the `slots_max_assigned` field is the total number of slots in all capacity commitments in the administration project. If there are any autoscale or reservation changes during this minute, the array is populated with 60 rows. However, for non-autoscale reservations that remain unchanged during this minute, the array is empty because it'll otherwise repeat the same number 60 times.
`project_id`	`STRING`	ID of the reservation administration project.
`project_number`	`INTEGER`	Number of the project.
`reservation_id`	`STRING`	For joining with the jobs_timeline table. This is of the form project_id:location.reservation_name.
`reservation_name`	`STRING`	The name of the reservation.
`slots_assigned`	`INTEGER`	The number of slots assigned to this reservation.
`slots_max_assigned`	`INTEGER`	The maximum slot capacity for this reservation, including slot sharing. If `ignore_idle_slots` is true, this is the same as `slots_assigned`, otherwise this is the total number of slots in all capacity commitments in the administration project.
`max_slots`	`INTEGER`	The maximum number of slots that this reservation can use, which includes baseline slots (`slot_capacity`), idle slots (if `ignore_idle_slots` is false), and autoscale slots. This field is specified by users for using the reservation predictability feature.
`scaling_mode`	`STRING`	The scaling mode for the reservation, which determines how the reservation scales from baseline to `max_slots`. This field is specified by users for using the reservation predictability feature.

Scope and syntax

Queries against this view must include a region qualifier. If you don't specify a regional qualifier, metadata is retrieved from all regions. The following table explains the region and resource scope for this view:

View name	Resource scope	Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE[_BY_PROJECT]	Project level	`REGION`

Replace the following:

Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
REGION: any dataset region name. For example, `region-us`.

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

Examples

Example: See total slot usage per second

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

. For example, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION.

The following example shows per-second slot usage from projects assigned to YOUR_RESERVATION_ID across all jobs:

SELECT
  s.start_time AS period_start,
  SUM(jobs.period_slot_ms) / 1000 AS period_slot_seconds,
  ANY_VALUE(s.slots_assigned) AS estimated_slots_assigned,
  ANY_VALUE(s.slots_max_assigned) AS estimated_slots_max_assigned
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION jobs
JOIN `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE res
    ON jobs.reservation_id = res.reservation_id
 JOIN res.per_second_details s
    ON jobs.period_start = s.start_time
WHERE
  jobs.job_creation_time
    BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
        AND CURRENT_TIMESTAMP()
  AND res.period_start
    BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
        AND CURRENT_TIMESTAMP()
  AND res.reservation_id = 'YOUR_RESERVATION_ID'
  AND (jobs.statement_type != "SCRIPT" OR jobs.statement_type IS NULL)  -- Avoid duplicate byte counting in parent and children jobs.
GROUP BY
  period_start
ORDER BY
  period_start DESC;

The result is similar to the following:

+-----------------------+---------------------+--------------------------+------------------------------+
|     period_start      | period_slot_seconds | estimated_slots_assigned | estimated_slots_max_assigned |
+-----------------------+---------------------+--------------------------+------------------------------+
|2021-06-08 21:33:59 UTC|       100.000       |         100              |           100                |
|2021-06-08 21:33:58 UTC|        96.753       |         100              |           100                |
|2021-06-08 21:33:57 UTC|        41.668       |         100              |           100                |
+-----------------------+---------------------+--------------------------+------------------------------+

Example: Slot usage by reservation

The following example shows per-second slot usage for each reservation in the last day:

SELECT
  s.start_time AS period_start,
  res.reservation_id,
  SUM(jobs.period_slot_ms) / 1000 AS period_slot_seconds,
  ANY_VALUE(res.slots_assigned) AS estimated_slots_assigned,
  ANY_VALUE(res.slots_max_assigned) AS estimated_slots_max_assigned,
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION jobs
JOIN `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE res
  ON jobs.reservation_id = res.reservation_id
JOIN res.per_second_details s
  ON jobs.period_start = s.start_time
WHERE
  jobs.job_creation_time
      BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
          AND CURRENT_TIMESTAMP()
  AND res.period_start
      BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
          AND CURRENT_TIMESTAMP()
  AND (jobs.statement_type != "SCRIPT" OR jobs.statement_type IS NULL)  -- Avoid duplicate byte counting in parent and children jobs.
GROUP BY
  period_start,
  reservation_id
ORDER BY
  period_start DESC,
  reservation_id;

The result is similar to the following:

+-----------------------+----------------+---------------------+--------------------------+------------------------------+
|     period_start      | reservation_id | period_slot_seconds | estimated_slots_assigned | estimated_slots_max_assigned |
+-----------------------+----------------+---------------------+--------------------------+------------------------------+
|2021-06-08 21:33:59 UTC|     prod01     |       100.000       |             100          |              100             |
|2021-06-08 21:33:58 UTC|     prod02     |       177.201       |             200          |              500             |
|2021-06-08 21:32:57 UTC|     prod01     |        96.753       |             100          |              100             |
|2021-06-08 21:32:56 UTC|     prod02     |       182.329       |             200          |              500             |
+-----------------------+----------------+---------------------+--------------------------+------------------------------+