Field type automatic mapping and lossless conversion method for multi-source heterogeneous database

Abstract

This application discloses a method and system for automatic field type mapping and lossless conversion for multi-source heterogeneous databases, including: collecting source metadata and target type capabilities; normalizing source field types into a unified intermediate type; identifying business semantics by combining field names, comments, and data samples; automatically generating mapping rules based on the intermediate type, target capabilities, and business semantics, and performing lossless risk assessment, outputting risk levels and alternative solutions; performing runtime conversion according to the rules, and implementing a fallback strategy for abnormal data; and performing field-level consistency verification and outputting a diagnostic report after writing is complete. This invention decouples heterogeneous database differences by using a unified intermediate type, and combines semantic recognition and pre-assessment of risks to solve the problems of error-prone manual configuration, accuracy loss, and time zone distortion. Through fallback strategies and post-verification, it achieves lossless data transmission, significantly reducing configuration and maintenance costs, and improving the reliability and data quality of heterogeneous data synchronization.

Description

Technical Field

[0001] This application relates to the field of data processing technology, specifically to a method, system, computer-readable storage medium, and electronic device for automatic field type mapping and lossless conversion of multi-source heterogeneous databases. Background Technology

[0002] With the increasing frequency of enterprise digital transformation and data interoperability, data needs to be transferred and integrated between various heterogeneous databases such as MySQL, Oracle, PostgreSQL, SQL Server, ClickHouse, Hive, DM, Kingbase, GaussDB, GBase, and Vastbase. Existing data transfer tools typically adopt an architecture based on Reader / Writer plugins, requiring users to manually configure the field names, order, types, and conversion rules of the source and target ends.

[0003] However, existing technologies have the following shortcomings in data transmission between heterogeneous databases:

[0004] (1) Field type mapping relies on human experience and is prone to errors: Different databases have significant differences in the definition, precision and value range of the same semantic type. For example, the implementation of numeric types such as NUMBER, DECIMAL, NUMERIC, BIGINT, and DOUBLE is not the same in different databases. When configuring manually, it is easy to lose precision, data overflow or rounding errors due to insufficient experience or negligence.

[0005] (2) Time type conversion is prone to distortion: The storage semantics, time zone processing and precision support of time types such as DATE, DATETIME, TIMESTAMP, TIMESTAMP WITH TIMEZONE are different in different databases. During the migration process, time zone offset, loss of millisecond / microsecond / nanosecond precision and date boundary processing errors may occur.

[0006] (3) Insufficient ability to handle differences in character sets and encodings: The source and target databases may use different character sets such as UTF-8, GBK, and LATIN1. Existing solutions usually only perform simple string transmission and lack the ability to identify risks of garbled characters, length truncation, and unmappable characters in advance.

[0007] (4) Lack of a unified conversion model for complex field types: The support methods and internal structures of complex types such as JSON, ARRAY, MAP, BLOB, CLOB, GEOMETRY, UUID, BOOLEAN, and ENUM vary significantly in different databases. Existing synchronization solutions often degenerate them into general strings or binary processing, resulting in the loss of original structural information and the inability to achieve semantic-level conversion.

[0008] (5) Lack of pre-conversion verification and post-conversion verification mechanisms: Most tools only report errors when data writing fails, and cannot predict the risk of field incompatibility or potential data loss before the task is executed; at the same time, there is a lack of effective verification mechanisms for precision, length, encoding or semantic consistency after data writing, making it difficult to guarantee data quality.

[0009] (6) Dispersed conversion logic and high maintenance costs: The type conversion logic is implemented independently in each database plugin, resulting in scattered and inconsistent rules. When a new database is added or the type definition of an existing database changes, a large amount of mapping and conversion code needs to be repeatedly developed and maintained, resulting in high maintenance costs.

[0010] In summary, existing technologies for mapping and converting field types in heterogeneous databases suffer from low automation, high data quality risks, and high maintenance costs, necessitating a more intelligent, reliable, and lossless solution. Summary of the Invention

