DolphinScheduler定期清除工作流

背景

由于工作流实例以及任务实例过多，准备只保留两天的任务记录

需要的表

t_ds_process_instance 工作流实例表
t_ds_task_instance  任务实例表
t_ds_relation_process_instance  工作流和任务实例关联表  保存着工作流和任务实例id

1.2 sql编写

创建工作流实例临时表保存两天的id

drop table if exists tmp_t_ds_process_instance_1;
create table tmp_t_ds_process_instance_1 as
SELECT id process_instance_id
FROM t_ds_process_instance
WHERE DATE(end_time) < CURDATE() - INTERVAL 2 DAY
union
select id process_instance_id
from t_ds_process_instance
where DATE(start_time) < CURDATE() - INTERVAL 2 DAY;

创建任务实例临时表保存两天的id

drop table if exists tmp_t_ds_process_instance_2;
create table tmp_t_ds_process_instance_2 as
select  
a.id as task_instance_id
from t_ds_task_instance a, tmp_t_ds_process_instance_1 b
where a.process_instance_id = b.process_instance_id;

保留两天工作流实例日志

DELETE  a from t_ds_process_instance a  where exists (
select 1 from tmp_t_ds_process_instance_1 b where a.id = b.process_instance_id
);

保留两天的任务实例日志

delete a from t_ds_task_instance a where exists (
select 1 from tmp_t_ds_process_instance_2 b where a.id = b.task_instance_id
);

删除关联的任务日志

delete a from t_ds_relation_process_instance a where exists ( select  1 from tmp_t_ds_process_instance_1 b where a.parent_process_instance_id = b.process_instance_id
);
delete a from t_ds_relation_process_instance a where exists ( select  1 from tmp_t_ds_process_instance_1 b where a.process_instance_id = b.process_instance_id
);
delete a from t_ds_relation_process_instance a where exists (select 1 from tmp_t_ds_process_instance_2 b where a.parent_task_instance_id = b.task_instance_id
);

删除临时表

drop table if exists tmp_t_ds_process_instance_1;
drop table if exists tmp_t_ds_process_instance_2;

1.3 海豚上创建任务定时运行