全国职业院校技能大赛-大数据 离线数据处理模块-数据清洗

子任务2：数据清洗

    编写Hive SQL代码，将ods库中相应表数据全量抽取到Hive的dwd库中对应表中。表中有涉及到timestamp类型的，均要求按照yyyy-MM-dd HH:mm:ss，不记录毫秒数，若原数据中只有年月日，则在时分秒的位置添加00:00:00，添加之后使其符合yyyy-MM-dd HH:mm:ss。

1. 抽取ods库中environmentdata的全量数据进入Hive的dwd库中表fact_environment_data，分区字段为etldate且值与ods库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user均填写“user1”，dwd_insert_time、dwd_modify_time均填写当前操作时间，并进行数据类型转换。使用hive cli按照envoid降序排序，查询前5条数据，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下； set hive.exec.dynamic.partition.mode=nonstrict;insert overwrite table dwd.act_environment_data partition (etldate)select id, name, 'user1', date_format(current_date(), 'yyyyMMdd HH:mm:ss'), 'user1', date_format(current_date(), 'yyyyMMdd HH:mm:ss'), etldatefrom ( select id, -- 我这里使用的是简单的测试字段，具体的字段名称看比赛环境而定 name, etldate from ods.environmentdata) t1;使用hive cli按照envoid降序排序，查询前5条数据select *from dwd.act_environment_dataorder by envoid desclimit 5;

2. 抽取ods库中changerecord的全量数据进入Hive的dwd库中表fact_change_record，抽取数据之前需要对数据根据changeid和changemachineid进行联合去重处理，分区字段为etldate且值与ods库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user均填写“user1”，dwd_insert_time、dwd_modify_time均填写当前操作时间，并进行数据类型转换。使用hive cli按照change_machine_id降序排序，查询前1条数据，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下； -- 这里因为没有数据，具体的字段用注释来代替-- 通过group by对changeid,changemachineid进行组合分组-- 然后通过过滤之后的changeid，changemachineid连接此表的其他字段insert overwrite table fact_change_record partition (etldate)select -- t2的其他字段 t1.changeid, t1.changemachineid, 'user1', date_format(current_date(), 'yyyyMMdd HH:mm:ss'), 'user1', date_format(current_date(), 'yyyyMMdd HH:mm:ss'), etldate -- 动态分区字段from ( select changeid, changemachineid from ods.changerecord group by changeid,changemachineid) t1left join ( select -- 其他字段 changeid, changemachineid, etldate from ods.changerecord) t2 on t1.changeid = t2.changeidand t1.changemachineid = t2.changemachineid使用hive cli按照change_machine_id降序排序select *from dwd.fact_change_recordorder by change_machine_id desclimit 1;

3. 抽取ods库中basemachine的全量数据进入Hive的dwd库中表dim_machine,抽取数据之前需要对数据根据basemachineid进行去重处理。分区字段为etldate且值与ods库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user均填写“user1”，dwd_insert_time、dwd_modify_time均填写当前操作时间，并进行数据类型转换。使用hive cli按照base_machine_id升序排序，查询dim_machine前2条数据，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下

insert overwrite table dwd.dim_machine partition (etldate)
select
    -- 其他字段,
    base.basemachineid,
    'user1',
    date_format(current_date(), 'yyyyMMdd HH:mm:ss'),
    'user1',
    date_format(current_date(), 'yyyyMMdd HH:mm:ss'),
    etldate
from (
     select
         basemachineid
     from ods.basemachine
     group by basemachineid
) base
left join (
    select
        -- 其他字段
        basemachineid,
        etldate
    from ods.basemachine
) t on base.basemachineid = t.basemachineid;

使用hive cli按照base_machine_id升序排序，查询dim_machine前2条数据

select
    *
from dwd.dim_machine
order base_machine_id asc
limit 2;

1. 抽取ods库中producerecord的全量数据进入Hive的dwd库中表fact_produce_record,分区字段为etldate且值与ods库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user均填写“user1”，dwd_insert_time、dwd_modify_time均填写当前操作时间，并进行数据类型转换。使用hive cli按照produce_machine_id升序排序，查询fact_produce_record前2条数据，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；

insert overwrite table dwd.fact_produce_record partition (etldate)
select
    -- t表中的其他字段
    produce_machine_id,
    'user1',
    date_format(current_date(), 'yyyyMMdd HH:mm:ss'),
    'user1',
    date_format(current_date(), 'yyyyMMdd HH:mm:ss'),
    etldate
from (
     select
         -- 其他字段,
         produce_machine_id,
         etldate
     from ods.producerecord
) t

用hive cli按照produce_machine_id升序排序，查询fact_produce_record前2条数据

select
    *
from dwd.fact_produce_record
order by produce_machine_id asc
limit 2;

1. 抽取ods库中machinedata的全量数据进入Hive的dwd库中表fact_machine_data。分区字段为etldate且值与ods库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user均填写“user1”，dwd_insert_time、dwd_modify_time均填写当前操作时间，并进行数据类型转换。使用hive cli按照machine_id降序排序，查询前1条数据，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下。

insert overwrite table dwd.fact_machine_data partition (etldate)
select
    -- t表中的其他字段
    machine_id,
    'user1',
    date_format(current_date(), 'yyyyMMdd HH:mm:ss'),
    'user1',
    date_format(current_date(), 'yyyyMMdd HH:mm:ss'),
    etldate
from (
         select
             -- 其他字段,
             machine_id,
             etldate
         from ods.machinedata
) t

使用hive cli按照machine_id降序排序，查询前1条数据

select
    *
from fact_machine_data
order by machine_id
limit 1;

数据清洗主要是对SQL去重的考察，这里主要使用group by 的方式对字段进行去重，因为没有涉及到分组聚合的功能，因此本小节也不是很难实现