1. hdfs 文件
1
|
{
"retCode"
:
1
,
"retMsg"
:
"Success"
,
"data"
:[{
"secID"
:
"000001.XSHE"
,
"ticker"
:
"000001"
,
"secShortName"
:
"深发展A"
,
"exchangeCD"
:
"XSHE"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
24
,
"actPreClosePrice"
:
24
,
"openPrice"
:
24
,
"highestPrice"
:
24.4
,
"lowestPrice"
:
23.85
,
"closePrice"
:
23.9
,
"turnoverVol"
:
355700
,
"turnoverValue"
:
8582250
,
"turnoverRate"
:
0.0058
,
"accumAdjFactor"
:
0.0117201563
,
"negMarketValue"
:
1462295257.8
,
"marketValue"
:
2145064267.7
,
"PB"
:
2.2666
,
"isOpen"
:
1
},{
"secID"
:
"000002.XSHE"
,
"ticker"
:
"000002"
,
"secShortName"
:
"深万科A"
,
"exchangeCD"
:
"XSHE"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
8
,
"actPreClosePrice"
:
8
,
"openPrice"
:
8
,
"highestPrice"
:
8
,
"lowestPrice"
:
7.7
,
"closePrice"
:
7.9
,
"turnoverVol"
:
375000
,
"turnoverValue"
:
2944200
,
"turnoverRate"
:
0.0066
,
"accumAdjFactor"
:
0.0117337592
,
"negMarketValue"
:
451011000
,
"marketValue"
:
615927450
,
"PB"
:
1.0001
,
"isOpen"
:
1
},{
"secID"
:
"000004.XSHE"
,
"ticker"
:
"000004"
,
"secShortName"
:
"深安达A"
,
"exchangeCD"
:
"XSHE"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
7.25
,
"actPreClosePrice"
:
7.25
,
"openPrice"
:
7.25
,
"highestPrice"
:
7.25
,
"lowestPrice"
:
7.2
,
"closePrice"
:
7.2
,
"turnoverVol"
:
92000
,
"turnoverValue"
:
665125
,
"turnoverRate"
:
0.0078
,
"accumAdjFactor"
:
0.2649084628
,
"negMarketValue"
:
84977100
,
"marketValue"
:
175500000
,
"PB"
:
7.4199
,
"isOpen"
:
1
},{
"secID"
:
"000005.XSHE"
,
"ticker"
:
"000005"
,
"secShortName"
:
"深原野A"
,
"exchangeCD"
:
"XSHE"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
6.46
,
"actPreClosePrice"
:
6.46
,
"openPrice"
:
6.49
,
"highestPrice"
:
6.49
,
"lowestPrice"
:
6.49
,
"closePrice"
:
6.49
,
"turnoverVol"
:
94500
,
"turnoverValue"
:
613305
,
"turnoverRate"
:
0.0021
,
"accumAdjFactor"
:
0.1016459912
,
"negMarketValue"
:
287756865
,
"marketValue"
:
584100000
,
"PB"
:
9.1783
,
"isOpen"
:
1
},{
"secID"
:
"000009.XSHE"
,
"ticker"
:
"000009"
,
"secShortName"
:
"深宝安A"
,
"exchangeCD"
:
"XSHE"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
5.75
,
"actPreClosePrice"
:
5.75
,
"openPrice"
:
5.7
,
"highestPrice"
:
5.8
,
"lowestPrice"
:
5.65
,
"closePrice"
:
5.75
,
"turnoverVol"
:
767500
,
"turnoverValue"
:
4382245
,
"turnoverRate"
:
0.0084
,
"accumAdjFactor"
:
0.1026538759
,
"negMarketValue"
:
524745000
,
"marketValue"
:
1293922500
,
"PB"
:
2.4503
,
"isOpen"
:
1
},{
"secID"
:
"600601.XSHG"
,
"ticker"
:
"600601"
,
"secShortName"
:
"延中实业"
,
"exchangeCD"
:
"XSHG"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
65.7
,
"actPreClosePrice"
:
65.7
,
"openPrice"
:
66.4
,
"highestPrice"
:
66.4
,
"lowestPrice"
:
66.4
,
"closePrice"
:
66.4
,
"turnoverVol"
:
5333
,
"turnoverValue"
:
354111
,
"dealAmount"
:
81
,
"turnoverRate"
:
0.0053
,
"accumAdjFactor"
:
0.0010592167
,
"negMarketValue"
:
66400000
,
"marketValue"
:
66400000
,
"PB"
:
40.7703
,
"isOpen"
:
1
},{
"secID"
:
"600602.XSHG"
,
"ticker"
:
"600602"
,
"secShortName"
:
"真空电子"
,
"exchangeCD"
:
"XSHG"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
640.6
,
"actPreClosePrice"
:
640.6
,
"openPrice"
:
647
,
"highestPrice"
:
647
,
"lowestPrice"
:
647
,
"closePrice"
:
647
,
"turnoverVol"
:
2589
,
"turnoverValue"
:
1675083
,
"dealAmount"
:
227
,
"turnoverRate"
:
0.0051
,
"accumAdjFactor"
:
0.0019640692
,
"negMarketValue"
:
330552300
,
"marketValue"
:
1294000000
,
"PB"
:
287.6707
,
"isOpen"
:
1
},{
"secID"
:
"600651.XSHG"
,
"ticker"
:
"600651"
,
"secShortName"
:
"飞乐音响"
,
"exchangeCD"
:
"XSHG"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
119.6
,
"actPreClosePrice"
:
119.6
,
"openPrice"
:
120.8
,
"highestPrice"
:
120.8
,
"lowestPrice"
:
120.8
,
"closePrice"
:
120.8
,
"turnoverVol"
:
1102
,
"turnoverValue"
:
133122
,
"dealAmount"
:
14
,
"turnoverRate"
:
0.0022
,
"accumAdjFactor"
:
0.0008192464
,
"negMarketValue"
:
60400000
,
"marketValue"
:
60400000
,
"PB"
:
39.6397
,
"isOpen"
:
1
},{
"secID"
:
"600652.XSHG"
,
"ticker"
:
"600652"
,
"secShortName"
:
"爱使电子"
,
"exchangeCD"
:
"XSHG"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
83.2
,
"actPreClosePrice"
:
83.2
,
"openPrice"
:
0
,
"highestPrice"
:
0
,
"lowestPrice"
:
0
,
"closePrice"
:
83.2
,
"turnoverVol"
:
0
,
"turnoverValue"
:
0
,
"dealAmount"
:
0
,
"turnoverRate"
:
0
,
"accumAdjFactor"
:
0.0006920481
,
"negMarketValue"
:
22464000
,
"marketValue"
:
22464000
,
"PB"
:
33.8019
,
"isOpen"
:
0
},{
"secID"
:
"600653.XSHG"
,
"ticker"
:
"600653"
,
"secShortName"
:
"申华电工"
,
"exchangeCD"
:
"XSHG"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
103.4
,
"actPreClosePrice"
:
103.4
,
"openPrice"
:
104.4
,
"highestPrice"
:
104.4
,
"lowestPrice"
:
104.4
,
"closePrice"
:
104.4
,
"turnoverVol"
:
240
,
"turnoverValue"
:
25056
,
"dealAmount"
:
4
,
"turnoverRate"
:
0.0005
,
"accumAdjFactor"
:
0.0009289199
,
"negMarketValue"
:
52200000
,
"marketValue"
:
52200000
,
"PB"
:
97.279
,
"isOpen"
:
1
},{
"secID"
:
"600654.XSHG"
,
"ticker"
:
"600654"
,
"secShortName"
:
"飞乐股份"
,
"exchangeCD"
:
"XSHG"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
633.2
,
"actPreClosePrice"
:
633.2
,
"openPrice"
:
639.5
,
"highestPrice"
:
639.5
,
"lowestPrice"
:
639.5
,
"closePrice"
:
639.5
,
"turnoverVol"
:
101
,
"turnoverValue"
:
64590
,
"dealAmount"
:
26
,
"turnoverRate"
:
0.0048
,
"accumAdjFactor"
:
0.000663586
,
"negMarketValue"
:
13429500
,
"marketValue"
:
134358950
,
"PB"
:
282.9834
,
"isOpen"
:
1
},{
"secID"
:
"600656.XSHG"
,
"ticker"
:
"600656"
,
"secShortName"
:
"浙江凤凰"
,
"exchangeCD"
:
"XSHG"
,
"tradeDate"
:
"1991-10-21"
,
"preClosePrice"
:
1242.9
,
"actPreClosePrice"
:
1242.9
,
"openPrice"
:
1255.3
,
"highestPrice"
:
1255.3
,
"lowestPrice"
:
1255.3
,
"closePrice"
:
1255.3
,
"turnoverVol"
:
140
,
"turnoverValue"
:
175742
,
"dealAmount"
:
7
,
"turnoverRate"
:
0.0031
,
"accumAdjFactor"
:
0.0007136096
,
"negMarketValue"
:
56502308.3
,
"marketValue"
:
321798665.6
,
"PB"
:-
604.4303
,
"isOpen"
:
1
}]}
|
1
|
|
2. 创建 hive 临时表
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
|
CREATE EXTERNAL TABLE
if
not exists sensitop.equd_json_tmp (
retCode string,
retMsg string,
data array<struct<
secID: string,
tradeDate: date,
ticker: string,
secShortName: string,
exchangeCD: string,
preClosePrice:
double
,
actPreClosePrice:
double
,
openPrice:
double
,
highestPrice:
double
,
lowestPrice:
double
,
closePrice:
double
,
turnoverVol:
double
,
turnoverValue:
double
,
dealAmount:
int
,
turnoverRate:
double
,
accumAdjFactor:
double
,
negMarketValue:
double
,
marketValue:
double
,
PE:
double
,
PE1:
double
,
PB:
double
,
isOpen:
int
>>)
ROW FORMAT SERDE
'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION
'hdfs://hdfs1.wdp:8020/sensitop/finance/equd'
;
|
1
|
|
3. 创建 hive 表
1
|
需要把上面表里数组里的数据一条一条放入这个表:
|
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
|
CREATE TABLE
if
not exists sensitop.equd_h(
secID string,
ticker string,
secShortName string,
exchangeCD string,
tradeDate date,
preClosePrice
double
,
actPreClosePrice
double
,
openPrice
double
,
highestPrice
double
,
lowestPrice
double
,
closePrice
double
,
turnoverVol
double
,
turnoverValue
double
,
dealAmount
int
,
turnoverRate
double
,
accumAdjFactor
double
,
negMarketValue
double
,
marketValue
double
,
PE
double
,
PE1
double
,
PB
double
,
isOpen
int
)
partitioned by (year string)
ROW FORMAT SERDE
'org.apache.hive.hcatalog.data.JsonSerDe'
|
1
|
|
1
|
然后新建一个最张表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
CREATE TABLE
if
not exists sensitop.equd(
secID string,
ticker string,
secShortName string,
exchangeCD string,
tradeDate date,
preClosePrice
double
,
actPreClosePrice
double
,
openPrice
double
,
highestPrice
double
,
lowestPrice
double
,
closePrice
double
,
turnoverVol
double
,
turnoverValue
double
,
dealAmount
int
,
turnoverRate
double
,
accumAdjFactor
double
,
negMarketValue
double
,
marketValue
double
,
PE
double
,
PE1
double
,
PB
double
,
isOpen
int
)
partitioned by (year string)
|
1
|
|
1
|
<strong>注意:这里的字段顺序和上面临时表的顺序要一致。</strong>
|
1
|
|
4. 用 Partition 更新数据
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
|
insert overwrite table sensitop.equd_tmp
partition (year=
'2016'
)
select b.dt.secID,
b.dt.ticker,
b.dt.secShortName,
b.dt.exchangeCD,
b.dt.tradeDate,
b.dt.preClosePrice,
b.dt.actPreClosePrice,
b.dt.openPrice,
b.dt.highestPrice,
b.dt.lowestPrice,
b.dt.closePrice,
b.dt.turnoverVol,
b.dt.turnoverValue,
b.dt.dealAmount,
b.dt.turnoverRate,
b.dt.accumAdjFactor,
b.dt.negMarketValue,
b.dt.marketValue,
b.dt.PE,
b.dt.PE1,
b.dt.PB,
b.dt.isOpen
from sensitop.equd_json_tmp LATERAL VIEW explode(equd_json_tmp.data) b AS dt
where dt.tradedate >=
'2016-01-01'
and dt.tradedate <=
'2016-12-31'
;
|
1
|
|
1
|
|
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
|
insert overwrite table sensitop.equd
partition (year=
'2016'
)
select secID,
ticker,
secShortName,
exchangeCD,
tradeDate,
preClosePrice,
actPreClosePrice,
openPrice,
highestPrice,
lowestPrice,
closePrice,
turnoverVol,
turnoverValue,
dealAmount,
turnoverRate,
accumAdjFactor,
negMarketValue,
marketValue,
PE,
PE1,
PB,
isOpen
from sensitop.equd_tmp dt
where year =
'2016'
;
|
1
|
|
5. 用nifi实现动态插入数据
1
|
|
1
|
这里有二个分支,左边一个是每天
20
:
00
更新当年的partion; 右边一个是更新
1990
到
2015
年的数据,而且只需要更新一次。
|
1
|
|
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
|
insert overwrite table sensitop.equd_h
partition (year=
'${year}'
)
select b.dt.secID,
b.dt.ticker,
b.dt.secShortName,
b.dt.exchangeCD,
b.dt.tradeDate,
b.dt.preClosePrice,
b.dt.actPreClosePrice,
b.dt.openPrice,
b.dt.highestPrice,
b.dt.lowestPrice,
b.dt.closePrice,
b.dt.turnoverVol,
b.dt.turnoverValue,
b.dt.dealAmount,
b.dt.turnoverRate,
b.dt.accumAdjFactor,
b.dt.negMarketValue,
b.dt.marketValue,
b.dt.PE,
b.dt.PE1,
b.dt.PB,
b.dt.isOpen
from sensitop.equd_json_tmp LATERAL VIEW explode(equd_json_tmp.data) b AS dt
where dt.tradedate >=
'${year}-01-01'
and dt.tradedate <=
'${year}-12-31'
|
1
|
|
1
|
|
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
|
insert overwrite table sensitop.equd
partition (year=
'${year}'
)
select secID,
ticker,
secShortName,
exchangeCD,
tradeDate,
preClosePrice,
actPreClosePrice,
openPrice,
highestPrice,
lowestPrice,
closePrice,
turnoverVol,
turnoverValue,
dealAmount,
turnoverRate,
accumAdjFactor,
negMarketValue,
marketValue,
PE,
PE1,
PB,
isOpen
from sensitop.equd_tmp dt
where year =
'${year}'
|
1
|
|
本文转自疯吻IT博客园博客,原文链接:http://www.cnblogs.com/fengwenit/p/6022599.html,如需转载请自行联系原作者