使用 Flink 将实时数据写入 MatrixOne

概述

Apache Flink 是一个强大的框架和分布式处理引擎，专注于进行有状态计算，适用于处理无边界和有边界的数据流。Flink 能够在各种常见集群环境中高效运行，并以内存速度执行计算，支持处理任意规模的数据。

应用场景

  • 事件驱动型应用

    事件驱动型应用通常具备状态，并且它们从一个或多个事件流中提取数据，根据到达的事件触发计算、状态更新或执行其他外部动作。典型的事件驱动型应用包括反欺诈系统、异常检测、基于规则的报警系统和业务流程监控。

  • 数据分析应用

    数据分析任务的主要目标是从原始数据中提取有价值的信息和指标。Flink 支持流式和批量分析应用，适用于各种场景，例如电信网络质量监控、移动应用中的产品更新和实验评估分析、消费者技术领域的实时数据即席分析以及大规模图分析。

  • 数据管道应用

    提取 - 转换 - 加载（ETL）是在不同存储系统之间进行数据转换和迁移的常见方法。数据管道和 ETL 作业有相似之处，都可以进行数据转换和丰富，然后将数据从一个存储系统移动到另一个存储系统。不同之处在于数据管道以持续流模式运行，而不是周期性触发。典型的数据管道应用包括电子商务中的实时查询索引构建和持续 ETL。

本篇文档将介绍两种示例，一种是使用计算引擎 Flink 实现将实时数据写入到 MatrixOne，另一种是使用计算引擎 Flink 将流式数据写入到 MatrixOne 数据库。

前期准备

硬件环境

本次实践对于机器的硬件要求如下：

服务器名称服务器 IP安装软件操作系统
node1192.168.146.10MatrixOneDebian11.1 x86
node2192.168.146.12kafkaCentos7.9
node3192.168.146.11IDEA、MYSQLwin10

软件环境

本次实践需要安装部署以下软件环境：

示例 1：从 MySQL 迁移数据至 MatrixOne

