Method for realizing real-time synchronization from MySQL to Dores based on FlinkCDC

Abstract

The invention discloses a method for realizing real-time synchronization from MySQL to Dores based on FlinkCDC, aiming at the special requirement of a business party on deletion operation, when the method is used for processing deleted data streams, soft deletion is realized on records physically deleted at a MySQL end at a Dores end, and the records are accurately marked as' deleted '; in a deletion operation processing flow, a DeleteFlag field is added for befront data, a value is assigned to be 1, and meanwhile, a DorisCreateTime field is added to record deletion time, so that data records are reserved, convenience is provided for data auditing and tracking, the defect that service soft deletion logic cannot be met when data is synchronously deleted in an original method is effectively overcome, and the service soft deletion efficiency is improved. And the flexibility of the service in the aspect of data processing is greatly enhanced.

Description

Technical Field

[0001] The present invention relates to the field of electromechanical equipment, and in particular to a method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC. Background Art

[0002] The existing method mainly uses the flink-doris-connector package officially maintained by Doris to achieve real-time synchronization of MySQL data to Doris, including data synchronization and DDL synchronization. Specifically: by capturing the CRUD data stream and schema change change stream in the binlog log of the MySQL database, these change records are transferred to the Doris table. Real-time synchronization of MySQL to Doris table data and real-time changes of adding and deleting table fields are achieved. In the current method, the synchronized data cannot be modified according to the business logic, which greatly limits the flexibility of the business: During the synchronization process, the business side requires that for records that are physically deleted on the MySQL side, the record must be retained on the Doris side (soft deleted) and marked as "deleted", rather than directly deleting the record by physically deleting it on the MySQL side in the source mode.

[0003] It is not possible to meet the needs of the business party to add fields during the synchronization process, such as time update fields, deletion identification fields, etc.

[0004] It is not possible to synchronize schema change operations such as expanding the length of table fields to the Doris end in real time.

[0005] In summary, a method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC is needed to solve the shortcomings of the existing technology. Summary of the invention

[0006] In view of the deficiencies in the prior art, the present invention provides a method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC, aiming to solve the above problems.

[0007] To achieve the above object, the present invention provides the following technical method: a method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC, comprising the following steps: Step S1: Initialization and configuration. Use the Flink builder to set connection parameters, specify the database and table to be captured, configure startup options and synchronization mode, and perform deserialization operations. Step S2: Set up the Flink environment, initialize the Flink stream execution environment, set the checkpoint interval, and create a DataStreamSource to obtain MySQL data; Step S3: Parse and convert data. Parse each piece of data in the mysqlSource stream from the Debezium format into an object containing key fields, and process it according to the OP attribute of the operation type. Step S4: Customize the json object of each data according to the operator OP value; Step S5: If the OP value is null, it is identified as a schema change operation, and regular expressions are used to extract column names, types, and other attributes, and the MySQL data type is converted to the Doris type, and a DDL statement for modifying the Doris table structure is constructed; Step S6: Configure Doris Sink, set the information for connecting to the Doris database, build and configure DorisSink, and specify a custom serializer; Step S7: Data is synchronized to Doris, and a request containing the final DDL statement is sent to Doris through the constructed HttpPost object.

[0008] Optionally, step S1 includes the following steps: Step A1: Set basic connection parameters and use MySqlSource.Builder to configure the basic connection information of the MySQL server; Step A2: Specify the capture scope and startup mode, specify the database and table to be captured through databaseList and tableList, and select the appropriate StartupOptions startup mode; Step A3: Configure deserialization and related properties: Create a JsonDebeziumDeserializationSchema object as a deserializer, turn on the includeSchemaChanges option, and set the time zone and server_id.

[0009] Optionally, step S2 includes the following steps: Step B1: Initialize the execution environment and obtain the Flink stream execution environment object; Step B2: Configure parallelism and checkpoint mechanism, set the parallelism and checkpoint mechanism of the execution environment to make data processing stable; Step B3: Create a MySQL data source: Based on the configured MySqlSource object, create a DataStreamSource object mySQLSource.

[0010] Optionally, the data is parsed and converted in step S3 in the following manner: Step C1: Convert each row of data passed in from a string in Debezium format to a JSON object; Step C2: Extract key sub-objects and important fields from the JSON object, including but not limited to: Before: used to record the status before updating or deleting; After: used to record the status after new insertion or update; Source: used to provide data source information; OP: indicates the operation type; ts_ms: indicates the timestamp when the operation occurred.

