QQ图片20190419230817.png
一、剖析原始项目
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
