JOBS_TIMELINE_BY_USER view

The INFORMATION_SCHEMA.JOBS_TIMELINE_BY_USER view contains near real-time BigQuery metadata by timeslice of the jobs submitted by the current user in the current project. This view contains currently running and completed jobs.

Required permissions

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

  • Project Viewer
  • BigQuery User

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.
project_id STRING (Clustering column) ID of the project.
project_number INTEGER Number of the project.
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.
priority STRING The priority of this job. Valid values include INTERACTIVE and BATCH.
parent_job_id STRING ID of the parent job, if any.
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.
edition STRING The edition associated with the reservation assigned to this job. For more information about editions, see Introduction to BigQuery editions.
total_bytes_billed INTEGER If the project is configured to use on-demand pricing, then this field contains the total bytes billed for the job. If the project is configured to use flat-rate pricing, then you are not billed for bytes and this field is informational only.
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_shuffle_ram_usage_ratio FLOAT Shuffle usage ratio in the selected time period.
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.
transaction_id STRING ID of the transaction in which this job ran, if any. (Preview)

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

View name Resource scope Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_USER Jobs submitted by the current user in 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`.

    • Example

    The following query displays the total slot milliseconds consumed per second by jobs submitted by the current user in the designated project:

    SELECT
  period_start,
  SUM(period_slot_ms) AS total_period_slot_ms
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_USER
GROUP BY
  period_start
ORDER BY
  period_start DESC;

    The result is similar to the following:

    +---------------------------+---------------------------------+
|  period_start             |  total_period_slot_ms           |
+---------------------------+---------------------------------+
|  2019-10-10 00:00:04 UTC  |  118639                         |
|  2019-10-10 00:00:03 UTC  |  251353                         |
|  2019-10-10 00:00:02 UTC  |  1074064                        |
|  2019-10-10 00:00:01 UTC  |  1124868                        |
|  2019-10-10 00:00:00 UTC  |  1113961                        |
+---------------------------+---------------------------------+