Clustering optimization recommendations-MaxCompute(MaxCompute)-阿里云帮助中心

MaxCompute analyzes the recent read and write patterns of your tables to generate clustering optimization recommendations. These recommendations can help improve job performance and reduce CU consumption. Use the estimated benefits and detailed suggestions to decide whether to apply a recommendation.

Limitations

Supported regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), and China (Chengdu).
Clustering optimization recommendations are not supported for three-tier model projects.
The clustering optimization analysis covers the run history of most jobs but excludes those with multiple Fuxi Jobs, so a small number of jobs may not be analyzed.
To learn about optimizing jobs that use clustered tables, see Hash Clustering.

View clustering optimization recommendations

You can view recommended tables, their estimated benefits after clustering, and detailed suggestions for all or specific projects in the current region. Follow these steps:

Log in to the MaxCompute console and select a region in the upper-left corner.
In the left-side navigation pane, choose Intelligent Optimization > Data Layout Optimization.

On the Clustering Optimization tab, click Estimated Benefits and use the filters to check for tables with clustering recommendations.

Parameter	Description
Project Name	Select a MaxCompute project from the drop-down list. If you do not select a project, it defaults to All Projects.
Table Name	Enter a table name. Fuzzy search is supported. Separate multiple table names with a comma (`,`).
Recommendation Generation Date	The date the recommendation was generated. The previous day is selected by default.

Estimated benefit metrics

Metric

Description

Estimated Number of Beneficial Jobs/Day

The estimated number of daily jobs that would benefit if the table is converted to a clustered table.

Estimated Shuffle Reduction/Day

The estimated daily reduction in shuffle volume after converting the recommended table into a clustered table.

Reducing shuffle volume effectively decreases the CU-hours consumed by jobs. Typically, a 1 TB reduction in shuffle volume saves 2 to 4 CU-hours daily.

Optimization recommendation list

Review the suggested values for the parameters in the list and view the recommendation for more details about table optimization.

Column

Description

Project

The project that contains the recommended table.

Table Name

The name of the recommended table.

Type

The recommended clustering type for the table. Currently, only Hash Clustering recommendations are supported.

Recommended Clustering Key

The recommended cluster key for the table. This key primarily affects Shuffle Removal and data filtering for point lookups.

Recommended Sort Key

The recommended sort key for the table. This key primarily affects data filtering and storage compression.

Bucket Count

The recommended bucket count for the table. This count primarily affects the parallelism of table write operations and of table read jobs that use Shuffle Removal.

Recommendation Index

A 1- to 5-star rating. More stars indicate a stronger recommendation to modify the table's clustering attributes. The star rating is calculated as follows:

Evaluation dimension	Deduction rule	Penalty
Timeliness of the optimizable pattern	Observation window is less than 14 days	Deduct 1 star
Shuffle volume reduction on read	Reduction is less than 1 TB	Deduct 1 star
Write job activity	No write jobs were recorded for the day	Deduct 1 star
Number of optimizable partitions	Deduct 1 star if the number of optimizable partitions is greater than 3 Deduct 2 stars if the number of optimizable partitions is greater than 31	Dynamic adjustment

Note

If no write jobs are recorded for the day, a star is deducted because the increased cost of daily writes cannot be estimated.
If there are many optimizable partitions, clustering optimization takes effect only after many existing partitions are rewritten with new data or actively rewritten. This delay results in a star deduction.

Estimated Shuffle Reduction/Day

The estimated daily reduction in shuffle volume after converting the table to a clustered table.

Observation Interval

The number of days the same optimization recommendation has appeared during the observation period.

Actions

Click a recommendation to open the Details of Table with Optimization Recommendations page. The page includes the following sections:

Optimization Recommendations
Current Status
Overview of Estimated Benefits
- Estimated Shuffle Reduction/Day
- Beneficial Table Read Jobs
- Full Table Write Jobs
- Full Table Read Jobs

Apply clustering optimization recommendations

Apply recommendations to the original table

Use the console
For a partitioned table, you can directly apply a recommendation to convert the original table into a clustered table with a single click. Follow these steps:
1. In the left-side navigation pane, choose Intelligent Optimization > Data Layout Optimization.
2. On the Clustering Optimization tab, click Estimated Benefits.
3. In the Actions column for the target table, click View Details to go to the Details of Table with Optimization Recommendations page.
4. In the upper-right corner, click Apply Recommendations to complete the conversion.

Use a SQL command:

-- Change a table to a Hash Clustering table.
ALTER TABLE <table_name> [CLUSTERED BY (<col_name> [, <col_name>, ...])
                       [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
                       INTO <number_of_buckets> BUCKETS];

After the conversion, inspect the optimized clustered table and run its associated read jobs to ensure they all operate as expected. If any issues arise, perform a rollback immediately by running the following command:
```
-- Change a Hash Clustering table back to a non-clustered table.
ALTER TABLE <table_name> NOT CLUSTERED;
```

Important

After you convert a table to a clustered table, you cannot perform incremental writes, such as INSERT INTO or data uploads through Tunnel.
You cannot apply clustering recommendations directly to a non-partitioned table.
After clustering attributes are modified, the latency and CU consumption of write jobs increase. The CU consumption of read jobs decreases, resulting in overall net CU savings.
In some scenarios, we recommend that you rewrite partitions and verify the benefits for downstream optimizable jobs. For more information, see Rewrite partition data (Recommended).

Rewrite partition data (Recommended)

When you modify the clustering attributes of a table, the changes apply only to new partitions. To apply optimizations to existing partitions, you must rewrite their data. In the following scenarios, you need to rewrite partitions and verify the benefits for downstream optimizable jobs:

The table is a dependency for high-priority, latency-sensitive jobs.
The table has large partitions where single write operations exceed 10 TB.
A downstream job reads from multiple partitions, which must be simultaneously rewritten as clustered partitions to apply the optimization.

For large tables in a daily full-and-incremental merge job, rewrite the last day's partition. This practice mitigates the risk of increased costs and slower run times for the next day's initial job run.

-- Assume the partition column is ds, and new partitions such as 20241015 and 20241016 are added daily.
-- The data in new partitions is generated by merging incremental data from the previous day's partition.
INSERT OVERWRITE TABLE <table_name> PARTITION(ds) 
SELECT * FROM <table_name> WHERE ds = max_pt('<table_name>');

If an optimizable job reads from multiple existing partitions, we recommend rewriting the existing partitions within the read range to apply the clustering optimization sooner.

-- Assume the partition column is ds, and new partitions such as 20241015, 20241016, etc., are added daily.
-- The read range starts from 20241015.
INSERT OVERWRITE TABLE <table_name> PARTITION(ds) 
SELECT * FROM <table_name> WHERE ds >='20241015';

After rewriting, we recommend running a trial of the optimizable job to verify that the optimization is effective.

Apply recommendations to a new table

For a non-partitioned table, you cannot directly modify the clustering attributes of the original table. You must manually apply the recommendation by creating a new clustered table. Follow these steps:

Create a new clustered table.

-- Check the CREATE TABLE statement of the existing table.
SHOW CREATE TABLE <original_table>;
-- Create a new cluster table by inserting the CLUSTER information at the appropriate position in the CREATE TABLE syntax.
CREATE TABLE <new_table> [CLUSTERED BY (<col_name> [, <col_name>, ...])
                          [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
                          INTO <number_of_buckets> BUCKETS];

Load data into the new table.

INSERT OVERWRITE TABLE <new_table>
SELECT * FROM <original_table>;

Rename the original table to a backup table.

ALTER TABLE <original_table> RENAME TO <original_table_backup>;

Rename the new table to the original table name.

ALTER TABLE <new_table> RENAME TO <original_table>;

The new table, now renamed to the original table's name, clusters data according to the new clustering attributes.

Roll back clustering recommendations

Inspect the new clustered table to ensure all jobs operate as expected. If any issues arise, perform a rollback immediately by running the following commands:

Delete the new table, which has the same name as the original table.
```
DROP TABLE IF EXISTS <original_table>;
```

Rename the backup table to the original table name.

ALTER TABLE <original_table_backup> RENAME TO <original_table>;

View the benefits of clustering optimization

On the Clustering Optimization tab, click Actual Benefits to view the benefits of clustering optimization. Follow these steps:

Log in to the MaxCompute console and select a region in the upper-left corner.
In the left-side navigation pane, choose Intelligent Optimization > Data Layout Optimization.
On the Clustering Optimization tab, click Actual Benefits.

Filter by Project Name and Analysis Time to view a summary and details of the benefits gained from clustered tables with modified clustering attributes.

Benefit metric descriptions

Metric	Description
Number of Benefited Jobs	The number of times the recently modified clustered table was read during the analysis period.
Saved CU-hours	The CU-hour savings for all jobs that read the modified clustered table during the analysis period, compared to consumption before the modification.
Reduced Shuffle Amount	The reduction in shuffle volume for all jobs that read the recently modified clustered table during the analysis period, compared to the shuffle volume before the table was modified.

The benefits of clustering optimization are calculated by comparing the average consumption of jobs that have the same Signature before and after the modification. The statistics cover clustered tables modified based on recommendations within the last 365 days.

Optimized table list

Column	Description
Project	The project that contains the clustered table with modified clustering attributes.
Table Name	The name of the table with modified clustering attributes.
Clustering Attribute Modification Time	The date when the table's clustering attributes were last modified.
Number of Benefited Jobs	The number of times the table was read during the analysis period after its clustering attributes were modified.
Saved Computing Duration	The reduction in computing duration for jobs that read this table during the analysis period, compared to the duration before the attributes were modified.
Saved CU-hours	The reduction in CU-hour consumption for jobs that read this table during the analysis period, compared to the consumption before the attributes were modified.
Reduced Shuffle Amount	The reduction in shuffle volume for jobs that read this table during the analysis period, compared to the volume before the attributes were modified.
Actions	Click a recommendation to open the Details of Optimized Table page. The page includes the following sections: Current Status of Table Clustering Type Cluster key Sort key Number of Buckets Partition Key Benefit Overview Number of Benefited Jobs Saved CU-hours Reduced Shuffle Amount List of benefited read jobs Signature Saved Computing Duration Saved CU-hours Reduced Shuffle Amount

Important

After you convert a table to a clustered table, daily job benefit statistics are updated on a T+1 basis. For real-time optimization results, use job O&M tools or LogView.
Clustering optimization benefit statistics are based on historical run data of jobs with the same Signature and are influenced by factors such as daily job performance fluctuations. If you find that the benefits do not meet expectations, compare the job execution details from different dates to identify the influencing factors.
The clustering optimization benefit statistics are for reference only. For the final CU savings, refer to your bill.