[0011] To overcome the aforementioned deficiencies in existing technologies, this application proposes a novel method and system for automatic field type mapping and lossless conversion in multi-source heterogeneous databases. More specifically, this invention proposes an automatic field type mapping and lossless conversion method for data integration, data synchronization, data migration, and offline ETL scenarios involving multi-source heterogeneous databases, aiming to solve problems such as reliance on manual experience in field type mapping during data transmission between heterogeneous databases, which can easily lead to precision loss, time zone distortion, and encoding errors.

[0012] Specifically, this application provides the following technical solutions:

[0013] The first aspect of this application provides a method for automatic field type mapping and lossless conversion for multi-source heterogeneous databases, such as... Figure 1 As shown, the method includes the following steps:

[0014] S1. Metadata and Type Capability Collection: Connect to the source database to collect source metadata of the tables to be synchronized; connect to the target database to collect target type capability information.

[0015] S2. Construct a unified intermediate type: Normalize and map the source field types in the source metadata to a unified intermediate type that is independent of the specific database;

[0016] S3. Field semantic recognition: By combining field names and field comments in the source metadata, as well as field distribution and business rules obtained through data sample analysis, the true business semantics of the source fields are identified.

[0017] S4. Target field mapping rule generation and risk assessment: Based on the unified intermediate type, target end type capabilities and identified business semantics, automatically generate target field types and conversion expressions, and conduct a lossless risk assessment of the conversion process, outputting risk levels and alternative solutions.

[0018] S5. Perform runtime conversion: During data transmission, perform data type conversion according to the generated mapping rules and conversion expressions, and execute a preset fallback strategy for abnormal data that cannot be converted without loss.

[0019] S6. Validation of Conversion Results: After the data is written, perform field-level consistency validation on the source and target data and output a diagnostic report.

[0020] Furthermore, in the method of this application, the source metadata in step S1 includes one or more of the following: field name, field type, length, precision, scale, nullability, default value, primary key, index, comment, and character set; the target type capability information includes one or more of the following: supported field types, length limit, precision limit, time precision, character set, constraint rules, and write capability.

[0021] Furthermore, in the method of this application, the unified intermediate type mentioned in step S2 includes at least: INT64, DECIMAL(p,s), STRING(length, charset, lengthUnit), DATETIME(precision, timezone), BOOLEAN, BINARY, JSON, ARRAY, MAP, TEXT, and GEOMETRY.

[0022] Furthermore, in the method of this application, the field semantic recognition in step S3 is achieved through the following mechanisms: analysis of field names and annotations based on regular expression matching, analysis of statistical distribution characteristics of data samples, and matching of external business rule bases; the recognized semantics include, but are not limited to: mobile phone number, ID card number, amount, timestamp, enumeration value, and JSON string.

[0023] Furthermore, in the method of this application, the lossless risk assessment in step S4 covers risks of precision loss, length truncation, encoding conflict, time zone offset, structural loss, and constraint conflict; the risk level is divided into low risk, medium risk, and high risk; for high-risk fields, the alternatives include mapping the numeric type to a higher precision or larger range type, mapping the time type to a fidelity string, or extending the storage length of the character type.

[0024] Furthermore, in the method of this application, the runtime transformation in step S5 specifically includes: performing precision and range checks on numeric fields, performing time zone and precision processing on time fields, performing encoding and length checks on character fields, performing structural validity checks on JSON fields, and processing binary fields according to byte stream or Base64 strategies; the fallback strategy includes one or more of the following: blocking tasks, recording as dirty data, writing to a bypass table, using a faithful string as a fallback, and automatically expanding the target field type.

[0025] Furthermore, in the method of this application, the field-level consistency verification in step S6 includes, but is not limited to: row count consistency verification, field null value ratio verification, numerical precision verification, time offset verification, character length verification, JSON structure verification, and sample hash verification.

[0026] The second aspect of this application provides a system for automatic field type mapping and lossless conversion for multi-source heterogeneous databases. The system, when running, implements the steps of the aforementioned method for automatic field type mapping and lossless conversion for multi-source heterogeneous databases, such as... Figure 2 As shown, the system includes:

[0027] The source metadata collection module is used to connect to the source database and collect source metadata of the tables to be synchronized.

[0028] The target end type capability acquisition module is used to connect to the target database and collect target end type capability information;

[0029] The Unified Intermediate Type Model module is used to normalize and map the source field types in the source metadata to a unified intermediate type that is independent of the specific database.

[0030] The field semantic recognition module is used to identify the true business semantics of the source field by combining the field name and field annotation in the source metadata, as well as the field distribution and business rules obtained through data sample analysis.

[0031] The automatic mapping rule generation module is used to automatically generate target field types and conversion expressions based on the unified intermediate type, target end type capabilities and identified business semantics, and to perform a lossless risk assessment of the conversion process, outputting risk levels and alternative solutions.

[0032] The runtime conversion execution module is used to perform data type conversions based on the generated mapping rules and conversion expressions during data transmission, and to execute a preset fallback strategy for abnormal data that cannot be converted without loss.

[0033] The conversion result verification module is used to perform field-level consistency verification on the source and target data after the data is written, and output a diagnostic report.

[0034] A third aspect of this application provides an electronic device, including: a memory and a processor;

[0035] Memory: Used to store computer programs;

[0036] Processor: Used to execute the computer program to implement the steps of the aforementioned method for automatic mapping and lossless conversion of field types for multi-source heterogeneous databases.

[0037] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the aforementioned method for automatic mapping and lossless conversion of field types for multi-source heterogeneous databases.

[0038] In summary, compared with the prior art, the solution of the present invention has the following advantages:

[0039] (1) Significantly reduce manual configuration costs: Users do not need to manually configure type mapping field by field. The system can automatically generate conversion rules based on metadata and data samples, which greatly improves configuration efficiency.

[0040] (2) Effectively reduce data precision loss: Protect the precision of key fields such as amount, large integer, high precision decimal, and timestamp to avoid numerical distortion and time zone offset caused by implicit conversion.

[0041] (3) Comprehensively improve the reliability of heterogeneous database migration: potential high-risk fields can be identified through lossless risk assessment before data synchronization, avoiding the exposure of write failures or data errors during task execution and improving the success rate of tasks.

[0042] (4) Enhance compatibility with complex types: Provide structural fidelity or downgrade fidelity strategies for complex types such as JSON, CLOB, BLOB, ARRAY, and MAP to preserve their original semantic information as much as possible.

[0043] (5) Improve plugin reusability and reduce maintenance costs: The type mapping logic is extracted from each database plugin into a unified conversion layer, and each database plugin only needs to declare its own type capabilities. When adding a new database, only the type capability description needs to be added, without having to repeatedly develop a large amount of mapping logic, thus reducing system maintenance costs.

[0044] (6) Support for domestic database adaptation: Unified adaptation of the type differences of domestic databases such as DM, Kingbase, Shentong, GBase, GaussDB, Vastbase, etc., reducing the cost of domestic migration and data integration.

[0045] (7) Provide an auditable transformation chain: The mapping rules, risk level, runtime transformation process and post-transformation verification results of each field can be recorded, traced and audited, which improves the level of data governance and compliance.

[0046] Other features and advantages of this application will be set forth in detail in the following description, or will become apparent through the implementation of the relevant technical solutions of this application. The objectives and other advantages of this application can be achieved through the technical features and means explicitly pointed out in the description, claims, and drawings, and will be obtained through the implementation of these technical contents. Attached Figure Description

[0047] To more clearly illustrate the technical solution of this application, the accompanying drawings involved in the description of this invention will be briefly introduced below. It should be noted that the drawings only show some embodiments of the invention. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0048] Figure 1 This diagram illustrates the operational steps of the automatic field type mapping and lossless conversion method for multi-source heterogeneous databases proposed in this application.

[0049] Figure 2 This is a structural diagram of the field type automatic mapping and lossless conversion system for multi-source heterogeneous databases in this application.

[0050] Figure 3 This is a schematic diagram of the system architecture for the automatic field type mapping and lossless conversion method for multi-source heterogeneous databases in this application.

