Multi-source heterogeneous data incremental synchronization method and system, storage medium and electronic device

Abstract

The application provides a multi-source heterogeneous data incremental synchronization method and system, a storage medium and an electronic device, and relates to the technical field of data processing. The application marks data for addition, update and deletion by comparing the primary key values and hash values of the fields in the source database and the target database, and performs addition, update and deletion operations on the target table according to the addition, update and deletion marks. The method does not need to consider log records, and can realize multi-source heterogeneous data incremental synchronization regardless of whether the log records of the source database are opened. Meanwhile, the application does not need to synchronize full data, but only needs to synchronize the changed data in the source database to the target database, so as to reduce the reading and writing of the database and improve the performance.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and specifically to a method, system, storage medium, and electronic device for incremental synchronization of multi-source heterogeneous data. Background Technology

[0002] With the development of big data, data extraction and aggregation have become an indispensable part of big data platforms. This process involves merging data from different data sources. Because these data sources use different database types, data synchronization cannot be achieved solely through database logs. For example, MySQL database logs cannot be used with Oracle, and Oracle database logs cannot be used with KingBase.

[0003] Existing incremental synchronization technologies generally rely on log records in the database for incremental synchronization. This synchronization method requires the source database to have its log records enabled to proceed, which imposes many limitations on the database during incremental synchronization and is not conducive to the development of incremental synchronization of multi-source heterogeneous data.

[0004] As described above, existing incremental synchronization technologies have the technical problem that logging must be enabled before incremental synchronization can be performed. Summary of the Invention

[0005] (a) Technical problems to be solved

[0006] To address the shortcomings of existing technologies, this invention provides a method, system, storage medium, and electronic device for incremental synchronization of multi-source heterogeneous data, solving the technical problem that existing incremental synchronization technologies require logging to be enabled for incremental synchronization.

[0007] (II) Technical Solution

[0008] To achieve the above objectives, the present invention provides the following technical solution:

[0009] In a first aspect, the present invention provides a method for incremental synchronization of multi-source heterogeneous data, comprising:

[0010] S1. Obtain and select the source database, the source table in the source database, and the fields in the source table that are used as source data; obtain and select the target database, the target table in the target database, and the target fields in the target table that need to be synchronized.

[0011] S2. Select the primary key comparison fields from the source data fields and the target data fields respectively, and obtain the primary key values ​​of the primary key comparison fields;

[0012] S3. Query all data in the source table and the target table respectively, and read the hash value of each data in the target table;

[0013] S4. Calculate the hash value of each data entry in the source table;

[0014] S5. Compare each data entry in the source table with the primary key value and hash value of each data entry in the target table to obtain different comparison results. Then, mark the data as added, updated, or deleted based on the comparison results.

[0015] S6. Based on the add, update, and delete flags, perform add, update, and delete operations on the target table respectively.

[0016] Preferably, the source database and the target database are of the same type, or the source database and the target database are of different types.

[0017] Preferably, S3 specifically includes:

[0018] The system reads data from the selected source database, tables, and fields and loads it into memory. When the data volume is large, it reads the data in batches.

[0019] The system reads data from the selected target database, table, and fields, and also reads the hash value of each data entry in the target table.

[0020] Preferably, S4 specifically includes:

[0021] Each piece of data loaded into memory from the source table is converted into a string value according to its different field types, and the entire data is concatenated into a large string. The hash value of the large string is then calculated.

[0022] Preferably, S5 specifically includes:

[0023] Each data entry in the source table is compared with the primary key value of each data entry in the target table to determine if the primary key value of each data entry in the source table exists in the data in the target table. If not, the data entry is marked as newly added. If so, the hash value of the data entry is further checked to see if it is equal to the hash value in the target table. If they are equal, the data entry is marked as unchanged; otherwise, the data entry is marked as updated.

[0024] After all data in the source table has passed the above checks, if there is any extra data in the target table that does not match, then the extra data will be marked for deletion.

[0025] Preferably, S6 specifically includes:

