Writing Real-Time Data to MatrixOne Using Flink

Overview

Apache Flink is a powerful framework and distributed processing engine focusing on stateful computation. It is suitable for processing both unbounded and bounded data streams efficiently. Flink can operate efficiently in various common cluster environments and performs calculations at memory speed. It supports processing data of any scale.

Scenarios

  • Event-Driven Applications

Event-driven applications typically have states and extract data from one or more event streams. They trigger computations, update states, or perform other external actions based on incoming events. Typical event-driven applications include anti-fraud systems, anomaly detection, rule-based alert systems, and business process monitoring. - Data Analytics Applications

The primary goal of data analytics tasks is to extract valuable information and metrics from raw data. Flink supports streaming and batch analytics applications, making it suitable for various scenarios such as telecom network quality monitoring, product updates, and experiment evaluation analysis in mobile applications, real-time ad-hoc analysis in the consumer technology space, and large-scale graph analysis. - Data Pipeline Applications

Extract, transform, load (ETL) is a standard method for transferring data between different storage systems. Data pipelines and ETL jobs are similar in that they can perform data transformation and enrichment and move data from one storage system to another. The difference is that data pipelines run in a continuous streaming mode rather than being triggered periodically. Typical data pipeline applications include real-time query index building in e-commerce and continuous ETL.

This document will introduce two examples. One involves using the Flink computing engine to write real-time data to MatrixOne, and the other uses the Flink computing engine to write streaming data to the MatrixOne database.

Before you start

Hardware Environment

The hardware requirements for this practice are as follows:

| Server Name | Server IP | Installed Software | Operating System | | node1 | 192.168.146.10 | MatrixOne | Debian11.1 x86 | | node2 | 192.168.146.12 | kafka | Centos7.9 | | node3 | 192.168.146.11 | IDEA,MYSQL | win10 |

Software Environment

This practice requires the installation and deployment of the following software environments:

Example 1: Migrating Data from MySQL to MatrixOne

Step 1: Initialize the Project

  1. Start IDEA, click File > New > Project, select Spring Initializer, and fill in the following configuration parameters:

  2. Name:matrixone-flink-demo

  3. Location:~\Desktop
  4. Language:Java
  5. Type:Maven
  6. Group:com.example
  7. Artifact:matrixone-flink-demo
  8. Package name:com.matrixone.flink.demo
  9. JDK 1.8

Writing Real-Time Data to MatrixOne Using Flink - 图1 2. Add project dependencies and edit the content of pom.xml in the project root directory as follows:

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 related dependency packages-->
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 related dependency packages -->
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>

Step 2: Read MatrixOne Data

After connecting to MatrixOne using the MySQL client, create the necessary database and data tables for the demonstration.

  1. Create a database, tables and import data in 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. In IDEA, create the MoRead.java class to read MatrixOne data using Flink:

``` 1. package com.matrixone.flink.demo;

  1. import org.apache.flink.api.common.functions.MapFunction;
  2. import org.apache.flink.api.common.typeinfo.BasicTypeInfo;
  3. import org.apache.flink.api.java.ExecutionEnvironment;
  4. import org.apache.flink.api.java.operators.DataSource;
  5. import org.apache.flink.api.java.operators.MapOperator;
  6. import org.apache.flink.api.java.typeutils.RowTypeInfo;
  7. import org.apache.flink.connector.jdbc.JdbcInputFormat;

  8. import org.apache.flink.types.Row;

  9. import java.text.SimpleDateFormat;

  10. /**

    • @author MatrixOne
    • @description
  13. */

  14. public class MoRead {

  15. private static String srcHost = "192.168.146.10";

  16. private static Integer srcPort = 6001;
  17. private static String srcUserName = "root";
  18. private static String srcPassword = "111";

  19. private static String srcDataBase = "test";

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

  21. ExecutionEnvironment environment = ExecutionEnvironment.getExecutionEnvironment();

  22. // Set parallelism
  23. environment.setParallelism(1);

  24. SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

  25. // Set query field type

  26. RowTypeInfo rowTypeInfo = new RowTypeInfo(
  27. new BasicTypeInfo[]{
  28. BasicTypeInfo.INT_TYPE_INFO,
  29. BasicTypeInfo.STRING_TYPE_INFO,
  30. BasicTypeInfo.DATE_TYPE_INFO
  31. },
  32. new String[]{
  33. "id",
  34. "name",
  35. "birthday"
  36. }

  37. );

  38. DataSource dataSource = environment.createInput(JdbcInputFormat.buildJdbcInputFormat()

  39. .setDrivername("com.mysql.cj.jdbc.Driver")
  40. .setDBUrl("jdbc:mysql://" + srcHost + ":" + srcPort + "/" + srcDataBase)
  41. .setUsername(srcUserName)
  42. .setPassword(srcPassword)
  43. .setQuery("select * from person")
  44. .setRowTypeInfo(rowTypeInfo)

  45. .finish());

  46. // Convert Wed Jul 12 00:00:00 CST 2023 date format to 2023-07-12

  47. MapOperator mapOperator = dataSource.map((MapFunction) row -> {
  48. row.setField("birthday", sdf.format(row.getField("birthday")));
  49. return row;

  50. });

  51. mapOperator.print();

  52. }
  53. } ```
  54. Run MoRead.Main() in IDEA, the result is as below:

