hive优化

1

前言

       毫不夸张的说，有没有掌握hive调优，是判断一个数据工程师是否合格的重要指标 

       hive调优涉及到压缩和存储调优，参数调优，sql的调优，数据倾斜调优，小文件问题的调优等

2

数据的压缩与存储格式

1. map阶段输出数据压缩 ，在这个阶段，优先选择一个低CPU开销的算法。

set hive.exec.compress.intermediate=true

Set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec

set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;

2. 对最终输出结果压缩

set hive.exec.compress.output=true 

set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec ## 当然，也可以在hive建表时指定表的文件格式和压缩编码

结论，一般选择orcfile/parquet + snappy 方式

3

合理利用分区分桶

      分区是将表的数据在物理上分成不同的文件夹，以便于在查询时可以精准指定所要读取的分区目录，从来降低读取的数据量

分桶是将表数据按指定列的hash散列后分在了不同的文件中，将来查询时，hive可以根据分桶结构，快速定位到一行数据所在的分桶文件，从来提高读取效率

4

hive参数优化

// 让可以不走mapreduce任务的，就不走mapreduce任务

hive> set hive.fetch.task.conversion=more;

// 开启任务并行执行 set hive.exec.parallel=true;// 解释：当一个sql中有多个job时候，且这多个job之间没有依赖，则可以让顺序执行变为并行执行（一般为用到union all的时候）  // 同一个sql允许并行任务的最大线程数

set hive.exec.parallel.thread.number=8;

// 设置jvm重用// JVM重用对hive的性能具有非常大的 影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。

set mapred.job.reuse.jvm.num.tasks=10;

 // 合理设置reduce的数目// 方法1：调整每个reduce所接受的数据量大小

set hive.exec.reducers.bytes.per.reducer=500000000; （500M）

// 方法2：直接设置reduce数量

set mapred.reduce.tasks = 20

// map端聚合，降低传给reduce的数据量

set hive.map.aggr=true  

 // 开启hive内置的数倾优化机制

set hive.groupby.skewindata=true

5

sql优化

1.where条件优化

优化前（关系数据库不用考虑会自动优化）

select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808';

优化后(where条件在map端执行而不是在reduce端执行）

select m.cid,u.id from （select * from order where dt='20180818'） m join customer u on( m.cid =u.id);

2.union优化

尽量不要使用union （union 去掉重复的记录）而是使用 union all 然后在用group by 去重

3.count distinct优化

不要使用count (distinct  cloumn) ,使用子查询

select count(1) from (select id from tablename group by id) tmp;

4.用in 来代替join

如果需要根据一个表的字段来约束另为一个表，尽量用in来代替join . in 要比join 快

​​​​​​​

select id,name from tb1  a join tb2 b on(a.id = b.id); select id,name from tb1 where id in(select id from tb2);

5.优化子查询

消灭子查询内的 group by 、 COUNT(DISTINCT)，MAX，MIN。可以减少job的数量。

6.join 优化

Common/shuffle/Reduce JOIN 连接发生的阶段，发生在reduce 阶段， 适用于大表 连接 大表(默认的方式)

Map join ：连接发生在map阶段 ， 适用于小表 连接 大表
                       大表的数据从文件中读取
                       小表的数据存放在内存中（hive中已经自动进行了优化，自动判断小表，然后进行缓存）

set hive.auto.convert.join=true;

SMB join
   Sort -Merge -Bucket Join  对大表连接大表的优化，用桶表的概念来进行优化。在一个桶内发生笛卡尔积连接（需要是两个桶表进行join）

 set hive.auto.convert.sortmerge.join=true;  

 set hive.optimize.bucketmapjoin = true;  

set hive.optimize.bucketmapjoin.sortedmerge = true;

 set hive.auto.convert.sortmerge.join.noconditionaltask=true;

6

数据倾斜

表现：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。

原因：某个reduce的数据输入量远远大于其他reduce数据的输入量

1.sql本身导致的倾斜

1）group by

如果是在group by中产生了数据倾斜，是否可以将group by的维度变得更细，如果没法变得更细，就可以在原分组key上添加随机数后分组聚合一次，然后对结果去掉随机数后再分组聚合

在join时，有大量为null的join key，则可以将null转成随机值，避免聚集

2）count(distinct)

情形：某特殊值过多

后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务

解决方式：count distinct 时，将值为空的情况单独处理，比如可以直接过滤空值的行，

在最后结果中加 1。如果还有其他计算，需要进行 group by，可以先将值为空的记录单独处理，再和其他计算结果进行 union。

3）不同数据类型关联产生数据倾斜

情形：比如用户表中 user_id 字段为 int，log 表中 user_id 字段既有 string 类型也有 int 类型。当按照 user_id 进行两个表的 Join 操作时。

后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务

默认的 Hash 操作会按 int 型的 id 来进行分配，这样会导致所有 string 类型 id 的记录都分配

到一个 Reducer 中。

解决方式：把数字类型转换成字符串类型

select * from users a

left outer join logs b

on a.usr_id = cast(b.user_id as string)

1. Mapjoin

如果两个表 join 的时候，一个表为小表，可以用 mapjoin 做。

配置 mapjoin：

set hive.auto.convert.join = true;  是否开启自动mapjoin，默认是true

set hive.mapjoin.smalltable.filesize=100000000;   mapjoin的表size大小

5) 大小写转化 ，尽量在子查询里面处理大小写，不要在on后面处理

