mysqlcdc通过flink (java)算子实现数据的同步

mr_wuyongfu

已于 2024-12-04 14:07:58 修改

阅读量728

点赞数 15

CC 4.0 BY-SA版权

文章标签： flink java adb

于 2024-12-04 10:21:51 首次发布

本文链接：https://blog.csdn.net/qq_41673645/article/details/144231820

package com.yofoto.myfunction;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ververica.cdc.connectors.mysql.source.MySqlSource;
import com.ververica.cdc.debezium.JsonDebeziumDeserializationSchema;
import org.apache.flink.api.common.eventtime.WatermarkStrategy;
import org.apache.flink.api.common.functions.MapFunction;
import org.apache.flink.api.java.tuple.Tuple3;
import org.apache.flink.api.java.tuple.Tuple4;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;

public class StreamJoinExample3 {

    // Static and final DateTimeFormatter to avoid serialization issues
    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    public static void main(String[] args) throws Exception {
        // Create execution environment
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);

        final ObjectMapper objectMapper = new ObjectMapper();  // Jackson ObjectMapper to parse JSON

        // MySQL CDC configuration for the 'test' table
        MySqlSource<String> mySqlSource1 = MySqlSource.<String>builder()
                .hostname("localhost")
                .port(3306)
                .databaseList("test")
                .tableList("test.test")
                .username("root")
                .password("123456")
                .deserializer(new JsonDebeziumDeserializationSchema())
                .build();

        // Read data from MySQL CDC stream with WatermarkStrategy
        DataStream<String> mySQLStream1 = env.fromSource(mySqlSource1, WatermarkStrategy.noWatermarks(), "MySQL CDC Source");

        // Transform and process the stream to handle different types of operations
        DataStream<Tuple4<String, Integer, String, String>> transformedStream1 = mySQLStream1
                .map(new MapFunction<String, Tuple4<String, Integer, String, String>>() {
                    @Override
                    public Tuple4<String, Integer, String, String> map(String value) throws Exception {
                        JsonNode jsonNode = objectMapper.readTree(value);
                        String operation = jsonNode.path("op").asText();
                        JsonNode beforeNode = jsonNode.path("before");
                        JsonNode afterNode = jsonNode.path("after");
                        System.out.println(operation);
                        // Check if it's a delete event, in which case there might not be an "after" node
                        if ("d".equals(operation)) {
                            return new Tuple4<>(operation, beforeNode.path("id").asInt(), null, null);
                        }

                        int id = afterNode.path("id").asInt();
                        String name = afterNode.path("name").asText();
                        String dt = afterNode.path("dt").asText();
                        String dtMySql = convertIsoDateTimeToMySql(dt);

                        return new Tuple4<>(operation, id, name, dtMySql);
                    }
                });


        // Print the transformed streams to console for debugging/inspection
        transformedStream1.print("Transformed Stream 1");

        // 将数据流写入 JDBC Sink
        transformedStream1.addSink(new MySQLJDBCSink());

