CarbonData在BI场景中的测试.pdf

《CarbonData在BI场景中的测试.pdf》由会员分享，可在线阅读，更多相关《CarbonData在BI场景中的测试.pdf（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、CarbonDataCarbonData 在在 BIBI 场景中的测试场景中的测试测试目的测试目的CarbonData 格式与 Parquet 格式在 BI 场景下的性能对比测试版本测试版本carbondata 1.1.0集群配置集群配置cpu：Intel(R)Core(TM)i7-4820K CPU 3.70GHz（1U*4Core）mem:64G节点个数：5测试素材测试素材使用某 BI 项目中的事实表作为测试表，建表 DDL 语句如下：create tableisdzd_b0_10y_c(userid_ string,btype_ string,shtag_ string,shtag2_

2、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,f19double,f20 double,f21 double,f22 double,f23 double,f24 double,f25 double,f26 double,f27 double,f

3、28 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,f48double,f49 double,f50 double,f51 double,f52 double,f53 double,f54 double,f55 d

4、ouble,f56 double,f57 double,f58 double,f59 double,f60 double,f61 double,f62 double,f63 double,f64double,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 doubl

5、e,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,f107double,f108 double,f109

6、 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)STORED BY carbondata测试方法测试方法分别测试在 2 亿和 10 亿的数据量下，CarbonData 与 parquet 格式在 ad-hoc 场景下的性能优劣。测试用例测试用例场景场景用例用例sqlsql

7、 用例用例即席分析select sum(a.F7)as F70,SUBSTR(a.XZQH,1,2)as XZQH0from_1 个isdzd_b0_10y_c awhere(a.HYDM IS NOT NULL)group by SUBSTR(a.XZQH,维、11,2)order by true limit 600;个指标即席select sum(a.F7)as F70,sum(a.F8)as F80,sum(a.F9)as F90,sum(a.F12)as分析_F120,sum(a.F13)as F130,SUBSTR(a.XZQH,1,2)as XZQH0,2 个SUBSTR(a.DJ

8、ZCLX,1,1)as DJZCLX0from ISDZD_B0_10Y_c awhere(a.HYDM维、5IS NOT NULL)group by SUBSTR(a.XZQH,1,2),SUBSTR(a.DJZCLX,1,1)order个指by true limit 600;标一般报表_1 个维，5个指标select count(a.NSRSBH)as C2,sum(a.F15)as D2,sum(a.F25)asE2,sum(a.F30)as F2,sum(a.F33)as G2,sum(a.F10)as H2,SUBSTR(a.XZQH,1,2)as B2from isdzd_b0_1

9、0y_c awhere(a.HYDM IS NOT NULL)AND(a.BBQ_ like 2014%)AND(21)group by SUBSTR(a.XZQH,1,2)order bysum(a.F10)desc nulls last limit 1000;select C2 as C2,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 as Q2,R2 asR2,S2 as S2,T2 as T2,U