步骤一：初始化项目

  1. 打开 IDEA，点击 File > New > Project，选择 Spring Initializer，并填写以下配置参数：

    • Name：matrixone-flink-demo
    • Location：~\Desktop
    • Language：Java
    • Type：Maven
    • Group：com.example
    • Artifact：matrixone-flink-demo
    • Package name：com.matrixone.flink.demo
    • JDK 1.8

    配置示例如下图所示：

    使用 Flink 将实时数据写入 MatrixOne - 图1

  2. 添加项目依赖，编辑项目根目录下的 pom.xml 文件，将以下内容添加到文件中：

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <modelVersion>4.0.0</modelVersion>
  7.     <groupId>com.matrixone.flink</groupId>
  8.     <artifactId>matrixone-flink-demo</artifactId>
  9.     <version>1.0-SNAPSHOT</version>
  11.     <properties>
  12.         <scala.binary.version>2.12</scala.binary.version>
  13.         <java.version>1.8</java.version>
  14.         <flink.version>1.17.0</flink.version>
  15.         <scope.mode>compile</scope.mode>
  16.     </properties>
  18.     <dependencies>
  20.         <!-- Flink Dependency -->
  21.         <dependency>
  22.             <groupId>org.apache.flink</groupId>
  23.             <artifactId>flink-connector-hive_2.12</artifactId>
  24.             <version>${flink.version}</version>
  25.         </dependency>
  27.         <dependency>
  28.             <groupId>org.apache.flink</groupId>
  29.             <artifactId>flink-java</artifactId>
  30.             <version>${flink.version}</version>
  31.         </dependency>
  33.         <dependency>
  34.             <groupId>org.apache.flink</groupId>
  35.             <artifactId>flink-streaming-java</artifactId>
  36.             <version>${flink.version}</version>
  37.         </dependency>
  39.         <dependency>
  40.             <groupId>org.apache.flink</groupId>
  41.             <artifactId>flink-clients</artifactId>
  42.             <version>${flink.version}</version>
  43.         </dependency>
  45.         <dependency>
  46.             <groupId>org.apache.flink</groupId>
  47.             <artifactId>flink-table-api-java-bridge</artifactId>
  48.             <version>${flink.version}</version>
  49.         </dependency>
  51.         <dependency>
  52.             <groupId>org.apache.flink</groupId>
  53.             <artifactId>flink-table-planner_2.12</artifactId>
  54.             <version>${flink.version}</version>
  55.         </dependency>
  57.         <!-- JDBC相关依赖包 -->
  58.         <dependency>
  59.             <groupId>org.apache.flink</groupId>
  60.             <artifactId>flink-connector-jdbc</artifactId>
  61.             <version>1.15.4</version>
  62.         </dependency>
  63.         <dependency>
  64.             <groupId>mysql</groupId>
  65.             <artifactId>mysql-connector-java</artifactId>
  66.             <version>8.0.33</version>
  67.         </dependency>
  69.         <!-- Kafka相关依赖 -->
  70.         <dependency>
  71.             <groupId>org.apache.kafka</groupId>
  72.             <artifactId>kafka_2.13</artifactId>
  73.             <version>3.5.0</version>
  74.         </dependency>
  75.         <dependency>
  76.             <groupId>org.apache.flink</groupId>
  77.             <artifactId>flink-connector-kafka</artifactId>
  78.             <version>3.0.0-1.17</version>
  79.         </dependency>
  81.         <!-- JSON -->
  82.         <dependency>
  83.             <groupId>com.alibaba.fastjson2</groupId>
  84.             <artifactId>fastjson2</artifactId>
  85.             <version>2.0.34</version>
  86.         </dependency>
  88.     </dependencies>
  93.     <build>
  94.         <plugins>
  95.             <plugin>
  96.                 <groupId>org.apache.maven.plugins</groupId>
  97.                 <artifactId>maven-compiler-plugin</artifactId>
  98.                 <version>3.8.0</version>
  99.                 <configuration>
  100.                     <source>${java.version}</source>
  101.                     <target>${java.version}</target>
  102.                     <encoding>UTF-8</encoding>
  103.                 </configuration>
  104.             </plugin>
  105.             <plugin>
  106.                 <artifactId>maven-assembly-plugin</artifactId>
  107.                 <version>2.6</version>
  108.                 <configuration>
  109.                     <descriptorRefs>
  110.                         <descriptor>jar-with-dependencies</descriptor>
  111.                     </descriptorRefs>
  112.                 </configuration>
  113.                 <executions>
  114.                     <execution>
  115.                         <id>make-assembly</id>
  116.                         <phase>package</phase>
  117.                         <goals>
  118.                             <goal>single</goal>
  119.                         </goals>
  120.                     </execution>
  121.                 </executions>
  122.             </plugin>
  124.         </plugins>
  125.     </build>
  127. </project>

步骤二：读取 MatrixOne 数据

