Quick start

Application scenarios

• IoV / IoT: device metric aggregation and time-series statistical analysis.

• E-commerce and marketing: multi-dimensional analysis of orders, user behavior, and user profiles.

• Logistics and supply chain: report queries on orders, waybills, and fulfillment status.

• Financial risk control: real-time aggregation and correlation analysis of transaction details.

Core capabilities

• OLAP query acceleration: Columnstore indexes are deeply optimized for OLAP operations such as aggregation, filtering, and scanning. During query execution, only the involved columns are read, avoiding the full-row scan overhead of row-based storage.

• Seamless compatibility with wide table SQL syntax: You can create an index using the standard CREATE INDEX ... USING COLUMNAR syntax. For queries, include a hint in the SELECT statement to automatically route the query to the computing engine and hit the columnstore index, without modifying business code.

• Flexible data partitioning strategies: Supports a combination of regular partition expressions and bucket partition expressions. Regular partitions support expression computation on business fields such as time and date. Bucket partitions enable hash sharding on high-cardinality fields, helping you balance data volume and query efficiency.

• Dynamic schema awareness: Automatically detects new columns, dynamic columns, and wildcard column changes in wide tables, and synchronizes them to the columnstore index. You can extend analytical fields without rebuilding the index.

Prerequisites

• A Lindorm instance has been purchased and activated, and the wide table engine version is 2.5.0 or later. To check or upgrade the version, see LindormTable release notes and Upgrade the minor engine version of a Lindorm instance.

• The computing engine has been enabled. For more information, see Activate the service.

• At least one OLAP resource group has been created and set as the default resource group. For information about how to create and configure a default resource group, see Activation and Management.

• The target wide table has been created in the wide table engine and sample data has been written.

Usage limits

• Columnstore indexes do not support synchronous building: Index data is built through asynchronous synchronization. After creating an index, you must wait until the index status changes to ACTIVE before it can be used for queries.

• The initial build of a columnstore index takes approximately 15 minutes. If there are many background index build tasks or the wide table contains a large amount of data, the build may take longer.

• If the primary table has hot and cold data separation enabled, monitor the throttling status of cold storage to prevent slow index building caused by cold storage lookups, which may also cause write backpressure on the primary table.

• After wide table data is cleared due to TTL expiration, the columnstore index data is not automatically cleared and must be explicitly handled on the business side.

Activation steps

1. Log on to the Lindorm console.

2. In the upper-left corner of the page, select the region where the instance is deployed.

3. On the Instances page, click the ID of the target instance or click View Instance Details in the Actions column for the instance.

4. In the left-side navigation pane, choose Wide Table Engine.

5. Click the Columnar Index tab, and then click Activate Now.

6. In the dialog box that appears, click Determine and wait for the activation to complete.

After the activation is complete, you can create columnstore indexes for wide tables by using the standard SQL statement CREATE INDEX ... USING COLUMNAR.

Scenario

Assume that you need to perform efficient parallel data analysis on the order detail table orders. The table structure is as follows:

+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME |  TYPE   | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| orders     | id          | BIGINT  | true           |
| orders     | dt          | VARCHAR | true           |
| orders     | amount      | DECIMAL | false          |
| orders     | status      | VARCHAR | false          |
+------------+-------------+---------+----------------+

• The primary key id is the order identifier with high cardinality, making it suitable as a bucket partition key.

• The primary key dt is the order date (typically analyzed by day in business scenarios) and can be used directly as a regular partition key.

Procedure

1. Create a columnstore index:

   Create a columnstore index on all columns of the orders table, partitioned by date with 128 buckets:

   CREATE INDEX orders_idx USING COLUMNAR
   ON orders(*)
   PARTITION BY ENUMERABLE (dt, bucket(128, id))
   WITH (
     `lindorm_columnar.user.index.database` = 'my_index_db',
     `lindorm_columnar.user.index.table`    = 'orders_index'
   );

   Note

   The preceding SQL has identical syntax in both OLAP Mode and Lake Mode. The system automatically selects the underlying implementation based on the default engine of the instance. You can also use WITH parameters to explicitly switch to Lake Mode or specify an OLAP engine resource group. For more information, see Storage modes.

2. Check the index status:

   SHOW INDEX FROM orders;

   Sample output (key fields):

   +-------------+------------+------------+-------------+---------------------------------+------------+
   | TABLE_SCHEMA| DATA_TABLE | INDEX_NAME | INDEX_STATE | INDEX_PROGRESS                  | INDEX_TYPE |
   +-------------+------------+------------+-------------+---------------------------------+------------+
   | default     | orders     | orders_idx | ACTIVE      | 2026-08-10 18:59:20.652 +0800   | COLUMNAR   |
   +-------------+------------+------------+-------------+---------------------------------+------------+

   • After INDEX_STATE changes from BUILDING to ACTIVE, the index is available for queries.

   • INDEX_PROGRESS indicates the incremental synchronization checkpoint: wide table data before this checkpoint has been fully synchronized to the columnstore index. This field is empty during the BUILDING phase and advances continuously with incremental synchronization after entering the ACTIVE state.

   For the complete field description of SHOW INDEX and query examples, see Basic usage — View columnstore indexes.

3. Query data using the columnstore index:

   Include a hint in the SELECT statement to route the query to the computing engine and hit the columnstore index:

   SELECT /*+ _use_ldps_(cg0), _columnar_index_ */
          dt, COUNT(*) AS order_cnt, SUM(amount) AS gmv
   FROM my_index_db.orders_index
   WHERE dt BETWEEN '2026-08-01' AND '2026-08-10'
   GROUP BY dt;

   In the preceding statement, cg0 is the name of the computing resource group used for the query. You can replace it with your actual resource group name.

Billing

The columnstore index feature itself does not incur additional charges. The following fees apply after an index is created:

• Storage fees for columnstore index data.

• CU fees for the actual data synchronization between the primary table and the columnstore index.

For specific billing items and rules, refer to the official Alibaba Cloud pricing page.

What to do next

• Learn basic syntax and partitioning strategies → Basic usage.

• Explore capabilities such as JSON flattening, dynamic schema awareness, and hot rebuilding → Advanced usage.

• Learn about the differences between the two storage modes and how to directly connect to query columnstore tables → Storage modes.

• Troubleshoot issues → FAQ.