Method, system and device for comparing multi-source heterogeneous data tables and storage medium

Abstract

This invention relates to the field of data processing technology, specifically providing a method, system, device, and storage medium for comparing multi-source heterogeneous data tables. The method first extracts the table structure metadata of the source and target tables, generating multi-dimensional features for each field; then, it calculates the similarity of various features between the source and target fields, obtaining a comprehensive similarity score through weighted summation; finally, it establishes a field mapping relationship based on preset thresholds and matching rules. This method innovatively employs multi-dimensional feature fusion and dynamic weighting mechanisms, effectively solving the problem of field matching in heterogeneous data tables and significantly improving mapping accuracy and processing efficiency. The corresponding system includes four core modules: feature extraction, similarity calculation, weight allocation, and mapping establishment, and can be widely applied in data governance scenarios.

Description

Technical Field

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

[0002] In the field of data governance, enterprises often face the problem of excessive number of tables and fields across multiple systems and databases, as well as inconsistencies in table structures between different systems when integrating multi-source heterogeneous systems. When establishing mapping relationships between different table fields (such as CRM.user_id -- ERP.client_code) for data migration, data warehouse construction, or cross-system analysis, traditional comparison methods using rule engines based on name similarity are inefficient, have an error rate as high as 30%, and cannot adapt to massive table structures. Summary of the Invention

[0003] In view of the above-mentioned shortcomings of the prior art, the present invention provides a method, system, device and storage medium for comparing multi-source heterogeneous data tables to solve the above-mentioned technical problems.

[0004] In a first aspect, the present invention provides a method for comparing multi-source heterogeneous data tables, comprising: Obtain the table structure metadata of the source data table and the target data table respectively, and generate multi-dimensional features of each field in the source data table and the target data table based on their respective table structure metadata; For each source field in the source data table, calculate the similarity between the various field features of the source field and the corresponding field features of each target field in the target data table, and obtain multiple sets of feature similarity values; The multiple sets of feature similarity values ​​are weighted and summed to obtain the comprehensive similarity score between the source field and each target field; Determine whether the overall similarity score between the source field and each target field is greater than or equal to a preset threshold; If there is a score greater than or equal to the preset threshold, then according to the preset matching rules, a mapping relationship is established between the source field and at least one target field that meets the conditions.

[0005] In one optional implementation, the table structure metadata includes field names, data types, constraint information, and field comment descriptions.

[0006] In one optional implementation, based on the respective table structure metadata, multidimensional features of each field in the source and target data tables are generated, including: Generate semantic features based on the field name; Generate the structural features of the fields based on the case of their names and their data types; Obtain random sampled data of the field, and generate instance features of the field based on the value range distribution and pattern matching of the sampled data; Generate the relationship characteristics of the fields based on the field constraint information and the related table information.

[0007] In an optional implementation, the method further includes: Determine if a mapping for the source field exists in the mapping library: If so, the mapping is validated, and the mapping is adopted after the validation is passed; If not, then perform a matching operation between the source field and the target field.

[0008] In an optional implementation, the similarity between various field features of the source field and corresponding field features of the same type for each target field in the target data table is calculated, including: Name similarity is obtained by calculating the normalized string matching degree of field names based on improved edit distance; type similarity is calculated by cosine similarity of pre-trained type embedding vectors; semantic vectors of field descriptions are extracted using a domain pre-trained language model and similarity is calculated; instance similarity is calculated based on the statistical distribution distance of field value samples.

[0009] In one alternative implementation, improving the edit distance includes: Perform pinyin conversion and English word rootization on strings containing mixed Chinese and English characters; Dynamically configure replacement cost weights based on character type; Semantic substitution calculations are performed on specialized terms based on domain dictionaries.

[0010] In an optional implementation, the weighted summation of the multiple sets of feature similarity values ​​includes: Retrieve the knowledge base context and corresponding scene tags for the field; Input the feature vector of the field, the knowledge base context and the scene label into the pre-trained neural network to obtain the weight adjustment amount corresponding to each feature output by the pre-trained neural network; Based on the weight adjustment amount corresponding to each feature and the preset base weight, the dynamic weight of each feature is calculated; based on the dynamic weight of each feature, the similarity values ​​of multiple sets of features between the source field and the target field are weighted and summed to obtain the comprehensive similarity score between the source field and the target field.

