前言
flink 提供了 Table API 和 SQL的方式,可以通过sql来操作DataStream。
1、引入依赖
相关的依赖
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table-api-java-bridge_2.11</artifactId>
<version>1.14.4</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table-planner_2.11</artifactId>
<version>1.14.4</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-streaming-scala_2.11</artifactId>
<version>1.14.4</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table-api-java-bridge_2.12</artifactId>
<version>1.14.4</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table-common</artifactId>
<version>1.14.4</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-csv</artifactId>
<version>1.14.4</version>
<scope>test</scope>
</dependency>
2、基于流创建表
public static StreamExecutionEnvironment env = null;
public static StreamTableEnvironment tableEnv = null;
public static Table table = null;
@BeforeEach
public void before() {
env = StreamExecutionEnvironment.getExecutionEnvironment();
env.setRuntimeMode(RuntimeExecutionMode.STREAMING).setParallelism(1);
List<Student> list = List.of(
new Student("a", 60L),
new Student("a", 80L),
new Student("a", 70L),
new Student("b", 60L),
new Student("b", 80L),
new Student("c", 50L)
);
//数据源
DataStreamSource<Student> source = env.fromCollection(list);
//环境配置
EnvironmentSettings setting = EnvironmentSettings.newInstance()
.inBatchMode()
.withBuiltInCatalogName("test_catalog")
.withBuiltInDatabaseName("test_database")
.build();
tableEnv = StreamTableEnvironment.create(env, setting);
//基于流创建表
table = tableEnv.fromDataStream(source);
}
3、表转换成流
@Test
public void table2DataStreamTest() throws Exception {
DataStream<Student> dataStream = tableEnv.toDataStream(table, Student.class);
dataStream.print("tableStream");
env.execute("table2DataStreamTest");
}
结果:
tableStream> Student(name=a, score=60)
tableStream> Student(name=a, score=80)
tableStream> Student(name=a, score=70)
tableStream> Student(name=b, score=60)
tableStream> Student(name=b, score=80)
tableStream> Student(name=c, score=50)
4、Table API
通过name分组,求score和
@Test
public void tableApiTest() throws Exception {
//创建临时视图
tableEnv.createTemporaryView("student", table);
Table t = tableEnv.from("student")
.groupBy($("name"))
.select($("name"), $("score").sum().as("total"));
DataStream<Row> dataStream = tableEnv.toChangelogStream(t, Schema.derived(), ChangelogMode.all());
dataStream.print("tableStream");
env.execute("tableApiTest");
}
结果:
tableStream> +I[a, 210]
tableStream> +I[b, 140]
tableStream> +I[c, 50]
5、SQL
@Test
public void sqlTest() throws Exception {
//创建临时视图
tableEnv.createTemporaryView("student", table);
String sql = "select name, sum(score) as total from student group by name";
Table t = tableEnv.sqlQuery(sql);
DataStream<Row> dataStream = tableEnv.toChangelogStream(t, Schema.derived(), ChangelogMode.upsert());
dataStream.print("tableStream");
env.execute("sqlTest");
}
结果:
tableStream> +I[a, 210]
tableStream> +I[b, 140]
tableStream> +I[c, 50]
6、tableEnv 创建表
@Test
public void tableSink1Test() throws Exception {
Schema schema = Schema.newBuilder()
.column("name", DataTypes.STRING())
.column("score", DataTypes.BIGINT())
.build();
//创建表
tableEnv.createTable("table1", TableDescriptor.forConnector("filesystem")
.schema(schema)
.option("path", "E:\\tmp")
.format(FormatDescriptor.forFormat("csv")
.option("field-delimiter", "|")
.build())
.build());
//创建临时视图
tableEnv.createTemporaryView("student", table);
String sql = "select name, score from student";
Table t = tableEnv.sqlQuery(sql);
DataStream<Row> dataStream = tableEnv.toChangelogStream(t, Schema.derived(), ChangelogMode.upsert());
dataStream.print("tableStream");
t.executeInsert("table1");
env.execute("sqlTest");
}
到 E:\tmp 目录下查看生成的文件
7、sql 创建表
@Test
public void tableSink2Test() throws Exception {
String creatTable = "CREATE TABLE table2 (\n" +
" name STRING,\n" +
" total BIGINT\n" +
") WITH (\n" +
"'connector' = 'filesystem',\n" +
"'path' = 'E:\\tmp',\n" +
"'format' = 'csv',\n" +
"'csv.field-delimiter'='|'\n" +
")";
//创建表
TableResult tableResult = tableEnv.executeSql(creatTable);
//创建临时视图
tableEnv.createTemporaryView("student", table);
String sql = "select name, sum(score) as total from student group by name";
Table t = tableEnv.sqlQuery(sql);
DataStream<Row> dataStream = tableEnv.toChangelogStream(t, Schema.derived(), ChangelogMode.upsert());
dataStream.print("tableStream");
t.executeInsert("table2");
env.execute("sqlTest");
}
查看生成的文件
8、explain
@Test
public void explainTest() throws Exception {
//创建临时视图
tableEnv.createTemporaryView("student", table);
String sql = "select name, sum(score) as total from student group by name";
Table t = tableEnv.sqlQuery(sql);
System.out.println(t.explain());
结果:
== Abstract Syntax Tree ==
LogicalAggregate(group=[{0}], total=[SUM($1)])
+- LogicalTableScan(table=[[test_catalog, test_database, Unregistered_DataStream_Source_1]])
== Optimized Physical Plan ==
HashAggregate(isMerge=[true], groupBy=[name], select=[name, Final_SUM(sum$0) AS total])
+- Exchange(distribution=[hash[name]])
+- LocalHashAggregate(groupBy=[name], select=[name, Partial_SUM(score) AS sum$0])
+- TableSourceScan(table=[[test_catalog, test_database, Unregistered_DataStream_Source_1]], fields=[name, score])
== Optimized Execution Plan ==
HashAggregate(isMerge=[true], groupBy=[name], select=[name, Final_SUM(sum$0) AS total])
+- Exchange(distribution=[hash[name]])
+- LocalHashAggregate(groupBy=[name], select=[name, Partial_SUM(score) AS sum$0])
+- TableSourceScan(table=[[test_catalog, test_database, Unregistered_DataStream_Source_1]], fields=[name, score])