Method for data comparison and data correction in data synchronization

Abstract

The application discloses a data comparison and data correction method in data synchronization, mainly comprising the following steps: S1: configuring a table object to be compared; configuring the size of a comparison data block; configuring a data sampling ratio; S2: checking whether the selected table has a unique key; S3: checking a data synchronization delay time; S4: starting threads to be connected to a source database and a target database respectively; S5: a source data extraction thread obtains a data block from a data snapshot of a selected table of the source database; S6: formatting a data queue; S7: a data checking thread monitors whether the formatted data queue has data; S8: a data correction thread reads a marked data block in the unified data queue and generates a correction SQL; and S9: after the data extraction, data arrangement, data checking and data correction are completed, the task is ended. The application can complete data comparison without suspending the business of the source database, and can provide a data correction SQL, so that the data correction efficiency is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer software, in particular to a data comparison and data correction method in data synchronization. BACKGROUND

[0002] Data is the digital life of various industries, and the core position of the database carrying data in various fields is self-evident. There are many types of databases, each with its own characteristics, which makes the demand for database synchronization extremely strong. In the process of data synchronization, unexpected problems may occur in the data synchronization program and the database system, causing abnormal synchronization of data replication to the peer database, resulting in data inconsistency errors. In scenarios with high requirements for data accuracy, data synchronization inconsistency can cause unpredictable losses. In order to ensure the consistency of the synchronized data, the synchronized data of the two database systems needs to be compared, and when the data inconsistency is checked, the correction result is given.

[0003] At present, many data synchronization tools on the market require that the data consistency function be checked in the data comparison process, and the writing to the source library is suspended. If the source library is in a business peak period, it will cause losses. Many data synchronization tools do not have comprehensive functions, do not support data comparison of heterogeneous databases, and do not provide data correction functions, which makes it necessary for users to manually find all inconsistent data rows even if the data inconsistency problem is found. This process is time-consuming and laborious and is prone to omissions. In addition, after the user finds the inconsistent data rows, they need to carefully compare the contents of each field to find the specific difference field and then decide how to repair the difference, resulting in low data correction efficiency and long time consumption. SUMMARY

[0004] This section is intended to summarize some aspects of the embodiments of the present application and briefly introduce some preferred embodiments. Some simplifications or omissions may be made in this section and the abstract and title of the specification to avoid obscuring the purpose of this section, the abstract and the title, and such simplifications or omissions cannot be used to limit the scope of the present application.

[0005] To solve the above technical problems, the present application provides the following technical solutions: a data comparison and data correction method in data synchronization, mainly comprising the following steps:

[0006] S1: Configure the table object to be compared, referred to as the selected table; configure the size of the comparison data block, referred to as the block size; configure the data sampling ratio;

[0007] S2: Check whether the selected table has a unique key, and eliminate the selected table without a unique key;

[0008] S3: Check the data synchronization delay time, judge whether the incremental data generated by the current source database has been synchronized to the target database, if yes, continue to the next step; if no, wait for a proper time or user to select to force to enter the next step;

[0009] S4: Start threads to connect to the source database and the target database respectively, and obtain the data snapshot of the selected table in the source database and the target database at this moment;

[0010] S5: The source data extraction thread obtains a data block from the data snapshot of the selected table in the source database;

[0011] S6: Format the data queue, and delete the corresponding two data blocks in the original data queue to release the memory;

[0012] S7: The data verification thread monitors whether the formatted data queue has data;

[0013] S8: The data correction thread reads the marked data block in the unified data queue, compares the data line by line, finds the specific data line that is inconsistent, and generates a correction SQL;

[0014] S9: After the data extraction, data arrangement, data verification and data correction are completed, the task is ended.

[0015] As a preferred scheme of the data comparison and data correction method in the data synchronization, wherein: the start thread connected to the source database is called a source data extraction thread, and the start thread connected to the target database is called a target data extraction thread.

[0016] As a preferred scheme of the data comparison and data correction method in the data synchronization, wherein: when a data block is obtained from the data snapshot of the selected table in the source database by the source data extraction thread, whether to check the data block is determined according to the set sampling ratio, if no, the step is repeated; if yes, the boundary data of the data block is transmitted to the target data extraction thread, the target data extraction thread reads the data block in the corresponding selected table snapshot in the target database according to the boundary data, and the two data blocks are delivered to the sequentially stored memory queue, which is called an original data queue, if the original data queue is full, the data extraction work is suspended, and after the space of the original data queue is released, the data extraction work is continued, the source data extraction thread and the target data extraction thread repeat the step until the data snapshot is read.

