关于shell调用MySQL存储过程的问题

举报
数据社 发表于 2022/09/25 02:23:05 2022/09/25
【摘要】 前天收到一个任务,要统计每天ETL的运行情况,感觉每天从给的SQL语句一个一个的查询影响效率。 就行用写一个存储过程来实现,然后通过shell调用这个存储过程来自动跑数据,写到相应的文件里~ 思路就是这样,废话不说,上一下写好的代码。 DELIMITER // create procedure sp_etl_info_day ...

前天收到一个任务,要统计每天ETL的运行情况,感觉每天从给的SQL语句一个一个的查询影响效率。

就行用写一个存储过程来实现,然后通过shell调用这个存储过程来自动跑数据,写到相应的文件里~

思路就是这样,废话不说,上一下写好的代码。

DELIMITER // 
create procedure sp_etl_info_day (in statis_date int)
begin
declare num_day int default 0;
declare num_month int default 0;
declare num_hour int default 0;
declare num_total int default 0;
declare num_add_lastday int default 0;
declare num_schedule_run int default 0;
declare num_run int default 0;
declare num_succeed int default 0;
declare num_failed int default 0;
declare num_failed1 int default 0;
declare num_killed int default 0;
declare num_waited int default 0;
declare num_norun int default 0;
declare num_man_run int default 0;
declare num_norun_lastday int default 0;
declare rate_wz_etl FLOAT default 0;
declare rate_js_etl FLOAT default 0;
select count(*) into num_day from schedule where isOnline=1 and scheduleOption like "%1#day%";
select count(*) into num_month from schedule where isOnline=1 and scheduleOption like "%1#month%";
select count(*) into num_hour from schedule where isOnline=1 and scheduleOption like "%1#hour%";
set num_total=num_day+num_month+num_hour;
select count(distinct scheduleId) into num_schedule_run from schedule_execution where DATE_FORMAT(startTime,'%Y%m%d')=statis_date and scheduleId in ( select id from schedule where isOnline=1);
select count(*) into num_run from (select count(*) as count, scheduleId,period from schedule_execution where DATE_FORMAT(startTime,'%Y%m%d')=statis_date and scheduleId in ( select id from schedule where isOnline=1) group by scheduleId) a;
select count(*) into num_failed from (select count(*) as count, scheduleId from schedule_execution where DATE_FORMAT(startTime,'%Y%m%d')=statis_date and scheduleId in ( select id from schedule where isOnline=1) and status="Failed" group by scheduleId) a where a.scheduleId not in (select scheduleId from schedule_execution where DATE_FORMAT(startTime,'%Y%m%d')=statis_date and scheduleId in ( select id from schedule where isOnline=1) and status="Succeed" group by scheduleId);
select count(*) into num_killed from (select count(*) as count, scheduleId from schedule_execution where DATE_FORMAT(startTime,'%Y%m%d')=statis_date and scheduleId in ( select id from schedule where isOnline=1) and status="Killed" group by scheduleId) a where a.scheduleId not in (select scheduleId from schedule_execution where DATE_FORMAT(startTime,'%Y%m%d')=statis_date and scheduleId in ( select id from schedule where isOnline=1) and status="Succeed" group by scheduleId);
set num_succeed=num_run-num_failed-num_killed;
set rate_wz_etl=round(num_succeed/num_run,4);
set num_failed1=num_failed+num_killed;
select CONCAT("日:",num_day,"个",",月:",num_month,"个",",小时:",num_hour,"个",",计划运行控制流",num_schedule_run,"个",",共",num_total,"个"
,',昨天共启动控制流:',num_run,'个',',正常执行成功',num_succeed,'个',',报错',num_failed1,'个',',昨天调度运行完整率:',rate_wz_etl) as "ETL日报";
select reserved2, flowName,source from flow where id in (
select originalFlowId from schedule where id in (
select distinct scheduleId from schedule_execution where DATE_FORMAT(startTime,'%Y%m%d')=statis_date and scheduleId in ( select id from schedule where isOnline=1) and status="Failed"
and scheduleId not in(
select scheduleId from schedule_execution where DATE_FORMAT(startTime,'%Y%m%d')=statis_date and scheduleId in ( select id from schedule where isOnline=1) and status="Succeed" )));

end ;//
DELIMITER ;

过程写好,下面就开始编写shell调用该存储过程了~

#!/bin/sh  
  
##  用来统计ETL日运行情况  
##  the script is executed at 10 o'clock by every day.  
DATE=$(date +%Y%m%d --date '1 days ago')  
USER=user  
PASSWD=****** 
DATABASE=user  
time=$(date +%Y%m%d --date '1 days ago') 
FILE=/home/mysql/etl_log/log/ETL_$time.txt  

  
echo "the date:"${DATE}  
  
SQL="mysql -u${USER} -p${PASSWD} -D ${DATABASE} -e \"call sp_etl_info_day(${DATE})\""  
echo ${SQL}
echo $SQL>>${FILE} 
 
mysql -u${USER} -p${PASSWD} -D ${DATABASE} -e "call sp_etl_info_day(${DATE})">>$FILE

exit

以后每天只要去对应的目录下查看文件就行啦~~

省去了不少时间~

我想这大概就是思路,在工作期间,尽量避免重复的工作量,把程序能做的让程序自动做~这样可以更高效的工作~

文章来源: dataclub.blog.csdn.net,作者:数据社,版权归原作者所有,如需转载,请联系作者。

原文链接:dataclub.blog.csdn.net/article/details/50687282

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。