MaxCompute:Automatic materialized view (AutoMV)

Introduction

In the field of data warehousing, materialized views are crucial for optimizing repetitive computations. Assuming that in the user's business scenario many query statements share some high-frequency, time-consuming calculations (such as JOIN and AGGREGATE) as common subqueries, you can use materialized views to store the results of these common subquery computations in a materialized view table. When you need these common subqueries, you don't need to re-perform the calculations, because you can simply read them from the table.

MaxCompute's AutoMV feature simplifies the analysis, creation, and maintenance of materialized views.

Upon enabling it, the system will automatically:

1. Continuously Monitor Queries: Monitor executed queries at the project level to identify common subqueries suitable for materialized views.

2. Automatically Create Materialized Views: Periodically generate materialized views based on identified common subqueries.

3. Intelligent Query Rewriting: Automatically rewrite job queries to directly read from the automatically created materialized view tables.

4. Adaptive Storage Resource Management: Efficiently manage storage resources with AutoMV's adaptive management and automatic cleaning mechanisms, preventing significant increases in storage usage.

Limits

• The supported regions are China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), and China (Chengdu).

• AutoMV creation requires that the common subquery includes at least one of the following operators: Project, Filter, JOIN, Aggregate.

• AutoMV does not support cross-project usage. If two MaxCompute projects could use the same materialized view, AutoMV will create two separate materialized views with identical data for each project.

• AutoMV supports only exact matches. To prevent performance degradation from broader queries hitting the materialized views, it will match jobs only with materialized views that have completely equivalent data.

Manage AutoMV switches and set storage resource limits

You can manage AutoMV switches and set storage resource limits through the MaxCompute console or SQL commands. If the manually set limit is exceeded, AutoMV will stop writing data to the materialized views.

View AutoMV benefits and storage occupation

On the AutoMV tab, you can view benefits such as saved computing time, CU hours, volume, and costs, along with storage usage after enabling the feature.

1. Log in to the MaxCompute console and select a region in the upper left corner.

2. In the left-side navigation pane, select Intelligent Optimization > Intelligent Materialized Views.

3. On the Intelligent Materialized Views page, select the AutoMV tab, choose the target MaxCompute project (or all projects if not selected), and the benefit statistics interval to view the benefits, storage occupation, and specific AutoMV list of projects with AutoMV enabled.

   • Indicator Parameters:

     Parameter	Description
     Hits	The number of times AutoMV is called within the benefit statistics interval.
     Saved Computing Duration	The saved computing duration of all jobs hitting the selected project's AutoMV within the benefit statistics interval compared to the computing duration before creating AutoMV.
     Saved CU-hours	The saved CU-hours consumption of all jobs hitting the selected project's AutoMV within the benefit statistics interval compared to the CU-hours consumption before creating AutoMV.
     Reduced Computing Load	The saved computing volume (scan volume × complexity) of all jobs hitting the selected project's AutoMV within the benefit statistics interval compared to the computing volume (scan volume × complexity) before creating AutoMV. Only jobs running on pay-as-you-go computing resources are counted.
     Savings in Computing Costs (Catalog Price)	The saved computing volume of all jobs hitting the selected project's AutoMV multiplied by USD 0.0438/GB (pay-as-you-go Standard Edition SQL computing unit price). Note The SAU (Riyadh - Partner Region) region is operated by a partner, and the unit price is USD 0.05256/GB.
     Occupied Storage Space	The storage size occupied by AutoMV.

   • AutoMV List Parameters:

     Column name	Description
     Materialized View Name	The name of AutoMV.
     Project	The project where AutoMV is located.
     Hits	The number of times AutoMV is called within the benefit interval.
     Saved Computing Duration (s)	The saved computing duration of jobs hitting this AutoMV within the benefit statistics interval compared to the computing duration before creating AutoMV.
     Saved CU-hours	The saved CU hours consumption of jobs hitting this AutoMV within the benefit statistics interval compared to the CU hours consumption before creating AutoMV.
     Reduced Computing Load	The saved computing volume (scan volume × complexity) of jobs hitting this AutoMV within the benefit statistics interval compared to the computing volume (scan volume × complexity) before creating AutoMV. Only jobs running on pay-as-you-go computing resources are counted.
     Occupied Storage Space	The storage size occupied by AutoMV.
     Actions	Click View Details to view the basic information and code details of AutoMV. Basic information Creation time: The creation time of AutoMV. Table structure update time: The table structure update time of AutoMV. Storage size: The storage size occupied by AutoMV. Code details: The SQL script defining AutoMV. You can view, copy, or download the SQL script.

Billing description

After enabling AutoMV for a project, users are only required to pay for the materialized view storage that the system automatically generates, at the standard storage unit cost.

References

• Materialized view operations.

• Query and rewrite for the materialized view.

• Recommendations and management of materialized views.