全国职业院校技能大赛-大数据 离线数据处理模块-指标计算

子任务三：指标计算

2. 编写scala代码，使用Spark根据dwd层的fact_produce_record表，基于全量历史增加设备生产一个产品的平均耗时字段（produce_per_avgtime），produce_code_end_time值为1900-01-01 00:00:00的数据为脏数据，需要剔除，并以produce_record_id和ProduceMachineID为联合主键进行去重（注：fact_produce_record表中，一条数据代表加工一个产品，produce_code_start_time字段为开始加工时间，produce_code_end_time字段为完成加工时间），将得到的数据提取下表所需字段然后写入dws层的表machine_produce_per_avgtime中，然后使用hive cli根据设备id降序排序查询前3条数据，将SQL语句复制粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下，将执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；

machine_produce_per_avgtime

字段	类型	中文含义	备注
produce_record_id	int	生产记录id	每生产一件产品产生一条数据
produce_machine_id	int	设备id
producetime	int	该产品耗时
produce_per_avgtime	int	设备生产一个产品平均耗时	单位：秒

 执行代码

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

object SparkReadDWDWriteDWS {
  def main(args: Array[String]): Unit = {

    // 创建sparkSession对象
    val conf = new SparkConf().setMaster("local[*]").setAppName("sparkSession")
    val spark: SparkSession = SparkSession.builder()
      .config(conf)
      .enableHiveSupport()
      .getOrCreate()

    // 启动动态分区
    spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
    // 关闭日志打印
    spark.sparkContext.setLogLevel("OFF")

    // 读取数据并剔除脏数据
    val df = spark.read
      .table("dwd.fact_produce_record")
      .filter("ProduceCodeEndTime != '1900-01-01 00:00:00'")
    df.createOrReplaceTempView("tempView")

    // 将查询的结果作为独立的表保存
    // 在group by 中进行联合主键去重
    // 如果使用group by,那么在查询语句中只能显示分组字段和聚合函数
    // 根据设备id进行分组，计算每个设备生产一个设备的平均时间
    val resultDF = spark.sql(
      """
        |SELECT
        |       t1.ProduceRecordID                                                           AS produce_record_id,
        |       t1.ProduceMachineID                                                          AS produce_machine_id,
        |       unix_timestamp(ProduceCodeEndTime) - unix_timestamp(ProduceCodeStartTime)    AS producetime,
        |       AVG(unix_timestamp(ProduceCodeEndTime) - unix_timestamp(ProduceCodeStartTime))
        |           OVER (PARTITION BY ProduceMachineID)                                     AS produce_per_avgtime
        |FROM (
        | SELECT
        |        ProduceRecordID,
        |        ProduceMachineID
        | FROM tempView
        | GROUP BY ProduceRecordID,ProduceMachineID
        | ) t1
        | LEFT JOIN (
        |     SELECT
        |         ProduceCodeStartTime,
        |         ProduceCodeEndTime,
        |         ProduceRecordID,
        |         ProduceMachineID,
        |     FROM tempView
        | ) t2 ON t1.ProduceRecordID = t2.ProduceRecordID
        | AND t1.ProduceMachineID = t2.ProduceMachineID
        |""".stripMargin) // 以ProduceRecordID,ProduceMachineID为联合主键进行去重
    resultDF.show()
    
    // 将数据保存到dws库中的machine_produce_per_avgtime中
    resultDF.write
      .mode("overwrite")
      .saveAsTable("dws.machine_produce_per_avgtime")

    // 使用hive cli根据设备id降序排序查询前3条数据
    // select * from dws.machine_produce_per_avgtime order by id desc limit 3;

    spark.close()
  }
}

 3.  编写scala代码，使用Spark根据dws层的machine_produce_per_avgtime表,获取各设备生产耗时最长的两个产品的用时,将计算结果存入MySQL数据库shtd_industry的machine_produce_timetop2表中（表结构如下），然后在Linux的MySQL命令行中根据设备id降序排序，查询出前2条，将SQL语句复制粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下，将执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；

字段	类型	中文含义	备注
machine_id	int	设备id
first_time	int	耗时最长
second_time	int	耗时次长

package com.atguigu.connecttest.writedws

import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}

