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

子任务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'),
       etldate
   from (
        select
            id,    -- 我这里使用的是简单的测试字段，具体的字段名称看比赛环境而定
            name,
            etldate
        from ods.environmentdata
   ) t1;

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

   select
       *
   from dwd.act_environment_data
   order by envoid desc
   limit 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
   ) t1
   left join (
       select
           -- 其他字段
           changeid,
           changemachineid,
           etldate
       from ods.changerecord
   ) t2 on t1.changeid = t2.changeid
   and t1.changemachineid = t2.changemachineid

   使用hive cli按照change_machine_id降序排序

   select
       *
   from dwd.fact_change_record
   order by change_machine_id desc
   limit 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;

4. 抽取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;

5. 抽取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 的方式对字段进行去重，因为没有涉及到分组聚合的功能，因此本小节也不是很难实现