Hive 动态作业

#!/bin/bash
source /u/users/$USER/.bash_profile

logPath="/u/users/$USER/daily_batch/log/"
current_date=$(date +"%Y-%m-%d" -d "+8hour")
fullPath=$logPath"/"$current_date

#新建目录
if [ ! -d "$fullPath" ];
then
    mkdir -p -m 774 "$fullPath"
fi

logName='test.log'
fullFileName=$fullPath"/"$logName

echo "----------------------------------------------------------------------------------------------------"
echo $USER
echo $current_date
echo "目录:" $fullPath
echo "目录文件:" $fullFileName


echo "----------------------------------------------------------------------------------------------------"
v_date_1_start=`date -d "-7 day +8hour" +'%Y-%m-%d'`
v_date_2_end=`date -d "-1 day +8hour" +'%Y-%m-%d'`

# 当有传入日期参数合法有效,按传入参数执行;没有参数或者无效参数时,按指定日期执行
if [[ $1 =~ [0-9]{4}-[0-9]{2}-[0-9]{2} && (${1:0:4} > '1999' && ${1:0:4} < '2050' ) && (${1:5:2} > '00' && ${1:5:2} < '13') && (${1:8:2} > '00' && ${1:8:2} < '32') ]]
then
    v_date_1_start=$1
fi
if [[ $2 =~ [0-9]{4}-[0-9]{2}-[0-9]{2} && (${1:0:4} > '1999' && ${1:0:4} < '2050' ) && (${1:5:2} > '00' && ${1:5:2} < '13') && (${1:8:2} > '00' && ${1:8:2} < '32') ]]
then
    v_date_2_end=$2
fi

## 打印输出 ##
executeFilePath=$(dirname $(readlink -f "$0"))
executeFileName=$0
echo "当前程序的路径和名称:" $executeFilePath/$executeFileName

echo "参数1:" $v_date_1_start
echo "参数2:" $v_date_2_end
echo "----------------------------------------------------------------------------------------------------"


hive -e "

set tez.queue.name=ecbireport;
SET hive.exec.dynamic.partition =true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.dynamic.partitions.pernode=100000;

insert overwrite table vn09jj5.test40_report
partition(channel,paid_date)
select  
 count(distinct order_id) as orders
 ,count(distinct delivery_phone) as customers
 ,sum(gmv) as sales
 ,channel,paid_date
from cn_ec_bi_dl_secure.wm_order_line
where channel in ('SNG','JDDJ','WMDJ')
and paid_date >='${v_date_1_start}' and paid_date <='${v_date_2_end}'
group by channel,paid_date
;
"

isOK=`echo $?`
echo $isOK

#(0=成功, 1=失败)
if [ "$isOK" == 0 ];
then
    echo "[`date -d "+8hour"`]"  $executeFilePath/$executeFileName "- 执行成功" 
    
    echo "[`date -d "+8hour"`] test40_report - 执行成功" >> $fullFileName
    echo "--------------------------------------------------------------" >> $fullFileName
    
else
    echo "[`date -d "+8hour"`]"  $executeFilePath/$executeFileName "- 执行失败" 
    
    echo "[`date -d "+8hour"`] test40_report - 执行失败" >> $fullFileName
    echo "--------------------------------------------------------------" >> $fullFileName
fi    
echo "----------------------------------------------------------------------------------------------------"

 

#!/bin/bash
source /u/users/$USER/.bash_profile

logPath="/u/users/$USER/daily_batch/log/"
current_date=$(date +"%Y-%m-%d" -d "+8hour")
fullPath=$logPath"/"$current_date

#新建目录
if [ ! -d "$fullPath" ];
then
    mkdir -p -m 774 "$fullPath"
fi

logName='test.log'
fullFileName=$fullPath"/"$logName


echo "----------------------------------------------------------------------------------------------------"
echo $USER
echo $current_date
echo "目录:" $fullPath
echo "目录文件:" $fullFileName

echo "----------------------------------------------------------------------------------------------------"
v_date_1_start=`date -d "-7 day +8hour" +'%Y-%m-%d'`
v_date_2_end=`date -d "-1 day +8hour" +'%Y-%m-%d'`

# 当有传入日期参数合法有效,按传入参数执行;没有参数或者无效参数时,按指定日期执行
if [[ $1 =~ [0-9]{4}-[0-9]{2}-[0-9]{2} && (${1:0:4} > '1999' && ${1:0:4} < '2050' ) && (${1:5:2} > '00' && ${1:5:2} < '13') && (${1:8:2} > '00' && ${1:8:2} < '32') ]]
then
    v_date_1_start=$1
