Incremental data synchronization method based on primary key combination

Abstract

The invention relates to an incremental data synchronization method based on primary key combination. The method comprises the steps that a mapping relation is established, keys of the mapping relation are formed by combining table names and main key values, and the values of the mapping relation are corresponding DML records; dML records are collected from the source library, whether main keys of the DML records exist in the mapping or not is checked, and if not, the DML records are directly added into the mapping; if yes, taking out the record of the same primary key from the mapping as an original record, and taking the DML record as a new record; if the type of the DML record is deelete, adding the original record and the new record into a sending queue; if the record is update, the original record and the new record are merged; if so, adding the original record into a sending queue, and adding the new record into mapping; and when the transaction is submitted, traversing all residual DML records in the mapping, writing the DML records into a sending queue, and sending the DML records to a downstream system for processing. According to the method, by optimizing the processing flow of the DML records related to incremental migration, rapid merging and efficient synchronization of incremental data are achieved, and the efficiency and accuracy of data synchronization are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data synchronization methods, and in particular to an incremental data synchronization method based on primary key merging. Background Art

[0002] With the advent of the big data era, the explosive growth of data volume has put forward higher requirements on the performance and efficiency of data synchronization technology. In scenarios such as distributed systems, cloud databases, and data warehouses, achieving efficient and accurate data synchronization is the key to ensuring data consistency and business continuity. Although the traditional full data synchronization method is simple and direct, its performance bottleneck is increasingly prominent when facing massive data. Especially in scenarios where data changes frequently, full data synchronization is not only time-consuming and labor-intensive, but may also lead to a huge waste of system resources.

[0003] Incremental data synchronization methods have emerged as the times require. They only synchronize data that has changed since the last synchronization, greatly improving the efficiency of data synchronization. However, how to efficiently process and synchronize these incremental data while ensuring data integrity and consistency is still a hot topic and difficulty in this technical field. In particular, when faced with complex data changes (such as insert, update, and delete operations), how to effectively merge these change records, reduce data transmission volume, and reduce processing complexity has become the key to improving data synchronization performance. Summary of the invention

[0004] In order to solve the above-mentioned problems existing in the incremental data synchronization process, the present invention proposes a new incremental data synchronization method based on primary key merging. The method of the present invention optimizes the processing flow of DML (including insert, update, delete) records involved in incremental migration, realizes rapid merging and efficient synchronization of incremental data, significantly improves the efficiency and accuracy of data synchronization, and reduces system resource consumption.

[0005] Specifically, the present invention provides an incremental data synchronization method based on primary key merging, such as Figure 2 As shown, the method comprises the following steps:

[0006] S1. Establish and maintain a mapping relationship in the program; the key of the mapping relationship is composed of the table name and the primary key value to ensure the uniqueness of each record; the value of the mapping relationship is the corresponding DML record. This mapping relationship is used to store and track all pending DML records;

[0007] S2. When a DML record is collected from the source database, first check whether the primary key of the DML record already exists in the mapping. If not, directly add the DML record to the mapping; if it already exists, take the record with the same primary key from the mapping as the original record (prev record), take the DML record as the new record (next record) and proceed to the next step;

[0008] S3. Determine whether the type of the DML record is the delete type. If so, add both the original record and the new record in the mapping to the sending queue and wait for them to be sent uniformly when the subsequent transaction is committed;

[0009] S4. If the type of the DML record is not the delete type, it is further determined whether it is the update type. If so, the original record and the new record in the mapping are merged. If not, the original record in the mapping is added to the sending queue and the new record is added to the mapping.

[0010] S5. When a transaction commit event is collected, all remaining DML records in the mapping are traversed, written sequentially into the send queue, and sent as a complete transaction to the downstream system for processing.

[0011] Furthermore, the value of the mapping relationship described in step S1 of the incremental data synchronization method based on primary key merging of the present invention is a corresponding DML record, including but not limited to: DML type, task ID, and task status.

[0012] Furthermore, the types of DML records described in step S2 of the incremental data synchronization method based on primary key merging of the present invention include: insert, update, and delete.

[0013] Furthermore, the incremental data synchronization method based on primary key merging of the present invention in step S4 merges the original record and the new record in the mapping, including overwriting the original value of the corresponding column in the original record with the new value in the new record (update record), and updating the corresponding record in the mapping.

[0014] Furthermore, all remaining DML records in the mapping described in step S5 of the incremental data synchronization method based on primary key merging of the present invention include records that are not sent due to delete or merged insert / update.

[0015] Furthermore, the incremental data synchronization method based on primary key merging of the present invention comprises, in step S5:

[0016] S51. When a transaction commit event is collected, all remaining DML records in the mapping are traversed;

