src/FluxUtil_1.scala
1package cn.piflow.bundle.flux.util
2
3
4import cn.piflow.bundle.flux.util.CommonUtil.SunriseAndSunsetTime
5import org.apache.spark.sql.expressions.Window
6import org.apache.spark.sql.functions.row_number
7import org.apache.spark.sql.types.{DoubleType, IntegerType}
8import org.apache.spark.sql.{DataFrame, SparkSession}
9
10import scala.collection.mutable.ArrayBuffer
11
12
13
14class FluxUtil_1 extends Serializable {
15
16 /**
17 * 单一字段阈值替换 ,不符合需求的数据 ,用标识位赋值 (包含上下限)
18 *
19 * @param spark
20 * @param originDF
21 * @param fields 要清洗的字段,多个以逗号分隔
22 * @param maxValue 阈值上限
23 * @param minValue 阈值下限
24 * @param flagValue 标识位赋值,不符合条件
25 * @param additionalCondition 标识位赋值,不符合条件
26 * @return
27 */
28 def singleFieldThresholdCleaning(spark: SparkSession, originDF: DataFrame, fields: String, maxValue: String, minValue: String, flagValue: String, additionalCondition: String): DataFrame = {
29
30 val thresholdReplaceSqlBuilder = new StringBuilder()
31 originDF.schema.fieldNames.foreach(x => {
32 if (fields.split(",").contains(x)) {
33// val str = s"if(`${x}` <= ${maxValue.toDouble} and `${x}` >= ${minValue.toDouble} ,`${x}`,'${flagValue}' ) as `${x}`"
34 val str = s"if(`${x}` <= ${maxValue.toDouble} and `${x}` >= ${minValue.toDouble} and ${additionalCondition} ,`${x}`,'${flagValue}' ) as `${x}`"
35 thresholdReplaceSqlBuilder.append(str + ",")
36 } else {
37 thresholdReplaceSqlBuilder.append("`" + x + "`,")
38 }
39 })
40
41 spark.catalog.dropTempView("singleDF")
42 originDF.createOrReplaceTempView("singleDF")
43
44 val sqlStr = s"select ${thresholdReplaceSqlBuilder.toString.stripSuffix(",")} from singleDF"
45 println(sqlStr)
46 val resultDF = spark.sql(sqlStr)
47
48 spark.sql("show tables").show()
49 resultDF
50 }
51
52
53 /**
54 * 批量阈值替换 根据阈值表中的数据,不符合需求的数据 ,用标识位赋值(包含上下限)
55 *
56 * @param spark
57 * @param originDF
58 * @param thresholdDF 阈值表: schema 需包含 field ,min_value ,max_value,flagValue 字段
59 * @param flagValue 标识位赋值,不符合条件
60 * @return
61 */
62 def batchFieldThresholdCleaning(spark: SparkSession, originDF: DataFrame, thresholdDF: DataFrame): DataFrame = {
63 // 阈值表中的所有字段名
64 val thresholdFields: ArrayBuffer[String] = new ArrayBuffer[String]()
65
66 thresholdDF.select("field").collect().foreach(x => {
67 thresholdFields.append(x.getString(0).toLowerCase.trim)
68 })
69
70 val thresholdReplaceSqlBuilder = new StringBuilder()
71 originDF.schema.fieldNames.foreach(x => {
72 if (thresholdFields.contains(x.toLowerCase)) {
73 val minValue = thresholdDF.where(s"field = '${x}'").select("min_value").first().get(0).toString.toDouble
74 val maxValue = thresholdDF.where(s"field = '${x}'").select("max_value").first().get(0).toString.toDouble
75 val flagValue = thresholdDF.where(s"field = '${x}'").select("flag_value").first().get(0).toString
76 val str = s"if(`${x}` <= ${maxValue} and `${x}` >= ${minValue} ,`${x}`,'${flagValue}' ) as `${x}`"
77 thresholdReplaceSqlBuilder.append(str + ",")
78 } else {
79 thresholdReplaceSqlBuilder.append("`" + x + "`,")
80 }
81 })
82
83 spark.catalog.dropTempView("batchFieldDF")
84 originDF.createOrReplaceTempView("batchFieldDF")
85 val sqlStr = s"select ${thresholdReplaceSqlBuilder.toString.stripSuffix(",")} from batchFieldDF "
86 val resultDF = spark.sql(sqlStr)
87
88 resultDF
89 }
90
91
92 /**
93 * 根据年月日小时及 日的id 添加 当前时间的状态【白天或者黑夜(按照日出日落时间定义)】
94 *
95 * @param spark
96 * @param originDF schema 必须包含 year ,month ,day, day_id(按照日期递增的 id)
97 * @param longitude 经度
98 * @param latitude 维度
99 * @return
100 */
101 def AddDayStateWithDayHour(spark: SparkSession, originDF: DataFrame, longitude: Double, latitude: Double): DataFrame = {
102 // val longitude:Double = 128.1
103 // val latitude:Double = 42.4
104
105 spark.udf.register("SunriseAndSunset", (id: String) => {
106 SunriseAndSunsetTime(longitude, latitude, id.toInt);
107 })
108 originDF.createOrReplaceTempView("addDayStateWithDayHour")
109 val resultDF = spark.sql(
110 """
111 |select if(cast(hour as double)>split(SunriseAndSunset(day_id),',')[0]
112 | and cast(hour as double)<split(SunriseAndSunset(day_id),',')[1],'day','night') as day_status
113 | ,*
114 | from addDayStateWithDayHour
115 |""".stripMargin)
116
117 resultDF
118 }
119
120
121 /**
122 * 按照时间日期 ,新增 day_id 字段 (按照日期递增的 id)
123 *
124 * @param spark
125 * @param originDF schema 必须包含 year ,month ,day
126 * @return
127 */
128 def AddIdWithDay(spark: SparkSession, originDF: DataFrame,partitionNum:Int): DataFrame = {
129
130 import spark.implicits._
131 val df_id = originDF.withColumn("year",$"year".cast(IntegerType))
132 .withColumn("month",$"month".cast(IntegerType))
133 .withColumn("day",$"day".cast(IntegerType))
134 .withColumn("hour",$"hour".cast(DoubleType))
135
136 val spec_id = Window.partitionBy().orderBy("year", "month", "day","hour")
137 val df_id_1= df_id.withColumn("id",row_number().over(spec_id))
138
139 val df_day_id = df_id.select("year","month","day").distinct()
140 val spec_day_id = Window.partitionBy().orderBy("year", "month", "day")
141 val df_day_id_1= df_day_id.withColumn("day_id",row_number().over(spec_day_id))
142
143
144 // 给通量数据,按照时间日期 ,新增 id 字段
145 val resultDF = df_id_1.join(df_day_id_1, Seq("year", "month", "day"),"left")
146 .repartition(partitionNum)
147 resultDF
148 }
149
150}