Writing Real-Time Data to MatrixOne Using Flink - 图2

Step 3: Write MySQL Data to MatrixOne

Now, you can begin migrating MySQL data to MatrixOne using Flink.

  1. Prepare MySQL data: On node3, use the MySQL client to connect to the local MySQL instance. Create the necessary database, tables, and insert data:

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. Clear MatrixOne table data:

On node3, use the MySQL client to connect to the local MatrixOne instance. Since this example continues to use the test database from the previous MatrixOne data reading example, you need to clear the data from the person table first.

1. -- On node3, use the MySQL client to connect to the local MatrixOne 2. mysql -h192.168.146.10 -P6001 -uroot -p111 3. mysql> TRUNCATE TABLE test.person; 3. Write code in IDEA:

Create the Person.java and Mysql2Mo.java classes to use Flink to read MySQL data. Refer to the following example for the Mysql2Mo.java class code:

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. //Set parallelism
40. environment.setParallelism(1);
41. //Set query field type
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. // add 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. //run 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. //set matrixone sink information
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. }

Step 4: View the Execution Results

Execute the following SQL in MatrixOne to view the execution results:

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)

Example 2: Importing Kafka data to MatrixOne

Step 1: Start the Kafka Service

Kafka cluster coordination and metadata management can be achieved using KRaft or ZooKeeper. Here, we will use Kafka version 3.5.0, eliminating the need for a standalone ZooKeeper software and utilizing Kafka’s built-in KRaft for metadata management. Follow the steps below to configure the settings. The configuration file can be found in the Kafka software’s root directory under config/kraft/server.properties.

The configuration file is as follows:

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

After the file configuration is completed, execute the following command to start the Kafka service:

1. #Generate cluster ID
2. $ KAFKA_CLUSTER_ID="$(bin/kafka-storage.sh random-uuid)"
3. #Set log directory format
4. $ bin/kafka-storage.sh format -t $KAFKA_CLUSTER_ID -c config/kraft/server.properties
5. #Start Kafka service
6. $ bin/kafka-server-start.sh config/kraft/server.properties

Step 2: Create a Kafka Topic

To enable Flink to read data from and write data to MatrixOne, we first need to create a Kafka topic named “matrixone.” In the command below, use the --bootstrap-server parameter to specify the Kafka service’s listening address as 192.168.146.12:9092:

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

Step 3: Read MatrixOne Data

After connecting to the MatrixOne database, perform the following steps to create the necessary database and tables:

  1. Create a database, and tables and import data in MatrixOne:

1. CREATE TABLE `users` ( 2. `id` INT DEFAULT NULL, 3. `name` VARCHAR(255) DEFAULT NULL, 4. `age` INT DEFAULT NULL 5. ) 2. Write code in the IDEA integrated development environment:

In IDEA, create two classes: User.java and Kafka2Mo.java. These classes read from Kafka and write data to the MatrixOne database using Flink.

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. //Initialize environment
39. StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
40. //Set parallelism
41. env.setParallelism(1);

43. //Set kafka source information
44. KafkaSource<User> source = KafkaSource.<User>builder()
45. //Kafka service
46. .setBootstrapServers(srcServer)
47. //Message topic
48. .setTopics(srcTopic)
49. //Consumer group
50. .setGroupId(consumerGroup)
51. //Offset When no offset is submitted, consumption starts from the beginning.
52. .setStartingOffsets(OffsetsInitializer.committedOffsets(OffsetResetStrategy.LATEST))
53. //Customized parsing message content
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. //set matrixone sink information
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. //default value is 5000
74. .withBatchSize(1000)
75. //default value is 0
76. .withBatchIntervalMs(200)
77. //Maximum attempts
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. }

After writing the code, you can run the Flink task by selecting the Kafka2Mo.java file in IDEA and executing Kafka2Mo.Main().

Step 4: Generate data

You can add data to Kafka’s “matrixone” topic using the command-line producer tools provided by Kafka. In the following command, use the --topic parameter to specify the topic to add to and the --bootstrap-server parameter to determine the listening address of the Kafka service.

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

After executing the above command, you will wait for the message content to be entered on the console. Enter the message values ​​(values) directly, with each line representing one message (separated by newline characters), as follows:

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}

Writing Real-Time Data to MatrixOne Using Flink - 图3

Step 5: View execution results

Execute the following SQL query results in MatrixOne:

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)