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_