object SparkReadDWDWriteDWS2 {
  def main(args: Array[String]): Unit = {

    // TODO 准备环境
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("spark")
    val spark = SparkSession.builder().config(conf).enableHiveSupport().getOrCreate()
    // 启动动态分区
    spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
    // 关闭打印日志
    spark.sparkContext.setLogLevel("OFF")

    val df: DataFrame = spark.read.table("dws.machine_produce_per_avgtime")
    // 创建临时表
    df.createOrReplaceTempView("tempView")

    // 这里的数据是基于第2题的结果的
    val hiveDF: DataFrame = spark.sql(
      """
        | select
        | machine_id,
        | if(rk = 1, producetime, null) first_time,
        | if(rk = 2, producetime, null) second_time
        | from (
        |   select
        |     machine_id,
        |     producetime,
        |     rank() over(partition by machine_id order by producetime desc) rk
        |   from tempView
        | ) t
        |""".stripMargin)
    // rank排名函数会可以直接根据machinc_id进行分区排序，因此可以不考虑手动通过group by分区


    // 将计算结果存入mysql
    hiveDF.write
      .format("jdbc")
      .mode(SaveMode.Append)
      .option("Driver", "com.mysql.jdbc.Driver")
      .option("url", "jdbc:mysql://hadoop102:3306/shtd_industry")
      .option("user", "root")
      .option("password", "000000")
      .option("dbtable", "machine_produce_timetop2")
      .save()

    // TODO 关闭连接
    spark.close()
  }
}

 在Linux的MySQL命令行中根据设备id降序排序，查询出前2条

select
    *
from shtd_industry.machine_produce_timetop2
oroup by machine_id
limit 2;

4. 编写Hive SQL代码，根据dwd层的fact_environment_data表，统计检测设备（BaseID）每月的平均湿度（Humidity），然后将每个设备的每月平均湿度与厂内所有检测设备每月检测结果的平均湿度做比较（结果值为：高/低/相同）存入MySQL数据库shtd_industry的machine_humidityAVG_compare表中（表结构如下），然后在Linux的MySQL命令行中根据检测设备ID降序排序，查询出前5条，将SQL语句复制粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下，将执行结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；

字段	类型	中文含义	备注
base_id	int	检测设备ID
machine_avg	varchar	单设备检测平均值
factory_avg	varchar	厂内所有设备平均值
comparison	varchar	比较结果	高/低/相同
env_date_year	varchar	检测年份	如：2021
env_date_month	varchar	检测月份	如：12

这里由于没有数据字段，无法写SQL，只能做一个大致的分析

--这里应该有两个子查询
-- 一个根据每月进行分组查询厂内所有检测设备每月检测结果的平均值
-- 一个根据设备id进行分组
--统计检测设备每月的平均湿度首先需要根据每个月份进行分组，比较湿度可以使用函数case when

-- 这里可以创建一个hive外部表，然后将这个hive外部表转换为mysql表
create table machine_humidityAVG_compare(
   base_id int,   -- 检测设备id
   machine_avg varchar(50),   -- 单设备平均值
   factory_avg varchar(50),   -- 厂内所有设备平均值
   comparison varchar(50),  -- 比较结果
   env_date_year varchar(50), -- '检测年份'
   env_date_month varchar(50), -- '检测月份'
)
STORED BY 'org.apache.hadoop.hive.mysql.MySQLStorageHandler'
WITH SERDEPROPERTIES (
    'mysql.host' = 'hadoop102',
    'mysql.port' = '3306',
    'mysql.database' = 'shtd_industry',
    'mysql.table' = 'machine_humidityAVG_compare'
)
TBLPROPERTIES ('hive.table.description' = 'MySQL table');

-- 根据上面的表格的测试代码仅供参考
insert into table machine_humidityAVG_compare
select
    base_id,
    machine_avg,
    factory_avg,
    case 
        when  machine_avg > factory_avg  then '高'
        when  machine_avg < factory_avg  then '低'
        when  machine_avg == factory_avg  then '相同'
    end comparison, -- 比较结果
    env_date_year,
    env_date_month
from (
    select
        base_id, -- 检测设备id
        env_date_year, -- '检测年份'
        env_date_month, -- '检测月份'
        avg(湿度) over(partition by env_date_month) machine_avg -- 单设备平均值
    from dwd.fact_environment_data
    group by base_id
) one
left join (
    select
        env_date_year, -- '检测年份'
        env_date_month, -- '检测月份'
        avg(湿度) over(partition by env_date_month) factory_avg -- 厂内所有设备平均值
    from dwd.fact_environment_data
) all on one.env_date_year = all.env_date_year 
and one.env_date_month = all.env_date_month