Data synchronization method, system and equipment for heterogeneous database and storage medium

Abstract

The invention relates to the technical field of databases, and particularly provides a data synchronization method, system and device of a heterogeneous database and a storage medium, and the method comprises the following steps: based on the same mapping rule, mapping the field type of a source database and the field type of a target database into intermediate description data; constructing a field type mapping relation from the source database to the target database based on the consistency of the intermediate description data; based on a source table structure in a source database, constructing a target table consistent with the source table structure in a target database; and based on the field type mapping relationship, converting data in the source table into a format supported by a target table, and inserting the converted data into the target table. According to the invention, the data synchronization efficiency of the heterogeneous database is improved.

Description

Technical Field

[0001] The present invention belongs to the technical field of databases, and in particular relates to a data synchronization method, system, device and storage medium for heterogeneous databases. Background Art

[0002] With the rapid advancement of medical informatization, medical institutions have now gathered a huge ocean of data from diversified systems, equipment and formats, covering electronic medical records, high-precision medical images, detailed laboratory test records and comprehensive diagnosis and treatment history, showing unprecedented data diversity and complexity. However, these data islands are significant, and format barriers, naming inconsistencies and structural differences between different databases have created many obstacles for efficient data collection and hierarchical management.

[0003] Although traditional data synchronization tools such as DataX can aggregate data, they lack intelligent parsing capabilities when processing mappings between database tables and fields. They often require a lot of time and effort to build cumbersome template files to achieve data synchronization. Summary of the invention

[0004] In view of the above-mentioned deficiencies in the prior art, the present invention provides a data synchronization method, system, device and storage medium for heterogeneous databases to solve the above-mentioned technical problems.

[0005] In a first aspect, the present invention provides a method for synchronizing data in a heterogeneous database, comprising: Based on the same mapping rules, the field types of the source database and the field types of the target database are mapped to intermediate description data; Construct the field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data; Based on the source table structure in the source database, construct a target table in the target database that is consistent with the source table structure; Based on the field type mapping relationship, the data in the source table is converted into a format supported by the target table, and the converted data is inserted into the target table.

[0006] In an optional implementation, based on the same mapping rule, the field types of the source database and the field types of the target database are mapped to intermediate description data, including: Obtaining version information of a source database, and determining all field types included in the source database based on the version information; Mapping the field types contained in the source database into the values ​​of the intermediate description data, and constructing a first value set using the values ​​as elements; Obtaining version information of the target database, and determining all field types included in the target database based on the version information; Map the field types included in the target database to numerical values of intermediate description data, and construct a second numerical set with the numerical values as elements.

[0007] In an optional embodiment, the intermediate description data is a constant used to describe SQL data types in JDBC programming.

[0008] In an optional embodiment, constructing a field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data includes: Calculate the intersection of the first numerical set and the second numerical set; Use the source database field types and target database field types corresponding to the elements in the intersection as corresponding field groups; Generate a field type mapping relationship based on the corresponding field groups according to the preset database priority; Count the special elements that do not belong to the intersection, specify the corresponding target database field types for the source database field types corresponding to the special elements, and generate a field type mapping relationship between the source database field types corresponding to the special elements and the corresponding target database field types.

[0009] In an optional embodiment, constructing a target table in the target database that is consistent with the source table structure based on the source table structure in the source database includes: Obtain the source table structure, which includes field types, primary keys, foreign keys, and indexes; Map the field types of the source table structure to the field types of the target table based on the field type mapping relationship; Create a target table in the target database using SQL statements according to the primary key, foreign key, and index of the source table, and the field types of the target table.

[0010] In an optional embodiment, constructing a field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data includes: Pre-construct a shared mapping library, which includes basic field type mappings; Obtain the field types of the source database and the field types of the target database; Using the field types of the source database as the query target, query the matching basic field type mappings from the shared mapping library, and save the matching basic field type mappings as a candidate mapping set; Using the field types of the target database as the query target, query the matching basic field type mappings from the candidate mapping set as the field type mapping relationship between the source database and the target database; The method for constructing the shared mapping library includes: Collect the field types of multiple versions of the database and convert the field types into intermediate description data; Save the intermediate description data of each version of the database as a data set; Perform pairwise intersection calculations on multiple data sets and generate the field type mapping relationships between the corresponding two versions of the database based on the calculation results; Summarize all the field type mapping relationships, save all the field type mapping relationships to a shared database, and obtain a shared mapping library.

