Data warehouse table metadata similarity detection method and system based on double-channel local sensitive hashing

Abstract

The application discloses a kind of data warehouse table metadata similarity detection method and system based on double-channel local sensitive hash, belong to big data processing and data management field.The method includes obtaining table metadata from data warehouse;Based on column name information extraction structure feature set, based on column annotation information extraction semantic feature set;Respectively to two sets of vectorization is carried out, corresponding feature index is constructed, and based on feature index executes approximate similarity connection, obtains candidate table pair set;The accurate similarity of each table pair in candidate table pair set in structure feature dimension and semantic feature dimension is calculated and weighted fusion is carried out, obtains comprehensive similarity score, and based on comprehensive similarity score output detection result.The application solves the problem that the accuracy of table similarity detection is low and the calculation efficiency is poor due to inconsistent table naming, mixed English and Chinese annotations and noise column interference in the prior art under the environment of mass data warehouse.

Description

Technical Field

[0001] This invention belongs to the field of big data processing and data governance technology, specifically relating to a method and system for detecting the similarity of data warehouse table metadata based on dual-channel local sensitive hashing. Background Technology

[0002] With the explosive growth of enterprise data warehouses, massive amounts of data tables have accumulated in systems like Hive. Due to independent development by different teams, historical version iterations, and a lack of unified governance standards, data warehouses commonly contain a large number of tables with similar structures or semantically equivalent but inconsistent naming. This "duplication" of tables not only causes a serious waste of storage resources but also leads to redundancy in the ETL (Extract-Transform-Load) process and inconsistencies in data definitions, posing significant challenges to data governance.

[0003] In existing technologies, similarity detection for data tables mainly relies on precise string matching or simple text similarity calculation. These methods have the following obvious technical drawbacks: Insufficient semantic recognition capability: Precise string matching cannot identify cases such as "user_id" and "customer_id" which refer to the same business concept but have different names, resulting in a large number of semantically similar tables being missed.

[0004] Weak ability to handle mixed text: Column names in data warehouses are usually in English, while column comments are often a mix of Chinese and English. Existing solutions are unable to effectively normalize the same concept expressed in different languages.

[0005] Severe noise interference: The data tables generally contain audit fields such as "create_time" and "etl_date". These fields appear repeatedly in all tables. If they are not filtered, they will seriously dilute the similarity between business columns, resulting in distorted detection results.

[0006] Low computational efficiency: With tens of thousands of tables, the time complexity of pairwise comparisons is O(n²), which consumes huge computational resources and cannot meet the needs of practical engineering applications.

[0007] The results lack interpretability: Existing solutions typically only output a single similarity score, failing to provide detailed evidence of why the tables are similar, which hinders the data governance team from conducting subsequent manual review and decision-making. Summary of the Invention

[0008] The purpose of this invention is to provide a data warehouse table metadata similarity detection method and system based on dual-channel local sensitive hashing, which can solve the problems of low accuracy and poor computational efficiency in table similarity detection caused by inconsistent table naming, mixed Chinese and English comments, and noise column interference in the context of massive data warehouses.

[0009] In a first aspect, the present invention provides a method for detecting the similarity of metadata in data warehouse tables based on dual-channel locality-sensitive hashing, comprising the following steps: Obtain table metadata information expanded by column from the data warehouse system, and filter the table metadata to exclude pre-defined temporary databases and noisy columns; Structural feature set is extracted based on column name information of filtered table metadata, and semantic feature set is extracted based on column annotation information of filtered table metadata. The structural feature set and semantic feature set are vectorized separately. Based on the structural feature vector and semantic feature vector, the locality-sensitive hashing algorithm is used to construct the corresponding structural feature index and semantic feature index for the metadata of each table. Approximate similarity join is performed independently based on the structural feature index and semantic feature index respectively. The two join results are merged to obtain the candidate table pair set. Calculate the precise similarity of each table pair in the candidate table pair set in both structural and semantic feature dimensions, and perform weighted fusion of the precise similarity in the two dimensions to obtain the comprehensive similarity score of the table pair. Output the detection result based on the comprehensive similarity score.

[0010] As an optional implementation method, extracting the structural feature set further includes: segmenting the column names into words, normalizing them based on the synonyms of the business word root library, and sorting and concatenating them, and combining the processed column names with their data types to form the structural feature set; Extracting the semantic feature set further includes: performing mixed Chinese and English word segmentation on the column annotations, and normalizing the synonyms based on the business word root library to form the semantic feature set.