        // Execute the job
        env.execute("Stream Join Example 2");

    }

    public static String convertIsoDateTimeToMySql(String isoDateTime) {
        System.out.println(isoDateTime);
        // Define format patterns
        DateTimeFormatter isoFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss'Z'"); // For full format with 'Z'
        DateTimeFormatter partialFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm"); // For partial format (missing seconds)
        DateTimeFormatter mysqlFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); // MySQL format

        try {
            // Check if the date string ends with 'Z' (indicating UTC time)
            if (isoDateTime.endsWith("Z")) {
                // Parse as full format (with 'Z')
                LocalDateTime localDateTime = LocalDateTime.parse(isoDateTime, isoFormatter);
                return localDateTime.format(mysqlFormatter);
            } else {
                // Try parsing the partial format (missing seconds)
                LocalDateTime localDateTime = LocalDateTime.parse(isoDateTime, partialFormatter);
                return localDateTime.format(mysqlFormatter);
            }
        } catch (DateTimeParseException e) {
            e.printStackTrace();
            return null;
        }
    }
    public static class MySQLJDBCSink extends RichSinkFunction<Tuple4<String, Integer, String, String>> {
        private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/test";
        private static final String MYSQL_USER = "root";
        private static final String MYSQL_PASSWORD = "123456";
        private static final String INSERT_SQL = "INSERT INTO test.test_target (id, name, dt) VALUES (?, ?, ?)";
        private static final String UPDATE_SQL = "UPDATE test.test_target SET name = ?, dt = ? WHERE id = ?";
        private static final String DELETE_SQL = "DELETE FROM test.test_target WHERE id = ?";

        private transient Connection connection;
        private transient PreparedStatement insertStatement;
        private transient PreparedStatement updateStatement;
        private transient PreparedStatement deleteStatement;

        @Override
        public void open(org.apache.flink.configuration.Configuration parameters) throws Exception {
            super.open(parameters);
            connection = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
            insertStatement = connection.prepareStatement(INSERT_SQL);
            updateStatement = connection.prepareStatement(UPDATE_SQL);
            deleteStatement = connection.prepareStatement(DELETE_SQL);
        }

        @Override
        public void invoke(Tuple4<String, Integer, String, String> value, Context context) throws Exception {
            String operation = value.f0;
            int id = value.f1;
            String name = value.f2;
            String dt = value.f3;

            switch (operation) {
                case "r": // Insert
                case "c": // Insert
                case "u": // Update
                    try {
                        if ("u".equals(operation)) {
                            updateStatement.setString(1, name);
                            updateStatement.setString(2, dt);
                            updateStatement.setInt(3, id);
                            updateStatement.executeUpdate();
                        } else {
                            insertStatement.setInt(1, id);
                            insertStatement.setString(2, name);
                            insertStatement.setString(3, dt);
                            insertStatement.executeUpdate();
                        }
                    } catch (SQLException e) {
                        // Log the error or handle it appropriately
                        throw e;
                    }
                    break;
                case "d": // Delete
                    try {
                        deleteStatement.setInt(1, id);
                        deleteStatement.executeUpdate();
                    } catch (SQLException e) {
                        // Log the error or handle it appropriately
                        throw e;
                    }
                    break;
                default:
                    // Handle unknown operation or log it
                    System.out.println("Unknown operation: " + operation);
                    break;
            }
        }

        @Override
        public void close() throws Exception {
            super.close();
            if (insertStatement != null) {
                insertStatement.close();
            }
            if (updateStatement != null) {
                updateStatement.close();
            }
            if (deleteStatement != null) {
                deleteStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }
}

maven依赖:

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <project.build.sourceEncoding>UTF8</project.build.sourceEncoding>
    <flink.version>1.14.0</flink.version>
    <scala.binary.version>2.11</scala.binary.version>
  </properties>

  <dependencies>
    <!-- https://mvnrepository.com/artifact/org.apache.flink/flink-connector-jdbc -->
    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-connector-jdbc_2.11</artifactId>
      <version>1.14.0</version>
    </dependency>
<!--    <dependency>-->
<!--      <groupId>com.fasterxml.jackson.core</groupId>-->
<!--      <artifactId>jackson-databind</artifactId>-->
<!--      <version>2.13.0</version>-->
<!--    </dependency>-->
    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-streaming-java_2.11</artifactId>
      <version>${flink.version}</version>
    </dependency>
    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-runtime-web_2.11</artifactId>
      <version>${flink.version}</version>
    </dependency>
    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-streaming-scala_2.11</artifactId>
      <version>${flink.version}</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.12.3</version> <!-- 使用最新的版本号 -->
    </dependency>
    <!-- Flink Kafka Connector 依赖 -->
    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-connector-kafka_2.11</artifactId>
      <version>${flink.version}</version>
    </dependency>

    <!-- Flink CDC 连接器依赖 -->
    <dependency>
      <groupId>com.ververica</groupId>
      <artifactId>flink-connector-mysql-cdc</artifactId>
      <version>2.3.0</version> <!-- 请根据你的实际 Flink CDC 版本进行调整 -->
    </dependency>

    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-clients_2.11</artifactId>
      <version>${flink.version}</version>
    </dependency>

    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-table-api-java-bridge_2.11</artifactId>
      <version>${flink.version}</version> <!-- 请根据你的实际 Flink 版本进行调整 -->
    </dependency>
    <!-- Flink Table 运行时依赖 -->
    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-table-runtime_2.11</artifactId>
      <version>${flink.version}</version> <!-- 请根据你的实际 Flink 版本进行调整 -->
    </dependency>

    <dependency>
      <groupId>org.apache.doris</groupId>
      <artifactId>flink-doris-connector-1.14_2.11</artifactId>
      <version>1.1.1</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.33</version>
    </dependency>


    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.83</version>
    </dependency>

    <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-all</artifactId>
      <version>5.8.28</version>
    </dependency>

    <!-- 添加 SLF4J API 依赖 -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.36</version>
    </dependency>

    <!-- 添加 SLF4J 日志实现，例如 logback -->
    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>1.2.10</version>
    </dependency>

    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-core</artifactId>
      <version>1.2.10</version>
    </dependency>

    <dependency>
      <groupId>commons-configuration</groupId>
      <artifactId>commons-configuration</artifactId>
      <version>1.6</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.26</version>
      <scope>compile</scope>
    </dependency>

  </dependencies>

  <build>
    <plugins>

      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-assembly-plugin</artifactId>
        <version>2.5.5</version>
        <configuration>
          <archive>
            <manifest>
              <mainClass>com.yofoto.myfunction.KafkaMaxEventTimePerGroupJob</mainClass>
            </manifest>
          </archive>
          <descriptorRefs>
            <descriptorRef>jar-with-dependencies</descriptorRef>
          </descriptorRefs>
        </configuration>
        <executions>
          <execution>
            <id>make-assembly</id>
            <phase>package</phase>
            <goals>
              <goal>single</goal>
            </goals>
          </execution>
        </executions>
      </plugin>

    </plugins>
  </build>