[0011] Optionally, the custom processing in step S4 includes the following methods: Create (c) and read (r) operations are processed to operate on the after data, mark the undeleted state, and set the format time of the current timestamp; Update (u) operation processing: Process the after data so that all necessary fields are correctly set or converted.

[0012] Delete (d) operation processing: operate on the before data, add DeleteFlag to mark the record as deleted, add DorisCreateTime and process the null value.

[0013] Optionally, step S5 is performed in the following manner: Step C1: Create a regular expression that matches MODIFY type ddl statements; Step C2: Determine whether it is a MODIFY DDL statement and extract matching results; Step C3: traverse the matching results and process the column information to obtain: column name, column type, length, number of decimal places, and comments; Step C4: Convert the data type of the MySQL database to the corresponding data type of the Doris database; Step C5: Construct the FieldSchema object with the field name, the corresponding data type after conversion and the annotation information, and obtain the doris database and table; Step C6: Construct the Doris DDL statement by using the String.format method to construct the preliminary DDL statement in a specific format.

[0014] Optionally, configuring Doris Sink in step S6 includes configuring DorisOptions and constructing DorisSink; Configure DorisOptions in the following ways: Step D1: Initialize a DorisOptions builder; Step D2: Set up Fenodes and call the configuration item to specify the front-end node address and port of the Doris cluster; Step D3: Set TableIdentifier, use setTableIdentifier to determine the identifier of the target table and write the data to the location in the Doris database; Step D4: Call the .build() method to complete the creation of the DorisOptions object and obtain a dorisOptions object that contains all necessary connection information, target table information, and authentication information.

[0015] Optionally, the Doris Sink is constructed in the following manner: Step E1: Set LabelPrefix. Use LabelPrefix to set a label prefix for the execution operation to identify and distinguish different tasks or operations during the execution of the Doris database operation. Step E2: Configure StreamLoadProp and set the properties related to stream loading to DorisExecutionOptions; Step E3: Set the Deletable property and specify whether the operation allows deletion of related data or resources through .setDeletable; Step E4: Build a DorisExecutionOptions instance. After completing all settings, call the .build() method to build a configured DorisExecutionOptions.

[0016] Beneficial effects of the present invention: 1. In the present invention, in view of the special requirements of the business party for the deletion operation, this method realizes soft deletion on the Doris side and accurately marks the records physically deleted on the MySQL side as "deleted" when processing the deletion data stream; by adding the DeleteFlag field to the before data and assigning it a value of 1 in the deletion operation processing flow, and adding the DorisCreateTime field to record the deletion time, not only the data records are retained, but also convenience is provided for data auditing and tracking, which effectively solves the defect that the business soft deletion logic cannot be satisfied when synchronously deleting data in the original method, and greatly enhances the flexibility of the business in data processing; 2. In the present invention, according to the business party's need to add fields during the synchronization process, the addition of time update fields (DorisCreateTime) and deletion identification fields (DeleteFlag) can be smoothly realized; in the creation, reading and update operation processing, the corresponding fields are added and processed to the data object, so that the data synchronized to the Doris end is more in line with the business logic, overcoming the limitations of the original method in this regard, so that the data can better adapt to the changes and expansion of business scenarios; 3. In the present invention, when using Flink to obtain data from the MySQL database, the accuracy and completeness of data acquisition are ensured by carefully setting basic connection parameters, capturing library table ranges, startup mode, deserialization and related attributes. At the same time, the Flink execution environment parameters are reasonably configured, such as setting the parallelism to 1 for specific debugging or resource-constrained scenarios, enabling the checkpoint mechanism and specifying the storage location, ensuring the preservation of task status and fault recovery capabilities during stream processing, laying a solid and stable foundation for the entire data synchronization process, and being more complete and reliable in data acquisition and processing environment construction than the original method; 4. In the present invention, when processing the Schema Change operation to send DDL statements to Doris, the process of constructing the HttpPost object is rigorous and comprehensive. From setting the request URL (including the random endpoint and the target database name), the authorization header, the content type header, to putting the DDL statement in the request body in JSON format, each step has been carefully designed to ensure that the HTTP request can accurately transmit the Schema Change information to the Doris end, so that the database structure changes can take effect in a timely manner, improve the timeliness and reliability of data synchronization, and improve the original method when processing the Schema Change operation to synchronize to Doris. The situation of untimely or inaccurate transmission may exist. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1The present invention is a schematic flow chart of a method. DETAILED DESCRIPTION