[0051] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application.

[0052] Caption: Processor-310, Communication Interface-320, Memory-330, Communication Bus-340. Detailed Implementation

[0053] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be understood that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0054] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0055] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0056] This invention provides the following technical solution:

[0057] A method for automatic field type mapping and lossless conversion for multi-source heterogeneous databases includes the following steps:

[0058] S1. Ability to collect source metadata and target type:

[0059] Connect to the source database and collect information such as field names, field types, lengths, precision, scales, nullability, default values, primary keys, indexes, comments, and character sets of the tables to be synchronized;

[0060] Connect to the target database and collect information such as its supported field types, length limits, precision limits, time precision, character sets, constraint rules, and write capabilities;

[0061] S2. Construct a unified intermediate type:

[0062] The source field types collected in step S1 are normalized and mapped to a unified intermediate type. The unified intermediate type includes, but is not limited to: INT64, DECIMAL(p,s), STRING(length, charset, lengthUnit), DATETIME(precision, timezone), BOOLEAN, BINARY, JSON, ARRAY, MAP, TEXT, GEOMETRY, etc.

[0063] S3. Perform field semantic recognition:

[0064] By combining the field names and field comments collected in step S1, as well as the field distribution and business rules obtained through data sample analysis, the true business semantics of the source field are identified and enhanced, such as identifying mobile phone numbers, ID card numbers, amounts, latitude and longitude, timestamps, enumeration values, JSON strings, etc.

[0065] S4. Generate target field mapping rules and perform non-destructive risk assessment:

[0066] Based on the unified intermediate type constructed in step S2, the target database type capabilities collected in step S1, and the field semantics identified in step S3, the target field type and corresponding conversion expression are automatically generated.

[0067] At the same time, a lossless risk assessment is performed on the conversion process of each field, calculating the potential risks of precision loss, length truncation, encoding, time zone, structural loss, and constraint conflict, and automatically providing alternative solutions or warnings based on the risk level;

[0068] S5. Perform runtime transformation:

[0069] During data transmission, the data is converted according to the mapping rules and conversion expressions generated in step S4.

[0070] For data that cannot be transformed without loss or that is abnormal, a preset fallback strategy is executed. The fallback strategy includes, but is not limited to: blocking the task, recording it as dirty data, writing it to a bypass table, using a string-preserving fallback (i.e., storing the unconvertible data in the original string format to avoid information loss), or automatically expanding the target field type.

[0071] S6. Perform conversion result verification:

[0072] After the data is written to the target database, field-level consistency checks are performed on the source and target data. These checks include, but are not limited to: row count consistency check, field null value ratio check, numerical precision check, time offset check, character length check, JSON structure check, and sample hash check.

[0073] Based on the verification results, a field-level diagnostic report is output, explaining the specific causes of the loss and recommending repair methods.

[0074] Preferably, the field semantic recognition in step S3 includes a recognition mechanism based on regular expression matching, lexical analysis, statistical distribution feature analysis, and external business rule base matching.

[0075] Preferably, the non-destructive risk assessment in step S4 includes risk levels of low risk, medium risk, and high risk.

[0076] When assessed as high risk, alternatives include, but are not limited to: mapping numeric types to types with higher precision or a wider range, mapping time types to strings for fidelity storage, or extending the storage length of character types.

[0077] Preferably, in the runtime conversion described in step S5, the conversion engine performs precision, scale, and range checks on numerical fields; performs time zone, precision, and format processing on time fields; performs encoding, length, and invisible character processing on character fields; performs structural validity checks on JSON fields; and processes binary fields according to byte stream or Base64 strategies.

[0078] Furthermore, this invention also provides an automatic field type mapping and lossless conversion system for multi-source heterogeneous databases, comprising:

[0079] Source metadata collection module: used to connect to the source database and collect field information of the table to be synchronized;

[0080] Target type capability acquisition module: used to connect to the target database and acquire its supported field type capabilities;

[0081] Unified intermediate type model module: Used to normalize the source field types into a unified intermediate type;

