Data synchronization method and device, storage medium and electronic equipment

Abstract

The invention discloses a data synchronization method and device, a storage medium and electronic equipment, and the method comprises the steps: obtaining initial data in a plurality of source data tables; determining a change field based on the change information of the plurality of source data tables; packaging the initial data and the change field to obtain target data; shunting the target data to obtain multiple pieces of shunting data; and synchronizing the multiple pieces of shunt data to a target data table corresponding to the shunt data based on a mapping relationship, the mapping relationship being an association relationship between the source data table and the target data table, and the target data table being a table to which the initial data is to be synchronized. Through the data synchronization method and device, the problem of low data synchronization efficiency is solved, and the effect of improving the data synchronization efficiency is achieved.

Description

Technical Field

[0001] This application relates to the field of computers, and more specifically, to a data synchronization method, apparatus, storage medium, and electronic device. Background Technology

[0002] In related technologies, it is usually necessary to create and run independent data synchronization tasks or components for each data source. The repeated configuration and use of resources leads to low data synchronization efficiency.

[0003] This indicates that the relevant technologies suffer from low data synchronization efficiency.

[0004] There is currently no effective solution to the aforementioned problems in the relevant technologies. Summary of the Invention

[0005] This application provides a data synchronization method, apparatus, storage medium, and electronic device to at least solve the technical problem of low data synchronization efficiency in related technologies.

[0006] According to one aspect of the embodiments of this application, a data synchronization method is provided, comprising: acquiring initial data from multiple source data tables; determining changed fields based on change information of the multiple source data tables; packaging the initial data and the changed fields to obtain target data; splitting the target data to obtain multiple split data; and synchronizing the multiple split data to the target data table corresponding to the split data based on a mapping relationship, wherein the mapping relationship is the association relationship between the source data table and the target data table, and the target data table is the table to which the initial data is to be synchronized.

[0007] In one exemplary embodiment, determining a change field based on change information of multiple source data tables includes: determining a change operation based on change information of multiple source data tables; and determining the change field based on the change operation.

[0008] In an exemplary embodiment, determining the change field based on the change operation includes: when the change operation is an insertion of first data, setting the version field included in the change field of the first data to an initial value and setting the status field included in the change field of the first data to a first value, wherein the version field is used to indicate the number of times the data has been updated and the status field is used to indicate the status of the data; when the change operation is a deletion of second data, maintaining the version field included in the change field of the second data unchanged and setting the status field included in the change field of the second data to a second value; when the change operation is an update of third data, setting the version field included in the change field of the third data to a target value, wherein the target value is the value of the current version field of the third data plus a target constant and setting the status field included in the change field of the third data to the first value.

[0009] In an exemplary embodiment, after synchronizing multiple stream data to the target data table corresponding to the stream data based on the mapping relationship, the method further includes: determining whether the target data table contains a unique key; if the target data table contains the unique key, using the unique key to deduplicate the stream data; if the target data table does not contain the unique key, deduplicate the stream data based on a target function.

[0010] In one exemplary embodiment, splitting the target data to obtain multiple split data includes: tagging the target data based on the source data table to obtain tagged data; and splitting the tagged data to obtain multiple split data.

[0011] In one exemplary embodiment, tagging the target data based on a source data table to obtain tagged data includes: determining a first identifier of the source data table and a second identifier of a source database containing the source data table; determining a combined identifier based on the first identifier and the second identifier; and adding the combined identifier to a target position in the target data to obtain the tagged data.

[0012] In one exemplary embodiment, the marking data is split to obtain multiple split data, including: determining sub-marking data belonging to the same source data table in the marking data based on the combined identifier included in the marking data; determining multiple sub-marking data as multiple split data, wherein one sub-marking data corresponds to one split data, and the split data corresponding to each sub-marking data is different.

[0013] According to another aspect of the embodiments of this application, a data synchronization apparatus is also provided, comprising: an acquisition module for acquiring initial data from multiple source data tables; a determination module for determining changed fields based on change information of the multiple source data tables; a packaging module for packaging the initial data and the changed fields to obtain target data; a splitting module for splitting the target data to obtain multiple split data; and a synchronization module for synchronizing the multiple split data to the target data table corresponding to the split data based on a mapping relationship, wherein the mapping relationship is the association relationship between the source data table and the target data table, and the target data table is the table to which the initial data is to be synchronized.

[0014] According to another aspect of the embodiments of this application, a computer-readable storage medium is also provided, wherein a computer program is stored therein, wherein the computer program is configured to perform the steps in any of the above method embodiments when executed by a processor.

[0015] According to another aspect of the embodiments of this application, a computer program product or computer program is provided, the computer program product or computer program including computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium, and the processor executes the computer instructions, causing the computer device to perform the steps in any of the method embodiments described above.

[0016] According to another aspect of the embodiments of this application, an electronic device is also provided, including a memory and a processor, wherein the memory stores a computer program, and the processor is configured to perform the steps of any of the above method embodiments through the computer program.

[0017] This application allows for the acquisition of initial data from multiple source tables within multiple source databases. By analyzing the table update information of the source tables, the modified fields can be identified. The acquired initial data and modified fields are then packaged into target data. By splitting the target data from multiple source tables, multiple split data streams can be obtained. These split data streams can then be synchronized to their respective target tables using the mapping relationship between the source tables and the target tables to which the initial data is to be synchronized. Since the splitting technology allows target data to be output to different streams (i.e., split data), and by modifying the fields, all modification operations on the data tables can be transformed into insert operations, requiring only the implementation of insert operation code, the problem of low data synchronization efficiency can be solved, thus improving data synchronization efficiency. Attached Figure Description

