20240901 大数据流式计算 -FLINK1.19（时序信号转事件场景）

时序信号转事件常用场景

对于时序信号数据（常见的IOT信号数据，比如最简单的：有无序列 000011110001010101 ）；需要将乱序有延时的信号，转换为时序按一定业务pattern识别后的有意义的事件（一段段不同状态的开始到结束范围）。并将事件结果同时向Mysql和redis输出（也可以是其他sink connector）。

以下是基于flinkSQL和窗口函数的简单实现实验。（*也可以基于高级抽象CEP实现）

flinkSQL 1.15.2的参考实现（baseline）：

1. mysql 事件sink表， 需要jdbc connector

<dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-connector-jdbc_2.12</artifactId>
            <version>1.14.4</version>
        </dependency>

2. 写入redis，需要redis connector

<dependency>
            <groupId>io.github.jeff-zou</groupId>
            <artifactId>flink-connector-redis</artifactId>
            <version>1.3.3</version>
</dependency>

3. flink SQL 实现事件实时触发

public static void main(String[] args) throws Exception {
        try {
            Map<String, String> argMap = Util.parseArgs(args);

            StreamExecutionEnvironment settings = StreamExecutionEnvironment.getExecutionEnvironment();
            System.out.println("=======================ETL02Stream_03=======================");
            settings.enableCheckpointing(5000); //5 seconds

            StreamTableEnvironment tEnv = StreamTableEnvironment.create(settings);
            tEnv.getConfig().getConfiguration().setString("pipeline.name", "ETL02Stream_03");

            TableResult tableResult = tEnv.executeSql(
                    "CREATE TABLE HB_TBL (  \n" +
                    "    ID           VARCHAR,     \n" +
                    "    DEVICE_ID    VARCHAR,     \n" +
                    "    FLAG         INT,         \n" +
                    "    EVENT_TIME   TIMESTAMP(3),\n" +
                    "    WATERMARK FOR EVENT_TIME AS EVENT_TIME - INTERVAL '5' SECOND,\n" +
                    "    primary key(ID) NOT ENFORCED \n" +  //需要创建主键，来保证数据不重复
                    ") WITH (\n" +
                            "  'connector'  = 'jdbc',\n" +
                            "  'url'        = 'jdbc:mysql://localhost:33306/flink_poc?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai',\n" +
                            "  'table-name' = 'HB_TBL',\n" +
                            "  'driver'     = 'com.mysql.jdbc.Driver',\n" +
                            "  'username'   = 'root',\n" +
                            "  'password'   = 'root'\n" +
                    ")");

            /*===================================== sink to table and redis =======================================*/
            tEnv.executeSql("CREATE TABLE HB_EVENT_SINK_TBL (\n" +
                    "    EVENT_ID     VARCHAR,\n" +
                    "    DEVICE_ID    VARCHAR,\n" +
                    "    STOP_TIME    TIMESTAMP,\n" +
                    "    RESUME_TIME  TIMESTAMP,\n" +
                    "    primary key(EVENT_ID) NOT ENFORCED \n" +  //需要创建主键，来保证数据不重复
                    ") WITH (\n" +
                    "  'connector'  = 'jdbc',\n" +
                    "  'url'        = 'jdbc:mysql://localhost:33306/flink_poc?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai',\n" +
                    "  'table-name' = 'HB_EVENT_SINK_TBL',\n" +
                    "  'driver'     = 'com.mysql.jdbc.Driver',\n" +
                    "  'username'   = 'root',\n" +
                    "  'password'   = 'root'\n" +
                    ")");


            tEnv.executeSql("CREATE TABLE HB_EVENT_SINK_REDIS (\n" +
                    "    EVENT_ID          VARCHAR,\n" +
                    "    VAL               VARCHAR,\n" +
                    "    primary key(EVENT_ID) NOT ENFORCED \n" +  //需要创建主键，来保证数据不重复
                    ") WITH (\n" +
                    "  'connector'  = 'redis',\n" +
                    "  'host'       = 'localhost',\n" +
                    "  'port'       = '6379',\n" +
                    "  'redis-mode' = 'single',\n" +
                    "  'command'    = 'set'\n" +
                    ")");

            tEnv.executeSql("create view HB_VW as " +
                    "select " +
                    "  ID " +
                    ", DEVICE_ID " +
                    ", FLAG " +
                    ", EVENT_TIME " +
                    ", LAG(FLAG)       over(partition by DEVICE_ID order by EVENT_TIME) NEXT_FLAG " +
                    ", LAG(EVENT_TIME) over(partition by DEVICE_ID order by EVENT_TIME) NEXT_EVENT_TIME " +
                    "from HB_TBL " );


            StatementSet statementSet = tEnv.createStatementSet();
            String sql="insert into HB_EVENT_SINK_TBL(EVENT_ID, DEVICE_ID, STOP_TIME, RESUME_TIME)  " +
                    "select concat(x.DEVICE_ID,'|',uuid()) EVENT_ID, x.DEVICE_ID, CAST(x.NEXT_EVENT_TIME AS TIMESTAMP), CAST(x.EVENT_TIME AS TIMESTAMP) " +
                    "from HB_VW x " +
                    "where x.FLAG = 1 and x.NEXT_FLAG = 0 ";
            System.out.println(sql);
            statementSet.addInsertSql(sql);

            String sql2="insert into HB_EVENT_SINK_REDIS(EVENT_ID, VAL)  " +
                    "select concat(x.DEVICE_ID,'|',uuid()) EVENT_ID, JSON_OBJECT('DEVICE_ID' VALUE x.DEVICE_ID, 'STOP_TIME' VALUE x.NEXT_EVENT_TIME, 'RESUME_TIME' VALUE x.EVENT_TIME) VAL " +
                    "from HB_VW x " +
                    "where x.FLAG = 1 and x.NEXT_FLAG = 0 ";
            System.out.println(sql2);
            statementSet.addInsertSql(sql2);
            statementSet.execute();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

mysql事件结果：

EVENT_ID	DEVICE_ID	STOP_TIME	RESUME_TIME
D001|ba3d1f6a-70a2-4027-8faa-418f14d7dce7	D001	2024-03-31 00:04:00	2024-03-31 00:05:00
D001|e3dcdde2-ac5f-4a86-87d7-2047db9df61d	D001	2024-03-31 00:07:00	2024-03-31 00:08:00

redis事件结果：