Data synchronization method, device and system, electronic equipment and medium

Abstract

The embodiment of the invention provides a data synchronization method, device and system, electronic equipment and a medium, and relates to the technical field of data transmission, and the technical scheme comprises the following steps: when it is detected that data in a source database is changed, obtaining a data change record of changed target data, and obtaining metadata of the target data, the metadata comprises a pattern name of the database and table information of the source data table. And then generating a synchronization message based on the data change record and the metadata, and writing the synchronization message into a theme corresponding to a source database in a message system. When it is monitored that a newly added target synchronization message exists in the theme of the message system, a data change record in the target synchronization message is sent to a demand end database, and the target synchronization message is a synchronization message comprising a preset mode name and preset table information. The operation cost and the fault risk of the database are reduced.

Description

Technical Field

[0001] The present application relates to the field of data transmission technology, and in particular to a data synchronization method, device, system, electronic device and medium. Background Art

[0002] Traditional enterprise systems typically store business data sets in databases and, at the end of each day, export the previous day's data for analysis. However, with the continued expansion of business volume and the rise of scenarios such as algorithmic trading, quantitative analysis, and automated risk control, simply relying on end-of-day batch exports can no longer meet the real-time requirements of various scenarios, including market analysis, dynamic adjustment of trading strategies, risk control, and real-time supervision.

[0003] Currently, the industry generally adopts a data synchronization solution based on Change Data Capture (CDC) technology. Specifically, when a downstream system needs to synchronize a certain data table in a database in real time, a CDC task is created. This CDC task establishes a resident connection with the database, continuously monitoring the incremental data in the table in the database and synchronizing the incremental data to the downstream system in real time. However, whenever a downstream system is added, or when a downstream system increases or changes its data synchronization requirements, a new CDC task must be created. When downstream systems have a high demand for data synchronization, the large number of CDC tasks places a high pressure on the database's resident connections. This not only occupies a large amount of the database's connection pool, memory, and network resources, but also increases the database load, resulting in high database operating costs and failure risks. Summary of the Invention

[0004] The purpose of the embodiments of the present application is to provide a data synchronization method, device, system, electronic device, and medium to reduce database operation costs and failure risks. The specific technical solutions are as follows:

[0005] In a first aspect, an embodiment of the present application provides a data synchronization method, the method comprising:

[0006] When a change is detected in the data in the source database, the data change record of the target data that has changed is obtained;

[0007] Obtain metadata of the target data, the metadata including: a schema name of the database to which it belongs and table information of the source data table to which it belongs;

[0008] Generate a synchronization message based on the data change record and the metadata, and write the synchronization message into a topic corresponding to the source database in a message system;

[0009] When a new target synchronization message is detected in the topic of the message system, a data change record in the target synchronization message is sent to the demand-side database. The target synchronization message is a synchronization message including a preset mode name and preset table information.

[0010] Optionally, when a newly added target synchronization message is monitored in the topic of the message system, before sending the data change record in the target synchronization message to the demand-side database, the method further includes:

[0011] Receive virtual table information, the virtual table information including: a source topic name of the virtual table to be synchronized with the demand-side database and filtering information of the virtual table, the filtering information including: a preset schema name of a source database to which data in the virtual table belongs and preset table information of a source data table;

[0012] Generate a real-time synchronization task using the message system as the source data source and the demand-side database as the target data source;

[0013] According to the real-time synchronization task, the message system is monitored.

[0014] Optionally, monitoring the message system according to the real-time synchronization task includes:

[0015] According to the real-time synchronization task, select a source synchronization plug-in corresponding to the type of the message system;

[0016] The source synchronization plug-in monitors, through the source synchronization plug-in, whether there is a target synchronization message including the filtering information in the source topic corresponding to the source topic name.

[0017] Optionally, the task information further includes the type and name of the demand-side database, the target-side type of the real-time synchronization task is the type of the demand-side database, and the target-side name of the real-time synchronization task is the name of the demand-side database; before sending the data change record in the target synchronization message to the demand-side database, the following is also included:

[0018] Select the target synchronization plug-in corresponding to the target end type;

[0019] The sending of the data change record in the target synchronization message to the demand-side database includes:

[0020] The data change record in the target synchronization message is sent to the demand-side database corresponding to the target-side name through the target synchronization plug-in.

[0021] In a second aspect, an embodiment of the present application provides a data synchronization device, the device comprising:

[0022] An acquisition module is used to acquire data change records of target data when a change is detected in the source database;

[0023] The acquisition module is further configured to acquire metadata of the target data, wherein the metadata includes: a schema name of the database to which it belongs and table information of the source data table to which it belongs;

[0024] A generation module, configured to generate a synchronization message based on the data change record and the metadata, and write the synchronization message into a topic corresponding to the source database in a message system;

[0025] The sending module is used to send the data change record in the target synchronization message to the demand-side database when it monitors that there is a new target synchronization message in the topic of the message system. The target synchronization message is a synchronization message including a preset mode name and preset table information.

[0026] Optionally, the device further includes:

[0027] A receiving module, configured to receive virtual table information before sending a data change record in the target synchronization message to the demand-side database when a newly added target synchronization message is detected in the topic of the message system, wherein the virtual table information includes: a source topic name of the virtual table to be synchronized in the demand-side database and filtering information of the virtual table, wherein the filtering information includes: a preset schema name of the source database to which the data in the virtual table belongs and preset table information of the source data table to which it belongs;

[0028] The generation module generates a real-time synchronization task using the message system as a source data source and the demand-side database as a target data source;

[0029] A monitoring module is used to monitor the message system according to the real-time synchronization task.