[0018] Figure 1This is a schematic diagram illustrating an application scenario of a data synchronization method according to an embodiment of this application;

[0019] Figure 2 This is a flowchart illustrating an optional data synchronization method according to an embodiment of this application;

[0020] Figure 3 This is a schematic diagram of the overall architecture according to an embodiment of this application;

[0021] Figure 4 This is a flowchart illustrating the data synchronization method in this optional example;

[0022] Figure 5 This is a structural block diagram of an optional data synchronization device according to an embodiment of this application;

[0023] Figure 6 This is a computer system architecture block diagram of an optional electronic device according to an embodiment of this application. Detailed Implementation

[0024] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0025] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0026] The technical terms appearing in this application are explained below:

[0027] Snapshot: MVCC (Multi-Version Concurrency Control) can provide a snapshot of the data for each transaction, ensuring that read operations are always based on the data state at a certain point in time.

[0028] Timestamp: Each transaction is assigned a unique timestamp when it begins execution. The system can use this timestamp to determine the version of the transaction.

[0029] Version Chain: Whenever data is modified, the system creates a new version of the data while retaining the old version. These versions can form a chain to ensure that the appropriate data version can be found based on the timestamp.

[0030] Stream processing: Techniques for processing data streams (such as real-time events), as opposed to batch processing.

[0031] Data synchronization is the process of copying data from the source system to the target system in real time, ensuring data consistency and timeliness.

[0032] Flink: An open-source stream processing framework that supports both stateful and stateless stream processing, providing high-throughput, low-latency real-time computing capabilities.

[0033] Watermark: A tool used to process event timing, helping the system identify the sequence of events in order to correctly handle out-of-order events.

[0034] Windowing: A method of dividing an infinite data stream into finite data blocks for processing. Windows can be divided according to time, count, etc.

[0035] State Management: A feature provided by Flink that manages the state generated during stream processing to maintain data consistency and business continuity during processing.

[0036] Flink Connector: A component used to connect Flink with external systems (such as Kafka, HDFS, Elasticsearch, etc.), supporting data reading and writing.

[0037] Fault-tolerant mechanism: Flink provides a recovery mechanism that ensures that the system can recover to a safe state in the event of a failure, thus ensuring the consistency and reliability of data processing.

[0038] (1) Sharding / Partitioning: Sharding / Partitioning refers to dividing a data stream into multiple parts so that it can be processed in parallel on multiple computing nodes. Sharding can improve processing performance and reduce the load on a single instance.

[0039] Data stream partitioning: Flink divides the input data stream into multiple independent sub-streams, called partitions. Each partition can be processed independently, allowing each node to process data from multiple partitions simultaneously.

[0040] Parallelism: Flink allows you to set the parallelism of operations. Each operation can specify the parallelism, meaning it can run simultaneously on multiple threads or multiple Task Managers. This flexible parallelism setting allows users to adjust resource utilization according to specific needs.

[0041] State Management: Flink supports stateful stream processing, which allows the computation state to be preserved during splitting. Even in the case of splitting and parallel processing, the system can still maintain data consistency and fault tolerance.

[0042] (2) Real-time data synchronization technology: Real-time data synchronization aims to ensure that data is updated quickly and timely between different databases or systems so that the latest data can always be obtained when users query or business needs occur.

[0043] Change Data Capture (CDC): CDC can be understood as a common real-time data synchronization technology. It mainly captures data addition, deletion, and modification operations by listening to the database change log. Through CDC, the system can obtain data change information in a timely manner and synchronize it.

[0044] Message queues: Message queues (such as Apache Kafka and RabbitMQ) can be used to achieve event-driven data synchronization. Data changes are sent to the message queue as messages, and other systems can subscribe to the corresponding messages to update the data.

[0045] Stream processing engines: Stream processing frameworks such as Apache Flink, Apache Storm and Apache Spark Streaming can process data streams in real time and can immediately perform operations and synchronization when the data source changes.

[0046] (3) Multi-Version Concurrency Control (MVCC): Multi-Version Concurrency Control (MVCC) can be understood as a database concurrency control mechanism that allows multiple versions of the same data object to coexist. This allows read operations to be performed without locking, avoiding blocking and thus improving the system's concurrency performance.

[0047] Reading data: When a transaction needs to read data, it can access the most recent snapshot, which contains all changes committed before it began; if the timestamp of the read transaction is less than the timestamp of the write transaction, the read operation will return an older version of the data.

[0048] Writing data: When a transaction needs to write data, it can generate a new version and add it to the version chain; during the writing process, other transactions can still read the old version of the data, but the writing transaction needs to wait for other concurrent transactions to complete.

[0049] Commit and Rollback: If a transaction is successfully committed, the database will mark the new data version as visible; if a rollback occurs, the changes will not take effect, and the relevant version can be reclaimed or marked as invalid.

[0050] According to one aspect of the embodiments of this application, a data synchronization method is provided. Optionally, in this embodiment, the above-described data synchronization method may be applied to, but is not limited to, [examples of data synchronization methods]. Figure 1 The hardware environment shown includes terminal device 102 and server 104. Server 104 can be connected to terminal device 102 via a network and can be used to provide services (e.g., application services, etc.) to terminal device 102 or clients installed on terminal device 102. A database can be set up on server 104 or independently of server 104 to provide data storage services for server 104.

