The analysis engine reorganizes data based on the primary key of the table by default, converting it to column-format storage format and saving it in the column-format storage node. This storage method can significantly improve the efficiency of queries based on the primary key as well as the efficiency of scans in filtering scenarios. However, if business queries mostly depend on other fields for filtering or sorting, the sorting mechanism based on the primary key may not fully leverage its performance benefits. In this case, you can further optimize SQL query efficiency by adjusting the ordering key of the column-format storage table. A typical example is SaaS business scenarios where tenant ID is often used to filter queries. In these scenarios, tenant ID can be set as the ordering key.
Note:
Currently, only the analysis engine kernel versions 2.2410.11.1, 3.2503.4.0 and later support customizing column-format storage ordering keys.
Customizing column-format storage ordering keys can suspend synchronization. It is recommended to perform the operation during off-peak business hours.
The custom columnar storage sort key feature is exclusively effective for the "Read-Only Analytical Engine" and is not supported by analytical cluster.
Scenarios Where Customizing Column-Format Storage Ordering Keys Is Not Supported
A custom column-format storage ordering key should include a primary key field. The analysis engine determines whether a primary key is included. If it is not included, it implicitly adds the primary key field to the ordering key.
Columns such as float, double, longtext, blob, boolean, and json are not supported as custom ordering keys.
Customizing column-format storage ordering keys is not supported for tables without primary keys.
After customizing a column-format storage ordering key for a table, performing DDL operations (dropping, modifying, renaming, changing columns, or dropping the primary key) on the ordering key columns can interrupt the synchronization of the column-format storage table. For example, if the ordering key is modified to "id, c1, c2", performing such operations on these three columns in the primary node will interrupt synchronization.
A custom column-format storage ordering key can include up to eight fields.
Columns whose names contain Chinese characters or special characters are not supported as ordering keys.
Operation Steps
Creating a Custom Column-Format Storage Ordering Key Table with the CREATE Statement
To create a table in the read-write instance, you can add order_key=column1,column2 information to the COMMENT section of the table definition statement, separating it from other comments with a space.
CREATE TABLE table_name (
...
) COMMENT 'order_key=column_name[,column_name]';
CREATE TABLE part (
p_partkey BIGINT NOT NULL PRIMARY KEY,
p_name VARCHAR(55) NOT NULL,
p_mfgr CHAR(25) NOT NULL,
p_brand CHAR(10) NOT NULL,
p_type VARCHAR(25) NOT NULL,
p_size BIGINT NOT NULL,
p_container CHAR(10) NOT NULL,
p_retailprice DECIMAL(15,2) NOT NULL,
p_comment VARCHAR(23) NOT NULL
) COMMENT 'Part information table order_key=p_brand,p_container,p_type';
Modifying Table Ordering Keys with the ALTER Statement
For tables with data already imported, you need to set a custom column-format storage ordering key by executing the ALTER statement in the read-write instance. The syntax is as follows:
ALTER TABLE table_name COMMENT 'order_key=column_name[,column_name]';
ALTER TABLE part COMMENT 'Part information table order_key=p_brand,p_container,p_type';
Confirming Ordering Key Modification Success
Currently, the adjustment progress of the ordering key cannot be viewed. However, during the adjustment process, you still can query table data. Although data synchronization will be paused during the process, you can monitor the synchronization delay to confirm whether the ordering key has been modified.
Non-Standard Ordering Key Syntax
An ordering key contains duplicate column names:
ALTER TABLE part COMMENT 'Part information table order_key=p_partkey,p_type,p_partkey COLUMNAR=1';
A non-existing column is specified as the ordering key:
ALTER TABLE part COMMENT 'Part information table order_key=column';
Keyword error:
ALTER TABLE part COMMENT 'Part information table ORDERKEY=column';
No space is added to separate from other COMMENT information:
ALTER TABLE part COMMENT 'Part information table order_key=p_partkey';
Multiple ordering key keywords are used consecutively:
ALTER TABLE part COMMENT 'Part information table order_key=p_partkey order_key=p_partkey';
Ordering key keywords are empty:
ALTER TABLE part COMMENT 'Part information table order_key= COLUMNAR=1';