[0082] Field semantic recognition module: used to identify the true business semantics of fields by combining field names, field comments, data samples and business rules;

[0083] Automatic mapping rule generation module: It is used to automatically generate target field types and conversion expressions based on unified intermediate types, target database capabilities and field semantics, and integrates lossless risk assessment functions;

[0084] Runtime conversion execution module: used to perform type conversion according to the generated rules during data transmission, and to handle abnormal data as a fallback.

[0085] The conversion result verification module is used to perform field-level consistency verification on the source and target data after the data is written to the target database, and output a diagnostic report.

[0086] To more clearly illustrate the technical solution of this application, the following will provide further explanation through specific scenario embodiments.

[0087] Figure 3This diagram illustrates the system architecture of the automatic field type mapping and lossless conversion method for multi-source heterogeneous databases according to the present invention. As shown in the figure, the system includes a source-side metadata acquisition module, a target-side type capability acquisition module, a unified intermediate type model module, a field semantic recognition module, an automatic mapping rule generation module, a runtime conversion execution module, and a conversion result verification module. These modules work collaboratively to achieve automatic, lossless, and verifiable data transmission from the source to the target.

[0088] 1. Source-side metadata collection module

[0089] This module is responsible for connecting to the source database (e.g., Oracle) and automatically reading detailed metadata information of the tables to be synchronized. This information includes not only field names and declared field types (such as NUMBER(20,6), TIMESTAMP(6), VARCHAR2(100 CHAR), CLOB, VARCHAR2(18)), but also precise length, precision, scale, nullability, default value, primary key information, index information, field comments, and the character set and collation used. The collected data will serve as the basis for subsequent steps.

[0090] 2. Target type capability acquisition module

[0091] This module is responsible for connecting to the target database (such as MySQL), automatically identifying and collecting all field types supported by the target database and their detailed capability parameters. These parameters include, but are not limited to: maximum length limit, maximum precision limit, millisecond / microsecond / nanosecond precision supported for each type, character set support, data integrity constraints (such as NOT NULL, unique constraints), and actual data writing capabilities (for example, some databases may use TEXT type for JSON but provide JSON validation functionality).

[0092] 3. Unified intermediate type model module

[0093] This module is the core of this invention for achieving decoupling and unified transformation of heterogeneous databases. It normalizes and maps the source-side field types and their attributes collected by the source-side metadata collection module into a standardized, database-independent unified intermediate type. For example:

[0094] Source type NUMBER(20,6) → intermediate type DECIMAL(20,6);

[0095] Source TIMESTAMP(6) → intermediate type DATETIME(6, timezone=unknown) (initial timezone unknown);

[0096] Source VARCHAR2(100 CHAR) → intermediate type STRING(100, lengthUnit=char) (explicitly specifies the character length);

[0097] Source CLOB → Intermediate type TEXT;

[0098] Source BLOB → intermediate type BINARY;

[0099] The source database's unique JSON type → intermediate JSON type;

[0100] PostgreSQL's ARRAY type → Intermediate type ARRAY.

[0101] This intermediate type representation not only retains the core information of the original type, but also enhances the accuracy of information expression through additional attributes (such as lengthUnit and timezone), laying the foundation for subsequent semantic recognition and lossless conversion.

[0102] 4. Field semantic recognition module

[0103] This module goes beyond simple type matching, aiming to identify the true business semantics of fields to guide smarter type mapping and conversion. It comprehensively utilizes the following information:

[0104] Field name analysis: based on keyword matching (e.g., "id_card", "phone", "amount", "create_time").

[0105] Field annotation analysis: Extract business description information from the annotations.

[0106] Data sample analysis: Sample data from the source table and analyze the distribution characteristics, patterns, lengths, and value ranges of field values. For example, use regular expressions to determine if the data is a string in the format of an ID card number, mobile phone number, or date; use statistical value distribution to determine if the data is an enumeration type.

[0107] External business rule library: Introduce predefined business rules, such as "all VARCHAR fields ending with 'ID' should be treated as business IDs and no numerical conversion should be performed".

