Hive从入门到放弃——Hive优化实践（十六）

背 景

  Hive 的优化分为join相关的优化和join无关的优化，从项目实际来说, join 相关的优化占了 Hive 优化的大部分内容，而 join 相关的优化又分为 mapjoin 可以解决的 join 优化和mapj oin 无法解决的 join 优化 。 本章将会逐一详细介绍其优化方法和原理 。
  另外一点，其实之所以需要优化，基本的本质原因是因为数据倾斜导致的，HiveQL的各种优化方法基本都和数据倾斜密切相关，说白了就是HiveQL在书写的时候因为表设计的不佳或者语句写的不佳，导致了数据倾斜，本来可以分布式并行做的事情，结果硬生生的做成了单节点处理，数据严重倾斜；就好比本来是你一家人分均的活，半个小时就能搞定，结果因为总指挥总设计的不当表现，硬生生的把活全部分到你这里了，可不就是耗时翻倍了吗？

join无关的优化

group by 引起的倾斜优化

  group by 引起的倾斜主要是输入数据行按照 group by 列分布不均匀引起的，比如，假设按照城市来统计订单数，而一线城市的订单往往要远远大于3三线城市，这个时候group by的时候就容易形成数据倾斜；
  对于 group by 引起的倾斜，优化措施非常简单，只需设置下面参数即可：

set hive.map.aggr = true
set hive.groupby.skewindata=true

  此时 Hive 在数据倾斜的时候会进行负载均衡，生成的查询计划会有两个 MapReduce Job；第 一 个MapReduce Job中，Map 的输出结果集合会随机分布到 Reduce 中， 每个Reduce 做部分聚合操作并输出结果，这样处理的结果是相同的 GroupBy Key 有可能被分布到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MapReduce Job 再根据预处理的数据结果按照 GroupBy Key 分布到 Reduce 中（这个过程可以保证相同的 GroupBy Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作；

count distinc引起的倾斜优化

  在 Hive 开发过程中，应该小心使用 count distinct ，因为很容易引起性能问题，比如下面的 SQL；

select count(distinct user) from ods_user;

  由于必须去重，因此 Hive 将会把 Map 阶段的输出全部分布到一个 Reduce Task 上， 此时很容易引起性能问题。 对于这种情况，可以通过先 group by 再 count 的方式来优化， 优化后的 HiveQL 如下：

select 
     count(*)
from
(
    select user
    from ods_user
    group by user
) tmp;

  其原理为：利用 group by 去重，再统计 group by 的行数目 。

join相关的优化

大表join小表优化

  我有个设备表ods_rs_assemble_tbb_media_device，记录某公司在全世界各工厂的设备信息，是个几千万行的大表，另外有个机型表ods_rs_assemble_tbd_device_style是这些设备的机型类型纬度表，机型也就几十种而已，即几十行；这就是典型的大表 join 小表问题，可以通过 mapjoin 的方式来优化。

MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，而普通的equality join则是类似于mapreduce模型中的file join，需要先分组，然后再reduce端进行连接，使用的时候需要结合着场景；由于mapjoin是在map是进行了join操作，省去了reduce的运行，效率也会高很多。

  具体实现只需添加 mapjoin的注释即可 ，如 /*+mapjoin(小表别名)*/,具体如下；

hive> select
    >    /*+mapjoin(ds)*/
    > tmd.device_style_id,ds.device_style_name
    > from      ods_rs_assemble_tbb_media_device tmd
    > left join ods_rs_assemble_tbd_device_style ds
    >        on tmd.device_style_id=ds.device_style_id
    >       and ds.event_day='20200729'
    > where tmd.event_day='20200729'

  输出日志可以看到启动mapjoin的关键字Starting to launch local task to process map join; maximum memory = 954728448 ……，具体如下；

2020-07-30 19:08:29     Starting to launch local task to process map join;      maximum memory = 954728448
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
2020-07-30 19:08:30     Dump the side-table for tag: 1 with group count: 310 into file: file:/tmp/liuxiaowei/4be13625-ce71-4f48-8add-2e80cd40bebc/hive_2020-07-30_19-08-19_925_30210501728546316 27-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile01--.hashtable
2020-07-30 19:08:31     Uploaded 1 File to: file:/tmp/liuxiaowei/4be13625-ce71-4f48-8add-2e80cd40bebc/hive_2020-07-30_19-08-19_925_3021050172854631627-1/-local-10004/HashTable-Stage-3/MapJoin- mapfile01--.hashtable (12333 bytes)
2020-07-30 19:08:31     End of local task; Time Taken: 1.613 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1587046987432_70601, Tracking URL = http://shucang-10.szanba.ren:8088/proxy/application_1587046987432_70601/
Kill Command = /data/tools/hadoop/current//bin/hadoop job  -kill job_1587046987432_70601
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
2020-07-30 19:08:57,917 Stage-3 map = 0%,  reduce = 0%
2020-07-30 19:09:12,885 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 6.52 sec
MapReduce Total cumulative CPU time: 6 seconds 520 msec
Ended Job = job_1587046987432_70601
MapReduce Jobs Launched:
Stage-Stage-3: Map: 1   Cumulative CPU: 6.52 sec   HDFS Read: 784682 HDFS Write: 4182 SUCCESS
Total MapReduce CPU Time Spent: 6 seconds 520 msec
OK
35      17寸竖机
50      21寸高清横机
……

