RESERVATIONS_TIMELINE view

The INFORMATION_SCHEMA.RESERVATIONS_TIMELINE view shows near real-time timeslices of reservation metadata for each reservation admin project for every minute. To compare slot usage to slot capacity, you can combine the reservation information with the jobs timeline by using the reservation timeline view.

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`	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.
`period_start`	`TIMESTAMP`	Start time of this one-minute period.
`project_id`	`STRING`	ID of the reservation admin 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 admin project.

Scope and syntax

Queries against this view must include a region qualifier. If you do not 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 minute

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-minute slot usage from projects assigned to YOUR_RESERVATION_ID across all jobs:

SELECT
  res.period_start,
  SUM(jobs.period_slot_ms) / 1000 / 60 AS period_slot_minutes,
  ANY_VALUE(res.slots_assigned) AS slot_assigned,
  ANY_VALUE(res.slots_max_assigned) AS slots_max_assigned
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION jobs
JOIN
  `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE res
  ON
    TIMESTAMP_TRUNC(jobs.period_start, MINUTE) = res.period_start
    AND jobs.reservation_id = res.reservation_id
WHERE
  jobs.job_creation_time
    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_minutes | slots_assigned| slots_max_assigned|
+-----------------------+---------------------+---------------+-------------------+
|2021-06-08 21:33:00 UTC|       100.000       |      100      |        100        |
|2021-06-08 21:32:00 UTC|        96.753       |      100      |        100        |
|2021-06-08 21:31:00 UTC|        41.668       |      100      |        100        |
+-----------------------+---------------------+---------------+-------------------+

Example: Slot usage by reservation

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

SELECT
  res.period_start,
  res.reservation_id,
  SUM(jobs.period_slot_ms) / 1000 / 60 AS period_slot_minutes,
  ANY_VALUE(res.slots_assigned) AS slots_assigned,
  ANY_VALUE(res.slots_max_assigned) AS slots_max_assigned,
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION jobs
JOIN
  `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE res
  ON TIMESTAMP_TRUNC(jobs.period_start, MINUTE) = res.period_start
     AND jobs.reservation_id = res.reservation_id
WHERE
  jobs.job_creation_time
  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_minutes | slot_assigned | slots_max_assigned |
+-----------------------+----------------+---------------------+---------------+--------------------+
|2021-06-08 21:33:00 UTC|     prod01     |       100.000       |      100      |        100         |
|2021-06-08 21:33:00 UTC|     prod02     |       177.201       |      200      |        500         |
|2021-06-08 21:32:00 UTC|     prod01     |        96.753       |      100      |        100         |
|2021-06-08 21:32:00 UTC|     prod02     |       182.329       |      200      |        500         |
+-----------------------+----------------+---------------------+---------------+--------------------+