Semantic-based migration and consistency verification method, system and equipment and medium

Abstract

The invention provides a semantic-based migration and consistency verification method, system and device and a medium, and relates to the technical field of databases. The method comprises the following steps: performing metadata topology scanning analysis by obtaining metadata, including generating an abstract syntax tree and constructing a semantic graph; according to a metadata topology scanning analysis result, performing data mapping and conversion, including loading a YAML rule and generating corresponding type mapping and constraint conversion; data migration is carried out through primary key fragmentation parallel migration, batch writing optimization and real-time double-writing verification; through structure-data-business three-layer verification, simulation business SQL comparison and automatic difference repair, the integrity of migrated data and business compliance are ensured, the semantic gap problem in heterogeneous database migration is solved, high-precision and high-efficiency database migration is realized, the migration efficiency is improved, and the data migration efficiency is improved. The method is suitable for scenes with strict requirements on data consistency and migration efficiency, such as financial, government affair and enterprise-level data centers.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a semantic-based migration and consistency verification method, system, device and medium. Background Art

[0002] Currently, heterogeneous databases refer to a collection of multiple related database systems that enable data sharing and transparent access. These systems often pre-exist before joining a heterogeneous database system, each with its own database management system and independent components. While data sharing is possible, each database system maintains its own application characteristics, integrity controls, and security controls. However, some databases, such as MySQL, MariaDB, and TiDB, are not fully heterogeneous databases due to their interoperability. This makes efficient data validation difficult in block data. Because fully heterogeneous databases have inconsistent sorting rules, queries based on the upper and lower bounds of a block will result in inconsistent block data, and the ordering of block data on both sides will also be inconsistent. For heterogeneous database migration scenarios, especially those migrating from MySQL to a domestic alternative like DMDB, current mainstream database migration tools (such as MySQL Workbench and AWS DMS) have their own shortcomings.

[0003] Existing technologies suffer from the following technical issues: 1. Lack of semantic consistency: Traditional migration tools (such as MySQL Workbench and AWS DMS) only perform mechanical type mapping (e.g., VARCHAR → VARCHAR2), ignoring the business semantics of fields. This can lead to post-migration data violating business logic (e.g., rounding errors and invalid constraints). 2. Inefficient migration: Existing solutions often use single-threaded full data replication, which can take upwards of 12 hours to migrate terabytes of data, with migration speed limited by I / O throughput. 3. Inadequate validation: Data existence is verified solely through primary key comparison or hash validation, lacking validation of business logic (e.g., order status and inventory linkage rules), making it impossible to detect semantic consistency issues. 4. Poor compatibility with heterogeneous databases and high manual intervention costs. Different database types (e.g., MySQL and DM) have significant differences in syntax, constraints, and functions (e.g., DECIMAL precision rules and LIMIT syntax). This requires manual adaptation of stored procedures and triggers, resulting in high migration costs. Summary of the Invention

[0004] The main purpose of the present invention is to provide a semantic-based migration and consistency verification method, system, equipment and medium to solve the technical problems in the existing technology such as lack of semantic consistency, low migration efficiency, insufficient verification and repair capabilities, and poor compatibility of heterogeneous databases.

[0005] To solve the above technical problems, the technical solution adopted by the present invention is: a semantic-based migration and consistency verification method, comprising the following steps: S1: Obtain metadata and use a metadata parser to perform metadata topology scanning and analysis, including generating an abstract syntax tree and building a semantic graph; S2: Performs data mapping and conversion based on the metadata topology scan and analysis results, including loading YAML rules and generating corresponding type mapping / constraint conversion; S3: Parallel migration: After data mapping and conversion, data migration is performed through primary key sharding parallel migration, batch write optimization, and real-time double-write verification. S4: Intelligent data verification: Through structure-data-business three-layer verification, simulated business SQL comparison and automated difference repair, if the verification is successful, a success report is returned to ensure the integrity of the migrated data and business compliance.

