二百六十六、Hive——Hive的DWD层数据清洗、清洗记录、数据修复、数据补全

一、目的

数据清洗是数据治理的关键，是提高数据质量的核心！数据清洗后，还有错误数据、清洗记录、数据重复性、数据准确性、错误数据修复、缺少数据补全等等

二、清洗步骤（以转向比数据为案例）

2.1 ODS层原始数据

create external table  if not exists  hurys_db.ods_turnratio(
    device_no           string          comment '设备编号',
    source_device_type  string          comment '设备类型',
    sn                  string          comment '设备序列号 ',
    model               string          comment '设备型号',
    create_time         string       comment '创建时间',
    cycle               int             comment '转向比数据周期' ,
    volume_sum          int             comment '指定时间段内通过路口的车辆总数',
    speed_avg           float           comment '指定时间段内通过路口的所有车辆速度的平均值',
    volume_left         int             comment '指定时间段内通过路口的左转车辆总数',
    speed_left          float           comment '指定时间段内通过路口的左转车辆速度的平均值',
    volume_straight     int             comment '指定时间段内通过路口的直行车辆总数',
    speed_straight      float           comment '指定时间段内通过路口的直行车辆速度的平均值',
    volume_right        int             comment '指定时间段内通过路口的右转车辆总数',
    speed_right         float           comment '指定时间段内通过路口的右转车辆速度的平均值',
    volume_turn         int             comment '指定时间段内通过路口的掉头车辆总数',
    speed_turn          float           comment '指定时间段内通过路口的掉头车辆速度的平均值'
)
comment '转向比数据外部表——静态分区'
partitioned by (day string)
row format delimited fields terminated by ','
stored as SequenceFile
;

2.2 DWD层原始数据清洗

主要是数据格式、数值范围以及逻辑方面的清洗

2.2.1 建表语句

create  table  if not exists  hurys_db.dwd_turnratio(
    id                  string          comment '唯一ID',
    device_no           string          comment '设备编号',
    source_device_type  string          comment '设备类型',
    sn                  string          comment '设备序列号 ',
    model               string          comment '设备型号',
    create_time         string       comment '创建时间',
    cycle               int             comment '转向比数据周期' ,
    volume_sum          int             comment '指定时间段内通过路口的车辆总数',
    speed_avg           decimal(10,2)   comment '指定时间段内通过路口的所有车辆速度的平均值',
    volume_left         int             comment '指定时间段内通过路口的左转车辆总数',
    speed_left          decimal(10,2)   comment '指定时间段内通过路口的左转车辆速度的平均值',
    volume_straight     int             comment '指定时间段内通过路口的直行车辆总数',
    speed_straight      decimal(10,2)   comment '指定时间段内通过路口的直行车辆速度的平均值',
    volume_right        int             comment '指定时间段内通过路口的右转车辆总数',
    speed_right         decimal(10,2)   comment '指定时间段内通过路口的右转车辆速度的平均值',
    volume_turn         int             comment '指定时间段内通过路口的掉头车辆总数',
    speed_turn          decimal(10,2)   comment '指定时间段内通过路口的掉头车辆速度的平均值'
)
comment '转向比数据表——动态分区'
partitioned by (day string)   --分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。
stored as orc                 --表存储数据格式为orc
;

2.2.2 清洗规则

2.2.3 清洗SQL

with t1 as (
select
        device_no,
        source_device_type,
        sn,
        model,
        create_time,
        cycle,
        case when  volume_sum      is null then 0 else volume_sum       end as  volume_sum,
        case when  speed_avg       is null then 0 else cast(speed_avg   as decimal(10,2))     end as   speed_avg,
        case when  volume_left     is null then 0 else volume_left      end as  volume_left ,
        case when  speed_left      is null then 0 else cast(speed_left  as decimal(10,2))     end as  speed_left,
        case when  volume_straight is null then 0 else volume_straight  end as  volume_straight,
        case when  speed_straight  is null then 0 else cast(speed_straight as decimal(10,2))  end as speed_straight,
        case when  volume_right    is null then 0 else volume_right     end as  volume_right,
        case when  speed_right     is null then 0 else cast(speed_right as decimal(10,2))     end as speed_right ,
        case when  volume_turn     is null then 0 else volume_turn      end as  volume_turn ,
        case when  speed_turn      is null then 0 else cast(speed_turn  as decimal(10,2))     end as  speed_turn,
        substr(create_time,1,10) day
from hurys_db.ods_turnratio
where  day ='2024-09-10'
)
insert overwrite table hurys_db.dwd_turnratio partition (day)
select
       UUID()  as  id,
       device_no,
       source_device_type,
       sn,