[0026] Once the data from the source database has been loaded into memory and marked, insert the data from the source table into the target table, update the target table, and delete any redundant data from the target table.

[0027] Preferably, when inserting data loaded from the source table into memory into the target table, a hash value is also inserted; when updating data loaded from the source table into the target table, the hash field is also modified.

[0028] Secondly, the present invention provides a multi-source heterogeneous data incremental synchronization system, comprising:

[0029] The Get and Select module is used to get and select the source database, the source table in the source database, and the fields in the source table that are used as source data; and to get and select the target database, the target table in the target database, and the target fields in the target table that need to be synchronized.

[0030] The primary key selection module is used to select the primary key comparison field from the source data field and the target data field respectively, and to obtain the primary key value of the primary key comparison field.

[0031] The query module is used to query all data in the source table and the target table respectively, and to read the hash value of each data record in the target table;

[0032] The hash value calculation module is used to calculate the hash value of each piece of data in the source table;

[0033] The comparison and marking module is used to compare each piece of data in the source table with the primary key value and hash value of each piece of data in the target table, obtain different comparison results, and mark the data as added, updated, or deleted based on the comparison results.

[0034] The operation module is used to perform add, update, and delete operations on the target table according to the add, update, and delete tags, respectively.

[0035] Thirdly, the present invention provides a storage medium storing a computer program for incremental synchronization of multi-source heterogeneous data, wherein the computer program causes a computer to execute the multi-source heterogeneous data incremental synchronization method as described above.

[0036] Fourthly, the present invention provides an electronic device, comprising:

[0037] One or more processors;

[0038] Memory; and

[0039] One or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the programs including methods for performing incremental synchronization of multi-source heterogeneous data as described above.

[0040] (III) Beneficial Effects

[0041] This invention provides a method, system, storage medium, and electronic device for incremental synchronization of multi-source heterogeneous data. Compared with existing technologies, it has the following advantages:

[0042] This invention marks data as added, updated, or deleted by comparing the primary key values ​​and hash values ​​of fields in the source and target databases. Based on these marks, the target table performs the corresponding add, update, and delete operations. This method eliminates the need for logging, enabling incremental synchronization of heterogeneous data from multiple sources regardless of whether logging is enabled in the source database. Furthermore, this invention does not require full data synchronization; only data that has changed in the source database is synchronized to the target database, reducing database read / write operations and improving performance. Attached Figure Description

[0043] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art 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.

[0044] Figure 1 This is a block diagram of a multi-source heterogeneous data incremental synchronization method according to an embodiment of the present invention;

[0045] Figure 2 This is a flowchart of a multi-source heterogeneous data incremental synchronization method according to an embodiment of the present invention. Detailed Implementation

[0046] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention are described clearly and completely. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0047] This application provides a method, system, storage medium, and electronic device for incremental synchronization of multi-source heterogeneous data. Existing incremental synchronization technologies have the technical problem that logging must be enabled for incremental synchronization. This application enables incremental extraction regardless of whether logging is enabled in the source database, and also achieves incremental synchronization across data types.

[0048] The technical solution in this application is to solve the above-mentioned technical problems, and the general idea is as follows:

[0049] Existing incremental synchronization technologies typically rely on database log records for incremental synchronization. This method requires logging to be enabled in the source database to proceed. Furthermore, for this log-based synchronization, the target database must be of the same or similar type as the source database. Therefore, if there are many different types of source databases, the target database must match their type. Moreover, this method of synchronizing data through log records has limitations and is not comprehensive; not all types of databases have logging capabilities.

[0050] Through the embodiments of the present invention, the problem of incremental extraction can be performed regardless of whether the source database has log recording enabled. On the other hand, it solves the problem that incremental synchronization can be performed on the same type of database or even across database types, as long as the MD5 hash value can be calculated, regardless of the database type.

[0051] To better understand the above technical solutions, the following will provide a detailed explanation of the technical solutions in conjunction with the accompanying drawings and specific implementation methods.

[0052] Example:

[0053] like Figure 1 As shown, this embodiment of the invention provides a method for incremental synchronization of multi-source heterogeneous data, including:

[0054] S1. Obtain and select the source database, the source table in the source database, and the fields in the source table that are used as source data; obtain and select the target database, the target table in the target database, and the target fields in the target table that need to be synchronized.

[0055] S2. Select the primary key comparison fields from the source data fields and the target data fields respectively, and obtain the primary key values ​​of the primary key comparison fields;

[0056] S3. Query all data in the source table and the target table respectively, and read the hash value of each data in the target table;

[0057] S4. Calculate the hash value of each data entry in the source table;

[0058] S5. Compare each data entry in the source table with the primary key value and hash value of each data entry in the target table to obtain different comparison results. Then, mark the data as added, updated, or deleted based on the comparison results.

[0059] S6. Based on the add, update, and delete flags, perform add, update, and delete operations on the target table respectively.

[0060] The following is a detailed explanation of each step:

[0061] A flowchart of a multi-source heterogeneous data incremental synchronization method according to an embodiment of the present invention is shown below. Figure 2 As shown.

[0062] In step S1, the source database, the source table in the source database, and the fields in the source table used as source data are obtained and selected; the target database, the target table in the target database, and the target fields in the target table that need to be synchronized are also obtained and selected. The specific implementation process is as follows:

[0063] Get and select the source database as the source data, then select the table in the source database, and finally select the specific field in the table.

[0064] Select the target database from which you want to synchronize data, select the table in that database, and then select the specific field in that table.

[0065] In specific implementations, different types of databases are selected to allow users flexibility and a wide range of choices, such as Oracle, MySQL, Kingbase, and DaMeng, enabling synchronization between similar or different databases. Furthermore, there is no limit to the number of source tables. Provided that the total number of fields in the selected source tables is equal to the total number of fields in the target table (excluding extended fields such as hash value, batch, operation method, creation time, update time, and data source information), fields can be selected from multiple source tables.

[0066] You can select only some fields in the table. If you have selected both the source and target tables, you can select only some fields to synchronize. The number of fields in the source and target tables should be equal (excluding extended fields such as hash value, batch, operation method, creation time, update time, and data source information).

[0067] In step S2, fields are selected as primary key comparison fields from the source and target fields, respectively, and the primary key values ​​of these fields are obtained. The specific implementation process is as follows:

[0068] Select certain fields from the specific fields in the source table and the target fields in the target table as primary key comparison fields, and obtain the primary key values ​​of these two primary key comparison fields.

[0069] In this embodiment of the invention, when the primary key comparison field is determined as the primary key, there are generally three ways to determine its value:

[0070] 1. Entirely determined by the database. 2. Partially dependent on the database. 3. Primary key values ​​are generated entirely on the client side.

[0071] In practice, all three methods can determine the primary key value of the primary key comparison field. When handling two fields used as primary key comparison fields, the determination method should remain consistent. These three methods are existing technologies and will not be elaborated upon here.

[0072] In step S3, all data in both the source and target tables are queried, and the hash value of each data entry in the target table is read. The specific implementation process is as follows:

[0073] The system reads data from the selected source database, tables, and fields and loads it into memory. If the data volume is large, it can be read in batches. It then reads data from the selected target database, tables, and fields, including the hash value of each record in the target table.

[0074] In step S4, the hash value of each data entry in the source table is calculated. The specific implementation process is as follows:

[0075] Each piece of data loaded into memory from the source table is converted into a string value according to its different field types, and the entire data is concatenated into a large string. Then, the hash value of the large string is calculated.

[0076] In step S5, each data entry in the source table is compared with the primary key value and hash value of each data entry in the target table. Different comparison results are obtained, and data is marked as added, updated, or deleted based on these results. The specific implementation process is as follows:

[0077] Each record in the source table is compared with the primary key value of each record in the target table to determine if the primary key value of each record in the source table exists in the target table. If not, the record is marked as newly added. If so, the hash value of the record is further checked to see if it is equal to the hash value in the target table. If they are equal, the record is marked as unchanged; otherwise, the record is marked as updated.