[0011] Secondly, the present invention provides a comparison system for multi-source heterogeneous data tables, comprising: The feature extraction module is used to obtain the table structure metadata of the source data table and the target data table respectively, and generate multi-dimensional features of each field in the source data table and the target data table based on their respective table structure metadata. The feature matching module is used to calculate the similarity between the various field features of each source field in the source data table and the corresponding field features of each target field in the target data table, and obtain multiple sets of feature similarity values. The scoring calculation module is used to perform a weighted summation of the multiple sets of feature similarity values ​​to obtain the comprehensive similarity score between the source field and each target field. The scoring judgment module is used to determine whether the comprehensive similarity score between the source field and each target field is greater than or equal to a preset threshold. The mapping construction module is used to establish a mapping relationship between the source field and at least one target field that meets the conditions, based on a preset matching rule, if there is a score greater than or equal to a preset threshold.

[0012] Thirdly, a device is provided, comprising: Memory, used to store the comparison program for multi-source heterogeneous data tables; A processor, configured to implement the steps of the comparison method for multi-source heterogeneous data tables as provided in the first aspect when executing the comparison program for the multi-source heterogeneous data tables.

[0013] Fourthly, a computer-readable storage medium is provided, on which a comparison program for multi-source heterogeneous data tables is stored, wherein when the comparison program for multi-source heterogeneous data tables is executed by a processor, the comparison program implements the steps of the comparison method for multi-source heterogeneous data tables as provided in the first aspect.

[0014] The beneficial effects of this invention are that the method, system, device, and storage medium for comparing multi-source heterogeneous data tables provided by this invention significantly improve the accuracy and efficiency of field mapping through multi-dimensional feature fusion and dynamic weighting mechanisms. First, it uses four-dimensional feature calculation based on name, type, semantics, and instance to overcome the limitations of single-name matching and solve the naming differences problem in heterogeneous systems. Second, it introduces a dynamic weight allocation strategy to automatically adjust feature weights based on field characteristics, adapting to the needs of different business scenarios. Finally, it supports one-to-many mapping through preset matching rules, enhancing compatibility with complex structures. Attached Figure Description

[0015] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

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

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

[0018] Figure 3 This is a schematic diagram of the structure of a device provided in an embodiment of the present invention. Detailed Implementation

[0019] To enable those skilled in the art to better understand the technical solutions of this invention, the technical solutions of the embodiments of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this invention, and not all embodiments. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this invention.

[0020] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention.

[0021] The comparison method for multi-source heterogeneous data tables provided in this embodiment of the invention is executed by a computer device, and correspondingly, the comparison system for multi-source heterogeneous data tables runs in the computer device.

[0022] Figure 1 This is a schematic flowchart illustrating a method according to an embodiment of the present invention. Wherein, Figure 1 The executing entity can be a comparison system of multi-source heterogeneous data tables. Depending on different needs, the order of steps in this flowchart can be changed, and some can be omitted.

[0023] like Figure 1 As shown, the method includes: S1. Obtain the table structure metadata of the source data table and the target data table respectively, and generate multi-dimensional features of each field in the source data table and the target data table based on their respective table structure metadata; S2. For each source field in the source data table, calculate the similarity between the various field features of the source field and the corresponding field features of each target field in the target data table, and obtain multiple sets of feature similarity values; S3. The multiple sets of feature similarity values ​​are weighted and summed to obtain the comprehensive similarity score between the source field and each target field; S4. Determine whether the comprehensive similarity score between the source field and each target field is greater than or equal to a preset threshold; S5. If there is a score greater than or equal to the preset threshold, then according to the preset matching rules, establish a mapping relationship between the source field and at least one target field that meets the conditions.

[0024] In one embodiment of the present invention, matching of existing mapping rules is also included: Determine if a mapping for the source field exists in the mapping library: If so, the mapping is validated, and the mapping is adopted after the validation is passed; If not, then perform a matching operation between the source field and the target field.

[0025] In one embodiment of the present invention, based on step S1, the following will provide a possible embodiment and describe its specific implementation in a non-limiting manner.

[0026] S101. Obtain the table structure metadata of the source data table and the target data table respectively. The table structure metadata includes field names, data types, constraint information, and field comment descriptions.

[0027] To achieve structural mapping analysis between the source and target data tables, structured information extraction is required using metadata dictionaries provided by the database system (such as MySQL's INFORMATION_SCHEMA and Oracle's DBA_TAB_COLUMNS). For field names, the original naming format (including special symbols and delimiters) must be preserved to maintain semantic integrity. Data type extraction should cover basic types (such as INT and VARCHAR), length constraints (such as VARCHAR(255)), and precision information (such as DECIMAL(10,2)), and be uniformly converted to a standard data type system (such as the SQL-92 specification) to eliminate database dialect differences. The acquisition of constraint information requires layered processing: primary key constraints are achieved by identifying the PRIMARYKEY marker and the associated field combination; foreign key constraints require simultaneous extraction of the reference table name, reference field, and matching pattern (e.g., ONDELETECASCADE); NOT NULL constraints, unique constraints, etc., require parsing the CHECK clause or dedicated constraint markers. The extraction of field annotations should support multilingual text (e.g., Chinese annotations containing UTF-8 encoding), and natural language preprocessing (e.g., removing redundant punctuation and standardizing abbreviations) should be used to improve the accuracy of subsequent semantic analysis. For fields without explicit annotations, null value markers should be recorded to distinguish them from missing data.