[0108] Through the above mechanism, the system can mark VARCHAR2(18) as "ID number", NUMBER(20,6) as "amount", and VARCHAR2 (arbitrary length) fields that conform to JSON format as "JSON string". This effectively avoids misjudging fields such as ID number and mobile phone number, which are only similar to numbers in appearance, as numeric types, thus preventing conversion errors.

[0109] 5. Automatic mapping rule generation module (integrated with non-destructive risk assessment)

[0110] This module is the core of the automatic mapping and lossless conversion decision-making process. Based on the unified intermediate type, target type capabilities, and field semantic recognition results, it intelligently generates mapping rules and conversion expressions from source fields to target fields.

[0111] (1) Mapping rule generation:

[0112] Exact match: If the target type has a type that is completely equivalent to or compatible with the intermediate type (e.g., DECIMAL(20,6) → DECIMAL(20,6)), then the mapping is direct.

[0113] Best fit: If no completely equivalent type exists, choose the closest type that preserves the most information. For example, if the source uses TIMESTAMP(6) (supports microseconds) and the target only supports DATETIME(3) (supports milliseconds), then DATETIME(3) will be preferred, and the risk of precision loss will be flagged. If the target does not support high-precision time types at all, it may be recommended to map to VARCHAR to faithfully store the original string.

[0114] Semantic priority: If the semantic recognition module recognizes VARCHAR2(N) as a "JSON string", it will be mapped to the JSON type of the target database (if supported) instead of a simple VARCHAR or TEXT.

[0115] (2) Non-destructive risk assessment: While generating mapping rules, this module performs real-time, field-level risk assessment of the transformation process of each field.

[0116] Risk of loss of precision: Compare the differences in precision and scaling between numeric and time types. For example, DECIMAL(38,10) → DOUBLE would be marked as high risk.

[0117] Length truncation risk: Compare character types for differences in maximum length, character set, and length unit. For example, VARCHAR(100 CHAR) → VARCHAR(100 BYTE) may result in truncation under the UTF-8 character set and is marked as medium / high risk.

[0118] Encoding risks: Assess compatibility between different character sets and identify risks of unmapped characters or garbled text.

[0119] Time zone risk: Assess the differences in time zone handling for different time types.

[0120] Risk of structural loss: For complex types such as JSON and ARRAY, assess whether the target database can retain their structural information.

[0121] Constraint conflict risk: For example, the source field can be null, but the target field is mapped to a non-null field.

[0122] The system categorizes risks into "low risk," "medium risk," and "high risk" based on the assessment results, and automatically provides alternative solutions or warnings for high-risk fields, such as changing DOUBLE to DECIMAL, changing DATETIME(3) to string fidelity storage, or suggesting expansion of the target field. Users can manually intervene or confirm based on the risk assessment report.

[0123] 6. Runtime conversion execution module

[0124] During data synchronization, this module is the engine that actually performs type conversions. Data from each record flows into the conversion engine, which performs data conversions based on the field-level mapping rules and conversion expressions generated by the automatic mapping rule generation module.

[0125] Numeric fields: Check if the precision and scale of the data exceed the range of the target type, perform rounding or truncation (depending on the rules), and check for overflow.

[0126] Time field: Handles time zone conversion, time precision truncation (if allowed), and formatting to a format acceptable to the target database.

[0127] Character field: Performs character set encoding conversion, length check, and handles invisible or special characters.

[0128] JSON field: Validates whether the source data conforms to the JSON format specification. If it does not conform, it is handled according to the fallback strategy. If the target supports the JSON type, it is transmitted according to the JSON structure.

[0129] Binary fields: transmitted as byte streams or using Base64 encoding strategies.

[0130] If data cannot be converted losslessly during the conversion process (e.g., numerical overflow, character truncation, invalid JSON format), the system will handle it according to the preset fallback strategy in the automatic mapping rule generation module:

[0131] Blocking tasks: For severe data errors that cannot be automatically corrected.

[0132] Record as dirty data: Record the problematic data to a log or a specific table, and the task continues.

[0133] Write to the bypass table: Isolate and store the abnormal data in a separate error table, and the task continues.