[0018] In order to more clearly illustrate the embodiments of the invention or the technical methods in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0019] like Figure 1 As shown in the figure, a method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC includes the following contents: Use Flink to obtain data from the MySQL database and perform related configuration Set basic connection parameters: Use the builder mode of MySqlSource to create an object, and set the basic connection information such as the host name (HOST), port number (MYSQL_PORT), database user name (MYSQL_USER) and password (MYSQL_PASSWD) of the MySQL server in turn to ensure that a valid connection can be established with the MySQL database.

[0020] Specify the capture specific library table range and configuration startup mode: Use databaseList and tableList to specify the database and table list to capture data. At the same time, set the startup option to StartupOptions. There are three startup modes, which can be configured accordingly according to needs.

[0021] Initial: This mode performs a full table scan to form a snapshot, which is suitable for situations where historical data needs to be captured. Since binlog does not necessarily contain all data, a full table scan of all tables is required to form a snapshot. earliest‌: Read from the earliest change log, synchronize only incremental data, and ignore the full data. Applicable to situations where you need to synchronize data from the earliest change log‌ latest‌: Read the latest data. This is applicable to real-time tables and is often used when synchronization needs to start from the latest data.

[0022] Configure deserialization and related properties and MySQL server related properties: Create and pass in a JsonDebeziumDeserializationSchema object as the deserializer, set includeSchemaChanges to true, indicating that changes in the database table structure and other schemas should be included; specify the server's time zone as Asia / Shanghai, and set a unique server_id to connect to the MySQL server.

[0023] Set Flink execution environment related parameters and create MySQL data source Get and initialize the execution environment: Get Flink's stream execution environment object env through StreamExecutionEnvironment.getExecutionEnvironment(), laying the foundation for subsequent execution of stream processing tasks in this environment.

[0024] Set the parallelism and checkpoint mechanism: Set the parallelism of the execution environment to 1, limiting only one task instance to run when executing stream processing tasks, which may be used to simplify debugging or specific resource-constrained scenarios. At the same time, enable the checkpoint mechanism, set the checkpoint interval to 3000 milliseconds, that is, create a checkpoint every 3 seconds, and specify the storage location of the checkpoint as an address (absolute path) of the local server, so as to save the task status during the stream processing process and ensure rapid recovery in the event of a failure.

[0025] Finally, based on the MySqlSource object constructed earlier, the DataStreamSource object mySQLSource is created through the env.fromSource method, and its name is given as "MySQL Source". This data source will obtain data from the MySQL database and provide a data stream to meet the subsequent data processing.

[0026] Parse each data of the mysqlSource stream from the Debezium format into objects containing key fields such as op.

[0027] Convert each row of data passed in from a Debezium-formatted string into a JSON object, and then extract key sub-objects such as "before", "after", "source" and important fields such as "op", "ts_ms", and "transaction" from the JSON object. The extracted data will serve as the basic information source for subsequent processing based on the operation type, and is used to support the execution of CRUD and schema change related logic.

[0028] Debezium format: Since the middleware for flinkcdc's underlying data parsing is Debezium, first understand the Debezium format: The JSON data generated by Debezium has a specific structure and rich properties, which are used to describe various database change events in detail, including common CRUD operations and Schema Change related information. The following is a comprehensive explanation of its JSON structure and properties: before: It is essential for update and delete operations. In update operations, it presents the state of the record before the update; in delete operations, it retains the original appearance of the deleted record. It is itself a JSON object, and the internal key-value pairs match the column names and their corresponding values ​​in the database table one by one. For example, if the database table has id, name, and age columns, for a record with an id of 1, a name of "John", and an age of 30, before may be presented as {"id": 1,"name": "John","age": 30} in the JSON of an update or delete operation. This property helps track the historical state of data and compare the differences before and after data changes.

[0029] after: Mainly used for insert and update operations. In insert operations, it contains the complete information of the newly inserted record; in update operations, it is the latest status of the record after the update. It is also a JSON object with a similar structure to before. For example, when inserting a new record with id 2, name "Jane", and age 25, after will be {"id":2,"name": "Jane","age": 25}. Through the after attribute, you can get the latest data content in the database after the data operation.

