Method and device for ensuring consistency of data extracted from source database to target database

Abstract

The present invention discloses a method and device for ensuring the consistency of data extracted from a source database to a target database. The method includes the following steps: before extracting data from the source database, obtaining the update identifiers of all source tables on the database for the first time, which are respectively recorded as first identifiers; after a preset time interval, obtaining the update identifiers of all source tables on the database for the second time, which are respectively recorded as second identifiers; comparing the first identifiers with the second identifiers, and if the two are the same, extracting the data into a memory; obtaining the update identifiers of all source tables on the database for the third time, which are recorded as third identifiers; comparing the second identifiers with the third identifiers, and if the two are the same, updating the source table data in the memory to the target database. The present invention adopts a method of checking the source table update identifiers three times to determine whether the source table has been updated during the data extraction process. This can avoid extracting the data of the source table into the memory or the target database while the source table is being updated, thereby improving the success rate of data extraction and reducing system resource consumption.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method and device for ensuring consistency of data extracted from a source database to a target database. Background Art

[0002] Industries like finance and data analysis often need to extract data from a source database into a target database, then perform operations based on the target database's data to ensure the security of the source database. However, due to the irregular update schedule and frequency of data in the source database, data transfer often encounters issues with the data being updated in the source database, leading to data issues in the target database. To address this issue, the following methods are commonly used:

[0003] 1. Compare the number of records before and after the transfer. If the number of records changes, it means that the data in the source database has changed during the data transfer process.

[0004] 2. Compare the hash values ​​of the data content before and after the transfer. If the hash value changes, it means that the data in the source database has changed during the data transfer process.

[0005] Both of the above methods have certain shortcomings. The method of comparing the number of records is simple and consumes less system resources, but it cannot detect updates where the record content has changed, and can only detect updates where the number of records has changed. The method of comparing hash values ​​can solve the defect of the record number method that cannot detect content updates on the source table. However, since this method requires loading the data result set into memory multiple times (at least 2 times) and then calculating the hash value of the result set, it consumes a lot of system resources. Especially when the data result set is large and the source table is updated frequently, the success rate of extracting data is very low, resulting in a waste of resources.

[0006] In view of this, there is an urgent need to improve the existing method of ensuring the consistency of data extracted from the source database to the target database, so as to ensure the success rate of data extraction and reduce system resource consumption. Summary of the Invention

[0007] In view of the above defects, the technical problem to be solved by the present invention is to provide a method and device for ensuring the consistency of data extracted from a source database to a target database, so as to solve the problems of low success rate of data extraction and waste of resources in existing methods.

[0008] To this end, the present invention provides a method for ensuring the consistency of data extracted from a source database to a target database, comprising the following steps:

[0009] Before extracting data from the source database, the update identifiers of all source tables are obtained from the source database for the first time, which are respectively recorded as the first identifiers;

[0010] After a preset time interval from the time when the first identifier is obtained, update identifiers of all source tables are obtained from the source database for the second time, which are respectively recorded as second identifiers;

[0011] Compare the first identifier and the second identifier of each source table. If they are the same, extract the data on the source database into the memory according to the data extraction logic; otherwise, exit.

[0012] The third time, the update flags of all source tables are obtained from the source database, which is recorded as the third flag.

[0013] Compare the corresponding second identifier and the third identifier. When they are the same, write the data in the memory into the target database; otherwise, exit.

[0014] In the above method, preferably, the source database is an ORACLE database, and the first identifier, the second identifier and the third identifier are system change numbers (SCNs) respectively.

[0015] In the above method, preferably, a new trigger is created in the source database, and each time a source table of the source database is updated, the trigger is triggered to record an update record of the source table, and the update record forms the first identifier, the second identifier or the third identifier.

[0016] In the above method, preferably, the preset time is 5-10 seconds.

[0017] The present invention also provides a device for ensuring the consistency of data extracted from a source database to a target database, comprising:

[0018] an identifier acquisition module, configured to acquire first, second, and third identifiers of all source tables on a source database; wherein the first identifier is an update identifier of all source tables acquired from the source database for the first time before extracting data from the source database; the second identifier is an update identifier of all source tables acquired from the source database for the second time after a preset time interval from acquiring the first identifier; and the third identifier is an update identifier of all source tables acquired from the source database for the third time after extracting data from the database into memory;

[0019] a comparison module, configured to compare the first identifier and the second identifier of each source table to obtain a first result; and compare the second identifier and the third identifier of each source table to obtain a second result;

[0020] an extraction module, configured to extract the data on the source database into a memory according to a data extraction logic based on the first result being the same;

[0021] An update module is configured to write the data in the memory into a target database according to the second result being the same.

[0022] In the above device, preferably, the source database is an ORACLE database, and the first identifier, the second identifier and the third identifier are system change numbers (SCNs) respectively.

[0023] In the above device, preferably, the source database is provided with a trigger, and each time a source table of the source database is updated, the trigger is triggered to record an update record of the source table, and the update record forms the first identifier, the second identifier or the third identifier.

