RFC-006: Fully functional "create index" SQL statement

[dl239]

Problem

If you want to develop a inference application, you need to write a feature extraction SQL to generate features. In general, the SQL is based on time windows which need to set as ts col when executed with performance sensitive mode. At the beginning, you may create table like this:

CREATE TABLE trans (c1 string, c2 int, c3 bigint, c4 timestamp, index(key=c1, ts=c4, abs_ttl=0, ttl_type=absolute));

You can compute feature in the time window by c1 and c4 columns:

SELECT c1, sum(c2) OVER w1 as w1_c4_sum FROM trans WINDOW w1 AS (PARTITION BY trans.c1 ORDER BY trans.c4 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

Later, if you want to compute feature in another time window by c2 and c4, you need to create a new index with c2, c4.

CREATE INDEX index1 ON trans(c2) OPTIONS(ts=c4, abs_ttl=1d, ttl_type=absolute);

But you cannot create new index with c2, c3 currently, because c3 have not been setted as ts column when creating the table.

/*unsupport*/
CREATE INDEX index2 ON trans(c2) OPTIONS(ts=c3, abs_ttl=1d, ttl_type=absolute);

Solution

• delete the ts checking code in CLI/NameServer
• get ts value from encoded value in LogEntry rather than TSDimension
• the situation that have both index without ts col and index with ts col is required to pay special attention
• skip current row if the value of setted ts col is NULL

The LogEntry structure is shown as below:

message LogEntry {
    optional uint64 term = 1;
    optional uint64 log_index = 2;
    optional string pk = 3;
    optional bytes value = 4;
    optional uint64 ts = 5;
    repeated Dimension dimensions = 6;
    optional MethodType method_type = 7;
    repeated TSDimension ts_dimensions = 8;
}

Note: When a new index is added, even if it can share the same PK col with another index, a new double-layer skiplist will be created.
Future improvement:
In order to reduce the memory used, we will share the PK col(first level skiplist) later.

Issues

• Support adding index with any ts col #529
• 创建索引时，相同key和ts，需要验证一致性 #164

Changes and Additions to Public Interfaces

N.A

Performance Impact

• the cost time of create index

Backwards Compatibility and Upgrade Path

N.A