[0011] In an optional implementation manner, based on the field type mapping relationship, convert the data in the source table into a format supported by the target table and insert the converted data into the target table, including: Create a data synchronization task and configure task parameters for the data synchronization task, where the task parameters include source database information, target database information, and table range; Execute the data synchronization task. According to the table range, use a data synchronization tool to gradually synchronize the data of the corresponding source table to the corresponding target table based on the field type mapping relationship; Add file locks to the source table and the target table for which data synchronization is performed; Determine the execution progress of the data synchronization task according to the source table for which data synchronization has been completed and the number of tables defined by the table range.

[0012] In a second aspect, the present invention provides a heterogeneous database data synchronization system, including: A mapping module for mapping both the field types of the source database and the field types of the target database into intermediate description data based on the same mapping rule; A matching module for constructing the field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data; A creation module for constructing a target table consistent with the source table structure in the target database based on the source table structure in the source database; A synchronization module for converting the data in the source table into a format supported by the target table based on the field type mapping relationship and inserting the converted data into the target table.

[0013] In a third aspect, a heterogeneous database data synchronization device is provided, including: A memory for storing a heterogeneous database data synchronization program; A processor for implementing the steps of the heterogeneous database data synchronization method provided in the first aspect when executing the heterogeneous database data synchronization program.

[0014] Fourthly, a computer-readable storage medium is provided, on which a data synchronization program for heterogeneous databases is stored. When the data synchronization program for heterogeneous databases is executed by a processor, the steps of the data synchronization method for heterogeneous databases provided in the first aspect are implemented.

[0015] The beneficial effects of the present invention are as follows. The data synchronization method, system, device and storage medium for heterogeneous databases provided by the present invention map the field types of heterogeneous databases to the same dimension by adopting the same mapping rules, match them in the same dimension, and establish the mapping relationship of the field types of heterogeneous databases based on the matching results. Based on this relationship, automatic conversion and synchronization of data formats can be realized, and the data synchronization efficiency of heterogeneous databases is improved.

[0016] In addition, the design principle of the present invention is reliable, the structure is simple, and it has a very wide application prospect. Description of the Drawings

[0017] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, for those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0018] Figure 1 It is a schematic flowchart of the method according to an embodiment of the present invention.

[0019] Figure 2 It is a schematic block diagram of the system according to an embodiment of the present invention.

[0020] Figure 3 It is a schematic structural diagram of a device provided by an embodiment of the present invention. Detailed Embodiments

[0021] In order to enable those skilled in the art to better understand the technical solutions in the present invention, the following will clearly and completely describe the technical solutions in the embodiments of the present invention with reference to the drawings in the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, rather than all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0022] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by those skilled in the art of the present invention. The terms used in the description of the present invention in this specification are only for the purpose of describing specific embodiments and are not intended to limit the present invention.

[0023] The data synchronization method for heterogeneous databases provided by the embodiments of the present invention is executed by a computer device. Correspondingly, the data synchronization system for heterogeneous databases runs in the computer device.

[0024] Figure 1 It is a schematic flowchart of the method according to an embodiment of the present invention. Among them, Figure 1 The execution subject can be a data synchronization system for heterogeneous databases. According to different requirements, the order of the steps in this flowchart can be changed, and some can be omitted.

[0025] Such as Figure 1 shown, the method includes: S1. Based on the same mapping rule, map the field types of the source database and the target database into intermediate description data; S2. Based on the consistency of the intermediate description data, construct a field type mapping relationship from the source database to the target database; S3. Based on the source table structure in the source database, construct a target table in the target database that is consistent with the source table structure; S4. Based on the field type mapping relationship, convert the data in the source table into a format supported by the target table, and insert the converted data into the target table.

[0026] In an embodiment of the present invention, based on step S1, a possible embodiment will be given below to non-restrictively elaborate on its specific implementation.

[0027] Read all field types of the corresponding version of the source database, and then map them to specific java.sql.type values; similarly, the target database will also read all field types of the corresponding version and map them to specific java.sql.type values.

[0028] S101. Obtain the version information of the source database: First, connect to the source database through a database connection tool (such as JDBC).

[0029] Use the system tables or functions provided by the database management system (DBMS) to query the version information of the source database. For example, in MySQL, you can use SELECT VERSION(); to obtain the version information.

[0030] Record the obtained version information for subsequent use.

[0031] S102. Determine all field types included in the source database based on the version information: According to the version information of the source database, query the database schema information, including table names, field names, and field types.

[0032] Go through all tables and fields, collect and record all different field types.

[0033] S103. Map the source database field type to the value of the intermediate description data: Use SQL data type constants defined in JDBC programming as intermediate description data. These constants are defined in the java.sql.Types class, such as Types.VARCHAR, Types.INTEGER, etc.

[0034] Create a mapping table or mapping function to map the field types of the source database to the corresponding JDBC type constants.

[0035] Use the mapped JDBC type constants (that is, values) as elements to build the first value set.

[0036] S104. Obtain the version information of the target database: Similar to the source database, connect to the target database through the database connection tool.

