Flink Table 将Stream直接写入MySQL数据库
Flink Table提供了一个JDBCAppendTableSink
,可以直接往可靠地数据库中Sink数据,下面以MySQL为例:
添加Maven的pom.xml依赖
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-jdbc_2.11</artifactId>
<version>1.8.0</version>
</dependency>
代码如下:
public class SqlSinkJdbcStream {
public static void main(String[] args) throws Exception {
StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
env.setParallelism(1);
// 必须设置checkpoint的间隔时间,不然不会写入jdbc
env.enableCheckpointing(5000L);
StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
Schema schema = new Schema()
.field("userId", Types.STRING)
.field("name", Types.STRING)
.field("age", Types.STRING)
.field("sex", Types.STRING)
.field("createTime", Types.BIG_DEC)
.field("updateTime", Types.BIG_DEC);
tableEnv
.connect(
new Kafka().version("0.10").topic("user").property("bootstrap.servers", "localhost:9092")
)
.withSchema(schema)
.withFormat(new Json().deriveSchema())
.inAppendMode()
.registerTableSource("Users");
Table table = tableEnv.sqlQuery("select userId,name,age,sex,createTime,updateTime from Users");
DataStream<Row> result = tableEnv.toAppendStream(table, TypeInformation.of(Row.class));
result.print();
JDBCAppendTableSink sink = new JDBCAppendTableSinkBuilder()
.setDBUrl("jdbc:mysql://localhost:3306/test?useSSL=false")
.setDrivername("com.mysql.jdbc.Driver")
.setUsername("root")
.setPassword("root")
.setBatchSize(1000)
.setQuery("REPLACE INTO user(userId,name,age,sex,createTime,updateTime) values(?,?,?,?,?,?)")
.setParameterTypes(new TypeInformation[]{Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.BIG_DEC, Types.BIG_DEC})
.build();
tableEnv.registerTableSink("Result",
new String[]{"userId", "name", "age", "sex", "createTime", "updateTime"},
new TypeInformation[]{Types.STRING, Types.STRING, Types.STRING, Types.STRING, Types.BIG_DEC, Types.BIG_DEC},
sink);
tableEnv.insertInto(table, "Result", new StreamQueryConfig());
env.execute("SqlSinkJdbcStream");
}
}
// mysql的建表语句
/*
CREATE TABLE `user` (
`userId` varchar(10) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` varchar(3) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
`createTime` varchar(20) DEFAULT NULL,
`updateTime` varchar(20) DEFAULT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/
注意:需要设置checkpoint的true,设置checkpoint间隔时间。