Materialized views

Step 1: Enable or disable recommendations

1. Go to the Data Asset Governance page.

   Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Governance > Data Asset Governance. On the page that appears, click Go to Data Asset Governance.

2. In the left-side navigation pane, choose Governance > Automation > Materialized View.

3. Select a Workspace and click Settings for Materialized View Recommendation. Turn on the intelligent recommendation switch for the project where you want to enable this feature.

   A dialog box appears, indicating that recommendations apply only to MaxCompute projects that are already bound to the DataWorks workspace.

Step 2: View recommendations

This module automatically identifies potential materialized views and their associated computing tasks. The relevant owners can then evaluate the recommendations and create the materialized view.

1. At the top of the page, select a Workspace and then click the Materialized View Recommendation tab.

2. Adjust the filter criteria, such as Project and Analysis Time Interval, to check for available recommendations.

   If recommendations are available within the specified Analysis Time Interval, you can view the following information.

   Other filter criteria include Data Source Type, Source Table Name, affected task node, Operation Type, recommendation rating, and operation status. The results table also includes columns for Input Data Volume, compute capacity, Analysis Time, and Number of Materialized Views. In the Actions column, you can click Create Materialized View.

   Key parameters:

   • The task identifier in the Materialized View Recommendation column contains a summary of the public subquery. The format is Aggregate: xx; Inputs: xx, xx;, where:

     • Aggregate: Indicates whether the recommended public subquery includes an aggregation operation.

     • Inputs: Lists the names of all source tables used by the public subquery.

   • Number of Possibly Affected SQL Statements: The number of other task jobs that use the public subquery.

   • Number of Affected Nodes: The number of scheduling nodes that use the public subquery.

   • Recommendation Rating: Calculated based on the frequency of the subquery, its complexity, and the number of input records.

3. Click a task identifier in the Materialized View Recommendation column to view details about the subquery, including Source Table Information, the Public Subquery, and related Job and Scheduling Tasks.

   The Source Table Information section includes the table name, table owner, and the node task that produces the table data. The Impact Assessment area is organized into Jobs and Scheduling Tasks tabs. The Jobs tab displays metrics such as Job ID, MaxCompute Project, Workspace, Runtime, CU Consumption, and Compute Capacity. It also provides options to View Code and Execution Details.

Step 3: Create a materialized view

1. Based on your business requirements and the recommendation details, decide whether to create a materialized view on the Materialized View Recommendation page. If so, click Create Materialized View in the Actions column.

2. On the Create Materialized View page, configure the parameters for the materialized view.

   1. Configuration - Create Materialized View: Create a materialized view node and generate a materialized view based on the common subquery of the input tasks.

      Parameter	Description
      Input Table	Automatically detected. No modification is needed.
      Create Materialized View	When you create a materialized view for the first time, this is set to Create Materialized View by default. Note If a materialized view with the same input tables already exists, you can select Select Existing Materialized View as needed.
      Materialized View Name	Enter a custom name.
      lifecycle	Valid values: 1 Day, 7 Days, and Custom Days.
      refresh type	The refresh method for the materialized view. Refresh at Fixed Interval If the source table is an output of a DataWorks task, Time Management Cron Mode is used by default. Otherwise, a fixed interval refresh is used. Time Management Cron Mode The refresh time defaults to the most recent output time of the upstream source table's generation node. Do Not Refresh
      Preview Statement	Preview the SQL statement for the materialized view.

   2. (Optional) Configuration - Create Materialized View Refresh Node: Create a node that can dynamically refresh the materialized view. When the source table task generates new output, the refresh is triggered to update the materialized view with the latest data.

      Configure the Materialized View Refresh Node Name and Node Running Timeout Period. The configuration panel includes the following fields: Input Table, which displays the associated input tables and their output node IDs; Materialized View Refresh Node Name (required), which supports automatically appending a timestamp suffix, for example, dgc_mv_1716775015179; and Node Running Timeout Period (required), which defaults to 60 minutes.

   3. (Optional) Configuration - Add Materialized View Refresh Node as Ancestor Node: For nodes with similar computations, add the refresh node as an upstream scheduling dependency to improve the query hit rate for incremental data. In the Add Materialized View Task Dependency area:

      This area shows the number of affected task nodes in the current workspace and other workspaces. You can click View Details to see specific task node information.

      The task node details dialog box includes fields such as Task ID, Task Name, Owner, Affected Baselines/Highest Level, Task Type, Schedule Type, and Last Modified Time.

3. Click Create and Execute. DataWorks starts to create the materialized view. You can monitor the creation status in the Status column on the Materialized View Recommendation page or click View Details in the Actions column to see detailed progress.

   • The Status can be one of the following:

     • To Be Created: The creation of the materialized view has not been initiated through DataWorks.

     • Created: The materialized view was created successfully.

     • Creating: The creation process has been initiated in DataWorks but is not yet complete.

     • Create Failed: The creation process was initiated in DataWorks but failed.

   • Click View Details to display the creation details.

     On the creation details page, the Plan Information section shows the workspace, creator, status, and creation time. The Process Details section lists five steps: Start Creation, Create Materialized View, Create Materialized View Refresh Node, Add Materialized View Task Dependency, and Complete Creation. Each step shows Execution Successful, and the overall status is Completed.

If you configure the Create Materialized View Refresh Node and Add Materialized View Refresh Node as Ancestor Node options, a new materialized view refresh node is added before the nodes with similar computations in the corresponding task workflow. The owner of this new node is the user who created the materialized view.

Step 4: Manage views

You can manage the materialized views created from recommendations within your workspace. This includes checking their hit rates and analyzing or deleting any views that do not perform as expected.

1. At the top of the page, select a Workspace and then click the Materialized View Management tab.

2. Adjust the filter criteria, such as Project, to view the materialized views that were created from recommendations.

   Other filter criteria include Data Source Type, Materialized View Name, Creation Time, and owner. The table also displays metrics such as Calls in Previous Day, Total Time Saved, and total saved CU-hours. A Batch Delete operation is also supported.

   Important

   If your MaxCompute project quota is based on the pay-as-you-go billing method, the following formula applies: Total saved compute capacity = Input data volume × SQL complexity. Fees for MaxCompute SQL jobs are calculated by using the formula: compute capacity × Unit price. For more information, see MaxCompute computing fees (pay-as-you-go).

   • Click a materialized view identifier in the Materialized View column to view its details.

     The materialized view details page displays the following information: materialized view name (for example, dgc_mv_1716775189582), data source type, table description, owner, number of reads, baseline task output, table type, and number of direct downstream nodes. The Basic Information section includes creation time, last update time, lifecycle, logical size, number of data rows, partition information, and output task. The Materialized View Properties section includes a link to view code, last refresh time, default refresh frequency, and associated refresh node. The Materialized View Tasks section provides statistics such as hit count, runtime, saved runtime, compute CUs, saved CUs, and compute capacity, which can be viewed by grouping dimension and time range.

   • You can click Delete in the Actions column to delete a materialized view.