OOZIE 任务管理常用SQL

来源：互联网发布：手机淘宝登录首页编辑：程序博客网时间：2024/06/16 08:34

OOZIE 任务管理常用SQL及命令

1、OOZIE任务执行时长统计

select      tp.app_name    ,ta.id     ,(unix_timestamp(tb.end_time) - unix_timestamp(tb.created_time))/60 long_minutes    ,date_format(adddate(date(ta.nominal_time),interval -1 day),'%Y%m%d') data_datefrom ( select *  from COORD_JOBS  where status='RUNNING') tp inner join ( SELECT *  FROM COORD_ACTIONS ) ta on tp.id = ta.job_id AND tp.last_action_number=ta.action_number inner join ( select *  from WF_ACTIONS     where id like '%hive%') tb on ta.external_id = tb.wf_idorder by long_minutes desc LIMIT 100;

2、OOZIE任务每日执行情况查询

select       ta.id,    concat('oozie job -rerun ',tb.id,' -action ',ta.action_number) as cmd ,tb.app_name,ta.status    from (select * from oozie.COORD_ACTIONS where status not in ('SUCCEEDED') ) ta inner join  (     SELECT * FROM COORD_JOBS       where (app_name like '%fact%' or app_name like '%rpt%' or app_name like 'ds%' or app_name like 'dim_%' or app_name like 'ods%' or app_name like 'dw%' )     and status = 'RUNNING' ) tb on ta.job_id = tb.id order by status ,app_name;

3、查询指定OOZIE任务

    SELECT         app_name       ,concat('oozie job -rerun ',id,' -action ',last_action_number) as rerun     FROM COORD_JOBS       where  (app_name like '%rpt_bi3_real_op_kpi_di%')     and status = 'RUNNING'

4、修改oozie任务并发量

    update coord_jobs     set concurrency=3    where id in (        '0391230-161231131609747-oozie-bigd-C'    );    commit ;

5、查询OOZIE重复提交的任务

    SELECT app_name,count(1) cnt  FROM COORD_JOBS       where   status = 'RUNNING'     group by app_name     having cnt >1

6、更新任务状态 [用时要慎重哦]

update oozie.COORD_ACTIONS set status='SUCCEEDED'where id in ( '0186948-161028184827360-oozie-bigd-C@51');commit ;

7、查任务启动时间

select     start_time   ,next_matd_time   ,created_time   ,end_time   ,last_modified_time from COORD_JOBS where app_name in (    'table_name' )and status='RUNNING';

8、更新任务启动时间 (时分秒一定要保持一样)

update COORD_JOBS set start_time='yyyy-MM-dd hh:mi:ss', next_matd_time='yyyy-MM-dd hh:mi:ss'where  app_name in (    'table_name' )and status='RUNNING';

9、常用命令

1、测试任务 oozie job -config job.properties -dryrun 2、正式提交运行一个任务 oozie job -config job.properties -run 3、杀死一个任务 oozie job -kill job_id 4、杀死一个action oozie job -kill job_id -action action_number 5、重跑一个action oozie job -rerun job_id -action action_number 6、更新一个oozie的配置文件或者脚本 hdfs dfs -put -f file_name dest_dir -- -f 如果存在则覆盖，不用再单独删除了 7、更新coordinator oozie job -update job_id 8、挂起一个job oozie job -suspend job_id 9、重新执行挂起任务 oozie job -resume job_id

0 0