JOBS_BY_ORGANIZATION view

The INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION view contains near real-time metadata about all jobs submitted in the organization that is associated with the current project.

Required role

To get the permission that you need to query the INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION view, ask your administrator to grant you the BigQuery Resource Viewer (roles/bigquery.resourceViewer) IAM role on your organization. For more information about granting roles, see Manage access.

This predefined role contains the bigquery.jobs.listAll permission, which is required to query the INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION view.

You might also be able to get this permission with custom roles or other predefined roles.

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

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

Schema

The underlying data is partitioned by the creation_time column and clustered by project_id and user_email. The query_info column contains additional information about your query jobs.

The INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION view has the following schema:

Column name	Data type	Value
bi_engine_statistics	RECORD	If the project is configured to use the BI Engine SQL Interface, then this field contains BiEngineStatistics. Otherwise NULL.
cache_hit	BOOLEAN	Whether the query results of this job were from a cache. If you have a multi-query statement job, cache_hit for your parent query is NULL.
creation_time	TIMESTAMP	(Partitioning column) Creation time of this job. Partitioning is based on the UTC time of this timestamp.
destination_table	RECORD	Destination table for results, if any.
end_time	TIMESTAMP	The end time of this job, in milliseconds since the epoch. This field represents the time when the job enters the DONE state.
error_result	RECORD	Details of any errors as ErrorProto objects.
job_id	STRING	The ID of the job. For example, bquxjob_1234.
job_stages	RECORD	Query stages of the job. Note: This column's values are empty for queries that read from tables with row-level access policies. For more information, see best practices for row-level security in BigQuery.
job_type	STRING	The type of the job. Can be QUERY, LOAD, EXTRACT, COPY, or NULL. A NULL value indicates an internal job, such as a script job statement evaluation or a materialized view refresh.
labels	RECORD	Array of labels applied to the job as key-value pairs.
parent_job_id	STRING	ID of the parent job, if any.
priority	STRING	The priority of this job. Valid values include INTERACTIVE and BATCH.
project_id	STRING	(Clustering column) The ID of the project.
project_number	INTEGER	The number of the project.
query	STRING	SQL query text. Only the JOBS_BY_PROJECT view has the query column.
referenced_tables	RECORD	Array of tables referenced by the job. Only populated for query jobs that are not cache hits.
reservation_id	STRING	Name of the primary reservation assigned to this job, in the format RESERVATION_ADMIN_PROJECT:RESERVATION_LOCATION.RESERVATION_NAME. In this output: RESERVATION_ADMIN_PROJECT: the name of the Google Cloud project that administers the reservation RESERVATION_LOCATION: the location of the reservation RESERVATION_NAME: the name of the reservation
session_info	RECORD	Details about the session in which this job ran, if any. (Preview)
start_time	TIMESTAMP	The start time of this job, in milliseconds since the epoch. This field represents the time when the job transitions from the PENDING state to either RUNNING or DONE.
state	STRING	Running state of the job. Valid states include PENDING, RUNNING, and DONE.
statement_type	STRING	The type of query statement. For example, DELETE, INSERT, SCRIPT, SELECT, or UPDATE. See QueryStatementType for list of valid values.
timeline	RECORD	Query timeline of the job. Contains snapshots of query execution.
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. Note: This column's values are empty for queries that read from tables with row-level access policies. For more information, see best practices for row-level security in BigQuery.
total_bytes_processed	INTEGER	Total bytes processed by the job. Note: This column's values are empty for queries that read from tables with row-level access policies. For more information, see best practices for row-level security in BigQuery.
total_modified_partitions	INTEGER	The total number of partitions the job modified. This field is populated for LOAD and QUERY jobs.
total_slot_ms	INTEGER	Slot milliseconds for the job over its entire duration in the RUNNING state, including retries.
transaction_id	STRING	ID of the transaction in which this job ran, if any. (Preview)
user_email	STRING	(Clustering column) Email address or service account of the user who ran the job.
query_info.resource_warning	STRING	The warning message that appears if the resource usage during query processing is above the internal threshold of the system. A successful query job can have the resource_warning field populated. With resource_warning, you get additional data points to optimize your queries and to set up monitoring for performance trends of an equivalent set of queries by using query_hashes.
query_info.query_hashes.normalized_literals	STRING	Contains the hashes of the query. normalized_literals is a hexadecimal STRING hash that ignores comments, parameter values, UDFs, and literals. This field appears for successful GoogleSQL queries that are not cache hits.
query_info.performance_insights	RECORD	Performance insights for the job.
query_info.optimization_details	STRUCT	The history-based optimizations for the job.
transferred_bytes	INTEGER	Total bytes transferred for cross-cloud queries, such as BigQuery Omni cross-cloud transfer jobs.
materialized_view_statistics	RECORD	Statistics of materialized views considered in a query job. (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. The following table explains the region scope for this view:

View name	Resource scope	Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.JOBS_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.

Note: When you query INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION to find a summary cost of query jobs, exclude the SCRIPT statement type, otherwise some values might be counted twice. The SCRIPT row includes summary values for all child jobs that were executed as part of this job.

Example

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_BY_ORGANIZATION

Replace the following:

• PROJECT_ID: the ID of the project
• REGION_NAME: the region for your project

For example, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION.

Top 5 jobs that scanned the most bytes today

The following example demonstrates how to find the five jobs that scanned the most bytes in an organization for the current day. You can filter further on statement_type to query for additional information such as loads, exports, and queries.

SELECT
  job_id,
  user_email,
  total_bytes_billed
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE
  EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY
  total_bytes_billed DESC
LIMIT 5;

Note: INFORMATION_SCHEMA view names are case-sensitive.

The result is similar to the following:

+--------------+--------------+---------------------------+
| job_id       |  user_email  |  total_bytes_billed       |
+--------------+--------------+---------------------------+
| bquxjob_1    |  [e-mail] |    999999                 |
| bquxjob_2    |  [e-mail] |    888888                 |
| bquxjob_3    |  [e-mail] |    777777                 |
| bquxjob_4    |  [e-mail] |    666666                 |
| bquxjob_5    |  [e-mail] |    555555                 |
+--------------+--------------+---------------------------+

View performance insights for queries

The following example returns all query jobs that have performance insights from your organization in the last 30 days, along with a URL that links to the query execution graph in the Google Cloud console.

SELECT
  `bigquery-public-data`.persistent_udfs.job_url(
    project_id || ':us.' || job_id) AS job_url,
  query_info.performance_insights
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE
  DATE(creation_time) >= CURRENT_DATE - 30 -- scan 30 days of query history
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND statement_type != 'SCRIPT'
  AND EXISTS ( -- Only include queries which had performance insights
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_standalone_insights
    )
    WHERE slot_contention OR insufficient_shuffle_quota
    UNION ALL
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_change_insights
    )
    WHERE input_data_change.records_read_diff_percentage IS NOT NULL
  );