[0017] As a preferred scheme of the data comparison and data correction method in the data synchronization, wherein: the formatted data queue, that is, the data arrangement thread reads two continuous data blocks in the original data queue each time, arranges the data lines in the two data blocks into a unified data format respectively, and saves them to another memory queue, which is called a formatted data queue.

[0018] As a preferred solution of the data comparison and data correction method in the data synchronization, the data arrangement thread continuously works until all data blocks are completed.

[0019] As a preferred solution of the data comparison and data correction method in the data synchronization, the data verification thread monitors whether there is data in the formatted data queue, and if there is, it takes out two continuous data blocks each time to calculate the checksum, and if the checksum is inconsistent, the data blocks are marked with a completed verification mark and an inconsistency mark; if the checksum is consistent, the data blocks in the formatted data queue are immediately released, reducing the memory usage.

[0020] As a preferred solution of the data comparison and data correction method in the data synchronization, the data verification thread continuously works until all data blocks are completed.

[0021] As a preferred solution of the data comparison and data correction method in the data synchronization, in the S8, the specific correction strategy is:

[0022] Different databases have different syntaxes, and syntax recognition needs to be performed for each data source. For the data missing in the target database, an insert SQL is generated; for the data existing in the target database but inconsistent, an update SQL is generated; for the redundant data of the target database, it is considered as data inserted from other data sources, and a query SQL is generated, which is only used as a prompt. The data block reading is completed, and the data block in the unified data queue is immediately released, reducing the memory usage.

[0023] As a preferred solution of the data comparison and data correction method in the data synchronization, in the S9, if there is inconsistent data in the data verification process, the data correction SQL is presented to the user, and the user decides whether to manually apply the correction SQL to the target database.

[0024] As a preferred solution of the data comparison and data correction method in the data synchronization, the data synchronization is to copy the object structure and data in one database from one database system to another database system; the source database and the target database are the data sending end and the data receiving end in the data synchronization process; the data comparison is to compare whether the data of the source database and the target database involved in the data synchronization process is consistent; the incremental data is the data changed in the source database in the data synchronization process.

[0025] The beneficial effects of the present application are as follows:

[0026] 1. The method proposed in this invention uses a data snapshot obtained at the start of the task during data comparison and correction. It does not involve incremental data generated during the data comparison period. The incremental data of the source database will not affect the comparison task. Therefore, data comparison and correction can be performed at any point in the data synchronization process. The source database does not need to suspend business, which can reduce the impact on business, shorten the time for data correction, and improve the efficiency of data correction.

[0027] 2. The method proposed in this invention allows users to configure the data sampling and comparison ratio. Configuring a suitable sampling ratio can reduce the pressure on the source database and the target database when dealing with large amounts of data.

[0028] 3. The method proposed in this invention supports heterogeneous databases. Data from different types of databases will be organized into a unified data format before comparison.

[0029] 4. The method proposed in this invention has high accuracy. It prioritizes waiting for no synchronization delay before reading data snapshots from the source and target databases for comparison.

[0030] 5. The method proposed in this invention supports data correction. After discovering inconsistent data, it continues to find the inconsistent data records and provides data correction SQL, eliminating the cost of manually searching for specific erroneous data records and manually writing correction SQL. Attached Figure Description

[0031] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. Wherein:

[0032] Fig. 1 This is a flowchart of a data comparison and data correction method in data synchronization according to the present invention.

[0033] Fig. 2 This is a flowchart illustrating the combination of a data comparison and data correction method in data synchronization according to the present invention with the required system. Detailed Implementation

[0034] To make the above-mentioned objects, features and advantages of the present invention more apparent and understandable, the specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0035] Many specific details are set forth in the following description in order to provide a full understanding of the invention. However, the invention may also be practiced in other ways different from those described herein, and those skilled in the art can make similar extensions without departing from the spirit of the invention. Therefore, the invention is not limited to the specific embodiments disclosed below.

[0036] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The phrase "in one embodiment" appearing in different places in this specification does not necessarily refer to the same embodiment, nor is it a single or selective embodiment that is mutually exclusive with other embodiments.

[0037] Secondly, the present invention is described in detail with reference to the schematic diagrams. When detailing the embodiments of the present invention, for ease of explanation, the cross-sectional views illustrating the device structure may be partially enlarged, not according to the usual scale. Furthermore, the schematic diagrams are merely examples and should not limit the scope of protection of the present invention. In addition, actual fabrication should include three-dimensional spatial dimensions of length, width, and depth.

[0038] Reference Figs. 1-2 This invention provides a method for data comparison and data correction in data synchronization, mainly including the following steps:

[0039] S1: Configure the table objects to be compared, called the selected table; configure the size of the comparison data blocks, called the block size; configure the data sampling ratio.

