Lindorm:Columnstore index

Create a columnstore index

CREATE INDEX index_name USING COLUMNAR
ON table_name(column_name(,..))
PARTITION BY ENUMERABLE (column_name(,...), bucket(bucket_num, column_name))
WITH (`lindorm_columnar.user.index.database` = 'columnar_db_name',
      `lindorm_columnar.user.index.table` = 'columnar_tbl_name');

CREATE INDEX my_tbl_idx USING COLUMNAR
ON my_tbl(pk0, pk1, pk2, col0, col1)
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');

View a columnstore index

After a columnstore index is created, its data is built continuously. The wide table, which is the primary table, continuously synchronizes its data to the columnstore index table. Data synchronization includes historical data and incremental data synchronization. During incremental data synchronization, the index data lags behind the primary table data. This delay is typically less than 1 hour.

You can use the SHOW INDEX statement to view the status of a columnstore index. For more information about how to use SHOW INDEX and for a description of the result set, see SHOW INDEX.

Use a columnstore index

A columnstore index can enhance the analytical computing capabilities for massive data in wide tables. You can specify HINT parameters in your SELECT query statement to route the query request to the compute engine. The compute engine then uses the columnstore index to accelerate the query, which improves the efficiency of big data computing.

SELECT /*+ _use_ldps_(cg0), _columnar_index_ */ COUNT(*), SUM(col0), MIN(col0), MAX(col0)
FROM my_index_db.my_index_tbl
WHERE col0 > 100 AND col0 < 200 OR col0 > 500
GROUP BY pk1;

SELECT /*+ _use_ldps_(cg0), _columnar_index_ */ pk0 + col0, pk1
FROM my_index_db.my_index_tbl
WHERE col0 > 100 AND col0 < 200 OR col0 > 500
ORDER BY pk1
LIMIT 100;

SELECT /*+ _use_ldps_(cg0), _columnar_index_ */ *
FROM my_index_db.my_index_tbl0 as t0
  JOIN my_index_db.my_index_tbl1 as t1 
ON t0.pk0 = t1.pk0
AND t0.pk1 = t1.pk1
LIMIT 100;

Delete a columnstore index

You can use the DROP INDEX statement to delete a columnstore index. For more information about how to use DROP INDEX and for examples, see DROP INDEX.

Complex partition filter expressions

Assume that the schema of the sample table my_ts_tbl is as follows:

+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME |  TYPE   | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| my_ts_tbl  | id          | INT     | true           |
| my_ts_tbl  | ts          | LONG    | true           |
| my_ts_tbl  | col0        | VARCHAR | false          |
| my_ts_tbl  | col1        | INT     | false          |
+------------+-------------+---------+----------------+

When you create a columnstore index, if the primary key fields of the wide table cannot be directly used in the partition expression for the columnstore index, you can include calculation logic in the partition expression. The following is an example.

• Create a columnstore index for all fields of the wide table and partition the columnstore index data by day based on the timestamp field ts:

  CREATE INDEX my_ts_idx USING COLUMNAR ON my_ts_tbl(*)
  PARTITION BY ENUMERABLE (ifnull(substring(from_unixtime(ts), 0, 10), 'unknown') AS dt, bucket(128, id))
  WITH (`lindorm_columnar.user.index.database` = 'my_ts_index_db',
        `lindorm_columnar.user.index.table` = 'my_ts_index_tbl');

• After the columnstore index is created, you can specify filter conditions in your query statement to query data in the columnstore index.

  SELECT /*+ _use_ldps_ */ COUNT(1)
  FROM lindorm_columnar.my_ts_index_db.my_ts_index_tbl
  WHERE dt = '2020-06-06';

Build a columnstore index for incremental data only

To skip historical data in a wide table and build a columnstore index only for incremental data, specify the lindorm_columnar.user.syncer.skip.fullsync = 'true' parameter. The following is an example:

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');

Expand and store JSON fields

The columnstore index supports expanding and storing JSON fields during data synchronization. Two methods are supported: static expansion and storage and dynamic expansion and storage.

Assume that the sample table my_json_tbl has the following table schema:

+-------------+-------------+--------+----------------+
| 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          |
+-------------+-------------+--------+----------------+

Execute the following statement to insert JSON-formatted 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}');

json_col has the following structure:

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

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');

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');

When you configure a JSON field for static expansion and storage or dynamic expansion and storage, the original JSON field is not stored in the column-oriented table. Additionally, the names of the expanded JSON fields in the column-oriented table are prefixed with the name of the corresponding JSON field.

For example, when you use `lindorm_columnar.user.syncer.lci.jsonMapping.json_col` = 'a.b.c VARCHAR, a.e BOOLEAN' to specify static JSON expansion for a columnstore index, the column-oriented table will not contain a column named json_col. Instead, it will contain the following columns:

• A column named json_col.a.b.c of type STRING that stores the a.b.c value from the json_col field.

• A column named json_col.a.e of type BOOLEAN that stores the a.e value from the json_col field.

If you still want to sync the original JSON field, or you do not want the names of the expanded JSON fields in the column-oriented table to be prefixed with the corresponding JSON field names, you can use the following methods:

Dynamically sense schema evolution (Public preview)

During data synchronization, the columnstore index can dynamically sense schema changes in the data table and apply them to the schema of the column-oriented table. When you create a columnstore index, you can specify `lindorm_columnar.user.syncer.lci.dynamicSchema` = 'true' to ensure that the schema of the column-oriented table remains consistent with the primary table schema. The following is an example:

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');

Add columns to a column-oriented table (Public preview)

After creating a columnstore index, you can use the ALTER INDEX syntax to add fields to the columnstore index without reindexing. You can add regular fields or mapping columns for static expansion of JSON fields.

Assume that the schema of the table my_json_tbl is as follows:

+-------------+-------------+---------+----------------+
|  TABLE_NAME | COLUMN_NAME |  TYPE   | IS_PRIMARY_KEY |
+-------------+-------------+---------+----------------+
| my_json_tbl | id          | BIGINT  | true           |
| my_json_tbl | col1        | INT     | false          |
| my_json_tbl | col2        | VARCHAR | false          |
| my_json_tbl | json_col1   | JSON    | false          |
| my_json_tbl | json_col2   | JSON    | false          |
+--------------+-------------+---------+----------------+

You can use the following SQL statement to create a columnstore index:

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');

At this point, the column-oriented table does not contain columns related to col2 or json_col2. You can use the following statement to add a regular field to the columnstore index:

ALTER INDEX IF EXISTS columnar_idx ON my_json_tbl ADD COLUMNS(col2);

You can also use the following statement to add a static mapping rule for a JSON field to the columnstore index:

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')
);