[0037] Use the system tables or functions provided by the DBMS to query the version information of the target database.

[0038] Record the obtained version information.

[0039] S105. Determine all field types included in the target database based on the version information: According to the version information of the target database, query the database architecture information, including table name, field name and field type.

[0040] Go through all tables and fields, collect and record all different field types.

[0041] S106. Map the target database field type to the value of the intermediate description data: The SQL data type constants defined in JDBC programming are also used as intermediate description data.

[0042] Create a mapping table or mapping function to map the field types of the target database to the corresponding JDBC type constants.

[0043] Use the mapped JDBC type constants (that is, values) as elements to construct the second value set.

[0044] The following is a specific example: Assume there are two databases: a source database (MySQL 5.7) and a target database (PostgreSQL 12). You need to map the field types in the source database to the field types in the target database.

[0045] Read the field types of the source database: The field types in MySQL 5.7 include: INT, VARCHAR, DATE, TEXT, DECIMAL, BLOB, etc.

[0046] These field types will be mapped to the corresponding java.sql.Type values. For example: INT -> java.sql.Types.INTEGER; VARCHAR -> java.sql.Types.VARCHAR; DATE -> java.sql.Types.DATE; And so on.

[0047] Read the field types of the target database: The field types in PostgreSQL 12 include: INTEGER, CHARACTER VARYING, DATE, TEXT, NUMERIC, BYTEA, etc.

[0048] These field types will also be mapped to the corresponding java.sql.Type values. For example: INTEGER -> java.sql.Types.INTEGER; CHARACTER VARYING -> java.sql.Types.VARCHAR; DATE -> java.sql.Types.DATE; And so on.

[0049] In an embodiment of the present invention, based on step S2, the following will give a possible embodiment to non - restrictively elaborate on its specific implementation scheme.

[0050] Read the corresponding java.sql.type of the target library through the source database java.sql.type. The relationship is one - to - many. If the relationship does not exist, the program will intelligently match according to the maximum name matching degree or the preset priority in the library. The administrator can also manually adjust the priority of the types in the target type set, and the types that cannot be corresponded can also be manually added and set. In short, the user is allowed to customize the field type mapping rules. The user can choose automatic mapping or manually adjust the mapping relationship according to the needs.

[0051] S201. Calculate the intersection of the first numerical set and the second numerical set: Use set operations (such as the retainAll method in Java) to calculate the intersection of these two sets. The intersection contains the numerical representations of the field types that exist in both databases.

[0052] S202. Take the source database field types and target database field types corresponding to the elements in the intersection as corresponding field groups: Traverse the intersection. For each element (i.e., numerical value), use the mapping relationship established previously to find the corresponding source database field type and target database field type.

[0053] Combine these corresponding field types into corresponding field groups, with each group containing one source field type and one target field type.

[0054] S203. Generate a field type mapping relationship based on the corresponding field groups according to the preset database priorities: The preset database priorities may be based on business requirements, data precision requirements, or other factors.

[0055] For each corresponding field group, if the source field type and the target field type are exactly the same, directly establish a mapping relationship.

[0056] If the types are different, but according to the priority rules, the target field type is an acceptable alternative (for example, from VARCHAR to TEXT), also establish a mapping relationship.

[0057] If the types are different and there is no suitable alternative, further analysis or conversion logic may be required.

[0058] S204. Count the special elements that do not belong to the intersection: Find those special elements that only exist in the first numerical set or the second numerical set. These elements represent the unique field types in the source database or the target database.

[0059] For each special element, it is necessary to determine whether type conversion is required or whether these fields should be ignored.

[0060] S205. Specify the corresponding target database field types for the source database field types corresponding to the special elements and generate a field type mapping relationship: For each special element, find a suitable field type in the target database according to its corresponding source database field type.

[0061] This process may involve manual mapping (based on business logic or data precision requirements) or automatic mapping (based on predefined rules or algorithms).

[0062] Generate the field type mapping relationship between the source database field type corresponding to the special element and the corresponding target database field type.

[0063] The following are specific processing examples: Special element one: ENUM type in MySQL Source database field type: ENUM('value1', 'value2', 'value3') Target database processing: In PostgreSQL, there is no direct ENUM type, but similar functionality can be achieved by creating a custom enumeration type or using a CHECK constraint.

[0064] Mapping decision: Based on business logic and data precision requirements, decide to create a corresponding custom enumeration type in PostgreSQL for each ENUM type.

[0065] Special element two: SET type in MySQL Source database field type: SET('value1', 'value2', 'value3') Target database processing: PostgreSQL also has no SET type. One solution is to use the VARCHAR type and combine it with application logic to parse and validate values.