[0134] Use a fallback string: Convert complex or high-precision data that cannot be matched into a string for storage, preserving the original information.

[0135] Automatic expansion of target field type: In some cases, the system can dynamically suggest or perform expansion of the target field type (for example, after finding a large amount of excessively long data, VARCHAR(100) may be expanded to VARCHAR(200) or TEXT).

[0136] 7. Conversion Result Verification Module

[0137] After the data is written to the target database, this module will perform multi-dimensional, field-level consistency checks on the data from the source and target ends to ensure the lossless or traceable nature of the conversion results.

[0138] Row count consistency check: Verify whether the total number of rows on the source and target ends match.

[0139] Field null value ratio validation: Compare the null value ratio of each field to detect abnormal null value conversion.

[0140] Numerical precision verification: Randomly sample numerical fields and compare whether their precision remains consistent or within an acceptable error range.

[0141] Time offset verification: Randomly sample the time field, compare its time value and millisecond / microsecond precision to see if they are consistent, and check for time zone offset.

[0142] Character length validation: Sample verification to check whether the length of the character field is consistent or truncated.

[0143] JSON structure validation: Performs structural validity and content consistency checks on fields stored as JSON type.

[0144] Sample hash verification: Calculate the hash value of the sampled data and compare it.

[0145] For fields found to be inconsistent, the system will output a detailed field-level diagnostic report, explaining the specific cause of the loss (e.g., "AMOUNT field lost 2 decimal places", "CREATE_TIME field lost microsecond precision"), the location and quantity of the loss, and recommending repair methods, providing users with powerful data quality assurance and auditing capabilities.

[0146] Through the above solution, the present invention achieves automatic, intelligent, and lossless conversion of field types in heterogeneous databases, significantly improving the efficiency, reliability, and accuracy of data migration.

[0147] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of systems, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0148] like Figure 4 As shown, embodiments of this application also disclose an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing processor-executable computer programs, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the above-described method for automatic field type mapping and lossless conversion of multi-source heterogeneous databases.

[0149] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0150] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types:

[0151] Object-oriented programming languages, such as Java, Smalltalk, C++, etc.

[0152] Conventional procedural programming languages, such as "C" or similar programming languages.

[0153] The execution methods of program code include, but are not limited to:

[0154] It runs entirely on the user's computer;

[0155] Part of it executes on the user's computer, and part of it executes on a remote computer;

[0156] Execute as a standalone software package;

[0157] It is executed entirely on a remote computer or server.

[0158] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0159] Furthermore, this application also discloses a computer-readable storage medium, which, when the instructions in the computer-readable storage medium are executed by the processor of an electronic device, enables the electronic device to perform the various steps of the automatic field type mapping and lossless conversion method for multi-source heterogeneous databases disclosed in this application.

[0160] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following:

[0161] (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.

[0162] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.

[0163] (3) Random Access Memory (RAM): A volatile storage medium used for temporary storage of data and program code.

[0164] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.

[0165] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.

[0166] (6) Fiber optic storage devices: storage media based on fiber optic technology.

[0167] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.

[0168] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.

[0169] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.

[0170] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.

[0171] These computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.

[0172] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program contains program code for executing the automatic field type mapping and lossless conversion method for multi-source heterogeneous databases disclosed in this application. When this computer program is executed by a processing system, it can achieve the functions defined in the embodiments of this application.

[0173] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0174] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for automatic field type mapping and lossless conversion for multi-source heterogeneous databases, characterized in that, Includes the following steps: S1. Metadata and Type Capability Collection: Connect to the source database and collect the source metadata of the table to be synchronized; Connect to the target database and collect target end-user type and capability information; S2. Construct a unified intermediate type: Normalize and map the source field types in the source metadata to a unified intermediate type that is independent of the specific database; S3. Field semantic recognition: By combining field names and field comments in the source metadata, as well as field distribution and business rules obtained through data sample analysis, the true business semantics of the source fields are identified. S4. Target field mapping rule generation and risk assessment: Based on the unified intermediate type, target end type capabilities and identified business semantics, automatically generate target field types and conversion expressions, and conduct a lossless risk assessment of the conversion process, outputting risk levels and alternative solutions. S5. Perform runtime conversion: During data transmission, perform data type conversion according to the generated mapping rules and conversion expressions, and execute a preset fallback strategy for abnormal data that cannot be converted without loss. S6. Validation of Conversion Results: After the data is written, perform field-level consistency validation on the source and target data and output a diagnostic report.

