Materialized view recommendations and management

View materialized view recommendations

On the Materialized View Recommendations tab, you can view the potential impact on jobs and the details of recommendations for all or specified projects in the current region.

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

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

3. On the Intelligent Materialized Views page, click the Materialized View Recommendations tab.

4. Use the following filter parameters to find materialized view recommendations.

   Parameter	Description
   Project Name	Select a MaxCompute project from the drop-down list.
   Source Table Name	Enter one or more table names, separated by a comma (,). Fuzzy search is supported.
   Analyze Time Interval	Select a date range to filter public subqueries.

You can view the following information for the recommended materialized views:

• Impact metrics:

  Metric	Description
  Estimated number of benefiting jobs per day	The estimated number of daily jobs that will benefit if the recommended materialized views are created.
  Current CU-hour consumption and percentage per day of benefiting jobs	The estimated daily CU-hour consumption of benefiting jobs and its proportion to the daily CU-hour consumption of all jobs after the recommended materialized views are created. A higher percentage indicates a greater impact.
  Current computing resource consumption and percentage per day of benefiting jobs	The estimated proportion of the daily computing resources (amount of scanned data × Complexity) consumed by benefiting jobs relative to the daily computing resources consumed by all jobs after the recommended materialized views are created. A higher percentage indicates a greater impact.
  Estimated computing resources required to create all MVs	This value (amount of scanned data × Complexity) estimates the creation cost. Creating materialized views consumes resources and incurs computing costs if you use the pay-as-you-go billing model.

• List of public subqueries. The following table describes the columns.

  Column	Description
  MV summary	A summary of the public subquery. Aggregation: Indicates whether the recommended public subquery contains an aggregation operation. Inputs: Lists all source table names used by the public subquery. Click a summary to open the Public Subquery Details panel, where you can view the jobs and SQL scripts that repeatedly use the public subquery. The panel contains the following information: Public Subquery No.: A unique ID that is automatically generated by the backend to associate with the materialized view. Public Subquery: The SQL script of the public subquery. Click the icon to expand the SQL script or view it in full screen. Click the icon to copy the SQL script. Click the icon to download the SQL script. Original SQL list: Lists job instances from the last five days that used this subquery, including the following details: Instance ID: The ID of the original job instance that executed this subquery. Submit Project Name: The name of the project where the job instance was run. Submitted By: The user who submitted the job. Execution Time (s): The execution time of the subquery, in seconds. Original Query SQL: You can perform the following actions. View Details: Click to view the SQL details. Download: Click to download the SQL script.
  Project	The project where the job that contains the public subquery was run.
  Recommendation rating	A one- to five-star rating. A higher rating indicates a stronger recommendation to create the materialized view.
  Number of benefiting jobs	The number of jobs that use this public subquery.
  Amount of scanned data	The amount of data scanned by the current public subquery.
  Complexity	The complexity of the current public subquery.
  Computing resources required for MV creation	The computing resources required to create the materialized view. This value is calculated using the following formula: Amount of scanned data × Complexity.
  Analysis time	The time when the recommendation for the current public subquery was generated.
  Included operations	Include Join Operation: The SQL script contains multi-table joins. Aggregate: The SQL script contains aggregation operations. You can filter the list based on these operations.
  Creation status	Indicates whether a materialized view has been created for the public subquery.
  Actions	If the status is Not Created, you can click Create Materialized View to open the creation panel. For a public subquery with a creation status of Created, you can click View Materialized Views to go to the materialized view details page.

Create materialized view

To create a materialized view from a recommendation on the Materialized View Recommendations page, follow these steps:

1. In the Actions column, click Create Materialized View.

2. On the Create Materialized View panel, configure the following parameters.

   Parameter	Description
   Materialized view name	The default name is rec_mv_{$project_name}_{$timestamp}. We recommend that you customize the name based on your requirements. The name must start with a letter or a digit and can contain only letters, digits, and underscores (_).
   Refresh type	The following options are available: Refresh at a Fixed Interval: Updates the materialized view data at a fixed interval in minutes, hours, or days. Corn: Updates the materialized view data based on a standard cron expression.
   Script	The recommended SQL script for creating the materialized view. Click the icon to expand the SQL script or view it in full screen. Click the icon to copy the SQL script. Click the icon to download the SQL script.

3. Click OK to create the materialized view.

Manage materialized views and track benefits

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

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

3. On the Intelligent Materialized Views page, click the MV Management and Benefits tab.

4. Select a MaxCompute project and a benefit statistics interval to view the list of created materialized views, track their benefits, and manage them.

   • Benefit metrics.

     Metric	Description
     Hits	The number of times the materialized view was queried within the benefit statistics interval.
     Saved computing duration	The amount of computing time saved by jobs that used the materialized views in the selected project within the benefit statistics interval.
     Saved CU-hours	The amount of CU-hours saved by jobs that used the materialized views in the selected project within the benefit statistics interval.
     Saved computing resources	The amount of computing resources (amount of scanned data × Complexity) saved by jobs that used the materialized views in the selected project within the benefit statistics interval. This metric includes only jobs that run on pay-as-you-go computing resources.
     Saved computing costs (List price)	The cost savings are calculated by multiplying the saved computing resources by the unit price of pay-as-you-go Standard Edition SQL computing. The unit price is CNY 0.3 per GB.

   • Materialized view list.

     Column	Description
     Materialized view name	The name of the materialized view.
     Hits	The number of times the materialized view was queried within the benefit interval (since its creation or in the last day).
     Saved computing duration	The amount of computing time saved by jobs that used this materialized view within the benefit statistics interval.
     Saved CU-hours	The amount of CU-hours saved by jobs that used this materialized view within the benefit statistics interval.
     Saved computing resources	The amount of computing resources (amount of scanned data × Complexity) saved by jobs that used this materialized view within the benefit statistics interval. This metric includes only jobs that run on pay-as-you-go computing resources.
     Creation time	The time when the materialized view was created.
     Status	The status of the materialized view. Valid values: Created Creating
     Actions	You can perform the following actions on a materialized view: Detail: Click to view the properties, query usage, and performance of the materialized view. The details page includes the following information: Materialized view name. Basic Information. Created At: The time when the materialized view was created. Table Schema Updated At: The time when the table schema of the materialized view was last updated. Storage Size: The storage space occupied by the materialized view. Refresh Interval (Minutes): The frequency at which the materialized view is refreshed. Refreshed At: The time when the materialized view was last refreshed. Code Details: The SQL script that defines the materialized view. Click the icon to expand the SQL script or view it in full screen. Click the icon to copy the SQL script. Click the icon to download the SQL script. Materialized View Call Details: Shows the details of all jobs that queried this materialized view. Instance ID: The ID of the job instance. You can click the ID to go to the Job Insights page for more details. Call MV Execution Time (s): The execution duration of the job when it queried the materialized view, in seconds. Save Time (s): The time this job saved by using the materialized view. Consumed CU-hours: The CU-hours consumed by the job. Saved CU-hours: The CU-hours this job saved by using the materialized view. Computing Load: The computing resources (amount of scanned data × Complexity) consumed by the job. Reduced Computing Load: The computing resources (amount of scanned data × Complexity) this job saved by using the materialized view. Delete Materialized View: Click to delete the materialized view.