[0028] S102. Based on the respective table structure metadata, generate multi-dimensional features for each field in the source and target data tables: The semantic feature generation based on field names adopts a hybrid semantic parsing framework: First, the names are structurally split using word segmentation tools (such as BPE-based sub-segmentation) (e.g., splitting "user_name" into "user" and "name"), and dedicated parsers are designed for naming rules such as camelCase (e.g., "userName") and underscore-based (e.g., "user_name"). Second, word vectors are generated using pre-trained language models (e.g., a domain-adjusted version of BERT), and semantic enhancement is performed by combining them with a domain terminology library (e.g., "account_no" in the financial field corresponds to "account number"). Finally, potential associations between fields are established through semantic similarity calculation (e.g., cosine similarity). Structural features include format features and type features: Format features construct binary feature vectors through case sensitivity recognition (e.g., all uppercase "USERID", first letter uppercase "UserId"); Type features employ a hierarchical encoding method, mapping basic data types (e.g., INT, DATE) to higher-level type systems (e.g., "numeric" "date"), and combining length information (e.g., the length value 50 of VARCHAR(50)) to construct numeric features. For composite types (e.g., JSON, ARRAY), their internal structure needs to be parsed and nested feature vectors generated to capture the hierarchical relationships of complex types. Instance feature generation is based on a stratified sampling strategy: full sampling is used when the table data volume is less than 100,000 rows; otherwise, stratified random sampling is used (to ensure the representativeness of samples in each value range), with the sample size controlled at 5%-10% of the total data volume (minimum of 1000 rows). Value range distribution features are quantified through statistics (such as minimum, maximum, mean, quantiles) and distribution type identification (such as normal distribution, uniform distribution); pattern matching features are used for string type fields, extracting format patterns through regular expression matching (such as email format "xxx@xxx.com", mobile phone number format "1\d{10}") and calculating the pattern matching rate (such as the percentage of samples matching the email format). For enumeration type fields, all distinct values ​​and their frequencies need to be recorded to capture the discrete value distribution features. The generation of relationship features requires the integration of constraint information and association rules: An explicit relationship graph is constructed based on primary key-foreign key constraints, recording referential integrity between fields (e.g., "order_id" refers to "order_id"); implicit associations are uncovered through field co-occurrence analysis (e.g., the frequency of occurrence of two fields in a JOIN operation); constraint strength features are generated for non-null constraints, unique constraints, etc. (e.g., the proportion of duplicate values ​​in a unique constraint field). For fields with functional dependencies (e.g., "total price = unit price × quantity"), the goodness of fit is calculated (e.g., R-squared in linear regression). 2 Values ​​quantify dependency strength to capture logical relationships between fields.

[0029] In one embodiment of the present invention, based on step S2, the following will provide a possible embodiment and describe its specific implementation in a non-limiting manner.

[0030] S201. Based on the improved edit distance, calculate the normalized string matching degree of the field name to obtain the name similarity.

[0031] To improve the robustness of field name matching, the edit distance algorithm is improved to achieve normalized string matching through a three-level optimization mechanism. For mixed Chinese and English naming scenarios, a two-way conversion strategy is first adopted: Chinese words are generated into pinyin strings through a pinyin conversion engine (supporting polyphonic character semantic disambiguation), while English words are processed by a stemmer (such as PorterStemmer) to remove word form suffixes and retain core word roots (such as converting "customers" to "custom"), making heterogeneous language names comparable in a unified character space.

[0032] The dynamic adjustment of character replacement costs employs a type-stratified mechanism: characters are divided into four categories: letters, numbers, symbols, and Chinese characters. Replacement costs between characters within the same category are set to a base value (e.g., the replacement cost between the letters 'a' and 'b' is 1). Cross-category replacement costs are weighted according to semantic relevance (e.g., the cost between the number '0' and the letter 'O' is set to 2 due to visual similarity, while the replacement cost between letters and symbols is set to 4). Simultaneously, a domain terminology weighting factor is introduced. For standardized abbreviations in the financial field such as "acct" and "account," a semantic replacement mechanism is triggered through domain dictionary matching, treating them as equivalent characters (reducing the replacement cost to 0.5), and recording the terminology matching confidence to correct the final distance value.

[0033] The final name similarity is normalized by the ratio of edit distance to the maximum string length and mapped to the [0,1] interval. The higher the value, the stronger the semantic consistency of the names.

[0034] S202. Calculate type similarity using the cosine similarity of pre-trained type embedding vectors.

