INFORMATION_SCHEMA.RECOMMENDATIONS view

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

The INFORMATION_SCHEMA.RECOMMENDATIONS view contains data about all BigQuery recommendations in the current project. BigQuery retrieves recommendations for all BigQuery recommenders from the Recommendation Hub and present it in this view.

The INFORMATION_SCHEMA.RECOMMENDATIONS view supports the following recommendations:

The INFORMATION_SCHEMA.RECOMMENDATIONS view shows only BigQuery-related recommendations. You can view Google Cloud recommendations in the Recommendation Hub.

Required permission

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

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

Schema

The INFORMATION_SCHEMA.RECOMMENDATIONS view has the following schema:

Column name Data type Value
recommendation_id STRING Base64 encoded ID that contains the RecommendationID and recommender.
recommender STRING The type of recommendation. For example, google.bigquery.table.PartitionClusterRecommender for partitioning and clustering recommendations.
subtype STRING The subtype of the recommendation.
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 recommendation was last created.
target_resources STRING Fully qualified resource names this recommendation is targeting.
state STRING The state of the recommendation. For a list of possible values, see State.
primary_impact RECORD The impact this recommendation can have when trying to optimize the primary category. Contains the following fields:
  • category: The category this recommendation is trying to optimize. For a list of possible values, see Category.
  • cost_projection: This value may be populated if the recommendation can project the cost savings from this recommendation. Only present when the category is COST.
  • security_projection: Might be present when the category is SECURITY.
priority STRING The priority of the recommendation. For a list of possible values, see Priority.
associated_insight_ids STRING Full Insight names associated with the recommendation.Insight name is the Base64 encoded representation of Insight type name & the Insight ID. This can be used to query Insights view.
additional_details RECORD Additional Details about the recommendation.
  • overview: Overview of the recommendation in JSON format. The content of this field might change based on the recommender.
  • state_metadata: Metadata about the state of the recommendation in key-value pairs.
  • operations: List of operations the user can perform on the target resources. This contains the following fields:
    • action: The type of action the user must perform. This can be a free-text set by the system while generating the recommendation. Will always be populated.
    • resource_type: The cloud resource type.
    • resource: Fully qualified resource name.
    • path: Path of the target field relative to the resource.
    • value: Value of the path field.

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.RECOMMENDATIONS[_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.

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.RECOMMENDATIONS
 Replace the following:

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

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

View top cost saving recommendations

The following example returns top 3 COST category recommendations on the basis of the projected slot_hours_saved_monthly:

SELECT 
   recommender,
   target_resources,
   LAX_INT64(additional_details.overview.bytesSavedMonthly) / POW(1024, 3) as est_gb_saved_monthly,
    LAX_INT64(additional_details.overview.slotMsSavedMonthly) / (1000 * 3600) as slot_hours_saved_monthly,
  last_updated_time
FROM 
  `region-us`.
   INFORMATION_SCHEMA.RECOMMENDATIONS_BY_PROJECT
WHERE 
   primary_impact.category = 'COST'
AND
   state = 'ACTIVE'
ORDER by 
   slot_hours_saved_monthly DESC 
LIMIT 3;

The result is similar to the following:

+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
|                    recommender                    |   target_resources      | est_gb_saved_monthly | slot_hours_saved_monthly |  last_updated_time
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
| google.bigquery.materializedview.Recommender      | ["project_resource"]    | 140805.38289248943   |        9613.139166666666 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_1"]    | 4393.7416711859405   |        56.61476777777777 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_2"]    |   3934.07264107652   |       10.499466666666667 |  2024-07-01 13:00:00
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+