Flink(1.11)高阶编程四——FlinkSQL

八、Flink SQL 编程

8.1 Table API

①创建表的执行环境

StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);

②将流转成动态表 ——> fromDataStream

Table sensorTable = tableEnv.fromDataStream(sensorDS, $("ts"), $("id"), $("vc").as("ergou"));

③使用TableAPI对动态表进行操作，返回一个结果表

Table resultTable = sensorTable
                    .where($("id").isEqual("sensor_1"))
                	.select($("id"), $("ergou"));

④ 1 将动态表转换成流，输出 ——> toAppendStream \ toRetractStream

正常：toAppendStream

DataStream<Tuple2<String, Integer>> resultDS = 
    tableEnv
    	.toAppendStream(resultTable, TypeInformation.of(new TypeHint<Tuple2<String, Integer>>() {}));

撤回流：toRetractStream

涉及到数据的更新，要使用 撤回流   
// 撤回流实现方式：
//  前面加一个boolean类型的标志位： true表示插入，false表示撤回
// 更新的逻辑： 将 旧的结果 置为 false，将 新的结果 插入，置为 true
// DataStream<Tuple2<Boolean, Row>> resultDS = tableEnv.toRetractStream(resultTable, Row.class);
DataStream<Tuple2<Boolean, Row>> resultDS =
    tableEnv
    	.toRetractStream(resultTable, Row.class);

④ 2 将动态流写入文件系统 ——> executeInsert

1）、把文件系统抽象成Table：Connect

1.调用connect，连接外部系统
2.调用withFormat，指定数据格式
3.调用withSchema，指定抽象成Table的表结构（字段名，字段类型）
4.调用creatTemporaryTable，指定Table的表名

tableEnv
	.connect(new FileSystem().path("output/flink.txt"))
	.withFormat(new OleCsv().filedDelimiter("|"))
	.withSchema(
                        new Schema()
                                .field("a", DataTypes.STRING())
                                .field("tt", DataTypes.BIGINT())
                                .field("cc", DataTypes.INT())
                )
               .createTemporaryTable("fsTable");

2）、通过往表插入数据的方法，把结果Sink到文件系统 ——> executeInsert

TableResult fsTable = resultTable.executeInsert("fsTable");

8.2 SQL API

① 创建表的执行环境

StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);

② 将流转换成动态表 ——> createTemporaryView

tableEnv.createTemporaryView("sensor", sensorDS, $("id"), $("ts"), $("vc"));
tableEnv.createTemporaryView("sensor1", sensorDS1, $("id"), $("ts"), $("vc"));

可以通过名字获取Table对象

Table sensorTable = tableEnv.from("sensor");

③ 使用SQL对动态表进行操作，返回一个结果表 ——> sqlQuery

​ 在 sqlQuery( ) 方法里写sql语句

Table resultTable = tableEnv
   .sqlQuery("select * from sensor where id='sensor_1'"); // 条件查询

//.sqlQuery("select id,count(id) cnt from sensor group by id"); // 分组查询
//.sqlQuery("select * from sensor right join sensor1 on sensor.id=sensor1.id"); // 分组查询
//.sqlQuery("select * from sensor where id not in (select id from sensor1)"); // 范围查询
//.sqlQuery("select (case id when 'sensor_1' then 1 else 0 end) test from sensor"); // case when查询

​ 返回一个结果表

tableEnv.createTemporaryView("result", resultTable);

④ 1 将动态表转换成流，输出 ——> toRetractStream

DataStream<Tuple2<Boolean,Row>> resultDS = tableEnv.toRetractStream(resultTable,Row.class)

④ 2 将其写入外部系统

1）、使用SQL将外部系统抽象成Table

tableEnv.executeSql("create table fsTable (a String,b bigint,c int) " +
                "with (" +
                "'connector'='filesystem'," +
                "'path'='output'," +
                "'format'='csv'" +
                ")");

2）、对其进行写入，相当于sink到外部系统 ——> executeSql

