【Flink1.14实战】Docker环境Flink Sql mysql连接器

JDBC SQL 连接器

JDBC 连接器允许使用 JDBC 驱动向任意类型的关系型数据库读取或者写入数据。本文档描述了针对关系型数据库如何通过建立 JDBC 连接器来执行 SQL 查询。

如果在 DDL 中定义了主键，JDBC sink 将以 upsert 模式与外部系统交换 UPDATE/DELETE 消息；否则，它将以 append 模式与外部系统交换消息且不支持消费 UPDATE/DELETE 消息。

依赖

wget https://repo.maven.apache.org/maven2/org/apache/flink/flink-connector-jdbc_2.11/1.14.4/flink-connector-jdbc_2.11-1.14.4.jar
wget https://repo.maven.apache.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar

要在 flink的lib 目录添加对应的jar

如何创建 JDBC 表

JDBC table 可以按如下定义：

-- 在 Flink SQL 中注册一张 MySQL 表 'users'
CREATE TABLE MyUserTable (
  id BIGINT,
  name STRING,
  age INT,
  status BOOLEAN,
  PRIMARY KEY (id) NOT ENFORCED
) WITH (
   'connector' = 'jdbc',
   'url' = 'jdbc:mysql://localhost:3306/mydatabase',
   'table-name' = 'users',
   'connector.username' = 'root',
   'connector.password' = '123456'
);

-- 从另一张表 "T" 将数据写入到 JDBC 表中
INSERT INTO MyUserTable
SELECT id, name, age, status FROM T;

-- 查看 JDBC 表中的数据
SELECT id, name, age, status FROM MyUserTable;

-- JDBC 表在时态表关联中作为维表
SELECT * FROM myTopic
LEFT JOIN MyUserTable FOR SYSTEM_TIME AS OF myTopic.proctime
ON myTopic.key = MyUserTable.id;

实战

基于docker-compose。

1、编辑 docker-compose.yml

version: "3"
services:
  jobmanager:
    image: flink:1.14.4-scala_2.11
    ports:
      - "8081:8081"
    command: jobmanager
    environment:
      - |
        FLINK_PROPERTIES=
        jobmanager.rpc.address: jobmanager        

  taskmanager:
    image: flink:1.14.4-scala_2.11
    depends_on:
      - jobmanager
    command: taskmanager
    scale: 1
    environment:
      - |
        FLINK_PROPERTIES=
        jobmanager.rpc.address: jobmanager
        taskmanager.numberOfTaskSlots: 4   
  mysql:
    image: mysql:5.7
    ports:
      - "3306:3306"
    volumes:
      - ./data:/var/lib/mysql
      - ./mysql-init:/docker-entrypoint-initdb.d
    command: [
          'mysqld',
          '--innodb-buffer-pool-size=80M',
          '--character-set-server=utf8mb4',
          '--collation-server=utf8mb4_unicode_ci',
          '--default-time-zone=+8:00',
          '--lower-case-table-names=1',
          '--skip-name-resolve'
        ]
    environment:
      MYSQL_USER: "sql-demo"
      MYSQL_PASSWORD: "demo-sql"
      MYSQL_DATABASE: "sql-demo"
      MYSQL_RANDOM_ROOT_PASSWORD: "yes"

2、创建文件夹mysql-init, create-table.sql

DROP TABLE IF EXISTS `book`;
CREATE TABLE book (
	id INT NOT NULL,
	title varchar(30),
	author varchar(30),
	price	   INT,
	PRIMARY KEY (id)
);

INSERT INTO `book` VALUES ('1', 'zhangsan', '123456', '18'), ('2', 'lishi', '123', '17'), ('3', 'wangwu', '1234', '18'), ('4', 'jam', '12345', '16');
COMMIT;

3、启动服务

$ docker-compose up -d

4、从mysql探索数据

$ docker-compose exec mysql mysql -Dsql-demo -usql-demo -pdemo-sql

mysql> use sql-demo;
Database changed

mysql> select count(*) from book;

5、复制依赖文件

$ docker cp flink-connector-jdbc_2.11-1.14.4.jar mysql_taskmanager_1:/opt/flink/lib
$ docker cp mysql-connector-java-8.0.28.jar  mysql_taskmanager_1:/opt/flink/lib

# 查看lib
docker exec -it mysql_jobmanager_1 /bin/bash
# 重启
$ docker-compose restart
注意： 如果不重启的话，无法加载到上面的依赖jar
java.lang.ClassNotFoundException: org.apache.flink.connector.jdbc.table.JdbcRowDataInputFormat

4、在flink中创建book表，该表的字段mysql的字段相同

$ docker-compose exec jobmanager./bin/sql-client.sh
Flink SQL> drop table book;
CREATE TABLE book (
  id INT,
  title STRING,
  author STRING,
  price INT,
  PRIMARY KEY (id) NOT ENFORCED
) WITH (
   'connector' = 'jdbc',
   'url' = 'jdbc:mysql://mysql:3306/sql-demo',
   'table-name' = 'book',
   'driver'='com.mysql.jdbc.Driver',
   'username' = 'sql-demo',
   'password' = 'demo-sql'
);
#flink sql中查询该行数据
Flink SQL> select *  from book;

5、java 流代码

package quick.table;

import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
import org.apache.flink.types.Row;



public class SqlExample {

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

        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);

        tableEnv.executeSql("CREATE TABLE book (" +
                "  id INT," +
                "  title STRING," +
                "  author STRING," +
                "  price INT," +
                "  PRIMARY KEY (id) NOT ENFORCED" +
                ") WITH (" +
                "   'connector' = 'jdbc'," +
                "   'url' = 'jdbc:mysql://mysql:3306/sql-demo'," +
                "   'table-name' = 'book'," +
                "   'driver'='com.mysql.jdbc.Driver'," +
                "   'username' = 'sql-demo'," +
                "   'password' = 'demo-sql'" +
                ")");

            // execute SELECT statement
        Table resultTable = tableEnv.sqlQuery("SELECT * FROM book");

        DataStream<Row> resultStream = tableEnv.toDataStream(resultTable);

        // 打印
        resultStream.print();
        env.execute();
    }
}

6、提交job

然后，将打包应用程序提交，Flink 的Web UI来提交作业监控集群的状态和正在运行的作业。

$ docker-compose logs -f taskmanager