每个测点每隔5秒一条，如何为每100个测点每个测点35万条记录产生一批模拟数据

Question

在DolphinDB database中分布式数据库按时间和测点两个维度进行组合分区，时间维度是按天值分区，测点维度是每100个测点范围分区。现在想插入3500亿条数据（100万个测点，每个测点5秒一条共35万条数据），要求每批产生100个测点的所有记录并插入数据库，产生每批数据的代码如下：

id_int=800000;
id=array(int,0);
j=100;
M=350000;
N_max=1799999;
time=array(DATETIME, 0);
ts=2019.03.22 15:43:13;
h=M;
id_par=array(int,0);
do{time.append!(ts);ts+=5;h-=1}while(ts<=2019.04.11 22:07:58 and h>=1)
tt=take(time,M*j);
b=id_int;
do{id.append!(take(id_int,M));id_int+=1;}while(id_int-b<j)
tmp=table(id as id,tt as time,take(1.0, M*j) as v,take(2.0, M*j) as q)
//todo:insert tmp into dfsTable

do { 
    b=id_int;
    id2=array(int,0);
    do{id2.append!(take(id_int,M));id_int+=1;}while(id_int-b<j)
    tmp=table(id2 as id,tt as time,take(1.0, M*j) as v,take(2.0, M*j) as q)
    //todo:insert tmp into dfsTable
}while(id_int<=N_max)

感觉代码的效率比较低，请问有什么优化的办法？

Answer 1

下面的代码中，prepareData函数可为每批100个测点产生所有记录，writeData把数据批量写入分布式表，loopWrite是把100万个测点用cut函数分割后用loop循环执行。

def prepareData(idVec, startTime,n){
    idSize=size(idVec)
    records= n * idSize
    id = array(INT, records)
    for(i in 0:idSize) id[(i*n) : ((i+1)*n)] = idVec[i]
    return table(id,  take(startTime+(0..(n-1))*5,records) as time, rand(100.0, records) as value,take(2.0,records) as quality)
}

def writeData(idVec,startTime,n){
    pt=loadTable("dfs://VALUE3","pt")
    pt.append!(prepareData(idVec,startTime,n))
}
def loopWrite(idVec,startTime,n){
    ids=idVec.cut(100)
    loop(writeData{,startTime,n},ids)
}
login("admin","123456")
idVec=800000..1799999
startTime=2019.03.22 15:43:13;
n=350000
submitJob("submit_write", "write data", loopWrite{idVec, startTime, n})