Failed to create a partitioned table using TSDB engine in DolphinDB: The sort keys must be specified for TSDB engine

Question

I wrote this script to create a partitioned table using a TSDB storage engine in DolphinDB.

dbName = "dfs://tsdb_value_int"
n = 10000
t = table(n:n, [`int,`long,`short,`float,`double,`string,`char,`bool,`timestamp], [INT, LONG, SHORT,FLOAT, DOUBLE, STRING, CHAR, BOOL, TIMESTAMP])
pt1 = db.createPartitionedTable(table=t, tableName=`pt1, partitionColumns=`int,compressMethods=dict(`timestamp`long,`delta`delta),keepDuplicates=ALL)

It raises an exception:

The sort keys must be specified for TSDB engine.

How can I create a partitioned table in DolphinDB?

Answer 1

To use the TSDB storage engine, you must specify sortColumns of function createPartitionedTable. The TSDB engine maintains an index based on the order of sort columns, so you can specify those frequently-queried columns as sort columns.

dbName = "dfs://tsdb_value_int"
if(existsDatabase(dbName)){
dropDatabase(dbName)
}
db = database(directory=dbName, partitionType=VALUE, partitionScheme=1..10,engine="TSDB")
n = 10000
t = table(n:n, [`int,`long,`short,`float,`double,`string,`char,`bool,`timestamp], [INT, LONG, SHORT,FLOAT, DOUBLE, STRING, CHAR, BOOL, TIMESTAMP])
pt1 = db.createPartitionedTable(table=t, tableName=`pt1, partitionColumns=`int,compressMethods=dict(`timestamp`long,`delta`delta),sortColumns=`short`int, keepDuplicates=ALL)
t[`int] = rand(100,n)
t[`long] = rand(100000l,n)
t[`short] = rand(10h,n)
t[`float] = rand(100.0f,n)
t[`double] = rand(100.0,n)
t[`string] = rand("A"+string(1..1000),n)
t[`char] = rand(100,n)
t[`bool] = rand([true,false],n)
tem = 2012.06.13T13:30:10.000
ts = array(timestamp,0,n)
for(i in 1..n){
tem=temporalAdd(tem, 1, `s)
ts.append!(tem)
 }
t[`timestamp] = ts
pt1.append!(t)

See createPartitionedTable — DolphinDB 2.0 documentation for details.