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

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

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

盖了95%以上大数据知识点，真正体系化！**

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取