关键字Bug：result =》 1.要么避免和关键字重复 2.要么加 ’ '

tableEnv.executeSql("insert into fsTable select * from 'result'");

8.3 FlinkSQL 集成Hive

〇 参数准备

String catalogName = "myhive"; //catalog名称
String defaultDatabase = "flinktest"; // hive数据库，设置为这个catalog下的默认库
String hiveConfDir = "F:\\atguigu\\01_course\\code\\hive-conf"; // hive配置文件所在的目录
//String hiveConfDir = "/opt/module/hive/conf"; // hive配置文件所在的目录
String version = "1.2.1";

①创建hive的catalog

HiveCatalog hiveCatalog = new HiveCatalog(catalogName, defaultDatabase, hiveConfDir, version);

②注册catalog

tableEnv.registerCatalog(catalogName,hiveCatalog);

③指定catalog（不指定，就是一个默认的catalog，叫default_catalog）

tableEnv.useCatalog(catalogName);

④ 指定sql语法为Hive sql

tableEnv.getConfig().setSqlDialect(SqlDialect.HIVE);

⑤使用sql操作hive表

//将流转成一个动态表
tableEnv.createTemporaryView("inputTable", inputDS);
//执行sql语句
tableEnv.executeSql("insert into test select * from inputTable");

8.4 窗口

8.4.1 分组窗口（Group Windows）

〇 准备

 //创建表的执行环境
StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
//将流转换成动态表，并给表去个别名
tableEnv.createTemporaryView("sensor",sensorDS,$("id"), $("ts").rowtime(), $("vc"));
//获取该表的对象
Table sensorTable = tableEnv.from("sensor");

① 滚动窗口

概念

1.	over：定义窗口长度
2.	on：用来分组（按时间间隔）或者排序（按行数）的时间字段
3.	as：别名，必须出现在后面的groupBy中

Table API 写法

