Skip to content

Latest commit

 

History

History

system_tables

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
 
 
 
 
 
 
 
 

BigQuery System Tables Reports

This view illustrates how users can leverage BigQuery's INFORMATION_SCHEMA metadata tables to understand their organization's slot and reservation utilization, job execution, and job errors. Users can use this dashboard and its underlying queries as-is, or use them as a starting point for more complex queries and/or visualizations.

The dashboard is comprised of the following reports:

  1. Daily Utilization Report
  2. Hourly Utilization Report
  3. Reservation Utilization Report
  4. Job Execution Report
  5. Job Error Report
  6. Job Comparison Report

The above links will direct you to documentation for each individual report which will describe its contents in more detail.

The underlying SQL queries for each report can be found here. These queries reference tables in a sample public dataset that was generated from real INFORMATION_SCHEMA usage data but was slightly modified to anonymize values and clean some data.

The following steps describe how to make a copy of this dashboard and the underlying data sources for use as-is.

Prerequisites

In order to create the dashboard and query the INFORMATION_SCHEMA tables a user must have access to the following INFORMATION_SCHEMA tables:

  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
  • INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
  • INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
  • INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
  • INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT

Detailed information about IAM permissions for each table can be found here and here. Note that because this dashboard uses "owner" data credentials, only the owners of the dashboard require access to the underlying tables. More information about data credentials in Data Studio can be found here.

1. Data sources

1.1 Copy the data sources

Log in to Data Studio and create a copy of the following data sources. More information on copying data sources can be found here.

  1. Daily Utilization
  2. Commitments Timeline
  3. Hourly Utilization
  4. Current Assignments
  5. Reservation Utilization 7 Days
  6. Reservation Utilization 30 Days
  7. Job Usage
  8. Job Errors
  9. Job Comparison
  10. Job Concurrency Slow
  11. Job Concurrency Fast
  12. Job Analyzer - Slow Job
  13. Job Analyzer - Fast Job

Please note that for the Job Comparison Report, you will need to make a copy of the Job Concurrency Slow and Job Analyzer Slow queries for the corresponding fast jobs data source. You will need to edit @job_param parameter on lines line 44 and line 153 respectively to @job_param_2 or similar. Examples of how to do this are shown in the sample data sources above.

1.2 Set the billing project

Once a copy is made, Data Studio will display the details for the data source. For each data source, enter the project id of the Billing Project. It is recommended to use the administration project where the capacity commitments were purchased, however a different billing project can be used.

1.3 Modify the data sources

Update the data source to reference your project's INFORMATION_SCHEMA tables as follows:

`region-{region_name}`.INFORMATION_SCHEMA.{table}

where {region_name} is the name of the region or multi-region where your commitments and reservations are located.
 

If you are using a billing project that is different from the administration project, update the data source as follows:

`{project_id}`.`region-{region_name}`.INFORMATION_SCHEMA.{table}

where {project_id} is the project id of the billing project and {region_name} is the name of the region or multi-region where your commitments and reservations are located.
 

When copying the Reservation Utilization data sources, you must also do the following:

  1. Replace all instances "admin-project:US." with "{project_id}:{location}.", where {project_id} is the project id of your administration project and {location} is the GCP region or multi-region where they are located.
  2. Replace all instances of TIMESTAMP("2020-07-15 23:59:59.000 UTC") with CURRENT_TIMESTAMP().

Once all modifications are complete and a Billing Project is specified, click "Reconnect".

2. Dashboard

2.1 Copy the dashboard

Create a copy of the public dashboard. You will be asked to choose a new data source for each data source in the report. Select the appropriate data sources from the ones you copied in step 1. Click on create report and rename it as desired.

2.2 Modify the date pickers

Once the report is copied and all of the data is rendered, modify any date pickers in the report pages to use the time period you desire (ex: last week, last 14 days, last 28 days, etc).