[0030] Optionally, the monitoring module is specifically used to: select a source synchronization plug-in corresponding to the type of the message system based on the real-time synchronization task; and monitor through the source synchronization plug-in whether there is a target synchronization message including the filtering information in the source topic corresponding to the source topic name.

[0031] Optionally, the task information further includes the type and name of the demand-side database, the target-side type of the real-time synchronization task is the type of the demand-side database, and the target-side name of the real-time synchronization task is the name of the demand-side database; the device further includes:

[0032] A startup module, configured to select a target synchronization plug-in corresponding to the target end type before sending the data change record in the target synchronization message to the demand end database;

[0033] The sending module is specifically used to: send the data change record in the target synchronization message to the demand-side database corresponding to the target-side name through the target synchronization plug-in.

[0034] In a third aspect, an embodiment of the present application provides a data synchronization system, the system comprising:

[0035] Source database, used to store data;

[0036] A data synchronization platform, configured to execute the method described in any one of the first aspects;

[0037] A message system, used to store synchronization messages from the data synchronization platform;

[0038] The demand-side database is used to receive the data change record in the target synchronization message sent by the data synchronization platform.

[0039] In a fourth aspect, an embodiment of the present application provides an electronic device, including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other via the communication bus;

[0040] Memory for storing computer programs;

[0041] The processor is configured to implement the method described in any one of the first aspects when executing a program stored in the memory.

[0042] In a fifth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method described in any one of the first aspects is implemented.

[0043] In a sixth aspect, an embodiment of the present application further provides a computer program product comprising instructions, which, when executed on a computer, enables the computer to execute the data synchronization method described in any one of the first aspects above.

[0044] Beneficial effects of the embodiments of the present application:

[0045] The data synchronization method, device, system, electronic device and medium provided by the embodiments of the present application can obtain the data change record of the target data that has changed, and the metadata of the target data when a change is detected in the data in the source database, and generate a synchronization message based on the data change record and the metadata, and write the synchronization message into the topic corresponding to the source database in the message system. When a newly added target synchronization message is detected in the topic of the message system, the data change record in the target synchronization message is sent to the demand-side database, where the target synchronization message is a synchronization message including a preset mode name and preset table information. It can be seen that the embodiments of the present application can write the data synchronization records of all the changed data in the source database into the message system through a synchronization task, and then distribute the messages in the message system according to the needs of the demand-side database. That is, the embodiments of the present application do not need to establish a resident connection with the source database for each data synchronization requirement, thereby reducing the operating cost and failure risk of the database.

[0046] Of course, it is not necessary to achieve all the advantages described above at the same time when implementing any product or method of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other embodiments can also be obtained based on these drawings.

[0048] Figure 1 A flowchart of a data synchronization method provided in an embodiment of the present application;

[0049] Figure 2 An exemplary schematic diagram of a data synchronization process provided in an embodiment of the present application;

[0050] Figure 3 A schematic diagram of the structure of a data synchronization system provided in an embodiment of the present application;

[0051] Figure 4 A schematic diagram of the structure of a data synchronization device provided in an embodiment of the present application;

[0052] Figure 5 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0053] The following will be combined with the drawings in the embodiments of this application to clearly and completely describe the technical solutions in the embodiments of this application. Obviously, the embodiments described are only part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field based on this application are within the scope of protection of this application.

[0054] To reduce database operating costs and failure risks, an embodiment of the present application provides a data synchronization method that is applied to a data synchronization platform. For example, the data synchronization platform may be SeaTunnel or i2UP, among others. SeaTunnel is an open-source, distributed, high-performance data integration tool that supports real-time synchronization of massive amounts of data and data integration between heterogeneous systems. i2UP is an efficient and intelligent data management platform that provides multi-dimensional big data management capabilities such as data monitoring, disaster recovery, migration, synchronization, distribution, sharing, integration, governance, and archiving. The data synchronization platform can run on electronic devices, such as servers, desktop computers, or laptops, which have data processing and communication capabilities.

[0055] like Figure 1 As shown, the data synchronization method provided in the embodiment of the present application includes the following steps:

[0056] S101. When a change is detected in data in a source database, a data change record of the target data is obtained.

[0057] The data synchronization platform can configure a multi-table real-time synchronization task for each source database that requires real-time data collection, and select an appropriate Change Data Capture (CDC) plug-in based on the type of the source database. When running the task, the CDC plug-in is used to establish a connection with the source database, and the CDC plug-in is used to parse the log of the source database to detect whether the data in each field of each data table in the source database has changed. When a change is detected in any field of any data table in the source database, the data change record of the target data that has changed is obtained. The data synchronization platform uses the same detection method and data change record acquisition method for each source database that requires real-time synchronization. The embodiment of the present application uses a source database as an example for explanation.

[0058] It is understandable that when data is added, deleted or modified in the source database, data changes will occur. The data change record may include: the content of the added target data, the modified content of the target data, the field name and data table name to which the target data belongs, etc.

[0059] Optionally, embodiments of the present application support multiple types of source databases, such as MySQL, Oracle, SQL Server, and DAMENG. MySQL is a relational database management system that uses Structured Query Language (SQL) to access databases; Oracle is a relational database management system with a distributed database as its core; SQL Server is a scalable and high-performance relational database management system; and DAMENG is a relational database management system with high-performance data processing capabilities and support for high-concurrency access.

[0060] S102: Obtain metadata of the target data, where the metadata includes: the schema name of the database to which it belongs and table information of the source data table to which it belongs.

[0061] The table information of the source data table to which the target data belongs includes the table name. The metadata may also include other information, such as the field name and field type of the field to which the target data belongs.

[0062] S103: Generate a synchronization message based on the data change record and metadata, and write the synchronization message into a topic corresponding to the source database in the message system.

