flink sql 之从dataStream和ddl中定义eventTime，group window，over window（4）

代码

package flinkSql

import flinkSourse.SensorReading
import org.apache.flink.streaming.api.TimeCharacteristic
import org.apache.flink.streaming.api.functions.timestamps.BoundedOutOfOrdernessTimestampExtractor
import org.apache.flink.streaming.api.scala._
import org.apache.flink.streaming.api.windowing.time.Time
import org.apache.flink.table.api.{EnvironmentSettings, Table}
import org.apache.flink.table.api.scala._
import org.apache.flink.types.Row

case class SensorReading(id: String, timestamp: Long, temperature: Double)

object FlinkSqlLession3_DatastreamToSqlEventTime {
  def main(args: Array[String]): Unit = {
    val executionEnvironment: StreamExecutionEnvironment = StreamExecutionEnvironment.getExecutionEnvironment
    executionEnvironment.setStreamTimeCharacteristic(TimeCharacteristic.EventTime) 
    executionEnvironment.setParallelism(1)
    //ddl形式必须使用blink planer ，2.1 blink版本planer的流处理，有setting的情况
    val blinkStreamSettings: EnvironmentSettings = EnvironmentSettings.newInstance().useBlinkPlanner().inStreamingMode().build()
    val tableEnvironment: StreamTableEnvironment = StreamTableEnvironment.create(executionEnvironment, blinkStreamSettings)

    //第一种从流数据中定义和获取event_time
    //    val stream2: DataStream[String] = executionEnvironment.socketTextStream("127.0.0.1", 1111)
    //    val transforStream: DataStream[SensorReading] = stream2.map(data => {
    //      val tmpList: Array[String] = data.split(",")
    //      SensorReading(tmpList(0), tmpList(1).toLong, tmpList(2).toDouble)
    //    }).assignTimestampsAndWatermarks(new BoundedOutOfOrdernessTimestampExtractor[SensorReading](Time.seconds(0)) {
    //      override def extractTimestamp(t: SensorReading) = t.timestamp * 1000
    //    })
    //    //FlinkSql,从流里面定义eventTime，转为table，执行sql
    //    tableEnvironment.createTemporaryView("sensorTable", transforStream, 'id, 'temperature, 'timestamp.rowtime)

    //第二种从ddl中定义和获取eventTime
    val sourceDDl: String =
      """
        | create table sensorTable(
        | id varchar(20),
        | timestampdata bigint,
        | temperature double,
        | rs as TO_TIMESTAMP(FROM_UNIXTIME(timestampdata)),
        | watermark for rs as rs - interval '0' second
        | ) with (
        | 'connector.type'='filesystem',
        | 'connector.path'='/Users/zha/untitled/src/main/resources/sensorReading.txt',
        | 'format.type'='csv'
        | )
        |""".stripMargin

    tableEnvironment.sqlUpdate(sourceDDl)


    //group window，实现计算每10s中计算每个id的数量，和温度的均值
    val tumbleSql: String =
      """
        | select id,
        |   count(id),
        |   avg(temperature),
        |   tumble_end(rs,interval '10' second)
        | from sensorTable
        | group by
        |   id,
        |   tumble(rs,interval '10' second)
        |""".stripMargin


    //over window，实现计算每行记录和前两行记录的中的数量，和温度的均值
    val overWindowSql =
      """
        | select id,
        |   rs,
        |   count(id) over ow,
        |   avg(temperature) over ow
        | from sensorTable
        | window ow as (
        |   partition by id
        |   order by rs
        |   rows between 2 preceding and current row
        | )
        |""".stripMargin


//    val sqlTable: Table = tableEnvironment.sqlQuery(tumbleSql)
    val sqlTable: Table = tableEnvironment.sqlQuery(overWindowSql)
    //当是group window 和 over window这种窗口聚合的时候，toAppendStream，toRetractStream是一样的
    sqlTable.toAppendStream[Row].print("toAppenStreamSqlResult")
    sqlTable.toRetractStream[Row].print("toRetractStreamSqlResult")

    executionEnvironment.execute("flink sql")
  }
}

输入

sensor_1,1547718199,32.9
sensor_1,1547718209,30.7
sensor_1,1547718208,34.5
sensor_1,1547718211,34.5
sensor_1,1547718213,30.9
sensor_1,1547718212,28
sensor_1,1547718225,30
sensor_1,1547718228,34
sensor_1,1547718213,23
sensor_1,1547718288,21
sensor_2,1547718219,21