[0078] After all data in the source table has passed the above checks, if there is any extra data in the target table that does not match, then the extra data will be marked for deletion.

[0079] In step S6, based on the markers completed in S5, add, update, and delete operations are performed on the target table. The specific implementation process is as follows:

[0080] In practice, after the data from the source database has been loaded into memory and marked, the data from the source table loaded into memory is inserted into the target table, updated in the target table, and redundant data is deleted from the target table.

[0081] Specifically, when inserting into the target table, the hash value is inserted synchronously; when updating the target table, the hash field is modified synchronously.

[0082] It should be noted that there may be other embodiments of the present invention. Without departing from the spirit and essence of the present invention, those skilled in the art can make various corresponding changes and modifications according to the present invention. For example, by using this method to classify and mark target data, the field values ​​(batch, operation method, creation time, update time, data source information, etc.) in addition to hash values ​​can be expanded to trace the source information; the full field values ​​of the data in the source table and the expanded fields (batch, operation method, creation time, update time, data source information, etc.) can also be saved to the big data to record the log of each data operation information.

[0083] This invention also provides a multi-source heterogeneous data incremental synchronization system, the system comprising:

[0084] The Get and Select module is used to get and select the source database, the source table in the source database, and the fields in the source table that are used as source data; and to get and select the target database, the target table in the target database, and the target fields in the target table that need to be synchronized.

[0085] The primary key selection module is used to select the primary key comparison field from the source data field and the target data field respectively, and to obtain the primary key value of the primary key comparison field.

[0086] The query module is used to query all data in the source table and the target table respectively, and to read the hash value of each data record in the target table;

[0087] The hash value calculation module is used to calculate the hash value of each piece of data in the source table;

[0088] The comparison and marking module is used to compare each piece of data in the source table with the primary key value and hash value of each piece of data in the target table, obtain different comparison results, and mark the data as added, updated, or deleted based on the comparison results.

[0089] The operation module is used to perform add, update, and delete operations on the target table according to the add, update, and delete tags, respectively.

[0090] It is understood that the heterogeneous data incremental synchronization system provided in this embodiment of the invention corresponds to the above-mentioned multi-source heterogeneous data incremental synchronization method. The explanation, examples, and beneficial effects of the relevant content can be referred to the corresponding content in the multi-source heterogeneous data incremental synchronization method, and will not be repeated here.

[0091] This invention also provides a computer-readable storage medium storing a computer program for incremental synchronization of heterogeneous data, wherein the computer program causes a computer to execute the multi-source heterogeneous data incremental synchronization method as described above.

[0092] This invention also provides an electronic device, comprising:

[0093] One or more processors;

[0094] Memory; and

[0095] One or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the programs including methods for performing incremental synchronization of multi-source heterogeneous data as described above.

[0096] In summary, compared with existing technologies, it has the following beneficial effects:

[0097] 1. This invention compares the primary key values ​​and hash values ​​of fields in the source and target databases to mark data as added, updated, or deleted. Based on these marks, the target table is then processed for the corresponding addition, update, or deletion operations. This method eliminates the need for logging, enabling incremental synchronization of heterogeneous data from multiple sources regardless of whether logging is enabled in the source database. Furthermore, this invention does not require full data synchronization; only data that has changed in the source database is synchronized to the target database, reducing database read / write operations and improving performance.

[0098] 2. In the primary key comparison process of this invention, the primary key value is compared first. If the primary key value does not exist, it is considered newly added data. If the primary key value exists, the hash value is compared next. If the hash values ​​are equal, it is considered that there has been no change; if the hash values ​​are not equal, it is considered that the data has changed and is marked as updated data. Any redundant data in the target table is considered data that needs to be deleted. The comparison logic is simple, can be operated in memory, does not require frequent database reads, and data is added to the database incrementally.

[0099] 3. The embodiments of the present invention can select different types of databases, allowing users to make flexible and wide-ranging choices, such as Oracle, MySQL, KingBase, DaMeng, etc., for synchronization of the same type of database or different types of databases.

