美文网首页Hadoop
基于ETL离线项目的改造

基于ETL离线项目的改造

作者: 喵星人ZC | 来源:发表于2019-04-27 19:55 被阅读90次
QQ图片20190419230817.png

原始项目Hadoop MR ETL离线项目

一、剖析原始项目
1、shell脚本为

#/bin/bash

source ~/.bash_profile

if [ $# != 1 ] ; then
echo "Usage: g6_mr_etl.sh <dateString>"
echo "E.g.: g6_mr_etl.sh 20190402"
exit 1;
fi


process_date=$1

echo -e "\033[36m###### step1:MR ETL ######\033[0m"  
hadoop jar /home/hadoop/soul/g6/lib/hadoop-1.0.jar com.ruoze.hadoop.mapreduce.LogETLDriver /g6/hadoop/accesslog/$process_date/ /g6/hadoop/access/output/day=$process_date


echo -e "\033[36m###### step2:Mv Data to DW ###### \033[0m"  
hadoop fs -rmr /g6/hadoop/access/clear/day=$process_date
hadoop fs -mkdir /g6/hadoop/access/clear/day=$process_date
hadoop fs -mv /g6/hadoop/access/output/day=$process_date/part* /g6/hadoop/access/clear/day=$process_date/



echo -e "\033[36m###### step3:Alter metadata ######\033[0m"  
database=g6_hadoop
hive -e "use ${database}; alter table g6_access add if not exists partition(day=$process_date);"

在整个过程中(Log--> MR ETL -->DW)都是采用的Text，Log格式为Text，MR输出的还是Text，DW的表g6_access采用的也是TextFile

2、所以我们可以修改DW的表为parquet/orc格式来提高性能

3、我们还可以让MR输出时就采用parquet格式输出

相比较2和3，建议使用2.因为3涉及到修改代码，而2只是创建一个parquet表而已，比较简单方便。

三、改造及性能测试

1、修改shell，在shell跑完，也就是hive临时表g6_access可以查询到数据后，增加step4将数据移动到parquet表

#/bin/bash

source ~/.bash_profile

if [ $# != 1 ] ; then
echo "Usage: g6_mr_etl.sh <dateString>"
echo "E.g.: g6_mr_etl.sh 20190402"
exit 1;
fi


process_date=$1

echo -e "\033[36m###### step1:MR ETL ######\033[0m"  
hadoop jar /home/hadoop/soul/g6/lib/hadoop-1.0.jar com.ruoze.hadoop.mapreduce.LogETLDriver /g6/hadoop/accesslog/$process_date/ /g6/hadoop/access/output/day=$process_date


echo -e "\033[36m###### step2:Mv Data to Temp Table  ###### \033[0m"  
hadoop fs -rmr /g6/hadoop/access/clear/day=$process_date
hadoop fs -mkdir /g6/hadoop/access/clear/day=$process_date
hadoop fs -mv /g6/hadoop/access/output/day=$process_date/part* /g6/hadoop/access/clear/day=$process_date/

echo -e "\033[36m###### step3:reflush metadata partition ######\033[0m"  
database=g6_hadoop
hive -e "use ${database}; alter table g6_access add if not exists partition(day=$process_date);"


echo -e "\033[36m###### step4:Mv Data to Parquet Table ###### \033[0m"  
hive -e "create table g6_access_parquet stored as parquet as select * from g6_access;"

2、查询parquet表
select domain,count(*) from g6_access group by domain;

hive (g6_hadoop)> select domain,count(*) from g6_access_parquet group by domain;
Query ID = hadoop_20190427174646_d3e79299-1e9c-42de-b781-8637b8e94acd
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1555760099632_0039, Tracking URL = http://hadoop000:8088/proxy/application_1555760099632_0039/
Kill Command = /home/hadoop/soul/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1555760099632_0039
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2019-04-27 19:49:40,091 Stage-1 map = 0%,  reduce = 0%
2019-04-27 19:49:47,608 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.38 sec
2019-04-27 19:49:54,927 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.6 sec
MapReduce Total cumulative CPU time: 3 seconds 600 msec
Ended Job = job_1555760099632_0039
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 3.6 sec   HDFS Read: 85383 HDFS Write: 76 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 600 msec
OK
domain  _c1
v1.go2yd.com    74908
v2.go2yd.com    74795
v3.go2yd.com    75075
v4.go2yd.com    75222
Time taken: 24.349 seconds, Fetched: 4 row(s)

Time taken: 24.349 seconds

3、查询TextFile表

hive (g6_hadoop)> select domain,count(*) from g6_access group by domain;
Query ID = hadoop_20190427174646_d3e79299-1e9c-42de-b781-8637b8e94acd
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1555760099632_0038, Tracking URL = http://hadoop000:8088/proxy/application_1555760099632_0038/
Kill Command = /home/hadoop/soul/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1555760099632_0038
Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1
2019-04-27 18:57:40,521 Stage-1 map = 0%,  reduce = 0%
2019-04-27 18:57:52,799 Stage-1 map = 9%,  reduce = 0%, Cumulative CPU 2.48 sec
2019-04-27 18:57:54,959 Stage-1 map = 19%,  reduce = 0%, Cumulative CPU 4.97 sec
2019-04-27 18:57:56,106 Stage-1 map = 60%,  reduce = 0%, Cumulative CPU 6.18 sec
2019-04-27 18:57:57,157 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 7.4 sec
2019-04-27 18:58:03,494 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.55 sec
MapReduce Total cumulative CPU time: 8 seconds 550 msec
Ended Job = job_1555760099632_0038
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 2  Reduce: 1   Cumulative CPU: 8.55 sec   HDFS Read: 2509842 HDFS Write: 76 SUCCESS
Total MapReduce CPU Time Spent: 8 seconds 550 msec
OK
domain  _c1
v1.go2yd.com    74908
v2.go2yd.com    74795
v3.go2yd.com    75075
v4.go2yd.com    75222
Time taken: 31.756 seconds, Fetched: 4 row(s)

Time taken: 31.756 seconds

对比会发现parquet性能比TextFIle表性能好。

4、其实我们将数据导入到parquet表后，应该删除temp表g6_access的内容，所以最后shell应该是

#/bin/bash

source ~/.bash_profile

if [ $# != 1 ] ; then
echo "Usage: g6_mr_etl.sh <dateString>"
echo "E.g.: g6_mr_etl.sh 20190402"
exit 1;
fi


process_date=$1

echo -e "\033[36m###### step1:MR ETL ######\033[0m"  
hadoop jar /home/hadoop/soul/g6/lib/hadoop-1.0.jar com.ruoze.hadoop.mapreduce.LogETLDriver /g6/hadoop/accesslog/$process_date/ /g6/hadoop/access/output/day=$process_date


echo -e "\033[36m###### step2:Mv Data to Temp Table  ###### \033[0m"  
hadoop fs -rmr /g6/hadoop/access/clear/day=$process_date
hadoop fs -mkdir /g6/hadoop/access/clear/day=$process_date
hadoop fs -mv /g6/hadoop/access/output/day=$process_date/part* /g6/hadoop/access/clear/day=$process_date/

echo -e "\033[36m###### step3:reflush metadata partition ######\033[0m"  
database=g6_hadoop
hive -e "use ${database}; alter table g6_access add if not exists partition(day=$process_date);"


echo -e "\033[36m###### step4:Mv Data to Parquet Table ###### \033[0m"  
hive -e "create table g6_access_parquet stored as parquet as select * from g6_access;"

hadoop fs -rmr /g6/hadoop/access/clear/day=$process_date

相关文章

网友评论

    本文标题：基于ETL离线项目的改造

    本文链接：https://www.haomeiwen.com/subject/wdyhgqtx.html

    延伸阅读

    深度阅读

    • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

    栏目导航

    热点阅读

    Hadoop

    关于我们|服务条款|联系我们|基于ETL离线项目的改造|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！