A method for data incremental synchronization based on multi-table association query

Abstract

The present invention relates to the field of data processing technology, and specifically to a method for data incremental synchronization based on multi-table associated query, wherein a base table unique key is selected, one or more tables of the source of data synchronization are called base tables, and a target table of data synchronization is called a target table; the target table is defined, and in addition to the fields required for normal synchronization, the target table additionally includes the unique keys of all base tables; a data synchronization module is established, and when the base table data changes, the data before and after the change of the base table can be automatically stored, and then the data is updated into the target table in an asynchronous manner according to the association logic between the base tables; the method for data incremental synchronization of multi-table associated query solves the problem of synchronization of multi-table associated incremental data, and also provides a cross-database synchronization solution for multi-table associated incremental data, which is more efficient, does not require a large number of archive logs, and significantly reduces the data synchronization pressure on disks and local disaster recovery.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a method for data incremental synchronization based on multi-table association query. Background Art

[0002] Existing data synchronization methods include trigger-based incremental synchronization and timestamp-based incremental synchronization, but current technologies are all single-table synchronization without processing logic.

[0003] There is a published Chinese patent: A database management method and device, publication number: CN101382949A, which uses a unique index or primary key to determine whether there are duplicate data in multiple database tables. However, this patent is a single-table incremental synchronization, and the single-table incremental synchronization scenario is relatively simple.

[0004] Since the data after multi-table association contains query logic, in order to maintain the integrity of the data after the association query, the query results are often fully synchronized or materialized views are constructed. This method can work simply and effectively when the data volume is not large, but for large-scale relational table association queries, data synchronization is time-consuming and generates a large number of archive logs, which can easily have a great impact on system performance. The ordinary view method is eventually converted into a base table query, which has a certain loss in query performance. Summary of the invention

[0005] The present invention provides a method for data incremental synchronization based on multi-table associated query, which is used to solve the problem of data associated incremental synchronization when the base of stock data of multiple tables is large but the number of updates is not large.

[0006] In order to achieve the above purpose, a method for data incremental synchronization based on multi-table association query is designed, and the method is as follows:

[0007] A. Select the unique key of the base table. The source table or tables of data synchronization are called base tables, and the target table of data synchronization is called target table.

[0008] B. Define the target table. In addition to the fields required for normal synchronization, the target table also includes the unique keys of all base tables.

[0009] C. Establish a data synchronization module. When the base table data changes, the data before and after the change can be automatically stored. Then, in an asynchronous manner, the data is updated into the target table according to the association logic between the base tables. The specific logic is as follows:

[0010] a. When any base table data is newly added, the synchronization program captures the value of the base table's unique key or the combination of values ​​of multiple table unique keys, generates new incremental data based on the value of the unique key or the combination of values ​​of multiple table unique keys according to the association logic between the base tables, and inserts the new incremental data into the destination table;

[0011] b. When any base table data is deleted, the synchronization program captures the base table unique key value or the combination of multiple table unique key values, and deletes all data records associated with it in the target table based on the base table unique key value or the combination of multiple table unique key values;

[0012] c. When any base table data is updated, the synchronization program captures the value of the base table's unique key or the combination of multiple table unique key values, and deletes all data records associated with it in the target table based on the value of the base table's unique key or the combination of multiple table unique key values; at the same time, according to the association logic between the base tables, new incremental data is generated based on the value of the unique key or the combination of multiple table unique key values, and the new incremental data is inserted into the target table.

[0013] The present invention also has the following preferred technical solutions:

[0014] 1. The unique key of the base table is the primary key field or the unique index field.

[0015] 2. In step B, a query index is created for the target table at the same time

[0016] 3. The data synchronization module is used to provide configuration entries for the base table, target table, and base table associated logic, and can automatically establish add, delete, and modify triggers on the base table.

[0017] 4. The data synchronization module provides a message confirmation mechanism and a failure retransmission mechanism after data synchronization.

[0018] Compared with the prior art, the present invention has the following advantages: when the base of stock data in multiple tables is large but the number of updates is small, the method for incremental synchronization of multi-table associated query data solves the problem of synchronization of multi-table associated incremental data, and also provides a cross-database synchronization solution for multi-table associated incremental data. Compared with full synchronization, it is more efficient, without a large number of archive logs, and the data synchronization pressure on disks and local disaster recovery is also significantly reduced.

[0019] When multiple tables are associated, the Cartesian product of the data in the multiple tables in the base database will inevitably result in a new record result set, and the new record result set has no absolute comparison benchmark in the target table. The processing methods for adding, deleting, and updating operations are not exactly the same. This data synchronization is a synchronization with processing logic. The present invention uses a multi-table unique joint identifier combined with logic to achieve the purpose. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] Figure 1It is a flow chart of the present invention. DETAILED DESCRIPTION