[0066] Mapping decision: Since there is no direct replacement for the SET type in PostgreSQL, decide to use the VARCHAR type and perform additional validation at the application layer.

[0067] Special element three: TINYINT(1) type in MySQL Source database field type: TINYINT(1) (usually used as a boolean value) Target database processing: PostgreSQL has a BOOLEAN type and can be directly mapped.

[0068] Mapping decision: Since the BOOLEAN type is built-in in PostgreSQL, it is directly mapped to BOOLEAN.

[0069] Based on the above analysis, the following field type mapping relationship is generated: MySQL ENUM('value1', 'value2', 'value3') → PostgreSQL custom enumeration type; Example: ENUM('active', 'inactive', 'pending') maps to CREATE TYPE status_type AS ENUM ('active', 'inactive', 'pending') in PostgreSQL; MySQL SET('value1', 'value2', 'value3') → PostgreSQL VARCHAR; Example: SET('read', 'unread','starred') maps to VARCHAR(255) in PostgreSQL and is verified at the application layer.

[0070] MySQL TINYINT(1) → PostgreSQL BOOLEAN; Example: TINYINT(1) (used as a boolean) maps directly to BOOLEAN in PostgreSQL.

[0071] Implementation steps: Create a custom enumeration type in PostgreSQL (for ENUM types).

[0072] Update the data migration script to reflect the above mapping relationships.

[0073] Add necessary validation logic to the application code (for the case where SET types are mapped to VARCHAR).

[0074] Test the migration process to ensure data integrity and accuracy.

[0075] S206. Record and verify field type mapping relationships: Record all generated field type mapping relationships for use in data migration or synchronization processes.

[0076] Verify the accuracy of the mapping relationships to ensure that data is not lost or corrupted during the conversion process.

[0077] Based on the above embodiments, in order to further improve the efficiency of constructing the field mapping relationships provided by the above embodiments, in one embodiment, a shared mapping library can be pre-constructed: 1. Pre-construct a shared mapping library In database migration or data synchronization projects, in order to efficiently and accurately handle field type mappings between different databases, a shared mapping library is pre-constructed. This library contains the basic field type mappings between multiple versions of databases, and these mapping relationships are generated through the following steps: Collect the field types of multiple versions of the database: Extract the field type information from multiple different versions of the source database and the target database.

[0078] These databases may include different types or versions of DBMS such as MySQL, PostgreSQL, Oracle, SQL Server, etc.

[0079] Convert the field types into intermediate description data: Use the SQL data type constants defined in JDBC programming (such as the constants in java.sql.Types) as the intermediate description data.

[0080] Map the field types in each database to the corresponding JDBC type constants.

[0081] Save as a data set: Save the intermediate description data of each version of the database as an independent data set.

[0082] Each data set contains the intermediate description data representation of all field types in the database of that version.

[0083] Calculate the pairwise intersections and generate mapping relationships: Perform pairwise intersection calculations on multiple data sets to find the common field types between different versions of the database.

[0084] Based on the results of these intersection calculations, generate the field type mapping relationships between the corresponding two versions of the database.

[0085] Summarize and save to the shared database: Summarize all the generated field type mapping relationships.

[0086] Save these mapping relationships to the shared database to construct the shared mapping library.

[0087] 2. Obtain the field type mapping relationships using the shared mapping library After constructing the shared mapping library, this library can be used to obtain the field type mapping relationships between the source database and the target database. The specific steps are as follows: Obtain the field types of the source database and the target database: Connect to the source database and the target database through a database connection tool (such as JDBC).

[0088] Query the database schema information to obtain the field type information of all tables and fields.

[0089] Query the basic field type mappings in the shared mapping library: Using the field type of the source database as the query target, query the matching basic field type mapping from the shared mapping library.

[0090] Save the matched basic field type mapping as a candidate mapping set.

[0091] Determine the final field type mapping relationship: Using the field type of the target database as the query target, query the matching basic field type mapping from the candidate mapping set.

[0092] If a match is found, use it as the field type mapping relationship between the source database and the target database.

[0093] If no exact match is found, further analysis or manual specification of the mapping relationship may be required.

[0094] Generate a mapping relationship report: A detailed mapping relationship report can be generated, listing the matching and non-matching situations of the field types in the source database and the target database.

[0095] This report can be used as a reference for developers to help them better understand the differences between databases and how to perform data migration or synchronization.

[0096] By pre-building a shared mapping library and using it to obtain the field type mapping relationship, the efficiency and accuracy of database migration or data synchronization projects can be greatly improved. At the same time, this method also has a certain degree of flexibility and scalability, and can adapt to the migration requirements between different versions and types of databases.

[0097] In an embodiment of the present invention, based on step S3, a possible embodiment will be given below to non-restrictively elaborate on its specific implementation scheme.

