Flink个人学习整理-SQL篇（十四）

Flink个人学习整理-SQL篇（十四）

一、查询
1.1、未注册表 demo

public class Flink_SQL_NoTable {
    public static void main(String[] args) throws Exception {
        //  获取运行时环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env);

        SingleOutputStreamOperator<WaterSensor> localhost = env.socketTextStream("localhost", 9999)
                .map(new MapFunction<String, WaterSensor>() {
                    @Override
                    public Sensor map(String value) throws Exception {
                        String[] strings = value.split(",");
                        return new Sensor(
                                strings[0],
                                Long.parseLong(strings[1]),
                                Integer.parseInt(strings[2])
                        );
                    }
                });
        //  如果传过来的是元组，可在 fromDataStream 中第二个参数开始，定义字段名 $("id"),$("ts"),$("vc") ...
        Table table = tableEnvironment.fromDataStream(localhost);

        //  使用SQL查询未注册的表
        Table result = tableEnvironment.sqlQuery("select id,ts,vc from " + table + " where id = 'ws_001' ");

        //  将表转换为流进行输出
        tableEnvironment.toAppendStream(result, Row.class).print();

        env.execute();

    }
}

1.2、注册表 demo

public class Flink_SQLAgg_Table {
    public static void main(String[] args) throws Exception {
        //  获取运行时环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env);

        SingleOutputStreamOperator<Sensor> localhost = env.socketTextStream("localhost", 9999)
                .map(new MapFunction<String, WaterSensor>() {
                    @Override
                    public Sensor map(String value) throws Exception {
                        String[] strings = value.split(",");
                        return new Sensor(
                                strings[0],
                                Long.parseLong(strings[1]),
                                Integer.parseInt(strings[2])
                        );
                    }
                });
//        //  如果传过来的是元组，可在 fromDataStream 中第二个参数开始，定义字段名 $("id"),$("ts"),$("vc") ...
//        Table table = tableEnvironment.fromDataStream(localhost);

        //  将流注册表
        tableEnvironment.createTemporaryView("sensor",localhost);

        //  使用SQL查询注册的表
        Table result = tableEnvironment.sqlQuery("select id,count(distinct ts),sum(vc) from sensor group by id ");

        //  将表转换为流进行输出
        tableEnvironment.toRetractStream(result, Row.class).print();

        env.execute();

    }
}

1.3、直接查询输出
Kafka To Kafka

public class Flink_SQL_KafkaToKafka {
    public static void main(String[] args) throws Exception {
        //  获取运行时环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env);

        //  DDL 注册sourceTable
        TableResult tableSource = tableEnvironment.executeSql("create table sourcesql(id string,ts bigint,vc int) with (" +
                " 'connector.type' = 'kafka', " +
                " 'connector.version' = 'universal', " +
                " 'connector.topic' = 'sourcesql',  " +
                " 'connector.properties.bootstrap.servers' = 'aaaaaa:9092,bbbbbb:9092,cccccc:9092', " +
                " 'connector.properties.group.id' = 'testGroup', " +
                //  earliest-offset 最早  latest-offset 最晚
                " 'connector.startup-mode' = 'latest-offset', " +
                " 'format.type' = 'csv' )");

        //  DDL 注册sinkTable
        TableResult tableSink = tableEnvironment.executeSql("create table sinksql(id string,ts bigint,vc int) with (" +
                " 'connector.type' = 'kafka', " +
                " 'connector.version' = 'universal', " +
                " 'connector.topic' = 'sinksql',  " +
                " 'connector.properties.bootstrap.servers' = 'aaaaaa:9092,bbbbbb:9092,cccccc:9092', " +
                " 'format.type' = 'json' )");

        //  执行查询，插入数据
        tableEnvironment.executeSql("insert into sinksql select * from sourcesql where id = 'ws_001' ");

        //  因为是纯SQL的所以不需要 env.execute();

    }
}

Kafka To MySQL

