kettle使用不做介绍,本文旨在记录etl调度参数配置与作业状态监控
流程说明
1、设置变量:
1.1、 创建<etl作业配置表>ETL_JOB_INFO,作业名称 取数开始时间 取数结束时间 作业开始时间 作业结束时间 作业平均时长 耗时(秒) 状态
1.2、从ETL_JOB_INFO中读取该作业,取数开始时间 取数结束时间 作业名称
2、start_job:
从上一步中取 作业名称,作为参数传入 proc_start_job。在etl作业调度记录表中插入本次调度的信息与开始时间
create or replace procedure proc_start_job(p_job varchar2)
is
begin
--插入历史表
insert into etl_job_record(bu_name,job_name,start_date,end_date,job_start_date,status,cn_Name)
select bu_name,job_name,start_date,end_date,sysdate,'1',cn_name from etl_job_record where job_name=p_job;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'-'||SQLERRM);
end;
3、end_job:
3.1、在业务数据处理完成之后,更新etl作业调度记录表的结束时间、耗时与状态。
3.2、根据ETL_JOB_INFO中配置的偏移度,设置下一次作业的取数时间参数
create or replace procedure proc_end_job(p_job varchar2) is
V_FREQ ETL_JOB_INFO.freq%type;
V_OFFSET ETL_JOB_INFO.offset%type;
begin
--计入作业历史表
update etl_job_record m
set job_finis_date = sysdate,
elapsed_time = ROUND(TO_NUMBER(sysdate - job_start_date) * 24 * 60 * 60),
status = '0' --0完成,未完成
where job_name = p_job
and m.job_start_date =
(select max(job_start_date)
from etl_job_cycle_history z
where m.job_name = z.job_name);
SELECT T.FREQ,T.OFFSET
INTO V_FREQ,V_OFFSET
FROM ETL_JOB_INFO t
WHERE job_name = p_job;
DBMS_OUTPUT.PUT_LINE('作业:'||p_job||',频度:'|| V_FREQ ||',偏移: '||V_OFFSET );
if V_FREQ='D' THEN --日频度
--作业结束,准备好下一次的取数时间 起始日期为当月1日 涉及补单
IF V_OFFSET=0 THEN
update ETL_JOB_INFO
set start_date = to_date(to_char(trunc(sysdate, 'mm'),'yyyy-mm-dd') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date = to_date(to_char(sysdate + 1,'yyyy-mm-dd')|| to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job
--and freq='D'
;
ELSIF V_OFFSET=2 and to_char(sysdate,'dd') < 15 THEN
update ETL_JOB_INFO
set start_date = to_date(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1,'YYYY-MM-DD') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date = to_date(to_char(sysdate,'yyyy-mm-dd')|| to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job
;
ELSIF V_OFFSET=2 and to_char(sysdate,'dd') >= 15 THEN
update ETL_JOB_INFO
set start_date = to_date(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1,'YYYY-MM-DD') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date = to_date(to_char(sysdate,'yyyy-mm-dd')|| to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job
;
--一天之内重复跑问题,当12点为最后一次跑,判断一下,不超过12点则设开始结束日期为昨天和今天
ELSIF V_OFFSET=3 and to_char(sysdate,'hh24') < 23 THEN
update ETL_JOB_INFO
set start_date = to_date(to_char(sysdate-1,'yyyy-mm-dd') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date = to_date(to_char(sysdate,'yyyy-mm-dd')|| to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job
;
ELSE
--起始日起+1
update ETL_JOB_INFO
set start_date = to_date(to_char(sysdate,'yyyy-mm-dd') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date = to_date(to_char(sysdate+1,'yyyy-mm-dd')|| to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job
--and freq='D'
;
END IF;
elsif V_FREQ='W' THEN --周频度
IF V_OFFSET=0 THEN --都是1日开始
update ETL_JOB_INFO
set start_date =to_date(to_char(trunc(sysdate,'mm'),'yyyy-mm-dd') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') --月上初1日到当月1日
,end_date = to_date(to_char(next_day(sysdate,to_char(end_date,'day')),'yyyy-mm-dd') || to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')--如果直接+7,隔天重跑就会有问题
where job_name = p_job
--and freq='W'
;
ELSIF V_OFFSET=2 THEN
update ETL_JOB_INFO--从本周一到本周日
set start_date = to_date(TO_CHAR(trunc(sysdate,'iw'),'YYYY-MM-DD') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date = to_date(to_char(trunc(sysdate,'iw')+6,'yyyy-mm-dd') || to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job;
ELSE
update ETL_JOB_INFO--从上周到本周
set start_date = to_date(to_char(next_day(sysdate, to_char(end_date,'day'))-7,'yyyy-mm-dd')|| to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date = to_date(to_char(next_day(sysdate,to_char(end_date,'day')),'yyyy-mm-dd') || to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')--如果直接+7,隔天重跑就会有问题
where job_name = p_job
--and freq='W'
;
end if;
elsif V_FREQ='M' THEN --月频度,从月初-1日
update ETL_JOB_INFO
set start_date = to_date(to_char(trunc(sysdate,'mm'),'yyyy-mm-dd') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') --月上初1日到当月1日
,end_date = to_date(to_char(last_day(trunc(sysdate))+1 ,'yyyy-mm-dd')|| to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job
--and freq='M'
;
--least(last_day(trunc(sysdate))+1,next_day(sysdate, 'Friday'))+ 8 / 24
elsif V_FREQ='S' THEN --白晚班
update ETL_JOB_INFO
set start_date = start_date+1/2
,end_date = end_date+1/2
where job_name = p_job
;
elsif V_FREQ='T' THEN --白晚班
update ETL_JOB_INFO
set start_date = to_date(to_char(TRUNC(SYSDATE-8.5/24),'yyyy-mm-dd')|| to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date= to_date(to_char(TRUNC(SYSDATE-8.5/24)+1,'yyyy-mm-dd')|| to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job
;
elsif V_FREQ='Q' THEN --季频度,
update etl_job_cycle
set start_date = add_months(start_date,3)
,end_date = add_months(end_date,3)
where job_name = p_job
--and freq='M'
;
else --默认日平频度,自增1天
update ETL_JOB_INFO
set start_date = to_date(to_char(sysdate,'yyyy-mm-dd') || to_char(start_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
,end_date = to_date(to_char(sysdate+1,'yyyy-mm-dd')|| to_char(end_date,'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
where job_name = p_job
--and freq='D'
;
end if;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || '-' || SQLERRM);
end;