CarbonData在BI场景中的测试.doc

CarbonData在BI场景中的测试测试目的CarbonData格式与Parquet格式在BI场景下的性能对比测试版本carbondata1.1.0集群配置cpu：Intel(R)Core(TM)i7-4820KCPU3.70GHz（1U*4Core）mem:64G节点个数：5测试素材使用某BI项目中的事实表作为测试表，建表DDL语句如下：createtableisdzd_b0_10y_c(userid_string,btype_string,shtag_string,shtag2_string,option_string,f2double,f3double,f4double,f5double,f6double,f7double,f8double,f9double,f10double,f11double,f12double,f13double,f14double,f15double,f16double,f17double,f18double,f19double,f20double,f21double,f22double,f23double,f24double,f25double,f26double,f27double,f28double,f29double,f30double,f31double,f32double,f33double,f34double,f35double,f36double,f37double,f38double,f39double,f40double,f41double,f42double,f43double,f44double,f45double,f46double,f47double,f48double,f49double,f50double,f51double,f52double,f53double,f54double,f55double,f56double,f57double,f58double,f59double,f60double,f61double,f62double,f63double,f64double,f65double,f66double,f67double,f68double,f69double,f70double,f71double,f72double,f73double,f74double,f75double,f76double,f77double,f78double,f79double,f80double,f81double,f82double,f83double,f84double,f85double,f86double,f87double,f88double,f89double,f90double,f91double,f92double,f93double,f94double,f95double,f96double,f97double,f98double,f99double,f100double,f101double,f102double,f103double,f104double,f105double,f106double,f107double,f108double,f109double,f110double,f111double,f112double,f113double,f114double,nsrsbhstring,nsrmcstring,hydmstring,djzclxstring,xzqhstring,zzsjnfsstring,zzsyhzcstring,upid_string,bbq_string)STOREDBYcarbondata测试方法分别测试在2亿和10亿的数据量下，CarbonData与parquet格式在ad-hoc场景下的性能优劣。测试用例场景用例sql用例即席分析_1个维、1个指标selectsum(a.F7)asF70,SUBSTR(a.XZQH,1,2)asXZQH0fromisdzd_b0_10y_cawhere(a.HYDMISNOTNULL)groupbySUBSTR(a.XZQH,1,2)orderbytruelimit600;即席分析_2个维、5个指标selectsum(a.F7)asF70,sum(a.F8)asF80,sum(a.F9)asF90,sum(a.F12)asF120,sum(a.F13)asF130,SUBSTR(a.XZQH,1,2)asXZQH0,SUBSTR(a.DJZCLX,1,1)asDJZCLX0fromISDZD_B0_10Y_cawhere(a.HYDMISNOTNULL)groupbySUBSTR(a.XZQH,1,2),SUBSTR(a.DJZCLX,1,1)orderbytruelimit600;一般报表_1个维，5个指标selectcount(a.NSRSBH)asC2,sum(a.F15)asD2,sum(a.F25)asE2,sum(a.F30)asF2,sum(a.F33)asG2,sum(a.F10)asH2,SUBSTR(a.XZQH,1,2)asB2fromisdzd_b0_10y_cawhere(a.HYDMISNOTNULL)AND(a.BBQ_like2014%)AND(21)groupbySUBSTR(a.XZQH,1,2)orderbysum(a.F10)descnullslastlimit1000;一般报表_1个维，20个指标selectC2asC2,D2asD2,E2asE2,F2asF2,G2asG2,H2asH2,I2asI2,J2asJ2,K2asK2,L2asL2,M2asM2,N2asN2,O2asO2,P2asP2,Q2asQ2,R2asR2,S2asS2,T2asT2,U2asU2,V2asV2,B2asB2,B2_sortnasB2_sortnfrom(selectsum(a.F15)asC2,sum(a.F25)asD2,sum(a.F30)asE2,sum(a.F33)asF2,sum(a.F10)asG2,sum(a.F37)asH2,sum(a.F42)asI2,sum(a.F48)asJ2,sum(a.F49)asK2,sum(a.F50)asL2,sum(a.F52)asM2,sum(a.F53)asN2,sum(a.F54)asO2,sum(a.F55)asP2,sum(a.F56)asQ2,sum(a.F57)asR2,sum(a.F59)asS2,sum(a.F60)asT2,sum(a.F60)asU2,sum(a.F58)asV2,SUBSTR(a.XZQH,1,2)asB2,row_number()over(orderbysum(a.F10)descnullslast)asB2_sortnfromisdzd_b0_10y_cawhere(a.HYDMISNOTNULL)AND(a.BBQ_like2014%)AND(21)groupbySUBSTR(a.XZQH,1,2)awhere(B2_sortn1)groupbySUBSTR(a.XZQH,1,2),SUBSTR(a.DJZCLX,1,1)aleftjoin(selectsum(a.F15)asC2,sum(a.F25)asD2,sum(a.F30)asE2,sum(a.F33)asF2,sum(a.F10)asG2,SUBSTR(a.XZQH,1,2)asB2fromisdzd_b0_10y_cawhere(a.HYDMISNOTNULL)AND(a.BBQ_like2014%)AND(21)groupbySUBSTR(a.XZQH,1,2)bon(a.B2=b.B2)awhere(B2_sortn1)groupbySUBSTR(a.XZQH,1,2),SUBSTR(a.DJZCLX,1,1)aleftjoin(selectsum(a.F15)asC2,sum(a.F25)asD2,sum(a.F30)asE2,sum(a.F33)asF2,sum(a.F10)asG2,sum(a.F37)asH2,sum(a.F42)asI2,sum(a.F48)asJ2,sum(a.F49)asK2,sum(a.F50)asL2,sum(a.F52)asM2,sum(a.F53)asN2,sum(a.F54)asO2,sum(a.F55)asP2,sum(a.F56)asQ2,sum(a.F57)asR2,sum(a.F59)asS2,sum(a.F60)asT2,sum(a.F60)asU2,sum(a.F58)asV2,SUBSTR(a.XZQH,1,2)asB2fromisdzd_b0_10y_cawhere(a.HYDMISNOTNULL)AND(a.BBQ_like2014%)AND(21)groupbySUBSTR(a.XZQH,1,2)bon(a.B2=b.B2)awhere(B2_sortn1)groupbySUBSTR(a.XZQH,1,2)awhere(B2_sortn1)groupbySUBSTR(a.HYDM,1,1)awhere(B2_sortn1)groupbySUBSTR(a.XZQH,1,2)awhere(B2_sortn1)orderbytruelimit1000)aunionallselect*from(selectsum(a.F15)asB2,sum(a.F25)asC2,sum(a.F30)asD2,sum(a.F33)asE2,sum(a.F10)asF2,sum(a.F37)asG2,sum(a.F42)asH2,sum(a.F48)asI2,sum(a.F49)asJ2,sum(a.F50)asK2,sum(a.F52)asL2,sum(a.F53)asM2,sum(a.F54)asN2,sum(a.F55)asO2,sum(a.F56)asP2,sum(a.F57)asQ2,sum(a.F59)asR2,sum(a.F60)asS2,sum(a.F60)asT2,sum(a.F58)asU2,SUBSTR(a.XZQH,1,2)asA2fromisdzd_b0_10y_cawhere(a.HYDMISNOTNULL)AND(a.BBQ_like2014%)AND(21)AND(21)groupbySUBSTR(a.XZQH,1,2)orderbytruelimit1000)b;统计报表下钻_跳转selectsum(a.F15)asC2,sum(a.F25)asD2,sum(a.F30)asE2,sum(a.F33)asF2,sum(a.F10)asG2,sum(a.F37)asH2,sum(a.F42)asI2,a.DJZCLXasB2,max(b.caption_)asmc_B20fromisdzd_b0_10y_caleftjoinDIM_2014NSSZLD_NSRDJZCLXbon(a.DJZCLX=b.id_)where(a.HYDMISNOTNULL)AND(a.BBQ_like2014%)AND(21)groupbya.DJZCLXorderbysum(a.F33)descnullslastlimit1000;图表联动selectsum(a.F30)asB3,sum(a.F80)asC3,sum(a.F102)asD3,SUBSTR(a.XZQH,1,2)asA3fromisdzd_b0_10y_cawhere(a.HYDMISNOTNULL)AND(a.BBQ_like2014%)AND(21)groupbySUBSTR(a.XZQH,1,2)orderbytruelimit1000;测试结果场景用例2亿10亿CarbonDataParquetCarbonDataParquet即席分析_1个维、1个指标19.3s17.3s27.8s25.1s即席分析_2个维、5个指标18.3s17.2s45.8s44.8s一般报表_1个维，5个指标10.9s13s13.3s42.9s一般报表_1个维，20个指标19.5s19.9s46s50s多级浮动_2个维，5个指标14.1s16.5s17.1s48s多级浮动_2个维，20个指标22.7s24.8s61.7s76.7s图表展现_单图单表14.9s15.8s22.2s18.9s图表展现_双图双表12.5s14.5s17.6s29.6s统计报表下钻_折叠展开24.6s46.4s42.2s67.9s统计报表下钻_跳转17.1s15.6s21.9s33.3s图表联动12.2s10.8s17.8s42.7s测试结论1.CarbonData与Parquet在全表扫描方式下的性能基本相当，没有明显差别。（参考：用例1,2）2.对于含过滤条件的筛选查询，当数据规模到一定量级时，CD较之Parquet有较明显的优势，主要是CarbonData的多级索引发挥了作用，而且数据量越大，性能差别越明显，从本次的测试用例可以看出，2亿行级的数据量两者差别很小，10亿行级的数据量两者的性能差距较明显。（参考：用例3,5,11）3.CarbonData很好地利用了索引扫描，使得查询耗时的增加幅度明显低于数