[0011] As an alternative implementation method, sorting and concatenating column names specifically involves: dividing the normalized column names into segments, sorting them in lexicographical order, and then connecting them with underscores to eliminate the impact of word order differences in column names on similarity detection.

[0012] As an alternative implementation method, mixed Chinese and English word segmentation includes: Use regular expressions to segment column comments into consecutive Chinese character segments, consecutive English letter segments, and consecutive number segments; For continuous Chinese character segments, a forward longest matching algorithm based on a business word root library is used for segmentation.

[0013] As an alternative implementation, filtering table metadata to exclude preset noise columns includes: filtering column-level metadata based on an audit field blacklist using both exact matching and regular expression matching modes. The audit field blacklist includes at least time-related fields, ETL-related fields, partition-related fields, and general primary key fields.

[0014] As an alternative implementation, independent local sensitive hash indexes are constructed for structural features and semantic features respectively, and approximate similarity joins are performed on them respectively. Then, the union of the two join results is taken so that data table pairs with similar structure or semantics can be recalled.

[0015] As an alternative implementation, the similarity detection method further includes: introducing physical dimension-assisted soft constraints, obtaining the number of records and storage size of table data, and when the ratio of the number of records or the ratio of the storage size between candidate table pairs exceeds a preset threshold, reducing the confidence level of the table pair, but not deleting the detection result.

[0016] As an optional implementation, the output detection results may also include: outputting a list of common structural features and a list of common semantic roots for each similar table pair, in order to provide a basis for the interpretability of the detection results.

[0017] Secondly, the present invention provides a data warehouse table metadata similarity detection system based on dual-channel locality-sensitive hashing, comprising: The metadata loading and filtering module is configured to: retrieve table metadata information expanded by column from the data warehouse system, and filter the table metadata to exclude preset temporary databases and noisy columns; The feature extraction module is configured to: extract a set of structural features based on the column name information of the filtered table metadata, and extract a set of semantic features based on the column annotation information of the filtered table metadata; The dual-channel recall module is configured to: vectorize the structural feature set and the semantic feature set respectively; based on the structural feature vector and the semantic feature vector, use the locality-sensitive hashing algorithm to construct the corresponding structural feature index and semantic feature index for the metadata of each table; and independently perform approximate similarity join based on the structural feature index and the semantic feature index respectively; and merge the two join results to obtain a set of candidate table pairs. The scoring and output module is configured to: calculate the precise similarity of each table pair in the candidate table pair set in both structural and semantic feature dimensions, perform weighted fusion of the precise similarity in the two dimensions to obtain the comprehensive similarity score of the table pair, and output the detection result based on the comprehensive similarity score.

[0018] As an alternative implementation, the feature extraction module distributes the blacklist configuration and root word library configuration as broadcast variables to each computing node to perform distributed filtering and feature extraction in user-defined functions.

[0019] Compared with the prior art, the beneficial effects of the present invention are as follows: This invention proposes a data warehouse table metadata similarity detection method and system based on dual-channel Local Sensitive Hash (LSH). By constructing a dual-channel extraction mechanism for structural and semantic features, and combining the normalization and sorting concatenation of synonyms from a business root word library, it effectively solves the semantic gap problem caused by differences in naming systems and word order, significantly improving the accuracy of similarity detection. By introducing dual-pattern matching with an audit field blacklist, the interference of noisy columns is systematically eliminated, allowing similarity calculation to focus on core business columns. In terms of computational efficiency, dual-channel independent LSH indexes are used for candidate recall, reducing the computational complexity in a massive table environment from O(n²) to sub-quadratic complexity, while a union strategy ensures a high recall rate. Furthermore, this invention enhances the interpretability and robustness of the detection results by outputting a list of common features and introducing soft constraints on physical dimensions. This enables the method and system to efficiently and accurately discover duplicate or similar tables in large-scale, highly complex enterprise data warehouse governance scenarios, providing strong support for data governance decisions. Attached Figure Description

