Advanced usage

Complex partition expressions

When primary key fields cannot be directly used for partitioning (for example, the primary key is a UNIX timestamp rather than a date string), you can include function calls in the regular partition expression to derive partition keys from primary keys that are more convenient for business filtering.

The sample table events has a primary key of id + create_time (where create_time is a UNIX timestamp in seconds):

+------------+---------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME   |  TYPE   | IS_PRIMARY_KEY |
+------------+---------------+---------+----------------+
| events     | id            | BIGINT  | true           |
| events     | create_time   | BIGINT  | true           |
| events     | payload       | VARCHAR | false          |
| events     | source        | VARCHAR | false          |
+------------+---------------+---------+----------------+

Convert the timestamp field create_time to a date string and use it as the regular partition key for daily partitioning:

CREATE INDEX events_idx USING COLUMNAR ON events(*)
PARTITION BY ENUMERABLE (
  ifnull(substring(from_unixtime(create_time), 0, 10), 'unknown') AS dt,
  bucket(128, id)
)
WITH (
  `lindorm_columnar.user.index.database` = 'my_index_db',
  `lindorm_columnar.user.index.table`    = 'events_index'
);

When querying, filter by the partition field dt to hit the daily partitions:

SELECT /*+ _use_ldps_ */ COUNT(1)
FROM lindorm_columnar.my_index_db.events_index
WHERE dt = '2026-08-10';

Build columnstore indexes for incremental data only

If you do not want to backfill historical data and only want to build indexes for incremental data, set lindorm_columnar.user.syncer.skip.fullsync = 'true':

CREATE INDEX my_tbl_idx USING COLUMNAR ON my_tbl(*)
PARTITION BY ENUMERABLE (pk1, pk2, bucket(128, pk0))
WITH (
  `lindorm_columnar.user.index.database`         = 'my_index_db',
  `lindorm_columnar.user.index.table`            = 'my_index_tbl',
  `lindorm_columnar.user.syncer.skip.fullsync`   = 'true'
);

JSON field flattening

During data synchronization, columnstore indexes can flatten JSON fields into independent columns to accelerate point queries and filtering. Both static flattening and dynamic flattening are supported.

Sample table my_json_tbl:

+-------------+-------------+--------+----------------+
| TABLE_NAME  | COLUMN_NAME |  TYPE  | IS_PRIMARY_KEY |
+-------------+-------------+--------+----------------+
| my_json_tbl | id          | BIGINT | true           |
| my_json_tbl | col1        | INT    | false          |
| my_json_tbl | json_col    | JSON   | false          |
+-------------+-------------+--------+----------------+

Example of writing JSON data:

UPSERT INTO my_json_tbl (id, col1, json_col)
VALUES (2, 2, '{"a": {"b": {"c": "hello,world", "d": 123}, "e": false}, "f": 3.14}');

Static flattening

Use lindorm_columnar.user.syncer.lci.jsonMapping. to define explicit mappings from JSON fields to columnstore table fields:

CREATE INDEX columnar_idx USING COLUMNAR ON my_json_tbl(*)
PARTITION BY ENUMERABLE (ifnull(id % 16, 0) AS dt, bucket(16, id))
WITH (
  `lindorm_columnar.user.syncer.lci.jsonMapping.json_col` = 'a.b.c VARCHAR, a.e BOOLEAN, f DOUBLE',
  `lindorm_columnar.user.index.database` = 'my_index_db',
  `lindorm_columnar.user.index.table`    = 'my_index_tbl'
);

• a.b.c VARCHAR, a.e BOOLEAN, f DOUBLE defines each flattened field, separated by commas.

• Field name: JSON path, separated by periods (.).

• Field type: Supports BOOLEAN, BYTE, SHORT, INTEGER, LONG, FLOAT, DOUBLE, and VARCHAR.

• You can configure mappings for multiple JSON fields through multiple lindorm_columnar.user.syncer.lci.jsonMapping. entries.

• The same JSON field cannot be defined in both static and dynamic flattening simultaneously.

Dynamic flattening

Use lindorm_columnar.user.syncer.lci.dynamicJsonColumns to declare JSON columns that need to be dynamically flattened. The columnstore index infers field types from the actual JSON content and automatically extends the columnstore table:

CREATE INDEX columnar_idx USING COLUMNAR ON my_json_tbl(*)
PARTITION BY ENUMERABLE (ifnull(id % 16, 0) AS dt, bucket(16, id))
WITH (
  `lindorm_columnar.user.syncer.lci.dynamicJsonColumns` = 'json_col',
  `lindorm_columnar.user.index.database` = 'my_index_db',
  `lindorm_columnar.user.index.table`    = 'my_index_tbl'
);

• Multiple dynamic JSON columns are supported, separated by commas, such as json_col1,json_col2.

• Automatically inferred field types include only BOOLEAN, LONG, DOUBLE, and STRING. When multiple types appear for the same field, it degrades to STRING.

• Dynamic JSON flattening is not supported during full build: Dynamic flattening takes effect only during the incremental phase.

• The same JSON field cannot be defined in both static and dynamic flattening simultaneously.

JSON flattened field naming rules

After configuring static or dynamic flattening for a JSON field:

• Default behavior: The original JSON field is no longer included in the columnstore table. Flattened field names are prefixed with the corresponding JSON field name, such as json_col.a.b.c and json_col.a.e.

Synchronize the original JSON field

To also retain the original JSON field, set lindorm_columnar.user.syncer.lci.json.syncOriginalJsonContent = 'true':

• json_col: STRING type, stores the original JSON content.

• json_col.a.b.c and json_col.a.e: Flattened fields.

Remove the prefix from flattened field names

