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

赛题来源2023年全国职业院校技能大赛赛题第5套任务B中数据清洗模块

子任务二：数据清洗

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

第一题

抽取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】中对应的任务序号下

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

object DWD01 {
  def main(args: Array[String]): Unit = {
    System.setProperty("HADOOP_USER_NAME", "root")

    val conf = new SparkConf().setMaster("local[*]").setAppName("DWD01")
    val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()

    spark.conf.set("hive.exec.dynamic.partitions.mode", "nonstrict")
    spark.sparkContext.setLogLevel("OFF")

    val querySelect = spark.sql(
      """
        | select
        |   t2.*,
        |   t1.changeid,
        |   t1.changemachineid,
        |   'user1' dwd_insert_user,
        |   date_format(current_date(), 'yyyy-MM-dd HH:mm:ss') dwd_insert_time,
        |   'user1' dwd_modify_user,
        |   date_format(current_date(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time,
        |   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
        |""".stripMargin)

    // 将查询的结果写出到dwd库中指定的表中
    querySelect.write
     .format("hive")
     .mode("append")
     .partitionBy("etldate")
     .saveAsTable("dwd.fact_change_record")
  }
}

使用hive cli执行SQL查看结果

select * 
from dwd.fact_change_record
order by change_machine_id desc
limit 1; 

第二题

抽取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】中对应的任务序号下

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
import scala.language.postfixOps

object DWD02 {
  def main(args: Array[String]): Unit = {
    System.setProperty("HADOOP_USER_NAME", "root")

    val conf = new SparkConf().setMaster("local[*]").setAppName("DWD02")
    val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()

    spark.conf.set("hive.exec.dynamic.partitions.mode", "nonstrict")
    spark.sparkContext.setLogLevel("OFF")

    val queryResult = spark.sql(
      """
        | select
        |   t2.*,
        |   t1.basemachineid,
        |   'user1' dwd_insert_user,
        |   date_format(current_date, 'yyyy-MM-dd HH:mm:ss') dwd_insert_time,
        |   'user1' dwd_modify_user,
        |   date_format(current_date, 'yyyy-MM-dd HH:mm:ss') dwd_modify_time,
        |   etldate
        | from (
        |   select
        |     basemachineid
        |   from ods.basemachine
        |   group by basemachineid
        | ) t1 left join (
        |   select
        |     *,
        |     basemachineid，
        |     etldate
        |   from ods.basemachine
        | ) t2 on t1.basemachineid = t2.basemachineid
        |""".stripMargin)

    queryResult.write
      .format("hive")
      .mode("append")
      .partitionBy("etldate")
      .saveAsTable("dwd.dim_machine")

    spark.close()
  }
}

使用hive cli执行SQL查看结果

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

第三题

抽取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前1条数据，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

object DWD03 {
  def main(args: Array[String]): Unit = {
    System.setProperty("HADOOP_USER_NAME", "root")

    val conf = new SparkConf().setMaster("local[*]").setAppName("DWD03")
    val spark = SparkSession
      .builder
      .config(conf)
      .enableHiveSupport
      .getOrCreate

    val queryResult = spark.sql(
      """
        | select
        |   *,
        |   'user1' dwd_insert_user,
        |   date_format(current_date(), 'yyyy-MM-dd HH:mm:ss') dwd_insert_time,
        |   'user1' dwd_modify_user,
        |   date_format(current_date(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time,
        |   etldate
        | from (
        |   select
        |     *,
        |     etldate
        |   from ods.producerecord
        | )
        |""".stripMargin)

    queryResult.write
      .format("hive")
      .partitionBy("etldate")
      .saveAsTable("dwd.fact_produce_record")

    spark.close()
  }
}

使用hive cli执行SQL查看结果

select * from dwd.fact_produce_record order by produce_machine_id asc limit 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】中对应的任务序号下

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

object DWD04 {
  def main(args: Array[String]): Unit = {
    System.setProperty("HADOOP_USER_NAME", "root")

    val conf = new SparkConf().setMaster("local[*]").setAppName("DWD04")
    val spark = SparkSession
      .builder
      .config(conf)
      .enableHiveSupport()
      .getOrCreate()

    val queryResult = spark.sql(
      """
        | select
        |   *,
        |   'user1' dwd_insert_user,
        |   date_format(current_date(), 'yyyy-MM-dd HH:mm:ss') dwd_insert_time,
        |   'user1' dwd_modify_user,
        |   date_format(current_date(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time,
        |   etldate
        | from (
        |   select
        |     *,
        |     etldate
        |   from ods.machinedata
        | )
        |""".stripMargin)

    queryResult.write
      .format("hive")
      .partitionBy("etldate")
      .saveAsTable("dwd.fact_machine_data")

    spark.close()
  }
}

使用hive cli查询结果

select * from dwd.fact_machine_data order by machine_id desc limit 1;

数据清洗部分如果没有如果需要进行联合去重，那么只需要根据这两个字段进行group by操作就可以了。