Data synchronization method, system, device and storage medium for heterogeneous databases

Abstract

The present invention relates to the field of database technology and specifically provides a method, system, device, and storage medium for data synchronization across heterogeneous databases. The method comprises: mapping the field types of a source database and a target database to intermediate description data based on the same mapping rules; constructing a field type mapping relationship between the source database and the target database based on the consistency of the intermediate description data; 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; and 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. The present invention improves the efficiency of data synchronization across heterogeneous databases.

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 healthcare informatization, medical institutions now gather a vast ocean of data from diverse systems, devices, and formats. This includes electronic medical records, high-precision medical images, detailed laboratory test records, and comprehensive medical histories, demonstrating unprecedented data diversity and complexity. However, these data islands are prominent, and format barriers, inconsistent naming, and structural differences between different databases pose numerous obstacles to efficient data collection and hierarchical management.

[0003] Traditional data synchronization tools such as DataX can aggregate data, but they lack intelligent parsing capabilities when processing mappings between database tables and fields. They often require a lot of manual work and time 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 heterogeneous databases, comprising:

[0006] Based on the same mapping rules, the field types of the source database and the target database are mapped to intermediate description data;

[0007] Construct the field type mapping relationship between the source database and the target database based on the consistency of the intermediate description data;

[0008] 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;

[0009] 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.

[0010] In an optional embodiment, 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:

[0011] Obtaining version information of the source database, and determining all field types contained in the source database based on the version information;

[0012] Mapping the field types contained in the source database to the values ​​of the intermediate description data, and constructing a first value set using the values ​​as elements;

[0013] Obtaining version information of a target database, and determining all field types included in the target database based on the version information;

[0014] 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.

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

[0016] In an optional implementation, building a field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data includes:

[0017] Calculating the intersection of the first value set and the second value set;

[0018] 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;

[0019] Generate field type mapping relationships based on the corresponding field groups according to the preset database priority;

[0020] 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 special elements, and field type mapping relationships between source database field types corresponding to special elements and corresponding target database field types are generated.

[0021] In an optional embodiment, based on the source table structure in the source database, constructing a target table in the target database that is consistent with the source table structure includes:

[0022] Obtaining the source table structure, including field types, primary keys, foreign keys, and indexes;

[0023] Mapping the field types of the source table structure to the field types of the target table based on the field type mapping relationship;

[0024] Use SQL statements to create the 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.

[0025] In an optional implementation, building a field type mapping relationship from the source database to the target database based on the consistency of the intermediate description data includes:

[0026] Pre-building a shared mapping library, wherein the shared mapping library includes basic field type mapping;

[0027] Get the field types of the source database and the target database;

[0028] 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;

[0029] Taking the field type of the target database as the query target, the matching basic field type mapping is searched from the candidate mapping set as the field type mapping relationship between the source database and the target database;

[0030] Methods for building a shared mapping library include:

[0031] Collect field types from multiple versions of databases and convert them into intermediate description data;

[0032] Save the intermediate description data of each version of the database as a data set;

[0033] 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;

[0034] Summarize all field type mapping relationships, save all field type mapping relationships to a shared database, and obtain a shared mapping library.

[0035] In an optional embodiment, 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 includes:

[0036] 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;

[0037] Execute the data synchronization task, and 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 according to the table range;

[0038] Add file locks for the source and target tables for data synchronization;

[0039] The execution progress of the data synchronization task is determined according to the source tables for which data synchronization has been completed and the number of tables limited by the table range.

[0040] In a second aspect, the present invention provides a data synchronization system for heterogeneous databases, comprising:

[0041] A mapping module, used to map the field types of the source database and the field types of the target database into intermediate description data based on the same mapping rules;

[0042] The matching module is used to build the field type mapping relationship between the source database and the target database based on the consistency of the intermediate description data;

[0043] A creation module is used to construct a target table in a target database based on a source table structure in a source database and having the target table structure consistent with the source table structure;

[0044] 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.

[0045] In a third aspect, a data synchronization device for heterogeneous databases is provided, comprising:

[0046] Memory, used to store data synchronization programs of heterogeneous databases;

[0047] The processor is configured to implement the steps of the heterogeneous database data synchronization method provided in the first aspect when executing the heterogeneous database data synchronization program.

[0048] In a fourth aspect, 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.

[0049] The beneficial effects of the present invention are that 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, perform matching in the same dimension and establish the field type mapping relationship of the heterogeneous databases based on the matching results. Based on this relationship, automatic conversion and synchronization of data formats can be achieved, thereby improving the data synchronization efficiency of heterogeneous databases.

[0050] In addition, the present invention has a reliable design principle, a simple structure and a very broad application prospect. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0052] Figure 1 is a schematic flow chart of a method according to an embodiment of the present invention.

[0053] Figure 2 FIG. 4 is a schematic block diagram of a system according to an embodiment of the present invention.

[0054] Figure 3 A schematic structural diagram of a device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0055] In order to enable those skilled in the art to better understand the technical solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments 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 should fall within the scope of protection of the present invention.

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

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

[0058] Figure 1 is a schematic flow chart of a method according to an embodiment of the present invention. Figure 1 The execution subject can be a data synchronization system of a heterogeneous database. According to different requirements, the order of the steps in the flowchart can be changed, and some steps can be omitted.

[0059] like Figure 1 As shown, the method includes:

[0060] S1. Based on the same mapping rules, map the field types of the source database and the target database to intermediate description data;

[0061] S2. Construct a field type mapping relationship between the source database and the target database based on the consistency of the intermediate description data;

[0062] S3. Based on the source table structure in the source database, a target table is constructed in the target database that is consistent with the source table structure;

[0063] 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.

[0064] In an embodiment of the present invention, based on step S1, a possible embodiment will be given below to illustrate its specific implementation scheme in a non-limiting manner.

[0065] Read all field types of the corresponding version in the source database and 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.

[0066] S101. Get the version information of the source database:

[0067] First, connect to the source database using a database connection tool such as JDBC.

[0068] 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.

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

[0070] S102. Determine all field types contained in the source database based on the version information:

[0071] Based on the version information of the source database, query the database schema information, including table names, field names, and field types.

[0072] Traverse all tables and fields, collect and record all different field types.

[0073] S103. Map the source database field type to the value of the intermediate description data:

[0074] 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 and Types.INTEGER.

[0075] Create a mapping table or mapping function to map the source database field type to the corresponding JDBC type constant.

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

[0077] S104. Get the version information of the target database:

[0078] Similar to the source database, connect to the target database through the database connection tool.

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

[0080] Record the obtained version information.

[0081] S105. Determine all field types included in the target database based on the version information:

[0082] Based on the version information of the target database, query the database schema information, including table names, field names, and field types.

[0083] Traverse all tables and fields, collect and record all different field types.

[0084] S106. Map the target database field type to the value of the intermediate description data:

[0085] The SQL data type constants defined in JDBC programming are also used as intermediate description data.

[0086] Create a mapping table or mapping function to map the target database field type to the corresponding JDBC type constant.

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

[0088] The following is a specific example:

[0089] 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.

[0090] Read the source database field type:

[0091] Field types in MySQL 5.7 include: INT, VARCHAR, DATE, TEXT, DECIMAL, BLOB, etc.

[0092] These field types will be mapped to the corresponding java.sql.Type values. For example:

[0093] INT->java.sql.Types.INTEGER;

[0094] VARCHAR->java.sql.Types.VARCHAR;

[0095] DATE->java.sql.Types.DATE;

[0096] And so on.

[0097] Read the target database field type:

[0098] Field types in PostgreSQL 12 include: INTEGER, CHARACTER VARYING, DATE, TEXT, NUMERIC, BYTEA, etc.

[0099] These field types will also be mapped to the corresponding java.sql.Type values. For example:

[0100] INTEGER->java.sql.Types.INTEGER;

[0101] CHARACTER VARYING->java.sql.Types.VARCHAR;

[0102] DATE->java.sql.Types.DATE;