[0024] In the above device, preferably, the preset time is 5-10 seconds.

[0025] As can be seen from the above technical solution, the present invention provides a method and device for ensuring the consistency of data extracted from a source database to a target database, which solves the problem of low data extraction success rate and waste of resources in existing methods. Compared with the existing technology, the present invention has the following beneficial effects:

[0026] The system checks the source table update flag three times to determine whether the source table has been updated during the data extraction process. The first comparison verifies whether the source table is currently being updated, and the second comparison verifies whether the source table has been updated during the data extraction process. This can avoid extracting data into memory or the target database even when the source table is being updated, greatly improving the success rate of data extraction and reducing system resource consumption. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] To more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces and describes the drawings required for use in the embodiments of the present invention or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present invention, and those skilled in the art can derive other drawings based on these drawings without inventive effort.

[0028] Figure 1 A flow chart of a method provided by the present invention for ensuring the consistency of data extracted from a source database to a target database. DETAILED DESCRIPTION

[0029] The following will clearly and completely describe the technical solutions of the embodiments of the present invention in conjunction with the accompanying drawings of the embodiments of the present invention. Obviously, the embodiments described below are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts shall fall within the scope of protection of the present invention.

[0030] The implementation principle of the present invention is: using the method of checking the source table update mark three times to determine whether the source table is updated during the data extraction process, it can avoid extracting its data into the memory or target library while the source table is being updated, greatly improving the success rate of data extraction and reducing system resource consumption.

[0031] In order to more clearly explain and illustrate the technical solution and implementation of the present invention, several preferred specific embodiments for implementing the technical solution of the present invention are introduced below.

[0032] It should be noted that the directional words such as "inside, outside", "front, back" and "left, right" in this article are expressed based on the product usage status. Obviously, the use of the corresponding directional words does not constitute a limitation on the scope of protection of this scheme.

[0033] See Figure 1 , Figure 1 A flow chart of a method provided by the present invention for ensuring the consistency of data extracted from a source database to a target database.

[0034] like Figure 1 As shown, the present invention provides a method for ensuring the consistency of data extracted from a source database to a target database, comprising the following steps:

[0035] Step 110 , before extracting data from the source database, obtain update identifiers of all source tables from the source database for the first time, which are respectively recorded as first identifiers.

[0036] Step 120 : After obtaining the first identifier, at a preset time interval, obtain the update identifiers of all source tables from the source database for the second time, which are respectively recorded as second identifiers.

[0037] The preset time interval is set to 5-10 seconds.

[0038] Step 130 , compare the first identifier and the second identifier of each source table. If they are the same, execute step 140 to extract the data from the source database into the memory according to the data extraction logic; otherwise, exit without extracting data from the source database.

[0039] Step 150 , after extracting the data from the source database into the memory, obtain the update identifiers of all source tables from the database for the third time, which is recorded as the third identifier.

[0040] Step 160 , compare the corresponding second identifier and third identifier. If they are the same, execute step 170 to write the source table data in the memory into the target database. Otherwise, exit, abandon the source table data in the memory, and do not write the data in the memory into the target database.

[0041] This method only requires obtaining the source database's update flag three times and comparing the two update flag values ​​to verify whether the source table in the source database has been updated during the data extraction process. The first comparison is primarily used to verify whether the source table was already in an updated state before data extraction. If so, subsequent processes are unnecessary, avoiding the extraction of useless data into memory and wasting system resources. The second comparison is primarily used to determine whether the source table was updated during the data extraction process according to the data extraction logic, thus preventing the extraction of useless data into the target database.

[0042] The method of the present invention only needs to extract data into the memory once, and can effectively detect update operations in which the content has changed.

[0043] The solution provided in this application can be applied to ORACLE databases and other databases. For ORACLE databases, SCN is used as the first identifier, the second identifier, and the third identifier.

[0044] An SCN, or System Change Number, is a timestamp that defines a committed version of a database at a specific point in time. Oracle assigns a unique SCN to each committed transaction. The SCN value represents the logical point in time when a change was made to the database, and Oracle uses this number to record changes made to the database.

[0045] Starting with Oracle 10G, a pseudo column, ORA_ROWSCN, is available in two modes: block-based, which is the default mode; and row-based, which can only be used by specifying ROWDEPENDENCIES when creating a table and cannot be used through subsequent alter table operations. This also places a performance burden on the database.

[0046] Oracle records the SCN of the most recent transaction in each block (row) in the header of each block (row). When the system inserts a record into a table, it inserts the current database SCN value into the header of the block (row). Therefore, you can determine whether the table has been updated by checking whether the maximum SCN value has changed. Also, because the SCN value is recorded in the block header, if the table is empty, obtaining the SCN value will result in an error. This method can also be used to determine whether the source table is empty.