使用 MySQL 客户端连接 MatrixOne 后，创建演示所需的数据库以及数据表。

  1. 在 MatrixOne 中创建数据库、数据表，并导入数据：

    1. CREATE DATABASE test;
    2. USE test;
    3. CREATE TABLE `person` (`id` INT DEFAULT NULL, `name` VARCHAR(255) DEFAULT NULL, `birthday` DATE DEFAULT NULL);
    4. INSERT INTO test.person (id, name, birthday) VALUES(1, 'zhangsan', '2023-07-09'),(2, 'lisi', '2023-07-08'),(3, 'wangwu', '2023-07-12');

  2. 在 IDEA 中创建 MoRead.java 类，以使用 Flink 读取 MatrixOne 数据：

    1. package com.matrixone.flink.demo;
    3. import org.apache.flink.api.common.functions.MapFunction;
    4. import org.apache.flink.api.common.typeinfo.BasicTypeInfo;
    5. import org.apache.flink.api.java.ExecutionEnvironment;
    6. import org.apache.flink.api.java.operators.DataSource;
    7. import org.apache.flink.api.java.operators.MapOperator;
    8. import org.apache.flink.api.java.typeutils.RowTypeInfo;
    9. import org.apache.flink.connector.jdbc.JdbcInputFormat;
    10. import org.apache.flink.types.Row;
    12. import java.text.SimpleDateFormat;
    14. /**
    15.  * @author MatrixOne
    16.  * @description
    17.  */
    18. public class MoRead {
    20.     private static String srcHost = "192.168.146.10";
    21.     private static Integer srcPort = 6001;
    22.     private static String srcUserName = "root";
    23.     private static String srcPassword = "111";
    24.     private static String srcDataBase = "test";
    26.     public static void main(String[] args) throws Exception {
    28.         ExecutionEnvironment environment = ExecutionEnvironment.getExecutionEnvironment();
    29.         // 设置并行度
    30.         environment.setParallelism(1);
    31.         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    33.         // 设置查询的字段类型
    34.         RowTypeInfo rowTypeInfo = new RowTypeInfo(
    35.                 new BasicTypeInfo[]{
    36.                         BasicTypeInfo.INT_TYPE_INFO,
    37.                         BasicTypeInfo.STRING_TYPE_INFO,
    38.                         BasicTypeInfo.DATE_TYPE_INFO
    39.                 },
    40.                 new String[]{
    41.                         "id",
    42.                         "name",
    43.                         "birthday"
    44.                 }
    45.         );
    47.         DataSource<Row> dataSource = environment.createInput(JdbcInputFormat.buildJdbcInputFormat()
    48.                 .setDrivername("com.mysql.cj.jdbc.Driver")
    49.                 .setDBUrl("jdbc:mysql://" + srcHost + ":" + srcPort + "/" + srcDataBase)
    50.                 .setUsername(srcUserName)
    51.                 .setPassword(srcPassword)
    52.                 .setQuery("select * from person")
    53.                 .setRowTypeInfo(rowTypeInfo)
    54.                 .finish());
    56.         // 将 Wed Jul 12 00:00:00 CST 2023 日期格式转换为 2023-07-12
    57.         MapOperator<Row, Row> mapOperator = dataSource.map((MapFunction<Row, Row>) row -> {
    58.             row.setField("birthday", sdf.format(row.getField("birthday")));
    59.             return row;
    60.         });
    62.         mapOperator.print();
    63.     }
    64. }

  3. 在 IDEA 中运行 MoRead.Main()，执行结果如下：

    MoRead 执行结果

步骤三：将 MySQL 数据写入 MatrixOne

