Access Lindorm Column Store

Background information

Notes

The feature for accessing data in Lindorm Column Store is currently in invitational preview. If you want to use this feature, contact Lindorm technical support (DingTalk ID: s0s3eg3).

Prerequisites

• You have read Usage notes.
• Complete the required operations based on your job type:
  • For Java Database Connectivity (JDBC) development, see JDBC development practices.
  • For JAR job development, see JAR job development practices.
  • For Python job development, see Python job development practices.

Features

DDL

DML

Best practices

You can use the following methods to accelerate data queries or computations.

Query data by primary key

If a table contains a massive dataset, you can specify filter conditions on the primary key to accelerate queries. The smaller the data range of the primary key you set, the better the acceleration.

Assume that the table schema is as follows:

USE lindorm_columnar;
CREATE TABLE orders
(
o_orderkey       int,
o_custkey        int,
o_orderstatus    string,
o_totalprice     double ,
o_orderdate      string ,
o_orderpriority  string,
o_clerk          string,
o_shippriority   int,
o_comment        string
)
PARTITIONED BY (bucket(1024,o_orderkey,o_custkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderkey,o_custkey'
);           

USE lindorm_columnar;
SELECT * FROM orders WHERE o_orderkey=18394 AND o_custkey=81772;

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_orderkey>100000 AND o_orderkey<200000;

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_orderkey>100000 AND o_custkey<99;

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_orderkey>100000;

Add a partition filter

In the Lindorm Column Store engine, different partitions are physically isolated from each other. Therefore, you can add partition filter conditions to accelerate data queries.

Assume that the table schema is as follows:

USE lindorm_columnar;
CREATE TABLE orders
(
o_orderkey       int,
o_custkey        int,
o_orderstatus    string,
o_totalprice     double ,
o_orderdate      string ,
o_orderpriority  string,
o_clerk          string,
o_shippriority   int,
o_comment        string
)
PARTITIONED BY (o_orderdate, bucket(1024,o_orderkey,o_custkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderdate,o_orderkey,o_custkey'
);

USE lindorm_columnar;
SELECT o_orderdate, count(*) FROM orders WHERE o_orderdate='2022-01-01' GROUP BY o_orderdate;

USE lindorm_columnar;
SELECT o_orderdate, count(*) FROM orders WHERE o_orderdate>='2022-01-01' AND o_orderdate<='2022-01-07' GROUP BY o_orderdate;

Query by non-primary key condition

For the partition compaction process, you can configure the lce.compact.major.splitKey parameter when you create a table to specify secondary sort keys. In addition to the primary key, data is sorted again based on several other columns. This accelerates queries that use non-primary key conditions.

Assume that the table schema is as follows:

USE lindorm_columnar;
CREATE TABLE orders
(
o_orderkey       int,
o_custkey        int,
o_orderstatus    string,
o_totalprice     double ,
o_orderdate      string ,
o_orderpriority  string,
o_clerk          string,
o_shippriority   int,
o_comment        string
)
PARTITIONED BY (bucket(1024,o_orderkey,o_custkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderkey,o_custkey',
'lce.compact.major.splitKey' = 'o_shippriority,o_totalprice'
);

COMPACT orders FORCE MAJOR;

You can use the following SQL statements to query data in the compacted table.

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_shippriority=0;

USE lindorm_columnar;
SELECT count(*) FROM orders WHERE o_shippriority=0 AND o_totalprice>999.9;

Associate data

The bucket partitioning method in a column-oriented table affects the data distribution. For two massive datasets, you can define the same bucket partitioning method based on the association conditions. This accelerates the contextual computing process.

Assume that the table schemas are as follows:

USE lindorm_columnar;
# Schema of the orders0 table:
CREATE TABLE orders0
(
o_orderkey       int,
o_custkey        int,
o_orderstatus    string,
o_totalprice     double ,
o_orderdate      string ,
o_orderpriority  string,
)
PARTITIONED BY (o_orderdate, bucket(1024,o_orderkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderdate,o_orderkey'
);

# Schema of the orders1 table:
CREATE TABLE orders1
(
o_orderkey       int,
o_custkey        int,
o_orderdate      string ,
o_clerk          string,
o_shippriority   int,
o_comment        string
)
PARTITIONED BY (o_orderdate, bucket(1024,o_orderkey))
TBLPROPERTIES(
'lce.primaryKey' = 'o_orderdate,o_orderkey'
);

In the `orders0` and `orders1` tables, the bucket partitioning method for each day (`o_orderdate`) is the same: bucket(1024,o_orderkey). Therefore, associating the data of the two tables for a single day based on the bucket partition field (`o_orderkey`) provides better acceleration.

The following is an example of an association query:

USE lindorm_columnar;
SELECT * FROM orders0 a
JOIN
 orders1 b
ON a.o_orderkey=b.o_orderkey AND a.o_orderdate=b.o_orderdate
WHERE o_orderdate='2022-01-01';

Accelerate queries

Compacting a specified table or a specified partition in a table can improve data order or compactness. This improves data scan performance.

CREATE TABLE hello_tbl
(id int, city string, name string, score int)
partitioned by (city, hash(4, id))
tblproperties('lce.primaryKey' = 'id,city');

COMPACT hello_tbl FORCE MAJOR;

COMPACT hello_tbl PARTITION (city='beijing') FORCE MAJOR;

After the compaction is complete, if you want to further improve the efficiency of subsequent queries, you can execute the following statements to set the relevant table parameters to accelerate subsequent queries:

ALTER TABLE hello_tbl SET TBLPROPERTIES ('lce.scan.majorCompactFilesOnly' = true);

ALTER TABLE hello_tbl SET TBLPROPERTIES ('lce.scan.enableVectorizedReader' = true);