[0030] source: Provides detailed information about the data source, which is a JSON object. It contains key information such as database name (db), table name (table), server name (server), timestamp (ts_ms), etc. For example, {"db":"mydb","table":"mytable","server": "mysql-server","ts_ms":1609459200000}, where ts_ms accurately records the timestamp of the operation (in milliseconds), which is convenient for determining the time sequence of data changes and comparing timestamps when synchronizing data.

[0031] op: A string attribute indicating the type of operation. Common values ​​are "c" (corresponding to insert operations, i.e. create), "r" (representing read operations, i.e. read), "u" (representing update operations, i.e. update), or "d" (representing delete operations, i.e. delete). The op attribute can be used to quickly determine what kind of database operation the current JSON data describes, thereby determining the subsequent data processing logic.

[0032] ts_ms: As mentioned in the source attribute above, it is used to record the exact timestamp (in milliseconds) when the operation occurred. This not only plays a key role in data synchronization and consistency processing, but can also be used for auditing and tracking data changes, helping to determine the state changes of data at different time points.

[0033] transaction: This property appears when the operation is in a transaction environment. It is a JSON object that contains important information about the transaction, such as the transaction ID. This helps to understand the relevance and integrity of data at the transaction level when dealing with data changes involving transactions. For example, when dealing with distributed transactions or data synchronization related to rollback operations, the transaction property provides necessary context information.

[0034] Schema Change related properties (appears when there is a schema change): ddl: This is a string attribute that contains the DDL (Data Definition Language) statement that caused the schema change. For example, if the ALTER TABLE mytable ADD COLUMN new_column VARCHAR(50) operation is executed, the value of the ddl attribute will be the corresponding DDL statement. Through the ddl attribute, you can clearly understand what changes have occurred in the database table structure, so as to adjust the data processing logic or perform data migration operations in the data processing system accordingly.

[0035] tableChanges: is a JSON array object, where each element corresponds to the schema change information of a table. Each element contains properties such as type (change type, such as CREATE, ALTER, DROP, etc.), id (unique identifier of the table), table (JSON object containing the table name and column information). For example, for an ALTER TABLE operation, the elements in the tableChanges array may be similar. This property describes in detail the specific changes of each table in the schema change, which is very helpful for handling multi-table structure changes and complex data migration scenarios. "ts_ms" usually indicates the timestamp (in milliseconds) when the operation occurs.

[0036] Configure DorisOptions and build DorisSink 1. Configure DorisOptions First, use DorisOptions.builder to build the DorisOptions object, which is used to configure various parameters related to Doris database connection and operation.

[0037] Set Fenodes: The node address and port of the Doris cluster are specified through .setFenodes("ip:port"), which is the key information to establish a connection with the Doris database, telling the program which specific Doris node to connect to for subsequent operations.

[0038] Set TableIdentifier: Use .setTableIdentifier("schemachagedb"+"."+tbl) to determine the target table identifier in the Doris database. It is composed of the database name (here, schemachagedb) and the specific table name (concatenated by the variable tbl), which clarifies the specific location where the data will be written to the Doris database.

[0039] Set the username and password: Set the username and password required to connect to the Doris database through .setUsername("root") and .setPassword("dorispassword") respectively, which are used for identity authentication to ensure that only users with legal permissions can operate the Doris database.

[0040] Finally, the .build() method is used to complete the construction of the DorisOptions object, and a configured DorisOptions instance dorisOptions is obtained, which contains the basic connection information, target table information, and authentication information required to connect to the Doris database. 2. Build DorisSink In the getStringBuilder method, first build DorisExecutionOptions. Create a builder object through DorisExecutionOptions.Builder and set it as follows: Set LabelPrefix: Use .setLabelPrefix("label-doris" + UUID.randomUUID()) to set a label prefix with a random UUID for the execution operation. This label helps to identify and distinguish different tasks or operations during the execution of Doris's operation, which facilitates subsequent management and tracking.

[0041] Set StreamLoadProp: Set some stream loading related properties (through the passed-in Properties object props) to DorisExecutionOptions through .setStreamLoadProp(props). These properties may involve data loading methods, rules, etc., depending on the property information contained in props.

[0042] Set Deletable: Use .setDeletable(false) to explicitly set whether the operation can delete related data or resources. Setting it to false here means that it cannot be deleted, which helps protect the integrity and stability of the data and avoids accidental deletion of important data or resources during the operation.

[0043] After completing the above settings, a configured DorisExecutionOptions instance is constructed through the .build() method.

[0044] 3. DorisSink.Builder configuration Then construct the DorisSink.Builder object to further configure the operations related to writing data to the Doris database.

