Source code

Download source archive ↓

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}