脚本化加载文件与转储
1、加载数据到原生表
1.1 介绍
由于每天都会产生大量的日志数据，需要对每天的日志进行加载与清洗以及转储，编写脚本文件后，通过azkaban进行调度即可。
1.2 编写load_data_to_hive_raw_logs.sql
加载数据到hive原生表，注意使用的动态分区，每天只加载前一天的数据。其中#号部分是需要替换成具体的时间值。
--load_data_to_hive_raw_logs.sql
use umeng_big11 ;
load data inpath 'hdfs://mycluster/user/centos/umeng/raw _logs/${hiveconf:ym}/${hiveconf:day}/${hiveconf:hm}' into table raw_logs partition(ym=${hiveconf:ym},day=${hiveconf:day},hm=${hiveconf:hm}) ;
1.3 编写load_data_to_hive_raw_logs.sh
该脚本负责负责调用上面的sql脚本，调用前需要将参数进行填充。
[load_data_to_hive_raw_logs.sh]
#!/bin/bash
cd /home/centos/umeng
if [[ $# = 0 ]] ;
then time=`date -d "-3 minutes" "+%Y%m-%d-%H%M"` ;
else time=$1-$2-$3
fi
#external time variable
echo -n $time > _time
ym=`echo $time | awk -F '-' '{print $1}'`
day=`echo $time | awk -F '-' '{print $2}'`
hm=`echo $time | awk -F '-' '{print $3}'`
hive -hiveconf ym=${ym} -hiveconf day=${day} -hiveconf hm=${hm} -f load_data_to_hive_raw_logs.sql
1.4 修改脚本权限并执行
#增加执行权限
$>chmod +x load_data_to_hive_raw_logs.sh
#调用脚本，指定具体时间
$>./load_data_to_hive_raw_logs.sh 2018 2 4
#使用当前时间
$>./load_data_to_hive_raw_logs.sh
2、叉分并转储
2.1 说明
加载原生表的日志需要进行叉分，时间对齐以及地域信息处理分别转储到5张日志子表中。日志子表也都是分区表，因此查出来的数据需要动态指定分区表。
2.2 叉分startuplogs表
2.2.1 编写sql文件
[fork_startuplogs.sql]
--startuplog,动态分区
use umeng_big11 ;
set hive.cli.print.header=true ;
add jar /soft/hive/lib/umeng_hive.jar ;
create temporary function forkstartuplogs as 'com.oldboy.umeng.hive.udf.ForkStartupLogUDTF' ;
create temporary function forkeventlogs as 'com.oldboy.umeng.hive.udf.ForkEventLogUDTF' ;
create temporary function forkerrorlogs as 'com.oldboy.umeng.hive.udf.ForkErrorLogUDTF' ;
create temporary function forkusagelogs as 'com.oldboy.umeng.hive.udf.ForkUsageLogUDTF' ;
create temporary function forkpagelogs as 'com.oldboy.umeng.hive.udf.ForkPageLogUDTF' ;
insert into startuplogs partition(ym,day,hm)
select
t.appChannel ,
t.appId ,
t.appPlatform ,
t.appVersion ,
t.brand ,
t.carrier ,
t.country ,
t.createdAtMs ,
t.deviceId ,
t.deviceStyle ,
t.ipAddress ,
t.network ,
t.osType ,
t.province ,
t.screenSize ,
t.tenantId ,
date_format(cast(t.createdatms as timestamp) , 'yyyyMM') ,
date_format(cast(t.createdatms as timestamp) , 'dd') ,
date_format(cast(t.createdatms as timestamp) , 'HHmm')
from
(
select
--动态传参叉分函数
${hiveconf:func}(servertimestr , clienttimems , clientip , log)
from
raw_logs
where
status = 200
and ym=${hiveconf:ym}
and day = ${hiveconf:day}
and hm = ${hiveconf:hm}
)t
2.2.2 编写sh文件
shell脚本需要从_time文件中提取时间值，然后传入sql文件名和叉分函数名。
[fork_logs.sh]
#!/bin/bash
cd /home/centos/umeng
#取第一个参数作为叉分函数
func=${1}
time=`cat _time`
ym=`echo -n $time | awk -F '-' '{print $1}'`
day=`echo -n $time | awk -F '-' '{print $2}'`
hm=`echo -n $time | awk -F '-' '{print $3}'`
hive -hiveconf ym=${ym} -hiveconf day=${day} -hiveconf hm=${hm} -hiveconf func=${2} -f ${1}
2.2.3 执行脚本
#指定叉分函数
$>./fork_logs.sh fork_startuplogs.sql forkstartuplogs
2.3 叉分eventlogs表
2.3.1 编写sql
[fork_eventlogs.sql]
--eventlog,动态分区
use umeng_big11 ;
add jar /soft/hive/lib/umeng_hive.jar ;
create temporary function forkstartuplogs as 'com.oldboy.umeng.hive.udf.ForkStartupLogUDTF' ;
create temporary function forkeventlogs as 'com.oldboy.umeng.hive.udf.ForkEventLogUDTF' ;
create temporary function forkerrorlogs as 'com.oldboy.umeng.hive.udf.ForkErrorLogUDTF' ;
create temporary function forkusagelogs as 'com.oldboy.umeng.hive.udf.ForkUsageLogUDTF' ;
create temporary function forkpagelogs as 'com.oldboy.umeng.hive.udf.ForkPageLogUDTF' ;
insert into eventlogs partition(ym,day,hm)
select
t.appChannel ,
t.appId ,
t.appPlatform ,
t.appVersion ,
t.createdAtMs ,
t.deviceId ,
t.deviceStyle ,
t.eventDurationSecs,
t.eventId ,
t.osType ,
t.tenantId ,
date_format(cast(t.createdatms as timestamp) , 'yyyyMM') ,
date_format(cast(t.createdatms as timestamp) , 'dd') ,
date_format(cast(t.createdatms as timestamp) , 'HHmm')
from
(
select
--动态传参叉分函数
${hiveconf:func}(servertimestr , clienttimems , clientip , log)
from
raw_logs
where
status = 200
and ym=${hiveconf:ym} --年月
and day = ${hiveconf:day} --日
and hm = ${hiveconf:hm} --时分
)t
2.3.2 执行脚本
$>#指定叉分函数
$>./fork_logs.sh fork_eventlogs.sql forkeventlogs
2.4 叉分errorlogs表
2.4.1 编写sql
[fork_errorlogs.sql]
--eventlog,动态分区
use umeng_big11 ;
add jar /soft/hive/lib/umeng_hive.jar ;
create temporary function forkstartuplogs as 'com.oldboy.umeng.hive.udf.ForkStartupLogUDTF' ;
create temporary function forkeventlogs as 'com.oldboy.umeng.hive.udf.ForkEventLogUDTF' ;
create temporary function forkerrorlogs as 'com.oldboy.umeng.hive.udf.ForkErrorLogUDTF' ;
create temporary function forkusagelogs as 'com.oldboy.umeng.hive.udf.ForkUsageLogUDTF' ;
create temporary function forkpagelogs as 'com.oldboy.umeng.hive.udf.ForkPageLogUDTF' ;
insert into errorlogs partition(ym,day,hm)
select
t.appChannel ,
t.appId ,
t.appPlatform ,
t.appVersion ,
t.createdAtMs ,
t.deviceId ,
t.deviceStyle ,
t.errorBrief ,
t.errorDetail ,
t.osType ,
t.tenantId ,
date_format(cast(t.createdatms as timestamp) , 'yyyyMM') ,
date_format(cast(t.createdatms as timestamp) , 'dd') ,
date_format(cast(t.createdatms as timestamp) , 'HHmm')
from
(
select
--动态传参叉分函数
${hiveconf:func}(servertimestr , clienttimems , clientip , log)
from
raw_logs
where
status = 200
and ym=${hiveconf:ym} --年月
and day = ${hiveconf:day} --日
and hm = ${hiveconf:hm} --时分
)t
2.4.2 执行脚本
$>#指定叉分函数
$>./fork_logs.sh fork_errorlogs.sql forkerrorlogs
2.5 叉分usagelogs表
2.5.1 编写sql
[fork_usagelogs.sql]
--usagelogs,动态分区
use umeng_big11 ;
add jar /soft/hive/lib/umeng_hive.jar ;
create temporary function forkstartuplogs as 'com.oldboy.umeng.hive.udf.ForkStartupLogUDTF' ;
create temporary function forkeventlogs as 'com.oldboy.umeng.hive.udf.ForkEventLogUDTF' ;
create temporary function forkerrorlogs as 'com.oldboy.umeng.hive.udf.ForkErrorLogUDTF' ;
create temporary function forkusagelogs as 'com.oldboy.umeng.hive.udf.ForkUsageLogUDTF' ;
create temporary function forkpagelogs as 'com.oldboy.umeng.hive.udf.ForkPageLogUDTF' ;
insert into usagelogs partition(ym,day,hm)
select
t.appChannel ,
t.appId ,
t.appPlatform ,
t.appVersion ,
t.createdAtMs ,
t.deviceId ,
t.deviceStyle ,
t.osType ,
t.singleDownloadTraffic,
t.singleUploadTraffic ,
t.singleUseDurationSecs,
t.tenantId
date_format(cast(t.createdatms as timestamp) , 'yyyyMM') ,
date_format(cast(t.createdatms as timestamp) , 'dd') ,
date_format(cast(t.createdatms as timestamp) , 'HHmm')
from
(
select
--动态传参叉分函数
${hiveconf:func}(servertimestr , clienttimems , clientip , log)
from
raw_logs
where
status = 200
and ym=${hiveconf:ym}
and day = ${hiveconf:day}
and hm = ${hiveconf:hm}
)t
2.5.2 执行脚本
$>#指定叉分函数
$>./fork_logs.sh fork_usagelogs.sql forkusagelogs
2.6 叉分pagelogs表
2.6.1 编写sql
[fork_pagelogs.sql]
--pagelog,动态分区
use umeng_big11 ;
add jar /soft/hive/lib/umeng_hive.jar ;
create temporary function forkstartuplogs as 'com.oldboy.umeng.hive.udf.ForkStartupLogUDTF' ;
create temporary function forkeventlogs as 'com.oldboy.umeng.hive.udf.ForkEventLogUDTF' ;
create temporary function forkerrorlogs as 'com.oldboy.umeng.hive.udf.ForkErrorLogUDTF' ;
create temporary function forkusagelogs as 'com.oldboy.umeng.hive.udf.ForkUsageLogUDTF' ;
create temporary function forkpagelogs as 'com.oldboy.umeng.hive.udf.ForkPageLogUDTF' ;
insert into pagelogs partition(ym,day,hm)
select
t.appChannel ,
t.appId ,
t.appPlatform ,
t.appVersion ,
t.createdAtMs ,
t.deviceId ,
t.deviceStyle ,
t.nextPage ,
t.osType ,
t.pageId ,
t.pageViewCntInSession,
t.stayDurationSecs ,
t.tenantId ,
t.visitIndex ,
date_format(cast(t.createdatms as timestamp) , 'yyyyMM') ,
date_format(cast(t.createdatms as timestamp) , 'dd') ,
date_format(cast(t.createdatms as timestamp) , 'HHmm')
from
(
select
--动态传参叉分函数
${hiveconf:func}(servertimestr , clienttimems , clientip , log)
from
raw_logs
where
status = 200
and ym=${hiveconf:ym}
and day = ${hiveconf:day}
and hm = ${hiveconf:hm}
)t
2.6.2 执行脚本
$>#指定叉分函数
$>./fork_logs.sh fork_pagelogs.sql forkpagelogs
3、总结
编写5个sql文件，一个shell脚本文件，通过传递参数给执行脚本，动态执行每张日志表的叉分与转储工作。