目录
前言
题目：
一、读题分析
二、处理过程
三、重难点分析
总结
前言
本题来源于全国职业技能大赛之大数据技术赛项工业数据处理赛题 - 离线数据处理 - 指标计算
注：由于个人设备问题，代码执行结果以及最后数据显示结果将不会给出。
题目：
提示：以下是本篇文章正文内容，下面案例可供参考（使用Scala语言编写）
一、读题分析
涉及组件：Scala,Spark,clickhouse,hive
涉及知识点：
hive数据表关联表数据处理与计算clickhouse数据库的使用
二、处理过程
import org.apache.spark.sql.functions.{col, unix_timestamp}
import org.apache.spark.sql.{SparkSession, functions}
import java.util.Properties
object answer_No2 {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().appName("machine").master("spark://hadoop100:7077")
.enableHiveSupport().getOrCreate()
val fact_change_record = spark.read.table("dwd.fact_change_record")
val dim_machine = spark.read.table("dwd.dim_machine")
// 将fact_change_record与dim_machine表关联，得到运行时长总和
val fi_result = fact_change_record.filter("ChangeRecordState = '运行'")
.filter("ChangeEndTime is not null")
.join(dim_machine, fact_change_record("ChangeMachineID") === dim_machine("BaseMachineID"))
.groupBy("MachineFactory", "ChangeMachineID")
.agg(functions.sum(
unix_timestamp(col("ChangeEndTime")) - unix_timestamp(col("ChangeStartTime"))
).alias("total_running_time"))
// fi_result.show()
fi_result.createOrReplaceTempView("tempView")
// 根据车间和设备id分组，统计中位数，结果存入clickhouse数据库shtd_industry的表machine_running_median中
val df = spark.sql(
"""
|WITH temp AS (
| SELECT MachineFactory, ChangeMachineID, total_running_time,
| ROW_NUMBER() OVER (PARTITION BY MachineFactory ORDER BY total_running_time) AS row_num,
| COUNT(*) OVER (PARTITION BY MachineFactory) AS total_num
| FROM tempView
|)
|SELECT MachineFactory, ChangeMachineID, total_running_time
|FROM temp
|WHERE row_num IN (CEIL(total_num / 2), FLOOR(total_num / 2) + 1)
|
|
|""".stripMargin)
/*法二，不清楚偶数情况下是否能保留两个
spark.sql(
"""
SELECT
MachineFactory,
ChangeMachineID,
percentile(total_running_time, 0.5) as median_running_time
FROM
running_time
GROUP BY
MachineFactory,
ChangeMachineID
ORDER BY
MachineFactory DESC,
ChangeMachineID DESC
""")*/
df.show()
// Linux的clickhouse命令行中根据所属车间、设备id均为倒序排序，option("url", "jdbc:clickhouse://10.0.0.1:8123/shtd_industry")
// 涉及clickhouse数据库，改用mysql数据库存前缀为clickhouse11_
//由于没有导包，设备没有该数据库，所以代码没有运行起来，请自行搜索导包运行,存入clickhouse没有经过测试-仅供参考。
{
sendToClickHouse(df,"shtd_industry.machine_running_median","","root","")
import org.apache.spark.sql.{DataFrame, SparkSession}
import ru.yandex.clickhouse.{ClickHouseConnection, ClickHouseDataSource, ClickHousePreparedStatement}
import java.sql.Types
def sendToClickHouse(data: DataFrame, tableName: String, clickhouseUrl: String, clickhouseUser: String, clickhousePassword: String): Unit = {
// 建立 ClickHouse 连接
val dataSource = new ClickHouseDataSource(clickhouseUrl, clickhouseUser, clickhousePassword)
// 将 Spark Dataset 转为 ClickHouse PreparedStatement
data.foreachPartition(it => {
// 获取连接
val connection: ClickHouseConnection = dataSource.getConnection.asInstanceOf[ClickHouseConnection]
connection.setAutoCommit(false)
// 构建 PreparedStatement
val query = s"INSERT INTO $tableName (machine_id, machine_factory, total_running_time) VALUES (?, ?, ?)"
val stmt: ClickHousePreparedStatement = connection.prepareStatement(query)
// 遍历数据集并向 ClickHouse 写入数据
while (it.hasNext) {
val row = it.next()
try {
stmt.setInt(1, row.getInt(0))
stmt.setInt(2, row.getInt(1))
stmt.setInt(3, row.getInt(2))
stmt.addBatch()
} catch {
case e: Exception => println("行信息解析失败: " + e.getMessage)
}
}
// 执行批处理
stmt.executeBatch()
connection.commit()
connection.close()
})
}
}
// 查询出前10条数据
/*
select
*
from
machine_running_median
order by
machine_id,machine_factory
limit 10
*/
}
}
三、重难点分析
题目要求求出运行的总时长，这个要看怎么去理解它了，是一台设备的总时长，还是一个车间设备的总时长。中位数在哪个设备（为偶数时，两条数据原样保留输出）
本期为指标计算第2篇，后续应该还会出3篇。
总结
使用Scala和Spark统计设备运行时长中位数并将结果存储到clickhouse数据库中，并在Linux的clickhouse命令行中查询前十条数据的问题。下面是具体的要求和表结构：
在dwd层fact_change_record表中关联dim_machine表，统计每个车间中所有设备运行时长的中位数（设备状态为“运行”，未结束的状态不参与计算）。将计算结果存储到clickhouse数据库shtd_industry的表machine_running_median中，表结构包含machine_id（设备id）、machine_factory（所属车间）和 total_running_time（运行总时长，以秒为单位）字段。通过Linux的clickhouse命令行工具查询出前10条车间和设备id均为倒序排列的运行总时长数据，将SQL语句和执行结果截图粘贴到报告中。
注意：这个题目需要掌握Spark编程、clickhouse数据库以及Linux命令行工具的使用。
请关注我的大数据技术专栏大数据技术 作者: Eternity.Arrebol
请关注我获取更多与大数据相关的文章Eternity.Arrebol的博客
精彩内容
您阅读本篇文章共花了：
发表评论