hive调优

PONY LEE

已于 2023-05-19 14:58:31 修改

阅读量311

点赞数

分类专栏： hive 文章标签： hive hadoop big data

于 2022-06-14 13:41:17 首次发布

本文链接：https://blog.csdn.net/weixin_38251332/article/details/125276436

版权

hive 专栏收录该内容

23 篇文章 2 订阅

订阅专栏

hive调优的基本思想：

尽早尽量过滤数据，减少每个阶段的数据量
减少job数
解决数据倾斜问题

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

1）列裁剪
    只读取查询逻辑中真实需要的列
2）分区裁剪
    在查询的过程中减少不必要的分区
    Select  count(orderid) From order_table where to_date(sale_time)=＇2014-03-03＇ and hour(to_date(sale_time))=10  
    优化为如下：
    Select  count(orderid) From order_table where 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,item from order_table limit 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 产生的数据倾斜
    用法：SELECT/*+ MAPJOIN(b) */a.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; //注意：如果想得到正确的排序结果，分发字段不能与排序字段相同。然而得到的结果并不是全局有序的，只是在单个reduce内有序。
    
    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 的执行。