[0100] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0101] The above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for incremental synchronization of multi-source heterogeneous data, characterized in that, include: S1. Obtain and select the source database, the source table in the source database, and the fields in the source table that are used as source data; Get and select the target database, the target table in the target database, and the target fields in the target table that need to be synchronized; S2. Select the primary key comparison fields from the source data fields and the target data fields respectively, and obtain the primary key values ​​of the primary key comparison fields; S3. Query all data in the source table and the target table respectively, and read the hash value of each data in the target table; S4. Calculate the hash value of each data entry in the source table; S5. Compare each data entry in the source table with the primary key value and hash value of each data entry in the target table to obtain different comparison results. Then, mark the data as added, updated, or deleted based on the comparison results. S6. Based on the add, update, and delete flags, perform add, update, and delete operations on the target table respectively.

2. The multi-source heterogeneous data incremental synchronization method as described in claim 1, characterized in that, The source database and the target database are either of the same type or of different types.

3. The multi-source heterogeneous data incremental synchronization method as described in claim 1, characterized in that, S3 specifically includes: The system reads data from the selected source database, tables, and fields and loads it into memory. When the data volume is large, it reads the data in batches. The system reads data from the selected target database, table, and fields, and also reads the hash value of each data entry in the target table.

4. The multi-source heterogeneous data incremental synchronization method as described in claim 1, characterized in that, S4 specifically includes: Each piece of data loaded into memory from the source table is converted into a string value according to its different field types, and the entire data is concatenated into a large string. The hash value of the large string is then calculated.

5. The multi-source heterogeneous data incremental synchronization method as described in any one of claims 1 to 4, characterized in that, S5 specifically includes: Each data entry in the source table is compared with the primary key value of each data entry in the target table to determine if the primary key value of each data entry in the source table exists in the data in the target table. If not, the data entry is marked as newly added. If so, the hash value of the data entry is further checked to see if it is equal to the hash value in the target table. If they are equal, the data entry is marked as unchanged; otherwise, the data entry is marked as updated. After all data in the source table has passed the above checks, if there is any extra data in the target table that does not match, then the extra data will be marked for deletion.

6. The multi-source heterogeneous data incremental synchronization method as described in any one of claims 1 to 4, characterized in that, S6 specifically includes: Once the data from the source database has been loaded into memory and marked, insert the data from the source table into the target table, update the target table, and delete any redundant data from the target table.

7. The multi-source heterogeneous data incremental synchronization method as described in claim 6, characterized in that, When inserting data loaded into memory from the source table into the target table, the hash value is also inserted; when updating data loaded into memory from the source table into the target table, the hash field is modified.

8. A multi-source heterogeneous data incremental synchronization system, characterized in that, include: The Get and Select module is used to get and select the source database, the source table in the source database, and the fields in the source table that are used as source data. Get and select the target database, the target table in the target database, and the target fields in the target table that need to be synchronized; The primary key selection module is used to select the primary key comparison field from the source data field and the target data field respectively, and to obtain the primary key value of the primary key comparison field. The query module is used to query all data in the source table and the target table respectively, and to read the hash value of each data record in the target table; The hash value calculation module is used to calculate the hash value of each piece of data in the source table; The comparison and marking module is used to compare each piece of data in the source table with the primary key value and hash value of each piece of data in the target table, obtain different comparison results, and mark the data as added, updated, or deleted based on the comparison results. The operation module is used to perform add, update, and delete operations on the target table according to the add, update, and delete tags, respectively.

9. A storage medium, characterized in that, It stores a computer program for incremental synchronization of multi-source heterogeneous data, wherein the computer program causes the computer to perform the multi-source heterogeneous data incremental synchronization method as described in any one of claims 1 to 7.

10. An electronic device, characterized in that, include: One or more processors; Memory; as well as One or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the programs including methods for performing the multi-source heterogeneous data incremental synchronization method as described in any one of claims 1 to 7.

Images