Recommendations overview

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.

BigQuery works with Active Assist to provide variousrecommendations that you can use to optimize your BigQueryresources.

Recommendations are generated byrecommenders, which usemachine learning (ML) or heuristics to provide recommendations on how tooptimize your BigQuery resource usage.

You can view and manage recommendations across the different recommenders byusing BigQuery in the Google Cloud console—either in theBigQuery Active Assist, or by recommendationnotifications in BigQuery Studio. You can also view recommendationsthrough variousINFORMATION_SCHEMA views at the project and organizationlevel.

To view your BigQuery recommendations along with otherrecommendations across the Google Cloud console, use theActive Assist.

BigQuery recommenders

BigQuery offers the following recommenders:

• Partitioning and clustering recommender,which analyzes your query behavior to find opportunities for partitioning andclustering to optimize your BigQuery tables.
• Materialized view recommender,which finds opportunities to use materialized views to optimize yourworkflows.
• IAM recommender,which analyzes permissions on your BigQuery datasetsand suggests Identity and Access Management (IAM) role updates for principals that haveexcess permissions.

View recommendations

To view your recommendations using the Google Cloud console, do the following:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the navigation menu, clickRecommendations.

   TheRecommendations page opens, showing all recommendations that aregenerated for the current project or organization, depending on theselected scope.

3. To see more information about a specific recommendation or insight,click a recommendation.

View recommendations withINFORMATION_SCHEMA

You can also view your recommendations and insights usingINFORMATION_SCHEMAviews. For example, you can use theINFORMATION_SCHEMA.RECOMMENDATIONS view toview your top three recommendations based on slots savings, as seen in thefollowing example:

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.RECOMMENDATIONSWHEREprimary_impact.category='COST'ANDstate='ACTIVE'ORDERbyslot_hours_saved_monthlyDESCLIMIT3;

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

• INFORMATION_SCHEMA.RECOMMENDATIONS view
• INFORMATION_SCHEMA.RECOMMENDATIONS_BY_ORGANIZATION view
• INFORMATION_SCHEMA.INSIGHTS view

What's next

• Learn how toview partition and cluster recommendations.
• Learn how toapply partition and cluster recommendations.
• Learn how tomanage materialized view recommendations.
• Learn how touse the IAM recommender.