[0063] The data synchronization platform can construct data change records and metadata into a structured data format, such as the Debezium JSON format, and use the converted data as synchronization messages. The Debezium JSON format is a structured data format that includes fields such as before, after, operation type (op), timestamp (ts_ms), and transaction information (transaction). The before field represents the data before the change; after represents the data after the change; the operation type represents the Data Manipulation Language (DML) operation performed on the data, such as insert, update, or delete; ts_ms represents the timestamp when the synchronization message was generated; and transaction information represents the transaction that caused the data change.

[0064] The data synchronization platform configures a multi-table real-time synchronization task for each source database requiring real-time data collection. The target plugin configured in this task is the write plugin for the messaging system. For example, if the messaging system is Kafka, the target plugin is the Kafka write plugin, which writes synchronization messages to Kafka. The multi-table real-time synchronization task is also configured with the topic corresponding to the source database. This allows the data synchronization platform to write synchronization messages to the topic corresponding to the source database in the messaging system after generating them when executing the multi-table real-time synchronization task.

[0065] For example, the messaging system can be Kafka, a high-throughput, low-latency distributed message queue system, a data streaming messaging system. Leveraging Kafka's inherent high throughput and strong scalability, synchronization messages generated based on data change records for each table in the source database are written to the corresponding topic in chronological order. These synchronization messages are then deleted from the topic when they meet a preset expiration condition, such as a preset expiration period after being stored in the topic.

[0066] S104: When a new target synchronization message is detected in the topic of the message system, a data change record in the target synchronization message is sent to the demand-side database.

[0067] The demand-side database is the database of the downstream system that needs to obtain data change records from the source database. Taking the securities industry as an example, the downstream system can be a risk warning system, a big data analysis system, a report generation system, or a regulatory system. For example, the database of each downstream system can be a relational data storage system such as MySQL, PostgreSQL, or TDSQL, or a non-relational data storage system such as MongoDB, Elasticsearch, or ClickHouse. Among them, PostgreSQL is a very powerful, open-source client / server relational database management system; TDSQL is a distributed relational database management system designed for high-load online transaction scenarios with high availability and high reliability; MongoDB is a database management system based on distributed file storage; Elasticsearch is a distributed, highly scalable, and highly real-time non-relational data storage system that supports data search and data analysis; ClickHouse is an open source column-based non-relational data storage system that supports real-time data analysis.

[0068] The target synchronization message is a synchronization message that includes the preset schema name and preset table information. Based on each downstream system's data synchronization requirements, the data synchronization platform can create a real-time synchronization task. This task retrieves synchronization messages containing the preset schema name and preset table information from the message system and synchronizes the data change records in these synchronization messages to the database of the specified downstream system.

[0069] The data synchronization method provided by the embodiment of the present application can obtain the data change record of the target data that has changed, and the metadata of the target data when it is detected that the data in the source database has changed, and generate a synchronization message based on the data change record and the metadata, and write the synchronization message into the topic corresponding to the source database in the message system. When it is monitored that there is a newly added target synchronization message in the topic of the message system, the data change record in the target synchronization message is sent to the demand-side database, where the target synchronization message is a synchronization message including a preset mode name and preset table information. It can be seen that the embodiment of the present application can write the data synchronization records of all the changed data in the source database into the message system through a synchronization task, and then distribute the messages in the message system according to the needs of the demand-side database. That is, the embodiment of the present application does not need to establish a resident connection with the source database for each data synchronization requirement, thereby reducing the operating cost and failure risk of the database.

[0070] The data synchronization method provided in the embodiment of the present application is described in detail below.

[0071] In an embodiment of the present application, the source database may enable CDC settings in advance, that is, enable corresponding log or data change record tracking options, and set data tables that support monitoring. For example, each data table in the source database may be set to support monitoring.

[0072] For example, MySQL enables binary logging (Binlog), Oracle enables the LogMiner / Java library (LogMiner / XStream) mechanism, and SQL Server enables the native CDC function, thereby supporting the data synchronization platform to detect whether its stored data has changed. The source databases supported by the embodiments of the present application are not limited to the above three types, and the method of enabling CDC settings for each source database is not limited to this. Based on the database type and business needs, you can select the appropriate CDC setting tool and perform the corresponding configuration to ensure that the data synchronization platform can accurately and efficiently capture data change records in the source database.

[0073] When checking the source database, the data synchronization platform uses CDC technology to analyze the source database's logs for new data change events. Each data change event includes the data change information and the table metadata for the data change event. The data synchronization platform obtains the data change information from the new data change event as a data change record. Based on the table metadata of the data change event, the platform extracts information such as the schema name, table name, field name, and field content to construct the metadata of the target data.

[0074] Taking MySQL as an example, the MySQL log records data change events using the MySQL Binlog event stream structure. This event stream structure consists of header data and multiple data change events. Each data change event includes a TABLE_MAP_EVENT and a WRITE_ROWS_EVENT. The TABLE_MAP_EVENT contains metadata about the table targeted by the data change event, such as the schema name, table ID, database name, table name, column data, data type, and metadata for each column field. The WRITE_ROWS_EVENT contains information before and after the data change, such as the target data identifier (ID), data content, and the username that modified the target data. The data synchronization platform can select all information from the TABLE_MAP_EVENT, or select information from multiple pre-set fields, and combine it with information such as the event timestamp in the header, the event's location in the log file, and the ID of the source database server as the target data's metadata. It also uses all information from the WRITE_ROWS_EVENT, or select information from multiple pre-set fields, as the data change record.

