Hive调优的总结

hive的优势

​ hive的优势是不要求数据转化维特定的格式，而是利用hadoop本身InputFormat API来从不同的数据源读取数据，同样使用OutputFormat API将数据写成不同的格式。hive的统一的元数据管理，所以和Spark、Impala等SQL引擎是通用的；这里的通用是指：在拥有了统一的metastore之后，在hive中建立一张表，在spark\Impala中是能用的。反之，在spark中建立一张表，在Hive中也是能用的，只需要公用元数据即可；

​ Hive使用SQL语法，提供快速开发的能力，还可以通过用户自定义函数UDF,用户自定义聚合函数UDAF,和用户定义的表函数UDTF进行拓张；避免了直接去写mapreduce，减少开发成本；hive中不经可以使用逗号和制表符等文本文件存储；还可以使用Sequence File、RC、ORC、Parquet存储；还可以自定义存储格式；hive多用在数据离线处理如：日志分析等

Hive的优化

1、数据倾斜情况下的Hive优化

• 数据量较大时，慎用count(distinct)->需要用一个Reduce,count(dinstinct)容易产生数据倾斜问题，改用UDAF + group by 来实现对应功能；

• 自定义的UDAF函数优化 sum,count,max等UDAF，不怕数据倾斜问题，hadoop在map端汇总并优化；

• key值分布不均匀，选择key值分布均匀的表作为驱动表同时做好列裁剪

• 不同数据类型的id的关联会产生数据倾斜问题：

  • 如：日志表中类型是string的，但商品表的id却是bigint的，在做join时，把日志中的商品id来做hash分区，由于string转化的hash值可能和bigint的不一样,导致了字段关联不上，字符串的表字段都到一个Reduce上了，造成数据倾斜
    • 解决方法：将string转int，或者int转string就可以了

• 某个字段的数据特别多，造成的数据倾斜

  • 参数调节：
    • set hive.map.aggr=true => 在map端聚合，相当于Combiner
    • set hive.groupbymapaggr.checkinterval=100000,在map端进行聚合操作的条目数量
    • set hive.groupby.skewindata=true,在有数据倾斜的时候进行负载均衡
      • 默认为false，开启时原本的mapReduce计划会生成两个：第一个MR中的map的输出结果集合会随机发布到reduce中，每个reduce做聚合操作，并输出结果从而达到负载均衡目的；第二个MapReduce任务再根据预处理的数据结果按照Group By Key分布到Reduce中，最后完成聚合操作

• 大表join大表

  • 空Key过滤

    • 情景：两表join，主键为null的值会被当做相同的Key而分配到同个计算Map

      • 方法1：子查询过滤null,再union回去

      • 方法2：函数过滤null,并将将空值的key变成一个字符串加上随机数，从而解决数据倾斜问题

        SELECT * FROM log a LEFT OUTER
        JOIN bmw_users b ON
        CASE WHEN a.user_id IS NULL THEN CONCAT('dp_hive', RAND()) ELSE a.user_id END = b.user_id;
        

• 小表Join大表

  • Map Join操作

    • 开启Map Join功能，碰到小表时自动启动map join

    • -- 设置自动选择Map join
      set hive.auto.convert.join=true;默认是true
      -- 设置大表小表的阈值（默认25M以下是小表）
      set hive.mapjoin.smalltable.filesize=25000000;
      

2、从map reduce的Task数量考虑

2.1 map阶段优化

2.1.1 调整分块大小

• 注：需要压缩算法支持分割split，增多split才有效

• set mapred.max.split.size指的是数据的最小分割大小，默认为1B
  set mapred.max.split.size:指的是数据的最大分割单元大小，默认为256MB
  

• snappy不支持split,hadoop本身不自带需要安装

• 压缩格式的使用：

  • --job输出文件安装block以Gzip的文件方式机械压缩
    set mapreduce.output.fileoutputformat.compress=true // 默认值是 false
    set mapreduce.output.fileoutputformat.compress.type=BLOCK // 默认值是 Record
    set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.GzipCodec // 默认值是 org.apache.hadoop.io.compress.DefaultCodec
    --Map输出结果也以Gzip进行压缩
    set mapreduce.map.output.compress=true;
    set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.GzipCodec
    
    -- 对hive输出结果和中间结果都进行压缩
    set hive.exec.compress.output=true;//默认是false，不压缩
    set hive.exec.compress.intermediate=true//默认是false，为true时 MR 设置的压缩才启用
    

