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

一、目录环境

[hadoop@emr-worker-9 UserPortrait]$ pwd/home/hadoop/nisj/UserPortrait[hadoop@emr-worker-9 UserPortrait]$ lltotal 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 bashecho `date`,'数据跑批开始...'indexCalc_sqlFile_array=(indexCalc-view-byGameId.sqlindexCalc-view-byRoomId.sqlindexCalc-view-byTagId.sqlindexCalc-view-byUidOnly.sqlindexCalc-barrage-byUidOnly.sqlindexCalc-gift-byUidOnly.sql)for indexCalc_sqlFile in ${indexCalc_sqlFile_array[*]}do{/usr/lib/hive-current/bin/hive -f /home/hadoop/nisj/UserPortrait/$indexCalc_sqlFilesleep 1} &donewait/usr/lib/hive-current/bin/hive -f /home/hadoop/nisj/UserPortrait/indexCalc-view-historyBehavior.sqlsleep 1echo `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 aswith 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_dayfrom 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 aswith 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_timefrom (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_rk2from Tab_recommend_data_view_byDaywhere view_time>=5) x1group by uid,gameid;-- 最近七天有效观看时长drop table if exists rcd_static_view_bygameid_last7day;create table rcd_static_view_bygameid_last7day aswith 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_dayfrom 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 aswith 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_dayfrom Tab_recommend_data_view_byDay where view_time>=5group 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 aswith 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_dayfrom Tab_recommend_data_view_byDay where view_time>=5group by uid,gameid,concat(year(pt_day),'@',month(pt_day));