[0020] Figure 1 This is an overall flowchart of the data warehouse table metadata similarity detection method based on dual-channel local sensitive hashing disclosed in the embodiments of the present invention; Figure 2 This is a detailed flowchart of the feature extraction steps disclosed in the embodiments of the present invention; Figure 3 This is a flowchart of dual-channel locality-sensitive mapping candidate recall and precise scoring disclosed in an embodiment of the present invention; Figure 4 This is a schematic diagram of the normalization process for column names disclosed in an embodiment of the present invention; Figure 5 This is a schematic diagram of the process for performing mixed Chinese and English word segmentation on column annotations as disclosed in an embodiment of the present invention; Figure 6 This is an architecture diagram of a data warehouse table metadata similarity detection system based on dual-channel local sensitive hashing disclosed in an embodiment of the present invention. Detailed Implementation

[0021] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the scope of protection of this application.

[0022] The technical solutions disclosed in the various embodiments of this application are described in detail below with reference to the accompanying drawings.

[0023] Example 1 like Figure 1 As shown, this embodiment provides a data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing, including the following steps: Obtain table metadata information expanded by column from the data warehouse system, and filter the table metadata to exclude pre-defined temporary databases and noisy columns; Structural feature set is extracted based on column name information of filtered table metadata, and semantic feature set is extracted based on column annotation information of filtered table metadata. The structural feature set and semantic feature set are vectorized separately. Based on the structural feature vector and semantic feature vector, the locality-sensitive hashing algorithm is used to construct the corresponding structural feature index and semantic feature index for the metadata of each table. Approximate similarity join is performed independently based on the structural feature index and semantic feature index respectively. The two join results are merged to obtain the candidate table pair set. Calculate the precise similarity of each table pair in the candidate table pair set in both structural and semantic feature dimensions, and perform weighted fusion of the precise similarity in the two dimensions to obtain the comprehensive similarity score of the table pair. Output the detection result based on the comprehensive similarity score.

[0024] The following section uses the Hive data warehouse as an example to illustrate the specific steps of the technical solution of this invention.

[0025] Step S1: Metadata loading and preprocessing.

[0026] Data from a specified date partition is read from the metadata source table of the Hive data warehouse. This data is stored in a column-expanded manner, with each record corresponding to a field of a table, containing information such as database name (db_name), table name (table_name), table comment (table_comment), table owner (table_owner), column name (column_name), column data type (column_type), column comment (column_comment), column order (column_index), number of table records (num_rows), and table size (total_size). After loading, based on preset database exclusion rules (such as excluding databases whose names contain "tmp", "temp", or "staging"), metadata records from temporary databases are filtered out to ensure that subsequent calculations are performed only on the business tables.

[0027] Step S2, noise column filtering.

[0028] like Figure 2 As shown, this step aims to filter out audit fields or ETL fields that appear repeatedly in most tables and contribute no meaningful business logic. This is specifically implemented based on a configurable audit field blacklist. This blacklist supports two matching modes: Exact match: A predefined list of column names, including time-related fields (create_time, update_time, created_time, updated_time, insert_time, load_time, etc.), human-related fields (created_by, updated_by, create_user, etc.), ETL-related fields (etl_date, etl_time, batch_id, batch_no, job_id, etc.), partition-related fields (dt, ds, p_date, partition_date, etc.), and general primary key fields (id, pk, row_id, uuid, etc.). The match is case-insensitive.

[0029] Regular expression matching: A predefined list of regular expression patterns, such as "etl_". "Match fields that start with 'etl_'," "._time" matches fields that end with "_time". "_date" matches fields that end with "_date", etc.

[0030] For each column name, exact matching and regular expression matching are performed sequentially. If either rule is matched, the column is determined to be a noise column and is excluded from subsequent calculations.

[0031] Step S3, Two-Dimensional Feature Extraction. This step extracts features from the filtered column data from both structural and semantic dimensions.

[0032] Step S3a, structural feature extraction. For example... Figure 2 and Figure 4 As shown, the specific process is as follows: 1. Segmentation: Convert column names to lowercase and separate them by underscores "_". For example, "customer_id" is split into ["customer", "id"].

[0033] 2. Synonym Normalization: For each segment, query the synonym mapping table in the business root word database. The root word database defines the mapping between the standard root word and its variants. For example, the standard root word "user" corresponds to variants {"customer", "member", "account", "user", "customer", "member", "account"}. If a segment matches a variant or the standard root word itself, it is replaced with the standard root word; if a stop word (such as "the" or "of") is matched, the segment is discarded.