[0103] And so on.

[0104] In an embodiment of the present invention, based on step S2, a possible embodiment will be given below to illustrate its specific implementation scheme in a non-limiting manner.

[0105] The source database's java.sql.type is used to read the corresponding target database's java.sql.type. This relationship is one-to-many. If the relationship does not exist, the program will intelligently match the target database based on the maximum name match or the priority set within the database. Administrators can also manually adjust the priority of types in the target type set and manually add settings for types that do not match. In short, users can customize field type mapping rules. Users can choose automatic mapping or manually adjust the mapping relationship as needed.

[0106] S201. Calculate the intersection of the first value set and the second value set:

[0107] Use set operations (such as the retainAll method in Java) to calculate the intersection of the two sets. The intersection contains the numeric representations of the field types that exist in both databases.

[0108] S202. The source database field type and the target database field type corresponding to the elements in the intersection are used as corresponding field groups:

[0109] Traverse the intersection, and for each element (i.e., value), use the previously established mapping relationship to find the corresponding source database field type and target database field type.

[0110] Combine these corresponding field types into corresponding field groups, each group containing a source field type and a target field type.

[0111] S203. Generate a field type mapping relationship based on the corresponding field group according to the preset database priority:

[0112] Predetermined database priorities may be based on business needs, data accuracy requirements, or other factors.

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

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

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

[0116] S204. Count the special elements that do not belong to the intersection:

[0117] Find unique elements that exist only in the first or second value set. These elements represent unique field types in the source or target database.

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

[0119] S205. Specify the corresponding target database field type for the source database field type corresponding to the special element, and generate a field type mapping relationship:

[0120] For each special element, find a suitable field type in the target database based on its corresponding source database field type.

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

[0122] Generates a field type mapping relationship between the source database field type corresponding to the special element and the corresponding target database field type.

[0123] The following is a specific processing example:

[0124] Special element 1: MySQL ENUM type

[0125] Source database field type: ENUM('value1', 'value2', 'value3')

[0126] 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.

[0127] Mapping decision: Based on business logic and data accuracy requirements, it was decided to create a corresponding custom enumeration type in PostgreSQL for each ENUM type.

[0128] Special element 2: MySQL SET type

[0129] Source database field type: SET('value1', 'value2', 'value3')

[0130] Target database handling: PostgreSQL also does not have a SET type. One solution is to use the VARCHAR type and combine it with application logic to parse and validate the value.

[0131] Mapping decision: Since the SET type has no direct replacement in PostgreSQL, it was decided to use the VARCHAR type and perform additional validation at the application layer.

[0132] Special element three: MySQL's TINYINT(1) type

[0133] Source database field type: TINYINT(1) (usually used as a Boolean value)

[0134] Target database processing: PostgreSQL has a BOOLEAN type that can be mapped directly.

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

[0136] Based on the above analysis, the following field type mapping relationships are generated:

[0137] MySQLENUM('value1', 'value2', 'value3') → PostgreSQL custom enumeration type;

[0138] Example: ENUM('active', 'inactive', 'pending') maps to PostgreSQL's CREATETYPE status_type AS ENUM('active', 'inactive', 'pending');

[0139] MySQLSET('value1', 'value2', 'value3')→PostgreSQLVARCHAR;

[0140] Example: SET('read', 'unread', 'starred') is mapped to PostgreSQL's VARCHAR(255) and validated at the application layer.

[0141] MySQLTINYINT(1)→PostgreSQLBOOLEAN;

[0142] Example: TINYINT(1) (used as a Boolean value) maps directly to PostgreSQL's BOOLEAN.

[0143] Implementation steps:

[0144] Create custom enumeration types in PostgreSQL (for ENUM type).

[0145] Update the data migration script to reflect the above mapping relationship.

[0146] Add necessary validation logic in your application code (for cases where SET types are mapped to VARCHAR).

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

[0148] S206. Record and verify field type mapping:

[0149] Record all generated field type mapping relationships for use during data migration or synchronization.

