-
Notifications
You must be signed in to change notification settings - Fork 8
/
fund_data_load.txt
71 lines (68 loc) · 2.63 KB
/
fund_data_load.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/**
fund_data_load.txt
Script to load data to datacase
DolphinDB Inc.
DolphinDB server version: 2.00.7 2022.07.14
Storage engine: OLAP
Last modification time: 2022.10.31
*/
// clean up the environment
undef all
clearAllCache()
go
login("admin", "123456")
/**
modified location: csvPath、csvPath1
*/
csvPath = "datafile/nav.csv"
csvPath1 = "datafile/hs300.csv"
def readColumnsFromWideCSV(absoluteFilename){
schema1 = extractTextSchema(absoluteFilename)
update schema1 set type = `STRING
allSymbol = loadText(absoluteFilename,,schema1)[0, 1:]
titleSchema = extractTextSchema(absoluteFilename, skipRows = 0);
for(x in allSymbol){
testValue = exec x[name] from titleSchema
testValue = testValue[1:]
}
return testValue
}
def readIndexedMatrixFromWideCSV(absoluteFilename){
contracts = readColumnsFromWideCSV(absoluteFilename)
dataZoneSchema = extractTextSchema(absoluteFilename, skipRows = 1)
update dataZoneSchema set type = "DOUBLE" where name != "col0"//所有行除第一行外全部改成double
update dataZoneSchema set type = "DATE" where name = "col0"//所有行除第一行外全部改成DATE
dataZoneWithIndexColumn = loadText(absoluteFilename, skipRows = 1, schema = dataZoneSchema)
indexVector = exec col0 from dataZoneWithIndexColumn
dataZoneWithoutIndex = dataZoneWithIndexColumn[:, 1:]
dataMatrix = matrix(dataZoneWithoutIndex)
dataMatrix.rename!(indexVector, contracts)
return dataMatrix
}
//data cleaning and transform
allSymbols = readColumnsFromWideCSV(csvPath)$STRING
dataMatrix = readIndexedMatrixFromWideCSV(csvPath)
fundTable = table(dataMatrix.rowNames() as tradingDate, dataMatrix)
result = fundTable.unpivot(`tradingDate, allSymbols).rename!(`tradingDate`fundNum`value)
allSymbols1 = readColumnsFromWideCSV(csvPath1)$STRING
dataMatrix1 = readIndexedMatrixFromWideCSV(csvPath1)
fundTable1 = table(dataMatrix1.rowNames() as tradingDate, dataMatrix1)
result1 = fundTable1.unpivot(`tradingDate, allSymbols1).rename!(`tradingDate`fundNum`value)
result1
//create database an table, then load data
dbName = "dfs://fund_OLAP"
dataDate = database(, VALUE, 2021.01.01..2021.12.31)
symbol = database(, HASH, [SYMBOL, 20])
if(existsDatabase(dbName)){
dropDatabase(dbName)
}
db = database(dbName, COMPO, [dataDate, symbol])
name = `tradingDate`fundNum`value
type = `DATE`SYMBOL`DOUBLE
tbTemp = table(1:0, name, type)
tbName1 = "fund_OLAP"
db.createTable(tbTemp, tbName1)
loadTable(dbName, tbName1).append!(result)
tbName2 = "fund_hs_OLAP"
db.createTable(tbTemp, tbName2)
loadTable(dbName, tbName2).append!(result1)