[0034] 3. Sorting to Eliminate Word Order: Sort the normalized non-empty segments lexicographically. For example... Figure 4 As shown, both "user_id" (normalized to "user" and "ID") and "id_user" (normalized to "ID" and "user") are sorted to obtain "ID" and "user", thus eliminating the word order difference.

[0035] It should be noted that sorting the segments within a single column name is to eliminate internal word order differences caused by different naming conventions (such as "User_ID" versus "ID_User"). As for differences in the order of different columns within a table, since this invention ultimately stores the column characteristics of each table as an unordered set, these differences are automatically eliminated during the aggregation process and require no additional processing.

[0036] 4. Combination Feature: Reconnect the sorted segments with underscores and combine them with the original data type (column_type) of the column to form a structure feature string of "normalized column name:type", such as "ID_User:bigint".

[0037] 5. Aggregation: For all columns of the same table, collect their structural feature strings into a set to form the structural feature set of the table.

[0038] Step S3b, semantic feature extraction. For example... Figure 2 and Figure 5 As shown, the specific process is as follows: 1. Hybrid word segmentation: column annotations are segmented into three types of tokens: consecutive Chinese characters, consecutive English letters and consecutive numbers by using regular expressions.

[0039] 2. Longest matching for Chinese: for the segmented Chinese fragments, a vocabulary sorted in descending order of length is constructed by using all Chinese entries in the business root lexicon. The forward longest matching algorithm is used for segmentation: starting from the first character, words from the longest to the shortest are tried in sequence, and a segmented word is output once matched, then the process continues for subsequent characters. For example, for the annotation "客户订单金额 (customer order amount)", the algorithm preferentially matches the longest root, and successfully segments it into "客户 (customer)", "订单 (order)", "金额 (amount)". Unmatched individual characters are processed as single-character tokens.

[0040] 3. Normalization and stop word removal: all tokens are normalized to standard roots through a synonym mapping table again, and stop words are filtered out.

[0041] 4. Aggregation: the normalized roots of all columns in a table are deduplicated to form a semantic feature set of the table.

[0042] In the Chinese-English mixed word segmentation process for column annotations, consecutive English letter fragments are converted to lowercase first, and then synonym normalization is performed based on the business root lexicon; if they hit stop words or noise patterns, they are filtered out; if they do not hit but comply with the business word retention rule, they can be retained as candidate business tokens. By default, consecutive number fragments are not directly retained as original semantic features, but are filtered according to preset rules, or normalized to numeric category placeholders, so as to avoid unnecessary interference of different values to semantic similarity.

[0043] Step S4: feature vectorization.

[0044] A vocabulary is constructed by taking the structural feature sets and semantic feature sets of all tables as the corpus respectively. Then, the feature set of each table is converted into a sparse vector by using the binary count vectorization method. In this vector, each dimension represents a feature item, and the value of each dimension is only 0 or 1, indicating the presence or absence of the feature item: a value of 1 indicates that the feature exists in the table, and a value of 0 indicates that the feature does not exist. This method does not record word frequency, so that the similarity between vectors can better reflect the inclusion relationship at the set level.

[0045] Step S5: dual-channel locality-sensitive hashing candidate recall. As Figure 3 shown, this step aims to efficiently screen out potentially similar candidate table pairs.

[0046] Channel A (Structure): An index model is constructed for all tables with non-empty structural feature vectors using the locality-sensitive hashing algorithm. An approximate similarity join operation is performed, with the join threshold set such that the Jaccard distance does not exceed 1-similarity_min (for example, if the minimum similarity threshold similarity_min is 0.5, the distance threshold is 0.5). The condition source_table_id<target_table_id ensures that each table pair appears only once, and a set of candidate table pairs in the structural dimension is generated.

[0047] Channel B (Semantic): Similarly, another set of index models is independently constructed for all tables with non-empty semantic feature vectors using the locality-sensitive hashing algorithm, and approximate similarity join is performed. A set of candidate table pairs in the semantic dimension is generated.

[0048] Finally, the two sets of candidate table pairs generated by Channel A and Channel B are merged and deduplicated to obtain the final set of candidate table pairs. In this way, table pairs that are structurally similar but have sparse annotations can be recalled by Channel A, while table pairs that have similar annotations but very different naming can be recalled by Channel B.