注意：如果你使用较高的Hive版本，是默认开启了了mapjoin的，具体涉及的参数如下所述；

在Hive0.11后，Hive默认启动该优化，也就是不在需要显示的使用MAPJOIN标记，其会在必要的时候触发该优化操作将普通JOIN转换成MapJoin，可以通过以下两个属性来设置该优化的触发时机
hive.auto.convert.join
默认值为true，自动开户MAPJOIN优化
hive.mapjoin.smalltable.filesize
默认值为2500000(25M),通过配置该属性来确定使用该优化的表的大小，如果表的大小小于此值就会被加载进内存中

大表join大表优化

  如果上述 mapjoin中维度表很大呢？比如超过了hive.mapjoin.smalltable.filesize 的大小,甚至夸张点，都是几十G的数据，这种就是大表join 大表的问题 。 此类问题相对比较复杂，要根据具体情况具体分析，但是奥义还是一样，就是要避免数据倾斜，如果数据没有倾斜还跑的很慢，就是另外一个故事了，得加资源了。

  基本思路如下；

• 是否能限制其中一个表的条件，筛选分区，去重后能否可以转成小表，如果可以，则转成小表，变成了大表join小表，可以转换成mapjoin的优化；
  做法：筛选分区，去重，只去另一个表内有的数据等，达到减小一个表的目的；

• 慢的主要原因是是因为join条件内的两表的关联key数据倾斜导致的，可以在关联的key上加上随机数，打散数据，尽量让集群的所有机器都能共同工作起来。
  场景：购买者表和购买者等级表的一个关联，但是由于此款产品为私人订制系列，大多数订单都来源于某几个大客户，少数订单来源于其他人，单独的根据购买者id join容易出现数据倾斜，所以需要加入随机数来改写join键值 ，伪代码参考如下；

select
      m.buyer_id
    ,sum(pay_cnt_90d) as pay_cnt_90d
    ,sum(case when m.s_level=O then pay_cnt_90d end) as pay_cnt_90d_s0
    ,sum(case when m.s_level=l then pay_cnt_90d end) as pay_cnt_90d_sl
    ,sum(case when m.s_level=2 then pay_cnt_90d end) as pay_cnt_90d_s2
    ,sum(case when m.s_level=3 then pay_cnt_90d end) as pay_cnt_90d_s3
    ,sum(case when m.s_level=4 then pay_cnt_90d end) as pay_cnt_90d_s4
    ,sum(case when m.s_level=5 then pay_cnt_90d end) as pay_cnt_90d_s5
from
(
    select
        a.buyer_id,a.seller_id,b.s_level,a.pay_cnt_90d
    from
	(
        select /*mapjoin(big)*/
            buyer_id,seller_id,pay_cnt_90d,
            if(big.seller_id is not null,concat(table_A.seller_id,’rnd',cast(rand() *1000 as bigint),table_A.seller_id) as seller_id_joinkey
        from table A
        left join
        --big表seller有重复，请注意一定要group by后再join，保证table_A的行数保持不变
       (
	       select 
		       seller_id 
		   from dim_big_seller 
		   group by seller_ id
	   ) big
               on table_A.seller_ id=big.seller_id
    ) a
    join
	(
        select ／mapjoin(big)*/
            seller_ id,s_level,
         -- big表的seller_id_joinkey 生成逻辑和上面的生成逻辑一样
            coalesce(seller_id_joinkey,table_B.seller_id) as seller_ id_ joinkey
        from table B 
        left  join
        --table_B表join 大卖家表后大卖家行数放大1000 倍，其他卖家行数保持不变
       (
	       select seller_id,seller_id_joinkey 
		   from dim_big_ seller
	   ) big
         on table_ B.seller_ id=big.seller_id
    ) b
    on a.seller_id_joinkey=b.seller_id_joinkey
)m
group by m.buyer_id

  以上就是Hive优化实践的一些思路，其实优化的奥义就是要避免数据倾斜，如果数据没有倾斜，每个节点都饱和工作，主要是通过监控节点的CPU，内存，磁盘读写，网络带宽，其中的一项或者多项保持接近100%，就说明集群某一方面的资源不足，就是另外一个故事了，得加资源了，说明目前的集群配置不足以支持目前的业务数据需求，上报boss，加钱吧！