Console
Start free

Manage materialized view recommendations

This document describes how the materialized view recommender works, and also shows you how to view and apply any materialized view recommendations.

Introduction

The BigQuery materialized view recommender can help you improve workload performance and save workload execution cost. These recommendations are based on historical query execution characteristics from the past 30 days.

Materialized views are precomputed views that periodically cache the results of a query for increased performance and efficiency. Materialized views use smart tuning to transparently rewrite queries against source tables to use existing materialized views for better performance and efficiency.

How the recommender works

The recommender generates recommendations daily for each project that executes query jobs in BigQuery. Recommendations are based on the analysis of the workload execution over the past 30 days. The materialized view recommender looks for repetitive query patterns and computes any savings that could be made if the repetitive subquery could be moved to an incremental materialized view. The recommender takes into account any savings at query time and account maintenance cost for the materialized view. If these combined factors show a significant positive outcome, then the recommender makes a recommendation.

Consider the following query example:

WITH revenue   AS
(SELECT l_suppkey as supplier_no,
        sum(l_extendedprice * (1 - l_discount)) as total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= date '1996-01-01'
    AND l_shipdate < date_add(date '1996-01-01', interval 3 MONTH)
  GROUP BY l_suppkey)
SELECT s_suppkey,
      s_name,
      s_address,
      s_phone,
      total_revenue
FROM
supplier,
revenue
WHERE s_suppkey = supplier_no
AND total_revenue =
  (SELECT max(total_revenue)
    FROM revenue)
ORDER BY s_suppkey

This query example shows information about the top supplier. The query contains a common table expression (CTE) named revenue which represents the total revenue per every supplier (l_suppkey). revenue is joined with the supplier table on the condition that the supplier's total_revenue matches max(total_revenue) across all suppliers. As a result, the query computes information (l_suppkey, s_name, s_address, s_phone, total_revenue) about the supplier with the maximum total revenue.

The whole query itself is too complicated to be put into an incremental materialized view. However, the supplier CTE is an aggregation over a single table — a query pattern which is supported by incremental materialized views. The supplier CTE is also the most computationally expensive part of the query. Therefore, if the example query was run repeatedly over constantly changing source tables, then the materialized view recommender might suggest putting the supplier CTE into a materialized view. The materialized view recommendation for the preceding sample query might look similar to the following:

CREATE MATERIALIZED VIEW mv AS
SELECT l_suppkey as supplier_no,
         sum(l_extendedprice * (1 - l_discount)) as total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= date '1996-01-01'
    AND l_shipdate < date_add(date '1996-01-01', interval 3 MONTH)
  GROUP BY l_suppkey

The Recommender API also returns query execution information in the form of insights. Insights are findings that help you understand your project's workload, providing more context on how a materialized view recommendation might improve workload costs.

Limitations

Before you begin

Before you can view or apply materialized view recommendations, you need to enable the Recommender API.

Required permissions

To get the permissions that you need to access materialized view recommendations, ask your administrator to grant you the BigQuery Materialized View Recommender Viewer (roles/recommender.bigqueryMaterializedViewViewer) IAM role. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to access materialized view recommendations. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to access materialized view recommendations:

You might also be able to get these permissions with custom roles or other predefined roles.

For more information about IAM roles and permissions in BigQuery, see Introduction to IAM.

View materialized view recommendations

This section describes how to view materialized view recommendations and insights using the Google Cloud console, the Google Cloud CLI or the Recommender API.

Select one of the following options:

Console

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

    Go to BigQuery

  2. In the navigation menu, click Recommendations.

  3. The BigQuery Recommendations pane opens. Under Optimize BigQuery workload cost, click View details.

    View details to view all BigQuery recommendations

  4. A recommendation list appears, showing all recommendations generated for the current project. To see more information about a specific materialized view recommendation or table insight, click Details.

Alternatively, you can view all recommendations available for your project or organization by clicking Recommendations in the navigation menu.

gcloud

To view materialized view recommendations for a specific project, use the gcloud recommender recommendations list command:

gcloud recommender recommendations list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --recommender=google.bigquery.materializedview.Recommender \
    --format=FORMAT_TYPE \

Replace the following:

The following table describes the important fields from the `recommendations` response:

Property Relevant for subtype Description
recommenderSubtype CREATE_MATERIALIZED_VIEW The type of recommendation.
content.overview.sql CREATE_MATERIALIZED_VIEW Suggested DDL statement that creates a materialized view.
content.overview.slotMsSavedMonthly CREATE_MATERIALIZED_VIEW Estimated slot milliseconds to be saved monthly by suggested view.
content.overview.bytesSavedMonthly CREATE_MATERIALIZED_VIEW Estimated bytes scanned to be saved monthly by suggested view.
content.overview.baseTables CREATE_MATERIALIZED_VIEW Reserved for future use.

To view insights that prompted materialized view recommendations using the gcloud CLI, use the gcloud recommender insights list command:

gcloud recommender insights list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --insight-type=google.bigquery.materializedview.Insight \
    --format=FORMAT_TYPE \

Replace the following:

The following table describes the important fields from the insights API response:
Property Relevant for subtype Description
content.queryCount CREATE_MATERIALIZED_VIEW Number of queries in the observation period with repetitive pattern that can be optimized using materialized view.

REST API

To view materialized view recommendations for a specific project, use the REST API. With each command, you must provide an authentication token, which you can get using the gcloud CLI. For more information about getting an authentication token, see Methods for getting an ID token.

You can use the curl list request to view all recommendations for a specific project:

$ curl
-H "Authorization: Bearer $(gcloud auth print-access-token)"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/PROJECT_NAME/locations/LOCATION/recommenders/google.bigquery.materializedview.Recommender/recommendations

Replace the following:

The following table describes the important fields from the `recommendations` response:

Property Relevant for subtype Description
recommenderSubtype CREATE_MATERIALIZED_VIEW The type of recommendation.
content.overview.sql CREATE_MATERIALIZED_VIEW Suggested DDL statement that creates a materialized view.
content.overview.slotMsSavedMonthly CREATE_MATERIALIZED_VIEW Estimated slot milliseconds to be saved monthly by suggested view.
content.overview.bytesSavedMonthly CREATE_MATERIALIZED_VIEW Estimated bytes scanned to be saved monthly by suggested view.
content.overview.baseTables CREATE_MATERIALIZED_VIEW Reserved for future use.

To view insights that prompted materialized view recommendations using REST API run the following command:

$ curl
-H "Authorization: Bearer $(gcloud auth print-access-token)"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/PROJECT_NAME/locations/LOCATION/insightTypes/google.bigquery.materializedview.Insight/insights

Replace the following:

The following table describes the important fields from the insights API response:

Property Relevant for subtype Description
content.queryCount CREATE_MATERIALIZED_VIEW Number of queries in the observation period with repetitive pattern that can be optimized using materialized view.

View recommendations with INFORMATION_SCHEMA

You can also view your recommendations and insights using INFORMATION_SCHEMA views. For example, you can use the INFORMATION_SCHEMA.RECOMMENDATIONS view to view your top three recommendations based on slots savings, as seen in the following example:

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

For more information, see the following resources:

Apply materialized view recommendations

You can apply a recommendation to create a materialized view by executing the suggested CREATE MATERIALIZED VIEW type DDL statement in the Google Cloud console.

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

    Go to BigQuery

  2. In the navigation menu, click Recommendations.

  3. The BigQuery Recommendations pane opens. Under Optimize BigQuery workload cost, click View details.

    View details to view all BigQuery recommendations

  4. A recommendation list appears, showing all recommendations generated for the current project or organization, depending on the selected scope. Locate a materialized view recommendation and click Details.

  5. Click View in BigQuery Studio. A SQL editor opens containing a CREATE MATERIALIZED VIEW DDL statement.

  6. In the provided CREATE MATERIALIZED VIEW statement, modify the MATERIALIZED_VIEW placeholder with a unique materialized view name.

  7. Run the CREATE MATERIALIZED VIEW DDL statement to create a recommended materialized view.

Troubleshoot recommendation issues

Issue: No recommendations appear for a specific table.

Materialized view recommendations might not appear under the following circumstances:

Pricing

There is no cost or adverse impact on workload performance when you view recommendations.

When you apply recommendations by creating materialized views, you can incur storage, maintenance, and querying costs. For more information, see Materialized Views Pricing.