2、业务数据本身的特性(存在热点key)

join的每路输入都比较大，且长尾是热点值导致的，可以对热点值和非热点值分别进行处理，再合并数据

3、key本身分布不均

可以在key上加随机数：random

或者增加reduceTask数量：set mapred.reduce.tasks = 15;

开启数据倾斜时负载均衡

set hive.groupby.skewindata=true;

思想：就是先随机分发并处理，再按照 key group by 来分发处理。

操作：当选项设定为 true，生成的查询计划会有两个 MRJob。

第一个 MRJob 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 GroupBy Key 有可能被分发到不同的Reduce 中，从而达到负载均衡的目的；

第二个 MRJob 再根据预处理的数据结果按照 GroupBy Key 分布到 Reduce 中（这个过程可以保证相同的原始 GroupBy Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

4、控制空值分布

将为空的 key 转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分不到多个 Reducer。

注：对于异常值如果不需要的话，最好是提前在 where 条件里过滤掉，这样可以使计算量大大减少

5、join优化 小表join大表-MapJoin，小表放内存 例如一个表数据有1亿，一个表数据1千万 hive.auto.convert.join=true 大表join大表-Skewjoin 当两个表都非常大，无法直接加载到内存中,那么这个时候就需要评估join key的分布是否均匀。

情况一：当key分布均匀，那么这个时候一般就不是倾斜的范畴了，需要考虑增加reduce数量等其他调优手段了 情况二：当key分布不均匀，如果只是某几个key数据量比较大，那么就需要把这几个key单独拿出来进行计算；如果大部分key的数据量都很大，那么这个时候就需要进行增加随机前缀的方式了，也就是二次聚合的思想。

大表 key 加盐，小表扩大 N 倍 join
这种方法适用于倾斜的key比较多，没办法把这些key单独拿出来进行计算。

即将存在数据倾斜的数据集全部加上随机前缀，然后对另外一个不存在严重数据倾斜的数据集整体与随机前缀集作笛卡尔乘积（即将数据量扩大N倍）。

二阶段聚合：

第一次是局部聚合，先给每个key打上随机数

然后执行聚合操作,如reduceByKey,groupByKey,这个时候得到的结果相对原始数据集肯定是少了很多，然后再把key上的随机数给删除，保证原始数据集中的相同key可以被分配到同一个task上

再次进行聚合操作，得到最终结果

具体code如下：

set hive.optimize.skewjoin=true;
set hive.skewjoin.key=100000;
set hive.optimize.skewjoin.compiletime=true;
-- 某几个key的数据量比较大,需要单独进行计算
select
  lnc.request_url,
  count(uuid) as pv
