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
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_name STRING The name of the reservation.
ignore_idle_slots BOOL False if slot sharing is enabled, otherwise true.
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.
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.
  • max_slots: the maximum number of slots that could be added to the reservation by autoscaling.
reservation_id STRING For joining with the jobs_timeline table. This is of the form project_id:location.reservation_name.

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.

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         |
+-----------------------+----------------+---------------------+---------------+--------------------+