[0098] S301. Asynchronously generate the mapping of the relationship between the source table and the target table User selection and configuration: The user first selects the source tables and target databases that need to be migrated or synchronized through a graphical interface or a command-line tool.

[0099] The user may also need to configure some additional options, such as whether to retain table structure elements such as primary keys, foreign keys, and indexes, and whether to perform data conversion or cleaning operations.

[0100] Asynchronous task startup: After the system receives the user's selection and configuration, it starts an asynchronous task to process the mapping of the relationship between tables and the migration of table structures.

[0101] This asynchronous task can run in the background without blocking other user operations.

[0102] Generate table relationship mapping: The system analyzes the table structure of the source database, including information such as table names, field names, field types, primary keys, foreign keys, and indexes.

[0103] According to the previously generated field type mapping relationship (as described in the previous step), map the field types of the source table to the field types of the target table.

[0104] At the same time, the system also generates the table relationship mapping between the source table and the target table, including relationship types such as one-to-one, one-to-many, and many-to-many.

[0105] S302. Generate the SQL statement for querying and synchronizing the source table Construct the query statement: Based on the generated table relationship mapping and field type mapping, the system constructs the SQL statement for querying data from the source table.

[0106] These query statements will be used to extract data from the source table during the data migration or synchronization process.

[0107] Optimize query performance: The system may optimize the generated query statements to improve the efficiency of data migration or synchronization.

[0108] The optimization measures may include adding index hints, using batch queries, etc.

[0109] S303. Automatically create the target table structure Obtain the source table structure: The system connects to the source database through a database connection tool (such as JDBC) and queries the table structure information of the selected source table.

[0110] This information includes field types, primary keys, foreign keys, and indexes, etc.

[0111] Map field types: Using the previously generated field type mapping relationship, map the field types of the source table structure to the field types of the target table.

[0112] This process ensures that the field types in the target table are compatible with or have corresponding alternative types to those in the source table.

[0113] Construct the table creation statement: Based on the primary key, foreign key, and index information of the source table, and the field type mapping results of the target table, the system constructs the SQL table creation statement for creating the target table in the target database.

[0114] These table creation statements will ensure that the target table has a structure similar to the source table, including primary key constraints, foreign key associations, indexes, etc.

[0115] Execute the table creation statements: The system sends the generated table creation statements to the target database for execution.

[0116] If a table with the same name already exists in the target database, the system may prompt the user to perform an overwrite or skip operation.

[0117] If the corresponding table does not exist in the target database, the system will automatically execute the table creation statements to create the necessary target table structure.

[0118] S304. Verification and Recording Verify the table structure: After creating the target table, the system may verify whether the table structure of the target table is consistent with the expectation.

[0119] The verification process may include checking field types, primary key constraints, foreign key associations, indexes, etc.

[0120] Record mapping relationships and operation logs: The system records all generated field type mapping relationships, inter-table relationship mappings, and operation logs during data migration or synchronization.

[0121] These records can be used for subsequent problem troubleshooting, data recovery, or auditing operations.

[0122] Through the above steps, the system can asynchronously generate the inter-table relationship mapping between the source table and the target table according to the user's selection, and create the necessary target table structure accordingly. This helps to ensure data integrity and consistency during data migration or synchronization, while improving the automation level and efficiency of operations.

[0123] In an embodiment of the present invention, based on step S4, a possible embodiment will be given below to non-restrictively elaborate on its specific implementation scheme.

[0124] S401. Create and Configure a Data Synchronization Task Initialize the data synchronization task: The user or system administrator creates a new data synchronization task through a graphical interface or command-line tool.

[0125] The system assigns a unique identifier to this task for subsequent management and tracking.

[0126] Configure task parameters: Configure the necessary parameters for the newly created data synchronization task. These parameters include but are not limited to: Source database information: Connection information of the source database, such as database type, host address, port number, database name, username, and password, etc.

[0127] Target database information: Connection information of the target database, which also includes database type, host address, port number, database name, username, and password, etc.

[0128] Table range: Specify the range of source tables and target tables for which data synchronization is required. It can be one or more tables, or a set of tables filtered by certain conditions.

[0129] S402. Execute the data synchronization task Start the task: After the configuration is completed, the user or system administrator starts the data synchronization task.

[0130] After the system receives the start instruction, it begins to execute the data synchronization task.

[0131] Synchronize data step by step: According to the configured table range, the system uses data synchronization tools (such as Apache Sqoop, Talend, AWS DMS, etc.) to gradually synchronize the data of the source tables to the corresponding target tables.

[0132] During the synchronization process, the system will perform necessary conversions and processing on the data according to the field type mapping relationship.