[0040] S2: Check if the selected table has a unique key, and remove the selected table that does not have a unique key.

[0041] S3: Check the data synchronization delay time to determine whether all incremental data generated by the current source database has been synchronized to the target database. If yes, continue to the next step; if not, wait for an appropriate time or the user chooses to force entry to the next step.

[0042] S4: Start threads that connect to the source and target databases respectively, called the source data extraction thread and the target data extraction thread, to obtain data snapshots of the selected tables in the source and target databases at this moment. Because it is comparing data snapshots of the databases at this current moment, subsequent DML operations in the source and target databases will not affect the current data comparison task, so the database does not need to be shut down.

[0043] S5: The source data extraction thread obtains a data block from the data snapshot of the selected table in the source database. According to the set sampling ratio, it is determined whether to check the data block. If not, repeat the step; if yes, the boundary data of the data block is transmitted to the target data extraction thread. The target data extraction thread reads the data block in the corresponding snapshot of the selected table in the target database according to the boundary data, and delivers the two data blocks to the sequentially stored memory queue, which is called the original data queue. If the original data queue is full, the data extraction work is suspended. After the original data queue releases space, the data extraction work continues. The source data extraction thread and the target data extraction thread repeat the step until the data snapshot is read completely.

[0044] S6: The data arrangement thread reads two continuous data blocks in the original data queue each time, arranges the data rows in the two data blocks into a unified data format respectively, and saves them to another memory queue, which is called the formatted data queue. At the same time, the corresponding two data blocks in the original data queue are deleted to release the memory. Because the current mainstream relational databases such as Oracle, SQL Server, PostgreSQL, and MySql support the JDBC specification, the fields of the data rows are loaded into corresponding Java objects according to the data types of the corresponding databases. The boundary conditions of different data types of different databases need special processing, that is, the data format is unified based on the JDBC specification. The data arrangement thread continues to work until all the data blocks are arranged.

[0045] S7: The data verification thread monitors whether there is data in the formatted data queue. If there is, it takes out two continuous data blocks each time to calculate the checksum. If the checksum is inconsistent, the pair of data blocks is marked as having completed verification and inconsistent. If the checksum is consistent, the pair of data blocks in the formatted data queue is immediately released to reduce memory usage. The data verification thread continues to work until all the data blocks are verified.

[0046] S8: The data correction thread reads the marked data block in the unified data queue, compares the data row by row, finds the specific data row that is inconsistent, and generates a correction SQL.

[0047] The specific correction strategy is as follows: the syntax of different databases is different, and syntax recognition needs to be performed for each data source. For the data missing in the target database, an insert SQL is generated. For the data existing in the target database but inconsistent, an update SQL is generated. For the redundant data in the target database, it is considered to be data inserted from other data sources, and a query SQL is generated as a prompt. After the pair of data blocks is read, the pair of data blocks in the unified data queue is immediately released to reduce memory usage.

[0048] S9: After the data extraction, data arrangement, data verification and data correction are completed, the task is ended. If there are inconsistent data, the data correction SQL is presented to the user to let the user decide whether to manually apply the correction SQL to the target database.

[0049] In the above, the data synchronization is to copy the object structure and data in one database from one database system to another database system; the source database and the target database are the data sending end and the data receiving end in the data synchronization process; the data comparison is to compare whether the data of the source database and the target database involved in the data synchronization process are consistent; the incremental data is the data changed in the source database in the data synchronization process.

[0050] The system required for using the data comparison and data correction method in the above data synchronization includes a data extraction node, a data arrangement node, a data verification node and a data correction node.

[0051] The data extraction node is used to establish a connection with the source database and the target database, capture the data snapshot in the database at this moment, traverse the data snapshot, obtain the original data of the source and target databases in blocks, and deliver the original data to the original data queue.

[0052] The data arrangement node is used to take out two continuous original data blocks from the original data queue each time, arrange each data record in the original data blocks into a unified format data according to the JDBC specification, obtain a pair of data blocks in the unified format, deliver the data blocks to the formatted data queue, and immediately delete the corresponding data blocks in the original data queue.

[0053] The data verification node is used to traverse the data blocks in the formatted data queue, take out two continuous data blocks each time, calculate the verification values of the data blocks, and if the verification values are consistent, immediately delete the two data blocks in the formatted data queue; if the verification values are inconsistent, mark the two data blocks.

[0054] The data correction node is used to traverse the data blocks in the formatted data queue, check whether there is a mark, take out two continuous data blocks if there is a mark, compare the data records in the two data blocks row by row, find the specific inconsistent data records, and generate a repair SQL.