fi
if [[ $2 =~ [0-9]{4}-[0-9]{2}-[0-9]{2} && (${1:0:4} > '1999' && ${1:0:4} < '2050' ) && (${1:5:2} > '00' && ${1:5:2} < '13') && (${1:8:2} > '00' && ${1:8:2} < '32') ]]
then
    v_date_2_end=$2
fi

## 打印输出 ##
executeFilePath=$(dirname $(readlink -f "$0"))
executeFileName=$0
echo "当前程序的路径和名称:" $executeFilePath/$executeFileName

echo "参数1:" $v_date_1_start
echo "参数2:" $v_date_2_end
echo "----------------------------------------------------------------------------------------------------"

store_id=1094
echo $store_id

hive -d store_id=$store_id -e "

set tez.queue.name=ecbireport;
SET hive.exec.dynamic.partition =true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.dynamic.partitions.pernode=100000;

insert overwrite table vn09jj5.test50_report
partition(channel,paid_date)
select  
 count(distinct order_id) as orders
 ,count(distinct delivery_phone) as customers
 ,sum(gmv) as sales
 ,channel,paid_date
from cn_ec_bi_dl_secure.wm_order_line
where channel in ('SNG','JDDJ','WMDJ')
and paid_date >='${v_date_1_start}' and paid_date <='${v_date_2_end}'
and store_id=${store_id} 
group by channel,paid_date
;
"  >> $fullFileName 2>&1

isOK=`echo $?`
echo $isOK

#(0=成功, 1=失败)
if [ "$isOK" == 0 ];
then
    echo "[`date -d "+8hour"`]"  $executeFilePath/$executeFileName "- 执行成功" 
    
    echo "[`date -d "+8hour"`] test50_report - 执行成功" >> $fullFileName
    echo "--------------------------------------------------------------" >> $fullFileName
    
else
    echo "[`date -d "+8hour"`]"  $executeFilePath/$executeFileName "- 执行失败" 
    
    echo "[`date -d "+8hour"`] test50_report - 执行失败" >> $fullFileName
    echo "--------------------------------------------------------------" >> $fullFileName
fi    
echo "----------------------------------------------------------------------------------------------------"

 

#!/bin/bash
source /u/users/$USER/.bash_profile

logPath="/u/users/$USER/daily_batch/log/"
current_date=$(date +"%Y-%m-%d" -d "+8hour")
fullPath=$logPath"/"$current_date

#新建目录
if [ ! -d "$fullPath" ];
then
    mkdir -p -m 774 "$fullPath"
fi

logName='test.log'
fullFileName=$fullPath"/"$logName

echo "----------------------------------------------------------------------------------------------------"
echo $USER
echo $current_date
echo "目录:" $fullPath
echo "目录文件:" $fullFileName


echo "----------------------------------------------------------------------------------------------------"
v_date_1_start=`date -d "-7 day +8hour" +'%Y-%m-%d'`
v_date_2_end=`date -d "-1 day +8hour" +'%Y-%m-%d'`

# 当有传入日期参数合法有效,按传入参数执行;没有参数或者无效参数时,按指定日期执行
if [[ $1 =~ [0-9]{4}-[0-9]{2}-[0-9]{2} && (${1:0:4} > '1999' && ${1:0:4} < '2050' ) && (${1:5:2} > '00' && ${1:5:2} < '13') && (${1:8:2} > '00' && ${1:8:2} < '32') ]]
then
    v_date_1_start=$1
fi
if [[ $2 =~ [0-9]{4}-[0-9]{2}-[0-9]{2} && (${1:0:4} > '1999' && ${1:0:4} < '2050' ) && (${1:5:2} > '00' && ${1:5:2} < '13') && (${1:8:2} > '00' && ${1:8:2} < '32') ]]
then
    v_date_2_end=$2
fi

## 打印输出 ##
executeFilePath=$(dirname $(readlink -f "$0"))
executeFileName=$0
echo "当前程序的路径和名称:" $executeFilePath/$executeFileName

echo "参数1:" $v_date_1_start
echo "参数2:" $v_date_2_end
echo "----------------------------------------------------------------------------------------------------"


hive -hivevar v_date_1_start=$v_date_1_start -hivevar v_date_2_end=$v_date_2_end -f /u/users/$USER/test60_report.sql

isOK=`echo $?`
echo $isOK

#(0=成功, 1=失败)
if [ "$isOK" == 0 ];
then
    echo "[`date -d "+8hour"`]"  $executeFilePath/$executeFileName "- 执行成功" 
    
    echo "[`date -d "+8hour"`] test60_report - 执行成功" >> $fullFileName
    echo "--------------------------------------------------------------" >> $fullFileName
    