[0045] Set DorisReadOptions: The relevant options for reading Doris data are set through .setDorisReadOptions(DorisReadOptions.builder().build()). Although here we simply create a default DorisReadOptions instance through the builder (it may be further customized according to specific needs in actual applications), it provides a basic configuration framework for subsequent possible reading operations.

[0046] Set DorisExecutionOptions: Use .setDorisExecutionOptions(executionBuilder.build()) to set the previously built DorisExecutionOptions instance to DorisSink.Builder, so that the operation of writing to the Doris database can follow the previously set execution options, such as label prefix, stream loading properties, deletability, etc.

[0047] Set DorisOptions: Set the initially constructed DorisOptions instance containing connection information, table identifier, user name and password to DorisSink.Builder through .setDorisOptions(dorisOptions) to ensure that the write operation can accurately connect to the specified Doris database and table and has legal authentication.

[0048] Set Serializer: Instead of using the default SimpleStringSerializer, set the serializer by constructing SaasDebeziumDataChangeSerializer. When constructing SaasDebeziumDataChangeSerializer, Pass the DorisOptions instance again through .setDorisOptions(dorisOptions) to obtain relevant connection information, and set whether to use the new schemachange through .setNewSchemaChange(true) to support synchronization of MySQL multi-column changes and default values. Starting from 1.6.0, this parameter defaults to true. Then, use the .build() method to build a serializer that meets the requirements and set it to DorisSink.Builder to serialize the data to be written to the Doris database to ensure that the data can be received by the Doris database in the appropriate format.

[0049] Finally, through builder.build(), a fully configured DorisSink object can be obtained, which is used to accurately write the processed data into the Doris database according to the above configurations.

[0050] Customize the JSON object of each piece of data according to the operator op value (create, update, read, delete) Create (c) and read (r) operation processing When processing creation and reading operations, after extracting key sub-objects from the original JSON data, the focus is on the after data. The DeleteFlag field is added and assigned a value of "0" to clearly mark that the record is not deleted. At the same time, the DorisCreateTime field is added and set to the corresponding formatted time based on the current timestamp. Subsequently, each key-value pair in the after object is traversed and checked. Once a null value is found, it is converted to "\\N". Finally, the processed related sub-objects are reassembled into a new JSON object and output to the stream.

[0051] Update (u) operation processing The processing flow of the update operation is similar to that of the create and read operations. First, the key sub-object is obtained from the original JSON data, and then specific fields are added to the after data, such as setting DeleteFlag to "0" to indicate the undeleted state, and adding DorisCreateTime and setting it to the corresponding time. Then, the null value is processed for the key-value pairs in after, and the null value is converted to "\\N". After that, the JSON object is rebuilt and output to the stream.

[0052] Delete (d) operation processing When processing the deletion operation, after extracting the relevant sub-objects from the original JSON data, the operation is mainly performed on the before data. The DeleteFlag field is added and assigned a value of 1 to clearly indicate that the record has been deleted, realizing the logical mark of data soft deletion, meeting the business party's requirement that the Doris side still retains the record and marks the deletion status after the physical deletion on the MySQL side. At the same time, the DorisCreateTime field is added and set to the corresponding formatted time based on the current timestamp. After that, the null value is processed for the key-value pairs in before, and the null value is converted to "\\N". Finally, a new JSON object is constructed and output to the stream. This provides an accurate identification of the deletion time recorded on the Doris side, which is helpful for subsequent data auditing and tracking.

[0053] For each data record, if the op value of the json object is null, that is, the schema change operation is processed by customization - add the function of modify field change data flow parsing processing When the op value of the data stream is null, it is determined that the data is a schema change operation; by obtaining the different type values ​​in the jsons array object tableChanges above, it can be divided into CREATE operation and ALTER operation. Among them, ALTE is divided into ADD, DROP, RENAME / CHANGE and MODIFY types. Among them, the functions of CREATE, ADD, DROP, RENAME / CHANGE fields are already available in the original program. This invention focuses on the implementation process of the MODIFY type.

[0054] In the init initialization method in the newly created serialized SaasDebeziumDataChangeSerializer class above: The init method is mainly responsible for initializing some key objects in the SaasDebeziumDataChangeSerializer class. These objects are related to processing data changes (including schema changes schemaChange and ordinary data changes dataChange). During the initialization process, the value of newSchemaChange (setNewSchemaChange is set to true) is used to determine which specific schema change implementation class to use, laying the foundation for the subsequent correct processing of different types of data changes.

