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 to projects, folders, and organizations.

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:

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:

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

• 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-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE
  EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY
  total_bytes_billed DESC
LIMIT 5;

The result is similar to the following:

+--------------+--------------+---------------------------+
| job_id       |  user_email  |  total_bytes_billed       |
+--------------+--------------+---------------------------+
| bquxjob_1    |  abc@xyz.com |    999999                 |
| bquxjob_2    |  def@xyz.com |    888888                 |
| bquxjob_3    |  ghi@xyz.com |    777777                 |
| bquxjob_4    |  jkl@xyz.com |    666666                 |
| bquxjob_5    |  mno@xyz.com |    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-REGION_NAME`.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
  );