hive round函数不起作用_Hive性能优化总结(二)

接上文，从计算步骤和计算资源的角度进行Hive性能优化

三、计算步骤优化

计算步骤优化主要是为了减少单个SQL中的task的数量。

3.1 多表join尽量保持join key一致

同一个SQL中相同关联key的表join时会放在一个join任务中完成，数据类型也必须保持一致。

具体案例：

create table table_a(id bigint, col1 string);
create table table_b(id bigint, col2 string);
create table table_c(id bigint, col3 string);
           

关联的key相同，字段类型也一致，那么就只有一个join任务：

select
    a.id as id
    ,col1
    ,col2
    ,col3
from
    (
    select
        id
        ,col1
    from
        db.table_a
    ) as a
    left join
    (
    select
        id
        ,col1
    from
        db.table_b
    ) as b
    on a.id = b.id
    left join
    (
    select
        id
        ,col1
    from
        db.table_c
    ) as c
    on b.id = c.id
           

如果关联的key的字段类型不一致，有2个join任务：

select
    a.id as id
    ,col1
    ,col2
    ,col3
from
    (
    select
        id
        ,col1
    from
        db.table_a
    ) as a
    left join
    (
    select
        id
        ,col1
    from
        db.table_b
    ) as b
    on cast(a.id as string) = cast(b.id as string)
    left join
    (
    select
        id
        ,col1
    from
        db.table_c
    ) as c
    on b.id = c.id
           

3.2 避免暴力扫描

• 全量表与增量表的不同使用方式
• 分区裁剪与列裁剪

四、计算资源优化

4.1 并发优化

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

• 参数1：控制在同一个SQL中的不同的job是否可以同时运行(job之间没有前后依赖的都可以并行执行)，默认为false
• 参数2：同一个SQL允许并行任务的最大线程数

4.2 数据倾斜优化

set hive.map.aggr=true;
set hive.groupby.skewindata=true;
set hive.groupby.mapaggr.checkinterval=100000;
set hive.optimize.skewjoin=true;
set hive.skewjoin.key=100000;
           

• 参数1：在mapper端部分聚合，相当于Combiner 。Map-Side聚合（一般在聚合函数sum,count时使用）
• 参数2：当选项设定为 true，生成的查询计划会有两个 MR Job。

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

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

• 参数3：这是group的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置。
• 参数4：
• 参数5：join的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置；

hive 在运行的时候没有办法判断哪个 key 会产生多大的倾斜，所以使用这个参数控制倾斜的阈值，如果超过这个值，新的值会发送给那些还没有达到的 reduce。对full join无效。如果你不知道设置多少，可以就按官方默认的1个reduce 只处理1G的算法，那么 skew_key_threshold = 1G/平均行长，或者默认直接设成250000000 (差不多算平均行长4个字节)

4.3 小文件合并

解决小文件合并可从以下两个方向入手：

• 输入合并； 即在Map前合并小文件。这个方法即可以解决之前小文件数太多，导致mapper数太多的问题；还可以防止输出小文件合数太多的问题（因为mr只有map时，mapper数就是输出的文件个数）
• 输出合并； 即在输出结果的时候合并小文件

4.3.1 输入文件合并

set hive.hadoop.supports.splittable.combineinputformat=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
set mapred.max.split.size=2048000000;
set mapred.min.split.size.per.node=2048000000;
set mapred.min.split.size.per.rack=2048000000;
           

• 参数1：打开合并开关
• 参数2：执行Map前进行小文件合并
• 参数3：每个Map最大输入大小，默认2G
• 参数4：一个节点上split的至少的大小 ，决定了多个data node上的文件是否需要合并
• 参数5：一个交换机下split的至少的大小，决定了多个交换机上的文件是否需要合并

4.3.2 输出文件合并

set hive.merge.mapfiles=true;
set hive.merge.mapredfiles=true;
set hive.merge.size.per.task=256*1000*1000;
set hive.merge.smallfiles.avgsize=16000000;
           

• 参数1：在Map-only的任务结束时合并小文件
• 参数2：在Map-Reduce的任务结束时合并小文件
• 参数3：合并后每个文件的大小，默认256000000
• 参数4：平均文件大小，参数值是决定是否执行合并操作的阈值，默认16000000触发合并的条件

以上参数在文件输出时合并。但是它们和「压缩」并存时会失效，并对ORC格式的表（orc本身就已经压缩）不起作用。

4.4 内存优化

-- container的内存，运行mapper的容器的物理内存
set mapreduce.map.memory.mb=2048;

-- jvm堆内存
set mapred.child.map.java.opts='-Xmx2048M';
set mapreduce.map.java.opts='-Xmx2048M';  
set mapreduce.reduce.memory.mb=2048;
set mapred.child.reduce.java.opts='-Xmx2048m';
set mapreduce.reduce.java.opts='-Xmx2048M';

-- app内存
set yarn.app.mapreduce.am.resource.mb=3000;
set yarn.app.mapreduce.am.command-opts='-Xmx2048m';
           

am指Yarn中AppMaster，针对MapReduce计算框架就是MR AppMaster，通过配置这两个选项，可以设定MR AppMaster使用的内存。

一般看hadoop日志时可以看到map/reduce，但是当没有map/reduce时就开始报

beyond memory limit

类似的错时，说明是am的内存不够。

在yarn container这种模式下，map/reduce task是运行在Container之中的，所以上面提到的mapreduce.map(reduce).memory.mb大小 都大于mapreduce.map(reduce).java.opts值的大小。mapreduce.{map|reduce}.java.opts能够通过Xmx设置JVM最大的heap的使用， 一般设置为0.75倍的memory.mb，因为需要为java code等预留些空间。

4.5 Hive压缩

-- map输出压缩
set hive.exec.compress.intermediate=true;
set mapred.map.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;

-- 结果输出压缩
set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set mapred.output.compression.type=BLOCK;
           

References：

hive笔记​meihuakaile.github.io

数据分析利器之hive优化十大原则​mp.weixin.qq.com

Hive性能优化（全面）​mp.weixin.qq.com