2. The method according to claim 1, characterized in that, The source metadata in step S1 includes one or more of the following: field name, field type, length, precision, scale, nullability, default value, primary key, index, comment, and character set; the target type capability information includes one or more of the following: supported field types, length limit, precision limit, time precision, character set, constraint rules, and write capability.

3. The method according to claim 1, characterized in that, The unified intermediate types mentioned in step S2 include at least: INT64, DECIMAL(p,s), STRING(length, charset, lengthUnit), DATETIME(precision,timezone), BOOLEAN, BINARY, JSON, ARRAY, MAP, TEXT, and GEOMETRY.

4. The method according to claim 1, characterized in that, The field semantic recognition described in step S3 is achieved through the following mechanisms: analysis of field names and annotations based on regular expression matching, analysis of statistical distribution characteristics of data samples, and matching with external business rule bases; the recognized semantics include: mobile phone number, ID card number, amount, timestamp, enumeration value, and JSON string.

5. The method according to claim 1, characterized in that, The lossless risk assessment described in step S4 covers risks of precision loss, length truncation, encoding conflicts, time zone offset, structural loss, and constraint conflicts; the risk levels are divided into low risk, medium risk, and high risk; for high-risk fields, the alternatives include mapping numeric types to higher precision or larger range types, mapping time types to fidelity strings, or extending the storage length of character types.

6. The method according to claim 1, characterized in that, The runtime transformation described in step S5 specifically includes: performing precision and range checks on numeric fields, time zone and precision processing on time fields, encoding and length checks on character fields, structural validity checks on JSON fields, and processing binary fields according to byte stream or Base64 strategies; the fallback strategy includes one or more of the following: blocking tasks, recording as dirty data, writing to a bypass table, using a faithful string as a fallback, and automatically expanding the target field type.

7. The method according to claim 1, characterized in that, The field-level consistency verification in step S6 includes: row count consistency verification, field null value ratio verification, numerical precision verification, time offset verification, character length verification, JSON structure verification, and sample hash verification.

8. A system for automatic field type mapping and lossless conversion for multi-source heterogeneous databases, characterized in that, The system runtime implements the steps of the automatic field type mapping and lossless conversion method for multi-source heterogeneous databases as described in any one of claims 1-7, including: The source metadata collection module is used to connect to the source database and collect source metadata of the tables to be synchronized. The target end type capability acquisition module is used to connect to the target database and collect target end type capability information; The Unified Intermediate Type Model module is used to normalize and map the source field types in the source metadata to a unified intermediate type that is independent of the specific database. The field semantic recognition module is used to identify the true business semantics of the source field by combining the field name and field annotation in the source metadata, as well as the field distribution and business rules obtained through data sample analysis. The automatic mapping rule generation module is used to automatically generate target field types and conversion expressions based on the unified intermediate type, target end type capabilities and identified business semantics, and to perform a lossless risk assessment of the conversion process, outputting risk levels and alternative solutions. The runtime conversion execution module is used to perform data type conversions based on the generated mapping rules and conversion expressions during data transmission, and to execute a preset fallback strategy for abnormal data that cannot be converted without loss. The conversion result verification module is used to perform field-level consistency verification on the source and target data after the data is written, and output a diagnostic report.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps of the automatic mapping and lossless conversion method for field types of multi-source heterogeneous databases as described in any one of claims 1-7.

10. An electronic device, characterized in that, include: Memory and processor; Memory: Used to store computer programs; Processor: Used to execute the computer program to implement the steps of the method for automatic mapping and lossless conversion of field types for multi-source heterogeneous databases as described in any one of claims 1-7.

Images