[0150] Verify the accuracy of the mapping relationship to ensure that data is not lost or damaged during the conversion process.

[0151] On the basis of the above embodiment, in order to further improve the efficiency of constructing the field mapping relationship provided by the above embodiment, in one embodiment, a shared mapping library can be pre-constructed:

[0152] 1. Pre-build shared mapping library

[0153] In database migration or data synchronization projects, a shared mapping library is pre-built to efficiently and accurately handle field type mappings between different databases. This library contains basic field type mappings between multiple versions of databases. These mappings are generated through the following steps:

[0154] Collect field types for multiple versions of the database:

[0155] Extract field type information from multiple different versions of source and target databases.

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

[0157] Convert the field type to intermediate description data:

[0158] Use SQL data type constants defined in JDBC programming (such as constants in java.sql.Types) as intermediate description data.

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

[0160] Save as a data set:

[0161] The intermediate description data of each version of the database is saved as an independent data set.

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

[0163] Calculate the pairwise intersection and generate a mapping relationship:

[0164] Perform pairwise intersection calculations on multiple data sets to find the common field types between different versions of databases.

[0165] Based on these intersection calculation results, the corresponding field type mapping relationship between the two versions of the database is generated.

[0166] Summarize and save to a shared database:

[0167] Summarize all generated field type mappings.

[0168] These mapping relationships are saved in a shared database, thereby constructing a shared mapping library.

[0169] 2. Use the shared mapping library to obtain field type mapping relationships

[0170] After building the shared mapping library, you can use it to obtain the field type mapping relationship between the source database and the target database. The specific steps are as follows:

[0171] Get the field types of the source and target databases:

[0172] Connect to the source and target databases through database connectivity tools such as JDBC.

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

[0174] Query the basic field type mapping in the shared mapping library:

[0175] Use the source database's field type as the query target and query the shared mapping library for matching basic field type mappings.

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

[0177] Determine the final field type mapping relationship:

[0178] Take the field type of the target database as the query target and query the matching basic field type mapping from the candidate mapping set.

[0179] If a match is found, it is used as the field type mapping relationship between the source and target databases.

[0180] If an exact match is not found, further analysis or manual specification of the mapping may be necessary.

[0181] Generate a mapping relationship report:

[0182] A detailed mapping relationship report can be generated, listing the matches and mismatches in field types between the source and target databases.

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

[0184] By pre-building a shared mapping library and leveraging it to obtain field type mappings, you can significantly improve the efficiency and accuracy of database migration or data synchronization projects. This approach also offers flexibility and scalability, adapting to migration requirements between databases of different versions and types.

[0185] In an embodiment of the present invention, based on step S3, a possible embodiment will be given below to illustrate its specific implementation scheme in a non-limiting manner.

[0186] S301. Asynchronously generate table relationship mapping between source table and target table

[0187] User selection and configuration:

[0188] The user first selects the source table and target database to be migrated or synchronized through the graphical interface or command line tool.

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

[0190] Asynchronous task start:

[0191] After receiving the user's selection and configuration, the system starts an asynchronous task to handle the mapping of relationships between tables and the migration of table structures.

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

[0193] Generate relationship mapping between tables:

[0194] The system analyzes the table structure of the source database, including information such as table name, field name, field type, primary key, foreign key, and index.

[0195] Map the source table's field types to the target table's field types based on the field type mapping relationships generated previously (as described in the previous steps).

[0196] At the same time, the system also generates inter-table relationship mappings between the source table and the target table, including one-to-one, one-to-many, many-to-many and other relationship types.

[0197] S302. Generate source table synchronization query SQL statement

[0198] Construct the query statement:

[0199] Based on the generated inter-table relationship mapping and field type mapping, the system constructs an SQL statement for querying data from the source table.

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

[0201] Optimize query performance:

[0202] The system may optimize the generated query statements to improve the efficiency of data migration or synchronization.

[0203] Optimization measures may include adding index hints, using batch queries, etc.

[0204] S303. Automatically create target table structure

[0205] Get the source table structure:

[0206] 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.

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

[0208] Mapping field type:

[0209] Use the field type mapping relationship generated previously to map the field types of the source table structure to the field types of the target table.

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

[0211] Construct the table creation statement:

[0212] 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 an SQL table creation statement for creating the target table in the target database.

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

[0214] Execute the create table statement:

[0215] The system sends the constructed table creation statement to the target database for execution.

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

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

[0218] S304. Verification and Recording

[0219] Verify the table structure:

[0220] After the target table is created, the system may verify whether the table structure of the target table is consistent with the expected one.

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

[0222] Record mapping relationships and operation logs:

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

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

[0225] Through the above steps, the system asynchronously generates table relationship mappings between the source and target tables based on user selections and creates the necessary target table structure accordingly. This helps ensure data integrity and consistency during data migration or synchronization, while also improving operational automation and efficiency.

[0226] In an embodiment of the present invention, based on step S4, a possible embodiment will be given below to illustrate its specific implementation scheme in a non-limiting manner.

[0227] S401. Create and configure data synchronization tasks

[0228] Initialize the data synchronization task:

[0229] A user or system administrator creates a new data synchronization task through the graphical interface or command line tool.

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

[0231] Configure task parameters:

[0232] Configure the necessary parameters for the newly created data synchronization task, including but not limited to:

[0233] Source database information: connection information of the source database, such as database type, host address, port number, database name, user name, and password.

[0234] Target database information: connection information of the target database, including database type, host address, port number, database name, user name and password, etc.

[0235] Table range: Specifies the range of source and target tables for data synchronization. This can be one or more tables, or a set of tables filtered by certain conditions.

[0236] S402. Execute data synchronization task

[0237] Start the task:

[0238] After the configuration is complete, the user or system administrator starts the data synchronization task.

[0239] After receiving the start instruction, the system starts to execute the data synchronization task.

[0240] Synchronize data step by step:

[0241] Based on 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 table to the corresponding target table.

[0242] During the synchronization process, the system will perform necessary conversion and processing on the data based on the field type mapping relationship.

[0243] Add file lock:

[0244] To avoid concurrency issues during data synchronization, the system adds file locks to the source and target tables that are being synchronized.

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

[0246] S403. Tracking task execution progress

[0247] Record synchronization status:

[0248] During the synchronization process, the system will record the synchronization status of each table in real time, including the amount of synchronized data, synchronization speed, whether errors occurred, etc.

[0249] Calculate execution progress:

[0250] The system calculates the execution progress of the data synchronization task based on the number of source tables that have completed data synchronization and the total number of tables limited by the configured table range.

[0251] This progress can be displayed to users or system administrators in the form of a percentage so that they can understand the completion status of the task.

[0252] Update progress information:

[0253] The system regularly updates task execution progress information and feeds this information back to the user or system administrator through a graphical interface or command line tool.

[0254] Handling exceptions:

[0255] If an error or exception is encountered during the synchronization process, the system will perform error handling and attempt to resume the synchronization operation.

[0256] If the error is unrecoverable, the system will log the error message and stop the synchronization task. Users or system administrators can use the error message to troubleshoot and fix the problem.

[0257] By following these steps, the system can create and configure data synchronization tasks, gradually synchronize data from the source table to the corresponding target table, and track the task's progress in real time. This helps ensure data integrity and consistency during the data migration or synchronization process, while also improving the automation and efficiency of operations.

[0258] In some embodiments, the data synchronization system for heterogeneous databases may include multiple functional modules composed of computer program segments. The computer programs of the various program segments in the data synchronization system for heterogeneous databases may be stored in a memory of a computer device and executed by at least one processor to perform (see Figure 1 Description) Data synchronization function for heterogeneous databases.

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

[0260] A mapping module, used to map the field types of the source database and the field types of the target database into intermediate description data based on the same mapping rules;

[0261] The matching module is used to build the field type mapping relationship between the source database and the target database based on the consistency of the intermediate description data;

[0262] A creation module is used to construct a target table in a target database based on a source table structure in a source database and having the target table structure consistent with the source table structure;

