分享

Hive调优:让任务并行执行

langke93 2015-5-16 01:28:12 发表于 实操演练 [显示全部楼层] 回帖奖励 阅读模式 关闭右栏 3 43021



业务背景
extract_trfc_page_kpi的hive sql如下:
[mw_shl_code=bash,true]set mapred.job.queue.name=pms;
set hive.exec.reducers.max=8;
set mapred.reduce.tasks=8;
set mapred.job.name=extract_trfc_page_kpi;

insert overwrite table pms.extract_trfc_page_kpi partition(ds='$yesterday')
select distinct
    page_type_id,
    pv,
    uv,
    '$yesterday' update_time
from
(
    --针对PC、H5
    select
        page_type_id,
        sum(pv) as pv,
        sum(uv) as uv
    from dw.rpt_trfc_page_kpi
    where ds = '$yesterday' and stat_type = 1
    group by page_type_id

union all

    --PC搜索页特殊处理
    select
        5 as page_type_id,
        sum(pv) as pv,
        sum(uv) as uv
    from dw.rpt_trfc_page_kpi
    where ds = '$yesterday' and stat_type = 1 and page_type_id in (51, 52)

union all

    --针对APP
    select
        a.page_type_id,
        sum(pv) as pv,
        sum(uv) as uv
    from dw.rpt_trfc_page_kpi a
    left outer join (
        select distinct
            page_type_id,
            old_page_type_id
        from tandem.mobile_backend_page_url_rule
        where is_delete = 0
    ) b on (a.page_type_id = b.old_page_type_id)
    where a.ds = '$yesterday' and stat_type = 1
    group by a.page_type_id
) t;[/mw_shl_code]
上面的sql中存在两个union all操作,顺序执行下来的话,需要耗时20分钟。

优化策略
分析以上的sql,其中union all前后的三个查询操作并无直接关联,因此没有必要顺序执行,因此优化的思路是让这三个查询操作并行执行,hive提供了如下参数实现job的并行操作:
[mw_shl_code=bash,true]// 开启任务并行执行
set hive.exec.parallel=true;
// 同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;[/mw_shl_code]

方案一
在执行sql时加上上面的两个hive参数,如:
[mw_shl_code=bash,true]set mapred.job.queue.name=pms;
set hive.exec.reducers.max=8;
set mapred.reduce.tasks=8;
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
set mapred.job.name=extract_trfc_page_kpi;

insert overwrite table pms.extract_trfc_page_kpi partition(ds='$yesterday')
select distinct
    page_type_id,
    pv,
    uv,
    '$yesterday' update_time
from
(
    --针对PC、H5
    select
        page_type_id,
        sum(pv) as pv,
        sum(uv) as uv
    from dw.rpt_trfc_page_kpi
    where ds = '$yesterday' and stat_type = 1
    group by page_type_id

union all

    --PC搜索页特殊处理
    select
        5 as page_type_id,
        sum(pv) as pv,
        sum(uv) as uv
    from dw.rpt_trfc_page_kpi
    where ds = '$yesterday' and stat_type = 1 and page_type_id in (51, 52)

union all

    --针对APP
    select
        a.page_type_id,
        sum(pv) as pv,
        sum(uv) as uv
    from dw.rpt_trfc_page_kpi a
    left outer join (
        select distinct
            page_type_id,
            old_page_type_id
        from tandem.mobile_backend_page_url_rule
        where is_delete = 0
    ) b on (a.page_type_id = b.old_page_type_id)
    where a.ds = '$yesterday' and stat_type = 1
    group by a.page_type_id
) t;[/mw_shl_code]

方案二
在hive-site.xml中进行设置,查看当前版本hive的配置参数:

[mw_shl_code=bash,true]hive> set -v;
...
hive.exec.orc.zerocopy=false
hive.exec.parallel=false
hive.exec.parallel.thread.number=8
hive.exec.perf.logger=org.apache.hadoop.hive.ql.log.PerfLogger
hive.exec.rcfile.use.explicit.header=true
hive.exec.rcfile.use.sync.cache=true
hive.exec.reducers.bytes.per.reducer=1000000000
hive.exec.reducers.max=999
hive.exec.rowoffset=false
hive.exec.scratchdir=/tmp/hive-pms
hive.exec.script.allow.partial.consumption=false
hive.exec.script.maxerrsize=100000
hive.exec.script.trust=false
hive.exec.show.job.failure.debug.info=true
...[/mw_shl_code]

这些参数是配置在$HIVE_HOME/conf/hive-site.xml中的,现在在这个配置文件中加入:

[mw_shl_code=bash,true]<property>
    <name>hive.exec.parallel</name>
    <value>true</value>
</property>
<property>
    <name>hive.exec.parallel.thread.number</name>
    <value>16</value>
</property>[/mw_shl_code]

重新启动hive,看到刚刚配置的参数已经生效了:

[mw_shl_code=bash,true]hive> set -v;
...
hive.exec.orc.skip.corrupt.data=false
hive.exec.orc.zerocopy=false
hive.exec.parallel=true
hive.exec.parallel.thread.number=16
hive.exec.perf.logger=org.apache.hadoop.hive.ql.log.PerfLogger
hive.exec.rcfile.use.explicit.header=true
hive.exec.rcfile.use.sync.cache=true
hive.exec.reducers.bytes.per.reducer=1000000000
hive.exec.reducers.max=999
hive.exec.rowoffset=false
hive.exec.scratchdir=/tmp/hive-pms
hive.exec.script.allow.partial.consumption=false
...[/mw_shl_code]


结论
经过测试,添加了这两个参数以后,extract_trfc_page_kpi脚本执行时间从耗时20分钟,优化为耗时3分钟。


欢迎加入about云群425860289432264021 ,云计算爱好者群,关注about云腾讯认证空间

已有(3)人评论

跳转到指定楼层
arBen 发表于 2015-5-16 08:54:28
感谢楼主分享...........
回复

使用道具 举报

xuliang123789 发表于 2016-4-5 19:07:42
谢谢楼主分享,学习一下,赞~~
回复

使用道具 举报

a530491093 发表于 2018-12-5 15:57:54

谢谢楼主分享,学习一下,赞~~
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

推荐上一条 /2 下一条