New columnstore index

Prerequisites

The following engines must be enabled and meet the version requirements:

Example

To perform efficient, parallel data analytics on a large data table named my_tbl, you can create a columnstore index for the table.

Sample table schema:

+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME |  TYPE   | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| my_tbl     | pk0         | INT     | true           |
| my_tbl     | pk1         | VARCHAR | true           |
| my_tbl     | pt_d        | VARCHAR | true           |
| my_tbl     | col0        | INT     | false          |
| my_tbl     | col1        | VARCHAR | false          |
+------------+-------------+---------+----------------+

• pk0: A data identifier with high cardinality. This makes it suitable for use as a bucket partition key.

• pt_d: The date when the data is generated. Because data is usually analyzed by day, this field is suitable for use as an enumeration partition key.

Syntax

CREATE INDEX index_name USING COLUMNAR ON table_name(column_list)
PARTITION BY ENUMERABLE (partition_expression)
WITH (properties);

Parameters:

Index columns

The index columns must include all primary key fields of the wide table.

Supported data types: TINYINT, SMALLINT, INTEGER, BIGINT, LONG, FLOAT, DOUBLE, VARCHAR, BINARY, VARBINARY, BOOLEAN, DECIMAL, JSON, DATE, and TIMESTAMP.

The JSON data type is stored as VARCHAR in the column store.

Partition configuration

The partition expression specifies that index data is partitioned using an enumeration algorithm to improve retrieval efficiency during queries. A partition expression includes an enumeration partition expression and a bucket partition expression.

PARTITION BY ENUMERABLE (dt, bucket(200, uid))

Property configuration

Data synchronization

Example of incremental synchronization only:

CREATE INDEX my_tbl_idx USING COLUMNAR ON my_tbl(pk0, pk1, pt_d, col0, col1)
PARTITION BY ENUMERABLE (pt_d, bucket(128, pk0))
WITH (
  `lindorm_columnar.user.index.database` = 'my_db',
  `lindorm_columnar.user.index.table` = 'my_tbl',
  `lindorm_columnar.user.index.type` = 'LCE',
  `lindorm_columnar.user.syncer.skip.fullsync` = 'true'
);

Expand JSON fields

You can use the json_extract function to expand JSON fields into separate columns. This facilitates efficient queries and analysis in the columnstore engine.

{
  "a": {
    "b": {
      "c": "hello,world",
      "d": 123
    },
    "e": false
  },
  "f": 3.14
}

CREATE INDEX my_tbl_idx USING COLUMNAR ON my_tbl(
  pk0, pk1, pt_d,
  json_extract_string(json_col, '$.a.b.c'),
  json_extract_long(json_col, '$.a.b.d'),
  json_extract_boolean(json_col, '$.a.e'),
  json_extract_double(json_col, '$.f')
)
PARTITION BY ENUMERABLE (pt_d, bucket(16, pk0))
WITH (
  `lindorm_columnar.user.index.database` = 'my_db',
  `lindorm_columnar.user.index.table` = 'my_tbl',
  `lindorm_columnar.user.index.type` = 'LCE'
);

`lindorm_columnar.user.syncer.lci.json.syncOriginalJsonContent` = 'true'

CREATE INDEX my_tbl_idx USING COLUMNAR ON my_tbl(
  pk0, pk1, pt_d, json_col,
  json_extract_string(json_col, '$.a.b.c'),
  json_extract_long(json_col, '$.a.b.d')
)
PARTITION BY ENUMERABLE (pt_d, bucket(16, pk0))
WITH (
  `lindorm_columnar.user.index.database` = 'my_db',
  `lindorm_columnar.user.index.table` = 'my_tbl',
  `lindorm_columnar.user.index.type` = 'LCE',
  `lindorm_columnar.user.syncer.lci.json.syncOriginalJsonContent` = 'true'
);

Syntax

You can use a HINT to specify that the query should use the columnstore index instead of the raw data from the wide table.

SELECT /*+ _use_ldps_(cg_name), _columnar_index_ */ ...
FROM index_database.index_table
WHERE ...;

In the syntax, cg_name is the name of the OLAP resource group for the compute engine.

Examples

-- Aggregate query
SELECT /*+ _use_ldps_(cg_name), _columnar_index_ */ 
  pk1, COUNT(*), SUM(col0)
FROM my_db.my_tbl
WHERE pt_d = '2024-01-01'
GROUP BY pk1;

-- Detail query
SELECT /*+ _use_ldps_(cg_name), _columnar_index_ */ *
FROM my_db.my_tbl
WHERE pt_d = '2024-01-01' AND pk0 = 12345
LIMIT 100;

View an index

SHOW INDEX FROM table_name;

The output includes information such as the index status and synchronization progress.

Modify an index

Add columns:

ALTER INDEX index_name ON table_name ADD COLUMNS (col1, col2);

Delete an index

DROP INDEX index_name ON table_name;