现在可以开始使用 Flink 将 MySQL 数据迁移到 MatrixOne。

  1. 准备 MySQL 数据：在 node3 上，使用 Mysql 客户端连接本地 Mysql，创建所需数据库、数据表、并插入数据：

    1. mysql -h127.0.0.1 -P3306 -uroot -proot
    2. mysql> CREATE DATABASE motest;
    3. mysql> USE motest;
    4. mysql> CREATE TABLE `person` (`id` int DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `birthday` date DEFAULT NULL);
    5. mysql> INSERT INTO motest.person (id, name, birthday) VALUES(2, 'lisi', '2023-07-09'),(3, 'wangwu', '2023-07-13'),(4, 'zhaoliu', '2023-08-08');

  2. 清空 MatrixOne 表数据：

    在 node3 上，使用 MySQL 客户端连接 node1 的 MatrixOne。由于本示例继续使用前面读取 MatrixOne 数据的示例中的 test 数据库，因此我们需要首先清空 person 表的数据。

    1. -- 在 node3 上，使用 Mysql 客户端连接 node1 的 MatrixOne
    2. mysql -h192.168.146.10 -P6001 -uroot -p111
    3. mysql> TRUNCATE TABLE test.person;

  3. 在 IDEA 中编写代码：

    创建 Person.java 和 Mysql2Mo.java 类，使用 Flink 读取 MySQL 数据，执行简单的 ETL 操作（将 Row 转换为 Person 对象），最终将数据写入 MatrixOne 中。

  1. package com.matrixone.flink.demo.entity;
  4. import java.util.Date;
  6. public class Person {
  8.     private int id;
  9.     private String name;
  10.     private Date birthday;
  12.     public int getId() {
  13.         return id;
  14.     }
  16.     public void setId(int id) {
  17.         this.id = id;
  18.     }
  20.     public String getName() {
  21.         return name;
  22.     }
  24.     public void setName(String name) {
  25.         this.name = name;
  26.     }
  28.     public Date getBirthday() {
  29.         return birthday;
  30.     }
  32.     public void setBirthday(Date birthday) {
  33.         this.birthday = birthday;
  34.     }
  35. }
  1. package com.matrixone.flink.demo;
  3. import com.matrixone.flink.demo.entity.Person;
  4. import org.apache.flink.api.common.functions.MapFunction;
  5. import org.apache.flink.api.common.typeinfo.BasicTypeInfo;
  6. import org.apache.flink.api.java.typeutils.RowTypeInfo;
  7. import org.apache.flink.connector.jdbc.*;
  8. import org.apache.flink.streaming.api.datastream.DataStreamSink;
  9. import org.apache.flink.streaming.api.datastream.DataStreamSource;
  10. import org.apache.flink.streaming.api.datastream.SingleOutputStreamOperator;
  11. import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
  12. import org.apache.flink.types.Row;
  14. import java.sql.Date;
  16. /**
  17.  * @author MatrixOne
  18.  * @description
  19.  */
  20. public class Mysql2Mo {
  22.     private static String srcHost = "127.0.0.1";
  23.     private static Integer srcPort = 3306;
  24.     private static String srcUserName = "root";
  25.     private static String srcPassword = "root";
  26.     private static String srcDataBase = "motest";
  28.     private static String destHost = "192.168.146.10";
  29.     private static Integer destPort = 6001;
  30.     private static String destUserName = "root";
  31.     private static String destPassword = "111";
  32.     private static String destDataBase = "test";
  33.     private static String destTable = "person";
  36.     public static void main(String[] args) throws Exception {
  38.         StreamExecutionEnvironment environment = StreamExecutionEnvironment.getExecutionEnvironment();
  39.         //设置并行度
  40.         environment.setParallelism(1);
  41.         //设置查询的字段类型
  42.         RowTypeInfo rowTypeInfo = new RowTypeInfo(
  43.                 new BasicTypeInfo[]{
  44.                         BasicTypeInfo.INT_TYPE_INFO,
  45.                         BasicTypeInfo.STRING_TYPE_INFO,
  46.                         BasicTypeInfo.DATE_TYPE_INFO
  47.                 },
  48.                 new String[]{
  49.                         "id",
  50.                         "name",
  51.                         "birthday"
  52.                 }
  53.         );
  55.         //添加 srouce
  56.         DataStreamSource<Row> dataSource = environment.createInput(JdbcInputFormat.buildJdbcInputFormat()
  57.                 .setDrivername("com.mysql.cj.jdbc.Driver")
  58.                 .setDBUrl("jdbc:mysql://" + srcHost + ":" + srcPort + "/" + srcDataBase)
  59.                 .setUsername(srcUserName)
  60.                 .setPassword(srcPassword)
  61.                 .setQuery("select * from person")
  62.                 .setRowTypeInfo(rowTypeInfo)
  63.                 .finish());
  65.         //进行 ETL
  66.         SingleOutputStreamOperator<Person> mapOperator = dataSource.map((MapFunction<Row, Person>) row -> {
  67.             Person person = new Person();
  68.             person.setId((Integer) row.getField("id"));
  69.             person.setName((String) row.getField("name"));
  70.             person.setBirthday((java.util.Date)row.getField("birthday"));
  71.             return person;
  72.         });
  74.         //设置 matrixone sink 信息
  75.         mapOperator.addSink(
  76.                 JdbcSink.sink(
  77.                         "insert into " + destTable + " values(?,?,?)",
  78.                         (ps, t) -> {
  79.                             ps.setInt(1, t.getId());
  80.                             ps.setString(2, t.getName());
  81.                             ps.setDate(3, new Date(t.getBirthday().getTime()));
  82.                         },
  83.                         new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
  84.                                 .withDriverName("com.mysql.cj.jdbc.Driver")
  85.                                 .withUrl("jdbc:mysql://" + destHost + ":" + destPort + "/" + destDataBase)
  86.                                 .withUsername(destUserName)
  87.                                 .withPassword(destPassword)
  88.                                 .build()
  89.                 )
  90.         );
  92.         environment.execute();
  93.     }
  95. }