• 两种情况

  • 减少map数量：

    • 适用场景：小文件过多，会造成map资源的浪费

    • 假设一个SQL任务：
      Select count(1) from popt_tbaccountcopy_meswhere pt = '2012-07-04';
      该任务的inputdir :  /group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04
      共有194个文件，其中很多事远远小于128M的小文件，总大小9G，正常执行会用194个map任务。
      Map总共消耗的计算资源：SLOTS_MILLIS_MAPS= 623,020
      
      通过以下方法来在map执行前合并小文件，减少map数：
      set mapred.max.split.size=128000000;(128Mb)
      set mapred.min.split.size.per.node=100000000;
      set mapred.min.split.size.per.rack=100000000;
      set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
      再执行上面的语句，用了74个map任务，map消耗的计算资源：SLOTS_MILLIS_MAPS= 333,500
      对于这个简单SQL任务，执行时间上可能差不多，但节省了一半的计算资源。
      大概解释一下，100000000表示100M, 
      set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;这个参数表示执行前进行小文件合并，
      前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，
      小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），
      进行合并,最终生成了74个块。
      

  • 增多map数量：没有充分利用集群map的性能，一个map处理的文件过大

    • 如何适当的增加map数？
      当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，
      来使得每个map处理的数据量减少，从而提高任务的执行效率。
      
       假设有这样一个任务：
          Select data_desc,
                     count(1),
                     count(distinct id),
                     sum(case when ...),
                     sum(case when ...),
                     sum(...)
          from a group by data_desc
      
      如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，
      这种情况下，我们要考虑将这一个文件合理的拆分成多个，分桶表
      这样就可以用多个map任务去完成。
          
      
      这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。
      每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。
      

2.2 reduce阶段优化

• Reduce的个数对整个作业的性能影响很大，过大则产生多个小文件，过小则单个Reduce处理数据量将会加大，很可能会引起OOM异常

  • reduce确定个数机制：

    • 参数1：hive.exec.reducers.bytes.per.reducer(每个reduce任务处理的数据量，默认为1G)

    • 参数2：hive.exec.reducers.max(每个任务最大的reduce数，默认为999)

    • reduceNum=min(参数2，总输入数据量/参数1)

    • 注：也就是说：如果reduce的输入（map的输出）总大小不超过1G，那么只会有一个reduce任务；

    • 如：select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt; 
                  /group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04 总大小为9G多，
        因此这句有10个reduce
      

• 调整reduce个数的方法一：

  • set hive.exec.reducers.bytes.per.reducer=5000000000;(500M)

  • -- 增加了reduce数
    select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt;
    
    　--　这次有15个reduce
    

• 调整reduce个数的方法二：

  • set mapred.reduce.tasks=15;
    select pt,count(1) from popt_tbaccountcopy_mes where pt='2012-07-04' group by pt;
    --这次也有15个
    

注：reduce的个数并不是越多越好，是业务而定

• 什么情况下只有一个reduce
  • 很多时候你会发现任务不管数据量有多大，不管你有没有调整reduce个数的参数，却只有一个reduce!原因如下可能：
    • 没有group by
    • 使用了Order by(可以使用distribute by + sort by代替)
    • 有笛卡尔积

2.3 小文件合并优化（合并map的输出文件，reduce的结果文件）

• 相关设置合并的参数：

  • -- 合并map输出文件： set hive.merge.mapfiles=true(默认true)
    -- 合并reduce端输出文件：set hvie.merge.mapredfiles=false(默认false)
    -- 合并文件的大小：ser hive.merge.size.per.task=256*1000*1000(默认值值为256M)
    

• 小文件的产生情况

  • 动态分区插入数据，产生大量的小文件，从而导致map数据剧增
  • reduce数量过多，小文件也过多
  • 数据源本身就含大量的小文件（从进入map执行前就可以合并）

• 小文件问题解决方案

  • 使用特殊的格式如：parquet存储表，一定程度上可以减少小文件

  • 减少reduce的数量（set mapred.reduce.tasks=??）

  • 对于已有的小文件我们采用如下几种方法：

    • 重建表，建表时减少reduce数量

    • 通过参数调节，设置map/reduce端的相关参数如：

      • //每个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输出进行合并的相关参数：
        [java] view plain copy
        //设置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
        

3 、SQL优化

3.1 列裁剪

• 做好列裁剪可以忽略不需要的列，节省了读取开销，如：

  • select a,b from q where e<10;
    

3.2 分区裁剪

• 可以在查询过程中减少不必要的分区。列如：

  • select * from (select a1,count(1) from T group by a1) subq where subq.prtn=100; -- 多余分区
    select * from T1 join (select * from T2) subq on (T1.a1=subq.a2) where subq.prtn=100;
    

  • 查询若将”subq.prtn=100"条件放入子查询中更为高效，可以减少读入的分区数目；HIve自动执行这种裁剪优化

3.2利用Hive对Union all优化的特性

• 多表union all会优化成一个job.

• --问题：比如推广效果表要和商品表关联，效果表中的auction_id列既有32位字符串商品id，也有数字id，和商品表关联得到的商品的信息
  -- 解决方法：Hive SQL union all性能会比较好
  select * from effect a
  join
  (select auction_id as auction_id from auctions
  union all
  select cast(auction_string as int) as auction_id from auctions) b
  on a.auction_id=b.auction_id;
  -- 使用union all 会比分别过滤数字id，字符串id然后分别和商品表关联性能要好
  