[0035] Data type similarity calculation relies on a domain-adaptive type embedding model. First, a structured type corpus is constructed, covering basic types (such as INT, VARCHAR), composite types (such as JSON, ARRAY), and custom types. Each type data entry includes a syntactic description (such as "VARCHAR(255)"), a semantic description (such as "variable-length string"), and typical application scenarios. A pre-trained language model (such as a type-specific fine-tuned version of BERT) is used to deeply encode the type corpus, generating high-dimensional type embedding vectors. The vector dimension is set to 768 to balance semantic expressiveness and computational efficiency.

[0036] Type similarity is measured using vector space cosine similarity, which reflects the semantic association between types by calculating the cosine of the angle between two vectors. For types with hierarchical relationships (such as "INT" and "BIGINT"), the model learns their inherent inheritance relationship through fine-tuning, resulting in a significantly higher similarity score than heterogeneous types (such as "INT" and "DATE"). For composite types, the embedding vectors simultaneously encode their internal structural features (such as "ARRAY"). <int>"with "ARRAY" <float>The similarity will be higher than that with the "STRUCT" type.

[0037] S203. Use a domain-pre-trained language model to extract semantic vectors of field descriptions and calculate similarity.

[0038] The semantic matching of field descriptions employs a domain-enhanced semantic representation framework. First, the field annotation text undergoes standardized preprocessing: redundant markers (such as parentheses and special symbols) are removed, Chinese and English word segmentation is performed (using bidirectional maximum matching for Chinese and NLTK segmentation for English), and stop words are filtered (based on a domain-specific stop word list). For descriptions containing technical terms (such as "user unique identifier ID"), entity links are established using a domain knowledge graph to supplement the hierarchical relationship information of the terms (such as linking "ID" to the concept of "identifier").

[0039] Domain-specific pre-trained language models (such as FinBERT in finance and BioBERT in healthcare) are used to encode the processed descriptive text, and the [CLS] position vector is taken as the semantic vector of the field description. This vector not only captures the literal meaning but also encodes deep semantic relationships (e.g., "customer name" and "user name" will generate highly similar vectors). Semantic similarity is calculated using the cosine value of the vectors and combined with term overlap (e.g., the number of co-occurring domain terms) for secondary calibration to improve matching accuracy in professional scenarios.

[0040] S204. Calculate instance similarity based on the statistical distribution distance of field value samples.

[0041] Instance similarity is quantified through the statistical distribution characteristics of field values. A representative sample set is obtained using stratified sampling (the sample size ensures an error ≤ 5% at a 95% confidence level). Multi-dimensional distribution features are constructed for different data types: for numerical fields, mean, variance, quartiles, and distribution patterns (such as skewness and kurtosis) are extracted; for categorical fields, the frequency, proportion, and entropy of each category are statistically analyzed; and for string fields, length distribution, pattern matching rate (such as the proportion of email formats), and character set diversity are analyzed.

[0042] In one embodiment of the present invention, based on step S3, the following will provide a possible embodiment and describe its specific implementation in a non-limiting manner.

[0043] S301. Obtain the knowledge base context and corresponding scene tag for the field; The knowledge base's scenario tag system adopts a hierarchical definition mechanism, specifically including four core tag categories: Business domain tags: Automatically generated based on table name keywords (such as "finance", "medical", "e-commerce", etc.), used to identify the industry sector to which the data belongs; Data topic tags: determined through knowledge graph entity association analysis (such as "customer information", "transaction records", etc.), revealing the business meaning of the data; Sensitivity level labels: These labels use regular expressions to match field content and combine them with manual review and annotation (such as "PII Personal Identity Information") to indicate the data security level. Technical feature tags: generated based on the table structure analysis results (such as "high cardinality number segment", "time series field", etc.), describing the technical attributes of the field.

[0044] This hierarchical tagging architecture enables the collaborative representation of business semantics and technical features, providing multi-dimensional contextual support for intelligent field mapping.

[0045] Obtain the knowledge base context by following these steps: Query initiation: The mapping engine sends a context query request to the knowledge graph system, carrying the target field name and the unique identifier of the table to which it belongs; Knowledge retrieval: After receiving a request, the knowledge graph executes a graph traversal algorithm to query related entities, including: semantic relationships of related fields, business rule constraints, and scenario classification tags; Caching: Search results are automatically stored in the vector cache database and a 1-hour time-to-live (TTL) period is set. Result return: The caching system returns the structured context data packet to the mapping engine, completing the knowledge acquisition loop.

[0046] The returned context data packet adopts a three-layer structured design: 1. Scene tag layer "scene_tag": "Finance / Customer Information / PII" hierarchical structure: using a three-segment code separated by slashes.