[0021] The present invention is further described below in conjunction with the accompanying drawings, and the structure and principle of the present invention are very clear to people in the field. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0022] The present invention is a method for data incremental synchronization based on multi-table association query, which is specifically as follows:

[0023] A. Select the unique key of the base table (which can be the primary key field or the unique index field). The source table or tables of data synchronization are called base tables, and the target table of data synchronization is called target table. The same applies below.

[0024] B. Define the target table. In addition to the fields required for normal synchronization, the target table also includes the unique keys of all base tables. At the same time, you can create a query index for the target table to increase the query speed of the target table.

[0025] C. Establish a data synchronization program, which can achieve the following functions:

[0026] 1. Provide configuration entry for base table, target table and base table association logic;

[0027] 2. It can automatically create add, delete and modify triggers on the base table;

[0028] 3. When the base table data changes, the data before and after the change can be automatically stored, and then the data can be updated into the target table in an asynchronous manner according to the association logic between the base tables. The specific logic is as follows:

[0029] a. When any base table data is newly added, the synchronization program captures the value of the base table's unique key or the combination of values ​​of multiple table unique keys, generates new incremental data based on the value of the unique key or the combination of values ​​of multiple table unique keys according to the association logic between the base tables, and inserts the new incremental data into the destination table;

[0030] b. When any base table data is deleted, the synchronization program captures the base table unique key value or the combination of multiple table unique key values, and deletes all data records associated with it in the target table based on the base table unique key value or the combination of multiple table unique key values;

[0031] c. When any base table data is updated, the synchronization program captures the value of the base table's unique key or the combination of multiple table unique key values, and deletes all data records associated with it in the target table based on the value of the base table's unique key or the combination of multiple table unique key values; at the same time, according to the association logic between the base tables, new incremental data is generated based on the value of the unique key or the combination of multiple table unique key values, and the new incremental data is inserted into the target table.

[0032] D. The program is based on the client and server mode, and provides a message confirmation mechanism after data synchronization to ensure data loss caused by network jitter. At the same time, when the transmission fails, it can be retransmitted based on the previously stored data.

[0033] In a preferred embodiment, the details are as follows:

[0034] The system includes:

[0035] S0: Select the unique key of the base table. For example, the primary keys of table A and table B, A_id and B_id;

[0036] S1: Define the target table. For example, Table C, add auxiliary fields A_id and B_id;

[0037] S2: Input the base table, target table and associated logic. For example, select Aa..,Bb.. from A,B where A_id=B_id and …;

[0038] S3: Automatically create triggers on the base table to monitor changes in base table data;

[0039] S4: Execute data synchronization according to data changes. For specific logic, see step C. If the execution is successful, go to S6. Otherwise, retransmit and go to S5.

[0040] S5: Check the destination table and retransmit based on the recorded old data;

[0041] S6: End.

Claims

1. A method for data incremental synchronization based on multi-table association query, characterized in that The method is specifically as follows: A. Select the unique key of the base table, which is the primary key field or the unique index field. The source table or tables of data synchronization are called base tables, and the target table of data synchronization is called target table. B. Define the target table. In addition to the fields required for normal synchronization, the target table also includes the unique keys of all base tables. C. Establish a data synchronization module. When the base table data changes, the data before and after the change can be automatically stored. Then, in an asynchronous manner, the data is updated into the target table according to the association logic between the base tables. The specific logic is as follows: When any base table data is newly added, the synchronization program captures the value of the base table's unique key or the combination of the values ​​of multiple table unique keys, generates new incremental data based on the unique key value or the combination of the values ​​of multiple table unique keys according to the association logic between the base tables, and inserts the new incremental data into the destination table; When any base table data is deleted, the synchronization program captures the base table unique key value or the combination of multiple table unique key values, and deletes all data records associated with it in the target table based on the base table unique key value or the combination of multiple table unique key values; When any base table data is updated, the synchronization program captures the base table unique key value or the combination of multiple table unique key values, and deletes all data records associated with it in the target table based on the base table unique key value or the combination of multiple table unique key values; at the same time, according to the association logic between the base tables, new incremental data is generated based on the unique key value or the combination of multiple table unique key values, and the new incremental data is inserted into the target table; In the step B, a query index is simultaneously established for the target table.

2. A method for data incremental synchronization based on multi-table association query as described in claim 1, characterized in that The data synchronization module is used to provide configuration entry for the base table, the target table and the base table associated logic, and can automatically establish add, delete and modify triggers on the base table.

3. A method for data incremental synchronization based on multi-table association query as described in claim 1, characterized in that The data synchronization module provides a message confirmation mechanism and a failure retransmission mechanism after data synchronization.

Images