INFORMATION_SCHEMA.RECOMMENDATIONS view

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service 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.RECOMMENDATIONS view contains data about all BigQueryrecommendations in the current project. BigQuery retrievesrecommendations for all BigQuery recommenders from the Active Assistand present it in this view.

TheINFORMATION_SCHEMA.RECOMMENDATIONS view supports the followingrecommendations:

TheINFORMATION_SCHEMA.RECOMMENDATIONS view shows only BigQuery-related recommendations.You can view Google Cloud recommendations in the Active Assist.

Required permission

To view recommendations with theINFORMATION_SCHEMA.RECOMMENDATIONS view, youmust have the required permissions for the corresponding recommender. TheINFORMATION_SCHEMA.RECOMMENDATIONS view only returns recommendations that youhave permission to view.

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

Schema

TheINFORMATION_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, seeState.
`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, seeCategory. `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, seePriority.
`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.

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.

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: anydataset region name. For example,`region-us`.
Note: You must use a 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.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 3COST category recommendations on the basisof the projectedslot_hours_saved_monthly:

SELECTrecommender,target_resources,LAX_INT64(additional_details.overview.bytesSavedMonthly)/POW(1024,3)asest_gb_saved_monthly,LAX_INT64(additional_details.overview.slotMsSavedMonthly)/(1000*3600)asslot_hours_saved_monthly,last_updated_timeFROM`region-us`.INFORMATION_SCHEMA.RECOMMENDATIONS_BY_PROJECTWHEREprimary_impact.category='COST'ANDstate='ACTIVE'ORDERbyslot_hours_saved_monthlyDESCLIMIT3;

Note:INFORMATION_SCHEMA view names are case sensitive.

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

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-19 UTC.