flink-sql同步doris到mysql(flink集群模式)

环境:
OS:Centos 7
doris:4.0.8
mysql:5.7
flink:1.17.1

1.下载依赖包
在一台机器上下载,我这里是在192.168.1.134机器上下载
cd /opt/flink-1.17.1/lib
flink-doris-connector-1.17-25.0.0.jar
wget https://repository.apache.org/content/repositories/releases/org/apache/doris/flink-doris-connector-1.17/25.0.0/flink-doris-connector-1.17-25.0.0.jar

flink-connector-jdbc-3.1.0-1.17.jar
wget https://repo1.maven.org/maven2/org/apache/flink/flink-connector-jdbc/3.1.0-1.17/flink-connector-jdbc-3.1.0-1.17.jar

mysql-connector-j-8.0.33.jar
wget https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar

2.拷贝到另外的2台机器
scp /opt/flink-1.17.1/lib/flink-doris-connector-1.17-25.0.0.jar root@192.168.1.135:/opt/flink-1.17.1/lib/
scp /opt/flink-1.17.1/lib/flink-connector-jdbc-3.1.0-1.17.jar root@192.168.1.135:/opt/flink-1.17.1/lib/
scp /opt/flink-1.17.1/lib/mysql-connector-j-8.0.33.jar root@192.168.1.135:/opt/flink-1.17.1/lib/

scp /opt/flink-1.17.1/lib/flink-doris-connector-1.17-25.0.0.jar root@192.168.1.113:/opt/flink-1.17.1/lib/
scp /opt/flink-1.17.1/lib/flink-connector-jdbc-3.1.0-1.17.jar root@192.168.1.113:/opt/flink-1.17.1/lib/
scp /opt/flink-1.17.1/lib/mysql-connector-j-8.0.33.jar root@192.168.1.113:/opt/flink-1.17.1/lib/

重启集群
192.168.1.134
/opt/flink-1.17.1/bin/stop-cluster.sh
/opt/flink-1.17.1/bin/start-cluster.sh

3.准备同步脚本

vi /opt/flink-1.17.1/flinksql/doris2mysql_tb_hxl.sql

SET 'pipeline.name' = 'doris同步到mysql';
SET 'execution.runtime-mode' = 'BATCH';
SET 'table.exec.sink.upsert-materialize' = 'NONE';

-- Doris源表,直接读取tb_hxl
CREATE TABLE doris_source (
            id STRING,
            name1 STRING,
            name2 STRING,
            name3 STRING,
            name4 STRING,
            name5 STRING,
            name6 STRING,
            name7 STRING,
            name8 STRING,
            name9 STRING,
            name10 STRING,
            create_time TIMESTAMP(3),
            update_time TIMESTAMP(3)
) WITH (
  'connector' = 'doris',
  'fenodes' = '192.168.1.134:8030',
  'table.identifier' = 'db_test.tb_hxl',
  'username' = 'root',
  'password' = 'mysql',
  'doris.read.field' = 'id,name1,name2,name3,name4,name5,name6,name7,name8,name9,name10,create_time,update_time'
);

-- MySQL目标表,JDBC upsert,主键冲突更新
CREATE TABLE mysql_sink (
            id BIGINT PRIMARY KEY NOT ENFORCED,
            name1 STRING,
            name2 STRING,
            name3 STRING,
            name4 STRING,
            name5 STRING,
            name6 STRING,
            name7 STRING,
            name8 STRING,
            name9 STRING,
            name10 STRING,
            create_time TIMESTAMP(3),
            update_time TIMESTAMP(3)
) WITH (
  'connector' = 'jdbc',
  'url' = 'jdbc:mysql://192.168.1.14:3306/db_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai',
  'table-name' = 'tb_hxl',
  'username' = 'hxl',
  'password' = 'mysql',
  'sink.buffer-flush.max-rows' = '1000',
  'sink.buffer-flush.interval' = '2000ms',
  'sink.max-retries' = '3',
);

INSERT INTO mysql_sink
SELECT 
    CAST(id AS BIGINT),
    name1,
    name2,
    name3,
    name4,
    name5,
    name6,
    name7,
    name8,
    name9,
    name10,
    create_time,
    update_time
FROM doris_source;

 

4.启动任务
在其中一个master节点上执行,我这里是在192.168.1.134机器上执行
/opt/flink-1.17.1/bin/sql-client.sh -f /opt/flink-1.17.1/flinksql/doris2mysql_tb_hxl.sql

5.查找任务在那个机器上执行
安装jq
yum install -y jq

列出所有任务
curl -s http://192.168.1.134:8081/jobs | jq
[root@flink-134 log]# curl -s http://192.168.1.134:8081/jobs | jq
{
  "jobs": [
    {
      "id": "715873db8a5249936623ed85bb176834",
      "status": "FINISHED"
    },
    {
      "id": "494da61cbbb1aff7f27cb9fbe9b63ca4",
      "status": "FINISHED"
    },
    {
      "id": "09e2d997611fff9eca2c6a1da77595ec",
      "status": "RUNNING"
    }
  ]
}

查看具体某个job
curl http://192.168.1.134:8081/jobs/09e2d997611fff9eca2c6a1da77595ec|jq

查找vertexId
JOB_ID="09e2d997611fff9eca2c6a1da77595ec"
[root@flink-134 log]# curl -s http://192.168.1.134:8081/jobs/${JOB_ID} | jq -r '.vertices[0].id'
cbc357ccb763df2852fee8c4fc7d55f2

查看job在那个机器上执行
JOB_ID="09e2d997611fff9eca2c6a1da77595ec"
curl -s http://192.168.1.134:8081/jobs/${JOB_ID}/vertices/cbc357ccb763df2852fee8c4fc7d55f2 | jq
[root@flink-134 log]# curl -s http://192.168.1.134:8081/jobs/${JOB_ID}/vertices/cbc357ccb763df2852fee8c4fc7d55f2 | jq
{
  "id": "cbc357ccb763df2852fee8c4fc7d55f2",
  "name": "Source: doris_source[1] -> Calc[2] -> ConstraintEnforcer[3] -> Sink: mysql_sink[3]",
  "parallelism": 1,
  "maxParallelism": 1,
  "now": 1793699498915,
  "subtasks": [
    {
      "subtask": 0,
      "status": "RUNNING",
      "attempt": 0,
      "host": "flink-134",
      "start-time": 1793699448680,
      "end-time": -1,
      "duration": 50235,
      "metrics": {
        "read-bytes": 0,
        "read-bytes-complete": true,
        "write-bytes": 0,
        "write-bytes-complete": true,
        "read-records": 0,
        "read-records-complete": true,
        "write-records": 0,
        "write-records-complete": true,
        "accumulated-backpressured-time": 0,
        "accumulated-idle-time": 202,
        "accumulated-busy-time": 16885
      },