if [[ -z $1 ]]; then
datetime=$(date -d "-1 day" +"%Y-%m-%d")
else
datetime=$1
fi
echo "datetime:${datetime}"
statdate=$(date -d "${datetime}" +"%Y%m%d")
echo "statdate:${statdate}"
before1day_statdate=$(date -d"1 day ago ${datetime}" +"%Y%m%d")
echo "before1day_statdate:${before1day_statdate}"
before7day_statdate=$(date -d"7 day ago ${datetime}" +"%Y%m%d")
echo "before7day_statdate:${before7day_statdate}"
stat_date=$(date -d "${datetime}" +"%Y-%m-%d")
echo "stat_date:${stat_date}"
before7day_stat_date=$(date -d"7 day ago ${datetime}" +"%Y-%m-%d")
echo "before7day_stat_date:${before7day_stat_date}"
function init() {
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 初始化相关变量及环境"
source /etc/profile
cd $(dirname $0)
curpath=$(pwd)
echo "curpath:${curpath}"
source ../config.sh $0
}
verifier() {
if [ $1 -eq 0 ]; then
echo "$2 succeed! "
else
echo "$2 failed! "
exit 1
fi
}
function delete_hive_data() {
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 删除hive数据"
hql="alter table db_result.r_daily_new_clue_d drop if exists partition(statdate='${statdate}')"
echo "hql: ${hql}"
beeline_execute_hql "${hql}"
check_run
}
function hive_stat() {
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 计算当天各营收金额明细"
hql="
insert overwrite table db_result.r_daily_new_clue_d partition(statdate = '${statdate}')
SELECT '${stat_date}' new_clue_date,
coalesce(t1.all_clue, 0) all_clue,
coalesce(t2.issue_allocate_clue, 0) issue_allocate_clue,
coalesce(t3.issue_self_allocate_clue, 0) issue_self_allocate_clue,
coalesce(t4.no_issue_terminal_register_clue, 0) no_issue_terminal_register_clue,
coalesce(t5.no_issue_operator_invite_clue, 0) no_issue_operator_invite_clue,
coalesce(t6.no_issue_other_clue, 0) no_issue_other_clue
FROM
(SELECT count(merchant_id) all_clue,
'${statdate}' statdate
FROM db_result.r_clue_detail_extend_dt
WHERE statdate = '${statdate}'
AND clue_created_at LIKE '${stat_date}%') t1
LEFT JOIN
(SELECT count(merchant_id) issue_allocate_clue,
'${statdate}' statdate
FROM db_result.r_clue_detail_extend_dt
WHERE statdate = '${statdate}'
AND clue_created_at LIKE '${stat_date}%'
AND allocation_type = 1) t2 ON t1.statdate = t2.statdate
LEFT JOIN
(SELECT count(merchant_id) issue_self_allocate_clue,
'${statdate}' statdate
FROM db_result.r_clue_detail_extend_dt
WHERE statdate = '${statdate}'
AND clue_created_at LIKE '${stat_date}%'
AND allocation_type = 2) t3 ON t1.statdate = t3.statdate
LEFT JOIN
(SELECT count(merchant_id) no_issue_terminal_register_clue,
'${statdate}' statdate
FROM db_result.r_clue_detail_extend_dt
WHERE statdate = '${statdate}'
AND clue_created_at LIKE '${stat_date}%'
AND allocation_type = 3
AND plan_name IN ('小鹅通助手APP',
'商家助手',
'安卓市场',
'IOS市场')) t4 ON t1.statdate = t4.statdate
LEFT JOIN
(SELECT count(merchant_id) no_issue_operator_invite_clue,
'${statdate}' statdate
FROM db_result.r_clue_detail_extend_dt
WHERE statdate = '${statdate}'
AND clue_created_at LIKE '${stat_date}%'
AND allocation_type = 3
AND plan_name = '运营者邀请短信') t5 ON t1.statdate = t5.statdate
LEFT JOIN
(SELECT count(merchant_id) no_issue_other_clue,
'${statdate}' statdate
FROM db_result.r_clue_detail_extend_dt
WHERE statdate = '${statdate}'
AND clue_created_at LIKE '${stat_date}%'
AND allocation_type IN (3,
4)
AND plan_name NOT IN ('小鹅通助手APP',
'商家助手',
'安卓市场',
'IOS市场',
'运营者邀请短信')) t6 ON t1.statdate = t6.statdate
"
echo "hql: ${hql}"
beeline_execute_hql "${hql}"
check_run
}
function delete_mysql_data() {
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 删除mysql数据"
sql="delete from db_bi.t_r_daily_new_clue_d where new_clue_date = '${stat_date}';"
mysql -h${mysql_host_39} -P${mysql_port_39} -u${mysql_user_39} -p${mysql_password_39} -e "${sql}"
}
function sqoop_hive_2_mysql() {
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 同步hive数据到mysql"
sqoop_execute export \
--connect "jdbc:mysql://${mysql_host_39}:${mysql_port_39}/db_bi?useUnicode=true&characterEncoding=UTF-8" \
--username ${mysql_user_39} \
--password ${mysql_password_39} \
--table t_r_daily_new_clue_d \
--export-dir ${chdfs_path}/usr/hive/warehouse/db_result.db/r_daily_new_clue_d/statdate=${statdate} \
--columns "new_clue_date,all_clue,issue_allocate_clue,issue_self_allocate_clue,no_issue_terminal_register_clue,
no_issue_operator_invite_clue,no_issue_other_clue" \
--input-fields-terminated-by '\001' \
--lines-terminated-by '\n' \
--input-null-non-string '\\N' \
--input-null-string '\\N' \
--num-mappers 1
check_run
}
init
delete_hive_data
hive_stat
delete_mysql_data
sqoop_hive_2_mysql
: <<!
CREATE TABLE db_result.r_daily_new_clue_d(
new_clue_date string COMMENT '建立线索日期',
all_clue int COMMENT '当天总线索量',
issue_allocate_clue int COMMENT '当天下发分配线索量',
issue_self_allocate_clue int COMMENT '当天下发自拓线索量',
no_issue_terminal_register_clue int COMMENT '不下发终端注册线索量',
no_issue_operator_invite_clue int COMMENT '不下发运营者邀请短信注册线索量',
no_issue_other_clue int COMMENT '其他线索量')
COMMENT '当天各线索量明细'
PARTITIONED BY (statdate string)
CREATE TABLE db_bi.t_r_daily_new_clue_d (
new_clue_date varchar(128) DEFAULT NULL COMMENT '建立线索日期',
all_clue int DEFAULT '0' COMMENT '当天总线索量',
issue_allocate_clue int DEFAULT '0' COMMENT '当天下发分配线索量',
issue_self_allocate_clue int DEFAULT '0' COMMENT '当天下发自拓线索量',
no_issue_terminal_register_clue int DEFAULT '0' COMMENT '不下发终端注册线索量',
no_issue_operator_invite_clue int DEFAULT '0' COMMENT '不下发运营者邀请短信注册线索量',
no_issue_other_clue int DEFAULT '0' COMMENT '其他线索量',
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (new_clue_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='当天各线索量明细'
!