[0006] In the preferred embodiment, the S1 specifically includes: S11: Use a syntax parsing tool to parse the DDL statements of the source database, generate an abstract syntax tree (AST), and extract table structure, constraints, and index metadata. S12: By matching the semantic annotation rules of field names and annotations, the semantic type of the field is inferred and a table-level dependency graph is constructed.

[0007] In the preferred embodiment, S2 specifically includes: S21: Based on semantic type matching in the dynamic rule engine, the source database field type is mapped to the target database type, and the conversion rules are driven by YAML configuration; S22: Convert the CHECK constraints of the source database into triggers or stored procedures of the target database.

[0008] In the preferred solution, the parallel migration in S3 is implemented through sharding strategy and dependency scheduling, including: S31: Shard large tables horizontally by primary key range, migrate non-dependent tables in parallel, and migrate dependent tables sequentially; S32: uses batch writing mode, supporting writing 10,000 data records in a single batch; S33: A real-time double-write verification engine that splits data into the source and target databases and compares data differences through random sampling.

[0009] In a preferred embodiment, the step S4 includes: S41: Perform three-layer verification: metadata comparison at the structure layer, shard sampling comparison at the data layer, and business rule verification through predefined SQL at the business layer; S42: Simulates SQL access behavior in business scenarios, executes queries on the source and target databases in parallel, and automatically compares the results. S43: Automatically repair structural differences, data differences, and business logic differences, including data accuracy repair and business rule updates.

[0010] In the preferred solution, in S4, if the verification fails, a repair task is triggered, which specifically includes: S431: First, perform difference classification and priority determination; S432: Perform automated repairs based on the results of S431, including structural differences, data differences, and business logic differences; S433: Repair verification and closed-loop processing: A secondary check is performed immediately after the repair for confirmation. If the repair fails, a one-click rollback mechanism is triggered, and the newly discovered differences and repair solutions are stored in the rule knowledge base.

[0011] In a preferred solution, the automated repair includes: automatically repairing illegal data, generating executable repair SQL scripts, and rolling back to the pre-migration state with one click within a preset time.

[0012] A semantics-based migration and consistency verification system, comprising: Metadata scanning module, used to parse the source database DDL and stored procedures, generate syntax trees, and build semantic annotations and dependency graphs; The data mapping and conversion module is used to perform data mapping and conversion based on the metadata topology scanning and analysis results, including the dynamic rule engine loading YAML rules and generating corresponding type mapping / constraint conversion; The parallel migration module is used to migrate data after data mapping and conversion, using primary key sharding parallel migration, batch write optimization, and real-time double-write verification. The intelligent verification module is used to verify the structure, data, and business layers, compare simulated business SQL statements, and automatically repair differences. If the verification is successful, a success report is returned to ensure the integrity of the migrated data and business compliance.

[0013] An electronic device comprising a memory and a processor; The memory is used to store computer programs; The processor is used to implement the semantic-based migration and consistency verification method when executing the computer program.

[0014] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the semantic-based migration and consistency verification method.

[0015] The present invention provides a semantic-based migration and consistency verification method. By acquiring metadata, a metadata parser is used to perform metadata topology scanning and analysis, including generating an abstract syntax tree and building a semantic graph. According to the metadata topology scanning and analysis results, data mapping and conversion are performed, including loading YAML rules and generating corresponding type mapping / constraint conversion. Parallel migration: after data mapping and conversion, data migration is performed through primary key sharding parallel migration, batch write optimization and real-time double write verification. Intelligent data verification: through structure-data-business three-layer verification, simulated business SQL comparison and automatic difference repair, if the verification is successful, a success report is returned, thereby ensuring the integrity of the migrated data and business compliance.

[0016] The technical effects are as follows: 1) High-precision migration: Through field precision expansion, an error of less than 0.001% is achieved; foreign keys and check constraints are 100% retained, and equivalent logic is implemented through triggers or stored procedures.

