INFO-丢数据排查示例

适用模块

具体说明

使用示例

场景一、多个任务操作同一个表

1、调度任务

SELECT  
  flow.cluster,  
  flow.product,  
  flow.flow_name,  
--  flow.exec_id,  调度的实例id
  job.detail   
FROM  
  easytaskops.eto_az_exec_job flow  
  left join easytaskops.eto_az_job_detail job on flow.job_id = job.job_id  
where  
  job.detail like '%dsc_support.lost_test%'  
group by  
  flow.flow_name

2、离线任务

SELECT  
  flow.cluster,  
  flow.product,  
  flow.flow,  
  job.job_alias_name  
FROM  
  mammut.pf_az_flow flow  
  join (  
    SELECT  
      f.flowaliasname,  
      def.job_alias_name  
    FROM  
      mammut.pf_az_job_flow_name f  
      JOIN mammut.pf_azjob_def def ON f.azconfigid = def.azconfigid  
    WHERE  
      def.data LIKE '%access_test2%'  
  ) job on flow.flow = job.flowaliasname;

场景二、多表使用相同location，导致数据重复

select  
  DBS.NAME as db_name,  
  t.TBL_NAME as table_name
from  
  metastore.TBLS t  
  left join metastore.SDS s on t.SD_ID = s.SD_ID  
  left join metastore.DBS dbs on t.DB_ID = dbs.DB_ID  
where  
  location = 'hdfs://easyops-cluster/user/dsc_support/hive_db/dsc_support.db/lost_test' \G;

场景三、人为删除数据

1、hdfs审计日志确认操作人
bdms_xxx为个人账号，排查自助分析与离线开发，项目账号为调度执行


2、自助分析删除
2.1、已知操作项目


2.2、全平台检索

    select 
        product
        ,clusterid
        ,engine
        ,updated_at
        ,text 
    from mammut.pf_noteblock_exec 
    where text like "%drop%access_test%" \G;

3、离线开发删除

SELECT  
  flow.cluster,  
  flow.product,  
  flow.flow,  
  job.job_alias_name  
FROM  
  mammut.pf_az_flow flow  
  join (  
    SELECT  
      f.flowaliasname,  
      def.job_alias_name  
    FROM  
      mammut.pf_az_job_flow_name f  
      JOIN mammut.pf_azjob_def def ON f.azconfigid = def.azconfigid  
    WHERE  
      def.data LIKE '%drop%access_test2%'  
  ) job on flow.flow = job.flowaliasname;

4、调度删除

SELECT  
    flow.cluster,  
    flow.product,  
    flow.flow_name,  
    flow.exec_id,  
    flow.job_name   
FROM  easytaskops.eto_az_exec_job flow  
left join easytaskops.eto_az_job_detail job on flow.job_id = job.job_id  
where  
    job.detail like '%drop%access_test2%'  
group by  
    flow.flow_name

作者：林帅