仅从SQL和表及参数层面简单总结22种Hive基础调优方式

1、SQL和table层面

1、column pruning(列裁剪)
    查询的时候需要哪些列，直接查这些列即可，不要全局查询。

2、partition pruning(分区裁剪)          
    查询的时候需要哪些分区，直接查这些分区即可，不要全局查询。

3、合理利用中间结果集
    SQL1包含:select a,b,c from xxx group ...   
    SQL2包含:select a,b,d from xxx group ....
    
    我们可以在sql1和sql2执行之前先创建临时表
            create temporary table tmp_1 as select a,b,c,dfrom xxx group ....
    然后在临时表的基础上执行SQL1和SQL2。

4、对表或分区进行分桶（大表join大表）
    2张表对于连接的字段进行分桶。
    处理左边表内某个桶的Mapper他知道右边表内对应的行在对应的桶内。
    因此Mapper只需要获取那个桶，然后取得数据进行JOIN 。 

5、多个join尽可能使用相同的连接键
    select t1.a,t2.a,t3.a from t1 
        join t2 on t1.key1=t2.key1 
        join t3 on t2.key1=t3.key1;
        #####连接条件相同，只有一个MR
    select t1.a,t2.a,t3.a from t1 
        join t2 on t1.key1=t2.key1 
        join t3 on t2.key2=t3.key2;
        #####连接条件不同，有两个MR

6、排序优化       
    Order By：在一个reducer上处理所有数据，虽然整体有序的，但不能并发执行，效率低。
    而且只有一个reducer，作业可能跑不出来，而且很可能数据倾斜。
    Sort By：排序操作在多个reducer上执行，整体无序（reducer间）但局部有序（reducer内）。
    Distributed By：按指定字段分发数据到reducer,相同的hash值到同一个reducer。
        我们经常使用distributed by id sort by score,来进行排序。
                相同id会到同一个reducer,各个reducer中通过name来排序。
    Clustered By：Sort By和Distribute By两个后接字段一致，即分发字段和排序字段是同一个字段，则
等同于使用clustered by ，但clustered by不能指定排序方式。


7、将reduce join转成map join 
    hive可以将小表（默认小于25000000bytes,即25mb为小表）加入内存，与map端的大表join,这称之为map join。
    语法：
        select  /*+ mapjoin(t1)*/  t1.key,  t2.value from  t1  join t2 on      t1.key=t2.key;
        #####t1为小表，t2为大表
    
    也可以自动让hive去判断是否转map join:
        set hive.auto.convert.join = true;
    当这个参数为true，hive会去判断左表是否超过25000000bytes,没超过则加入内存执行map join。

相关配置参数：
    hive.mapjoin.smalltable.filesize;  #####小表判断阈值，默认25000000byte
    hive.ignore.mapjoin.hint；######手动自动都开启时是否忽略手动指定的mapjoin,默认为true
    hive.auto.convert.join.noconditionaltask;####将多个mapjoin转化为一个mapjoin,默认true
    hive.auto.convert.join.noconditionaltask.size;#####mapjoin合并后表的最大大小


8、存储格式优化
    https://blog.youkuaiyun.com/pengzonglu7292/article/details/79463092

9、配置压缩
    https://blog.youkuaiyun.com/pengzonglu7292/article/details/79419021

结合8、9两点：
    如果配置压缩，就用parquet，压缩格式lzo。
    如果不配置压缩，就使用orc。


10、连接查询与子查询互转避免分组字段数据倾斜

2、参数层面

1、抓取策略
    Hive中对某些情况的查询不需要使用MapReduce计算，而仅仅是将数据抓取过来展现即可。

    hive.fetch.task.conversion
        #####参数取值none,minimal和more
    none:  禁用抓取策略。任何SQL都会执行mr。
    minimal:
        select * from emp where name='张三' limit 2;
        如果表emp没有分区，则以上SQL不会跑MR。
        如果表emp有分区，则必须还要指定具体分区。 
    more:
        select * from emp where name='张三' limit 2;
        表emp有没有分区，以上SQL都不会跑MR。
        新版本的默认策略。
    
2、本地模式
    在本地执行作业，避免作业提交到集群上任务分配与资源调度花费太多时间。
    仅适用于在非生产环境的小型job。

    hive.exec.mode.local.auto    
        #####是否开启本地模式，默认为false
    ive.exec.mode.local.auto.inputbytes.max
        #####job对应文件的最大值，若大于该值仍会以集群方式来运行job，默认为128MB

