JOBS_TIMELINE view
The INFORMATION_SCHEMA.JOBS_TIMELINE
view contains near real-time
BigQuery metadata by timeslice for all jobs submitted in the
current project. This view contains currently running and completed jobs.
Required permissions
To query the INFORMATION_SCHEMA.JOBS_TIMELINE
view, you need the
bigquery.jobs.listAll
Identity and Access Management (IAM) permission for the project.
Each of the following predefined IAM roles includes the required
permission:
- Project Owner
- BigQuery Admin
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:
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_PROJECT] |
Project level | REGION |
- 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
.
Examples
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,
`myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE
.
The following example calculates the slot utilization for every second in the last day:
SELECT period_start, SUM(period_slot_ms) AS total_slot_ms, FROM `reservation-admin-project.region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE WHERE period_start BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP() GROUP BY period_start ORDER BY period_start DESC;Note: `INFORMATION_SCHEMA` view names are case-sensitive. The result is similar to the following:
+---------------------+---------------+ | period_start | total_slot_ms | +---------------------+---------------+ | 2020-07-29 03:52:14 | 122415176 | | 2020-07-29 03:52:15 | 141107048 | | 2020-07-29 03:52:16 | 173335142 | | 2020-07-28 03:52:17 | 131107048 | +---------------------+---------------+
You can check usage for a particular reservation with
WHERE reservation_id = "…"
. For script jobs, the parent job also reports the
total slot usage from its children jobs. To avoid double counting, use
WHERE statement_type != "SCRIPT"
to exclude the parent job.
Example: Number of RUNNING
and PENDING
jobs over time
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,
`myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE
.
The following example computes the number of RUNNING
and PENDING
jobs at every
second in the last day:
SELECT period_start, SUM(IF(state = "PENDING", 1, 0)) as PENDING, SUM(IF(state = "RUNNING", 1, 0)) as RUNNING FROM `reservation-admin-project.region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE WHERE period_start BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP() GROUP BY period_start;
The result is similar to the following:
+---------------------+---------+---------+ | period_start | PENDING | RUNNING | +---------------------+---------+---------+ | 2020-07-29 03:52:14 | 7 | 27 | | 2020-07-29 03:52:15 | 1 | 21 | | 2020-07-29 03:52:16 | 5 | 21 | | 2020-07-29 03:52:17 | 4 | 22 | +---------------------+---------+---------+
Example: Resource usage by jobs at a specific point in time
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,
`myproject`.`region-us`.INFORMATION_SCHEMA.JOBS
.
The following example returns the job_id
of all jobs running at a specific
point in time together with their resource usage during that one-second period:
SELECT job_id, period_slot_ms FROM `reservation-admin-project.region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT WHERE period_start = '2020-07-29 03:52:14' AND statement_type != 'SCRIPT';
The result is similar to the following:
+------------------+ | job_id | slot_ms | +------------------+ | job_1 | 2415176 | | job_2 | 4417245 | | job_3 | 427416 | | job_4 | 1458122 | +------------------+