常规使用不提,主要是使用不同模式完成排序,数据更新操作,算是对小结2的补充优化。
1、排序,使用complete模式,将数据流看做静态表,不断追加数据,通过order by可以实现排序功能。
val df =spark.readStream
.format("kafka")
.option("kafka.bootstrap.servers", "*:9092")
// .option("kafka.bootstrap.servers", "*:9092")
.option("subscribe", "test")
.option("startingOffsets", "latest")
.load()
.selectExpr("CAST(value AS STRING)")
.as[String]
.filter(line => line.split(",").length > 2)
// // .dropDuplicates()
.map(line => {
val mes = line.split(",")
val provincial_centre_id = mes(0).toInt
val terminal_id = mes(1).toLong
val timestamp = mes(2).toLong
val value = mes(3).toInt
val time = MyUtils.getSqlTimes(timestamp)
(provincial_centre_id,terminal_id,timestamp,value,time)
})
.toDF("provincial_centre_id","terminal_id","timestamp","value","time")
.selectExpr("provincial_centre_id","terminal_id" ,"timestamp","value","time")
.withWatermark("time","10 seconds")
// .groupBy("time","provincial_centre_id","terminal_id","timestamp","value").count()
// .repartition(100,new Column("terminal_id"))
.createOrReplaceTempView("t_odscb_sales")
// spark.sql("select * from b_mapping_siteclerk limit 3").show()
println("=====================>2")
// val sql_sale = "select terminal_id,max(provincial_centre_id) as provincial_centre_id," +
// " max(timestamp) as timestamp,1 as value,max(time) as time ,count(1)" +
// " from t_odscb_sales " +
// " group by terminal_id " +
// " "
val sql_sale = "select terminal_id,provincial_centre_id," +
" timestamp,time ,count(1)" +
" from t_odscb_sales " +
" group by terminal_id,provincial_centre_id,timestamp,time" +
" order by timestamp " +
" "
// // println(sql_sale)
val df1 = spark.sql(sql_sale)
// val query1 = df
.writeStream
.outputMode("complete")
// .foreach(new Test)
.format("console")
.option("checkpointLocation", directory1)
.start()
println("=====================>3")
spark.streams.awaitAnyTermination()
排序结果,好用
官网说配合withWatermark实现数据过期处理,通过实验,结果失败,数据不能过期,随着数据量增大,表的数据会很大。如何配合使用,有待后续继续研究。( .withWatermark("time","10 seconds")无效)
2、update模式配合max聚合,不支持排序order by。进行计数统计,使用 .withWatermark("time","10 seconds")无效。
去重可用,.dropDuplicates("timestamp")
去重加时间范围。去重可用, withWatermark("time","10 seconds")无效。
去重加sql语句的时间控制进行数据处理
.dropDuplicates("timestamp") " where timestamp >= unix_timestamp()-60 " +
3、append
.withWatermark("time","10 seconds")延迟一个批次显示
使用聚合函数需要配合
.withWatermark("time","10 seconds")使用