[0051] The aforementioned network may include, but is not limited to, at least one of the following: wired network and wireless network. The aforementioned wired network may include, but is not limited to, at least one of the following: wide area network (WAN), metropolitan area network (MAN), and local area network (LAN). The aforementioned wireless network may include, but is not limited to, at least one of the following: Wireless Fidelity (WIFI) and Bluetooth. Terminal device 102 may be, but is not limited to, a personal computer (PC), mobile phone, tablet computer, etc. Server 104 may be, but is not limited to, a cloud server, server cluster, or other server types.

[0052] The data synchronization method of this application embodiment can be executed by server 104, terminal device 102, or jointly by server 104 and terminal device 102. Alternatively, the data synchronization method of this application embodiment can be executed by a client installed on the terminal device 102.

[0053] Figure 2 This is a flowchart illustrating an optional data synchronization method according to an embodiment of this application, such as... Figure 2 As shown, the process of this method may include the following steps:

[0054] Step S202: Obtain initial data from multiple source data tables;

[0055] The data synchronization method in this embodiment can be applied to big data development, data synchronization, database connections, and Apache. This involves technologies such as Flink (an open-source stream processing framework) and containerization, encompassing Java (an object-oriented programming language and platform), Flink, and Kubernetes (an open-source container orchestration system). Specific applications include real-time monitoring, user behavior analysis, log processing, data warehouse updates, and social media data processing. Real-time monitoring, in industries like finance and IoT, requires monitoring and analyzing real-time data. Real-time data synchronization allows sensor data and transaction records to be pushed to a monitoring platform in real time, enabling timely detection of anomalies. User behavior analysis allows e-commerce platforms to use Flink to synchronize user clicks, purchases, and other behavioral data to an analysis system in real time, helping to build user profiles and analyze behavior to optimize recommendation algorithms. Log processing involves synchronizing application-generated log data to log analysis tools in real time for big data analysis and anomaly monitoring. Data warehouse updates ensure consistency between data and business systems, and real-time data synchronization via Flink guarantees timely updates. Social media data processing allows for the rapid processing of real-time data streams from social media, enabling analysis of user sentiment, trending topics, and more.

[0056] In the above embodiments, a single source component in the related technologies can only process a single database or table. When processing multiple databases and tables, this increases resource usage and reduces overall resource utilization, resulting in wasted resources. Furthermore, data synchronization requires handling various types of data operations, including insertion, deletion, and update. This diversity of data operations makes the code structure more complex, increases development and maintenance difficulty, and reduces synchronization efficiency. To address the problems in the related technologies, this application constructs a Flink real-time data synchronization method using a stream splitting technique. This method is applicable to various scenarios requiring fast and accurate data processing, whether it's synchronization between data lakes and data warehouses, or real-time processing of IoT data, online transaction monitoring, and social media analysis. Flink's powerful stream processing capabilities provide efficient data synchronization solutions, demonstrating Flink's enormous potential in implementing data processing and analysis, and meeting the ever-growing demand for real-time data.

[0057] In the above embodiments, the process of synchronizing data from the database to the target system can begin with preparing the data source and the target system. Figure 3This is a schematic diagram of the overall architecture according to an embodiment of this application, as shown below. Figure 3 As shown, data tables from multiple business databases (e.g., MySQL, PostgreSQL, and Oracle) can be synchronized to corresponding data tables in the target system's Hive, ClickHouse, or Byconity databases. This means that based on the data source information configured in the source configuration, a connection to the data source is established via the Flink JDBC (Java Database Connectivity) connector provided by Apache Flink. Data can then be read from various relational source databases, including MySQL, PostgreSQL, and Oracle. Since the source databases contain multiple tables, each with its specific structure (e.g., column names, data types) and relationships (e.g., primary keys, foreign keys), and these source tables are the fundamental objects of data manipulation—all query, insert, update, and delete operations are performed on these tables—the initial data to be synchronized can be directly obtained from the source tables using Debezium. Since the data output by Debezium is in JSON format, it needs to be converted into the RowData type that Flink can process. Therefore, the TableRowDataDebeziumDeserializeSchema class can be used to deserialize the JSON data received from Debezium into the RowData data type that Flink SQL can support, so that it can be used in Flink stream processing jobs.

[0058] Step S204: Determine the changed fields based on the change information of the multiple source data tables;

[0059] In the above embodiments, before obtaining the initial data included in the source data table of the business data (i.e., the aforementioned source database), the data source connection information configuration and the target system connection information configuration (i.e., ...) can be performed first. Figure 3(Source and sink preparation in Apache Flink). During the data source connection configuration process, binary logging can be enabled in the source component for most databases. This allows the database to record all table change events (i.e., the table change information mentioned above). The changed fields corresponding to each different change event can be identified through these change events. Here, "source" can be understood as the source data, the starting point of the data flow, the place where data is generated or acquired. In Apache Flink, the source component can read data from multiple tables across multiple databases. "Sink" can be understood as the destination of the data flow, the output point after data processing. In Apache Flink, the sink component can output the processed data to a specified system or service. A dedicated CDC (Change Data Capture) user can also be created in the source component, possessing sufficient permissions to read table change information for each source table without interfering with the normal operation and data integrity of the source database. Furthermore, within a source component of the Flink cluster, a CDC connector Debezium tool can be provided for each source database. In Debezium, appropriate parameters are set according to the specific characteristics of each source table, such as table name, schema, and connection information. This ensures that the required tables and data are captured. The Debezium tool can then obtain the initial data and send it to the Flink cluster as a data stream. During the configuration of the target system connection information, the database type should be clearly defined first, such as Hive, ClickHouse, or Byconity. Since different databases have different configuration parameters, the sink component in the Flink cluster needs to be configured with different database connection relationships, such as database address, account, password, and port.