10、2 as U2,V2 as V2,B2 as B2,B2_sortn as B2_sortnfrom(select sum(a.F15)as C2,sum(a.F25)as D2,sum(a.F30)as E2,sum(a.F33)asF2,sum(a.F10)as G2,sum(a.F37)as H2,sum(a.F42)as I2,sum(a.F48)asJ2,sum(a.F49)as K2,sum(a.F50)as L2,sum(a.F52)as M2,sum(a.F53)asN2,sum(a.F54)as O2,sum(a.F55)as P2,sum(a.F56)as Q2,sum(a

11、.F57)asR2,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,row_number()over(order by sum(a.F10)descnulls last)as B2_sortnfrom isdzd_b0_10y_c awhere(a.HYDM IS NOT NULL)AND(a.BBQ_ like 2014%)AND(21)group by SUBSTR(a.XZQH,1,2)awhere(B2_sortn1)group by SUBSTR(a.XZQ

12、H,1,2),个指SUBSTR(a.DJZCLX,1,1)aleft join(select sum(a.F15)as C2,sum(a.F25)as标D2,sum(a.F30)as E2,sum(a.F33)as F2,sum(a.F10)as G2,SUBSTR(a.XZQH,1,2)as B2from isdzd_b0_10y_c awhere(a.HYDM IS NOT NULL)AND(a.BBQ_ like 2014%)AND(21)group by SUBSTR(a.XZQH,1,2)bon(a.B2=b.B2)awhere(B2_sortn1)group by SUBSTR(a

13、.XZQH,1,2),SUBSTR(a.DJZCLX,1,1)aleft join(select sum(a.F15)as C2,sum(a.F25)asD2,sum(a.F30)as E2,sum(a.F33)as F2,sum(a.F10)as G2,sum(a.F37)asH2,sum(a.F42)as I2,sum(a.F48)as J2,sum(a.F49)as K2,sum(a.F50)asL2,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

14、,sum(a.F59)as S2,sum(a.F60)asT2,sum(a.F60)as U2,sum(a.F58)as V2,SUBSTR(a.XZQH,1,2)as B2fromisdzd_b0_10y_c awhere(a.HYDM IS NOT NULL)AND(a.BBQ_ like 2014%)AND(21)group by SUBSTR(a.XZQH,1,2)bon(a.B2=b.B2)awhere(B2_sortn1)group by SUBSTR(a.XZQH,1,2)awhere(B2_sortn1)group by SUBSTR(a.HYDM,1,1)awhere(B2_

15、sortn1)group by SUBSTR(a.XZQH,1,2)awhere(B2_sortn1)order by true limit下钻 _1000)aunion allselect*from(select sum(a.F15)as B2,sum(a.F25)as折叠C2,sum(a.F30)as D2,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

16、,sum(a.F54)as N2,sum(a.F55)asO2,sum(a.F56)as P2,sum(a.F57)as Q2,sum(a.F59)as R2,sum(a.F60)asS2,sum(a.F60)as T2,sum(a.F58)as U2,SUBSTR(a.XZQH,1,2)as A2fromisdzd_b0_10y_c awhere(a.HYDM IS NOT NULL)AND(a.BBQ_ like 2014%)AND(21)AND(21)group by SUBSTR(a.XZQH,1,2)order by true limit1000)b;select sum(a.F15

17、)as C2,sum(a.F25)as D2,sum(a.F30)as E2,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 isdzd_b0_10y_c aleft join下钻 _DIM_2014NSSZLD_NSRDJZCLX bon(a.DJZCLX=b.id_)where(a.HYDM IS跳转NOT NULL)AND(a.BBQ_ like 2014%)AND(21)group by a.DJZCLXorderb

18、y 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 isdzd_b0_10y_c awhere(a.HYDM IS NOT联动NULL)AND(a.BBQ_ like 2014%)AND(21)group by SUBSTR(a.XZQH,1,2)order by true limit 1000;测试结果测试结果场景用例场景用例即席分析_1 个维、1 个指标一般报表_1 个维，5 个指标2 2 亿亿

19、19.3s10.9s17.3s17.2s13s19.9s16.5s24.8s15.8s14.5s46.4s15.6s10.8s27.8s45.8s13.3s46s17.1s61.7s22.2s17.6s42.2s21.9s17.8s1010 亿亿25.1s44.8s42.9s50s48s76.7s18.9s29.6s67.9s33.3s42.7sCarbonDataParquetCarbonDataParquet即席分析_ 2 个维、5 个指标18.3s一般报表_1 个维，20 个指标19.5s多级浮动_ 2 个维，5 个指标14.1s多级浮动_ 2 个维，20 个指标 22.7s图表展现_单

20、图单表图表展现_双图双表统计报表下钻 _折叠展开统计报表下钻 _跳转图表联动14.9s12.5s24.6s17.1s12.2s测试结论测试结论1.CarbonData 与 Parquet 在全表扫描方式下的性能基本相当，没有明显差别。（参考：用例 1,2）2.对于含过滤条件的筛选查询，当数据规模到一定量级时，CD 较之Parquet 有较明显的优势，主要是 CarbonData 的多级索引发挥了作用，而且数据量越大，性能差别越明显，从本次的测试用例可以看出，2 亿行级的数据量两者差别很小，10 亿行级的数据量两者的性能差距较明显。（参考：用例 3,5,11）3.CarbonData 很好地利用

21、了索引扫描，使得查询耗时的增加幅度明显低于数据量的膨胀速度，从测试结果我们不难发现，当数据量从 2 亿增长到10 亿时，parquet 格式的查询响应时间也成倍增加。而 CarbonData 格式的增加幅度相对平稳得多，这得益于索引扫描替代了全表扫描。（参考：用例 3,5,10,11）4.含过滤条件的查询，若使用开窗函数，CarbonData 的性能优势不复存在，笔者推测是因为开窗函数通常需要走全表扫描，导致 CarbonData 的索引无法被利用（参考：用例 4,6）5.CarbonData 上的 union all 操作比 parquet 要快，原因还在分析中。（参考：用例 8,9）6.从实测结果来看，CarbonData 存储格式适合 BI 的应用场景