[0047] Semantic parsing: The first paragraph identifies the business domain (e.g., finance), the middle paragraph defines the data subject (e.g., customer information), and the last paragraph indicates the sensitivity level (e.g., PII).

[0048] 2. Entity Association Layer "related_entities": [{"field": "id_card", "type": "sensitive field"},{"field": "phone", "type": "foreign key related user table"}].

[0049] Field Relationship Graph: Each entity contains field names and relationship types; Relationship types include: sensitive fields, foreign key associations, master-detail relationships, etc. Application value: Reveals the topological relationships of fields in a business system.

[0050] 3. Business Rules Layer "business_rules": [{"constraint": "length=18", "apply_to": "id_card"},{"action": "AES-256 encryption", "trigger": "storage operation"}].

[0051] Rule elements: constraints (such as length validation), target objects (such as ID number fields), and triggering operations (such as encrypted storage).

[0052] Execution mechanism: The "condition-action" trigger pattern is adopted.

[0053] S302. Input the feature vector of the field, the knowledge base context and the scene label into the pre-trained neural network to obtain the weight adjustment amount corresponding to each feature output by the pre-trained neural network; The pre-trained neural network adopts a multimodal fusion architecture and designs dedicated processing channels for heterogeneous input data. The feature vector channel uses a convolutional neural network (CNN) for local feature extraction, capturing local correlation patterns between field features through three 1D convolutional layers. The kernel size increases with each layer (3→5→7) to expand the receptive field. The knowledge base context channel uses a bidirectional long short-term memory network (BiLSTM) to process structured text information, filtering key business rules and entity association information through a gating mechanism. The hidden layer dimension is set to 256 to balance semantic capacity and computational efficiency. The scene label channel uses an embedding layer to convert hierarchical labels into low-dimensional dense vectors, and strengthens the feature representation of key labels (such as sensitivity level labels) through residual connections and attention mechanisms. The network training process employs a domain-adaptive strategy. Based on a general pre-trained model (such as BERT-base), it undergoes secondary fine-tuning using industry-specific datasets (such as customer information tables in the financial sector and medical record tables in the medical sector). The fine-tuning phase utilizes mini-batch gradient descent with a batch size of 32 and a dynamically adjusted learning rate (initial value 5e-5, decaying by 50% every 10 epochs). The model's output layer employs a multi-head attention mechanism to generate the weight adjustments for each feature. This attention weight distribution dynamically perceives the importance of features in different scenarios—for example, in a financial scenario, the attention weight corresponding to sensitivity level labels is significantly higher than that of technical feature labels, thus causing the model to tend to increase the matching weight of PII-related fields.

[0054] S303. Calculate the dynamic weights of each feature based on the weight adjustment amount corresponding to each feature and the preset basic weights.

[0055] The dynamic weight calculation employs a synergistic mechanism of base weights and adjustment amounts. Base weights are based on preset initial values ​​for feature types (name similarity 0.3, type similarity 0.25, description similarity 0.2, instance similarity 0.25), determined through domain expert experience and statistical analysis of historical matching data. Weight adjustment amounts are converted based on the feature importance scores output by the neural network, and standardized to map the adjustment amounts to the [-0.15, 0.15] range, avoiding weight imbalance caused by excessively large single adjustments.

[0056] The final determination of dynamic weights incorporates a scenario correction factor. For scenarios with the business domain label "medical," the weight adjustment of features associated with the sensitivity level label is automatically increased (correction coefficient 1.2). For fields with the technical feature label "high cardinality number segment," the weight ratio of instance similarity is strengthened (correction coefficient 1.1). The adjusted weights need to be normalized to ensure that the sum of the weights of each feature is 1. At the same time, a lower limit for the weights is set (the weight of each feature is not less than 0.1) to prevent features from being over-suppressed and to ensure the synergistic effect of multi-dimensional features.

[0057] S304. Based on the dynamic weights of each feature, the similarity values ​​of multiple sets of features between the source field and the target field are weighted and summed to obtain the comprehensive similarity score between the source field and the target field.

[0058] The calculation of the overall similarity score adopts a hierarchical weighted summation framework. First, the four sets of feature similarity values ​​(name, type, description, instance) between the source and target fields are standardized and mapped to the [0,1] interval through min-max scaling to eliminate dimensional differences. In the weighted fusion process, a feature confidence factor is introduced: for scenarios with missing field descriptions, the weight of description similarity is automatically reduced (confidence factor 0.8); for fields with fewer than 100 instance samples, the influence of instance similarity is weakened (confidence factor 0.7). The fusion results are optimized through a secondary calibration mechanism: when there are explicit constraints at the business rule layer (such as "ID number length must be 18 digits"), if the instance features of the source field and the target field do not match under this constraint, a penalty coefficient (0.9) is applied to the comprehensive score; if the associated entity layer shows that the two fields have a foreign key relationship, a reward coefficient (1.1) is added. The final comprehensive similarity score is retained to three decimal places, and the value range is strictly controlled within the range of [0,1]. Field pairs with a score ≥0.8 are marked as "high matching degree" and included in the subsequent manual review process; field pairs with a score in the range of [0.6,0.8) are considered "candidate matches" and need to be verified in conjunction with more business rules, thereby improving the system's recall rate while ensuring matching accuracy.

