hive调优

hive调优

优化的根本思想：

1. 尽早尽量过滤数据，减少每个阶段的数据量；
2. 减少job数；
3. 解决数据倾斜问题。

1、尽早尽量过滤数据，减少每个阶段的数据量

    1）列裁剪
        只读取查询逻辑中真实需要的列
    2）分区裁剪
        在查询的过程中减少不必要的分区
        Select  count(orderid) From order_tablewhere to_date(sale_time)=＇2014-03-03＇ and hour(to_date(sale_time))=10  
        优化为如下：
        Select  count(orderid) From order_tablewhere dt=‘2014-03-03’ and  to_date(sale_time)=‘2014-03-03’ and hour(to_date(sale_time))=10 
        
        通过 explain dependency 语法，获取 input table 和input partition 来分析扫描的数据量

2、减少job数

减少job数会减少中间状态磁盘的读写，减少IO
1）Local Model
    Select user,itemfrom order_tablelimit 10;
    优化为如下：
    Select * from order_tablelimit 10; (不会生成mapreduce程序) 
2）利用hive 的优化机制减少JOB数，join 优化
    不论是外关联outer join还是内关联inner join，如果Join的key相同，不管有多少个表，都会合并为一个MapReduce任务
    SELECt a.val, b.val, c.valFROM a JOIN b ON (a.key= b.key1) JOIN c ON (c.key= b.key1 ) -> 1个JOB 
    SELECt a.val, b.val, c.valFROM a JOIN b ON (a.key= b.key1) JOIN c ON (c.key= b.key2) ->2个JOB
3）job输入输出优化
    善用muti-insert、union all，不同表的union all 相当于 multiple inputs；同一个表的union all，相当map一次输出多条
        insert overwrite table tmp1
            select ... from a where 条件1;
            insert overwrite table tmp2
            select ... from a where 条件2;
        扫描两次
        优化为如下：
        from a
            insert overwrite table tmp1
            select ... where 条件1
            insert overwrite table tmp2
            select ... where 条件2;
        扫描一次

3、解决数据倾斜问题

1）null值过多导致的数据倾斜：预先过滤null值；赋与null值随机值
        
            解决方法1： user_id为空的不参与关联（红色字体为修改后）

            select * from log a

              join users b

              on a.user_id is not null

              and a.user_id = b.user_id

            union all

            select * from log a

              where a.user_id is null;

             

            解决方法2 ：赋与空值分新的key值

            select *

              from log a

              left outer join users b

              on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;

            结论：方法2比方法1效率更好，不但io少了，而且作业数也少了。解决方法1中 log读取两次，jobs是2。解决方法2 job数是1 。这个优化适合无效 id (比如 -99 , ’’, null 等) 产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。
        
        2）关联键分布不均衡，某个key数据量太大导致的数据倾斜：两次聚合，加1~100的前缀，第一次聚合；去掉前缀，在此聚合。

4、JOIN *** 作及优化

    1）避免笛卡尔积
    2）在JOIN前过滤掉不需要的数据
    3）小表放前大表放后原则
        在编写带有join *** 作的代码语句时，应该将条目少的表/子查询放在Join *** 作符的左边。
        因为在Reduce 阶段，位于Join  *** 作符左边的表的内容会被加载进内存，载入条目较少的表可以有效减少OOM（out of memory）即内存溢出。
        所以对于同一个key来说，对应的value值小的放前，大的放后。
    4）Mapjoin()
        当小表与大表JOIN时，采用mapjoin,即在map端完成。同时也可以避免小表与大表JOIN 产生的数据倾斜
        用法：SELECta.key, a.valueFROM a join b on a.key= b.key
        参考：https://blog.csdn.net/zixoa/article/details/108508959
    5）LEFT SEMI JOIN 
        LEFT SEMI JOIN 是IN/EXISTS 子查询的一种更高效的实现，0.13版本以前不支持IN/EXISTS
        Left semi join 与JOIN 的区别:  B表有重复值的情况下left semi join 产生一条，join 会产生多条
        参考：https://www.cnblogs.com/dcx-1993/p/10232221.html

