大数据之指标计算（2） -- 使用Spark根据dwd层fact_change_record表关联dim_machine表统计每个车间中所有设备运行时长...将计算结果存入clickhouse数据库

目录

前言

题目：

一、读题分析

二、处理过程

三、重难点分析

总结 

---

前言

本题来源于全国职业技能大赛之大数据技术赛项工业数据处理赛题 - 离线数据处理 - 指标计算

注：由于个人设备问题，代码执行结果以及最后数据显示结果将不会给出。

题目：

 

---

提示：以下是本篇文章正文内容，下面案例可供参考（使用Scala语言编写） 

一、读题分析

涉及组件：Scala,Spark,clickhouse,hive

涉及知识点：

1. hive数据表关联表
2. 数据处理与计算
3. 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
     */
  }
}

 

---

三、重难点分析

1. 题目要求求出运行的总时长，这个要看怎么去理解它了，是一台设备的总时长，还是一个车间设备的总时长。
2. 中位数在哪个设备（为偶数时，两条数据原样保留输出）

本期为指标计算第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的博客