[0133] Add file locks: To avoid concurrency issues during data synchronization, the system adds file locks to the source tables and target tables that are currently undergoing data synchronization.

[0134] These locks can ensure that no other operations modify or delete these tables during the synchronization process.

[0135] S403. Track the task execution progress Record the synchronization status: During the synchronization process, the system will record the synchronization status of each table in real time, including the amount of data that has been synchronized, the synchronization speed, and whether there are any errors, etc.

[0136] Calculate the execution progress: Based on the number of source tables for which data synchronization has been completed and the total number of tables defined by the configured table range, the system calculates the execution progress of the data synchronization task.

[0137] This progress can be presented to the user or system administrator in the form of a percentage so that they can understand the completion status of the task.

[0138] Update the progress information: The system regularly updates the information on the progress of task execution and feeds back this information to the user or system administrator through a graphical interface or command-line tool.

[0139] Handling abnormal situations: If an error or abnormal situation is encountered during the synchronization process, the system will perform error handling and attempt to resume the synchronization operation.

[0140] If the error cannot be recovered, the system will record the error information and stop the synchronization task. The user or system administrator can troubleshoot and fix the problem based on the error information.

[0141] Through the above steps, the system can create and configure data synchronization tasks, gradually synchronize the data of the source table to the corresponding target table, and track the execution progress of the task in real time. This helps to ensure data integrity and consistency during the data migration or synchronization process, while improving the automation level and efficiency of the operation.

[0142] In some embodiments, the data synchronization system for heterogeneous databases may include multiple functional modules composed of computer program segments. The computer programs of each program segment in the data synchronization system for heterogeneous databases can be stored in the memory of the computer device and executed by at least one processor to perform the functions of data synchronization for heterogeneous databases (see Figure 1 description).

[0143] In this embodiment, according to the functions it performs, the data synchronization system for heterogeneous databases can be divided into multiple functional modules, such as Figure 2 shown. The functional modules of system 200 may include: a mapping module 210, a matching module 220, a creating module 230, and a synchronization module 240. The module referred to in the present invention means a series of computer program segments that can be executed by at least one processor and can complete fixed functions, and are stored in the memory. In this embodiment, the functions of each module will be described in detail in subsequent embodiments.

[0144] The mapping module is used to map both the field types of the source database and the field types of the target database to intermediate description data based on the same mapping rules; The matching module is used to construct a mapping relationship of field types from the source database to the target database based on the consistency of the intermediate description data; The creating module is used to construct a target table in the target database that is consistent with the source table structure based on the source table structure in the source database; The synchronization module is used to convert the data in the source table into a format supported by the target table based on the field type mapping relationship and insert the converted data into the target table.

[0145] Optionally, as an embodiment of the present invention, based on the same mapping rule, mapping the field types of the source database and the target database into intermediate description data, including: Obtain the version information of the source database, and determine all field types included in the source database based on the version information; Map the field types included in the source database into the numerical values of the intermediate description data, and construct a first numerical set with the numerical values as elements; Obtain the version information of the target database, and determine all field types included in the target database based on the version information; Map the field types included in the target database into the numerical values of the intermediate description data, and construct a second numerical set with the numerical values as elements.

[0146] Optionally, as an embodiment of the present invention, the intermediate description data is a constant used to describe SQL data types in JDBC programming.

[0147] Optionally, as an embodiment of the present invention, constructing a field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data, including: Calculate the intersection of the first numerical set and the second numerical set; Take the source database field types and target database field types corresponding to the elements in the intersection as corresponding field groups; Generate a field type mapping relationship based on the corresponding field groups according to the preset database priority; Count the special elements that do not belong to the intersection, specify the corresponding target database field types for the source database field types corresponding to the special elements, and generate a field type mapping relationship between the source database field types corresponding to the special elements and the corresponding target database field types.

[0148] Optionally, as an embodiment of the present invention, constructing a target table in the target database that is consistent with the source table structure based on the source table structure in the source database, including: Obtain the source table structure, and the structure includes field types, primary keys, foreign keys, and indexes; Map the field types of the source table structure into the field types of the target table based on the field type mapping relationship; Create a target table in the target database using SQL statements according to the primary key, foreign key, and index of the source table, and the field types of the target table.

[0149] Optionally, as an embodiment of the present invention, constructing a field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data, including: Pre-construct a shared mapping library, and the shared mapping library includes basic field type mappings; Obtain the field types of the source database and the field types of the target database; Using the field type of the source database as the query target, query the matching basic field type mappings from the shared mapping library, and save the matching basic field type mappings as a candidate mapping set; Using the field type of the target database as the query target, query the matching basic field type mappings from the candidate mapping set as the field type mapping relationship between the source database and the target database; The method for constructing the shared mapping library includes: Collect the field types of multiple versions of the database, and convert the field types into intermediate description data; Save the intermediate description data of each version of the database as a data set; Perform pairwise intersection calculations on multiple data sets, and generate the field type mapping relationships of the corresponding two versions of the database based on the calculation results; Summarize all field type mapping relationships, save all field type mapping relationships to the shared database to obtain the shared mapping library.