[0263] 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.

[0264] Optionally, as an embodiment of the present invention, 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:

[0265] Obtaining version information of the source database, and determining all field types contained in the source database based on the version information;

[0266] Mapping the field types contained in the source database to the values ​​of the intermediate description data, and constructing a first value set using the values ​​as elements;

[0267] Obtaining version information of a target database, and determining all field types included in the target database based on the version information;

[0268] 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.

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

[0270] Optionally, as an embodiment of the present invention, constructing a field type mapping relationship from a source database to a target database based on the consistency of the intermediate description data includes:

[0271] Calculating the intersection of the first value set and the second value set;

[0272] 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;

[0273] Generate field type mapping relationships based on the corresponding field groups according to the preset database priority;

[0274] 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 special elements, and field type mapping relationships between source database field types corresponding to special elements and corresponding target database field types are generated.

[0275] Optionally, as an embodiment of the present invention, based on the source table structure in the source database, constructing a target table consistent with the source table structure in the target database includes:

[0276] Obtaining the source table structure, including field types, primary keys, foreign keys, and indexes;

[0277] Mapping the field types of the source table structure to the field types of the target table based on the field type mapping relationship;

[0278] Use SQL statements to create the 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.

[0279] Optionally, as an embodiment of the present invention, constructing a field type mapping relationship from a source database to a target database based on the consistency of the intermediate description data includes:

[0280] Pre-building a shared mapping library, wherein the shared mapping library includes basic field type mapping;

[0281] Get the field types of the source database and the target database;

[0282] 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;

[0283] Taking the field type of the target database as the query target, the matching basic field type mapping is searched from the candidate mapping set as the field type mapping relationship between the source database and the target database;

[0284] Methods for building a shared mapping library include:

[0285] Collect field types from multiple versions of databases and convert them into intermediate description data;

[0286] Save the intermediate description data of each version of the database as a data set;

[0287] 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;

[0288] Summarize all field type mapping relationships, save all field type mapping relationships to a shared database, and obtain a shared mapping library.

[0289] Optionally, as an embodiment of the present invention, 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 includes:

[0290] 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;

[0291] Execute the data synchronization task, and 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 according to the table range;

[0292] Add file locks for the source and target tables for data synchronization;

[0293] The execution progress of the data synchronization task is determined according to the source tables for which data synchronization has been completed and the number of tables limited by the table range.

[0294] Figure 3 The data synchronization method for heterogeneous databases provided in the embodiments of the present application can be applied to devices. Those skilled in the art will understand that the device structure involved in the embodiments of the present invention does not constitute a limitation on the device, and the device may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently. In the embodiments of the present invention, the device includes but is not limited to a laptop computer, a desktop computer, a workbench, a personal digital assistant, a server, a blade server, a mainframe computer, 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 the present application described and / or required herein.

[0295] The device 300 may include a processor 310, a memory 320, and a communication unit 330. These components communicate via one or more buses. Those skilled in the art will appreciate that the server structure shown in the figure does not limit the present invention. The server structure may be a bus structure or a star structure, and may include more or fewer components than shown, or combine certain components, or arrange the components differently.

[0296] The memory 320 can be used to store execution instructions of the processor 310. The memory 320 can be implemented by any type of volatile or non-volatile memory 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, magnetic disk, or optical disk. When the execution instructions in the memory 320 are executed by the processor 310, the device 300 can perform some or all of the steps in the above-described method embodiments.

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

[0298] The 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.