[0060] Step S206: Package the initial data and the changed fields to obtain the target data;

[0061] In the above embodiments, after connecting to the source database, the `fetchSize` and `splitSize` parameters can be configured during full synchronization. The `fetchSize` parameter controls the number of records retrieved at once by the Flink data source, while the `splitSize` parameter controls how the data source divides the data reading task into multiple parallel subtasks. After obtaining the initial data from the source data table through `DataStreamSource` and the CDC connector, the CDC connector can convert the change information (usually obtained in Debezium's SourceRecord format) from the source data table into JSON (JavaScript Object Notation) format and add corresponding change fields determined based on the change information. These change fields can include `_version` and `_delete`. `_version` identifies the version number of each record, which is particularly useful when handling update operations, ensuring that the records in the target table are the latest version. `_delete` indicates whether a record has been deleted. Finally, the obtained, format-converted initial data and the corresponding change fields can be packaged into target data and synchronized.

[0062] Step S208: Divide the target data into multiple streams to obtain multiple streams of data;

[0063] In the above embodiments, after obtaining the target data, since the target data contains data from multiple source databases and multiple source data tables, the target data can be split according to the identifiers of the source databases, the identifiers of the source data tables, and the mapping relationship between the database tables. This can result in split data with the same number of source data tables. Each split data item corresponds to data from a different source data table.

[0064] Step S210: Based on the mapping relationship, synchronize multiple split data to the target data table corresponding to the split data. The mapping relationship is the association between the source data table and the target data table. The target data table is the table to which the initial data is to be synchronized.

[0065] In the above embodiments, since data synchronization involves moving data from one table (i.e., the source table) to another table (i.e., the target table), and the source and target systems have inconsistent data formats, and the field names and data types of the source and target tables do not perfectly match, mapping relationships need to be configured: a mapping relationship between the source database and the target system database, and a mapping relationship between the source table and the target table. Through these mapping relationships, target data can be converted into the target table format during data synchronization. Since each stream of data corresponds to a source table, the target table corresponding to the streamed data can also be determined based on the mapping relationships, thus allowing different streams of data to be synchronized to their corresponding target tables.

[0066] This application allows for the acquisition of initial data from multiple source tables within multiple source databases. By analyzing the table update information of the source tables, the modified fields can be identified. The acquired initial data and modified fields are then packaged into target data. By splitting the target data from multiple source tables, multiple split data streams can be obtained. These split data streams can then be synchronized to their respective target tables using the mapping relationship between the source tables and the target tables to which the initial data is to be synchronized. Since the splitting technology allows target data to be output to different streams (i.e., split data), and by modifying the fields, all modification operations on the data tables can be transformed into insert operations, requiring only the implementation of insert operation code, the problem of low data synchronization efficiency can be solved, thus improving data synchronization efficiency.

[0067] Optionally, the entity performing the above steps may be a terminal, a server, or other devices with similar processing capabilities, but is not limited to these.

[0068] In one exemplary embodiment, determining a change field based on change information of multiple source data tables includes: determining a change operation based on change information of multiple source data tables; and determining the change field based on the change operation.

[0069] In the above embodiment, since binary logging is enabled during the data source connection information configuration process, binary logging can record change information for all source data tables. Therefore, the type of each event (i.e., change operation) can be parsed through the change information. Change operations can include insert, update, and delete operations. After determining different change operations, the corresponding change fields can be determined for different types of change operations. For example, for update operations, only the fields whose values ​​have changed need to be considered, without processing all fields.

[0070] In the above embodiments, data can be transformed during the data synchronization process, such as type conversion, format transformation, enumeration mapping, or null value handling. Type conversion can be understood as the handling rules when the data types of the source field and the target field are inconsistent; format transformation can be understood as the handling rules when the formats of the source field and the target field are inconsistent, for example, converting the date format from YYYY-MM-DD to DD / MM / YYYY; enumeration mapping can be understood as defining how to perform the conversion in the mapping if the value of a certain field uses an enumeration; null value handling can be understood as filling a field with a specific value or setting it to null if the field value is empty.

[0071] Through this embodiment, by identifying and parsing change events from multiple source data tables, different types of operations such as insertion, update, and deletion can be distinguished, and the specific fields of change can be accurately located accordingly. Since the system only receives and processes the actual changed parts, rather than the complete records, it can not only reduce the volume of data transmission and improve the synchronization speed, but also reduce the storage pressure on the system.

[0072] In an exemplary embodiment, determining the change field based on the change operation includes: when the change operation is an insertion of first data, setting the version field included in the change field of the first data to an initial value and setting the status field included in the change field of the first data to a first value, wherein the version field is used to indicate the number of times the data has been updated and the status field is used to indicate the status of the data; when the change operation is a deletion of second data, maintaining the version field included in the change field of the second data unchanged and setting the status field included in the change field of the second data to a second value; when the change operation is an update of third data, setting the version field included in the change field of the third data to a target value, wherein the target value is the value of the current version field of the third data plus a target constant and setting the status field included in the change field of the third data to the first value.

[0073] In the above embodiment, the modified fields may include _version (version field) and _delete (status field). The newly added built-in fields _version and _delete can turn all operations on the data table into Insert operations. The Writer only needs to implement the PrepareStatement of Insert single SQL to improve synchronization efficiency.

[0074] In the above embodiments, when new data is inserted into the source data table, that is, when the change operation is the insertion of the first data operation, a new record can be created and inserted into the target data table. In the new record, the _version of the first data can be set to an initial value, such as 1, which can represent the first version of the data. The _delete can also be set to a first value of 0 or false, that is, the _version field is the initial value and the _delete field is 0 or false, which can represent the insertion operation.

[0075] In the above embodiments, when a piece of data in the source data table is deleted, that is, when the change operation is a deletion operation of the second data, the second data is not actually deleted from the target data table. Instead, the fields of the second data are updated, keeping the _version field unchanged and setting the _delete field to the second value 1 (deletion logic). That is, the _version field remains unchanged, and the _delete field being 1 can indicate a deletion operation. This can preserve the historical version of the data, and even in the case of logical deletion, the results can be filtered and displayed through the _delete field, so as not to affect subsequent data analysis or auditing needs.

[0076] In the above embodiment, when a piece of data in the source data table is updated, that is, when the change operation is an update operation of the third data, the third data in the target data table also needs to be updated. The _version field in the target data table can be incremented by a target constant value, for example, incremented by 1 or 2 each time it is updated, to correctly reflect that the third data has been updated again; at the same time, the _delete field is reset to the first value 0 or false, indicating that this field has no function. That is, the _version field is incremented by the target value, and the _delete field is the first value 0 or false, which can indicate an update operation.

[0077] In this embodiment, since all data changes are treated as insert operations, the Writer implementation becomes very simple. It only requires creating a PreparedStatement object, then looping through all records, calling the setXXX() method to set parameters, and finally executing executeUpdate(). This avoids complex and time-consuming update logic and improves the speed and efficiency of data writing.

[0078] In an exemplary embodiment, after synchronizing multiple stream data to the target data table corresponding to the stream data based on the mapping relationship, the method further includes: determining whether the target data table contains a unique key; if the target data table contains the unique key, using the unique key to deduplicate the stream data; if the target data table does not contain the unique key, deduplicate the stream data based on a target function.

[0079] In the above embodiments, after executing SQL (Structured Query Language) statements to synchronize the target data to the target data table, deduplication can be performed on the data in the target data table. First, it can be determined whether the target data table contains a unique key. A unique key can be any field or combination of fields; _version can be used as a unique key. When the target data table contains a unique key, deduplication can be achieved directly during data writing using database features. Specifically, multi-version control will automatically retain the latest data. When the ID of an attempted insertion conflicts with an existing record in the target table, an update operation will be performed to ensure that the latest version and status are correctly recorded, and the data fields are also updated. When the target data table does not contain a unique key, data deduplication can be achieved using a window function (i.e., the target function mentioned above).

[0080] This embodiment intelligently utilizes version and status fields, and selects appropriate deduplication strategies based on the structure and constraints of the target data table, significantly improving the efficiency and accuracy of real-time data synchronization. It not only reduces redundant operations during data processing but also avoids potential data duplication or inconsistency issues in the target system, thereby enhancing the performance and reliability of the entire data synchronization system.

[0081] In one exemplary embodiment, splitting the target data to obtain multiple split data includes: tagging the target data based on the source data table to obtain tagged data; and splitting the tagged data to obtain multiple split data.

[0082] In the above embodiments, after the initial data is formatted and packaged with the changed fields into target data, the target data can be tagged according to the tagging logic of the source data tables to obtain tagged data containing identifiers. Then, the tagged data can be split according to the splitting rules and the mapping relationship between the data tables, resulting in multiple split data sets. Through data tagging and splitting, the initial data obtained from multiple source data tables can be separated, meaning that one split data set represents data from one source data table.

[0083] This embodiment demonstrates how tagging and splitting target data based on the source data table can significantly improve the efficiency and accuracy of real-time data synchronization, while optimizing resource utilization and reducing storage overhead. Specifically, the tagging process associates each record in the data stream with its source information, facilitating precise routing and operations during subsequent processing; while splitting enables parallel data processing, improving the throughput of data synchronization and system response speed.

[0084] In one exemplary embodiment, tagging the target data based on a source data table to obtain tagged data includes: determining a first identifier of the source data table and a second identifier of a source database containing the source data table; determining a combined identifier based on the first identifier and the second identifier; and adding the combined identifier to a target position in the target data to obtain the tagged data.

[0085] In the above embodiments, during the tagging of target data, the data tagging logic can be set first. This can be achieved by combining the name of the source database (i.e., the second identifier mentioned above) and the name of the source data table (i.e., the first identifier mentioned above) as a combined identifier, used for the tagging logic of the target data tagging operation. Alternatively, the combined identifier can be the result of adding the second identifier or the first identifier. The name of the target data table or the name of the database in which the target data table exists can also be used as the tagging logic. After determining the tagging logic, the ProcessFunction can be used to tag the target data, and the generated tagged data can be output. The ProcessFunction can be understood as a tool in Apache Flink that outputs data to multiple data streams.

[0086] This embodiment employs a tagging strategy based on source data tables. By combining the first identifier of the source data table with the second identifier of the source database to generate a unique combined identifier, the accuracy and tracking capability of data synchronization can be significantly enhanced. Specifically, after adding this combined identifier to the target location of the target data, each data entry is given clear source information. Furthermore, by utilizing the combined identifier, the system can better manage the fusion of multi-source data, ensuring efficient data processing even in complex data environments. This effectively avoids data chaos and redundancy, improving the stability and reliability of the entire real-time data synchronization architecture.

[0087] In one exemplary embodiment, the marking data is split to obtain multiple split data, including: determining sub-marking data belonging to the same source data table in the marking data based on the combined identifier included in the marking data; determining multiple sub-marking data as multiple split data, wherein one sub-marking data corresponds to one split data, and the split data corresponding to each sub-marking data is different.

[0088] In the above embodiments, during the process of splitting the tagged data, a splitting rule based on the source database name and source table name can be determined first. The ProcessFunction can then be used to split sub-tagged data belonging to the same source table based on the splitting rule and mapping relationship, generating a corresponding number of SideOutputStreams (i.e., the number of source tables) (i.e., the split data mentioned above). Each split sub-tagged data represents data from a different source table. After generating multiple split data streams, each stream can be concatenated into a complete SQL statement according to the target system's insert statement format, based on the data table mapping relationship.

[0089] In the above embodiments, preparing the mapping relationship may include configuring the mapping relationship between the data source and the target system database table, and configuring the mapping relationship between the source data table and the target data table fields. The configuration of the mapping relationship between the data source and the target system database table may include a source table: specifying the table name and its corresponding schema in the source database, and a target table: specifying the table name and its schema in the target system. The configuration of the mapping relationship between the source data table and the target data table fields can be understood as defining the correspondence between fields in the source data table and fields in the target data table, and may include: source field name, target field name, and data type (such as certificate, string, etc.).

[0090] This embodiment enables intelligent data routing for labeling data, achieving refined management and efficient processing of multi-source data. The system can accurately identify sub-labeling data belonging to the same source data table and then route these data to specific processing paths. This effectively avoids the data mixing and low processing efficiency problems that may occur in traditional processing methods, ensuring the purity of each data stream and the targeted nature of the processing.

[0091] The data synchronization method in this application will be explained below with reference to specific embodiments.

[0092] Figure 4 This is a flowchart illustrating the data synchronization method in this optional example, as follows: Figure 4 As shown, the data synchronization method can include the following steps:

[0093] Step S402, Begin;

[0094] Step S404: Data source and target system preparation;

[0095] Step S406: Prepare database table mapping relationships;

[0096] Step S408: Connect to the data source;

[0097] Step S410: Process the data stream;

[0098] Step S412: Tag the data stream;

[0099] Step S414: Split the data stream;

[0100] Step S416: Generate SQL statements;

[0101] Step S418: Process the field values;

[0102] Step S420: Data is written to the target database;

[0103] Step S422: Determine whether the database was successfully written to the target database. If yes, proceed to step S424; otherwise, proceed to step S420.

[0104] Step S424, End.

[0105] Step 1: Data source and target system preparation

[0106] 1. Data source connection information configuration

[0107] 1) Enable binary logging: For most databases (such as MySQL and PostgreSQL), binary logging (binlog) needs to be enabled, which allows the database to record all change events.