[0150] Optionally, as an embodiment of the present invention, based on the field type mapping relationship, convert the data in the source table into a format supported by the target table, and insert the converted data into the target table, including: Create a data synchronization task, and configure task parameters for the data synchronization task. The task parameters include source database information, target database information, and table range; Execute the data synchronization task. According to the table range, use the data synchronization tool to gradually synchronize the data of the corresponding source table to the corresponding target table based on the field type mapping relationship; Add file locks to the source table and the target table for which data synchronization is performed; Determine the execution progress of the data synchronization task according to the source table for which data synchronization has been completed and the number of tables defined by the table range.

[0151] Figure 3The data synchronization method for heterogeneous databases provided by the embodiments of this application can be applied to devices. Those skilled in the art can understand that the device structure involved in the embodiments of the present invention does not constitute a limitation on the device. The device may include more or fewer components than shown in the figure, or combine certain components, or have different component arrangements. In the embodiments of the present invention, the device includes, but is not limited to, laptop computers, desktop computers, workbenches, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the embodiments of this application described and / or claimed herein.

[0152] Among them, the device 300 may include: a processor 310, a memory 320, and a communication unit 330. These components communicate through one or more buses. Those skilled in the art can understand that the structure of the server shown in the figure does not constitute a limitation on the present invention. It can be a bus structure, a star structure, or may include more or fewer components than shown in the figure, or combine certain components, or have different component arrangements.

[0153] Among them, the memory 320 can be used to store the execution instructions of the processor 310. The memory 320 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, a magnetic disk, or an optical disk. When the execution instructions in the memory 320 are executed by the processor 310, the device 300 can execute some or all of the steps in the above method embodiments.

[0154] The processor 310 is the control center of the storage device, connecting various parts of the entire electronic device through various interfaces and lines. By running or executing the software programs and / or modules stored in the memory 320, and by calling the data stored in the memory, it executes various functions of the electronic device and / or processes data. The processor can be composed of an integrated circuit (IC), for example, it can be composed of a single packaged IC, or can be composed of multiple packaged ICs with the same or different functions connected together. For example, the processor 310 may only include a central processing unit (CPU). In the embodiments of the present invention, the CPU can be a single operation core or can include multiple operation cores.

[0155] A communication unit 330 is configured to establish a communication channel so that the storage device can communicate with other devices, receive user data sent by other devices, or send user data to other devices.

[0156] The present invention also provides a computer storage medium. The computer storage medium can store a program which, when executed, may include some or all of the steps in the embodiments provided by the present invention. The storage medium may be a magnetic disk, an optical disk, a read-only memory (ROM), a random access memory (RAM), or the like.

[0157] Those skilled in the art can clearly understand that the technology in the embodiments of the present invention can be implemented by means of software plus a necessary general hardware platform. Based on such an understanding, the technical solutions in the embodiments of the present invention, in essence, or the part that contributes to the prior art can be embodied in the form of a software product. The computer software product is stored in a storage medium such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk, etc., which can store program codes, and includes several instructions to enable a computer device (which may be a personal computer, a server, or a second device, a network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention.

[0158] For the same or similar parts among the various embodiments in this specification, reference can be made to each other. In particular, for the device embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and for the relevant parts, reference can be made to the description in the method embodiments.

[0159] In several embodiments provided by the present invention, it should be understood that the disclosed systems and methods can be implemented in other ways. For example, the system embodiments described above are merely illustrative. For example, the division of the modules is only a logical function division, and there may be other division methods in actual implementation. For example, multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the displayed or discussed couplings or direct couplings or communication connections to each other can be through some interfaces. The indirect couplings or communication connections of systems or modules can be in electrical, mechanical, or other forms.

[0160] The module described as a separation component may or may not be physically separated. The component shown as a module may or may not be a physical module, that is, it may be located in one place or distributed across multiple network modules. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0161] In addition, in each embodiment of the present invention, each functional module can be integrated in a processing module, or each module can exist physically alone, or two or more modules can be integrated in one module.

[0162] Although the present invention has been described in detail by referring to the accompanying drawings and in combination with the preferred embodiments, the present invention is not limited thereto. Without departing from the spirit and essence of the present invention, those of ordinary skill in the art can make various equivalent modifications or substitutions to the embodiments of the present invention, and these modifications or substitutions should all be within the scope of the present invention. / Any person skilled in the art within the technical scope disclosed by the present invention can easily think of changes or substitutions, and all should be covered within the protection scope of the present invention.