[0049] In a preferred embodiment, the locality-sensitive hashing algorithm is the MinHash LSH algorithm. The MinHash LSH algorithm generates MinHash signatures based on the non-zero dimension index set of binary sparse vectors, and constructs locality-sensitive hash buckets based on the MinHash signatures to perform approximate similarity join.

[0050] After the structural feature set and semantic feature set are binarized into vectors, their non-zero dimension indices represent the set of feature items contained in the table. When constructing the MinHash LSH index, the system applies multiple hash functions to the non-zero dimension index set respectively, takes the minimum hash value under each hash function, and forms the MinHash signature vector of the table. Then, locality-sensitive hash buckets are constructed based on the signature vector, and approximate similarity join is performed within the buckets to generate a set of candidate table pairs. After the candidate table pairs are recalled, the final score still calculates the exact Jaccard similarity based on the original feature set.

[0051] Step S6, Exact similarity calculation and weighted fusion.

[0052] For each pair of tables in the candidate set, obtain their original structural feature set and semantic feature set, and calculate the exact Jaccard similarity: ; wherein A and B are respectively the feature sets of two tables. The structural similarity is obtained respectively and semantic similarity Then, the data is weighted and merged according to preset weights to calculate the overall score: ; in, and The weights are the structural features and the semantic features, respectively, and satisfy the following conditions: + =1. In this embodiment, the default setting is... =0.4, =0.6, to accommodate scenarios with richer annotation information.

[0053] Step S7: Confidence level assessment and auxiliary constraint output.

[0054] Confidence level: Classified according to the overall score, for example: weighted _ score ≥0.8 indicates a HIGH level; 0.6≤ weighted _ score <0.8 indicates a MEDIUM rating; weighted _ score A value less than 0.6 is considered a LOW level.

[0055] Physical dimension soft constraints: calculate the storage size ratio and record count ratio between table pairs.

[0056] Storage size ratio: ; Record count ratio: ; when > or < hour( If the threshold is a multiple of the size (default 100), then the confidence level of the table pair will be forcibly reduced to LOW. The processing logic is the same. This is because tables with vastly different data volumes are usually not duplicated. However, it's important to note that this is a "soft constraint," meaning that only its reliability is reduced, without removing the table pair from the results, preserving the information for human judgment.

[0057] The aforementioned record count ratio and storage size ratio thresholds are configurable parameters, not fixed constants. These thresholds can be determined based on data warehouse stratification, table type, and the physical size distribution of historically confirmed similar table pairs. The default 100x is merely an example used to lower the confidence level of candidate table pairs with excessively large physical size differences, without deleting the detection results, thus preserving space for manual review.

[0058] Interpretability output: Finally, detailed information is output for each similar table pair, including: identifiers of the two tables, weighted composite score, similarity of each dimension, confidence level, physical dimension ratio, shared "list of structural features" and shared "list of semantic roots", calculation time and date partition. These lists clearly explain the specific reasons for the similarity of the table pair, which facilitates the review by the governance team.

[0059] Construction and use of business root thesaurus: This section details the organization form and normalization mechanism of the business root thesaurus.

[0060] The root thesaurus is organized in the key-value pair form of "standard root - synonymous variants". Each rule defines a standard root and a set of variant words. When the system starts, a reverse mapping table from variants to roots (variant→root) is constructed, and the root itself is also included in the mapping.

[0061] For example: Standard root: user, synonymous variants: customer, member, account, user, customer, member, account; Standard root: order, synonymous variants: work order, document, order, ticket, work order number, order number; Standard root: amount, synonymous variants: fee, price, cost, amount, price, cost, money, price; Standard root: number, synonymous variants: code, code, number, code, no, number, id; The system loads the root thesaurus when starting, and constructs a reverse mapping table from variants to standard roots. During normalization, the input token is converted to lowercase first, and then the reverse mapping table is queried. The standard root is returned if there is a hit, and the original word is retained if there is no hit.

[0062] For example, the column comment "客户编码" of Table A is segmented into "客户" and "编码", which are normalized to {user, number}; the column comment "用户编号" of Table B is segmented into "用户" and "编号", which are also normalized to {user, number}. The two are determined to be completely consistent in the subsequent Jaccard calculation.

[0063] This design enables both "客户编码" and "user_id" to be normalized into "user" and "number", so as to achieve semantic alignment.

[0064] Distributed computing implementation: as Figure 6As shown, this embodiment describes the implementation of the system on a distributed computing framework. During system initialization, three configuration files are read: the main configuration file contains algorithm parameters, thresholds, weights, and input / output table information; the blacklist file contains exact match column names and regular expression patterns; and the root word library file contains synonym rules and stop words.

[0065] During the initialization phase, the feature extraction module encapsulates the blacklist configuration, root word library configuration, and Chinese lexicon into broadcast variables, which are then distributed to each worker node in the cluster. Each UDF reads the configuration from its local copy of the broadcast variables during execution, performing blacklist checks, column name normalization, and annotation segmentation, eliminating the need to repeatedly transmit configuration data for each record.

[0066] The computation process utilizes the adaptive query execution and dynamic resource allocation mechanisms of a distributed SQL engine. Intermediate results are cached on demand to avoid redundant computations, and memory is unpersisted after use.

[0067] Parameter tuning strategy: This section explains how to adjust the main parameters.

[0068] Regarding weighting: If the column names in the data warehouse are well-defined and standardized, the weights of structural features can be increased (e.g., ...). = 0.6, =0.4); If the column names are not standardized but the comments are sufficient, the semantic feature weights can be increased (e.g., = 0.3, =0.7); if annotations are generally missing, then focus on structural features (e.g., = 0.7, =0.3).

