数据清洗脱敏详细步骤

--todo 基于ods层的事故记录表进行脱敏过滤构建dwd的事故记录表


insert overwrite table dwd.dwd_base_acd_file_msk_f_d partition(ds='2024-08-08')
select   `ajmc`
,.......................
     ,regexp_replace(`jbr`,'^(\\S).(\\S*)$','$1*$2') as jbr
     ,`jdcsl`
,........................
     ,`sfty`
     ,if(sgbh = 'null',-1,sgbh) as sgbh
     ,`sgccyy`
,.......................
     ,`sgxt`
     ,if(ssrs = 'null',0,ssrs)
     ,`ssrs24`
,.......................
     ,`swrs`
     ,if(swrs24 = 'null',0,swrs24)
     ,if(swrs3 = 'null',0,swrs3)
     ,if(swrs30 = 'null',0,swrs30)
     ,`swrs7`
,......................
     ,if(zjccss = 'null',0,zjccss)
     ,`zmtj`
     ,`zsrs`
     ,`zyglss`
from ods.ods_base_acd_file_f_d
where ds = '2024-08-08'
  and sgfssj != 'null'
;

--------------==================================================================================================
--todo 依次检查项目需求所用字段
--todo sgbh sgfssj   ssrs  ssrs24 ssrs3 ssrs30 ssrs7   swrs24 swrs3 swrs30 swrs7 swrsq

-- todo 这里是排查是否有 事故编号 为null 等情况

select *
from bigdata008_ods.bigdata008_ods_base_acd_file_f_d
where ds = '2024-08-08'
  --todo 排查是否有 事故编号 为null 等情况 ，trim为去除两边空格
and (sgbh is null or sgbh is null or trim(sgbh) = '' or sgbh = 'null') --判断需求字段null类型
limit 10;

--
--NULL 的比较不能使用 == 操作符。NULL 被认为是“未知”的，因此用 == 或 != 比较时不会返回 TRUE 或 FALSE，而是 UNKNOWN。
--正确的做法：
--检查 NULL 值：
--使用 IS NULL 或 IS NOT NULL 来正确地检查列是否为空。例如：
--sgbh IS NULL
--------------==================================================================================================
-- todo 这里是排查是否有 sgfssj 为null 等情况
               --null字符串
select if(sgbh = 'null',-1,sgbh) as sgbh  --对sgbh处理，将null处理成-1
              ,sgfssj,ajmc
  --  COALESCE 函数从左到右检查参数，返回第一个非 NULL 的值。正常的非空null, 不是'null', ' '
  --  在这个例子中，sgfssj 如果为 NULL，则 COALESCE
  --  会尝试返回 kskcsj、jskcsj、gxsj 或 cclrsj 中的第一个非 NULL 值。
  --  如果 sgfssj 非 NULL，则 COALESCE 直接返回 sgfssj。
           --   ,coalesce(sgfssj,kskcsj,jskcsj,gxsj,cclrsj)
from bigdata008_ods.bigdata008_ods_base_acd_file_f_d
where ds = '2024-08-08'
and sgfssj = null    --todo 判断sgfssj为null的量及类型  如果大量null，可选择用其他字段代替或其他字段进行处理后代替
limit 1000;


--------------==================================================================================================
--todo 假设如果sgfssj数据量很大，且为正常null，所进行的处理
                --null字符串
select if(sgbh = 'null',-1,sgbh) as sgbh  --对sgbh处理，将null处理成-1
     ,sgfssj,ajmc
     --todo  COALESCE 函数从左到右检查参数，返回第一个非 NULL 的值。正常的非空null, 不是'null', ' '
     --  在这个例子中，sgfssj 如果为 NULL，则 COALESCE
     --  会尝试返回 kskcsj、jskcsj、gxsj 或 cclrsj 中的第一个非 NULL 值。
     --  如果 sgfssj 非 NULL，则 COALESCE 直接返回 sgfssj。
     ,coalesce(sgfssj,kskcsj,jskcsj,gxsj,cclrsj)
from bigdata008_ods.bigdata008_ods_base_acd_file_f_d
where ds = '2024-08-08'
 -- and sgfssj = null    --判断sgfssj为null的量及类型  如果大量null，可选择用其他字段代替或其他字段进行处理后代替
    limit 1000;

--------------==================================================================================================
--todo  判断ssrs的null的情况，数量，类型  这里查出数据为空的就1条，影响不大，后续直接取值

select if(sgbh = 'null',-1,sgbh) as sgbh
,sgfssj
,ajmc
,ssrs
,ssrs24
,ssrs3
,ssrs30
,ssrs7
from bigdata008_ods.bigdata008_ods_base_acd_file_f_d
where ds = '2024-08-08'
and ssrs = 'null'
    or ssrs24 ='null'
    or ssrs3 ='null'
    or ssrs30 ='null'
    or ssrs7 ='null'
limit 1000;
--------------==================================================================================================
--todo  判断swrs的null的情况，数量，类型
select if(sgbh = 'null',-1,sgbh) as sgbh
     ,sgfssj
     ,ajmc
     ,swrsq
     ,swrs24
     ,swrs3
     ,swrs30
     ,swrs7
     ,zjccss
from bigdata008_ods.bigdata008_ods_base_acd_file_f_d
where ds = '2024-08-08'
    and swrsq = 'null'
   or swrs24 ='null'
   or swrs3 ='null'
   or swrs30 ='null'
   or swrs7 ='null'
    or zjccss='null'
    limit 1000;

--------------==================================================================================================
--todo  判断swrs的null的数量较多的swrs类进行处理
select if(sgbh = 'null',-1,sgbh) as sgbh
     ,sgfssj
     ,ajmc
     ,swrsq
     ,if(swrs24 = 'null',0,swrs24)
     ,if(swrs3 = 'null',0,swrs3)
     ,if(swrs30 = 'null',0,swrs30)
     ,if(zjccss = 'null',0,zjccss)
--如果是真正的null nvl(zjccss,0)
from bigdata008_ods.bigdata008_ods_base_acd_file_f_d
where ds = '2024-08-08'
 --   and swrsq = 'null'
 --  or swrs24 ='null'
 --  or swrs3 ='null'
 --  or swrs30 ='null'
 --  or swrs7 ='null'
 --  or zjccss ='null'
    limit 1000;

--------------==================================================================================================


--todo 加密脱敏  在同一项目中，某个字段加密过后，所有的表都要以相同的方式进行加密，否则关联不上

--MD5
select  sfz,upper(md5(sfz)) as md5_sfz from XXX ;

--md5 拼接字符串(加盐)
select  sfz,upper(md5(concat(sfz,'abcdefg'))) as md5_sfz from XXX ;


--todo 正则匹配

select regexp_replace('12345678','^(\\d{3})(.*)(\\d{2})$','1$******$3');
-- 123******678


select  regexp_replace('张三丰','^(\\S).*(\\S)$','$1*$2');
-- 张*丰

select regexp_replace('张三','^(\\S).(\\S*)$','$1*$2');
-- 张*