• 具体好处：1个MapReduce作业，商品表只读一次，推广效果表只读取一次。把这个SQL换成Map/Reduce代码的话，Map的时候，把a表的记录打上标签a，商品表记录每读取一条，打上标签b，变成两个<key, value>对，<(b，数字id)，value>，<(b，字符串id)，value>。所以商品表的HDFS读取只会是一次。

3.3解决Hive对Union all优化的短板

• Hive对union all的优化的特性：对union all的优化只局限于非嵌套查询

  • 解决1：避免子查询内的group by

    • -- 示例1：子查询内有group by
      select * from 
      (select * from t1 group by c1,c2,c3 union all select * from t2 group by c1,c2,c3)
      

    • 从业务逻辑上说，子查询内的group by 怎么看都是多余（除非有count(distinct)）,如果不上因为Hive Bug或者性能上考量（曾经出现如果不执行子查询Group by,数据得不到正确的结果的Hive Bug）。所以这个Hive应该转化如下：

    • select * from (select * from t1 union all select * from t2) t3 group by c1,c2,c3;
      

    • 经过测试，并未出现bug,数据是一致的，MapReduce作业数由3减少到1。

  • 解决2：避免子查询中的聚合操作（count(distinct),max,min）

  • 解决3：避免子查询中的join

  • -- 优化前
    SELECT * FROM
    (SELECT * FROM t1 UNION ALL SELECT * FROM t4 UNION ALL SELECT * FROM t2 JOIN t3 ON t2.id=t3.id) x
    GROUP BY c1,c2;
    

  • -- 上面代码运行会有5个jobs。加入先JOIN生存临时表的话t5，然后UNION ALL，会变成2个jobs。
    
    INSERT OVERWRITE TABLE t5
    SELECT * FROM t2 JOIN t3 ON t2.id=t3.id;
    SELECT * FROM (t1 UNION ALL t4 UNION ALL t5);
    

3.3 优化in/exists语句

• 使用left semi join 代替 in/exists

• -- 比如说
  select a.id,a.name from a.id in (select b.id from b);
  select a.id,a.name from exists(select id from b where a.id = b.id);
  

• -- 应该转化为：	
  select a.id,a.name from a left semo join b on a.id = b.id
  

3.4 排序选择

• cluster by : 对同一字段分桶并排序，不能和sort by 连用
• distribute by + sort by :分桶，保证同一字段值只存在一个结果文件当中，结合sort by保证每个reduceTask结果有序；
• sort by:单机排序，单个reduce结果有序
• order by:全局排序，缺陷是只能使用一个reduce

4、其他

4.1 模式选择

• 本地模式

  • 对于大多数情况，Hive可以通过设置本地模式在单台机器上处理所有任务。对于小量数据，执行时间可以明显被缩短。通过set hive.exec.mode.local.auto=true(默认false)设置为本地模式，本地模式涉及到三个参数：

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mLK5c2BV-1607429261794)(C:\Users\99779\AppData\Roaming\Typora\typora-user-images\image-20201208200033420.png)]

  • set hive.exec.mode.local.auto=true;是打开hive自动判断是否启动本地模式，但是只是打开这个参数不能保证启动本地模式，条件为：

    • map的任务数不超过hive.exec.mode.local.auto.input.files.max的个数

    • 并且map输入文件大小不超过hive.exec.mode.local.auto.inputbytes.max所指定的大小时，本地模式才会启动

    • set hive.exec.mode.local.auto=true;  //开启本地mr
      //设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M
      set hive.exec.mode.local.auto.inputbytes.max=50000000;
      //设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
      set hive.exec.mode.local.auto.input.files.max=10;
      

• 并行模式

  • Hive会将一个查询转化成一个或多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。默认情况下，Hive一次只会执行一个阶段，由于job包含多个阶段，而这些阶段并非完全相互依赖，即：这些阶段可以并行执行，可以缩短整个job的执行时间。设置参数，set hive.exec.parallel=true,或者通过配置文件来完成：

    • set hive.exec.parallel;
      

5、JVM重用

• Hadoop通常是使用派生JVM来执行map和reduce任务的。这时JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含成百上千的task任务的情况。JVM重用可以使得JVM示例在同一个job中时候，通过参数mapred.job.reuse.jvm.num.tasks来设置。

6、推测执行

• Hadoop推测执行可以触发执行一些重复的任务，尽管因对重复的数据进行计算而导致消耗更多的计算资源，不过这个功能的目标是通过加快获取单个task的结果以侦测执行慢的TaskTracker加入到没名单的方式来提高整体的任务执行效率。Hadoop的推测执行功能由2个配置控制着，通过mapred-site.xml中配置：

  • mapred.map.tasks.speculative.execution=true
    mapred.reduce.tasks.speculative.execution=true