OOZIE 任务管理常用SQL

最新推荐文章于 2023-12-06 20:26:18 发布

路在脚下扬帆远航

最新推荐文章于 2023-12-06 20:26:18 发布

阅读量778

点赞数

分类专栏：数据仓库文章标签： oozie常用操作

本文链接：https://blog.csdn.net/yangws2004/article/details/65446448

版权

数据仓库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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_date
from 
(
 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_id
order 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