from wedw_dwd.log_ng_channel lnc
join
(
  select
     request_url,
     visit_time,
     uuid
  from wedw_dwd.track_beacon_log
  where request_url<>'www.baidu.com'
)t
on lnc.request_url = t.request_url
group by lnc.request_url
 
union all
 
select
  lnc.request_url,
  count(uuid) as pv
from wedw_dwd.log_ng_channel lnc
join
(
  select
     request_url,
     visit_time,
     uuid
  from wedw_dwd.track_beacon_log
  where request_url='www.baidu.com'
)t
on lnc.request_url = t.request_url
group by lnc.request_url
 
--大部分key的数据量都比较大，采用随机前缀的方式
select
  split(request_url,'&')[1] as request_url,
  sum(cnt) as cnt
from
(
  select
    t1.request_url,
    count(uuid) as cnt
  from
  (
    select   concat(cast(round(rand()*10) as int),'&',request_url) as request_url
  )t1
  left join
  ( select  concat(cast(round(rand()*10) as int),'&',request_url) as request_url,
            uid
    from wedw_dwd.track_beacon_log
  )t2
  on t1.request_url = t2.request_url
  group by t1.request_url
)t
group by split(request_url,'&')[1]

6、数据膨胀引发的数据倾斜 在多维聚合计算时，如果进行分组聚合的字段过多，如 jobid：166318 ,202339 如果表的数据量很大，并且Map端的聚合不能很好地起到数据压缩的情况下，会导致Map端产出的数据急速膨胀，这种情况容易导致作业内存溢出的异常。如果表中含有数据倾斜key，会加剧Shuffle过程的数据倾斜。 解决方法：可以拆分上面的sql，将grouping sets拆分成几个sql，但是如果聚合case太多，要拆解的sql也会变多。 在Hive中可以通过参数 hive.new.job.grouping.set.cardinality 配置的方式自动控制作业的拆解，该参数默认值是30，表示针对grouping sets/rollups/cubes这类多维聚合的操作。

7

合并小文件

小文件的产生有三个地方，map输入，map输出，reduce输出，小文件过多也会影响hive的分析效率：

设置map输入的小文件合并

set mapred.max.split.size=256000000;  //一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)

set mapred.min.split.size.per.node=100000000;//一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)  

set mapred.min.split.size.per.rack=100000000;//执行Map前进行小文件合并

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

设置map输出和reduce输出进行合并的相关参数：

//设置map端输出进行合并，默认为true

set hive.merge.mapfiles = true

//设置reduce端输出进行合并，默认为false

set hive.merge.mapredfiles = true

//设置合并文件的大小

set hive.merge.size.per.task = 256*1000*1000

//当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge。

set hive.merge.smallfiles.avgsize=16000000

8

查看sql的执行计划

explain sql 

学会查看sql的执行计划，优化业务逻辑 ，减少job的数据量。对调优也非常重要

数据倾斜优化案例：

1.找到spark日志，

2. 找到运行时间最久的task

 3.点击步骤2中logs列的stderr

4.从“here”进入后， 拉到日志的后面，可以看到正在Join的Key的字段和具体数据 task 45 与 Task Id 相对应，在Join结束后，还会输出该Task Join最多数据的Key，前缀有[max]

 5. 最后手动查看key的分布情况：

 从上图可知，是因为clientcode某一个数值过多（A表是clientcode，B表是cid） SELECT clientcode , COUNT(*) as cnt FROM dwhtl.edw_usr_app GROUP BY clientcode ORDER BY cnt DESC LIMIT 100; 示例中通过clientcode 关联，检查发现 dwhtl.ede_htl_cid_uid  表中clientcode=‘00000000000000000000’的有 8万，原因就是这8 多万数据都分配到一个 reduce 去执行，导致数据倾斜。 解决方法： 可以先把这部分clientcode去除之后再关联。

Spark UI 说明 https://spark.apache.org/docs/latest/web-ui.html