[0017] S52. Remove these DML records from the mapping and write them sequentially into the send queue to generate a complete transaction;

[0018] S53. Start or notify another thread / process to take out the transaction generated in the previous step from the sending queue and send it to the downstream system (target database);

[0019] S54. The downstream system performs corresponding processing according to the DML type.

[0020] Furthermore, the downstream system described in step S54 of the incremental data synchronization method based on primary key merging of the present invention performs corresponding processing according to the DML type, including inserting, updating or deleting operations.

[0021] On the other hand, the present invention also provides an incremental data synchronization system based on primary key merging, and when the system is running, the steps of the incremental data synchronization method based on primary key merging are implemented.

[0022] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of the above-mentioned incremental data synchronization method based on primary key merging are implemented.

[0023] In summary, the present invention provides an efficient and accurate incremental data synchronization method based on primary key merging, which has the following advantages:

[0024] (1) Improved synchronization efficiency: Through the primary key-based DML record merging strategy, the DML record processing flow is optimized, the data transmission volume is reduced, the processing complexity is reduced, and the efficiency of data synchronization is significantly improved. It has broad application prospects and important practical value.

[0025] (2) Ensure data consistency: By maintaining the mapping relationship, each DML record is ensured to be processed correctly, whether it is sent individually or as part of a transaction, the consistency and integrity of the data can be guaranteed.

[0026] (3) Reduced resource consumption: Unnecessary full data synchronization is reduced, and incremental data synchronization is performed only when the data changes, effectively reducing the consumption of system resources.

[0027] (4) Enhanced flexibility: This method is not only applicable to data synchronization of a single database, but can also be extended to data synchronization between multi-source heterogeneous databases, thereby enhancing the flexibility and scalability of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] In order to more clearly illustrate the technical solution of the present invention, the following briefly introduces the drawings required for use in the description of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without paying creative work.

[0029] Figure 1 The figure is a flowchart of the overall implementation of the method of the present invention.

[0030] Figure 2 It is a flow chart for implementing the method of the present invention. DETAILED DESCRIPTION

[0031] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments, and the present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0032] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.

[0033] Embodiment: A method for synchronizing incremental data based on primary key merging

[0034] Figure 1 The overall implementation process of the method of the present invention is shown in the form of a flow chart. From left to right, it is DML record collection, mapping relationship check, DML record processing (including different processing logics of insert, update, and delete), transaction submission processing, and final data transmission. The processing paths and results under different DML record types are represented by different branches and arrows in the figure.

[0035] The technical solution of the present invention mainly includes the following steps:

[0036] (I) Establish a mapping relationship: Maintain a mapping structure in the program, whose key is composed of the table name and the primary key value to ensure the uniqueness of each record; the value is the corresponding DML record. This mapping relationship is used to store and track all pending DML records.

[0037] (II) Collection and processing of DML records:

[0038] (1) When a DML record is collected from the source database, first check whether the primary key of the record already exists in the mapping.

[0039] (2) If it does not exist, add the record directly to the mapping.

[0040] (3) If it already exists, different processing is performed according to the type of the DML record (such as insert, update, delete, etc.):

[0041] (a) If it is a delete record, it will not be merged. The original record (prev) and the new record (next) in the mapping will be added to the sending queue and will be sent together when the subsequent transaction is committed.

[0042] (b) If it is an update record, it is merged with the original record (prev) in the mapping, the new value in the update record is used to overwrite the original value of the corresponding column of prev, and the record in the mapping is updated.

[0043] (c) If it is an insert record, and a record with the same primary key already exists in the mapping (i.e., prev is insert or update), the original record (prev) in the mapping is added to the send queue, and the new record is updated in the mapping.

[0044] (III) Transaction commit processing: When a transaction commit event is collected, all remaining DML records in the mapping (i.e., records not sent due to delete or merged insert / update) are traversed, written sequentially to the send queue, and sent to the downstream system as a complete transaction.

[0045] Specific implementation cases

[0046] Scene Setting

[0047] Source database: MySQL, which stores order data of the e-commerce platform.

[0048] Target database: Hadoop Hive, for data analysis.

[0049] Order ID: 123

[0050] Initial state

[0051] The map (HashMap) is empty.

[0052] The send queue (LinkedBlockingQueue) is empty.

[0053] DML operations and processing

[0054] (1) Insert operation

[0055] Situation: A user places a new order on an e-commerce platform, and the order ID is 123.

[0056] ·DML record: INSERT INTO orders(id,status,amount)VALUES(123,'pending',100.00)

[0057] ·deal with:

[0058] (a) Checks whether the key orders_123 exists in the map.

[0059] (b) Because it does not exist, a record is directly added to the mapping with the key being orders_123 and the value being a DMLRecord object containing the DML type (insert), order ID (123), order status (pending), and order amount (100.00).