[0055] The schemaChange object is then initialized based on the value of newSchemaChange (which is set to true).

[0056] If newSchemaChange is true: a new instance of the JsonDebeziumSchemaChangeImplV3 class is created, and the previously created JsonDebeziumChangeContext object is passed as a parameter to the constructor, i.e. newJsonDebeziumSchemaChangeImplV3(changeContext). This indicates that when the newSchemaChange condition is met, the custom implementation class JsonDebeziumSchemaChangeImplV3 will be used to handle schema change related operations. The subsequent schema change flow processing is implemented in this class. The newly created JsonDebeziumSchemaChangeImplV3 inherits JsonDebeziumSchemaChange and re-implements the processing logic of the schemachange flow. 1. Create a regular expression that matches the MODIFY type ddl statement, in preparation for subsequent extraction of key information such as column name (columName), column type (columType) and comment (comment). Define a regular expression pattern modifyDDLPattern.

[0057] This regular expression pattern is a complex string combination, including various possible situations of the DDL statement of the MODIFY COLUMN type, which is composed of multiple sub-expressions connected by connectors. This regular expression can accurately match various possible situations of the business library MySQL, such as the data type, size, character set, nullability, default value, comment, and relative position of the column (AFTER or BEFORE other columns) of the modified column.

[0058] 2. Determine whether it is a MODIFY DDL statement and extract matching results First, historyRecord is extracted from the stream, and then the ddl statement string is obtained from it. This is the core data definition language (DDL) content that needs to be parsed and processed. Then a Matcher object modifyDdlMatcher is created, and modifyDDLPattern.matcher(ddl) is used to associate it with the incoming ddl statement. ModifyDdlMatcher.find() is used to determine whether it is a MODIFY DDL statement. If so, the following process is executed: Use a do-while loop to continuously search for all parts of the ddl statement that match the MODIFY COLUMN pattern through modifyDdlMatcher.find(), and add each matching result to the matchers list through matchers.add((Matcher)modifyDdlMatcher.toMatchResult());.

[0059] 3. Traverse the matching results and process the column information to obtain: column name, column type, length, number of decimal places, and comments.

[0060] For each Matcher object in the matchers list (that is, each matched MODIFY COLUMN statement part), perform the following processing: Extract the column name (columName), column type (columType) and comment (comment) information through the group method. If the comment is empty, set it to "None".

[0061] The extracted column type information is further parsed by defining a new regular expression pattern column and a Matcher object matcher column to cut the column type (such as varchar(10), decimal(19,0), etc.) into three parts: source type name (sourceTypeName), length (length), and scale (scale).

[0062] 4. Convert the data type of the MySQL database (source type name, length, precision and other related attributes) to the corresponding data type of the Doris database.

[0063] The column type information obtained in the previous step contains three parts: source type name (sourceTypeName), length (length) and scale (scale). In the toDorisType method for MySql type conversion, the switch statement is used to classify and process different MySQL data types, combined with possible length, precision and other attributes, and the corresponding Doris data type is obtained after multiple conditional judgments, thus completing the conversion process from MySql to Doris data type. The following example: case "DECIMAL UNSIGNED ZEROFILL": return length != null&&length<= 38 ? String.format("%s(%s,%s)", "DECIMALV3", length, scale != null&&scale>= 0 ? scale : 0) : "STRING"; Taking the MySQL data type "DECIMAL UNSIGNED ZEROFILL" as an example, if its length is 20 and the precision scale is 5, because the length satisfies length!= null&&length<= 38, the length and scale values ​​will be substituted into the format string through String.format and converted to the Doris data type "DECIMALV3(20,5)"; if the length is 50 and does not meet the conditions, it will be directly converted to "STRING". This is an example of the conversion process of this type of data from MySQL to Doris. The final data type in Doris is determined by conditional judgment based on different attribute values ​​of the data type.

[0064] 5. Build the field name, the corresponding data type after conversion and the annotation information FieldSchema object and obtain the doris database and table; Create new FieldSchema(columnName, newcolumnType, comment): Here, the object is created by calling the constructor of the FieldSchema class. Three parameters are passed in: columnName: indicates the name of the field. It should be a defined string variable, which is used to specify the specific field schema information that this FieldSchema object corresponds to.