[0059] In one embodiment of the present invention, based on step S5, a possible embodiment will be given below, and its specific implementation will be described in a non-limiting manner.

[0060] When the combined similarity score between the source field and the target field is greater than or equal to a preset threshold, a multi-rule collaborative matching mechanism is triggered: Unique matching: If only one target field meets the threshold condition, a one-to-one mapping relationship is directly established, and the mapping type is marked as "deterministic matching". At the same time, the matching basis is recorded (such as "name similarity 0.92 + type exact match + business rule consistency"). Multiple candidate matching: If two or more target fields meet the conditions, they are sorted in descending order of comprehensive score. The top-ranked field is taken as the primary matching candidate, and the rest are used as alternative matches. At the same time, conflict detection is started. If there is an irreconcilable business rule conflict between the primary matching candidate and the source field (such as data length constraint contradiction), the suboptimal candidate is automatically selected until a compliant match is found. Combined mapping: For scenarios where source fields need to be split or merged (such as the source field "user information" corresponding to the target field "name" + "phone number"), if the weighted sum of the comprehensive scores of the combined fields is greater than or equal to the threshold, and the matching degree of each subfield is greater than or equal to 0.7, then a many-to-one or one-to-many mapping is established to generate a structured mapping expression (such as "user_info→name+phone").

[0061] Once the mapping relationship is established, it needs to undergo double verification: Technical verification: Verify the feasibility of data type conversion (e.g., INT to BIGINT is compatible, VARCHAR to DATE is incompatible) and the compatibility of length constraints (e.g., source field length ≤ target field length). Business validation: Check the consistency of sensitivity level labels (e.g., PII fields must be mapped to sensitive fields of the same level) and the applicability of business rules (e.g., encrypted fields must be mapped to target fields that support the same encryption algorithm). Mapping relationships that fail validation will be marked as "to be corrected" and automatically transferred to the manual review process.

[0062] For source fields with a comprehensive similarity score below a preset threshold, the system initiates a tiered manual review mechanism: Review queue construction: sort by field importance (primary key / foreign key > core business fields > ordinary fields) and sensitivity level (PII > internal sensitive > non-sensitive) to ensure that high-priority fields are processed first; Auxiliary information encapsulation: Generate an audit package for each field to be audited, which includes a multi-dimensional feature comparison table (name / type / instance similarity details), knowledge base context (associated entities and business rules), and historical similarity mapping cases, to reduce the cost of manual decision-making; Review result feedback: Manual labeling is supported for "confirmed mapping" (target field must be specified), "rejected mapping" (reason for rejection is recorded), or "information needs to be supplemented" (metadata completion process is triggered). All decisions will be updated synchronously to the knowledge graph as training data for subsequent model optimization.

[0063] In one application scenario, the following execution steps are included: 1. The system extracts table structure metadata from the source database (such as the business system MySQL) and the target database (such as the data warehouse Hive), including field names, data types, constraint information (such as primary keys / foreign keys), and field comments. It also performs random data sampling (at least 100 records) on each field to analyze the actual data patterns.

[0064] 2. Extract four types of features from each field and convert them into numerical vectors: Structural features: lowercase form of field names, standardized encoding of data types (e.g., mapping VARCHAR(18) to type ID=5), length constraint values; Semantic features: Use Chinese word segmentation tools to parse field comments and identify business entities (e.g., "ID number" → entity type ID_CARD); Example features: Analyze the value range distribution of the sampled data (such as the proportion of unique values ​​and the range of values) and pattern matching (such as the regular expression matching rate of mobile phone numbers); Relationship characteristics: Mark primary key / foreign key attributes and related table information.

[0065] 3. Query the historical mapping knowledge base. If a rule that meets the following conditions exists, it will be recommended directly: mapping records with the same source field and target field; the same business scenario tags (such as "finance-customer information"); and a confidence level higher than the threshold (default >0.9).