Claims

1. A data synchronization method for heterogeneous databases, characterized in that: include: Based on the same mapping rules, the field types of the source database and the field types of the target database are mapped to intermediate description data; Construct the field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data; Based on the source table structure in the source database, construct a target table in the target database that is consistent with the source table structure; Based on the field type mapping relationship, the data in the source table is converted into a format supported by the target table, and the converted data is inserted into the target table.

2. The method according to claim 1, characterized in that Based on the same mapping rules, the field types of the source database and the target database are mapped to intermediate description data, including: Obtaining version information of a source database, and determining all field types included in the source database based on the version information; Mapping the field types contained in the source database into the values ​​of the intermediate description data, and constructing a first value set using the values ​​as elements; Obtaining version information of the target database, and determining all field types included in the target database based on the version information; The field types contained in the target database are mapped to the values ​​of the intermediate description data, and the values ​​are used as elements to construct a second value set.

3. The method according to claim 1 or 2, characterized in that: The intermediate description data is a constant used to describe the SQL data type in JDBC programming.

4. The method according to claim 2, characterized in that: The field type mapping relationship between the source database and the target database is constructed based on the consistency of the intermediate description data, including: Calculate the intersection of the first value set and the second value set; The source database field type and the target database field type corresponding to the elements in the intersection are used as a corresponding field group; Generate field type mapping relationships based on the corresponding field groups according to the preset database priority; Special elements that do not belong to the intersection are counted, corresponding target database field types are specified for source database field types corresponding to the special elements, and a field type mapping relationship between the source database field types corresponding to the special elements and the corresponding target database field types is generated.

5. The method according to claim 1, characterized in that Based on the source table structure in the source database, a target table consistent with the source table structure is constructed in the target database, including: Obtaining a source table structure, including field types, primary keys, foreign keys, and indexes; Mapping the field type of the source table structure to the field type of the target table based on the field type mapping relationship; Use SQL statements to create a target table in the target database based on the primary key, foreign key, and index of the source table and the field type of the target table.

6. The method according to claim 1, characterized in that The field type mapping relationship between the source database and the target database is constructed based on the consistency of the intermediate description data, including: Pre-building a shared mapping library, wherein the shared mapping library includes basic field type mapping; Get the field type of the source database and the field type of the target database; Taking the field type of the source database as the query target, query the matching basic field type mapping from the shared mapping library, and save the matching basic field type mapping as a candidate mapping set; Taking the field type of the target database as the query target, query the matching basic field type mapping from the candidate mapping set as the field type mapping relationship between the source database and the target database; Methods for building a shared mapping library include: Collect the field types of multiple versions of databases and convert the field types into intermediate description data; The intermediate description data of each version of the database is saved as a data set; Perform pairwise intersection calculations on multiple data sets, and generate corresponding field type mapping relationships between two versions of the database based on the calculation results; All field type mapping relationships are summarized, and all field type mapping relationships are saved to a shared database to obtain a shared mapping library.

7. The method according to claim 1, characterized in that Based on the field type mapping relationship, converting the data in the source table into a format supported by the target table, and inserting the converted data into the target table, including: Create a data synchronization task and configure task parameters for the data synchronization task, wherein the task parameters include source database information, target database information, and table range; Execute the data synchronization task, and according to the table range, use the data synchronization tool to gradually synchronize the data of the corresponding source table to the corresponding target table based on the field type mapping relationship; Add file locks for the source and target tables for data synchronization; The execution progress of the data synchronization task is determined according to the source tables that have completed data synchronization and the number of tables limited by the table range.

8. A data synchronization system for heterogeneous databases, characterized in that: include: A mapping module, used for mapping the field types of the source database and the field types of the target database into intermediate description data based on the same mapping rule; A matching module is used to build a field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data; A creation module is used to construct a target table in a target database that is consistent with the source table structure based on the source table structure in the source database; The synchronization module is used to convert the data in the source table into a format supported by the target table based on the field type mapping relationship, and insert the converted data into the target table.

9. A data synchronization device for heterogeneous databases, characterized in that: include: A memory for storing data synchronization programs of heterogeneous databases; A processor is used to implement the steps of the heterogeneous database data synchronization method as described in any one of claims 1 to 7 when executing the data synchronization program of the heterogeneous database.

10. A computer-readable storage medium storing a computer program, characterized in that: The readable storage medium stores a data synchronization program for a heterogeneous database, and when the data synchronization program for a heterogeneous database is executed by a processor, the steps of the data synchronization method for a heterogeneous database as claimed in any one of claims 1 to 7 are implemented.

Images