public class Flink_SQL_KafkaToMySQL {
    public static void main(String[] args) throws Exception {
        //  获取运行时环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env);

        //  DDL 配置KafkaSource
        TableResult tableSource = tableEnvironment.executeSql("create table sourcesql(id string,ts bigint,vc int) with (" +
                " 'connector.type' = 'kafka', " +
                " 'connector.version' = 'universal', " +
                " 'connector.topic' = 'sourcesql',  " +
                " 'connector.properties.bootstrap.servers' = 'aaaaaa:9092,bbbbbb:9092,cccccc:9092', " +
                " 'connector.properties.group.id' = 'testGroup', " +
                //  earliest-offset 最早  latest-offset 最晚
                " 'connector.startup-mode' = 'latest-offset', " +
                " 'format.type' = 'csv' )");

        //  DDL 配置JDBC Sink（MySQL） 不会自动在MySQL创建所需要的表，需要自己手动去创建,注意网络通信问题
        TableResult tableSink = tableEnvironment.executeSql("create table sinksql(id string,ts bigint,vc int) with (" +
                " 'connector' = 'jdbc', " +
                " 'url' = 'jdbc:mysql://aaaaaa:3306/test', " +
                " 'table-name' = 'sinksql',  " +
                " 'username' = '______', " +
                " 'password' = '______' ) " );

        //  执行查询，插入数据
        tableEnvironment.executeSql("insert into sinksql select * from sourcesql where id = 'ws_001' ");

//        Table source_sensor = tableEnvironment.from("sourcesql");
//        source_sensor.executeInsert("sinksql");

        //  因为是纯SQL的所以不需要 env.execute();

    }
}

ProcessTime

public class FlinkSQL_SQL_ProcessTime_StreamToTable {
    public static void main(String[] args) throws Exception {
        //  获取运行时环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env);

        SingleOutputStreamOperator<WaterSensor> map = env.readTextFile("input/serson.txt")
                .map(new MapFunction<String, Sensor>() {
                    @Override
                    public Sensor map(String value) throws Exception {
                        String[] strings = value.split(",");
                        return new Sensor(
                                strings[0],
                                Long.parseLong(strings[1]),
                                Integer.parseInt(strings[2])
                        );
                    }
                });

        //  流转换为表，并定义处理时间（SQL 要自己定义）,需要加一个字段,则要手动列出全部字段   proctime():处理时间的方法
        Table table = tableEnvironment.fromDataStream(map, $("id"), $("ts"), $("vc"), $("pt").proctime());

        //  打印Schema信息
        table.printSchema();

        env.execute();
    }
}

EventTime

public class FlinkSQL_SQL_EventTime_StreamToTable {
    public static void main(String[] args) throws Exception {
        //  获取运行时环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env);

        //  读取数据并提取事件时间WaterMark
        SingleOutputStreamOperator<WaterSensor> waterSensorDS = env.readTextFile("input/serson.txt")
                .map(new MapFunction<String, Sensor>() {
                    @Override
                    public Sensor map(String value) throws Exception {
                        String[] strings = value.split(",");
                        return new Sensor(
                                strings[0],
                                Long.parseLong(strings[1]),
                                Integer.parseInt(strings[2])
                        );
                    }
                })
                .assignTimestampsAndWatermarks(WatermarkStrategy.<Sensor>forBoundedOutOfOrderness(Duration.ofSeconds(2))
                        .withTimestampAssigner(new SerializableTimestampAssigner<Sensor>() {
                            @Override
                            public long extractTimestamp(Sensor element, long recordTimestamp) {
                                return element.getTs() * 1000L;
                            }
                        }));

        //  将流转换为表，并指定事件时间字段  rowtime 行数据时间，事件时间
        Table table = tableEnvironment.fromDataStream(waterSensorDS, $("id"), $("ts"), $("vc"), $("rt").rowtime());

        table.printSchema();

        env.execute();
    }
}

二、Function

/*
注意：如果使用的是 12版本之前的Flink，如果使用rownumber函数，那么有个需要特殊关注的地方
 */
public class FlinkSQL_Function {
    public static void main(String[] args) throws Exception {
        //  获取流运行环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);

        //  部分函数需要使用Blink执行计划   在函数那，有些函数仅Blink客户端可使用
        EnvironmentSettings build = EnvironmentSettings.newInstance()
                .useBlinkPlanner()
                .inStreamingMode()
                .build();

        StreamTableEnvironment tableEnvironment = StreamTableEnvironment.create(env,build);

        env.execute();
    }
}