INFORMATION_SCHEMA.INSIGHTS view

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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

TheINFORMATION_SCHEMA.INSIGHTS view contains insights about all BigQueryrecommendations in the current project. BigQuery retrievesinsights for all BigQuery insight types from the Active Assistand present it in this view. BigQuery insights are alwaysassociated with a recommendation.

TheINFORMATION_SCHEMA.INSIGHTS view supports the followingrecommendations:

• Partition and cluster recommendations
• Materialized view recommendations
• Role recommendations for BigQuery datasets

Required permission

To view insights with theINFORMATION_SCHEMA.INSIGHTS view, youmust have the required permissions for the corresponding recommender. TheINFORMATION_SCHEMA.INSIGHTS view only returns insights from recommendationsthat you have permission to view.

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

• Partition & cluster recommender permissions
• Materialized view recommendations permissions
• Role recommendations for datasets permissions

Schema

TheINFORMATION_SCHEMA.INSIGHTS view has the followingschema:

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.

Scope and syntax

Queries against this view must include aregion qualifier. A project IDis optional. If no project ID is specified, the project that the query runsin is used.

• 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 usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

To run the query against a project other than your default project, add theproject ID in the following format:

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.INSIGHTS

• 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 toreturn 3 recommendations for the insights that are ACTIVE in COST category:

WITHinsightsas(SELECT*FROM`region-us`.INFORMATION_SCHEMA.INSIGHTS),recsas(SELECTrecommender,recommendation_id,additional_detailsFROM`region-us`.INFORMATION_SCHEMA.RECOMMENDATIONS)SELECTrecommender,target_resources,LAX_INT64(recs.additional_details.overview.bytesSavedMonthly)/POW(1024,3)asest_gb_saved_monthly,LAX_INT64(recs.additional_details.overview.slotMsSavedMonthly)/(1000*3600)asslot_hours_saved_monthly,insights.additional_details.observation_period_seconds/86400asobservation_period_days,last_updated_timeFROMinsightsJOINrecsONrecommendation_idinUNNEST(associated_recommendation_ids)WHEREstate='ACTIVE'ANDcategory='COST'LIMIT3;

+---------------------------------------------------+---------------------+--------------------+--------------------------+-------------------------+---------------------+|                    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 |+---------------------------------------------------+---------------------+--------------------+--------------------------+-------------------------+---------------------+