[0299] The present invention also provides a computer storage medium, wherein the computer storage medium may store a program that, when executed, may include some or all of the steps of each embodiment provided herein. The storage medium may be a magnetic disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0300] Those skilled in the art will clearly understand that the techniques in the embodiments of the present invention can be implemented using software and a necessary general-purpose hardware platform. Based on this understanding, the technical solutions in the embodiments of the present invention, or the portion that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium such as a USB flash drive, a mobile hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk, among other media capable of storing program code, and includes instructions for causing a computer device (which can 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 various embodiments of the present invention.

[0301] In this specification, the same or similar parts between the various embodiments can be referred to each other. In particular, for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the description of the method embodiment.

[0302] In the 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 merely a logical function division. In actual implementation, there may be other division methods, such as multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. In addition, the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of systems or modules, and can be electrical, mechanical or other forms.

[0303] The modules described as separate components may or may not be physically separate, and the components shown as modules may or may not be physical modules, that is, they may be located in one place or distributed across multiple network modules. Some or all of the modules may be selected to achieve the purpose of the present embodiment according to actual needs.

[0304] In addition, each functional module in each embodiment of the present invention may be integrated into one processing module, or each module may exist physically separately, or two or more modules may be integrated into one module.

[0305] Although the present invention has been described in detail with reference to the accompanying drawings and in conjunction with preferred embodiments, the present invention is not limited thereto. Without departing from the spirit and essence of the present invention, persons of ordinary skill in the art may make various equivalent modifications or substitutions to the embodiments of the present invention, and such modifications or substitutions shall be within the scope of the present invention. Any changes or substitutions that can be easily conceived by persons skilled in the art within the technical scope disclosed in the present invention shall be within the scope of protection 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 target database are mapped to intermediate description data; Construct the field type mapping relationship between the source database and 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, convert the data in the source table into a format supported by the target table, and insert the converted data into the target table; 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 the source database, and determining all field types contained in the source database based on the version information; Mapping the field types contained in the source database to the values ​​of the intermediate description data, and constructing a first value set using the values ​​as elements; Obtaining version information of a target database, and determining all field types included in the target database based on the version information; Mapping the field types contained in the target database into values ​​of the intermediate description data, and constructing a second value set using the values ​​as elements; The intermediate description data is a constant used to describe SQL data types in JDBC programming; Construct the field type mapping relationship between the source database and the target database based on the consistency of the intermediate description data, including: Calculating 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 special elements, and field type mapping relationships between source database field types corresponding to special elements and corresponding target database field types are generated.

2. 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 the source table structure, including field types, primary keys, foreign keys, and indexes; Mapping the field types of the source table structure to the field types of the target table based on the field type mapping relationship; Use SQL statements to create the 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.

3. The method according to claim 1, characterized in that Construct the field type mapping relationship between the source database and the target database 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 types of the source database and 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, the matching basic field type mapping is searched 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 field types from multiple versions of databases and convert them 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 corresponding field type mapping relationships between two versions of the database based on the calculation results; Summarize all field type mapping relationships, save all field type mapping relationships to a shared database, and obtain a shared mapping library.

4. The method according to claim 1, wherein 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 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 according to the table range; 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 for which data synchronization has been completed and the number of tables limited by the table range.

5. A data synchronization system for heterogeneous databases, characterized in that: include: A mapping module, used to map the field types of the source database and the field types of the target database into intermediate description data based on the same mapping rules; The matching module is used to build the field type mapping relationship between the source database and 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 based on a source table structure in a source database and having the target table structure consistent with the source table structure; A synchronization module, configured 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; 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 the source database, and determining all field types contained in the source database based on the version information; Mapping the field types contained in the source database to the values ​​of the intermediate description data, and constructing a first value set using the values ​​as elements; Obtaining version information of a target database, and determining all field types included in the target database based on the version information; Mapping the field types contained in the target database into values ​​of the intermediate description data, and constructing a second value set using the values ​​as elements; The intermediate description data is a constant used to describe SQL data types in JDBC programming; Construct the field type mapping relationship between the source database and the target database based on the consistency of the intermediate description data, including: Calculating 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 special elements, and field type mapping relationships between source database field types corresponding to special elements and corresponding target database field types are generated.

6. A data synchronization device for heterogeneous databases, characterized in that: include: Memory, used to store data synchronization programs of heterogeneous databases; A processor is configured to implement the steps of the method for synchronizing data with a heterogeneous database as claimed in any one of claims 1 to 5 when executing the data synchronization program for the heterogeneous database.

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

Images