[0108] 2) Create a CDC user: Create a dedicated user with sufficient permissions to read changes to the data table.

[0109] 3) Data Extraction Configuration: Configure the source database in tools such as Debezium to ensure the capture of the required tables and data. Appropriate parameters need to be set according to the specific characteristics of the data tables, such as table name, schema, and connection information.

[0110] 2. Target system connection information configuration

[0111] 1) Specify the database type: such as Hive, ClickHouse, Byconity, etc. Different databases have different configuration parameters.

[0112] 2) Provide database connection information: including database address, account, password, port, etc.

[0113] Step 2: Preparing Database Table Mapping Relationships

[0114] 1. Configuration of mapping relationship between data source and target system database tables

[0115] 1) Source Table: You need to specify the table name and its corresponding schema in the source database.

[0116] 2) Target Table: Specifies the table name and schema in the target system.

[0117] 2. Configuration of field mapping relationship between source table and target table

[0118] Defining the mapping between fields in the source table and fields in the target table typically includes:

[0119] 1) Source field name.

[0120] 2) Target field name.

[0121] 3) Data type (such as integer, string, etc.).

[0122] Step 3: Connect to the data source

[0123] 1. Connect to the data source: Connect to the data source via FlinkJDBC based on the data source connection information provided in step one.

[0124] 2. Parameter settings: When performing a full synchronization, you need to set fetchSize and splitSize.