[0075] Taking Oracle as the source database, for example, the data synchronization platform can parse new data change events in Oracle logs and use all information in the data change event, or select information from multiple preset fields, as data change records. Furthermore, through the LogMiner view, it can obtain the table information corresponding to the new data change event, including the schema name and the corresponding table name. All information in the table information, or information from multiple preset fields, can be used as metadata for the target data.

[0076] Taking SQL Server as the source database, for example, the data synchronization platform can parse new data change events in the SQL Server log and use all information in the data change event, or select information on multiple preset fields, as data change records. It then searches the SQL Server CDC data change table for the change information corresponding to the new data change event. This change information includes the schema name and the corresponding table name. All information in the change information, or information on multiple preset fields, is used as metadata for the target data.

[0077] Afterwards, the data synchronization platform constructs the data change records and metadata into a structured data format, generates a synchronization message, and writes it into the topic corresponding to the source database in the message system. If each data table in the source database corresponds to a Topic of the message system, then when a data table is added to the source database, or when the downstream system needs to synchronize data in more data tables, it is necessary to increase the Topic of the message system or modify the data table configuration of the source database, resulting in increased management complexity and bringing certain risks to online operations. However, in the embodiment of the present application, each source database corresponds to a Topic of the message system. Whether the source database adds a new data table or the downstream system needs to synchronize more data tables, there is no need to modify the Topic of the message system or the data table configuration of the source database. Therefore, management complexity is reduced and online operation and maintenance risks are also reduced.

[0078] In an embodiment of the present application, the message system acts as an intermediate layer in the data synchronization process, responsible for receiving, storing and distributing synchronization messages generated based on the data change records of the source database. Before realizing data synchronization, the data synchronization platform can also pre-configure the message system. Taking Kafka as an example of the message system, the number of Kafka nodes, the number of Topic partitions and the number of partition replicas can be configured in advance based on actual business needs and data volume synchronization requirements to ensure that Kafka has high performance and reliability. Among them, the node (Broker) represents a single server instance of Kafka, which is responsible for storing synchronization messages. A partition is a physical storage unit of a Topic. A Topic can be divided into multiple partitions, and synchronization messages are stored sequentially in the partitions. Each partition can include multiple replicas, and each replica can be distributed on different nodes to serve as a backup. At the same time, the data synchronization platform can also configure Kafka's message retention policy and partitioning policy. Among them, the message retention policy represents the retention and deletion policy for synchronization messages within a Topic. For example, the retention policy includes a preset expiration condition, which means that when a synchronization message within a Topic meets the preset expiration condition, the synchronization message is deleted. Conversely, when the synchronization message within the Topic does not meet the preset expiration condition, the synchronization message is retained. The partitioning strategy represents the way in which synchronization messages are assigned to a partition of a Topic. For example, according to the order of the partitions included in the Topic, synchronization messages are sequentially written to the first partition, and when the total number of messages stored in the first partition reaches the preset threshold, synchronization messages are sequentially written to the next partition, and so on. Kafka's retention policy can meet the user's needs to trace back historical data change records over a period of time, and when new data synchronization needs arise, they can be obtained from the synchronization messages stored in Kafka, thereby ensuring that new needs capture historical data change records.

[0079] It can be seen that Kafka's greatest advantage lies in its distributed architecture design. When all data change records of a single database are centrally stored in the same Topic, Kafka can achieve horizontal expansion by increasing the number of nodes. Unlike vertical expansion, horizontal expansion is not limited by the hardware resource constraints of the node. Each node can bear the read and write load of messages in part of the partition. As the number of nodes increases, the overall throughput of the Kafka cluster will also increase linearly.

[0080] Moreover, the embodiment of the present application can also configure Kafka's intelligent partitioning strategy to balance the load. For example, the synchronization messages belonging to different data tables in the source database are written to different partitions, and hot data tables with frequent changes are set to occupy one or more partitions exclusively, so that data tables with fewer changes share other partitions. In this way, data skew is avoided, that is, the uneven distribution of data in the data table within the partition is avoided, and the downstream system is provided with the option of subscribing to different partitions of the Topic by data table, which greatly reduces the overhead of pulling and filtering invalid data.

[0081] In addition, Kafka supports the use of various storage optimization methods to address the challenge of surging stored data volumes. For example, Kafka supports a variety of compression algorithms, including several file compression programs (gzip), compression and decompression development kits (snappy), and compression and decompression tools (lz4). These can compress and store synchronous messages, thereby reducing disk usage and network transmission overhead. Kafka also supports tiered storage, which automatically migrates historical data to lower-cost storage media. Furthermore, Kafka supports flexible configuration of retention policies, such as automatically clearing expired data based on storage time or data size to prevent unlimited growth of stored data.

[0082] In an embodiment of the present application, the data synchronization platform can store data change records in the partition log of the Topic while writing synchronization messages to the Topic. Among them, the partition log includes a log (log) file, an offset (index) file, and a time offset (timeindex) file. The log file is used to store data change records, and the index file is used to store an index from a specific offset in the log file to the physical location of the data, so as to quickly read data starting from a specific offset in the log file. The timeindex file is used to store an index from a specific timestamp to an offset, so as to quickly retrieve data from the log file based on a time range. The data synchronization platform can write data synchronization records to the log sequence of the message system, thereby storing the data synchronization records on the disk of the message system to achieve long-term storage and data playback, thereby improving the fault tolerance of the message system.

[0083] In the embodiment of the present application, before the data synchronization platform sends the data change record in the target synchronization message to the demand-side database in the above S104, it can also perform the following steps:

[0084] Step 1: Receive virtual table information. The virtual table information includes: the source topic name of the virtual table to be synchronized in the demand-side database and the virtual table's filtering information. The filtering information includes: the preset schema name of the source database to which the data in the virtual table belongs and the preset table information of the source data table.