步骤四：查看执行结果

在 MatrixOne 中执行如下 SQL 查询结果：

  1. mysql> select * from test.person;
  2. +------+---------+------------+
  3. | id   | name    | birthday   |
  4. +------+---------+------------+
  5. |    2 | lisi    | 2023-07-09 |
  6. |    3 | wangwu  | 2023-07-13 |
  7. |    4 | zhaoliu | 2023-08-08 |
  8. +------+---------+------------+
  9. 3 rows in set (0.01 sec)

示例 2：将 Kafka 数据写入 MatrixOne

步骤一：启动 Kafka 服务

Kafka 集群协调和元数据管理可以通过 KRaft 或 ZooKeeper 来实现。在这里，我们将使用 Kafka 3.5.0 版本，无需依赖独立的 ZooKeeper 软件，而是使用 Kafka 自带的 KRaft 来进行元数据管理。请按照以下步骤配置配置文件，该文件位于 Kafka 软件根目录下的 config/kraft/server.properties。

配置文件内容如下：

  1. # Licensed to the Apache Software Foundation (ASF) under one or more
  2. # contributor license agreements.  See the NOTICE file distributed with
  3. # this work for additional information regarding copyright ownership.
  4. # The ASF licenses this file to You under the Apache License, Version 2.0
  5. # (the "License"); you may not use this file except in compliance with
  6. # the License.  You may obtain a copy of the License at
  7. #
  8. #    http://www.apache.org/licenses/LICENSE-2.0
  9. #
  10. # Unless required by applicable law or agreed to in writing, software
  11. # distributed under the License is distributed on an "AS IS" BASIS,
  12. # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13. # See the License for the specific language governing permissions and
  14. # limitations under the License.
  16. #
  17. # This configuration file is intended for use in KRaft mode, where
  18. # Apache ZooKeeper is not present.  See config/kraft/README.md for details.
  19. #
  21. ############################# Server Basics #############################
  23. # The role of this server. Setting this puts us in KRaft mode
  24. process.roles=broker,controller
  26. # The node id associated with this instance's roles
  27. node.id=1
  29. # The connect string for the controller quorum
  30. controller.quorum.voters=1@192.168.146.12:9093
  32. ############################# Socket Server Settings #############################
  34. # The address the socket server listens on.
  35. # Combined nodes (i.e. those with `process.roles=broker,controller`) must list the controller listener here at a minimum.
  36. # If the broker listener is not defined, the default listener will use a host name that is equal to the value of java.net.InetAddress.getCanonicalHostName(),
  37. # with PLAINTEXT listener name, and port 9092.
  38. #   FORMAT:
  39. #     listeners = listener_name://host_name:port
  40. #   EXAMPLE:
  41. #     listeners = PLAINTEXT://your.host.name:9092
  42. #listeners=PLAINTEXT://:9092,CONTROLLER://:9093
  43. listeners=PLAINTEXT://192.168.146.12:9092,CONTROLLER://192.168.146.12:9093
  45. # Name of listener used for communication between brokers.
  46. inter.broker.listener.name=PLAINTEXT
  48. # Listener name, hostname and port the broker will advertise to clients.
  49. # If not set, it uses the value for "listeners".
  50. #advertised.listeners=PLAINTEXT://localhost:9092
  52. # A comma-separated list of the names of the listeners used by the controller.
  53. # If no explicit mapping set in `listener.security.protocol.map`, default will be using PLAINTEXT protocol
  54. # This is required if running in KRaft mode.
  55. controller.listener.names=CONTROLLER
  57. # Maps listener names to security protocols, the default is for them to be the same. See the config documentation for more details
  58. listener.security.protocol.map=CONTROLLER:PLAINTEXT,PLAINTEXT:PLAINTEXT,SSL:SSL,SASL_PLAINTEXT:SASL_PLAINTEXT,SASL_SSL:SASL_SSL
  60. # The number of threads that the server uses for receiving requests from the network and sending responses to the network
  61. num.network.threads=3
  63. # The number of threads that the server uses for processing requests, which may include disk I/O
  64. num.io.threads=8
  66. # The send buffer (SO_SNDBUF) used by the socket server
  67. socket.send.buffer.bytes=102400
  69. # The receive buffer (SO_RCVBUF) used by the socket server
  70. socket.receive.buffer.bytes=102400
  72. # The maximum size of a request that the socket server will accept (protection against OOM)
  73. socket.request.max.bytes=104857600
  76. ############################# Log Basics #############################
  78. # A comma separated list of directories under which to store log files
  79. log.dirs=/home/software/kafka_2.13-3.5.0/kraft-combined-logs
  81. # The default number of log partitions per topic. More partitions allow greater
  82. # parallelism for consumption, but this will also result in more files across
  83. # the brokers.
  84. num.partitions=1
  86. # The number of threads per data directory to be used for log recovery at startup and flushing at shutdown.
  87. # This value is recommended to be increased for installations with data dirs located in RAID array.
  88. num.recovery.threads.per.data.dir=1
  90. ############################# Internal Topic Settings  #############################
  91. # The replication factor for the group metadata internal topics "__consumer_offsets" and "__transaction_state"
  92. # For anything other than development testing, a value greater than 1 is recommended to ensure availability such as 3.
  93. offsets.topic.replication.factor=1
  94. transaction.state.log.replication.factor=1
  95. transaction.state.log.min.isr=1
  97. ############################# Log Flush Policy #############################
  99. # Messages are immediately written to the filesystem but by default we only fsync() to sync
  100. # the OS cache lazily. The following configurations control the flush of data to disk.
  101. # There are a few important trade-offs here:
  102. #    1. Durability: Unflushed data may be lost if you are not using replication.
  103. #    2. Latency: Very large flush intervals may lead to latency spikes when the flush does occur as there will be a lot of data to flush.
  104. #    3. Throughput: The flush is generally the most expensive operation, and a small flush interval may lead to excessive seeks.
  105. # The settings below allow one to configure the flush policy to flush data after a period of time or
  106. # every N messages (or both). This can be done globally and overridden on a per-topic basis.
  108. # The number of messages to accept before forcing a flush of data to disk
  109. #log.flush.interval.messages=10000
  111. # The maximum amount of time a message can sit in a log before we force a flush
  112. #log.flush.interval.ms=1000
  114. ############################# Log Retention Policy #############################
  116. # The following configurations control the disposal of log segments. The policy can
  117. # be set to delete segments after a period of time, or after a given size has accumulated.
  118. # A segment will be deleted whenever *either* of these criteria are met. Deletion always happens
  119. # from the end of the log.
  121. # The minimum age of a log file to be eligible for deletion due to age
  122. log.retention.hours=72
  124. # A size-based retention policy for logs. Segments are pruned from the log unless the remaining
  125. # segments drop below log.retention.bytes. Functions independently of log.retention.hours.
  126. #log.retention.bytes=1073741824
  128. # The maximum size of a log segment file. When this size is reached a new log segment will be created.
  129. log.segment.bytes=1073741824
  131. # The interval at which log segments are checked to see if they can be deleted according
  132. # to the retention policies
  133. log.retention.check.interval.ms=300000