[0017] 2) Significantly improved migration efficiency: TB-level data migration time was shortened from 12 hours to 2.5 hours, achieving a 79% efficiency improvement; incremental synchronization filtered out 70% of invalid events (such as log field updates).

[0018] 3) Fully automatic verification and repair: This feature provides three-tiered verification: structure (metadata), data (sampling comparison), and business (SQL rules). It also automatically repairs invalid data (such as converting a date from 0000-00-00 to NULL). It also supports one-click rollback, restoring the system to its pre-migration state within 10 minutes.

[0019] 4) Closed-loop reliability assurance: Real-time double-write verification, automatic difference repair, and rollback mechanisms ensure the monitorability and recoverability of the migration process. The knowledge base accumulates historical repair solutions and continuously optimizes them, further improving migration accuracy. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] The present invention will be further described below with reference to the accompanying drawings and examples: Figure 1 It is a flow chart of the method of the present invention; Figure 2 It is a schematic diagram of the dual write verification engine of the present invention; Figure 3 It is a schematic block diagram of the overall implementation of the method of the present invention; Figure 4 This is a schematic diagram of migrating MySQL to the DAMO database in the present invention. DETAILED DESCRIPTION

[0021] Example 1 like Figure 1-4 As shown, a semantic-based migration and consistency verification method includes the following steps: S1: Obtain metadata and use a metadata parser to perform metadata topology scanning and analysis, including generating an abstract syntax tree and building a semantic graph. Specifically, S11: Analyze database structure relationships through metadata and semantics, use ANTLR to parse MySQL's DDL syntax tree, extract metadata such as table structure, constraints, indexes, etc.; parse stored procedure / trigger code and generate an abstract syntax tree (AST).

[0022] S12: By matching the semantic annotation rules of field names and annotations, the semantic type of the field is inferred and a table-level dependency graph is constructed.

[0023] S2: Based on the metadata topology scan and analysis results, perform data mapping and conversion, including loading YAML rules and generating corresponding type mapping / constraint conversion, including: S21: Based on semantic type matching in the dynamic rule engine, the source database field type is mapped to the target database type, and the conversion rules are driven by YAML configuration; S22: Convert the CHECK constraint of the source database into a trigger or stored procedure of the target database, for example, convert the CHECK constraint of MySQL into a DM trigger.

[0024] S3: Parallel migration: After data mapping and conversion, data migration is performed through primary key sharding parallel migration, batch write optimization, and real-time double-write verification. Specifically, the migration includes: S31: Shard large tables horizontally by primary key range, migrate non-dependent tables in parallel, and migrate dependent tables sequentially.

[0025] S32: Uses batch writing mode, supporting writing 10,000 data records in a single batch.

[0026] S33: A real-time double-write verification engine that splits data into the source and target databases and compares data differences through random sampling.

[0027] S4: Intelligent Data Verification: Through structure-data-business verification, simulated business SQL comparison, and automated difference repair, a success report is returned if the verification is successful, ensuring the integrity of the migrated data and business compliance. Specifically, it includes: S41: Perform three-layer verification: compare metadata at the structure layer, compare shard sampling at the data layer, and verify business rules through predefined SQL at the business layer.

[0028] S42: Simulates SQL access behavior in business scenarios, executes source and target database queries in parallel, and automatically compares the results.

[0029] S43: Automatically repair structural differences, data differences, and business logic differences, including data accuracy repair and business rule updates.

[0030] This embodiment obtains metadata through steps S1-S4, uses a metadata parser to perform metadata topology scanning and analysis, performs data mapping and conversion based on the metadata topology scanning and analysis results, and then performs data migration through primary key sharding parallel migration, batch write optimization and real-time double-write verification, and performs intelligent data verification: through three-layer verification of structure-data-business, simulated business SQL comparison and automated difference repair, the integrity and business compliance of the migrated data are ensured; through the technical chain of semantic analysis → intelligent mapping → parallel migration → closed-loop verification, the semantic gap problem in heterogeneous database migration is solved, high-precision and high-efficiency database migration is achieved, and migration efficiency is improved. It can be applied to scenarios such as finance, government affairs, and enterprise-level data centers that have strict requirements on data consistency and migration efficiency.

