spark中多表连接优化实例
来源:互联网 发布:nginx 新增模块 编辑:程序博客网 时间:2024/06/16 08:07
环境信息:
hive1.2.1
spark1.6.1
hadoop2.6.0-cdh5.4.2
memory:1918752, vCores:506
表结构:
原hive sql:
(为求简便,所有字段以XX代替,最终有90个字段)
INSERT OVERWRITE TABLE H_DW_ORDER SELECT A.PC_ORDER_ID, A.XX90 FROM TST_PC_ORDER A LEFT JOIN (SELECT PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID ,XX ,ROW_NUMBER() OVER(PARTITION BY PC_ORDER_ID ORDER BY UPDATED_DATE DESC) AS ROWNO FROM TST_ORDER_RISK) K WHERE ROWNO = 1) B ON A.PC_ORDER_ID = B.PC_ORDER_ID LEFT JOIN TST_ORDER_VEHICLE C ON B.CLIENT_ID = C.PC_ORDER_VEHICLE_ID LEFT JOIN (SELECT T.* FROM TEMP_01_PC_ORDER T WHERE FLAG_L = 1) D ON A.PC_ORDER_ID = D.PC_ORDER_ID LEFT JOIN (SELECT T1.* FROM TEMP_01_PC_ORDER T1 WHERE FLAG_F = 1) F ON A.PC_ORDER_ID = F.PC_ORDER_ID LEFT JOIN (SELECT H.ORDER_ID, XX FROM (SELECT E.XX ,E.ORDER_ID ,ROW_NUMBER() OVER(PARTITION BY E.ORDER_ID ORDER BY E.DATE_UPDATED DESC) AS ROW_NO FROM TST_ORDER_ASSISTE) H WHERE H.ROW_NO = 1) G ON A.ORDER_ID = G.ORDER_ID;
原sql,hive运行,具体现象为几个stage最后有一个reduce时间特别长,以上表关联基本是主键关联或做除重,不应存在数据倾斜;
优化开启数据倾斜优化,增加reduce数量
set hive.optimize.skewjoin=true;
set hive.exec.reducers.bytes.per.reducer=400000000;
没有效果,执行过程中现象一样。而且报内存超限错误(143);
继续增大container内存设置:set mapreduce.reduce.memory.mb=6144;
中间运行不报错,但是执行时间很久;最后报错:[Error 30001]: StatsPublisher cannot be initialized·····
改用spark运行:使用belline命令: /appcom/spark/bin/beeline -u jdbc:hive2://10.xx.xx.xxx:88888 -n user0 --color=true --showHeader=true --verbose=true
直接运行原sql耗时2.3h;大量时间消耗在TST_PC_ORDER与各个表的关联,hdfs读写不断增加,60G左右,非常耗时
优化1:由于TST_ORDER_VEHICLE 是与 TST_ORDER_RISK 关联,可以将两者先做关联,之后与主表关联;
INSERT OVERWRITE TABLE H_DW_ORDER SELECT A.PC_ORDER_ID, A.XX90 FROM TST_PC_ORDER A LEFT JOIN (SELECT PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID ,XX ,ROW_NUMBER() OVER(PARTITION BY PC_ORDER_ID ORDER BY UPDATED_DATE DESC) AS ROWNO FROM TST_ORDER_RISK) K WHERE ROWNO = 1) B LEFT JOIN TST_ORDER_VEHICLE C ON B.CLIENT_ID = C.PC_ORDER_VEHICLE_ID) M ON A.PC_ORDER_ID = M.PC_ORDER_ID LEFT JOIN (SELECT T.* FROM TEMP_01_PC_ORDER T WHERE FLAG_L = 1) D ON A.PC_ORDER_ID = D.PC_ORDER_ID LEFT JOIN (SELECT T1.* FROM TEMP_01_PC_ORDER T1 WHERE FLAG_F = 1) F ON A.PC_ORDER_ID = F.PC_ORDER_ID LEFT JOIN (SELECT H.ORDER_ID, XX FROM (SELECT E.XX ,E.ORDER_ID ,ROW_NUMBER() OVER(PARTITION BY E.ORDER_ID ORDER BY E.DATE_UPDATED DESC) AS ROW_NO FROM TST_ORDER_ASSISTE) H WHERE H.ROW_NO = 1) G ON A.ORDER_ID = G.ORDER_ID;
这样做之后发现,spark会多生成一个stage,将TST_ORDER_VEHICLE 与 TST_ORDER_RISK 的关联提前做掉;但是这个stage耗时2min;总体时间42min
优化2:
既然关键耗时在TST_PC_ORDER表的扫描上,可以让其他的stage先运行;修改如下
INSERT OVERWRITE TABLE H_DW_ORDER SELECT A.PC_ORDER_ID, A.XX90 FROM TST_PC_ORDER A LEFT JOIN (SELECT H.ORDER_ID, XX FROM (SELECT E.XX ,E.ORDER_ID ,ROW_NUMBER() OVER(PARTITION BY E.ORDER_ID ORDER BY E.DATE_UPDATED DESC) AS ROW_NO FROM TST_ORDER_ASSISTE) H WHERE H.ROW_NO = 1) G ON A.ORDER_ID = G.ORDER_ID LEFT JOIN (SELECT R.PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID FROM TST_PC_ORDER) R LEFT JOIN (SELECT PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID ,XX ,ROW_NUMBER() OVER(PARTITION BY PC_ORDER_ID ORDER BY UPDATED_DATE DESC) AS ROWNO FROM TST_ORDER_RISK) K WHERE ROWNO = 1) B LEFT JOIN TST_ORDER_VEHICLE C ON B.CLIENT_ID = C.PC_ORDER_VEHICLE_ID) M ON R.PC_ORDER_ID = M.PC_ORDER_ID LEFT JOIN (SELECT T.* FROM TEMP_01_PC_ORDER T WHERE FLAG_L = 1) D ON R.PC_ORDER_ID = D.PC_ORDER_ID LEFT JOIN (SELECT T1.* FROM TEMP_01_PC_ORDER T1 WHERE FLAG_F = 1) F ON R.PC_ORDER_ID = F.PC_ORDER_ID) P ON A.PC_ORDER_ID =P.PC_ORDER_ID
如此多扫描TST_PC_ORDER表一次,但因为只扫描一列,速度较快;但是受制于A表与G表关联时间较长,且整体P需要A与G的关联结果出来之后才能运行;总耗时38min;
优化3:如果能在TST_PC_ORDER扫描的时候将所有其他的关联做完就完美了;修改表连接顺序如下:
INSERT OVERWRITE TABLE H_DW_ORDER SELECT A.PC_ORDER_ID, A.XX90 FROM TST_PC_ORDER A LEFT JOIN (SELECT R.PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID ,ORDER_ID FROM TST_PC_ORDER) R LEFT JOIN (SELECT T.* FROM TEMP_01_PC_ORDER T WHERE FLAG_L = 1) D ON R.PC_ORDER_ID = D.PC_ORDER_ID LEFT JOIN (SELECT T1.* FROM TEMP_01_PC_ORDER T1 WHERE FLAG_F = 1) F ON R.PC_ORDER_ID = F.PC_ORDER_ID LEFT JOIN (SELECT PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID, XX FROM (SELECT PC_ORDER_ID ,XX ,ROW_NUMBER() OVER(PARTITION BY PC_ORDER_ID ORDER BY UPDATED_DATE DESC) AS ROWNO FROM TST_ORDER_RISK) K WHERE ROWNO = 1) B LEFT JOIN TST_ORDER_VEHICLE C ON B.CLIENT_ID = C.PC_ORDER_VEHICLE_ID) M ON R.PC_ORDER_ID = M.PC_ORDER_ID LEFT JOIN (SELECT H.ORDER_ID, XX FROM (SELECT E.XX ,E.ORDER_ID ,ROW_NUMBER() OVER(PARTITION BY E.ORDER_ID ORDER BY E.DATE_UPDATED DESC) AS ROW_NO FROM TST_ORDER_ASSISTE) H WHERE H.ROW_NO = 1) G ON R.ORDER_ID = G.ORDER_ID) P ON A.PC_ORDER_ID = P.PC_ORDER_ID
这样A表扫描需要大约12分钟,在扫描的同时,P中的关联差不多执行完成;总耗时25min;
综上,优化考虑以下几点:
1、不需要的字段不要查出来,尽量减小结果集的数据量;
2、能够并行执行的stage,尽量在写法上将逻辑隔离开来,降低耦合度,让代码充分并行执行。
3、根据执行的关键路径进行优化,修改表的关联顺序,缩短关键路径执行时间。
- spark中多表连接优化实例
- MySQL多表连接优化实例分析
- Spark实例
- Spark优化
- spark优化
- spark优化
- spark 优化
- Spark 优化
- spark优化
- Spark优化-优化原则
- Spark优化-优化数据结构
- 【Spark系列3】Spark优化
- Spark SQL通过JDBC连接MySQL读写数据实例(比较过时了)
- [spark优化]如何优化数据结构
- Spark Streaming实例分析
- Spark Streaming实例分析
- spark简单实例
- Spark使用实例
- WatchOS 2 problem:dyld: Library not loaded
- 64和10进制的相互转换-PHP实现
- String参数是传值还是传递引用的测试
- poj 3723 MST
- opencv-图片特征点提取
- spark中多表连接优化实例
- Java读取文件内容并转换为字符串
- hdu 1505 City Game dp
- js中的windows对象
- hive 记录帖 待整理
- 【Unity3D自学记录】鼠标拖拽物体移动
- JAVA中用三种方法将字符串持久化到文件中
- mysql中连接失败2003(Can't connect to server on 'localhost'(10038))错误解决办法
- 生成一维条码和遮蔽层