系统/软件名

版本

centos

CentOS Linux release 7.3.1611

hadoop

hadoop-2.7.4

hive

apache-hive-2.1.1-bin

hbase

hbase-1.4.0

zookeeper

zookeeper-3.3.6

sqoop

sqoop-1.4.6.bin__hadoop-2.0.4-alpha

kylin

apache-kylin-2.3.1-bin

GP

greenplum-db-4.3.8.2-build-1-RHEL5-x86_64

IP

秘钥

数据库

表名

存储方式

数据量

192.168.xxx.xxx

xxgxx/xxx

testDB

dw_test_data

行式/无压缩

45W

10.5.xxx.xxx

xxx/xxx

testdb

dw_test_data

行式/无压缩

100W

数据库

SQL

平均执行时间（S）

数据量（万条）

结果数（条）

KYLIN

select 

rq,

gngw,

jjdq,

wp,

jjrlx,

jjrxb,

jjrnld,

sum(jdl),

sum(wtdl),

sum(tqjdl),

sum(tqwtdl),

sum(syjdl),

sum(sywtdl)  

from dw_test_data

group by rq, gngw, jjdq, wp, jjrlx, jjrxb, jjrnld

order by rq ,jjrnld

0.25

139

39939

同上

1.9

100

120528

GP

select 

da.month,

sum(dw.jdl) ,

sum(dw.wtdl) ,

sum(dw.tqjdl) ,

sum(dw.tqwtdl) ,

sum(dw.syjdl),

sum(dw.sywtdl)

from dw_test_data dw

left join dw_test_dim_date da on dw.rq=da.day

left join dw_test_dim_region re on dw.jjdq=re.city

GROUP BY

da.month

3.9

100

8

select 

dw.rq,

dw.gngw,

dw.jjdq,

dw.wp,

dw.jjrlx,

dw.jjrxb,

dw.jjrnld,

dw.sjrlx,

dw.sjrlb,

dw.sjrnlb,

dw.kdjc,

sum(dw.jdl) ,

sum(dw.wtdl) ,

sum(dw.tqjdl) ,

sum(dw.tqwtdl) ,

sum(dw.syjdl),

sum(dw.sywtdl)

from dw_test_data dw

left join dw_test_dim_date da on dw.rq=da.day

left join dw_test_dim_region re on dw.jjdq=re.city

GROUP BY

dw.rq,

dw.gngw,

dw.jjdq,

dw.wp,

dw.jjrlx,

dw.jjrxb,

dw.jjrnld,

dw.sjrlx,

dw.sjrlb,

dw.sjrnlb,

dw.kdjc

ORDER BY dw.rq ,dw.jjrnld

190

100

682992

select 

dw.rq,

dw.gngw,

dw.jjdq,

dw.wp,

dw.jjrlx,

dw.jjrxb,

dw.jjrnld,

dw.sjrlx,

dw.sjrlb,

dw.sjrnlb,

dw.kdjc,

sum(dw.jdl) ,

sum(dw.wtdl) ,

sum(dw.tqjdl) ,

sum(dw.tqwtdl) ,

sum(dw.syjdl),

sum(dw.sywtdl)

from dw_test_data dw

left join dw_test_dim_date da on dw.rq=da.day

left join dw_test_dim_region re on dw.jjdq=re.city

GROUP BY

dw.rq,

dw.gngw,

dw.jjdq,

dw.wp,

dw.jjrlx,

dw.jjrxb,

dw.jjrnld,

dw.sjrlx,

dw.sjrlb,

dw.sjrnlb,

dw.kdjc

ORDER BY dw.rq ,dw.jjrnld

limit 200

2

100

200