[0125] 3. Determine the message transformation method: Use the TableRowDataDebeziumDeserializeSchema class to deserialize the JSON data received from Debezium into the FlinkSQL RowData data type for use in Flink stream processing jobs.

[0126] Step 4: Process the data stream

[0127] 1. Set up a fault tolerance mechanism

[0128] Fault tolerance mechanisms are used to ensure data consistency, reliability, and recoverability. There are two types: Exactly-once (default, it will only be processed once) and AT_LEAST_ONCE (it will be processed at least once).

[0129] 2. Configure Checkpoint (Status Management)

[0130] 1) Periodic checkpoints: Flink periodically creates checkpoints (Configurable) to capture the current state of the stream processing and ensure the persistence of the state.

[0131] 2) Persistent storage: Checkpoints can be stored in reliable persistent storage, such as HDFS, S3, etc., to prevent data loss.

[0132] Obtaining data streams: Use DataStreamSource in conjunction with the CDC connector to obtain data from the data source.

[0133] 3. Message Conversion: Convert SourceRecord to JSON string and add built-in fields _version and _delete.

[0134] Step 5: Tag the data stream

[0135] 1. Set the data tagging logic: Use the database table name as the tagging logic.

[0136] 2. Use ProcessFunction to tag the data stream.

[0137] 3. Output the labeled data.

[0138] Step Six: Split the data stream

[0139] 1. Set traffic splitting rules

[0140] The traffic splitting rules are generated based on the database name and table name of the data source.

[0141] 2. Diversion

[0142] Based on the splitting rules and the data table mapping relationship in step two, the ProcessFunction is used to split the data stream and generate a corresponding number of SideOutputStreams.

[0143] Step 7: Generate SQL statements