[0060] (c) There is no need to join the sending queue because this is a new order waiting for the transaction to be committed.

[0061] (2) Update operation (order status update)

[0062] Situation: The payment for order 123 is successful and the order status needs to be updated.

[0063] ·DML record: UPDATE orders SET status='paid'WHERE id=123

[0064] ·deal with:

[0065] (a) Checks whether the key orders_123 exists in the map.

[0066] (b) If it exists, retrieve the original record (prev), which is left by the previous insert operation and has a status of 'pending'.

[0067] (c) Create a new record (next), based on prev but updating the status to 'paid'.

[0068] (d) Replace prev with next in the mapping.

[0069] (e) There is no need to join the sending queue immediately and wait for the transaction to be committed.

[0070] (3) Delete operation (order cancellation)

[0071] Situation: User canceled order 123.

[0072] ·DML record: DELETE FROM orders WHERE id=123

[0073] ·deal with:

[0074] (a) Checks whether the key orders_123 exists in the map.

[0075] (b) If it exists, take out the original record (prev) and directly add the record and the delete record to the sending queue.

[0076] (c) Remove the key orders_123 from the map.

[0077] (d) Note: You do not need to wait for the transaction to be committed at this time, because the delete operation usually means that the record no longer needs to be synchronized to the target database. However, according to the specific business logic, you can also design it to wait for the transaction to be committed before processing.

[0078] (4) Transaction Commit

[0079] · Situation: All DML operations are completed and the transaction is committed.

[0080] ·deal with:

[0081] (a) Traverse all remaining DML records in the mapping (in this example, assume that there are no more DML operations that affect order 123, so the only records left in the mapping may be insert 123 and delete 123).

[0082] (b) Remove these records from the map and add them to the send queue.

[0083] (c) Start or notify another thread / process to take records from the send queue and send them to the target database Hadoop Hive.

[0084] (d) In Hive, perform the corresponding insert, update, or delete operation based on the DML type.

[0085] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. An incremental data synchronization method based on primary key merging, characterized in that: The method comprises: S1. Establish and maintain a mapping relationship in the program; the key of the mapping relationship is composed of the table name and the primary key value to ensure the uniqueness of each record; the value of the mapping relationship is the corresponding DML record; S2. When a DML record is collected from the source database, first check whether the primary key of the DML record already exists in the mapping. If not, directly add the DML record to the mapping; if it already exists, take the record with the same primary key from the mapping as the original record, take the DML record as the new record and proceed to the next step; S3. Determine whether the type of the DML record is a delete type. If so, add both the original record and the new record in the mapping to the sending queue; S4. If the type of the DML record is not the delete type, it is further determined whether it is the update type. If so, the original record and the new record in the mapping are merged. If not, the original record in the mapping is added to the sending queue and the new record is added to the mapping. S5. When a transaction commit event is collected, all remaining DML records in the mapping are traversed, they are sequentially written into the sending queue, and sent as a complete transaction to the downstream system for processing.

2. The incremental data synchronization method based on primary key merging according to claim 1 is characterized in that: The value of the mapping relationship described in step S1 is the corresponding DML record, including: DML type, task ID, and task status.

3. The incremental data synchronization method based on primary key merging according to claim 1 is characterized in that: The types of DML records in step S2 include: insert, update, and delete.

4. The incremental data synchronization method based on primary key merging according to claim 1 is characterized in that: The merging of the original record and the new record in the mapping in step S4 includes overwriting the original value of the corresponding column in the original record with the new value in the new record, and updating the corresponding record in the mapping.

5. The incremental data synchronization method based on primary key merging according to claim 1 is characterized in that: All DML records remaining in the mapping described in step S5 include records not sent due to delete or merged insert / update.

6. The incremental data synchronization method based on primary key merging according to claim 1 is characterized in that: Step S5 includes: S51. When a transaction commit event is collected, all remaining DML records in the mapping are traversed; S52. Remove these DML records from the mapping and write them sequentially into the send queue to generate a complete transaction; S53. Start or notify another thread / process to take out the transaction generated in the previous step from the sending queue and send it to the downstream system; S54. The downstream system performs corresponding processing according to the DML type.

7. The incremental data synchronization method based on primary key merging according to claim 6 is characterized in that: The downstream system described in step S54 performs corresponding processing according to the DML type, including inserting, updating or deleting operations.

8. An incremental data synchronization system based on primary key merging, characterized in that: When the system is running, the steps of the incremental data synchronization method based on primary key merging described in any one of claims 1 to 7 are implemented.

9. A computer-readable storage medium having a computer program stored thereon, wherein the program, when executed by a processor, implements the steps of the incremental data synchronization method based on primary key merging as described in any one of claims 1 to 7.

Images