[0069] Regarding thresholds, the minimum similarity threshold (similarity_min) affects both the leniency of candidate recall for locality-sensitive mappings and the filtering strength of the final results. Lowering it to 0.3~0.4 yields more candidates, while raising it to 0.6~0.7 reduces noise. High and medium confidence thresholds are used to classify the results, facilitating downstream processing according to priority.

[0070] Regarding locality-sensitive mapping parameters, the number of hash buckets (numHashTables) affects the balance between recall and computational cost. When the number of tables is less than a thousand, a setting of 3-5 is sufficient; when it exceeds ten thousand tables, it can be adjusted to 10-20.

[0071] Regarding auxiliary filtering: In environments where dimension tables and fact tables are mixed, the multiplier threshold can be lowered to 10-50 to make the degradation more aggressive; in scenarios where the data volume varies greatly across ODS / DWD layers in the same table, the multiplier threshold should be kept at 100 or higher to avoid false degradation.

[0072] Example 2 like Figure 6 As shown, this embodiment provides a data warehouse table metadata similarity detection system based on dual-channel locality-sensitive hashing, including: The metadata loading and filtering module is configured to: retrieve table metadata information expanded by column from the data warehouse system, and filter the table metadata to exclude preset temporary databases and noisy columns; The feature extraction module is configured to: extract a set of structural features based on the column name information of the filtered table metadata, and extract a set of semantic features based on the column annotation information of the filtered table metadata; The dual-channel recall module is configured to: vectorize the structural feature set and the semantic feature set respectively; based on the structural feature vector and the semantic feature vector, use the locality-sensitive hashing algorithm to construct the corresponding structural feature index and semantic feature index for the metadata of each table; and independently perform approximate similarity join based on the structural feature index and the semantic feature index respectively; and merge the two join results to obtain a set of candidate table pairs. The scoring and output module is configured to: calculate the precise similarity of each table pair in the candidate table pair set in both structural and semantic feature dimensions, perform weighted fusion of the precise similarity in the two dimensions to obtain the comprehensive similarity score of the table pair, and output the detection result based on the comprehensive similarity score.

[0073] Preferably, the feature extraction module distributes the blacklist configuration and root word library configuration to each computing node as broadcast variables, so as to perform distributed filtering and feature extraction in user-defined functions.

[0074] It should be noted that the above modules correspond to the steps in Embodiment 1, and the examples and application scenarios implemented by the above modules and their corresponding steps are the same, but are not limited to the content disclosed in Embodiment 1. It should also be noted that the above modules can be executed in a computer system as part of the system.

[0075] The embodiments of this application have been described above with reference to the accompanying drawings. However, this application is not limited to the specific embodiments described above. The specific embodiments described above are merely illustrative and not restrictive. Those skilled in the art can make many other forms under the guidance of this application without departing from the spirit and scope of the claims, and all of these forms are within the protection scope of this application.

Claims

