Spark性能调优案例-多表join优化，减少shuffle

背景

A任务在凌晨1点到3点，平均耗时1h,且是核心公共任务，急需优化。
整体逻辑示意图：

// 从tableA读取一次数据，放到临时表t1
DROP TABLE IF EXISTS temp.tmp_xx_$date_1;
CREATE TABLE IF NOT EXISTS temp.tmp_xxx_$date_1
as 
select 
xxx
from  tableA
where xxxx;

// 从临时表t1读取和转换数据，得临时表t2
DROP TABLE IF EXISTS temp.tmp_xx_$date_2;
CREATE TABLE IF NOT EXISTS temp.tmp_xxx_$date_2
as 
SELECT
    device_id
   ,c2
FROM
(
    SELECT
        device_id
       ,c2
       ,ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY time DESC) AS num
    FROM
    (
    	   select 
		 device_id
          ,c2
       from  temp.tmp_xx_$date_1
       where !isempty(c2) 
    )t
)t
WHERE t.num=1;

// 从临时表t1读取和转换数据，得临时表t3
DROP TABLE IF EXISTS temp.tmp_xx_$date_3;
CREATE TABLE IF NOT EXISTS temp.tmp_xxx_$date_3
as 
SELECT
    device_id
   ,c3
FROM
(
    SELECT
        device_id
       ,c3
       ,ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY time DESC) AS num
    FROM
    (
    	   select 
		 device_id
          ,c3
       from  temp.tmp_xx_$date_1
       where !isempty(c3) 
    )t
)t
WHERE t.num=1;
// 从临时表t1读取和转换数据，得临时表t4
DROP TABLE IF EXISTS temp.tmp_xx_$date_4;
CREATE TABLE IF NOT EXISTS temp.tmp_xxx_$date_4
as 
SELECT
    device_id
   ,c4
FROM
(
    SELECT
        device_id
       ,c6
       ,ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY time DESC) AS num
    FROM
    (
	   select 
		 device_id
          ,c4
       from  temp.tmp_xx_$date_1
       where !isempty(c4) 
    )t
)t
WHERE t.num=1
...
// 从临时表t1读取和转换数据，得临时表t7
DROP TABLE IF EXISTS temp.tmp_xx_$date_7;
CREATE TABLE IF NOT EXISTS temp.tmp_xxx_$date_7
as 
SELECT
    device_id
   ,c7
FROM
(
    SELECT
        device_id
       ,c7
       ,ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY time DESC) AS num
    FROM
    (
	   select 
		 device_id
          ,c7
       from  temp.tmp_xx_$date_1
       where !isempty(c7) 
    )t
)t
WHERE t.num=1

//t2关联t3-t7结果写入最终结果表
INSERT OVERWRITE TABLE biads.xxxx
PARTITION (pt_d='$date')
select 
xxx
from  temp.tmp_xx_$date_2
left join  temp.tmp_xx_$date_3
left join  temp.tmp_xx_$date_4
...
left join  temp.tmp_xx_$date_7


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

排查思路

2.1 job,stage耗时分布
问题1： job太多 , 串行时间累加，导致整体耗时长
2.2 sql 执行dag图
问题2：存在8个表join，shuffle步骤多；
2.3 代码review
问题3：多表join时，存在大量重复逻辑。

解决方法

问题1: 保留必须的job, 合并重复逻辑，消除不必要的job(即临时表创建，一次临时表创建，代表一个job,且只能串行执行）
问题2，3：使用 group by + max (split(concat(xxx))) 替代重复row_number逻辑

SELECT  device_id
           ,SPLIT(MAX(CONCAT(time,'\001',c1)),'\001')[1]     AS c1
           ,SPLIT(MAX(CONCAT(time,'\001',c2)),'\001')[1]        AS c2
           ,SPLIT(MAX(CONCAT(time,'\001',c3)),'\001')[1]         AS c3
           ,SPLIT(MAX(CONCAT(time,'\001',c4)),'\001')[1]           AS c4
           ,SPLIT(MAX(CONCAT(time,'\001',c5)),'\001')[1]                c5 
           ,SPLIT(MAX(CONCAT(time,'\001',c6)),'\001')[1]          AS c6
           ,SPLIT(MAX(CONCAT(time,'\001',c7)),'\001')[1]     AS c7
    FROM
    (
        SELECT  device_id
               ,c1
               ,c2
               ,c3
               ,c4
               ,c5
               ,c6
               ,c7
        FROM temp.tmp_xx_$date_1
        where !isempty(c1) 
    ) t
    GROUP BY  device_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

注意使用CONCAT而不是 CONCAT_ws, 因为要剔除掉c1-c7为空的情况，如果使用CONCAT_ws，恰好最近时间的c1-c7为空，也会保留空值，则不是我们想要的，而CONCAT遇到null,则整体结果直接Null，排序到最后, 从而实现剔除效果

测试验证count数，字段级count数

由于我们重构的复杂逻辑，所以必须验证重构后的行数，按特定维度聚合后行数，前后是否一致，这里我们以设备数为统计指标，结果表明 ：
（1） 前后设备数一致
（2）按重要维度c1聚合后，设备数基本一致，存在一两百左右的误差，在可接受范围内。

优化后效果

消除了问题1，2，3，运行时间从1h降低到了10min.