else
    echo "[`date -d "+8hour"`]"  $executeFilePath/$executeFileName "- 执行失败" 
    
    echo "[`date -d "+8hour"`] test60_report - 执行失败" >> $fullFileName
    echo "--------------------------------------------------------------" >> $fullFileName
fi    
echo "----------------------------------------------------------------------------------------------------"

--test60_report.sql

set tez.queue.name=ecbireport;
SET hive.exec.dynamic.partition =true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.dynamic.partitions.pernode=100000;

insert overwrite table vn09jj5.test60_report
partition(channel,paid_date)
select
 count(distinct order_id) as orders
 ,count(distinct delivery_phone) as customers
 ,sum(gmv) as sales
 ,channel,paid_date
from cn_ec_bi_dl_secure.wm_order_line
where channel in ('SNG','JDDJ','WMDJ')
and paid_date >='${hivevar:v_date_1_start}' and paid_date <='${hivevar:v_date_2_end}'
group by channel,paid_date
;

 

#!/bin/bash
source /u/users/$USER/.bash_profile

logPath="/u/users/$USER/daily_batch/log/"
current_date=$(date +"%Y-%m-%d" -d "+8hour")
fullPath=$logPath"/"$current_date

#新建目录
if [ ! -d "$fullPath" ];
then
    mkdir -p -m 774 "$fullPath"
fi

logName='test.log'
fullFileName=$fullPath"/"$logName

echo "----------------------------------------------------------------------------------------------------"
echo $USER
echo $current_date
echo "目录:" $fullPath
echo "目录文件:" $fullFileName


echo "----------------------------------------------------------------------------------------------------"
v_date_1_start=`date -d "-7 day +8hour" +'%Y-%m-%d'`
v_date_2_end=`date -d "-1 day +8hour" +'%Y-%m-%d'`

# 当有传入日期参数合法有效,按传入参数执行;没有参数或者无效参数时,按指定日期执行
if [[ $1 =~ [0-9]{4}-[0-9]{2}-[0-9]{2} && (${1:0:4} > '1999' && ${1:0:4} < '2050' ) && (${1:5:2} > '00' && ${1:5:2} < '13') && (${1:8:2} > '00' && ${1:8:2} < '32') ]]
then
    v_date_1_start=$1
fi
if [[ $2 =~ [0-9]{4}-[0-9]{2}-[0-9]{2} && (${1:0:4} > '1999' && ${1:0:4} < '2050' ) && (${1:5:2} > '00' && ${1:5:2} < '13') && (${1:8:2} > '00' && ${1:8:2} < '32') ]]
then
    v_date_2_end=$2
fi

## 打印输出 ##
executeFilePath=$(dirname $(readlink -f "$0"))
executeFileName=$0
echo "当前程序的路径和名称:" $executeFilePath/$executeFileName

echo "参数1:" $v_date_1_start
echo "参数2:" $v_date_2_end
echo "----------------------------------------------------------------------------------------------------"

store_id=1094
echo $store_id

hive -hivevar store_id=$store_id -hivevar v_date_1_start=$v_date_1_start -hivevar v_date_2_end=$v_date_2_end -f /u/users/$USER/test70_report.sql

isOK=`echo $?`
echo $isOK

#(0=成功, 1=失败)
if [ "$isOK" == 0 ];
then
    echo "[`date -d "+8hour"`]"  $executeFilePath/$executeFileName "- 执行成功" 
    
    echo "[`date -d "+8hour"`] test70_report - 执行成功" >> $fullFileName
    echo "--------------------------------------------------------------" >> $fullFileName
    
else
    echo "[`date -d "+8hour"`]"  $executeFilePath/$executeFileName "- 执行失败" 
    
    echo "[`date -d "+8hour"`] test70_report - 执行失败" >> $fullFileName
    echo "--------------------------------------------------------------" >> $fullFileName
fi    
echo "----------------------------------------------------------------------------------------------------"

--test70_report.sql

set tez.queue.name=ecbireport;
SET hive.exec.dynamic.partition =true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.dynamic.partitions.pernode=100000;

insert overwrite table vn09jj5.test70_report
partition(channel,paid_date)
select
 count(distinct order_id) as orders
 ,count(distinct delivery_phone) as customers
 ,sum(gmv) as sales
 ,channel,paid_date
from cn_ec_bi_dl_secure.wm_order_line
where channel in ('SNG','JDDJ','WMDJ')
and paid_date >='${hivevar:v_date_1_start}' and paid_date <='${hivevar:v_date_2_end}'
and store_id=${store_id}
group by channel,paid_date
;