To remove the JSON field name prefix from flattened field names, set lindorm_columnar.user.syncer.lci.json.ignoreJsonMappingPrefix = 'true':

• The flattened column names are directly named a.b.c and a.e.

Dynamic schema change awareness

After setting lindorm_columnar.user.syncer.lci.dynamicSchema = 'true', the columnstore index automatically detects schema changes on the primary table and synchronizes them to the columnstore table:

CREATE INDEX my_tbl_idx USING COLUMNAR
ON my_tbl(*)
PARTITION BY ENUMERABLE (pt_d, bucket(128, pk0))
WITH (
  `lindorm_columnar.user.index.database`             = 'my_index_db',
  `lindorm_columnar.user.index.table`                = 'my_index_tbl',
  `lindorm_columnar.user.syncer.lci.dynamicSchema`   = 'true'
);

Column filter regular expression

When you only need to synchronize specific columns, use lindorm_columnar.user.syncer.lci.schema.columnRegex to specify a regular expression. This expression has an intersection relationship with the column_name(,...) list. Primary key columns are always synchronized and do not need to be listed in the regular expression.

Example: The wide table contains c1, c2, c31, c32. To synchronize only columns matching (c1|c3.*) (resulting in c1, c31, c32):

CREATE INDEX my_tbl_idx USING COLUMNAR
ON my_tbl(*)
PARTITION BY ENUMERABLE (pt_d, bucket(128, pk0))
WITH (
  `lindorm_columnar.user.index.database`                     = 'my_index_db',
  `lindorm_columnar.user.index.table`                        = 'my_index_tbl',
  `lindorm_columnar.user.syncer.lci.dynamicSchema`           = 'true',
  `lindorm_columnar.user.syncer.lci.schema.columnRegex`      = '(c1|c3.*)'
);

New columns added to the primary table later are automatically added to the columnstore index if they match the regular expression.

Add columns to a columnstore index

After creating an index, you can use ALTER INDEX to incrementally add regular fields or JSON static mapping fields to the columnstore index without rebuilding it.

Example:

-- Original table structure
-- my_json_tbl (id BIGINT PK, col1 INT, col2 VARCHAR, json_col1 JSON, json_col2 JSON)

CREATE INDEX columnar_idx USING COLUMNAR ON my_json_tbl(id, col1, json_col1)
PARTITION BY ENUMERABLE (ifnull(id % 16, 0) AS dt, bucket(16, id))
WITH (
  `lindorm_columnar.user.syncer.lci.jsonMapping.json_col1` = 'a.b.c VARCHAR, a.e BOOLEAN',
  `lindorm_columnar.user.index.database` = 'my_index_db',
  `lindorm_columnar.user.index.table`    = 'my_index_tbl'
);

-- Add a regular field
ALTER INDEX IF EXISTS columnar_idx ON my_json_tbl ADD COLUMNS(col2);

-- Add JSON static mapping fields
ALTER INDEX IF EXISTS columnar_idx ON my_json_tbl
ADD COLUMNS (
  json_extract_long(json_col2,   '$.key1'),
  json_extract_boolean(json_col2,'$.key2'),
  json_extract_double(json_col2, '$.key3.key4')
);

Modify the column filter regular expression

For scenarios where columnRegex is configured, you can update the regular expression using ALTER INDEX ... SET:

ALTER INDEX columnar_idx ON test_table SET
`lindorm_columnar.user.syncer.lci.schema.columnRegex` = '(c1|c3_(?!excludedColumn$).*|c3.*|c4.*)';

Rename a columnstore table

After the columnstore index enters the ACTIVE state, you can use ALTER INDEX ... SET to modify the database and table name of the columnstore table:

ALTER INDEX columnar_idx ON test_table SET
`lindorm_columnar.user.index.database` = 'my_index_db',
`lindorm_columnar.user.index.table`    = 'my_new_index_tbl';

After the modification, the database and table name of the columnstore table changes to my_index_db.my_new_index_tbl.

Hot rebuilding of columnstore indexes

Changes made through ALTER INDEX only take effect for subsequent incremental data and do not rewrite historical data. When you need a full rebuild (for example, adding a new column and requiring historical data to be queryable), directly dropping and recreating the index causes a long service unavailability window. By running two indexes in parallel and renaming the columnstore table, you can complete a hot rebuild without service disruption.

The procedure is as follows:

1. Keep the old index (columnar_idx_old):

       CREATE INDEX columnar_idx_old USING COLUMNAR ON test_table(id, col1)
       PARTITION BY ENUMERABLE (ifnull(id % 16, 0) AS dt, bucket(16, id))
       WITH (
         `lindorm_columnar.user.index.database` = 'my_index_db',
         `lindorm_columnar.user.index.table`    = 'my_old_index_tbl'
       );

2. Create the new index (columnar_idx_new, with the additional column col2):

       CREATE INDEX columnar_idx_new USING COLUMNAR ON test_table(id, col1, col2)
       PARTITION BY ENUMERABLE (ifnull(id % 16, 0) AS dt, bucket(16, id))
       WITH (
         `lindorm_columnar.user.index.database` = 'my_index_db',
         `lindorm_columnar.user.index.table`    = 'my_new_index_tbl'
       );

3. Wait until columnar_idx_new becomes ACTIVE.

4. Delete the old index:

       DROP INDEX columnar_idx_old ON test_table;

5. Switch the columnstore table name of the new index to the old columnstore table name (business query SQL does not need to be modified):

       ALTER INDEX columnar_idx_new ON test_table SET
       `lindorm_columnar.user.index.database` = 'my_index_db',
       `lindorm_columnar.user.index.table`    = 'my_old_index_tbl';

The hot rebuilding of the columnstore index is now complete.