1. A data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing, characterized in that, Includes the following steps: Obtain table metadata information expanded by column from the data warehouse system, and filter the table metadata to exclude preset temporary databases and noisy columns; Structural feature set is extracted based on column name information of filtered table metadata, and semantic feature set is extracted based on column annotation information of filtered table metadata. The structural feature set and the semantic feature set are vectorized respectively. Based on the structural feature vector and the semantic feature vector, the locality-sensitive hashing algorithm is used to construct the corresponding structural feature index and semantic feature index for the metadata of each table. Approximate similarity join is performed independently based on the structural feature index and the semantic feature index respectively. The two join results are merged to obtain a set of candidate table pairs. Calculate the precise similarity of each table pair in the candidate table pair set in both structural and semantic feature dimensions, and perform weighted fusion of the precise similarity in the two dimensions to obtain the comprehensive similarity score of the table pair, and output the detection result based on the comprehensive similarity score.

2. The data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing as described in claim 1, characterized in that, Extracting the structural feature set further includes: segmenting column names into words, normalizing synonyms based on the business word root library, and sorting and concatenating them, and combining the processed column names with their data types to form the structural feature set; Extracting the semantic feature set further includes: performing mixed Chinese and English word segmentation on the column annotations, and normalizing the synonyms based on the business root word library to form the semantic feature set.

3. The data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing as described in claim 2, characterized in that, The specific process of sorting and concatenating column names is as follows: after the normalized column names are divided into segments, they are sorted in lexicographical order and then connected with underscores to eliminate the impact of word order differences in column names on similarity detection.

4. The data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing as described in claim 2, characterized in that, The mixed Chinese and English word segmentation includes: Use regular expressions to segment column comments into consecutive Chinese character segments, consecutive English letter segments, and consecutive number segments; For the continuous Chinese character segments, the forward longest matching algorithm based on the business word root library is used for segmentation.

5. The data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing as described in claim 1, characterized in that, The filtering of table metadata to exclude preset noise columns includes: filtering column-level metadata based on an audit field blacklist using two modes: exact matching and regular expression matching. The audit field blacklist includes at least time-related fields, ETL-related fields, partition-related fields, and general primary key fields.

6. The data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing as described in claim 1, characterized in that, Independent local sensitive hash indexes are constructed for structural features and semantic features respectively, and approximate similarity joins are performed on them respectively. The two join results are then combined to ensure that data table pairs with similar structure or semantics can be recalled.

7. The data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing as described in claim 1, characterized in that, The similarity detection method further includes: introducing physical dimension-assisted soft constraints to obtain the number of records and storage size of table data; when the ratio of the number of records or the ratio of the storage size between candidate table pairs exceeds a preset threshold, reducing the confidence level of the table pair, but not deleting the detection result.

8. The data warehouse table metadata similarity detection method based on dual-channel locality-sensitive hashing as described in claim 1, characterized in that, The output detection results also include: outputting a list of common structural features and a list of common semantic roots for each similar table pair, so as to provide a basis for the interpretability of the detection results.

9. A data warehouse table metadata similarity detection system based on dual-channel locality-sensitive hashing, characterized in that, include: The metadata loading and filtering module is configured to: obtain table metadata information expanded by columns from the data warehouse system, and filter the table metadata to exclude preset temporary databases and noisy columns; The feature extraction module is configured to: extract a set of structural features based on the column name information of the filtered table metadata, and extract a set of semantic features based on the column annotation information of the filtered table metadata; The dual-channel recall module is configured to: vectorize the structural feature set and the semantic feature set respectively; construct corresponding structural feature index and semantic feature index for each table metadata based on the structural feature vector and the semantic feature vector using the locality sensitive hashing algorithm; and independently perform approximate similarity connection based on the structural feature index and the semantic feature index respectively; and merge the two connection results to obtain a candidate table pair set. The scoring and output module is configured to: calculate the precise similarity of each table pair in the candidate table pair set in the structural feature dimension and the semantic feature dimension, and perform weighted fusion of the precise similarity of the two dimensions to obtain the comprehensive similarity score of the table pair, and output the detection result based on the comprehensive similarity score.

10. The data warehouse table metadata similarity detection system based on dual-channel locality-sensitive hashing as described in claim 9, characterized in that, The feature extraction module distributes the blacklist configuration and root word library configuration to each computing node as broadcast variables, so as to perform distributed filtering and feature extraction in user-defined functions.

Images