[0065] newcolumnType: is the new data type of the field in the target environment (Doris database, etc.) obtained after a series of processing (the operation of converting from MySQL data type to Doris data type mentioned above) comment: Get the comment By splitting dorisOptions.getTableIdentifier(), we can get the name of the Doris database (db) and the table name (table).

[0066] 6. Construct Doris DDL statement Create a mutable string builder object modifyDDL using StringBuilder.

[0067] Then, the String.format method is used to construct the preliminary DDL statement in a specific format. The format string is "ALTER TABLE %s MODIFY COLUMN %s %s", where the three %s are placeholders and will be replaced by the following actual values: The first %s: is replaced by assembling the name (db) and table name (table) of the Doris database obtained above.

[0068] The second %s: Replace the field name columnName processed above with the second %s position to make it clear which field to modify.

[0069] The third %s: directly replace the obtained new data type newcolumnType to the third %s position, indicating the target data type to which the field is to be modified.

[0070] In addition, by calling ddl.append(" COMMENT '").append(DorisSystem.quoteComment(comment)).append("'"); Add comment based on modifyDDL.

[0071] 7. Construct an HttpPost object and send an HTTP request containing the final DDL statement to doris (according to the database parameter passed in).

[0072] The present invention meets the demand for real-time synchronization of data soft deletion from MySQL to Doris: In view of the special requirements of the business party for deletion operations, this method implements soft deletion on the Doris side and accurately marks the records physically deleted on the MySQL side as "deleted" when processing the deletion data stream. By adding a DeleteFlag field to the before data and assigning a value of 1 in the deletion operation processing flow, and adding a DorisCreateTime field to record the deletion time, not only the data records are retained, but also convenience is provided for data auditing and tracking, which effectively solves the defect that the original method cannot meet the business soft deletion logic when synchronously deleting data, and greatly enhances the flexibility of the business in data processing.

[0073] Support for adding custom fields: Based on the business party's needs to add fields during the synchronization process, it can smoothly implement the addition of time update fields (DorisCreateTime), deletion identification fields (DeleteFlag), etc. In the creation, reading and update operation processing, the corresponding fields are added and processed to the data objects, making the data synchronized to the Doris end more in line with the business logic, overcoming the limitations of the original method in this regard, and allowing the data to better adapt to the changes and expansion of business scenarios.

[0074] Improved the function of real-time synchronization of MODIFY fields in Schema Change Accurately parse MODIFY operations: For MODIFY types in Schema Change operations such as expanding the length of table fields, this method creates a special regular expression pattern (modifyDDLPattern) to match its DDL statements, which can accurately extract key information such as column names, column types, and comments, and effectively deal with various complex modifications to column data types, sizes, character sets, whether they can be nullable, default values, comments, and the relative position of columns. This makes up for the lack of real-time synchronization of MODIFY type Schema Change operations to the Doris end in the original method.

[0075] Reliable data processing and transmission Reasonable Flink configuration and data acquisition: When using Flink to obtain data from the MySQL database, the accuracy and completeness of data acquisition are ensured by carefully setting basic connection parameters, capturing library table ranges, startup mode, deserialization and related properties. At the same time, the Flink execution environment parameters are reasonably configured, such as setting the parallelism to 1 for specific debugging or resource-constrained scenarios, enabling the checkpoint mechanism and specifying the storage location, which ensures the preservation of task status and fault recovery capabilities during stream processing, laying a solid and stable foundation for the entire data synchronization process. Compared with the original method, it is more complete and reliable in data acquisition and processing environment construction.

[0076] Efficient HTTP request construction and transmission: When processing Schema Change operations to send DDL statements to Doris, the process of building HttpPost objects is rigorous and comprehensive. From setting the request URL (including the random endpoint and the target database name), authorization header, content type header, to putting the DDL statement in the request body in JSON format, each step has been carefully designed to ensure that the HTTP request can accurately transmit the Schema Change information to the Doris end, so that the database structure changes can take effect in a timely manner, improve the timeliness and reliability of data synchronization, and improve the original method when processing Schema Change operations to synchronize to Doris. The situation of untimely or inaccurate transmission may exist.

