Recommendations and management of materialized views - MaxCompute

With continuous use of MaxCompute, users may perform repeated queries on MaxCompute tables by using the same computational logic. To reduce repeated computing, accelerate computing, and improve computing efficiency, MaxCompute provides the intelligent analysis feature on materialized views to help users analyze data and provide recommendations on how to create materialized views. This topic describes how to create a materialized view by using the intelligent analysis feature.

Limits

You can enable the intelligent analysis feature only for a single project. You cannot use the feature to perform cross-project or multi-project analysis and recommendation.
To accurately generate recommendations for creating a materialized view and view the recommendations on the Materialized View Recommendations tab, you must make sure that the following conditions are met after you enable the intelligent analysis feature:
- Cycle tasks run for more than three consecutive days.
- The number of rows of input data in the public subquery that is selected for creating a materialized view is greater than 1,000,000.
- The public subquery includes operators that are related to data reorganization, such as join or aggregation operators.
Materialized views can be used only in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), and China (Chengdu).

Procedure

Enable the intelligent analysis feature

Log on to the MaxCompute console. In the upper-left corner of the console, select a region.
In the left-side navigation pane, choose Workspace > Materialized Views.
On the Materialized Views page, click the Settings tab.
On the Settings tab, turn on Enable Intelligent Analysis.
Click Add Project. In the Create Project dialog box, select a project from the Choose Project drop-down list and click Activate.

Create a materialized view

If you enable the intelligent analysis feature one day before you create a materialized view in your project, you can view the recommended public subqueries and create a materialized view on the Materialized View Recommendations tab.

Log on to the MaxCompute console. In the upper-left corner of the console, select a region.
In the left-side navigation pane, choose Workspace > Materialized Views.
On the Materialized Views page, click the Materialized View Recommendations tab.

On the Materialized View Recommendations tab, configure the parameters and click Search. The following table describes the parameters.

Parameter	Description
Project Name	Select a MaxCompute project from the drop-down list to obtain the public subqueries of the project. Note You must select a project for which the intelligent analysis feature is enabled.
Source Table Name	Enter the name of a source table to obtain the public subqueries that are related to the source table. You can enter multiple table names. Separate the names with commas (,).
Analyze Time Interval	Select the start date and end date to obtain the public subqueries that are performed within the specified date range.
Operations Included	Select an operation type to obtain the public subqueries that include the selected operation type. Valid values: Include Join Operation: An SQL query contains multi-table joins. Aggregate: An SQL query contains aggregate operations.

You can click Reset to reset the parameters.

In the list of public subqueries, find the desired public subquery and click Create Materialized View in the Actions column.

The following table describes the columns in the list of public subqueries.

Column name	Description
Summary	The summary of the public subquery. Aggregate specifies whether the recommended public subquery includes aggregate operations. Inputs provides the names of all source tables that are used by the public subquery. You can click information in the Summary column to go to the Public Subquery Details panel and view information about the tasks in which the public subquery is frequently used and information about the SQL script of the public subquery. The information includes the following items: Public Subquery No.: the unique ID that is automatically generated in the backend. The ID is associated with a materialized view. Public Subquery: the SQL script of the public subquery. In this field, you can click the icon to expand the SQL script or view the SQL script in full screen, click the icon to copy the SQL script, or click the icon to download the SQL script. Original SQL List (Data in the Last 5 Days): displays only the original task instances that use the subquery in the previous five days. Instance ID: the ID of the original task instance that uses the subquery. Submit Project Name: the name of the project in which the task runs. Submitted By: the submitter of the task. Execution Time (s): the duration for which the subquery runs. Unit: seconds. Original Query SQL:: the operations that can be performed. View Details: You can click View Details to view SQL details. Download: You can click Download to download the related SQL script.
Affected SQL Counts	The number of tasks that use the public subquery.
Recommend Rating	The recommendation rating for creating a materialized view. The recommendation rating is represented by one star to five stars. A larger number of stars indicates that creation of a materialized view is more recommended.
Analysis Time	The recommended analysis time of the public subquery.
Creation Status	Specifies whether a materialized view is created for the public subquery.
Number of Materialized Views	The number of materialized views that are created by using the public subquery. You can click the value in the column to go to the Manage Materialized Views tab.
Actions	The operation that you can perform: Create Materialized View. You can click Create Materialized View to go to the Create Materialized View panel.

In the Create Materialized View panel, configure the parameters. The following table describes the parameters.

Parameter	Description
Materialized View Name	The name of the materialized view. The default value is the ID of the public subquery. You can change the name for the materialized view. The name must start with a letter or digit, and can contain only letters, digits, and underscores (_).
Refresh Type	Valid values: Refresh at a Fixed Interval Every: refreshes data in the materialized view at an interval of the specified minutes, hours, or days. Cron: updates data in the materialized view in Cron mode.
Script	The SQL script that is used to create the recommended materialized view. In this field, you can click the icon to expand the SQL script or view the SQL script in full screen, click the icon to copy the SQL script, or click the icon to download the SQL script.

Click OK to create a materialized view.
You are charged storage fees when you create a materialized view. The storage pricing and the price calculation method for a materialized view are the same as the storage pricing and the price calculation method for a common table.

Manage materialized views

Log on to the MaxCompute console. In the upper-left corner of the console, select a region.
In the left-side navigation pane, choose Workspace > Materialized Views.
On the Materialized Views page, click the Manage Materialized Views tab.

On the Manage Materialized Views tab, configure the parameters and click Search. The following table describes the parameters.

Parameter	Description
Project Name	Select a MaxCompute project from the drop-down list to obtain the materialized views of the project. Note You must select a project for which the intelligent analysis feature is enabled.
Materialized View Name	Enter the name of the desired materialized view. You can enter the full name of a materialized view or a keyword in the name of a materialized view for a fuzzy match.
Created At	Select the start date and end date to obtain the materialized views that are created within the specified date range.

You can click Reset to reset the parameters.

In the list of materialized views, you can view information about the materialized views and manage the materialized views.

The following table describes the columns in the list of materialized views.

Column name	Description
Materialized View Name	The name of the materialized view.
Project Name	The name of the project to which the materialized view belongs.
Created At	The date when the materialized view was created.
Total Number of Calls	The total number of times the materialized view is called.
Number of Calls (Last 24 Hours)	The number of times the materialized view was called in the previous 24 hours.
Status	The status of the materialized view. Valid values: Deleted Using
Actions	The operations that can be performed on the materialized view. Valid values: MV Call Details: You can click MV Call Details to view the call details of the materialized view. The Materialized View Call Details panel contains the following items: Materialized View Name: the name of the materialized view. Materialized View Details: the SQL script that defines the materialized view. Use Materialized View Task List: the details of all tasks that call the materialized view. The details include the following items: Instance ID: the ID of the instance that corresponds to the task. Original Execution Date: the date on which the task runs before the task calls the materialized view. Original Execution Time (s): the duration for which the task runs before the task calls the materialized view. Unit: seconds. Call MV Run Date: the date on which the task runs when the task calls the materialized view. Call MV Execution Time (s): the duration for which the task runs after the task calls the materialized view. Unit: seconds. Save Time (s): the execution time that is saved after the task calls the materialized view. Delete: You can click Delete to delete the materialized view.