[0031] In this embodiment, semantic consistency refers to semantically related words and phrases that jointly express a specific concept or theme.

[0032] Database migration: The process of migrating a database from one database management system to another.

[0033] An Abstract Syntax Tree (AST) is a tree-like data structure used to represent the abstract syntactic structure of source code. Each node represents a structural element or syntactic construct in the source code. Unlike a Concrete Syntax Tree (CST), an AST ignores certain syntactic details and only preserves the semantic structure of the program.

[0034] Consistency check: Verify whether data or systems meet predetermined rules under different conditions.

[0035] like Figure 2 As shown, in step 3 of the parallel migration, the real-time dual-write verification engine simultaneously diverts data to the original database and the newly migrated database in the business system or simulated business system, and performs difference analysis by randomly comparing the data in the original database and the data in the newly migrated database.

[0036] The sharding strategy in step 3 includes horizontal sharding: splitting the large table into multiple shards based on the primary key range (for example, ID 1-1 million); and dependency scheduling: migrating non-dependent tables in parallel and executing dependent tables sequentially. This combination of horizontal sharding and parallel migration significantly improves CPU utilization.

[0037] It also supports batch writing (10,000 records in a single batch), reducing the time for migrating TB-level data from 12 hours to 2.5 hours, greatly improving economic efficiency.

[0038] like Figure 4 As shown in Table 1, this is an example of migrating MySQL to the DM database in this embodiment, through data mapping and conversion, including the dynamic rule engine: 1) type mapping, matching the target type according to the semantic type (YAML configuration driven); 2) converting the MySQL CHECK constraint into the DM trigger to achieve migration.

[0039] Table 1 MySQL migration to DM database instance

[0040] In step S4, if the verification fails, a repair task is triggered, which specifically includes: S431: First, perform difference classification and priority determination; S432: Perform automated repairs based on the results of S431, including structural differences, data differences, and business logic differences; S433: Repair verification and closed-loop processing: A secondary check is performed immediately after the repair for confirmation. If the repair fails, a one-click rollback mechanism is triggered, and the newly discovered differences and repair solutions are stored in the rule knowledge base.

[0041] In this embodiment, in the difference classification and priority determination: 1. Classification rules include structural differences, data differences, and business logic differences: Structural differences include: missing tables / columns, inconsistent indexes, and constraint conflicts (such as invalid foreign keys).

[0042] Data differences include: numerical precision truncation (such as DECIMAL→NUMBER), null value conversion exceptions, and character set encoding issues.

[0043] Business differences include: violations of predefined business rules (such as order amounts not being allowed to be negative).

[0044] 2. The priority strategy adopted is: structural differences > key business data differences > general data differences, ensuring that the integrity of basic data is repaired first.

[0045] 1) Structural difference repairs include: table / column loss and index / constraint repairs, Table / column loss includes: Invoking metadata topology analysis results to automatically generate a DDL patch script (e.g., ALTER TABLE ADD COLUMN) for the target database. If a field requires semantic conversion (e.g., TEXT in MySQL to CLOB in DM), the dynamic rule engine's mapping configuration is applied.

[0046] Index / constraint repair includes comparing the index definition in the source database and executing CREATE INDEX or ADD CONSTRAINT in the target database. Constraints that cannot be directly migrated (such as MySQL CHECK) are converted into equivalent trigger logic.

[0047] 2) Data discrepancy repair includes data accuracy and anomalies: Precision issues include: For numeric fields (such as DECIMAL), use the "round up + precision expansion" rule to fix them (for example: DECIMAL(10,2) in the source database → NUMBER(12,4) in the target database); Automatically generate UPDATE scripts to correct truncation or rounding errors.

