通过Shell数组传参控制Sql脚本串并行调度一例

一、目录环境 [hadoop@emr-worker-9 UserPortrait]$ pwd /home/hadoop/nisj/UserPortrait [hadoop@emr-worker-9 UserPortrait]$ ll total 616 -rw-r--r-- 1 hadoop hadoop 2516 Mar 7 18:21 indexCalc-barrage-byUidOnly.sql -rw-r--r-- 1 hadoop hadoop 4160 Mar 8 16:38 indexCalc-gift-byUidOnly.sql -rw-r--r-- 1 hadoop hadoop 4562 Mar 7 14:29 indexCalc-view-byGameId.sql -rw-r--r-- 1 hadoop hadoop 4562 Mar 7 14:29 indexCalc-view-byRoomId.sql -rw-r--r-- 1 hadoop hadoop 4529 Mar 7 14:29 indexCalc-view-byTagId.sql -rw-r--r-- 1 hadoop hadoop 4411 Mar 7 14:29 indexCalc-view-byUidOnly.sql -rw-r--r-- 1 hadoop hadoop 4292 Mar 7 14:29 indexCalc-view-historyBehavior.sql -rw------- 1 hadoop hadoop 565692 Mar 8 17:55 nohup.out -rwxr-x--- 1 hadoop hadoop 569 Mar 8 16:38 user_portrait_view_moreThread.sh 二、串并行调度的Shell脚本 [hadoop@emr-worker-9 UserPortrait]$ cat user_portrait_view_moreThread.sh  #!/usr/bin/env bash echo `date`,'数据跑批开始...' indexCalc_sqlFile_array=( indexCalc-view-byGameId.sql indexCalc-view-byRoomId.sql indexCalc-view-byTagId.sql indexCalc-view-byUidOnly.sql indexCalc-barrage-byUidOnly.sql indexCalc-gift-byUidOnly.sql ) for indexCalc_sqlFile in ${indexCalc_sqlFile_array[*]} do { /usr/lib/hive-current/bin/hive -f /home/hadoop/nisj/UserPortrait/$indexCalc_sqlFile sleep 1 } & done wait /usr/lib/hive-current/bin/hive -f /home/hadoop/nisj/UserPortrait/indexCalc-view-historyBehavior.sql sleep 1 echo `date`,'数据跑批完成!' 三、执行的Sql示例

/home/hadoop/nisj/UserPortrait/indexCalc-view-byGameId.sql

-- 观看时长等的计算 -- 观看总时长、总次数；有效观看总时长、有效观看次数、有效平均时长；第一次观看时间 drop table if exists rcd_static_view_bygameid_basic; create table rcd_static_view_bygameid_basic as with Tab_recommend_data_view_byDay as( select uid,gameid,sum(view_time) view_time,pt_day from recommend_data_view where uid>0 group by uid,gameid,pt_day) select uid,gameid, sum(view_time) total_view_time, count(view_time) total_view_times, sum(case when view_time>=5 then view_time else 0 end ) total_view_time_effective, sum(case when view_time>=5 then 1 else 0 end ) total_view_times_effective, sum(case when view_time>=5 then view_time else 0 end )/sum(case when view_time>=5 then 1 else 0 end ) avg_view_time_effective, min(pt_day) frist_view_day, max(pt_day) newest_view_day from Tab_recommend_data_view_byDay group by uid,gameid ; -- 最后一次、倒数第二次有效观看及单次有效最大观看时长相关（时长与时间） drop table if exists rcd_static_view_bygameid_rank; create table rcd_static_view_bygameid_rank as with Tab_recommend_data_view_byDay as( select uid,gameid,sum(view_time) view_time,pt_day from recommend_data_view where uid>0 group by uid,gameid,pt_day) select uid,gameid, max(case when effective_desc_rk=1 then pt_day end) tailender_effective_day, max(case when effective_desc_rk=1 then view_time end) tailender_effective_view_time, max(case when effective_desc_rk=2 then pt_day end) penul_timate_effective_day, max(case when effective_desc_rk=2 then view_time end) penul_timate_effective_view_time, max(case when effective_desc_rk2=1 then pt_day end) max_effective_view_day, max(case when effective_desc_rk2=1 then view_time end) max_effective_view_time from ( select uid,gameid,view_time,pt_day, row_number()over(partition by uid,gameid order by pt_day desc) effective_desc_rk, row_number()over(partition by uid,gameid order by view_time desc) effective_desc_rk2 from Tab_recommend_data_view_byDay where view_time>=5 ) x1 group by uid,gameid ; -- 最近七天有效观看时长 drop table if exists rcd_static_view_bygameid_last7day; create table rcd_static_view_bygameid_last7day as with Tab_recommend_data_view_byDay as( select uid,gameid,sum(view_time) view_time,pt_day from recommend_data_view where uid>0 group by uid,gameid,pt_day) select uid,gameid, sum(case when view_time>=5 then view_time else 0 end ) total_view_time_effective, sum(case when view_time>=5 then 1 else 0 end ) total_view_times_effective, sum(case when view_time>=5 then view_time else 0 end )/sum(case when view_time>=5 then 1 else 0 end ) avg_view_time_effective, min(pt_day) frist_view_day, max(pt_day) newest_view_day from Tab_recommend_data_view_byDay where view_time>=5 and pt_day between date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),7) and date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) group by uid,gameid ; -- 按周统计每周有效观看时长 drop table if exists rcd_static_view_bygameid_byweek; create table rcd_static_view_bygameid_byweek as with Tab_recommend_data_view_byDay as( select uid,gameid,sum(view_time) view_time,pt_day from recommend_data_view where uid>0 group by uid,gameid,pt_day) select uid,gameid,concat(year(pt_day),'@',weekofyear(pt_day)) week_no, sum(case when view_time>=5 then view_time else 0 end ) total_view_time_effective, sum(case when view_time>=5 then 1 else 0 end ) total_view_times_effective, sum(case when view_time>=5 then view_time else 0 end )/sum(case when view_time>=5 then 1 else 0 end ) avg_view_time_effective, min(pt_day) frist_view_day, max(pt_day) newest_view_day from Tab_recommend_data_view_byDay where view_time>=5 group by uid,gameid,concat(year(pt_day),'@',weekofyear(pt_day)) ; -- 按月统计每月有效观看时长 drop table if exists rcd_static_view_bygameid_bymonth; create table rcd_static_view_bygameid_bymonth as with Tab_recommend_data_view_byDay as( select uid,gameid,sum(view_time) view_time,pt_day from recommend_data_view where uid>0 group by uid,gameid,pt_day) select uid,gameid,concat(year(pt_day),'@',month(pt_day)) month_no, sum(case when view_time>=5 then view_time else 0 end ) total_view_time_effective, sum(case when view_time>=5 then 1 else 0 end ) total_view_times_effective, sum(case when view_time>=5 then view_time else 0 end )/sum(case when view_time>=5 then 1 else 0 end ) avg_view_time_effective, min(pt_day) frist_view_day, max(pt_day) newest_view_day from Tab_recommend_data_view_byDay where view_time>=5 group by uid,gameid,concat(year(pt_day),'@',month(pt_day)) ;