[0066] 4. For fields without historical rules, a dynamically weighted similarity calculation model is used: Total similarity = α * name similarity + β * type similarity + γ * semantic similarity + δ * instance similarity; The weighting coefficients are dynamically adjusted based on the field characteristics: When a field has comments, the semantic weight γ increases to 0.6 (base value 0.2). When the data type is numeric / date, the type weight β is increased to 0.7 (base value 0.4). When instance data patterns are highly consistent (difference <5%), the instance weight δ is increased to 0.5; Name similarity is calculated using an improved edit distance algorithm (considering mixed Chinese and English scenarios), while semantic similarity is calculated based on word vector cosine similarity.

[0067] 5. Output the top 3 matching suggestions in descending order of similarity to the interactive interface, where users can perform actions: Accept recommendations (automatically recorded in the knowledge base); Reject recommendations (select reason: type mismatch / semantic mismatch / other); Manually specify the mapping (add new rules).

[0068] 6. Dynamically update the knowledge base based on user feedback: Accept the recommendation: Increase the confidence of this rule (maximum 1.0) and add applicable scenario tags; Reject recommendation: Adjust the weight parameters according to the reason (e.g., increase the β weight if the reason is type mismatch); New rule: After review, data will be added to the database with an initial confidence level of 0.8.

[0069] In some embodiments, the comparison system for multi-source heterogeneous data tables may include multiple functional modules composed of computer program segments. The computer programs for each program segment in the comparison system may be stored in the memory of a computer device and executed by at least one processor to perform (see details). Figure 1 (Description) Functionality for comparing heterogeneous data tables from multiple sources.

[0070] In this embodiment, the multi-source heterogeneous data table comparison system can be divided into multiple functional modules according to its functions, such as... Figure 2 As shown. The module referred to in this invention is a series of computer program segments that can be executed by at least one processor and perform a fixed function, and is stored in memory. In this embodiment, the functions of each module will be described in detail in subsequent embodiments.

[0071] The feature extraction module is used to obtain the table structure metadata of the source data table and the target data table respectively, and generate multi-dimensional features of each field in the source data table and the target data table based on their respective table structure metadata. The feature matching module is used to calculate the similarity between the various field features of each source field in the source data table and the corresponding field features of each target field in the target data table, and obtain multiple sets of feature similarity values. The scoring calculation module is used to perform a weighted summation of the multiple sets of feature similarity values ​​to obtain the comprehensive similarity score between the source field and each target field. The scoring judgment module is used to determine whether the comprehensive similarity score between the source field and each target field is greater than or equal to a preset threshold. The mapping construction module is used to establish a mapping relationship between the source field and at least one target field that meets the conditions, based on a preset matching rule, if there is a score greater than or equal to a preset threshold.

[0072] Figure 3 The method for comparing multi-source heterogeneous data tables provided in the embodiments of this application can be applied to devices. Those skilled in the art will understand that the device structures involved in the embodiments of this invention do not constitute a limitation on the device. A device may include more or fewer components than illustrated, or combine certain components, or have different component arrangements. In the embodiments of this invention, the device includes, but is not limited to, laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The device may also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the embodiments of this application described and / or claimed herein.

[0073] 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 understand that the server structure shown in the figure does not constitute a limitation of the present invention. It may be a bus topology or a star topology, and may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0074] 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 storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk. When the execution instructions in the memory 320 are executed by the processor 310, the device 300 is able to perform some or all of the steps in the above method embodiments.

[0075] The processor 310 serves as the control center of the storage device, connecting various parts of the electronic device via various interfaces and lines. It executes software programs and / or modules stored in the memory 320, and calls data stored in the memory to perform various functions of the electronic device and / or process data. The processor can be composed of integrated circuits (ICs), such as a single packaged IC or multiple packaged ICs with the same or different functions connected together. For example, the processor 310 may consist only of a central processing unit (CPU). In this embodiment of the invention, the CPU may have a single processing core or include multiple processing cores.

[0076] The communication unit 330 is used to establish a communication channel, enabling the storage device to communicate with other devices. It can receive user data sent by other devices or send user data to other devices.

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

