子任务三:指标计算
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