[0048] Null value / format exceptions include: illegal dates (such as 0000-00-00) are converted to NULL or default values ​​supported by the target library.

[0049] 3) Business logic difference repair includes rule violations and update logic: Rule violation handling involves calling the pre-built business rule knowledge base, matching the violation scenario (e.g., "order amount is negative"), generating repair SQL (e.g., UPDATE orders SET amount=ABS(amount) WHERE amount<0), and recording it in the audit log.

[0050] Logical adaptation updates include: Syntax differences in stored procedures and triggers (e.g., LIMIT → ROWNUM) are rewritten based on AST analysis. The test case library is also updated to ensure that subsequent verification covers these scenarios. Character set encoding issues (e.g., UTF8 → GB18030) are re-encoded and backfilled according to the conversion table.

[0051] This embodiment avoids blind manual operations and improves the accuracy of method execution through difference classification and rule engine. The dynamic rule engine supports automatic syntax conversion, and stored procedures / triggers achieve cross-database compatibility through AST analysis. The closed-loop process of repair → verification → rollback ensures the reliability of the method, and through the accumulation of knowledge base, the automation rate of subsequent migration is improved.

[0052] In the preferred solution, automated repair includes: automatically repairing illegal data, generating executable repair SQL scripts, and rolling back to the pre-migration state with one click within a preset time.

[0053] In this embodiment, the layered verification is immediately re-executed after the repair (step 4.1) to confirm that the differences have been eliminated. If the repair fails, the rollback mechanism is triggered to restore the state to the state before the repair and issue an alarm. The newly discovered differences and repair solutions are stored in the rule knowledge base, optimizing subsequent migration.

[0054] Table 2 Comparison between the technical solution of this application and the existing technology

[0055] As shown in Table 2, which is a comparison table between this embodiment and the existing technology, in use, this embodiment solves the semantic gap problem in heterogeneous database migration through the technical chain of semantic analysis → intelligent mapping → parallel migration → closed-loop verification, and realizes high-precision and high-efficiency database migration.

[0056] Example 2 In conjunction with Example 1, a semantic-based migration and consistency verification system is provided, including: The metadata scanning module is used to parse the source database DDL and stored procedures, generate syntax trees, and build semantic annotations and dependency graphs.

[0057] The data mapping and conversion module is used to perform data mapping and conversion based on the metadata topology scanning and analysis results, including the dynamic rule engine loading YAML rules and generating corresponding type mapping / constraint conversion.

[0058] The parallel migration module is used to migrate data after data mapping and conversion through primary key sharding parallel migration, batch write optimization, and real-time double write verification.

[0059] The intelligent verification module is used to verify the structure, data, and business layers, compare simulated business SQL statements, and automatically repair differences. If the verification is successful, a success report is returned to ensure the integrity of the migrated data and business compliance.

[0060] An electronic device comprising a memory and a processor; The memory is used to store computer programs.

[0061] The processor is used to implement a semantic-based migration and consistency verification method in Example 1 when executing the computer program.

[0062] A computer-readable storage medium stores a computer program. When the computer program is executed by a processor, a semantic-based migration and consistency verification method in Example 1 is implemented. The above embodiments are merely preferred technical solutions of the present invention and should not be construed as limiting the present invention. The scope of protection of the present invention shall be the technical solutions set forth in the claims, including equivalent alternatives to the technical features of the technical solutions set forth in the claims. In other words, equivalent alternatives and improvements within this scope are also within the scope of protection of the present invention.

Claims

