Kettle数据清洗（案例一）

一、整体流程图（去除重复记录，脏记录）：

二、各个步骤如下:

1、VEH_FACT_VEHICLE

with veh_zc_zr_gh as ( 

 select 

      v.xh,

      v.GLBM as glbmbm,

      v.XZQH as xzqhbm

      ,HPZL as hpzlbm,

      FZJG as fzjgbm,

      SYQ as syqbm,

      SYXZ as syxzbm,

      CLLX as cllxbm,

      CLLY as cllYbm,

      xh as clbm, 

      to_char(fprq, 'YYYYMMDD') as sprqbm,

      to_char(ccdjrq, 'YYYYMMDD') as fzrqbm, 

      to_char(yxqz, 'YYYYMMDD') as yxqzbm,

       1 as bys,

       decode(clly,'1',1,0) as zcs, 

       decode(clly,'2',1,0) as zrs,

       decode(clly,'3',1,0) as ghs,

     /*  0 as zchus,

       0 as zxs,*/

       to_char(v.qzbfqz,'yyyymmdd') as BFRQBM, --

       1 as bfs

        from vehicle v

        --where ccdjrq between v_dt1 and v_dt2

        where --zt not in ('B','E','M') ; --

              --fzrq between v_dt1 and v_dt2 and --

              Instr(zt,'B')=0 and Instr(zt,'E')=0 and Instr(zt,'M')=0

              --ccdjrq<to_date('20130101','yyyymmdd');

     )

     select   t1.*,

     to_char(v.zxrq,'yyyymmdd') as ZXRQBM,-- 

     nvl2(v.xh,1,0) as zxs,

     to_char(vv.djrq,'yyyymmdd') as ZCRQBM,-- 

     nvl2(v.xh,1,0) as zchus

     from veh_zc_zr_gh t1 

     left join   vehicle_logout v on t1.xh=v.xh    

     left join   veh_out vv on vv.xh= t1.xh       

 2、Sort Rows

3、Unique Rows

4、Javascript

var filterv=0;

var tem=trimStr(XH)

var tem=tem.length;

if(tem==14)

{

	filterv=1;

}

else

{

	filterv=0;

}

function trimStr(str){return str.replace(/(^\s*)|(\s*$)/g,"");}

5、Field Selection

选择需要的字段，去除额外的字段

6、Switch/Case

7、Over

附：

Error1：

Switch/Case 步骤本能识别js中数据值

解决办法：

把Case值数据类型选上

Error2：

查看数据流程时，发现表输入记录个数为117081，而经过去除重复记录之后数据条数为117032，而oracle中查询重复记录条数为0（SELECT xh,count(1) FROM VEHICLE group by xh  having  count(1)>1），原因是表输入sql出现了重复记录，即步骤一。

记录日常点滴，方便自己，帮助他人！