大数据之指标计算（6） -- 编写Hive SQL代码，根据dwd层dwd.fact_environment_data表，统计检测设备的每月平均湿度与厂内检测结果做对比存入Mysql数据库中

admin管理员组

文章数量:1536090

目录

 

---

前言

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

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

题目：

 

---

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

一、读题分析

涉及组件：Hive

涉及知识点：

1. HiveSQL语法的使用
2. ...

---

二、处理过程

        本题给出两种参考方法一种是编写HiveSQL代码，另外一种是编写Scala代码使用spark处理框架去写，本质上差不多，调用的是SparkSQL。

但需注意的是：本题两种代码，作者均为测试证实，仅供参考。

 

1.HiveSQL

-- 在mysql端建表
create table machine_humidityAVG_compare
(
    base_id        int,
    machine_avg    varchar(255),
    factory_avg    varchar(255),
    comparison     varchar(255),
    env_date_year  varchar(255),
    env_date_month varchar(255)
);



with temp as
         (select base_id,
                 machine_avg,
                 avg(machine_avg) over () as factory_avg,
                 case
                     when machine_avg > avg(machine_avg) over () then '高'
                     when machine_avg < avg(machine_avg) over () then '低'
                     else '相同' end        as comparison,
                 env_date_year,
                 env_date_month
          from (select t.BaseID              base_id,
                       avg(t.Humidity)    as machine_avg,
                       year(t.InPutTime)  as env_date_year,
                       month(t.InPutTime) as env_date_month
                from fact_environment_data t
                group by t.BaseID, year(t.InPutTime), month(t.InPutTime)))
insert
overwrite
table
shtd_industry.machine_humidityAVG_compare
select *
from temp;
-- 不能导入，需要用sqoop导入mysql

select * from shtd_industry.machine_humidityAVG_compare order by base_id desc limit 5;

2.SparkSQL

import org.apache.spark.sql.SparkSession

import java.util.Properties

object answer_No6 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("MachineAvgProduceTime").master("local[*]").enableHiveSupport()
      .getOrCreate()

    val df = spark.sql(
      """
        |
        |select base_id,
        |                 machine_avg,
        |                 avg(machine_avg) over () as factory_avg,
        |                 case
        |                     when machine_avg > avg(machine_avg) over () then '高'
        |                     when machine_avg < avg(machine_avg) over () then '低'
        |                     else '相同' end        as comparison,
        |                 env_date_year,
        |                 env_date_month
        |          from (select t.BaseID              base_id,
        |                       avg(t.Humidity)    as machine_avg,
        |                       year(t.InPutTime)  as env_date_year,
        |                       month(t.InPutTime) as env_date_month
        |                from dwd.fact_environment_data t
        |                group by t.BaseID, year(t.InPutTime), month(t.InPutTime))
        |
        |""".stripMargin)
    val url = "jdbc:mysql://192.168.59.100:3306/shtd_industry"
    val prop = new Properties()
    prop.setProperty("user", "root")
    prop.setProperty("password", "123456")

    df.write.mode("overwrite").jdbc(url, "machine_humidityAVG_compare", prop)

  }


}

 

---

三、重难点分析

        每个设备的每月平均湿度与厂内所有检测设备每月检测结果的平均湿度做比较（结果值为：高/低/相同），在这里实现起来还是有点难度的。使用了case配对。

        本期为指标计算第6篇，后续应该还会出2篇。

---

总结 

这是一个关于使用Hive SQL统计检测设备每月平均湿度并将结果存储在MySQL数据库中，并与厂内所有检测设备每月检测结果的平均湿度做比较，然后在Linux的MySQL命令行工具中查询前五条检测设备ID倒序排列的数据的问题。下面是具体的要求和表结构：

• 在dwd层fact_environment_data表中，统计每个检测设备（BaseID）每月的平均湿度（Humidity），得到base_id、env_date_year、env_date_month和每月平均湿度字段。
• 将每个检测设备的每月平均湿度与厂内所有检测设备每月检测结果的平均湿度做比较，得到设备id（base_id）、单设备每月检测平均值（machine_avg）、厂内所有设备每月检测平均值（factory_avg）和比较结果（comparison，取值为“高”、“低”或“相同”）字段。
• 将比较结果存储在MySQL数据库shtd_industry的表machine_humidityAVG_compare中，表结构包含base_id、machine_avg、factory_avg、comparison、env_date_year和env_date_month字段。
• 通过Linux的MySQL命令行工具查询出前5条检测设备ID倒序排列的数据，将SQL语句和执行结果截图粘贴到报告中。

注意：这个题目需要掌握Hive SQL和MySQL数据库以及Linux命令行工具的使用。

 

        请关注我的大数据技术专栏大数据技术 作者: Eternity.Arrebol

        请关注我获取更多与大数据相关的文章Eternity.Arrebol的博客

Q-欢迎在评论区进行交流-Q

 

本文标签： 湿度数据库中检测设备厂内平均