sensorTable
         .window(Tumble.over(lit(3).seconds()).on($("ts").as("w"))

② 滑动窗口

概念

1.	over：定义窗口长度
2.	every：定义滑动步长
3.	on：用来分组（按时间间隔）或者排序（按行数）的时间字段
4.	as：别名，必须出现在后面的groupBy中

Table API 写法

sensorTable													  .window(Slide.over(lit(3).seconds()).every(lit(2).seconds()).on($("ts")).as("w"));

③ 会话窗口

概念

1.	withGap：会话时间间隔
2.	on：用来分组（按时间间隔）或者排序（按行数）的时间字段
3.	as：别名，必须出现在后面的groupBy中

Table API 写法

sensorTable    
	.groupBy($("w"),$("id"))
	.select($("id").count().as("cnt"),$("w").start(),$("w").end());

④SQL 写法 【时间参数不加s】

// TODO 3.使用 SQL 对 Table 开 GroupWindow
        Table resultTable = tableEnv.sqlQuery(
		"select id,count(id) cnt," +
         "TUMBLE_START(ts,INTERVAL '3' SECOND) as window_start," +
         "TUMBLE_END(ts,INTERVAL '3' SECOND) as window_end " +
           "from sensor " +
          "group by TUMBLE(ts,INTERVAL '3' SECOND),id"
        );

⑤ 将动态表转换成撤回流

tableEnv.toRetractStream(resultTable,Row.class).print();

8.4.2 Over Windows

概念

Group Windows在SQL查询的Group BY子句中定义。与使用常规GROUP BY子句的查询一样，使用GROUP BY子句的查询会计算每个组的单个结果行。
SQL支持以下Group窗口函数:
	TUMBLE(time_attr, interval)
定义一个滚动窗口，第一个参数是时间字段，第二个参数是窗口长度。
	HOP(time_attr, interval, interval)【必须写在groupby里面】
定义一个滑动窗口，第一个参数是时间字段，第二个参数是窗口滑动步长，第三个是窗口长度。
	SESSION(time_attr, interval)
定义一个会话窗口，第一个参数是时间字段，第二个参数是窗口间隔（Gap）。

另外还有一些辅助函数，可以用来选择Group Window的开始和结束时间戳，以及时间属性。
这里只写TUMBLE_*，滑动和会话窗口是类似的（HOP_*，SESSION_*）。
	TUMBLE_START(time_attr, interval)
	TUMBLE_END(time_attr, interval)
	TUMBLE_ROWTIME(time_attr, interval)
	TUMBLE_PROCTIME(time_attr, interval)

代码

//获取表执行环境
StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
//将流转表，并给表起一个名字
tableEnv.createTemporaryView("sensor", sensorDS, $("id"), $("ts").rowtime(), $("vc"));
//获取表对象
Table sensorTable = tableEnv.from("sensor");

Table API 写法

 sensorTable.window(
                Over
                .partitionBy($("id"))
                .orderBy($("ts").desc())
                .preceding(UNBOUNDED_ROW)
                .following(CURRENT_ROW)
                .as("ow")
        )
                .select($("*"),$("id").count().over($("ow")));

SQL 写法

  Table resultTable = tableEnv.sqlQuery(
                "select *," +
                        "count(id) over(partition by id order by ts desc) as cow\n" +
                        "from sensor"
        );

//表转流
tableEnv.toRetractStream(resultTable, Row.class).print();

env.execute();

8.5 案例实操

热门商品统计 TopN

public static void main(String[] args) throws Exception {
        // 1.创建执行环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        env.setStreamTimeCharacteristic(TimeCharacteristic.EventTime);

        // 2.读取数据
        SingleOutputStreamOperator<UserBehavior> userBehaviorDS = env
                .readTextFile("input/UserBehavior.csv")
                .map(new MapFunction<String, UserBehavior>() {
                    @Override
                    public UserBehavior map(String value) throws Exception {
                        String[] datas = value.split(",");
                        return new UserBehavior(
                                Long.valueOf(datas[0]),
                                Long.valueOf(datas[1]),
                                Integer.valueOf(datas[2]),
                                datas[3],
                                Long.valueOf(datas[4])
                        );
                    }
                })
                .assignTimestampsAndWatermarks(
                        WatermarkStrategy
                                .<UserBehavior>forBoundedOutOfOrderness(Duration.ofMinutes(1))
                                .withTimestampAssigner((data, ts) -> data.getTimestamp() * 1000L)
                );

        // TODO 3.使用 FlinkSQL 实现
        //flinkSQL只支持blinkplanner
        EnvironmentSettings settings = EnvironmentSettings.newInstance()
                .useBlinkPlanner()
                .inStreamingMode()
                .build();
        //blink基于流的模式
        StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env, settings);
        // 统计 每5分钟输出最近一小时的热门商品点击，只需要 商品ID、行为、时间戳
        Table userBehaviorTable = tableEnv.fromDataStream(userBehaviorDS,$("itemId"), $("behavior"), $("timestamp").rowtime().as("ts"));
        // 3.1 数据的准备：过滤出pv行为、分组、开窗、求和统计、携带窗口信息
        Table aggTable = userBehaviorTable.where($("behavior").isEqual("pv"))
                .window(
                        Slide
                                .over(lit(1).hours()).every(lit(5).minutes())
                                .on($("ts"))
                                .as("w")
                )
                .groupBy($("w"), $("itemId"))
                .select($("itemId"),
                        $("itemId").count().as("itemCount"),
                        $("w").end().as("windowEnd"));
        //把Table转成DataStream，再转成Table
        DataStream<Row> aggDS = tableEnv.toAppendStream(aggTable, Row.class);
        tableEnv.createTemporaryView("aggTable",aggDS,$("itemId"),$("itemCount"),$("windowEnd"));

        //3.2   实现TopN
        Table tableResult = tableEnv.sqlQuery(
                "select * " +
                        "from (" +
                        "select *,row_number() over(partition by windowEnd order by itemCount desc) as rn from aggTable) " +
                        "where rn <= 3");
        tableEnv.toRetractStream(tableResult, Row.class).print();


        env.execute();


    }