flinksql如何关联mysql维表_Flink 系例 之 TableAPI & SQL 与 MYSQL 数据查询

使用Tbale&SQL与Flink JDBC连接器从MYSQL数据库表中SELECT选取数据。

示例环境

java.version: 1.8.x

flink.version: 1.11.1

kafka：2.11

示例数据源(项目码云下载)

示例模块 (pom.xml)

SelectToMysql.java

package com.flink.examples.mysql;

import org.apache.flink.api.common.functions.FlatMapFunction;

import org.apache.flink.streaming.api.datastream.DataStream;

import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;

import org.apache.flink.table.api.EnvironmentSettings;

import org.apache.flink.table.api.Table;

import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

import org.apache.flink.types.Row;

import org.apache.flink.util.Collector;

import static org.apache.flink.table.api.Expressions.$;

/**

* @Description 使用Tbale&SQL与Flink JDBC连接器从MYSQL数据库表中SELECT选取数据。

*/

public class SelectToMysql {

/**

官方参考：https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/connectors/jdbc.html

分区扫描

为了加速并行Source任务实例中的数据读取，Flink为JDBC表提供了分区扫描功能。

scan.partition.column：用于对输入进行分区的列名。

scan.partition.num：分区数。

scan.partition.lower-bound：第一个分区的最小值。

scan.partition.upper-bound：最后一个分区的最大值。

*/

//flink-jdbc-1.11.1写法,所有属性名在JdbcTableSourceSinkFactory工厂类中定义

static String table_sql =

"CREATE TABLE my_users (\n" +

" id BIGINT,\n" +

" name STRING,\n" +

" age INT,\n" +

" status INT,\n" +

" PRIMARY KEY (id) NOT ENFORCED\n" +

") WITH (\n" +

" 'connector.type' = 'jdbc',\n" +

" 'connector.url' = 'jdbc:mysql://192.168.110.35:3306/flink?useUnicode=true&characterEncoding=utf-8', \n" +

" 'connector.driver' = 'com.mysql.jdbc.Driver', \n" +

" 'connector.table' = 'users', \n" +

" 'connector.username' = 'root',\n" +

" 'connector.password' = 'password', \n" +

" 'connector.read.fetch-size' = '3' \n" +

")";

public static void main(String[] args) throws Exception {

//构建StreamExecutionEnvironment

StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();

//构建EnvironmentSettings 并指定Blink Planner

EnvironmentSettings bsSettings = EnvironmentSettings.newInstance().useBlinkPlanner().inStreamingMode().build();

//构建StreamTableEnvironment

StreamTableEnvironment tEnv = StreamTableEnvironment.create(env, bsSettings);

//注册mysql数据维表

tEnv.executeSql(table_sql);

String sql = "select id,name,age,status from my_users";

//第一种：执行SQL

//Table table = tEnv.sqlQuery(sql);

//第二种：通过方法拼装执行语句

Table table = tEnv.from("my_users").select($("id"), $("name"), $("age"), $("status")).where($("status").isEqual(0));

//打印字段结构

table.printSchema();

//table 转成 dataStream 流

DataStream behaviorStream = tEnv.toAppendStream(table, Row.class);

behaviorStream.flatMap(new FlatMapFunction() {

@Override

public void flatMap(Row value, Collector out) throws Exception {

System.out.println(value.toString());

Thread.sleep(1 * 1000);

}

}).print();

env.execute();

}

}

打印结果

root

|-- id: BIGINT NOT NULL

|-- name: STRING

|-- age: INT

|-- status: INT

1,nike,16,0

2,nike,18,0