【案例】Azkaban定时任务

azkaban任务表

CREATE TABLE `bi_azkaban_task_v1` (
  `task_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '任务id',
  `task_name` varchar(255) NOT NULL COMMENT '任务名称',
  `task_desc` varchar(255) NOT NULL COMMENT '任务描述',
  `user_name` varchar(255) NOT NULL COMMENT 'BI名称',
  `dashboard_link` varchar(255) NOT NULL COMMENT '看板链接,如果没有，填:无',
  `py_script` text NOT NULL COMMENT 'python脚本',
  `start_date` varchar(10) NOT NULL COMMENT '开始日期',
  `end_date` varchar(10) NOT NULL COMMENT '结束日期',
  `repeat_way` int(2) NOT NULL COMMENT '重复方式，1:每天,7:每周,30:每月',
  `run_num` int(4) NOT NULL COMMENT '运行次数',
  `err_num` int(4) NOT NULL COMMENT '错误次数',
  `run_time` int(11) NOT NULL COMMENT '运行时长',
  `is_test` int(2) NOT NULL COMMENT '是否测试',
  `deleted` int(2) NOT NULL COMMENT '是否删除',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`task_id`) USING BTREE,
  UNIQUE KEY `index` (`task_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COMMENT='阿兹卡班任务';

定时任务执行脚本

from bi import *
# bi包是自己定义的工具包，主要是对数据库连接、时间处理等功能封装
def get_task():
    sql = r"""
    SELECT task_id,task_name,py_script
    FROM bi_azkaban_task_v1
    WHERE end_date >= curdate()
      AND deleted = 0
      AND is_test = 0
      AND ((repeat_way = 1)
           OR (repeat_way = 7
               AND WEEKDAY(start_date)=WEEKDAY(curdate()))
           OR (repeat_way = 30
               AND DAY(start_date)=DAY(curdate())))
    order by run_time asc
    """
    return pd.read_sql(sql, rds)


def job(index, task):
    print(f"任务【{task.iloc[index, 1]}】开始运行！！！")
    t1 = time.time()
    try:
        exec(task.iloc[index, 2])
        print(f"任务【{task.iloc[index, 1]}】运行成功！！！")
        is_test = 0
    except:
        is_test = 1
        print(f"任务【{task.iloc[index, 1]}】运行失败！！！")
    t2 = time.time()
    run_time = int(t2 - t1)
    update_task_sql = f"""
                        update bi_azkaban_task_v1 
                        set is_test = {is_test},
                            run_time = {run_time},
                            run_num = run_num+1,
                            err_num = err_num+{is_test}
                        where task_id = {task.iloc[index, 0]};
                        """
    add_task_record_sql = f"""
                            INSERT INTO bi_azkaban_task_record_v1 (task_id,is_success,run_time,is_test)
                            values ({task.iloc[index, 0]},1-{is_test},{run_time},0);
                            """
    bi_public = DB(py_rds)
    bi_public.select(update_task_sql, kind=False)
    bi_public.select(add_task_record_sql, kind=False)
    bi_public.close()
    return 1


azkaban_task = get_task()
if azkaban_task.shape[0] > 0:
    for i in range(azkaban_task.shape[0]):
        job(index=i, task=azkaban_task)
else:
    pass

将py文件设置为阿兹卡班定时任务，如有新增任务，存在bi_azkaban_task_v1表即可