[0144] Based on the data table field mapping relationship in step two, construct the data insertion statement.

[0145] Step 8: Processing field values

[0146] During data synchronization, it may be necessary to transform the data, for example:

[0147] 1. Type Conversion: The rules for handling situations where the data types of the source field and the target field are inconsistent.

[0148] 2. Format Transformation: For example, converting the date format from YYYY-MM-DD to DD / MM / YYYY.

[0149] 3. Enumeration Mapping: If the value of a field uses an enumeration, it may be necessary to define how to perform the conversion in the mapping.

[0150] 4. NullValueHandling: If a field value is empty, it needs to be filled with a specific value or set to empty.

[0151] Step 9: Write data to the target table

[0152] 1. Execute SQL: Insert data into the target table.

[0153] 2. Data deduplication: If the target table has a unique key, the latest data will be automatically retained based on multi-version control. If there is no unique key, data deduplication can be achieved through window functions.

[0154] In the aforementioned embodiments, a single source component can handle multiple databases and tables. The Context class in ProcessFunction can then be used to selectively output the data stream to different streams (SideOutputStream) based on certain conditions. This reduces resource duplication and improves overall resource utilization efficiency, avoiding the resource waste caused by creating multiple source components for each database or table. Furthermore, by introducing multi-version control, the newly added built-in fields _version and _delete can transform all operations into Insert operations. The Writer only needs to implement the PrepareStatement for a single Insert SQL statement to improve synchronization efficiency and simplify the data insertion method. In addition, based on the built-in fields _version and _delete, combined with the database unique key or through ETL tasks, it can be ensured that only the latest data is retained in the current table, thereby reducing storage overhead.

[0155] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions and modules involved are not necessarily essential to this application.

[0156] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods according to the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as read-only memory (ROM) / random access memory (RAM), magnetic disk, optical disk), and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, or network device, etc.) to execute the methods described in the various embodiments of this application.

[0157] According to another aspect of the embodiments of this application, a data synchronization device is also provided, which can be used to implement the data synchronization method provided in the above embodiments, and will not be repeated hereafter. As used below, the term "module" can be a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0158] Figure 5 This is a structural block diagram of an optional data synchronization device according to an embodiment of this application, such as... Figure 5 As shown, the data synchronization device includes:

[0159] The acquisition module 510 is used to acquire initial data from multiple source data tables;

[0160] The determination module 512 is used to determine the changed fields based on the change information of multiple source data tables;

[0161] Packaging module 514 is used to package the initial data and the changed fields to obtain target data;

[0162] The splitting module 516 is used to split the target data to obtain multiple split data.

[0163] The synchronization module 518 is used to synchronize multiple streams of data to the target data table corresponding to the streams of data based on a mapping relationship. The mapping relationship is the association between the source data table and the target data table, and the target data table is the table to which the initial data is to be synchronized.

[0164] In an exemplary embodiment, the determining module 512 can determine the changed field based on the change information of multiple source data tables in the following manner: determine the change operation based on the change information of multiple source data tables; and determine the changed field based on the change operation.

[0165] In an exemplary embodiment, the determining module 512 can determine the change field based on the change operation in the following manner: when the change operation is an insertion of first data, the version field included in the change field of the first data is set to an initial value, and the status field included in the change field of the first data is set to a first value, wherein the version field is used to indicate the number of times the data is updated, and the status field is used to indicate the status of the data; when the change operation is a deletion of second data, the version field included in the change field of the second data is kept unchanged, and the status field included in the change field of the second data is set to a second value; when the change operation is an update of third data, the version field included in the change field of the third data is set to a target value, wherein the target value is the value of the current version field of the third data plus a target constant, and the status field included in the change field of the third data is set to the first value.

[0166] In an exemplary embodiment, the apparatus may be used to: determine whether the target data table contains a unique key after synchronizing multiple sets of split data to the target data table corresponding to the split data based on the mapping relationship; if the target data table contains the unique key, use the unique key to deduplicate the split data; if the target data table does not contain the unique key, deduplicate the split data based on a target function.

[0167] In an exemplary embodiment, the splitting module 516 can split the target data to obtain multiple split data in the following manner: tagging the target data based on the source data table to obtain tagged data; and splitting the tagged data to obtain multiple split data.

[0168] In an exemplary embodiment, the splitting module 516 can tag the target data based on the source data table to obtain tagged data by: determining a first identifier of the source data table and a second identifier of the source database containing the source data table; determining a combined identifier based on the first identifier and the second identifier; and adding the combined identifier to the target position of the target data to obtain the tagged data.

[0169] In an exemplary embodiment, the splitting module 516 can split the marking data to obtain multiple split data in the following manner: determining sub-marking data belonging to the same source data table based on the combined identifier included in the marking data; determining multiple sub-marking data as multiple split data, wherein one sub-marking data corresponds to one split data, and the split data corresponding to each sub-marking data is different.

[0170] It should be noted that the above modules can be implemented by software or hardware. For the latter, they can be implemented in the following ways, but are not limited to: all the above modules are located in the same processor; or, the above modules are located in different processors in any combination.

[0171] According to another aspect of the embodiments of this application, a computer-readable storage medium is provided, the computer-readable storage medium including a stored program, wherein the program executes the steps in any of the above method embodiments when it is run.

[0172] In one exemplary embodiment, the aforementioned computer-readable storage medium may include, but is not limited to, various media capable of storing computer programs, such as USB flash drives, ROMs, RAMs, portable hard drives, magnetic disks, or optical disks.

