INFORMATION_SCHEMA.INSIGHTS view

To request feedback or support for this feature, send email to bq-recommendations+feedback@google.com .

The INFORMATION_SCHEMA.INSIGHTS view contains insights about all BigQuery recommendations in the current project. BigQuery retrieves insights for all BigQuery insight types from the Recommendation Hub and present it in this view. BigQuery insights are always associated with a recommendation.

The INFORMATION_SCHEMA.INSIGHTS view supports the following recommendations:

Required permission

To view insights with the INFORMATION_SCHEMA.INSIGHTS view, you must have the required permissions for the corresponding recommender. The INFORMATION_SCHEMA.INSIGHTS view only returns insights from recommendations that you have permission to view.

Ask your administrator to grant access to view insights. To see the required permissions for each recommender, see the following:

Schema

The INFORMATION_SCHEMA.INSIGHTS view has the following schema:

Column name

Data type

Value

insight_id

STRING

Base64 encoded ID that contains the insight type and insight ID

insight_type

STRING

The type of the Insight. For example, google.bigquery.materializedview.Insight .

subtype

STRING

The subtype of the insight.

project_id

STRING

The ID of the project.

project_number

STRING

The number of the project.

description

STRING

The description about the recommendation.

last_updated_time

TIMESTAMP

This field represents the time when the insight was last refreshed.

category

STRING

The optimization category of the impact.

target_resources

STRING

Fully qualified resource names this insight is targeting.

state

STRING

The state of the insight. For a list of possible values, see Value .

severity

STRING

The severity of the Insight. For a list of possible values, see Severity .

associated_recommendation_ids

STRING

Full recommendation names this insight is associated with. Recommendation name is the Base64 encoded representation of recommender type and the recommendations ID.

additional_details

RECORD

Additional details about the insight.

content : Insight content in JSON format.
state_metadata : Metadata about the state of the Insight. Contains key-value pairs.
observation_period_seconds : Observation Period for generating the insight.

Scope and syntax

Queries against this view must include a region qualifier . A project ID is optional. If no project ID is specified, the project that the query runs in is used.

View name	Resource scope	Region scope
[ PROJECT_ID .]`region- REGION `.INFORMATION_SCHEMA.INSIGHTS[_BY_PROJECT]	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 : any dataset region name . For example, `region-us` .

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA 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.INSIGHTS

Replace the following:

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

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

View active insights with cost savings

The following example joins insights view with the recommendations view to return 3 recommendations for the insights that are ACTIVE in COST category:

  WITH 
  
  
 insights 
  
 as 
  
 ( 
 SELECT 
  
 * 
  
 FROM 
  
 `region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 INSIGHTS 
 ), 
  
 recs 
  
 as 
  
 ( 
 SELECT 
  
 recommender 
 , 
  
 recommendation_id 
 , 
  
 additional_details 
  
 FROM 
  
 `region-us` 
 . 
 INFORMATION_SCHEMA 
 . 
 RECOMMENDATIONS 
 ) 
 SELECT 
  
  
 recommender 
 , 
  
 target_resources 
 , 
  
 LAX_INT64 
 ( 
 recs 
 . 
 additional_details 
 . 
 overview 
 . 
 bytesSavedMonthly 
 ) 
  
 / 
  
 POW 
 ( 
 1024 
 , 
  
 3 
 ) 
  
 as 
  
 est_gb_saved_monthly 
 , 
  
 LAX_INT64 
 ( 
 recs 
 . 
 additional_details 
 . 
 overview 
 . 
 slotMsSavedMonthly 
 ) 
  
 / 
  
 ( 
 1000 
  
 * 
  
 3600 
 ) 
  
 as 
  
 slot_hours_saved_monthly 
 , 
  
 insights 
 . 
 additional_details 
 . 
 observation_period_seconds 
  
 / 
  
 86400 
  
 as 
  
 observation_period_days 
 , 
  
 last_updated_time 
 FROM 
  
  
 insights 
  
 JOIN 
  
 recs 
  
 ON 
  
  
 recommendation_id 
  
 in 
  
 UNNEST 
 ( 
 associated_recommendation_ids 
 ) 
  
 WHERE 
  
  
 state 
  
 = 
  
 'ACTIVE' 
  
 AND 
  
 category 
  
 = 
  
 'COST' 
 LIMIT 
  
 3 
 ;

The result is similar to the following:

+---------------------------------------------------+---------------------+--------------------+--------------------------+-------------------------+---------------------+
|                    recommender                    |   target_resource   |  gb_saved_monthly  | slot_hours_saved_monthly | observation_period_days |  last_updated_time  |
+---------------------------------------------------+---------------------+--------------------+--------------------------+-------------------------+---------------------+
| google.bigquery.table.PartitionClusterRecommender | ["table_resource1"] |   3934.07264107652 |       10.499466666666667 |                    30.0 | 2024-07-01 16:41:25 |
| google.bigquery.table.PartitionClusterRecommender | ["table_resource2"] | 4393.7416711859405 |        56.61476777777777 |                    30.0 | 2024-07-01 16:41:25 |
| google.bigquery.materializedview.Recommender      | ["project_resource"]| 140805.38289248943 |        9613.139166666666 |                     2.0 | 2024-07-01 13:00:31 |
+---------------------------------------------------+---------------------+--------------------+--------------------------+-------------------------+---------------------+