JOBS_TIMELINE_BY_FOLDER view

TheINFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER view contains near real-timeBigQuery metadata by timeslice for all jobs submitted in theparent folder of the current project, including the jobs in subfolders under it.This view contains both running and completed jobs.

Required permissions

To query theINFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER view, you needthebigquery.jobs.listAll Identity and Access Management (IAM) permission for the parentfolder. Each of the following predefined IAM roles includes therequired permission:

Folder Admin
BigQuery Admin

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.JOBS_TIMELINE_BY_* views, the queryresults contain one row for every second of execution of everyBigQuery job. Each period starts on a whole-second interval andlasts exactly one second.

TheINFORMATION_SCHEMA.JOBS_TIMELINE_BY_* view has the following schema:

Note: The underlying data is partitioned by thejob_creation_time column andclustered byproject_id anduser_email.

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.
`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.
`principal_subject`	`STRING`	A string representation of the identity of the principal that 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, seeIntroduction to BigQuery editions.
`total_bytes_billed`	`INTEGER`	If the project is configured to useon-demand pricing, then this field contains the total bytes billed for the job. If the project is configured to useflat-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. The value is`0.0` if the job ran with a reservation that uses autoscaling and has zero baseline slots.
`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 thetransaction in which this job ran, if any. (Preview)

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Data retention

This view displays running jobs along with job history for the past 180 days.If a project migrates to an organization (either from having no organization orfrom a different one), job information predating the migration date isn'taccessible through theINFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER view, as theview only retains data starting from the migration date.

Scope and syntax

Queries against this view must include a region qualifier.If you don't specify a regional qualifier, metadata is retrieved from allregions. 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_FOLDER	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: anydataset region name. For example,`region-us`.
Note: You must use a region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Examples

The following examples show how to query theINFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER view.

Get the number of unique jobs

The following query displays the number of unique jobs running per minute in thedesignated project's folder:

SELECTTIMESTAMP_TRUNC(period_start,MINUTE)ASper_start,COUNT(DISTINCTjob_id)ASunique_jobsFROM`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER,UNNEST(folder_numbers)fWHEREmy_folder_number=fGROUPBYper_startORDERBYper_startDESC;

Note:INFORMATION_SCHEMA view names are case-sensitive.

The result is similar to the following:

+---------------------------+---------------------------------+|  per_start                |  unique_jobs                    |+---------------------------+---------------------------------+|  2019-10-10 00:04:00 UTC  |  5                              ||  2019-10-10 00:03:00 UTC  |  2                              ||  2019-10-10 00:02:00 UTC  |  3                              ||  2019-10-10 00:01:00 UTC  |  4                              ||  2019-10-10 00:00:00 UTC  |  4                              |+---------------------------+---------------------------------+

Calculate the slot-time used

The following query displays the slot-time used per minute in thedesignated project's folder:

SELECTTIMESTAMP_TRUNC(period_start,MINUTE)ASper_start,SUM(period_slot_ms)ASslot_msFROM`region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER,UNNEST(folder_numbers)fWHEREmy_folder_number=fANDreservation_id="my reservation id"ANDstatement_type!="SCRIPT"GROUPBYper_startORDERBYper_startDESC;

Note:INFORMATION_SCHEMA view names are case-sensitive.Note: Projects within a single folder can be assigned to more than onereservation.JOBS_TIMELINE_BY_FOLDER can provide data across multiplereservations. When summingperiod_slot_ms, ensure that you are filteringfor individual reservations.