[0173] According to another aspect of the embodiments of this application, an electronic device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor. The processor is configured to perform the steps of any of the method embodiments described above via the computer program. In an exemplary embodiment, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor, and the input / output device is connected to the processor.

[0174] Specific examples in this embodiment can be found in the examples described in the above embodiments and exemplary implementations, and will not be repeated here.

[0175] According to another aspect of the embodiments of this application, a computer program product is also provided, comprising a computer program / instructions containing program code for performing the methods shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network via communication section 609, and / or installed from removable medium 611. When the computer program is executed by central processing unit 601, it performs various functions provided in the embodiments of this application. The sequence numbers of the embodiments of this application above are merely descriptive and do not represent the superiority or inferiority of the embodiments.

[0176] Figure 6A schematic block diagram of a computer system architecture for implementing embodiments of the present application is shown. Figure 6 As shown, the computer system 600 includes a Central Processing Unit (CPU) 601, which performs various appropriate actions and processes based on programs stored in ROM 602 or loaded into RAM 603 from storage section 608. Random access memory 603 also stores various programs and data required for system operation. The CPU 601, ROM 602, and RAM 603 are interconnected via bus 604. Input / output (I / O) interface 605 is also connected to bus 604.

[0177] The following components are connected to I / O interface 605: an input section 606 including a keyboard, mouse, etc.; an output section 607 including a cathode ray tube (CRT), liquid crystal display (LCD), and speakers, etc.; a storage section 608 including a hard disk, etc.; and a communication section 609 including a network interface card, such as a local area network card or modem, etc. The communication section 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to I / O interface 605 as needed. A removable medium 611, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on drive 610 as needed so that computer programs read from it can be installed into storage section 608 as needed.

[0178] Specifically, according to embodiments of this application, the processes described in the various method flowcharts can be implemented as computer software programs. For example, embodiments of this application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via communication section 609, and / or installed from removable medium 611. When the computer program is executed by central processing unit 601, it performs various functions defined in the system of this application.

[0179] It should be noted that, Figure 6 The computer system 600 of the electronic device shown is merely an example and should not impose any limitation on the functionality and scope of use of the embodiments of this application.

[0180] Obviously, those skilled in the art should understand that the modules or steps of this application described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. They can be implemented using computer-executable program code, and thus can be stored in a storage device for execution by a computing device. In some cases, the steps shown or described can be performed in a different order than those described herein, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, this application is not limited to any particular combination of hardware and software.

[0181] The above are merely preferred embodiments of this application and are not intended to limit this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the principles of this application should be included within the protection scope of this application.

Claims

1. A data synchronization method, characterized in that, include: Retrieve initial data from multiple source tables; The changed fields are determined based on the change information from multiple source data tables; The initial data and the changed fields are packaged together to obtain the target data; The target data is split into multiple streams; Based on the mapping relationship, multiple sets of split data are synchronized to the target data table corresponding to the split data. The mapping relationship is the association between the source data table and the target data table, and the target data table is the table to which the initial data is to be synchronized.

2. The method according to claim 1, characterized in that, The changed fields are determined based on change information from multiple source data tables, including: The change operation is determined based on the change information of multiple source data tables; The changed field is determined based on the change operation.

3. The method according to claim 2, characterized in that, The changed fields are determined based on the change operation, including: When the change operation is an insertion of first data operation, the version field included in the change field of the first data is set to an initial value, and the status field included in the change field of the first data is set to a first value, wherein the version field is used to indicate the number of times the data is updated, and the status field is used to indicate the status of the data; When the change operation is to delete the second data, the version field included in the changed field of the second data remains unchanged, and the status field included in the changed field of the second data is set to the second value; When the change operation is an update operation for third data, the version field included in the change field of the third data is set to a target value, the target value being the current version field of the third data plus a target constant value, and the status field included in the change field of the third data is set to the first value.

4. The method according to claim 1, characterized in that, After synchronizing multiple stream data sets to the target data table corresponding to the stream data sets based on the mapping relationship, the method further includes: Determine whether the target data table contains a unique key; If the target data table includes the unique key, the unique key is used to deduplicate the split data; If the unique key is not included in the target data table, the split data is deduplicated based on the objective function.

5. The method according to claim 1, characterized in that, The target data is split into multiple streams, including: The target data is tagged based on the source data table to obtain tagged data; The marking data is split into multiple streams to obtain multiple streams of data.

6. The method according to claim 5, characterized in that, The target data is tagged based on the source data table to obtain tagged data, including: Determine the first identifier of the source data table and the second identifier of the source database containing the source data table; A combined identifier is determined based on the first identifier and the second identifier; The combined identifier is added to the target location of the target data to obtain the marking data.

7. The method according to claim 5, characterized in that, The marking data is split into multiple streams, including: Based on the combined identifiers included in the marking data, determine the sub-marking data that belong to the same source data table included in the marking data; Multiple sub-labeling data are determined as multiple split data, wherein one sub-labeling data corresponds to one split data, and the split data corresponding to each sub-labeling data is different.

8. A data synchronization device, characterized in that, include: The acquisition module is used to acquire initial data from multiple source data tables; The determination module is used to determine the changed fields based on the change information of multiple source data tables; The packaging module is used to package the initial data and the changed fields to obtain the target data; The splitting module is used to split the target data to obtain multiple split data. The synchronization module is used to synchronize multiple streams of data to the target data table corresponding to the streams of data based on a mapping relationship. The mapping relationship is the association between the source data table and the target data table, and the target data table is the table to which the initial data is to be synchronized.

9. A computer program product comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, wherein the computer program, when executed by a processor, implements the steps of the method according to any one of claims 1 to 7.

11. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 7.

Images