flink sql 从mysql读数据写入到mysql实践

背景

主要是实践下从mysql读数据写入到mysql，期间也会遇到的一些问题。
参考链接：https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/connectors/jdbc.html

先决条件

• 添加maven依赖，从mysql读取数据到mysql这两个依赖必不可少

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-connector-jdbc_${scala.binary.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>

• 数据库中创建相关的源表（写入相关样例数据）和目标表：

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `password` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO student (name,password,age) VALUES ('hj', '23',23);

DROP TABLE IF EXISTS `studentcp`;

CREATE TABLE `studentcp` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `password` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `age` int DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

代码

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


public class JavaSqlDemo {
    public static void main(String[] args) throws Exception {
        mysqlSourceToSink();
    }

    public static void mysqlSourceToSink() {
        EnvironmentSettings settings = EnvironmentSettings.newInstance().build();
        TableEnvironment tableEnvironment = TableEnvironment.create(settings);

        String schema = "id BIGINT ,name STRING, password STRING,age INT";
        String source_table = "student";
        String sink_table = "studentcp";
        String flink_source_table = "mysource";
        String flink_sink_table = "mysink";

        String base_sql = "CREATE TABLE %s (%s) " +
                "WITH (" +
                "'connector.type' = 'jdbc'," +
                "'connector.url' = 'jdbc:mysql://127.0.0.1:3306/feature'," +
                "'connector.driver' = 'com.mysql.cj.jdbc.Driver'," +
                "'connector.table' = '%s'," +
                " 'connector.username' = 'root'," +
                " 'connector.password' = '123456'" +
                " )";
        String source_ddl = String.format(base_sql, flink_source_table, schema, source_table);
        String sink_ddl = String.format(base_sql, flink_sink_table, schema, sink_table);

        tableEnvironment.executeSql(source_ddl);
        tableEnvironment.executeSql(sink_ddl);

        String insertsql = String.format("insert into %s select * from %s", flink_sink_table, flink_source_table);
        tableEnvironment.executeSql(insertsql).print();


    }

总结

执行过程中主要是注意在执行source 和sink ddl 语句 ，需要注意格式和语法，以及sql scheme 主键之类的影响。