Append delta table - Hash cluster (Invitation only)
MaxCompute enhances the Append Delta Table format with Hash Cluster support, improving query performance while enabling incremental data processing. This article covers the differences from other table types, syntax details, and usage examples for SQL and the Data Tunnel SDK.
Use cases
Hash Cluster is recommended for the following scenarios:
Equality filter queries: Use for point lookups or equality filters on specific columns to reduce data scanning.
Equi-joins and GROUP BY operations: Reduce data shuffling when joining or aggregating multiple tables on the same key.
Comparison with similar table types
Table type | Clustering method | Incremental write (ACID) | |
Hash | Not supported | Provides Hash Clustering optimization (Shuffle + Sort) but does not support ACID. | |
Hash | Supported | Offers ACID capabilities and Hash Clustering optimization. Suitable for data with a primary key. Lower read and write performance compared to tables without a primary key. | |
Range | Supported | Offers ACID capabilities and supports recluster, but its Range clustering method results in lower write performance than Hash. | |
Append Delta Table - Hash Cluster | Hash | Supported | Combines Hash Clustering optimization (Shuffle + Sort) with full ACID capabilities. It also supports both incremental and full recluster in the background, making it the most feature-complete option. |
Prerequisites
Before creating a table, enable the following session parameters:
SET odps.table.append2.enable=true;
SET odps.table.hash.delta.enable=true; -- Enables the trial feature for creating hash delta tables.Syntax
CREATE TABLE [IF NOT EXISTS] <table_name>
[(<col_name> <data_type> [comment <col_comment>], ...)]
[PARTITIONED BY (<col_name> <data_type> [comment <col_comment>], ...)]
CLUSTERED BY (<col_name> [, <col_name>, ...])
[SORTED BY (<col_name> [, <col_name>, ...])] -- Only ascending order is supported.
INTO <number_of_buckets> BUCKETS
TBLPROPERTIES ('table.format.version' = '2'); Parameters
Parameter | Description |
| Specifies the bucketing columns. Choose columns that are frequently used in equality filter queries, equi-joins, GROUP BY, or WINDOW PARTITION BY clauses. For best results, select columns with high cardinality to ensure an even data distribution across buckets. |
| Optional. Specifies the sort columns within each bucket. Only ascending order is currently supported. We recommend choosing columns used for range or equality filters, window calculations, or versioning timestamps. |
| Specifies the number of logical buckets. We recommend setting this number based on your data volume, query concurrency, and the cardinality of the bucketing columns. The number of buckets affects the parallelism of write operations and shuffle optimizations during read operations. |
| Set to |
SQL examples
This example uses a product status and price version table. In business logic, point lookups or joins are typically performed on products based on item_id. Therefore, item_id is designated as the hash bucketing column. The version effective time, event_time, is used to track historical changes, so event_time is designated as the sort column. This design is suitable for scenarios such as Slowly Changing Dimension (SCD) tables, product price version tables, and status change detail tables.
Preparation
SET odps.sql.type.system.odps2=true;
SET odps.table.append2.enable=true;
SET odps.table.hash.delta.enable=true;Create a table
Non-partitioned table
CREATE TABLE hash_delta_sales_demo (
item_id BIGINT,
event_time TIMESTAMP,
price DOUBLE,
status STRING
)
CLUSTERED BY (item_id)
SORTED BY (event_time)
INTO 256 BUCKETS
TBLPROPERTIES ('table.format.version' = '2');Partitioned table
If you need to manage data by date, you can also create a partitioned table:
CREATE TABLE hash_delta_sales_demo_pt (
item_id BIGINT,
event_time TIMESTAMP,
price DOUBLE,
status STRING
)
PARTITIONED BY (ds STRING)
CLUSTERED BY (item_id)
SORTED BY (event_time)
INTO 256 BUCKETS
TBLPROPERTIES ('table.format.version' = '2');Run DESC EXTENDED hash_delta_sales_demo; to view the table information. The table's bucketing and sorting definitions are as follows:
ClusterType: hash
BucketNum: 256
ClusterColumns: [item_id]
SortColumns: [event_time ASC]Incremental writes
The following examples use a non-partitioned table to demonstrate incremental writes and reclustering.
Initial data write
INSERT INTO TABLE hash_delta_sales_demo VALUES (1001, TIMESTAMP '2026-05-01 10:00:00', 10.00, 'active'), (1001, TIMESTAMP '2026-05-03 10:00:00', 13.00, 'active'), (1002, TIMESTAMP '2026-05-01 11:00:00', 20.00, 'active'); DESC EXTENDED hash_delta_sales_demo;Delete operation
Check the table status after deleting some data:
DELETE FROM hash_delta_sales_demo WHERE item_id = 1002; DESC EXTENDED hash_delta_sales_demo;Backfill historical data
Backfill a historical version. The event_time of this version falls between the existing timestamps for item_id=1001:
INSERT INTO TABLE hash_delta_sales_demo VALUES (1001, TIMESTAMP '2026-05-02 09:00:00', 12.00, 'active'); DESC EXTENDED hash_delta_sales_demo;
Bucket pruning
When you run an equality filter query on a bucketing column, MaxCompute uses the hash distribution to locate the target bucket directly, which avoids scanning all other buckets. The following query filters by item_id = 1001 and reads only the logical bucket containing this value, avoiding a full table scan:
SELECT * FROM hash_delta_sales_demo
WHERE item_id = 1001
ORDER BY event_time
LIMIT 10;
-- Returns:
+------------+---------------------+------------+--------+
| item_id | event_time | price | status |
+------------+---------------------+------------+--------+
| 1001 | 2026-05-01 10:00:00 | 10.0 | active |
| 1001 | 2026-05-02 09:00:00 | 12.0 | active |
| 1001 | 2026-05-03 10:00:00 | 13.0 | active |
+------------+---------------------+------------+--------+Full recluster
If you need to reorganize existing data, run RECLUSTER FULL. This operation preserves the semantics of historical data in the table and reorganizes the stored data according to the current table definition.
ALTER TABLE hash_delta_sales_demo RECLUSTER FULL;
DESC EXTENDED hash_delta_sales_demo;An Append Delta Table with Hash Cluster supports incremental write operations like INSERT, UPDATE, DELETE, and MERGE INTO, while preserving the hash distribution. The optimizer chooses an execution plan based on the current data state. It leverages sorted storage when data is ordered and falls back to using hash bucketing when the data is not fully sorted. You can run RECLUSTER FULL at any time to restore the full sort order.
Data Tunnel SDK example
This section shows how to use the Data Tunnel SDK to upload and download data from the hash_delta_sales_demo table.
Import the SDK dependency
Use version 0.59 or later. For details, see the Release Notes.
Sample code
FAQ
Choosing a bucket storage size
The recommended storage size for a single bucket is between several hundred megabytes and tens of gigabytes.
Small buckets increase storage overhead and shuffle costs.
Large buckets lengthen write times and reduce the effectiveness of Bucket Pruning and shuffle optimizations.
Set the number of buckets based on expected data growth, not just the current volume, to avoid frequent table structure modifications.
If your data volume is exceptionally large, a single bucket can support more storage, or you can set a higher number of buckets. However, you must evaluate the impact on write and query performance based on your specific use case.