[0085] Since messages in message systems, such as Kafka, are streaming data stored in a unified format, which mixes data change records from different source data tables in the source database, in order to enable the downstream to obtain the specific data it needs as easily as reading the data tables in an ordinary database, an embodiment of the present application provides a virtual table configuration mechanism.

[0086] A virtual table is essentially a logical data table defined in the data synchronization platform. It is used to map streaming messages without a fixed table structure in the message system into a logical table with a clear table structure.

[0087] Users can configure virtual tables on the data synchronization platform based on the data synchronization requirements of downstream systems. When configuring a virtual table on the platform, you can define the following key information:

[0088] (1) Source type and name: that is, specify the source data source as the message system, and specify the specific topic name where the virtual table is located.

[0089] (2) Virtual table name: Assign a unique name to the logical table to facilitate reference by downstream data synchronization tasks.

[0090] (3) Model definition: This is the core of the virtual table, including:

[0091] (3.1) Filtering conditions: Also known as filtering information, these conditions are used to filter data from a specific table in a topic. Filtering conditions include the schema name and table information of the source database to which the data in the virtual table belongs. For example, if a filtering condition includes both the schema name and the table name, since the data in the virtual table originates from the source database table, the schema name is the source database schema name, and the table name is the source table name.

[0092] (3.2) Field List and Mapping: This defines the field information for the virtual table's fields, such as field names, and the correspondence between each field and the JSON data in the synchronization message. For example, a field name corresponds to a key name in a key-value pair in the JSON data. Optionally, using the field list and mapping, the data synchronization platform can filter the target synchronization messages from the topic and further filter the synchronization messages that include the field information. This filtered synchronization message is then sent to the demand-side database to satisfy the downstream system's data synchronization requirements for the specified fields in the source data table.

[0093] Step 2: Generate a real-time synchronization task using the message system as the source data source and the demand-side database as the target data source.

[0094] When creating a real-time synchronization task, you can also select the data source (Source) as a pre-configured virtual table and the data target (Sink) as the target data table in the demand-side database of the downstream system. By executing the real-time synchronization task, the data in the virtual table can be synchronized to the target data table.

[0095] After starting the real-time synchronization task, the data synchronization platform can accurately consume and process qualified data from a single Topic in Kafka according to the filtering and mapping rules defined in the virtual table, and then write it to the database of the specified downstream system. Since the data change records of each source database are stored in the Topic corresponding to the source database in the message system, when the downstream system needs to obtain data change records, the data synchronization platform can filter out the synchronization messages that meet the requirements from the message system without having to obtain them from the source database. In this way, even if hundreds of downstream systems with different requirements are added, there is no need to make any modifications to the CDC task of the source database. Only new virtual tables and real-time synchronization tasks need to be configured, which greatly reduces the load and operation and maintenance complexity of the source database.

[0096] Step 3: Monitor the message system based on the real-time synchronization task.

[0097] The embodiment of the present application can capture data change records of various data tables in the source database by making a CDC connection to each source database. When the downstream system changes, or the real-time synchronization requirements of the downstream system change, for example, the database or data table to which the data to be synchronized belongs changes, it is only necessary to configure a new real-time synchronization task on the data synchronization platform to obtain synchronization messages that meet the new requirements from the message system without establishing a new CDC connection with the source database. It can be seen that even if the real-time synchronization requirements of the downstream system are changeable, it will not bring heavy CDC connection pressure to the source database, reducing the load pressure of the real-time synchronization requirements on the source database, and also reducing the operation and maintenance and testing costs of the source database. Moreover, due to the high reliability and strong scalability of the message system, the CDC connection has little impact on the load of the message system.

[0098] In some embodiments of the present application, the above-mentioned S104 data synchronization platform monitors the message system based on the real-time synchronization task, which can be implemented as follows: according to the real-time synchronization task, a source synchronization plug-in corresponding to the type of message system is selected, and during the process of running the real-time synchronization task, the data synchronization platform monitors the source topic corresponding to the source topic name through the source synchronization plug-in to see whether there is a target synchronization message including filtering information.

[0099] Because the messaging system uses a streaming data storage structure, once the producer (producer) in a multi-table real-time synchronization task writes a synchronization message to the messaging system, the consumer (consumer) in a real-time synchronization task configured for downstream system real-time synchronization requirements can immediately monitor the synchronization message. Therefore, the messaging system does not need to enable the CDC function and can support the data synchronization platform in normal operation. The data synchronization platform can use a source synchronization plugin that matches the messaging system type to monitor new synchronization messages and filter them based on the data within them. For example, if the messaging system is Kafka, the data synchronization platform can use a Kafka source plugin and the standard Kafka Consumer API to monitor a specified Kafka topic for target synchronization messages that meet the filtering criteria.

[0100] Because each synchronization message in the messaging system's topic contains the identifying schema and table name, as well as a record of data changes, the source synchronization plugin can filter the data in the synchronization messages when monitoring the messaging system to obtain the synchronization messages required for each real-time synchronization requirement. Furthermore, each real-time synchronization requirement corresponds to a real-time synchronization task, and the source synchronization plugin used by each real-time synchronization task can independently manage the consumption progress of synchronization messages, preventing interference between different real-time synchronization tasks and different downstream systems.

[0101] In some embodiments of the present application, the task information received in the above step 1 may also include the type and name of the demand-side database. Accordingly, the target-side type of the real-time synchronization task is the type of the demand-side database, and the target-side name of the real-time synchronization task is the name of the demand-side database. Among them, the target-side type is the type of the target data source, and the target-side name is the name of the target data source. Before the data synchronization platform sends the data change record in the target synchronization message to the demand-side database in the above S104, it can also: select the target synchronization plug-in corresponding to the target-side type. Since the target synchronization plug-in corresponding to the target-side type is configured in the real-time synchronization task, after the data synchronization platform starts the real-time synchronization task, it can select the target synchronization plug-in corresponding to the target-side type according to the real-time synchronization task.