文件配置完成后，执行如下命令，启动 Kafka 服务：

  1. #生成集群ID
  2. $ KAFKA_CLUSTER_ID="$(bin/kafka-storage.sh random-uuid)"
  3. #设置日志目录格式
  4. $ bin/kafka-storage.sh format -t $KAFKA_CLUSTER_ID -c config/kraft/server.properties
  5. #启动Kafka服务
  6. $ bin/kafka-server-start.sh config/kraft/server.properties

步骤二：创建 Kafka 主题

为了使 Flink 能够从中读取数据并写入到 MatrixOne，我们需要首先创建一个名为 “matrixone” 的 Kafka 主题。在下面的命令中，使用 --bootstrap-server 参数指定 Kafka 服务的监听地址为 192.168.146.12:9092：

  1. $ bin/kafka-topics.sh --create --topic matrixone --bootstrap-server 192.168.146.12:9092

步骤三：读取 MatrixOne 数据

在连接到 MatrixOne 数据库之后，需要执行以下操作以创建所需的数据库和数据表：

  1. 在 MatrixOne 中创建数据库和数据表，并导入数据：

    1. CREATE TABLE `users` (
    2. `id` INT DEFAULT NULL,
    3. `name` VARCHAR(255) DEFAULT NULL,
    4. `age` INT DEFAULT NULL
    5. )

  2. 在 IDEA 集成开发环境中编写代码：

    在 IDEA 中，创建两个类：User.java 和 Kafka2Mo.java。这些类用于使用 Flink 从 Kafka 读取数据，并将数据写入 MatrixOne 数据库中。

  1. package com.matrixone.flink.demo.entity;
  3. public class User {
  5.     private int id;
  6.     private String name;
  7.     private int age;
  9.     public int getId() {
  10.         return id;
  11.     }
  13.     public void setId(int id) {
  14.         this.id = id;
  15.     }
  17.     public String getName() {
  18.         return name;
  19.     }
  21.     public void setName(String name) {
  22.         this.name = name;
  23.     }
  25.     public int getAge() {
  26.         return age;
  27.     }
  29.     public void setAge(int age) {
  30.         this.age = age;
  31.     }
  32. }
  1. package com.matrixone.flink.demo;
  3. import com.alibaba.fastjson2.JSON;
  4. import com.matrixone.flink.demo.entity.User;
  5. import org.apache.flink.api.common.eventtime.WatermarkStrategy;
  6. import org.apache.flink.api.common.serialization.AbstractDeserializationSchema;
  7. import org.apache.flink.connector.jdbc.JdbcExecutionOptions;
  8. import org.apache.flink.connector.jdbc.JdbcSink;
  9. import org.apache.flink.connector.jdbc.JdbcStatementBuilder;
  10. import org.apache.flink.connector.jdbc.internal.options.JdbcConnectorOptions;
  11. import org.apache.flink.connector.kafka.source.KafkaSource;
  12. import org.apache.flink.connector.kafka.source.enumerator.initializer.OffsetsInitializer;
  13. import org.apache.flink.streaming.api.datastream.DataStreamSource;
  14. import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
  15. import org.apache.kafka.clients.consumer.OffsetResetStrategy;
  17. import java.nio.charset.StandardCharsets;
  19. /**
  20.  * @author MatrixOne
  21.  * @desc
  22.  */
  23. public class Kafka2Mo {
  25.     private static String srcServer = "192.168.146.12:9092";
  26.     private static String srcTopic = "matrixone";
  27.     private static String consumerGroup = "matrixone_group";
  29.     private static String destHost = "192.168.146.10";
  30.     private static Integer destPort = 6001;
  31.     private static String destUserName = "root";
  32.     private static String destPassword = "111";
  33.     private static String destDataBase = "test";
  34.     private static String destTable = "person";
  36.     public static void main(String[] args) throws Exception {
  38.         //初始化环境
  39.         StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
  40.         //设置并行度
  41.         env.setParallelism(1);
  43.         //设置 kafka source 信息
  44.         KafkaSource<User> source = KafkaSource.<User>builder()
  45.                 //Kafka 服务
  46.                 .setBootstrapServers(srcServer)
  47.                 //消息主题
  48.                 .setTopics(srcTopic)
  49.                 //消费组
  50.                 .setGroupId(consumerGroup)
  51.                 //偏移量 当没有提交偏移量则从最开始开始消费
  52.                 .setStartingOffsets(OffsetsInitializer.committedOffsets(OffsetResetStrategy.LATEST))
  53.                 //自定义解析消息内容
  54.                 .setValueOnlyDeserializer(new AbstractDeserializationSchema<User>() {
  55.                     @Override
  56.                     public User deserialize(byte[] message) {
  57.                         return JSON.parseObject(new String(message, StandardCharsets.UTF_8), User.class);
  58.                     }
  59.                 })
  60.                 .build();
  61.         DataStreamSource<User> kafkaSource = env.fromSource(source, WatermarkStrategy.noWatermarks(), "kafka_maxtixone");
  62.         //kafkaSource.print();
  64.         //设置 matrixone sink 信息
  65.         kafkaSource.addSink(JdbcSink.sink(
  66.                 "insert into users (id,name,age) values(?,?,?)",
  67.                 (JdbcStatementBuilder<User>) (preparedStatement, user) -> {
  68.                     preparedStatement.setInt(1, user.getId());
  69.                     preparedStatement.setString(2, user.getName());
  70.                     preparedStatement.setInt(3, user.getAge());
  71.                 },
  72.                 JdbcExecutionOptions.builder()
  73.                         //默认值 5000
  74.                         .withBatchSize(1000)
  75.                         //默认值为 0
  76.                         .withBatchIntervalMs(200)
  77.                         //最大尝试次数
  78.                         .withMaxRetries(5)
  79.                         .build(),
  80.                 JdbcConnectorOptions.builder()
  81.                         .setDBUrl("jdbc:mysql://"+destHost+":"+destPort+"/"+destDataBase)
  82.                         .setUsername(destUserName)
  83.                         .setPassword(destPassword)
  84.                         .setDriverName("com.mysql.cj.jdbc.Driver")
  85.                         .setTableName(destTable)
  86.                         .build()
  87.         ));
  88.         env.execute();
  89.     }
  90. }