5、输入输出优化

1）合理使用动态分区
       hive动态分区与静态分区：https://www.deeplearn.me/1536.html
   2）union all 优化
       利用hive对UNIOn ALL的优化的特性(0.13版本可以直接union)hive对union all优化只局限于非嵌套查询
       1、select * from(select  ci,c2,c3 from t1 Group by c1,c2,c3 Union all Select  c1,c2,c3 from t2  Group by c1,c2,c3) t3; -> 3个JOB   
       优化为如下：（仅限于t1与t2的表比较小的情况，否则会适得其反）            
       2、select * from (select * from t1 Union all Select * from t2) t3  Group by c1,c2,c3; ->1个JOB
       
   3）合理使用union all
       不同表太多的union ALL,不推荐使用;通常采用建临时分区表，将不同表的结果insert到不同的分区(可并行)，最后再统一处理；    
       INSERT overwrite TABLE lxw_test(flag = '1') SELECt sndaid,mobileFROM lxw_test1;
       INSERT overwrite TABLE lxw_test(flag = '2') SELECt sndaid,mobileFROM lxw_test2;
       INSERT overwrite TABLE lxw_test(flag = '3') SELECt sndaid,mobileFROM lxw_test3;
   
   4）合理使用UDTF
       select col1,col2,newCol from myTable LATERAL VIEW explode(myCol) adTable AS newCol
       LATERAL VIEW explode　参考：https://blog.csdn.net/clerk0324/article/details/58600284
       LATERAL VIEW的作用:侧视图的意义是配合explode（或者其他的UDTF），一个语句生成把单行数据拆解成多行后的数据结果集。
       说明：
           执行过程相当于与原表笛卡尔积关联，单独执行了两次读取，然后union到一个表里，但JOB数只有一个。
           同样myCol也需要为数组类型，但日常中我们多数情况下是string 类型经过split 函数拆分后获取数组类型。
           
   5）多粒度计算优化    
       应用UDTF 优化：按不同维度进行订单汇总。
           select * from 
           (select ‘1',province,sum(sales) from order_tablegroup by province 
           union all 
           select ‘2',city,sum(sales) from order_tablegroup by city  
           Union all 
           select ‘3',county,sum(sales) from order_tablegroup by county 
           ) df
           3次读取order_table,4个JOB
           
           应用UDTF 优化：按不同维度进行订单汇总
           select type,code,sum(sales) from 
           (select split(part,'_')[1] as type,split(part,'_')[0] as code ,sales 
           from order_table LATERAL VIEW explode(split(concat(province,'_1-',city,'_2-',county,'_3'),'-')) adTable AS part  
           ) df group by type,code

6、数据去重与排序

 1）DISTINCT  与GROUP BY 
        尽量避免使用DISTINCT 进行排重，特别是大表 *** 作，用GROUP BY 代替
        1、Select distinct  key from a
        优化为如下：
        2、Select key from a  group by key 
    2）排序优化
        select  * from test1 distribute by name sort by id; //注意：如果想得到正确的排序结果，分发字段不能与排序字段相同
        
        1、Order by 实现全局排序，一个reduce实现，由于不能并发执行，所以效率偏低  
        2、Sort by 实现部分有序，单个reduce输出的结果是有序的，效率高，
            通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字可以指定map 到reduce端的分发key，通过hash去分发）
        3、CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1  但不能指定排序规则，只能升序
        参考：https://blog.csdn.net/student__software/article/details/81634924
    
    3）分组排序    
      JDBDP新版row_number需要声明：CREATE temporary function row_number as 'org.apache.hadoop.hive.ql.udf.generic.GenericUDAFRowNumber';
      在使用 row_number() over()函数时候，over()开窗函数里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。  

７、不同类型需要做类型转换

比如 cast(string as int)