3、开启并行计算
    一个SQL语句可能转成很多个job,不相关的job可以并行计算。
    select t1.a,t2.a from (select count(x)a from emp)t1 ,
                        (select count(x)a from emp)t2;
    from子句中两个子查询就是不相关的。

    hive.exec.parallel
        #####是否开启并行计算
    hive.exec.parallel.thread.number
        #####一次SQL计算中允许并行执行的job个数的最大值
    

4、考虑使用严格模式
    严格模式下，用户的查询存在限制。
    查询限制：
        1、对于分区表，必须添加where对于分区字段的条件过滤；
        2、order by语句必须包含limit输出限制；
        3、限制执行笛卡尔积的查询。

    
    hive.mapred.mode
        #####是否开启严格模式，默认strict，为开启。
    
    
5、开启map端聚合
    hive.map.aggr 
        是否开启map端聚合（combiner），true为开启
    
    其他相关参数：            
        hive.map.aggr.hash.min.reduction： 
            进行聚合的最小比例，默认为0.5
            预先对十万条数据做聚合，若聚合效率值大于该值，则不聚合
        hive.map.aggr.hash.percentmemory： 
            map端聚合使用的内存的最大值
        hive.map.aggr.hash.force.flush.memory.threshold： 
            map端做聚合操作是hash表的最大可用内容，大于该值则会触发flush
        

6、grouy by 优化
    
    hive.groupby.skewindata
        是否对GroupBy产生的数据倾斜做优化，默认为false
    hive.groupby.mapaggr.checkinterval： 
        map端group by执行聚合时处理的多少行数据（默认：100000） 

    groupby产生的数据倾斜，仅设置以上两个参数还不够，
    必须设置hive.map.aggr为true先执行一次聚合。
   
7、合并map输出小文件
    浪费资源、造成的数据倾斜    

    hive.merge.mapfiles=true
        #####是否合并map输出文件
    hive.merge.mapredfiles=true
        #####是否合并reduce输出文件
    hive.merge.size.per.task=256*1000*1000
        #####合并文件的大小


8、去重统计(慎用)
    hive和其它关系数据库一样，支持count(distinct)操作，该操作会有一个reduce task来完成，极易造成数据倾斜。
    hive.groupby.skewindat
        #####将该参数设置为true，可以解决一般的数据倾斜问题。
   

9、调整map和reduce数量

    mapred.max.split.size    #####input split的最大值,map处理文件的最大值
    mapred.min.split.size.per.node    #####每个节点上input split的最小值
    mapred.min.split.size.per.rack    #####一个机架上split的最小值

 reducer数量过多可能造成小文件过多，reducer数量过少可能造成作业运行太慢。
    mapred.reduce.tasks    #####默认值-1，表示hive会自动设置reducer的数量
    hive.exec.reducers.bytes.per.reducer    #####每个reduce任务处理的数据量，默认256MB
    hive.exec.reducers.max    #####每个任务最大的reduce数


10、启用推测式执行
    一个作业的执行时间取决于最后一个task执行情况。
    如果最后一个或几个task所在机器负载较高，这就可能会大大增加作业的运行时间，形成长尾作业。
    我们可以设定一个阈值，比如最后一个或几个task执行时间超得过已经执行时间的百分之多少的时候。
    在另一台或几台低负载机器启动最后一个或几个task，哪边先执行完则采用哪边的结果。
    
    hive.mapred.reduce.tasks.speculative.execution
        是否打开推测式执行，默认为true(打开) 

        开启推测式执行的阈值
    
    #####如果整个集群负载都特别大，这种方式就要慎重啦。
        
111、jvm复用
    MapReduce的每个task都是一个进程，进程的启用和销毁都需要大量时间。
    启用jvm复用，则下一个task直接使用上一个task占用的进程，这样可以大大地提升作业的性能。
        
    mapred.job.reuse.jvm.num.tasks
        默认值为1，表示一个JVM可以顺序执行的tsak数量为1。

    缺点：
        task插槽所占资源只有到所有的task即整个job全部执行完成时，才会释放该task插槽资源！



12、谓词下压(predicate pushdown)
    将外层查询块的WHERE子句中的谓词移入所包含的较低层查询块（例如视图）。
    从而能够提早进行数据过滤以及有可能更好地利用索引。
    
    hive.optimize.ppd    
        #####是否支持谓词下压，默认开启
    hive.optimize.ppd.storage    
        #####谓词下推开启时，谓词是否下推到存储handler，默认开启，在谓词下推关闭时不起作用
    hive.ppd.recognizetransivity
        #####在等值join条件下是否产地重复的谓词过滤器，默认开启
    
    

 

3、资源调优

4、shuffle调优

5、数据倾斜调优      

hive的优化要说起码可以说五十种，剩下的以后再记录吧，乏了。