hive数据倾斜(大表join大表)

业务背景

用户轨迹工程的性能瓶颈一直是etract_track_info，其中耗时大户主要在于trackinfo与pm_info进行左关联的环节，trackinfo与pm_info两张表均为GB级别，左关联代码块如下：

[SQL] 纯文本查看 复制代码

?

1

2

3

fromtrackinfo a

leftouterjoin pm_info b

on(a.ext_field7 = b.id)

使用以上代码块需要耗时1.5小时。

优化流程
第一次优化

考虑到pm_info表的id是bigint类型，trackinfo表的ext_field7是string类型，其关联时数据类型不一致，默认的hash操作会按bigint型的id进行分配，这样会导致所有string类型的ext_field7集中到一个reduce里面，因此，改为如下：

[SQL] 纯文本查看 复制代码

?

1

2

3

fromtrackinfo a

leftouterjoin pm_info b

on(cast(a.ext_field7asbigint) = b.id)

改动为上面代码后，效果仍然不理想，耗时为1.5小时。

第二次优化

考虑到trackinfo表的ext_field7字段缺失率很高（为空、字段长度为零、字段填充了非整数）情况，做进行左关联时空字段的关联操作实际上没有意义，因此，如果左表关联字段ext_field7为无效字段，则不需要关联，因此，改为如下：

[Bash shell] 纯文本查看 复制代码

?

1

2

3

4

5

6

from trackinfo a

left outer joinpm_info b

on (a.ext_field7 is not null

and length(a.ext_field7) > 0

and a.ext_field7 rlike '^[0-9]+$'

and a.ext_field7 = b.id)

上面代码块的作用是，如果左表关联字段ext_field7为无效字段时（为空、字段长度为零、字段填充了非整数），不去关联右表，由于空字段左关联以后取到的右表字段仍然为null，所以不会影响结果。 
改动为上面代码后，效果仍然不理想，耗时为50分钟。

第三次优化

想了很久，第二次优化效果效果不理想的原因，其实是在左关联中，虽然设置了左表关联字段为空不去关联右表，但是这样做，左表中未关联的记录（ext_field7为空）将会全部聚集在一个reduce中进行处理，体现为reduce进度长时间处在99%。 
换一种思路，解决办法的突破点就在于如何把左表的未关联记录的key尽可能打散，因此可以这么做：若左表关联字段无效（为空、字段长度为零、字段填充了非整数），则在关联前将左表关联字段设置为一个随机数，再去关联右表，这么做的目的是即使是左表的未关联记录，它的key也分布得十分均匀

[SQL] 纯文本查看 复制代码

?

01

02

03

04

05

06

07

08

09

10

11

12

fromtrackinfo a

leftouterjoin pm_info b

on(

    casewhen(a.ext_field7 isnotnull

        andlength(a.ext_field7) > 0

        anda.ext_field7 rlike '^[0-9]+$')

    then

        cast(a.ext_field7asbigint)

    else

        cast(ceiling(rand() * -65535) asbigint)

    end= b.id

)

第三次改动后，耗时从50分钟降为了1分钟32秒，效果显著！