[0102] For example, the types of demand-side databases include: MySQL, PostgreSQL, MongoDB, Elasticsearch, and ClickHouse, and each type can correspond to a different downstream (sink) plug-in. The real-time synchronization task of the data synchronization platform is configured with the sink plug-in corresponding to the demand-side database type, so the sink plug-in in the real-time synchronization task can be used as the target synchronization plug-in.

[0103] On this basis, the data synchronization platform in S104 sends the data change record in the target synchronization message to the demand-side database by using the target synchronization plug-in to send the data change record in the target synchronization message to the demand-side database corresponding to the target-side name. Because the sink plug-in corresponding to the demand-side database type is configured in the real-time synchronization task, the data synchronization platform can use this sink plug-in to send the data change record in the target synchronization message to the demand-side database corresponding to the target-side name during the execution of the real-time synchronization task.

[0104] The embodiment of the present application can connect to different demand-side databases through target synchronization plug-ins corresponding to different demand-side database types, thereby building multiple real-time data transmission pipelines and transmitting synchronization messages to various heterogeneous downstream systems.

[0105] See also Figure 2 The following describes the overall process of the data synchronization method provided in the embodiment of the present application:

[0106] Assume that the source databases include: Database A, Database B, and Database C, where Database A is MySQL, Database B is Oracle, and Database C is DAMENG. The data synchronization platform is SeaTunnel, and the messaging system is Kafka. The demand side includes: Downstream System A, Downstream System B, and Downstream System C. The database of Downstream System A is TDSQL, the database of Downstream System B is MongoDB, and the database of Downstream System C is Elasticsearch.

[0107] SeaTunnel generates multi-table real-time synchronization task A for database A, multi-table real-time synchronization task B for database B, and multi-table real-time synchronization task C for database C. For each source database, SeaTunnel executes the corresponding multi-table real-time synchronization task. Specifically, when running each multi-table real-time synchronization task, SeaTunnel selects the source plugin corresponding to the source database type and the sink plugin corresponding to Kafka. The source plugin captures the source database's data change records and obtains the target database's metadata. It converts the data change records and metadata into Debezium JSON format, generates synchronization messages, and uses the corresponding Kafka sink plugin to write the synchronization messages to the Kafka topic corresponding to the source database. In Kafka, Topic A corresponds to Database A, Topic B to Database B, and Topic C to Database C.

[0108] SeaTunnel generates a real-time synchronization task for each real-time synchronization requirement of the downstream system. Each real-time synchronization task corresponds to a virtual table in a topic. For example, virtual table A1 corresponds to real-time synchronization task A1, virtual table A2 corresponds to real-time synchronization task A2, and so on. When SeaTunnel runs each real-time synchronization task, it selects the source plug-in corresponding to Kafka and the sink plug-in corresponding to the database type of the downstream system of the real-time synchronization task. The source plug-in monitors the specified Kafka topic for target synchronization messages that include the filter information defined in the virtual table. When a new target synchronization message is detected in the specified Kafka topic, it obtains the data change record in the target synchronization message. Then, the sink plug-in corresponding to the database type of the downstream system sends the data change record in the target synchronization message to the downstream system database.

[0109] The embodiments of the present application provide an efficient, flexible, and scalable real-time data synchronization method that can quickly extract data change records from the source database and widely distribute them to multiple downstream systems to support decision-making timeliness in seconds to minutes.

[0110] First, the embodiment of the present application writes the data change records of each source database into the same Topic of the message system. Then, based on the real-time synchronization requirements of the downstream system, the messages are filtered from the Topic and distributed to the downstream system. This approach avoids the need to establish a real-time synchronization task to obtain data change records from the source database for each real-time synchronization requirement, or to establish a Topic for each real-time synchronization requirement. This not only reduces the number of CDC connections established by the source database, lowering the load on the source database, but also simplifies the management and monitoring of the message system, reducing the complexity of operation and maintenance.

[0111] Secondly, virtual tables provide a more flexible data distribution method. The data synchronization platform can more flexibly filter synchronization messages based on data table information and field information according to the real-time synchronization requirements of downstream systems, without the need for additional configuration or modification of the source database. This not only improves the overall flexibility and scalability of the data synchronization system, but also ensures the independence and stability of the business required to be executed by the downstream system.

[0112] Thirdly, the data synchronization platform enables unified management and monitoring of all real-time synchronization tasks. It also has visual task configuration capabilities and plug-in scalability, enabling the data synchronization platform to quickly adapt to various source databases and various heterogeneous downstream systems, improving the overall performance and reliability of the data synchronization system.

[0113] Finally, the embodiment of the present application can achieve the distribution of data read from the source database to multiple downstream systems through the "read once, consume multiple times" model. This model not only reduces the connection pressure of the source database, but also ensures that the downstream system can flexibly obtain data on demand, and ensures the consistency, timeliness and reliability of data synchronization, so that each downstream system can make real-time decisions and processing based on the latest data, thereby adapting to various business scenarios and technical environments.

[0114] The following uses an example to illustrate the overall process of the data synchronization method provided in the embodiment of the application:

