JOBS_TIMELINE_BY_ORGANIZATION view

The INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION view contains near real-time BigQuery metadata by timeslice for all jobs submitted in the organization associated with the current project. This view contains currently running and completed jobs.

Required permissions

To query the INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION view, you need the bigquery.jobs.listAll Identity and Access Management (IAM) permission for the organization. Each of the following predefined IAM roles includes the required permission:

• BigQuery Resource Admin at the organization level
• Organization Owner
• Organization Admin

The JOBS_BY_ORGANIZATION schema table is only available to users with defined Google Cloud organizations.

For more information about BigQuery permissions, see Access control with IAM.

Schema

When you query the INFORMATION_SCHEMA.JOBS_TIMELINE_BY_* views, the query results contain one row for every second of execution of every BigQuery job. Each period starts on a whole-second interval and lasts exactly one second.

The INFORMATION_SCHEMA.JOBS_TIMELINE_BY_* view has the following schema:

Note: The underlying data is partitioned by the job_creation_time column and clustered by project_id and user_email.

Column name	Data type	Value
period_start	TIMESTAMP	Start time of this period.
period_slot_ms	INTEGER	Slot milliseconds consumed in this period.
period_shuffle_ram_usage_ratio	FLOAT	Shuffle usage ratio in the selected time period.
project_id	STRING	(Clustering column) ID of the project.
project_number	INTEGER	Number of the project.
folder_numbers	REPEATED INTEGER	Number IDs of the folders that contain the project, starting with the folder that immediately contains the project, followed by the folder that contains the child folder, and so forth. For example, if `folder_numbers` is `[1, 2, 3]`, then folder `1` immediately contains the project, folder `2` contains `1`, and folder `3` contains `2`.
user_email	STRING	(Clustering column) Email address or service account of the user who ran the job.
job_id	STRING	ID of the job. For example, bquxjob_1234.
job_type	STRING	The type of the job. Can be QUERY, LOAD, EXTRACT, COPY, or null. Job type null indicates an internal job, such as script job statement evaluation or materialized view refresh.
statement_type	STRING	The type of query statement, if valid. For example, SELECT, INSERT, UPDATE, or DELETE.
job_creation_time	TIMESTAMP	(Partitioning column) Creation time of this job. Partitioning is based on the UTC time of this timestamp.
job_start_time	TIMESTAMP	Start time of this job.
job_end_time	TIMESTAMP	End time of this job.
state	STRING	Running state of the job at the end of this period. Valid states include PENDING, RUNNING, and DONE.
reservation_id	STRING	Name of the primary reservation assigned to this job at the end of this period, if applicable.
total_bytes_processed	INTEGER	Total bytes processed by the job.
error_result	RECORD	Details of error (if any) as an ErrorProto.
cache_hit	BOOLEAN	Whether the query results of this job were from a cache.
period_estimated_runnable_units	INTEGER	Units of work that can be scheduled immediately in this period. Additional slots for these units of work accelerate your query, provided no other query in the reservation needs additional slots.

Data retention

This view contains currently running jobs and the job history of the past 180 days.

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 scope for this view:

View name	Resource scope	Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION	Organization that contains the specified project	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 specify the location to query the INFORMATION_SCHEMA views and 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         |
+-----------------------+----------------+---------------------+---------------+--------------------+