[0047] For other databases, since they do not provide the SCN function, you can create a new trigger in the source table to record each update record of the source table. That is, create a new trigger in the source database. Every time the source table is updated, the system triggers the corresponding trigger and records the update record of the source table. The update record forms the first identifier, second identifier, or third identifier.

[0048] Based on the above method, the present application also provides a device for ensuring the consistency of data extracted from a source database to a target database, including:

[0049] an identifier acquisition module, configured to acquire first, second, and third identifiers of all source tables on a source database; wherein the first identifier is an update identifier of all source tables acquired from the source database for the first time before extracting data from the source database; the second identifier is an update identifier of all source tables acquired from the source database for the second time at a preset interval after acquiring the first identifier; and the third identifier is an update identifier of all source tables acquired from the source database for the third time after extracting data from the database into memory;

[0050] a comparison module, configured to compare the first identifier and the second identifier of each source table to obtain a first result; and compare the second identifier and the third identifier of each source table to obtain a second result;

[0051] an extraction module, configured to extract the data on the source database into a memory according to a data extraction logic based on the first result being the same;

[0052] An update module is configured to write the data in the memory into a target database according to the second result being the same.

[0053] Based on the description of the above specific embodiments, the method and apparatus for ensuring consistency of data extracted from a source database to a target database provided by the present invention have the following advantages over the prior art:

[0054] First, you only need to obtain the update identifier of the source database three times and compare the two update identifier values ​​to check whether the source table has been updated during the data extraction process. This is convenient and quick.

[0055] Second, if the first comparison indicates that the source table is in an updated state, there is no need to execute subsequent processes, avoiding the extraction of useless data and wasting system resources.

[0056] Finally, it should be noted that the terms "comprise," "include," or any other variations thereof, as used herein, are intended to encompass non-exclusive inclusion, such that a process, method, article, or apparatus comprising a list of elements includes not only those elements but also other elements not explicitly listed, or elements inherent to such process, method, article, or apparatus. In the absence of further limitations, an element defined by the phrase "comprising a..." does not preclude the presence of additional identical elements in the process, method, article, or apparatus comprising the element.

[0057] The present invention is not limited to the above-mentioned optimal implementation mode. Anyone should be aware that any structural changes made under the inspiration of the present invention, and any technical solutions that are the same or similar to the present invention, fall within the scope of protection of the present invention.

Claims

1. A method for ensuring the consistency of data extracted from a source database to a target database, characterized in that: The following steps are involved: Before extracting data from the source database, the update identifiers of all source tables are obtained from the source database for the first time, which are respectively recorded as the first identifiers; After obtaining the first identifier, at a preset time interval, obtain update identifiers of all source tables from the source database for the second time, which are respectively recorded as second identifiers; Compare the first identifier and the second identifier of each source table. If they are the same, extract the data on the source database into the memory according to the data extraction logic; otherwise, exit. The third time, the update flags of all source tables are obtained from the source database, which is recorded as the third flag. Comparing the corresponding second identifier with the third identifier, and if they are the same, writing the data in the memory into the target database; otherwise, exiting; Among them, when the source database is an ORACLE database, the first identifier, the second identifier and the third identifier are respectively the system change number SCN. Each time the system inserts a record into the source table, the SCN of the ORACLE database is inserted into the header of the ORACLE Block through the block mode of the pseudo column ORA_ROWSCN provided by Oracle, and whether the source table is empty is determined by whether an SCN error is reported. When the source database is other databases that do not support the SCN function, a new trigger is used to record each update record of the source table, and the update record forms the first identifier, the second identifier or the third identifier.

2. The method according to claim 1, characterized in that The preset time is 5-10 seconds.

3. A device for ensuring the consistency of data extracted from a source database to a target database, characterized in that: include: an identifier acquisition module, configured to acquire first, second, and third identifiers of all source tables on a source database; wherein the first identifier is an update identifier of all source tables acquired from the source database for the first time before extracting data from the source database; the second identifier is an update identifier of all source tables acquired from the source database for the second time at a preset interval after acquiring the first identifier; and the third identifier is an update identifier of all source tables acquired from the source database for the third time after extracting data from the database into memory; a comparison module, configured to compare the first identifier and the second identifier of each source table to obtain a first result; and compare the second identifier and the third identifier of each source table to obtain a second result; an extraction module, configured to extract the data on the source database into a memory according to a data extraction logic based on the first result being the same; An updating module, configured to write the data in the memory into a target database according to the second result being the same; Among them, when the source database is an ORACLE database, the first identifier, the second identifier and the third identifier are respectively the system change number SCN. Each time the system inserts a record into the source table, the SCN of the ORACLE database is inserted into the header of the ORACLE Block through the block mode of the pseudo column ORA_ROWSCN provided by Oracle, and whether the source table is empty is determined by whether an SCN error is reported. When the source database is other databases that do not support the SCN function, a new trigger is used to record each update record of the source table, and the update record forms the first identifier, the second identifier or the third identifier.

4. The device according to claim 3, characterized in that The preset time is 5-10 seconds.

Images