[0115] Suppose a brokerage firm's internal downstream systems need to collect data from multiple tables related to information management in the information system's information database. Based on the type of information database, SeaTunnel configures the corresponding source plugin in the multi-table real-time synchronization task. This source plugin establishes a CDC connection with the information database. It also selects the corresponding Kafka sink plugin, which connects to Kafka using the Kafka address. A topic corresponding to the information database is created in Kafka, named "info-db-topic." SeaTunnel uses the source plugin to monitor the source database. When it detects a change in any data point in the source database, it retrieves the data change record for the target data and its metadata. It converts the data change record and metadata into Debezium JSON format, generates a synchronization message, and writes the synchronization message to the Kafka topic named "info-db-topic" via the corresponding Kafka sink plugin.

[0116] Assume that a data change record indicates the addition of a new piece of data in MySQL. The structure of the synchronization message in DebeziumJSON format generated by SeaTunnel is as follows:

[0117]

[0118]

[0119] Before represents the data before the change, and after represents the data after the change. If before is null and after is not null, the data did not exist before the change but exists after the change, indicating that the data change operation is an add operation. If before is not null and after is null, it indicates a delete operation. If before is not null and after is not null, it indicates an update operation. The before and after fields help downstream systems perform rich logical judgments in various application scenarios, such as data auditing, data backtracking, and data comparison.

[0120] source indicates the source information of the change event. op is c, which indicates an INSERT operation for adding data. op is u, which indicates an UPDATE operation for updating data. op is d, which indicates a DELETE operation for deleting data.

[0121] Furthermore, when executing real-time synchronization tasks, SeaTunnel selects the corresponding Kafka source plug-in and, based on the database type of the brokerage's downstream system, the corresponding sink plug-in. The source plug-in monitors the Kafka topic named "info-db-topic" to see if there are target synchronization messages with db = news_schema and table = article_info. When a new target synchronization message is detected, the sink plug-in sends the data change record in the target synchronization message to the brokerage's downstream system database.

[0122] Through the above method, the embodiment of the present application can provide an end-to-end real-time data pipeline of "multi-source CDC + single-topic centralized management + diversified downstream", realizing one-time reading of data change records of the source database and multiple real-time distribution.

[0123] Based on the same inventive concept, corresponding to the above method embodiment, the embodiment of the present application also provides a data synchronization system, such as Figure 3 As shown, the system includes: a source database 301, a data synchronization platform 302, a message system 303 and a demand-side database 304;

[0124] Source database 301, used to store data;

[0125] A data synchronization platform 302 is used to execute the method steps in the above method embodiment;

[0126] Message system 303, used to store synchronization messages from data synchronization platform 302;

[0127] The demand-side database 304 is used to receive the data change record in the target synchronization message sent by the data synchronization platform 302.

[0128] Optional, Figure 3 The source database 301, data synchronization platform 302, message system 303 and demand-side database 304 shown are each a software module. Each software module can be deployed in different hardware devices or in the same hardware device. The embodiment of the present application does not make specific limitations on this.

[0129] Based on the same inventive concept, corresponding to the above method embodiment, the embodiment of the present application also provides a data synchronization device, such as Figure 4 As shown, the device includes: an acquisition module 401, a generation module 402 and a sending module 403;

[0130] The acquisition module 401 is used to acquire the data change record of the target data when a change is detected in the data in the source database;

[0131] The acquisition module 401 is further used to acquire metadata of the target data, including: the schema name of the database to which it belongs and the table information of the source data table to which it belongs;

[0132] A generation module 402 is used to generate a synchronization message based on the data change record and metadata, and write the synchronization message to a topic corresponding to the source database in the message system;

[0133] The sending module 403 is used to send the data change record in the target synchronization message to the demand-side database when a new target synchronization message is detected in the topic of the message system. The target synchronization message is a synchronization message including a preset mode name and preset table information.

[0134] Optionally, the device further includes:

[0135] The receiving module is used to receive virtual table information before sending the data change record in the target synchronization message to the demand-side database when a new target synchronization message is detected in the topic of the message system. The virtual table information includes: the source topic name of the virtual table to be synchronized in the demand-side database and the filtering information of the virtual table. The filtering information includes: the preset mode name of the source database to which the data in the virtual table belongs and the preset table information of the source data table to which it belongs;

[0136] Generate module 402, using the message system as the source data source and the demand-side database as the target data source, to generate a real-time synchronization task;

[0137] The monitoring module is used to monitor the message system based on real-time synchronization tasks.

[0138] Optionally, the monitoring module is specifically used to: select a source synchronization plug-in corresponding to the type of message system according to the real-time synchronization task; and monitor the source topic corresponding to the source topic name through the source synchronization plug-in to see whether there is a target synchronization message including filtering information.

[0139] Optionally, the task information further includes the type and name of the demand-side database, the target-side type of the real-time synchronization task is the type of the demand-side database, and the target-side name of the real-time synchronization task is the name of the demand-side database; the device may further include:

[0140] The startup module is used to select the target synchronization plug-in corresponding to the target end type before sending the data change record in the target synchronization message to the demand-side database;

[0141] The sending module 403 is specifically used to send the data change record in the target synchronization message to the demand-side database corresponding to the target-side name through the target synchronization plug-in.

[0142] The present application also provides an electronic device, such as Figure 5 As shown, it includes a processor 501 , a communication interface 502 , a memory 503 and a communication bus 504 , wherein the processor 501 , the communication interface 502 and the memory 503 communicate with each other via the communication bus 504 .

[0143] Memory 503, used for storing computer programs;

[0144] The processor 501 is configured to implement the steps performed by the data synchronization platform in the above method embodiment when executing the program stored in the memory 503 .

[0145] The communication bus mentioned in the electronic device mentioned above may be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus. This communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of illustration, only one thick line is used in the figure, but this does not mean that there is only one bus or only one type of bus.

