IndexR试用与评测

IndexR试用与评测IndexR试用o建表o导入数据o查询o删除表IndexR评测o测试目的o测试版本o集群配置o测试素材o测试方法o测试用例o测试结果o测试结论IndexR试用建表创建json在/home/hadoop/app/indexr-tool/example目录创建一个文件叫test_schema2.json，内容如下：{"schema":{"columns":[{"name": "date", "dataType": "bigint"},{"name": "d1", "dataType": "string"},{"name": "m1", "dataType": "int"},{"name": "m2", "dataType": "bigint"},{"name": "m3", "dataType": "float"},{"name": "m4", "dataType": "double"}]}}创建表cd /home/hadoop/app/indexr-tool/exampletools.sh -cmd settb -t test2 -c test_schema2.json检验是否成功sqlline -u jdbc:drill:schema=indexr;zk=petabase-12,petabase-31,petabase-32:2181 0:jdbc:drill:schema=indexr> select * from indexr.test2 limit 10;导入数据Hive中创建外部表，外部表与drill中的test2共享同一个路径，且表结构和存储格式完全一致，如下：CREATE EXTERNAL TABLE IF NOT EXISTS test2 ( `date` int, `d1` string, `m1` int, `m2` bigint, `m3` float, `m4` double)ROW FORMAT SERDE 'io.indexr.hive.IndexRSerde'STORED AS INPUTFORMAT 'io.indexr.hive.IndexRInputFormat' OUTPUTFORMAT'io.indexr.hive.IndexROutputFormat'LOCATION 'hdfs://petabase-11:9000/indexr/segment/test2'; hive中插入或导入数据hive > insert into table test2 values(20160701,'mac',100,192444,1.55,-331.43555);hive和drill同步test2数据cd .../indexr-toolbin/tools.sh -cmd notifysu -t test2查询drill中检索数据sqlline -u jdbc:drill:schema=indexr;zk=petabase-12,petabase-31,petabase-32:2181 0:jdbc:drill:schema=indexr> select * from indexr.test2 limit 10;删除表移除目标表上所有的采集节点bin/tools.sh -cmd rttnode -t test2tools.sh -cmd rmrtt -t test2 -host hostA,hostB删除hdfs上对应的目录与文件hdfs dfs -rm -r ${indexr.fs.data.root}/segment/test2删除indexR表tools.sh -cmd rmtb -t test2hive中同步删除该表hive > drop table test2;IndexR评测测试目的indexR格式与parquet格式在典型的BI应用场景下的性能对比测试版本indexR 0.3集群配置cpu：Intel(R) Core(TM) i7-4820K CPU @ 3.70GHz （1U 4Core）mem: 64G节点个数：5测试素材使用某项目中的主要事实表作为测试用表，表结构的建表语句如下：create table isdzd_b0_2y_r(userid_ string, btype_ string, shtag_ string, shtag2_ string, option_ string, f2 double, f3 double, f4 double, f5 double, f6 double, f7 double, f8 double, f9 double, f10 double, f11 double, f12 double, f13 double, f14 double, f15 double, f16 double, f17 double, f18 double, f19 double, f20 double, f21 double, f22 double, f23 double, f24 double, f25 double, f26 double, f27 double, f28 double, f29 double, f30 double, f31 double, f32 double, f33 double, f34 double, f35 double, f36 double, f37 double, f38 double, f39 double, f40 double, f41 double, f42 double, f43 double, f44 double, f45 double, f46 double, f47 double, f48 double, f49 double, f50 double, f51 double, f52 double,f53 double, f54 double, f55 double, f56 double, f57 double, f58 double, f59 double, f60 double, f61 double, f62 double, f63 double, f64 double, f65 double, f66 double, f67 double, f68 double, f69 double, f70 double, f71 double, f72 double, f73 double, f74 double, f75 double, f76 double, f77 double, f78 double, f79 double, f80 double, f81 double, f82 double, f83 double, f84 double, f85 double, f86 double, f87 double, f88 double, f89 double, f90 double, f91 double, f92 double, f93 double, f94 double, f95 double, f96 double, f97 double, f98 double, f99 double, f100 double, f101 double, f102 double, f103 double, f104 double, f105 double, f106 double, f107 double, f108 double, f109 double, f110 double, f111 double, f112 double, f113 double, f114 double, nsrsbh string, nsrmc string, hydm string, djzclx string, xzqh string, zzsjnfs string, zzsyhzc string, upid_ string,bbq_ string )ROW FORMAT SERDE'io.indexr.hive.IndexRSerde'STORED AS INPUTFORMAT 'io.indexr.hive.IndexRInputFormat' OUTPUTFORMAT 'io.indexr.hive.IndexROutputFormat';测试方法分别测试在2亿和10亿的数据量下，indexR与parquet在ad-hoc场景下的性能差异，使用apache drill 1.10作为上层计算引擎测试用例场景用例sql用例即席分析_1个维、1个指标select sum(a.F7) as F70, SUBSTR(a.XZQH, 1, 2) as XZQH0 from indexr.`isdzd_b0_10y_r` awhere (a.HYDM IS NOT NULL)group by SUBSTR(a.XZQH, 1, 2) order by true limit 600;即席分析_ 2个维、5个指标select sum(a.F7) as F70,sum(a.F8) as F80,sum(a.F9) asF90,sum(a.F12) as F120,sum(a.F13) as F130, SUBSTR(a.XZQH,1,2) as XZQH0, SUBSTR(a.DJZCLX,1,1) as DJZCLX0from indexr.`isdzd_b0_10y_r` awhere (a.HYDM IS NOT NULL)group by SUBSTR(a.XZQH,1,2), SUBSTR(a.DJZCLX,1,1) order by true limit 600;一般报表_1个维，5select count(a.NSRSBH) as C2,sum(a.F15) as D2,sum(a.F25) as E2,sum(a.F30) as F2,sum(a.F33) as G2,sum(a.F10) as H2, SUBSTR(a.XZQH, 1, 2) as B2from indexr.`isdzd_b0_10y_r` a个指标where ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by SUBSTR(a.XZQH, 1, 2)order by sum(a.F10) desc nulls last limit 1000;一般报表_1个维，20个指标select C2 as C2,D2 as D2,E2 as E2,F2 as F2,G2 as G2,H2 as H2,I2 as I2,J2 as J2,K2 as K2,L2 as L2,M2 as M2,N2 as N2,O2 as O2,P2 asP2,Q2 as Q2,R2 as R2,S2 as S2,T2 as T2,U2 as U2,V2 as V2,B2 asB2,B2_sortn as B2_sortnfrom (select a.* from (select sum(a.F15) as C2,sum(a.F25) as D2,sum(a.F30) asE2,sum(a.F33) as F2,sum(a.F10) as G2,sum(a.F37) as H2,sum(a.F42) as I2,sum(a.F48) as J2,sum(a.F49) as K2,sum(a.F50) as L2,sum(a.F52) as M2,sum(a.F53) as N2,sum(a.F54) as O2,sum(a.F55) asP2,sum(a.F56) as Q2,sum(a.F57) as R2,sum(a.F59) as S2,sum(a.F60) as T2,sum(a.F60) as U2,sum(a.F58) as V2, SUBSTR(a.XZQH, 1, 2) as B2,sum(a.F10) as B2_sortnfrom indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by SUBSTR(a.XZQH, 1, 2) order by sum(a.F10)) a) b limit 40;多级浮动_ 2个维，5个指标select C3 as C3,D3 as D3,E3 as E3,F3 as F3,G3 as G3,C2 as C2,D2 as D2,E2 as E2,F2 as F2,G2 as G2,B3 as B3,B2 as B2,B2_sortn asB2_sortnfrom (select a.C3 as C3,a.D3 as D3,a.E3 as E3,a.F3 as F3,a.G3 as G3,b.C2 as C2,b.D2 as D2,b.E2 as E2,b.F2 as F2,b.G2 as G2,a.B3 as B3,a.B2 as B2,row_number() over (partition by a.B3 order by b.G2 desc nulls last) as B2_sortnfrom (select sum(a.F15) as C3,sum(a.F25) as D3,sum(a.F30) asE3,sum(a.F33) as F3,sum(a.F10) as G3, SUBSTR(a.DJZCLX, 1, 1) as B3, SUBSTR(a.XZQH, 1, 2) as B2from indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by SUBSTR(a.XZQH, 1, 2), SUBSTR(a.DJZCLX, 1, 1)) aleft join (select sum(a.F15) as C2,sum(a.F25) as D2,sum(a.F30) asE2,sum(a.F33) as F2,sum(a.F10) as G2, SUBSTR(a.XZQH, 1, 2) as B2 from indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by SUBSTR(a.XZQH, 1, 2)) bon (a.B2=b.B2)) awhere (B2_sortn<=40)order by B3 nulls first,B2_sortn nulls first limit 1000;多级浮动_ 2个维，20个指标select C3 as C3,D3 as D3,E3 as E3,F3 as F3,G3 as G3,H3 as H3,I3 as I3,J3 as J3,K3 as K3,L3 as L3,M3 as M3,N3 as N3,O3 as O3,P3 asP3,Q3 as Q3,R3 as R3,S3 as S3,T3 as T3,U3 as U3,V3 as V3,C2 asC2,D2 as D2,E2 as E2,F2 as F2,G2 as G2,H2 as H2,I2 as I2,J2 asJ2,K2 as K2,L2 as L2,M2 as M2,N2 as N2,O2 as O2,P2 as P2,Q2 asQ2,R2 as R2,S2 as S2,T2 as T2,U2 as U2,V2 as V2,B3 as B3,B2 asB2,B2_sortn as B2_sortnfrom (select a.C3 as C3,a.D3 as D3,a.E3 as E3,a.F3 as F3,a.G3 as G3,a.H3 as H3,a.I3 as I3,a.J3 as J3,a.K3 as K3,a.L3 as L3,a.M3 as M3,a.N3 as N3,a.O3 as O3,a.P3 as P3,a.Q3 as Q3,a.R3 as R3,a.S3 as S3,a.T3 as T3,a.U3 as U3,a.V3 as V3,b.C2 as C2,b.D2 as D2,b.E2 as E2,b.F2 as F2,b.G2 as G2,b.H2 as H2,b.I2 as I2,b.J2 as J2,b.K2 as K2,b.L2 asL2,b.M2 as M2,b.N2 as N2,b.O2 as O2,b.P2 as P2,b.Q2 as Q2,b.R2 as R2,b.S2 as S2,b.T2 as T2,b.U2 as U2,b.V2 as V2,a.B3 as B3,a.B2 asB2,row_number() over (partition by a.B3 order by b.G2 desc nulls last) as B2_sortnfrom (select sum(a.F15) as C3,sum(a.F25) as D3,sum(a.F30) asE3,sum(a.F33) as F3,sum(a.F10) as G3,sum(a.F37) as H3,sum(a.F42) as I3,sum(a.F48) as J3,sum(a.F49) as K3,sum(a.F50) as L3,sum(a.F52) as M3,sum(a.F53) as N3,sum(a.F54) as O3,sum(a.F55) asP3,sum(a.F56) as Q3,sum(a.F57) as R3,sum(a.F59) as S3,sum(a.F60) as T3,sum(a.F60) as U3,sum(a.F58) as V3, SUBSTR(a.DJZCLX, 1, 1) as B3, SUBSTR(a.XZQH, 1, 2) as B2from indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by SUBSTR(a.XZQH, 1, 2), SUBSTR(a.DJZCLX, 1, 1)) aleft join (select sum(a.F15) as C2,sum(a.F25) as D2,sum(a.F30) asE2,sum(a.F33) as F2,sum(a.F10) as G2,sum(a.F37) as H2,sum(a.F42) as I2,sum(a.F48) as J2,sum(a.F49) as K2,sum(a.F50) as L2,sum(a.F52) as M2,sum(a.F53) as N2,sum(a.F54) as O2,sum(a.F55) asP2,sum(a.F56) as Q2,sum(a.F57) as R2,sum(a.F59) as S2,sum(a.F60) as T2,sum(a.F60) as U2,sum(a.F58) as V2, SUBSTR(a.XZQH, 1, 2) as B2from indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by SUBSTR(a.XZQH, 1, 2)) bon (a.B2=b.B2)) awhere (B2_sortn<=40)order by B3 nulls first,B2_sortn nulls first limit 1000;图表展现_单图单表select C2 as C2,D2 as D2,B2 as B2,B2_sortn as B2_sortnfrom (select sum(a.F15) as C2,sum(a.F33) as D2, SUBSTR(a.XZQH, 1, 2) as B2,sum(a.F33) as B2_sortnfrom indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by SUBSTR(a.XZQH, 1, 2) order by sum(a.f33) desc nulls last) a order by B2_sortn nulls first limit 1000;图表展现_双图双表select * from (select C2 as C2,D2 as D2,B2 as B2,B2_sortn as B2_sortnfrom (select * from (select sum(a.F15) as C2,sum(a.F33) as D2, SUBSTR(a.HYDM, 1, 1) as B2,sum(a.F33) as B2_sortnfrom indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1))group by SUBSTR(a.HYDM, 1, 1) order by sum(a.F33) desc nulls last) a limit 40) border by B2_sortn nulls first limit 1000 ) aunion allselect * from (select C2 as C2,D2 as D2,B2 as B2,B2_sortn as B2_sortnfrom (select * from (select sum(a.F15) as C2,sum(a.F33) as D2, SUBSTR(a.XZQH, 1, 2) as B2,sum(a.F33) as B2_sortnfrom indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by SUBSTR(a.XZQH, 1, 2) order by sum(a.F33)) a limit 40) b order by B2_sortn nulls first limit 1000) b;统计报表下钻_折叠展开select * from (select sum(a.F15) as B2,sum(a.F25) as C2,sum(a.F30) asD2,sum(a.F33) as E2,sum(a.F10) as F2,sum(a.F37) as G2,sum(a.F42) as H2,sum(a.F48) as I2,sum(a.F49) as J2,sum(a.F50) asK2,sum(a.F52) as L2,sum(a.F53) as M2,sum(a.F54) as N2,sum(a.F55) as O2,sum(a.F56) as P2,sum(a.F57) as Q2,sum(a.F59) asR2,sum(a.F60) as S2,sum(a.F60) as T2,sum(a.F58) as U2,'000000' as A2from indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) order by true limit 1000) aunion allselect * from (select sum(a.F15) as B2,sum(a.F25) as C2,sum(a.F30) asD2,sum(a.F33) as E2,sum(a.F10) as F2,sum(a.F37) as G2,sum(a.F42) as H2,sum(a.F48) as I2,sum(a.F49) as J2,sum(a.F50) asK2,sum(a.F52) as L2,sum(a.F53) as M2,sum(a.F54) as N2,sum(a.F55) as O2,sum(a.F56) as P2,sum(a.F57) as Q2,sum(a.F59) asR2,sum(a.F60) as S2,sum(a.F60) as T2,sum(a.F58) as U2,SUBSTR(a.XZQH, 1, 2) as A2from indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1) AND (2>1))group by SUBSTR(a.XZQH, 1, 2) order by true limit 1000 ) b;统计报表下钻_跳转select sum(a.F15) as C2,sum(a.F25) as D2,sum(a.F30) asE2,sum(a.F33) as F2,sum(a.F10) as G2,sum(a.F37) as H2,sum(a.F42) as I2,a.DJZCLX as B2,max(b.caption_) as mc_B20from indexr.`isdzd_b0_10y_r` aleft join indexr.`dim_2014nsszld_nsrdjzclx` bon (a.DJZCLX=b.id_)where ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1)) group by a.DJZCLXorder by sum(a.F33) desc nulls last limit 1000;图表联动select sum(a.F30) as B3,sum(a.F80) as C3,sum(a.F102) as D3, SUBSTR(a.XZQH, 1, 2) as A3from indexr.`isdzd_b0_10y_r` awhere ((a.HYDM IS NOT NULL) AND (a.BBQ_ like '2014%') AND (2>1))group by SUBSTR(a.XZQH, 1, 2) order by true limit 1000; 测试结果场景用例 / drill 计算框架2亿10亿indexR Parquet indexR Parquet即席分析_1个维、1个指标7.5s 9.1s 36.2s 43.1s即席分析_ 2个维、5个指标38.4s 21.1s 98.8s 89.4s一般报表_1个维，5个指标10.2s 16.9s 42.9s 97.5s一般报表_1个维，20个指标 4.9s 26.6s 21.3s 140.8s多级浮动_ 2个维，5个指标 4.0s 17.0s 15.5s 74.2s多级浮动_ 2个维，20个指标 13.9s 36.1s 35.3s 172.6s图表展现_单图单表 2.1s 5.9s 9.8s 20.2s图表展现_双图双表 2.3s 6.3s 10.2s 27.5s统计报表下钻 _折叠展开8.3s 32.7s 27.7s 159.2s统计报表下钻 _跳转 2.3s 7.9s 12.3s 41.8s图表联动 2.1s 7.9s 8.0s 34.8s测试结论1.全表扫描方式下，indexR格式与Parquet格式基本相当，个别场景略慢于Parquet，这个结果和carbondata vs parquet的测试结果相似，carbondata格式的全扫描也是略慢于parquet，推测是因为这种带索引的列存储格式比parquet格式拥有更多的元信息，增加了路由的时间。