[0055] To sum up, the method provided by the application uses the data snapshot obtained at the beginning of the task during data comparison and correction, does not involve the incremental data generated during the data comparison, and the incremental data of the source database does not affect the comparison task, so the data comparison and correction can be performed at any time point in the data synchronization process, the source database does not need to pause the business, the influence on the business can be reduced, the time for correcting the data is shortened, and the efficiency of data correction is improved. Then, in the use process, the user can flexibly configure the proportion of data spot check comparison, and in the case of large data, configuring a more appropriate spot check proportion can reduce the pressure of the source database and the target database, and the comparison of heterogeneous databases is supported and the data correction is supported.

[0056] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present application but not limit the present application. Although the present application has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solutions of the present application can be modified or replaced equivalently without departing from the spirit and scope of the technical solutions of the present application, and they should be covered in the scope of the claims of the present application.

Claims

1. A method for data comparison and data correction in data synchronization, characterized in that, The method comprises the following steps: S1: configuring a table object to be compared with data, referred to as a selected table; configuring the size of a data block to be compared, referred to as a block size; and configuring a data sampling ratio; S2: checking whether the selected table has a unique key, and eliminating the selected table without a unique key; S3: checking a data synchronization delay time, judging whether the incremental data generated by a current source database has been completely synchronized to a target database, if yes, continuing to the next step; if no, waiting for a proper time or a user to select to forcibly enter the next step; S4: starting threads to be connected to the source database and the target database respectively, and obtaining data snapshots of the selected tables in the source database and the target database at this moment; S5: a source data sampling thread obtains a data block from the data snapshot of the selected table in the source database; When the source data sampling thread obtains a data block from the data snapshot of the selected table in the source database, it is determined according to the set sampling ratio whether to check the data block, if no, repeating the step; if yes, the boundary data of the data block is transmitted to a target data sampling thread, the target data sampling thread reads the data block in the corresponding selected table snapshot in the target database according to the boundary data, and delivers the two data blocks to a sequentially stored memory queue, referred to as an original data queue, if the original data queue is full, the data sampling work is suspended, and the data sampling work is continued after the original data queue releases space, the source data sampling thread and the target data sampling thread repeat the step until the data snapshot is read completely; S6: formatting the data queue, and deleting the corresponding two data blocks in the original data queue to release the memory; The formatted data queue, that is, a data arrangement thread reads two continuous data blocks in the original data queue each time, arranges the data rows in the two data blocks into a unified data format respectively, and saves the data rows into another memory queue, referred to as a formatted data queue; S7: a data checking thread monitors whether the formatted data queue has data; The data checking thread monitors whether the formatted data queue has data, if yes, it takes out two continuous data blocks each time to calculate a checksum, if the checksum is inconsistent, the two continuous data blocks are marked with a completed checking mark and an inconsistency mark; If the checksum is consistent, the two data blocks in the formatted data queue are immediately released to reduce the memory usage; S8: a data correction thread reads the marked data block in the unified data queue, compares the data row by row, finds the specific data row which is inconsistent, and generates a correction SQL; In S8, the specific correction strategy is as follows: The syntaxes of different databases are different, syntax recognition needs to be performed for each data source, for the data missing in the target database, an insertion SQL is generated; for the data existing in the target database but inconsistent, an update SQL is generated; for the redundant data in the target database, it is considered as the data inserted from other data sources, a query SQL is generated, only as a prompt, the two continuous data blocks in the unified data format in the unified data queue are immediately released after the two continuous data blocks are read, to reduce the memory usage; S9: after the data sampling, data arrangement, data checking and data correction work are completed, the task is ended.

2. The method of claim 1, wherein: The start thread connects to the source database and is called the source data extraction thread, and the start thread connects to the target database and becomes the target data extraction thread.

3. The method of claim 1, wherein: the data comparison and data revision in the data synchronization is performed by a data comparison and data revision module. The data arrangement thread continues to work until all data blocks are arranged.

4. The method of claim 1, wherein: the data comparison and data revision in the data synchronization is performed by a data comparison and data revision module. The data verification thread continues to work until all data blocks are verified.

5. The method of claim 1, wherein: the data comparison and data revision in the data synchronization is performed by a data comparison and data revision module. In the S9, if there are inconsistent data in the data verification process, the data correction SQL is presented to the user to let the user decide whether to manually apply the correction SQL to the target database.

6. The method of claim 1, wherein: The data synchronization is to copy the object structure and data in one database from one database system to another database system; the source database and the target database are the data sending end and the data receiving end in the data synchronization process; the data comparison is to compare whether the data of the source database and the target database involved in the data synchronization process is consistent; the incremental data is the data changed in the source database in the data synchronization process.

Images