1. A semantic-based migration and consistency verification method, characterized in that: The following steps are involved: S1: Obtain metadata and use a metadata parser to perform metadata topology scanning and analysis, including generating an abstract syntax tree and building a semantic graph; S2: Performs data mapping and conversion based on the metadata topology scan and analysis results, including loading YAML rules and generating corresponding type mapping / constraint conversion; S3: Parallel migration: After data mapping and conversion, data migration is performed through primary key sharding parallel migration, batch write optimization, and real-time double-write verification. S4: Intelligent data verification: Through structure-data-business three-layer verification, simulated business SQL comparison and automated difference repair, if the verification is successful, a success report is returned to ensure the integrity of the migrated data and business compliance.

2. The semantic-based migration and consistency verification method according to claim 1, characterized in that: Said S1 specifically includes: S11: Use a syntax parsing tool to parse the DDL statements of the source database, generate an abstract syntax tree (AST), and extract table structure, constraints, and index metadata. S12: By matching the semantic annotation rules of field names and annotations, the semantic type of the field is inferred and a table-level dependency graph is constructed.

3. The semantic-based migration and consistency verification method according to claim 1, characterized in that: The S2 specifically includes: S21: Based on semantic type matching in the dynamic rule engine, the source database field type is mapped to the target database type, and the conversion rules are driven by YAML configuration; S22: Convert the CHECK constraints of the source database into triggers or stored procedures of the target database.

4. The semantic-based migration and consistency verification method according to claim 1, characterized in that: The parallel migration in S3 is implemented through sharding strategy and dependency scheduling, including: S31: Shard large tables horizontally by primary key range, migrate non-dependent tables in parallel, and migrate dependent tables sequentially; S32: uses batch writing mode, supporting writing 10,000 data records in a single batch; S33: A real-time double-write verification engine that splits data into the source and target databases and compares data differences through random sampling.

5. The semantic-based migration and consistency verification method according to claim 1, characterized in that: The S4 includes: S41: Perform three-layer verification: metadata comparison at the structure layer, shard sampling comparison at the data layer, and business rule verification through predefined SQL at the business layer; S42: Simulates SQL access behavior in business scenarios, executes queries on the source and target databases in parallel, and automatically compares the results. S43: Automatically repair structural differences, data differences, and business logic differences, including data accuracy repair and business rule updates.

6. The semantic-based migration and consistency verification method according to claim 5, characterized in that: In S4, if the verification fails, a repair task is triggered, which specifically includes: S431: First, perform difference classification and priority determination; S432: Perform automated repairs based on the results of S431, including structural differences, data differences, and business logic differences; S433: Repair verification and closed-loop processing: A secondary check is performed immediately after the repair for confirmation. If the repair fails, a one-click rollback mechanism is triggered, and the newly discovered differences and repair solutions are stored in the rule knowledge base.

7. The semantic-based migration and consistency verification method according to claim 6, characterized in that: The automated repair includes: automatically repairing illegal data, generating executable repair SQL scripts, and rolling back to the pre-migration state with one click within a preset time.

8. A semantic-based migration and consistency verification system, characterized in that: include: Metadata scanning module, used to parse the source database DDL and stored procedures, generate syntax trees, and build semantic annotations and dependency graphs; The data mapping and conversion module is used to perform data mapping and conversion based on the metadata topology scanning and analysis results, including the dynamic rule engine loading YAML rules and generating corresponding type mapping / constraint conversion; The parallel migration module is used to migrate data after data mapping and conversion, using primary key sharding parallel migration, batch write optimization, and real-time double-write verification. The intelligent verification module is used to verify the structure, data, and business layers, compare simulated business SQL statements, and automatically repair differences. If the verification is successful, a success report is returned to ensure the integrity of the migrated data and business compliance.

9. An electronic device, characterized in that: including memory and processor; The memory is used to store computer programs; The processor is configured to implement a semantic-based migration and consistency verification method as described in any one of claims 1 to 7 when executing the computer program.

10. A computer-readable storage medium, characterized in that The storage medium stores a computer program, and when the computer program is executed by the processor, the semantic-based migration and consistency verification method according to any one of claims 1 to 7 is implemented.

Images