Shell脚本执行hive语句 | hive以日期建立分区表 | linux schedule程序

#!/bin/bash
source /etc/profile;

##################################################
# Author: ouyangyewei                            #
#                                                #
# Content: Combineorder Algorithm                #
##################################################

# change workspace to here
cd /
cd /home/deploy/recsys/workspace/ouyangyewei

# generate product_sell data
yesterday=$(date -d '-1 day' '+%Y-%m-%d')
lastweek=$(date -d '-1 week' '+%Y-%m-%d')

/usr/local/cloud/hive/bin/hive<<EOF 
CREATE EXTERNAL TABLE IF NOT EXISTS product_sell(
category_id bigint,
province_id bigint,
product_id bigint,
price double,
sell_num bigint
)
PARTITIONED BY (ds string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

INSERT OVERWRITE TABLE product_sell PARTITION (ds='$yesterday') select a.category_id, b.good_receiver_province_id as province_id, a.id as product_id, (b.sell_amount/b.sell_num) as price, b.sell_num from product a join (select si.product_id, s.good_receiver_province_id, sum(si.order_item_amount) sell_amount, sum(si.order_item_num) sell_num from so_item si join so s on (si.order_id=s.id) where si.is_gift=0 and si.is_hidden=0 and si.ds between '$lastweek' and '$yesterday' group by s.good_receiver_province_id, si.product_id) b on (a.id=b.product_id);
EOF

# generate yhd_gmv_month data
yesterday=$(date -d '-1 day' '+%Y-%m-%d')
lastmonth=$(date -d '-1 month' '+%Y-%m-%d')

/usr/local/cloud/hive/bin/hive<<EOF 
CREATE EXTERNAL TABLE IF NOT EXISTS yhd_gmv_month(
province_id bigint,
price_area int,
product_id bigint,
sell_num bigint
)
PARTITIONED BY (ds string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

INSERT OVERWRITE TABLE yhd_gmv_month PARTITION (ds='$yesterday') select ssi.province_id, (case when price>0.0 and price<=10.0 then 0 when price>10.0 and price<=20.0 then 1 when price>20.0 and price<=30.0 then 2 when price>30.0 then 3 end) as price_area, ssi.product_id, ssi.sell_num from (select s.good_receiver_province_id as province_id, si.product_id, sum(si.order_item_num) as sell_num, sum(si.order_item_amount)/sum(si.order_item_num) as price from so_item si join so s on (si.order_id=s.id) where si.is_hidden=0 and si.is_gift=0 and si.ds between '$lastmonth' and '$yesterday' group by s.good_receiver_province_id, si.product_id) ssi;
EOF

# exit hive
exit;

# execute the combineorder algorithm job
cd /
cd /home/deploy/recsys/workspace/ouyangyewei/schedule/pms_category_rec_prod
hadoop jar /home/deploy/recsys/workspace/ouyangyewei/schedule/pms_category_rec_prod/recommender-dm-1.0-SNAPSHOT.jar com.yhd.recommender.combineorder.schedule.CombineorderRecommendScheduler

# export "pms_category_rec_prod" data to mysql
cd /
cd /home/deploy/recsys/workspace/ouyangyewei/schedule/pms_category_rec_prod
hadoop jar /home/deploy/recsys/workspace/ouyangyewei/schedule/pms_category_rec_prod/recommender-merchantrank.jar com.yhd.recommender.exporter.db.HdfsToDBProcessor

# export "yhd_gmv_month" data to mysql
cd /
cd /home/deploy/recsys/workspace/ouyangyewei/schedule/yhd_gmv_month
hadoop jar /home/deploy/recsys/workspace/ouyangyewei/schedule/yhd_gmv_month/recommender-merchantrank.jar com.yhd.recommender.exporter.db.HdfsToDBProcessor

Shell脚本执行hive语句 | hive以日期建立分区表 | linux schedule程序,古老的榕树,5-wow.com