[0146] The communication interface is used for communication between the above electronic device and other devices.

[0147] The memory may include random access memory (RAM) or non-volatile memory (NVM), such as at least one disk memory. Alternatively, the memory may be at least one storage device located away from the processor.

[0148] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0149] In another embodiment provided by the present application, a computer-readable storage medium is further provided, in which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned data synchronization methods are implemented.

[0150] In another embodiment provided by the present application, a computer program product including instructions is also provided, which, when executed on a computer, enables the computer to execute any one of the data synchronization methods in the above embodiments.

[0151] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When software is used for implementation, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions can be transmitted from a website, computer, server or data center to another website, computer, server or data center via a wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) method. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrations. The available medium can be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state drive (SSD)).

[0152] It should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply the existence of any such actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of other identical elements in the process, method, article, or device comprising the element.

[0153] Each embodiment in this specification is described in a related manner. Similar portions between the embodiments can be referenced to each other. Each embodiment focuses on the differences between the other embodiments. In particular, the system and device embodiments are generally similar to the method embodiments, so their descriptions are relatively simple. For related portions, refer to the descriptions of the method embodiments.

[0154] The above description is only a preferred embodiment of the present application and is not intended to limit the scope of protection of the present application. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application are included in the scope of protection of the present application.

Claims

1. A data synchronization method, characterized in that: The method comprises: When a change is detected in the data in the source database, the data change record of the target data that has changed is obtained; Obtain metadata of the target data, the metadata including: a schema name of the database to which it belongs and table information of the source data table to which it belongs; Generate a synchronization message based on the data change record and the metadata, and write the synchronization message into a topic corresponding to the source database in a message system; When a new target synchronization message is detected in the topic of the message system, a data change record in the target synchronization message is sent to the demand-side database. The target synchronization message is a synchronization message including a preset mode name and preset table information.

2. The method according to claim 1, characterized in that When a newly added target synchronization message is detected in the topic of the message system, before sending the data change record in the target synchronization message to the demand-side database, the method further includes: Receive virtual table information, the virtual table information including: a source topic name of the virtual table to be synchronized with the demand-side database and filtering information of the virtual table, the filtering information including: a preset schema name of a source database to which data in the virtual table belongs and preset table information of a source data table; Generate a real-time synchronization task using the message system as the source data source and the demand-side database as the target data source; According to the real-time synchronization task, the message system is monitored.

3. The method according to claim 2, characterized in that The step of monitoring the message system according to the real-time synchronization task includes: According to the real-time synchronization task, select a source synchronization plug-in corresponding to the type of the message system; The source synchronization plug-in monitors, through the source synchronization plug-in, whether there is a target synchronization message including the filtering information in the source topic corresponding to the source topic name.

4. The method according to claim 2 or 3, characterized in that The task information also includes the type and name of the demand-side database, the target-side type of the real-time synchronization task is the type of the demand-side database, and the target-side name of the real-time synchronization task is the name of the demand-side database; Before sending the data change record in the target synchronization message to the demand-side database, the method further includes: Select the target synchronization plug-in corresponding to the target end type; The sending of the data change record in the target synchronization message to the demand-side database includes: The data change record in the target synchronization message is sent to the demand-side database corresponding to the target-side name through the target synchronization plug-in.

5. A data synchronization device, characterized in that: The device comprises: An acquisition module is used to acquire data change records of target data when a change is detected in the source database; The acquisition module is further configured to acquire metadata of the target data, wherein the metadata includes: a schema name of the database to which it belongs and table information of the source data table to which it belongs; A generation module, configured to generate a synchronization message based on the data change record and the metadata, and write the synchronization message into a topic corresponding to the source database in a message system; The sending module is used to send the data change record in the target synchronization message to the demand-side database when it monitors that there is a new target synchronization message in the topic of the message system. The target synchronization message is a synchronization message including a preset mode name and preset table information.

6. The device according to claim 5, characterized in that Also includes: A receiving module, configured to receive virtual table information before sending a data change record in the target synchronization message to the demand-side database when a newly added target synchronization message is detected in the topic of the message system, wherein the virtual table information includes: a source topic name of the virtual table to be synchronized in the demand-side database and filtering information of the virtual table, wherein the filtering information includes: a preset schema name of the source database to which the data in the virtual table belongs and preset table information of the source data table to which it belongs; The generation module generates a real-time synchronization task using the message system as a source data source and the demand-side database as a target data source; A monitoring module is used to monitor the message system according to the real-time synchronization task.

7. The device according to claim 6, characterized in that The monitoring module is specifically configured to: select a source synchronization plug-in corresponding to the type of the message system according to the real-time synchronization task; and monitor, through the source synchronization plug-in, whether a target synchronization message including the filtering information exists in the source topic corresponding to the source topic name; And / or, the task information also includes the type and name of the demand-side database, the target-side type of the real-time synchronization task is the type of the demand-side database, and the target-side name of the real-time synchronization task is the name of the demand-side database; the device also includes: a startup module, which is used to select the target synchronization plug-in corresponding to the target-side type before sending the data change record in the target synchronization message to the demand-side database; the sending module is specifically used to: send the data change record in the target synchronization message to the demand-side database corresponding to the target-side name through the target synchronization plug-in.

8. A data synchronization system, characterized in that: The system comprises: Source database, used to store data; A data synchronization platform for executing the method according to any one of claims 1 to 4; A message system, used to store synchronization messages from the data synchronization platform; The demand-side database is used to receive the data change record in the target synchronization message sent by the data synchronization platform.

9. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus; Memory for storing computer programs; A processor, configured to implement the method according to any one of claims 1 to 4 when executing a program stored in a memory.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 4 is implemented.

Images