[0078] Those skilled in the art will clearly understand that the techniques in the embodiments of the present invention can be implemented using software plus necessary general-purpose hardware platforms. Based on this understanding, the technical solutions in the embodiments of the present invention, or the parts that contribute 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 portable hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk, or any other medium capable of storing program code. It includes several instructions to cause a computer device (which may be a personal computer, a server, or a second device, network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention.

[0079] The same or similar parts between the various embodiments in this specification can be referred to mutually. In particular, the device embodiments are basically similar to the method embodiments, so the description is relatively simple, and the relevant parts can be referred to the description in the method embodiments.

[0080] In the embodiments provided by this 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 instance, the division of modules is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple modules or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between systems or modules may be electrical, mechanical, or other forms.

[0081] The modules described as separate components may or may not be physically separate. 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 can be selected to achieve the purpose of this embodiment according to actual needs.

[0082] In addition, the functional modules in the various embodiments of the present invention can be integrated into one processing module, or each module can exist physically separately, or two or more modules can be integrated into one module.

[0083] Although the present invention has been described in detail with reference to the accompanying drawings and preferred embodiments, the present invention is not limited thereto. Various equivalent modifications or substitutions can be made to the embodiments of the present invention by those skilled in the art without departing from the spirit and essence of the invention, and such modifications or substitutions should all be within the scope of the present invention. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should also be covered within the protection scope of the present invention.< / float> < / int>

Claims

1. A method for comparing multi-source heterogeneous data tables, characterized in that, include: Obtain the table structure metadata of the source data table and the target data table respectively, and generate multi-dimensional features of each field in the source data table and the target data table based on their respective table structure metadata; For each source field in the source data table, calculate the similarity between the various field features of the source field and the corresponding field features of each target field in the target data table, and obtain multiple sets of feature similarity values; The multiple sets of feature similarity values ​​are weighted and summed to obtain the comprehensive similarity score between the source field and each target field; Determine whether the overall similarity score between the source field and each target field is greater than or equal to a preset threshold; If there is a score greater than or equal to the preset threshold, then according to the preset matching rules, a mapping relationship is established between the source field and at least one target field that meets the conditions.

2. The method according to claim 1, characterized in that, The table structure metadata includes field names, data types, constraint information, and field comment descriptions.

3. The method according to claim 2, characterized in that, Based on their respective table structure metadata, multi-dimensional features for each field in the source and target data tables are generated, including: Generate semantic features based on the field name; Generate the structural features of the fields based on the case of their names and their data types; Obtain random sampled data of the field, and generate instance features of the field based on the value range distribution and pattern matching of the sampled data; Generate the relationship characteristics of the fields based on the field constraint information and the related table information.

4. The method according to claim 1, characterized in that, The method further includes: Determine if a mapping for the source field exists in the mapping library: If so, the mapping is validated, and the mapping is adopted after the validation is passed; If not, then perform a matching operation between the source field and the target field.

5. The method according to claim 1, characterized in that, Calculate the similarity between various field features of the source field and the corresponding field features of each target field in the target data table, including: Name similarity is obtained by calculating the normalized string matching degree of field names based on improved edit distance; type similarity is calculated by cosine similarity of pre-trained type embedding vectors; semantic vectors of field descriptions are extracted using a domain pre-trained language model and similarity is calculated; instance similarity is calculated based on the statistical distribution distance of field value samples.

6. The method according to claim 5, characterized in that, Improved edit distance includes: Perform pinyin conversion and English word rootization on strings containing mixed Chinese and English characters; Dynamically configure replacement cost weights based on character type; Semantic substitution calculations are performed on specialized terms based on domain dictionaries.

7. The method according to claim 1, characterized in that, The weighted summation of the multiple sets of feature similarity values ​​includes: Retrieve the knowledge base context and corresponding scene tags for the field; Input the feature vector of the field, the knowledge base context and the scene label into the pre-trained neural network to obtain the weight adjustment amount corresponding to each feature output by the pre-trained neural network; Based on the weight adjustment amount corresponding to each feature and the preset base weight, the dynamic weight of each feature is calculated; based on the dynamic weight of each feature, the similarity values ​​of multiple sets of features between the source field and the target field are weighted and summed to obtain the comprehensive similarity score between the source field and the target field.

8. A comparison system for multi-source heterogeneous data tables, characterized in that, include: The feature extraction module is used to obtain the table structure metadata of the source data table and the target data table respectively, and generate multi-dimensional features of each field in the source data table and the target data table based on their respective table structure metadata. The feature matching module is used to calculate the similarity between the various field features of each source field in the source data table and the corresponding field features of each target field in the target data table, and obtain multiple sets of feature similarity values. The scoring calculation module is used to perform a weighted summation of the multiple sets of feature similarity values ​​to obtain the comprehensive similarity score between the source field and each target field. The scoring judgment module is used to determine whether the comprehensive similarity score between the source field and each target field is greater than or equal to a preset threshold. The mapping construction module is used to establish a mapping relationship between the source field and at least one target field that meets the conditions, based on a preset matching rule, if there is a score greater than or equal to a preset threshold.

9. A comparison device for multi-source heterogeneous data tables, characterized in that, include: Memory, used to store the comparison program for multi-source heterogeneous data tables; A processor, configured to perform the steps of the comparison method for the multi-source heterogeneous data table as described in any one of claims 1-7 when executing the comparison program for the multi-source heterogeneous data table.

10. A computer-readable storage medium storing a computer program, characterized in that, The readable storage medium stores a comparison program for multi-source heterogeneous data tables, which, when executed by a processor, implements the steps of the comparison method for multi-source heterogeneous data tables as described in any one of claims 1-7.

Images