[0077] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modification, equivalent substitution or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC, characterized in that: The following steps are involved: Step S1: Initialization and configuration. Use the Flink builder to set connection parameters, specify the database and table to be captured, configure startup options and synchronization mode, and perform deserialization operations. Step S2: Set up the Flink environment, initialize the Flink stream execution environment, set the checkpoint interval, and create a DataStreamSource to obtain MySQL data; Step S3: Parse and convert data. Parse each piece of data in the mysqlSource stream from the Debezium format into an object containing key fields, and process it according to the OP attribute of the operation type. Step S4: Customize the json object of each data according to the operator OP value; Step S5: If the OP value is null, it is identified as a schema change operation, and regular expressions are used to extract column names, types, and other attributes, and the MySQL data type is converted to the Doris type, and a DDL statement for modifying the Doris table structure is constructed; Step S6: Configure Doris Sink, set the information for connecting to the Doris database, build and configure Doris Sink, and specify a custom serializer; Step S7: Data is synchronized to Doris, and a request containing the final DDL statement is sent to Doris through the constructed HttpPost object.

2. According to claim 1, the method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC is characterized in that: The step S1 comprises the following steps: Step A1: Set basic connection parameters and use MySqlSource.Builder to configure the basic connection information of the MySQL server; Step A2: Specify the capture scope and startup mode, specify the database and table to be captured through databaseList and tableList, and select the appropriate StartupOptions startup mode; Step A3: Configure deserialization and related properties: Create a JsonDebeziumDeserializationSchema object as a deserializer, turn on the includeSchemaChanges option, and set the time zone and server_id.

3. According to the method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC according to claim 1, it is characterized in that: The step S2 comprises the following steps: Step B1: Initialize the execution environment and obtain the Flink stream execution environment object; Step B2: Configure parallelism and checkpoint mechanism, set the parallelism and checkpoint mechanism of the execution environment to make data processing stable; Step B3: Create a MySQL data source: Based on the configured MySqlSource object, create a DataStreamSource object mySQLSource.

4. According to the method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC according to claim 1, it is characterized in that: The data is parsed and converted in step S3 in the following manner: Step C1: Convert each row of data passed in from a string in Debezium format to a JSON object; Step C2: Extract key sub-objects and important fields from the JSON object, including but not limited to: Before: used to record the status before updating or deleting; After: used to record the status after new insertion or update; Source: used to provide data source information; OP: indicates the operation type; ts_ms: indicates the timestamp when the operation occurred.

5. According to the method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC according to claim 1, it is characterized in that: The custom processing in step S4 includes the following methods: Create (c) and read (r) operations are processed to operate on the after data, mark the undeleted state, and set the format time of the current timestamp; Update (u) operation processing: Process the after data so that all necessary fields are set or converted correctly; Delete (d) operation processing: operate on the before data, add DeleteFlag to mark the record as deleted, add DorisCreateTime and process the null value.

6. According to the method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC according to claim 1, it is characterized in that: The step S5 is carried out in the following manner: Step C1: Create a regular expression that matches MODIFY type ddl statements; Step C2: Determine whether it is a MODIFY DDL statement and extract matching results; Step C3: traverse the matching results and process the column information to obtain: column name, column type, length, number of decimal places, and comments; Step C4: Convert the data type of the MySQL database to the corresponding data type of the Doris database; Step C5: Construct the FieldSchema object with the field name, the corresponding data type after conversion and the annotation information, and obtain the doris database and table; Step C6: Construct the Doris DDL statement by using the String.format method to construct the preliminary DDL statement in a specific format.

7. According to claim 1, the method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC is characterized in that: Configuring Doris Sink in step S6 includes configuring DorisOptions and building Doris Sink; Configure DorisOptions in the following ways: Step D1: Initialize a DorisOptions builder; Step D2: Set up Fenodes and call the configuration item to specify the front-end node address and port of the Doris cluster; Step D3: Set TableIdentifier, use setTableIdentifier to determine the identifier of the target table and write the data to the location in the Doris database; Step D4: Call the .build() method to complete the creation of the DorisOptions object and obtain a dorisOptions object that contains all necessary connection information, target table information, and authentication information.

8. According to claim 7, the method for implementing real-time synchronization from MySQL to Doris based on FlinkCDC is characterized in that: The Doris Sink is constructed in the following way: Step E1: Set LabelPrefix. Use LabelPrefix to set a label prefix for the execution operation to identify and distinguish different tasks or operations during the execution of the Doris database operation. Step E2: Configure StreamLoadProp and set the properties related to stream loading to DorisExecutionOptions; Step E3: Set the Deletable property and specify whether the operation allows deletion of related data or resources through .setDeletable; Step E4: Build a DorisExecutionOptions instance. After completing all settings, call the .build() method to build a configured DorisExecutionOptions.

Images