代码编写完成后，你可以运行 Flink 任务，即在 IDEA 中选择 Kafka2Mo.java 文件，然后执行 Kafka2Mo.Main()。

步骤四：生成数据

使用 Kafka 提供的命令行生产者工具，您可以向 Kafka 的 “matrixone” 主题中添加数据。在下面的命令中，使用 --topic 参数指定要添加到的主题，而 --bootstrap-server 参数指定了 Kafka 服务的监听地址。

  1. bin/kafka-console-producer.sh --topic matrixone --bootstrap-server 192.168.146.12:9092

执行上述命令后，您将在控制台上等待输入消息内容。只需直接输入消息值 (value)，每行表示一条消息（以换行符分隔），如下所示：

  1. {"id": 10, "name": "xiaowang", "age": 22}
  2. {"id": 20, "name": "xiaozhang", "age": 24}
  3. {"id": 30, "name": "xiaogao", "age": 18}
  4. {"id": 40, "name": "xiaowu", "age": 20}
  5. {"id": 50, "name": "xiaoli", "age": 42}

使用 Flink 将实时数据写入 MatrixOne - 图3

步骤五：查看执行结果

在 MatrixOne 中执行如下 SQL 查询结果：

  1. mysql> select * from test.users;
  2. +------+-----------+------+
  3. | id   | name      | age  |
  4. +------+-----------+------+
  5. |   10 | xiaowang  |   22 |
  6. |   20 | xiaozhang |   24 |
  7. |   30 | xiaogao   |   18 |
  8. |   40 | xiaowu    |   20 |
  9. |   50 | xiaoli    |   42 |
  10. +------+-----------+------+
  11. 5 rows in set (0.01 sec)