shell脚本:
#!/bin/sh
source ExitCodeCheck.sh
opts=$@
getparam(){
arg=$1
echo $opts |xargs -n1 |cut -b 2- |awk -F'=' '{if($1=="'"$arg"'") print $2}'
}
IncStart=`getparam inc_start`
IncEnd=`getparam inc_end`
oracle_connection=`getparam jdbc_str`
oracle_username=`getparam db_user`
oracle_password=`getparam db_psw`
dataName=`getparam db_sid`
queueName=`getparam hdp_queue`
hdfshostname=`getparam hdfs_host`;
IncStartYear=`echo ${IncStart:0:4}`;
IncStartMonth=`echo ${IncStart:4:2}`;
IncStartDay=`echo ${IncStart:6:2}`;
IncStartAll=${IncStartYear}"-"${IncStartMonth}"-"${IncStartDay}" 00:00:00.0";
IncEndYear=`echo ${IncEnd:0:4}`;
IncEndMonth=`echo ${IncEnd:4:2}`;
IncEndDay=`echo ${IncEnd:6:2}`;
IncEndAll=${IncEndYear}"-"${IncEndMonth}"-"${IncEndDay}" 00:00:00.0";
#任务名取脚本名
job_name=$0
#BAS 1
hive -v -e "set mapred.job.queue.name=${queueName};
set mapred.job.name=${job_name}_2;
use sx_360_safe;
INSERT OVERWRITE table an_pafc_safe.sub_zeb_bp_info_count PARTITION (day='${IncStartAll}')
--资讯编码各时间点击次数统计表
select substr(t1.created_date, 1, 10) as statis_date,
t1.code,
t1.info_no,
sum(case when substr(created_date,12,2) = '00' then 1 else 0 end) cnt00,
sum(case when substr(created_date,12,2) = '01' then 1 else 0 end) cnt01,
sum(case when substr(created_date,12,2) = '02' then 1 else 0 end) cnt02,
sum(case when substr(created_date,12,2) = '03' then 1 else 0 end) cnt03,
sum(case when substr(created_date,12,2) = '04' then 1 else 0 end) cnt04,
sum(case when substr(created_date,12,2) = '05' then 1 else 0 end) cnt05,
sum(case when substr(created_date,12,2) = '06' then 1 else 0 end) cnt06,
sum(case when substr(created_date,12,2) = '07' then 1 else 0 end) cnt07,
sum(case when substr(created_date,12,2) = '08' then 1 else 0 end) cnt08,
sum(case when substr(created_date,12,2) = '09' then 1 else 0 end) cnt09,
sum(case when substr(created_date,12,2) = '10' then 1 else 0 end) cnt10,
sum(case when substr(created_date,12,2) = '11' then 1 else 0 end) cnt11,
sum(case when substr(created_date,12,2) = '12' then 1 else 0 end) cnt12,
sum(case when substr(created_date,12,2) = '13' then 1 else 0 end) cnt13,
sum(case when substr(created_date,12,2) = '14' then 1 else 0 end) cnt14,
sum(case when substr(created_date,12,2) = '15' then 1 else 0 end) cnt15,
sum(case when substr(created_date,12,2) = '16' then 1 else 0 end) cnt16,
sum(case when substr(created_date,12,2) = '17' then 1 else 0 end) cnt17,
sum(case when substr(created_date,12,2) = '18' then 1 else 0 end) cnt18,
sum(case when substr(created_date,12,2) = '19' then 1 else 0 end) cnt19,
sum(case when substr(created_date,12,2) = '20' then 1 else 0 end) cnt20,
sum(case when substr(created_date,12,2) = '21' then 1 else 0 end) cnt21,
sum(case when substr(created_date,12,2) = '22' then 1 else 0 end) cnt22,
sum(case when substr(created_date,12,2) = '23' then 1 else 0 end) cnt23
from an_pafc_safe.sx_ela_bp_info t1
where substr(t1.created_date, 1, 10) < to_date('${IncStartAll}')
and t1.info_no is not null
and length(t1.info_no)<> 0
group by substr(t1.created_date, 1, 10),
t1.code,
t1.info_no
";
exitCodeCheck $?