Connectable table discovery method based on multi-granularity semantic fusion

Abstract

The invention relates to a connectable table discovery method based on multi-granularity semantic fusion, which comprises the following steps of: selecting a public data set containing connectable information, and extracting column pairs with clear connectable labels in the data set to form a column set; converting the column set into a data set D in a triple format, performing data enhancement, and then converting the data set D into a text format to obtain Y; selecting a general teacher model and a student model, and taking Y as input to obtain respective corresponding logit predicted values; and training the student model by adopting an AdamW optimizer, freezing teacher model parameters in the training process, and obtaining a trained student model after the training is completed. The method can be widely applied to data lake construction, metadata management, intelligent ETL tools and self-service BI platforms, and the data discovery efficiency and the data integration automation level are remarkably improved.

Description

Technical Field

[0001] This invention relates to the field of joinable table discovery in data mining, and particularly to a joinable table discovery method based on multi-granularity semantic fusion. Background Technology

[0002] The goal of joinable table discovery is to transform the traditional, repetitive, and inefficient process of manually searching for table joins into a precise, automated, and scalable intelligent discovery mechanism. This helps users efficiently identify semantic relationships and structural mappings across datasets. This capability not only significantly reduces data integration costs but also accelerates data analysis processes, optimizes query execution paths, and provides a solid data foundation for high-quality decision-making.

[0003] Over the past few decades, the number of open datasets from governments, academic institutions, and enterprises has exploded, bringing unprecedented opportunities for technological innovation, economic development, and social well-being. However, faced with massive amounts of data that are diverse in scale, origin, and structure, users often struggle to quickly locate and connect the information they need. Especially in key application scenarios such as data integration, data lake construction, and cross-source analysis, automatically identifying connectable relationships between different data tables—that is, "connectable table discovery"—has become a core challenge and technical bottleneck in breaking down data silos and fully unleashing the value of data.

[0004] Early joinable table discovery methods primarily relied on statistical similarity between sets of column values. They inferred potential relationships between tables by calculating the degree of overlap in these sets (e.g., Jaccard similarity), typically combining inverted indexes or hash approximation techniques to accelerate the search process, and supplementing with metadata or contextual information to enhance discriminative capabilities. These methods performed well on small-scale, low-cardinality datasets, effectively identifying candidate table pairs with high value overlap. However, their performance was significantly limited when faced with real-world data characterized by high cardinality, low overlap, sparse distribution, or semantically equivalent but differently expressed data. Because they depended on precise value matching, they struggled to capture semantic relationships, were susceptible to noise interference, and had low recall rates, failing to meet the demands of modern large-scale heterogeneous data environments for efficient, robust, and semantically aware automatic join discovery.

[0005] With the rapid development of natural language processing and deep learning technologies, research focus has gradually shifted from statistical matching based on value overlap to semantically aware joinable table discovery. Recent work has generally utilized pre-trained language models, word embedding techniques, and graph representation learning to encode column names, column values, or table context into low-dimensional semantic vectors. By calculating vector similarity (such as cosine similarity), table pairs with potential semantic associations are identified. These methods no longer rely on exact value matching but capture semantic equivalence relationships such as "New York" and "NYC," or "user ID" and "customer_id," significantly improving generalization ability in high-cardinality, heterogeneous, and noisy data. Some systems further combine inverted indexes, locality-sensitive hashing (LSH), or graph structure modeling to efficiently organize massive column vectors and accelerate nearest neighbor search; other studies integrate metadata, statistical features, and machine learning classifiers to construct end-to-end joinability prediction models, achieving scalable semantic join discovery in exabyte-scale data lakes. Overall, these semantic embedding-based methods break through the limitations of traditional value matching, propelling connectable table discovery from "formal matching" to "semantic understanding," and laying a key technological foundation for intelligent data integration and cross-source analysis in large-scale open data environments.

[0006] However, existing methods still have many limitations:

[0007] (1) Single semantic granularity and lack of multi-level modeling capabilities. Most existing methods rely on only a single level of semantic information, such as using only word embeddings of column names or column values, or focusing only on metadata structure (such as column type and schema). This "single-point" modeling makes it difficult to fully capture the true relational logic of the data. For example, a column named "City" may be connected to the "State" column because its value distribution is based on US state names, but this relationship may be overlooked if only column name similarity is considered; conversely, if only value distribution is relied upon, the semantic equivalence between "Country" and "Nation" cannot be understood. Therefore, the lack of joint modeling of metadata, value schema, and table context leads to incomplete semantic expression and weak generalization ability.

[0008] (2) Joining tables is inefficient and lacks efficient index support. Existing methods require calculating the similarity between the query column and a massive number of candidate columns one by one, with a time complexity of up to O(N). This results in slow response times at the scale of millions of columns, and the lack of efficient approximate indexes based on vector embedding makes it difficult to support real-time queries. Summary of the Invention

[0009] In view of the above-mentioned problems in the existing technology, the technical problem to be solved by the present invention is: how to improve the performance and efficiency of discovering joinable tables between data tables.

[0010] To solve the above-mentioned technical problems, the present invention adopts the following technical solution:

[0011] A joinable table discovery method based on multi-granularity semantic fusion includes the following steps:

[0012] S100: Select a publicly available dataset containing connectable information, extract column pairs with explicit connectable labels from the dataset containing connectable information to form a column set, and randomly select one column from the column set as the query sample column anchor;

[0013] Search the column set and select columns that are connectable to the anchor as positive sample columns. Randomly select one or more columns from the remaining columns other than the anchor and positive as negative sample columns.

[0014] The selected anchor and its corresponding positive and negative elements are combined to form a triplet sample;

[0015] Iterate through all columns in the column set to obtain the triplet sample for each column, and collect all triplet samples as the triplet sample set D.

[0016] S200: Data augmentation is performed on D to obtain a set of adversarial examples X;

[0017] S300: Convert the data information of each column in X into a text sequence format to obtain the column text sequence set Y;

[0018] S400: Select teacher model T and student model S, and initialize the parameters of T and S; both teacher model T and student model S adopt the Sentence-BERT architecture based on pre-trained language models. The Sentence-BERT architecture includes several Transformer encoder layers and mean pooling layers.

[0019] S500: Input Y into T and S respectively, and output the connectivity logit prediction value corresponding to T. t The logit prediction value of the connectivity corresponding to S s ;

[0020] S600: Constructing the total loss function L total L total The calculation expression is as follows:

[0021] L total =λ·L soft +(1-λ)·L hard

[0022] Among them, L soft Let L represent the KL divergence loss function. hardLet λ represent the binary cross-entropy loss function, and λ represent the balancing weight coefficient.

[0023] S700: Freeze the initialization parameters of T, take Y as the input of S, use the AdamW optimizer to train S, perform gradient backpropagation to update the parameters of S, stop training when the training reaches the maximum number of iterations or the total loss function converges, and obtain the trained S′.

[0024] S800: Input all columns in D into S′, output the embedding vector representation of all columns, and then store the embedding vector representation of all columns in the index structure to build the ANN index;

[0025] S900: Select the data table d to be tested, which comes from dataset C. Repeat S300-S800 to obtain the student model S” adapted to C. Then input C into S” to obtain the embedding vector representation of all columns in all data tables in C and the corresponding ANN index of C.

[0026] Select the embedding vector representation corresponding to any column in d, calculate the cosine similarity value between the corresponding embedding vector representation and the embedding vector representations corresponding to all other columns in C, and form a similarity value set;

[0027] Set a similarity threshold K, sort the values ​​in the similarity value set in descending order, and select the data table containing the columns corresponding to the top-K cosine similarity values ​​as the joinable table discovery results of d.

[0028] Preferably, the data augmentation of the training set in S200 is as follows:

[0029] Data augmentation of the training set involves injecting hard positive and hard negative samples into the training set. The steps for injecting hard positive and hard negative samples are as follows:

[0030] S210: Generation of difficult positive samples: Apply semantically preserving perturbation to each pair of positive samples (anchor, positive) to generate multiple variants with the same semantics but different expressions as difficult positive samples;

[0031] Generation of difficult-to-bear samples: Select columns from the column set that have similar characteristics to the anchor column but are actually not connected as difficult-to-bear samples;

[0032] S220: Inject the generated hard positive samples and hard negative samples into the training set in proportion to obtain the adversarial sample set.

[0033] Preferably, the content of the column text sequence set obtained in S300 is as follows:

[0034] Define the data information of each column in the adversarial sample set as metadata, and determine the required number of metadata n. Concatenate the metadata of each column in a structured manner to obtain a set of column text sequences. The format of each column text sequence in the set of column text sequences is: "Table:[table_name];Column:[column_name];Other Column:[other_column_names];Type:[data_type];Values:[val_1,val_2,...,val_n];Unique Count:[unique_count]", where [val_1,...,val_n] takes the first n non-empty deduplicated sample values ​​of the column. If there are less than n items, padding is added to the end.

[0035] Preferably, in S500, the connectedness logit prediction values ​​logit corresponding to T and S are output respectively. t and logit s The steps are as follows:

[0036] S510: Y contains two text sequences in two formats: [anchor, negative] and [positive, anchor]. Each text sequence contains a token sequence. For any input text sequence x, there is a corresponding token sequence for x. After word embedding and position encoding using torch.nn.Embedding, it is represented as follows:

[0037] H (0) =E token [x]+E pos [1,…,m]

[0038] Among them, H (0) Indicates that x passes through word embedding E token [·] and position code E pos The result after [·] indicates the length of the token;

[0039] S520: H (0) Context modeling is performed using an L-layer Transformer encoder to obtain the last hidden state.

[0040] The token vectors are aggregated using mean pooling to generate a fixed-dimensional sentence vector representation e:

[0041]

[0042] Where M∈{0,1} m A valid token mask;

[0043] S530: Select one text from Y with the structure [anchor, negative] and one text with the structure [positive, anchor]. [anchor, negative] corresponds to 'a', and [positive, anchor] corresponds to 'b'. Input the selected 'a' and 'b' into T and S respectively. Perform the above process independently on T and S to obtain the two embedding vectors corresponding to T. The two embedding vectors corresponding to S represent

[0044] S540: Will By concatenating and fusing the difference and interaction features, a classification input vector v for T is constructed. t :

[0045]

[0046] Will By concatenating and fusing the difference and interaction features, a classification input vector v for T is constructed. s :

[0047]

[0048] v t and v s The input is a shared fully connected classification header, which, after a non-linear transformation, outputs the corresponding connectability logit value. The calculation formula is as follows:

[0049] h = ReLU(W1v + b1)

[0050]

[0051] According to the above formula, when v = v t At that time, the connectability logit value of T is obtained. t When v = v s At that time, the connectability logit value of S is obtained. s ;

[0052] Where W1 represents the weights of the fully connected layer, and b1 represents the bias term of the fully connected layer. b1 represents the weight of the last fully connected layer, b2 represents the bias term of the last fully connected layer, and h represents the intermediate vector.

[0053] Preferably, the total loss function L is constructed in S600. total The steps are as follows:

[0054] S610: The KL divergence function is used to measure the difference in output probability distribution between the student model and the teacher model at the softmax temperature τ, and this difference is used as the loss function for SoftLoss. Defined as:

[0055]

[0056] Where σ is the softmax function;

[0057] The Hard Loss function is calculated using the binary cross-entropy loss function. Defined as:

[0058]

[0059] Where y∈{0,1} represents the true label. This represents the probability of predicted connectivity in the student model;

[0060] S620: will and The total loss function is obtained by constructing the balance coefficient.

[0061] Compared with the prior art, the present invention has at least the following advantages:

[0062] Advantage 1: This method employs an adversarial data augmentation strategy. It constructs "difficult positive samples" by introducing semantically preserving perturbations such as synonym substitution, character perturbation, and abbreviation completion, while simultaneously mining superficially similar but semantically unrelated "difficult negative samples" for model training. This approach effectively addresses the weak generalization ability of traditional methods due to limited training data and a lack of challenging samples, particularly when facing complex scenarios involving column name spelling variations, synonyms, and abbreviations. Through this targeted training mechanism, the model can ignore surface string differences in column names and focus on deep semantic consistency, significantly improving accuracy and robustness in real-world data lakes when faced with ambiguous naming and diverse expressions. This overcomes the fundamental deficiency of existing methods, which suffer from insufficient recognition accuracy due to their inability to effectively handle complex column name expressions.

[0063] Advantage Two: This method innovatively utilizes a pre-trained language model to perform deep semantic encoding on column names and their contextual content, generating high-quality, high-dimensional semantic vector representations. This deep semantic representation scheme successfully overcomes the fundamental limitation of traditional statistical or string matching methods in understanding semantics, solving the recognition failure problem these methods face when encountering synonyms, near-synonyms, or changes in business context. By mapping column content to the semantic space, this method can accurately determine the semantic equivalence of, for example, customer_id and client_identifier, and can distinguish the semantic differences between columns with the same name in different tables, achieving accurate matching based on semantic similarity. This fundamentally improves the recall rate in semantic connection scenarios while significantly reducing the false connection rate in complex semantic scenarios.

[0064] Advantage 3: This method transforms the semantic matching problem into a nearest neighbor search problem in a high-dimensional vector space and leverages the HNSW index structure of the FAISS library to achieve efficient retrieval, constructing a complete high-performance query system. This technical approach completely solves the performance bottlenecks of high query latency and high resource consumption caused by the need for pairwise comparisons or complex calculations in traditional exact matching or semantic join methods, overcoming the technical barrier that makes it difficult for large-scale data lakes to support interactive queries. Through an approximate nearest neighbor search mechanism, this method reduces the query time complexity from linear scanning in traditional methods to approximately logarithmic levels, achieving millisecond-level query responses. Ultimately, it overcomes the performance limitations of existing solutions in processing massive tables, enabling real-time, interactive join discovery in large-scale data lake environments to move from theory to practice. Attached Figure Description

[0065] Figure 1 This is a schematic diagram of the data processing and model training process of the present invention.

[0066] Figure 2 The implementation process is discovered for joinable tables.

[0067] Figure 3 This is a diagram illustrating the similarity of connectability among university entrepreneurial projects. Detailed Implementation

[0068] The present invention will now be described in further detail.

[0069] See Figures 1-2 A joinable table discovery method based on multi-granularity semantic fusion includes the following steps:

[0070] S100: Select a publicly available dataset containing connectable information, extract column pairs with explicit connectable labels from the dataset containing connectable information to form a column set, and randomly select one column from the column set as the query sample column anchor;

[0071] Search the column set and select columns that are connectable to the anchor as positive sample columns. Randomly select one or more columns from the remaining columns other than the anchor and positive as negative sample columns.

[0072] The selected anchor and its corresponding positive and negative elements are combined to form a triplet sample;

[0073] Iterate through all columns in the column set to obtain the triplet samples corresponding to each column, and collect all triplet samples as the triplet sample set D. In the experiment, the WebTables dataset is selected as the experimental dataset. Since WebTables provides annotation results for cross-table column pairs, all positive examples are generated based on real semantic or structural associations to ensure sample accuracy. Generate multiple triplets with different negative samples for each anchor column, and ensure that the negative samples come from tables of different topics or domains to enhance the model's robustness to domain shifts. In actual use, all triplets will be divided into training set, validation set and test set in an 8:1:1 ratio for model training and performance evaluation.

[0074] S200: Data augmentation is performed on D to obtain a set of adversarial examples X; the data augmentation is achieved using existing techniques or methods;

[0075] The data augmentation of the training set in S200 is as follows:

[0076] Data augmentation of the training set involves injecting hard positive and hard negative samples into the training set. The steps for injecting hard positive and hard negative samples are as follows:

[0077] S210: Generation of difficult positive samples: Apply semantically preserving perturbations to each pair of positive samples (anchor, positive) to generate multiple variants with the same semantics but different expressions as difficult positive samples; samples are generated by calling ChatGPT-4o-mini. Semantically preserving perturbations include synonym replacement (e.g., “Country” → “Nation”), character-level editing (e.g., “customer_id” → “cust_id”), English abbreviation completion (e.g., “ID” → “Identifier”), field order perturbation (e.g., adjusting the order of table names, column names, values, etc. in the context description), and shuffling the order of field values ​​(e.g., randomly rearranging the sequence of example values). Adding these variant samples can force the relevant model to learn to ignore surface differences and focus on essential semantic matching;

[0078] Generation of difficult-to-bear samples: Columns with similar characteristics to the anchor column but which are actually not connected are selected from the column set as difficult-to-bear samples. The specific selection strategy is to select columns that have similar characteristics to the anchor column in name, value distribution, or context but are actually not connected. The selection rules based on similar characteristics are as follows: columns that share the same naming pattern (such as both containing "_id" or "_time") but belong to different entities, columns whose value set has a Jaccard score greater than 0.5 but are semantically unrelated (such as "grade_level" and "classroom_number" are both integers 1-5), and columns with the same name but different business contexts (such as "status" having different meanings in the order table and the user table). These seemingly similar but actually different columns are added to the training set as difficult-to-bear samples to strengthen the model's ability to distinguish subtle semantic boundaries.

[0079] S220: Inject the generated hard positive samples and hard negative samples into the training set in proportion to obtain the adversarial sample set;

[0080] All perturbation operations are performed independently or in combination. The introduction of hard positive samples and hard negative samples can guide the model to move away from hard negative samples while bringing positive samples closer, thus achieving more refined semantic space modeling.

[0081] S300: Convert the data information of each column in X into a text sequence format to obtain a set of column text sequences Y; generally, the table name, column name, first twenty example values, number of unique values ​​and data type of each column in the set will be concatenated into a structured natural language description (e.g., "Table: Orders; Column: Region; Other Column: Id, Customer; Values: CA, NY, TX, ...; Unique Count: 48; Type: String").

[0082] The contents of the column text sequence set obtained in S300 are as follows:

[0083] Define the data information of each column in the adversarial sample set as metadata, and determine the required number of metadata n. Concatenate the metadata of each column in a structured manner to obtain a set of column text sequences. The format of each column text sequence in the set of column text sequences is: "Table:[table_name];Column:[column_name];Other Column:[other_column_names];Type:[data_type];Values:[val_1,val_2,...,val_n];Unique Count:[unique_count]", where [val_1,...,val_n] takes the first n non-empty deduplicated sample values ​​of the column. If there are less than n items, padding is added to the end. Based on actual needs, obtain the table name, column name, first n example values, number of unique values, and data type of each column, and concatenate the obtained metadata into a structured natural language description, such as: "Table: Orders; Column: Region; Other Column: Id, Customer; Values: CA, NY, TX, ...; Unique Count: 48; Type: String"; In the experimental verification process of this invention, the value of n is 20;

[0084] Data types include common SQL types such as Varchar, Integer, and Date; the number of unique values ​​reflects the cardinality of the column; all fields are arranged in a fixed order to ensure input consistency; missing fields (such as those without valid sample values) are filled with the special marker "[NULL]"; finally, all text sequences are standardized (converted to lowercase and extra spaces are removed) before being used as input to the pre-trained language model; this effectively maintains data consistency and integrity, and can improve the model's ability to analyze and process data.

[0085] S400: Select teacher model T and student model S, and initialize the parameters of T and S; both teacher model T and student model S adopt the Sentence-BERT architecture based on pre-trained language models. The Sentence-BERT architecture includes several Transformer encoder layers and mean pooling layers; the Sentence-BERT architecture based on pre-trained language models is existing technology.

[0086] S500: Input Y into T and S respectively, and output the connectivity logit prediction value corresponding to T. t The logit prediction value of the connectivity corresponding to S s ;

[0087] The S500 outputs the connectivity logit prediction values ​​corresponding to T and S, respectively. t and logit s The steps are as follows:

[0088] S510: Y contains two text sequences in two formats: [anchor, negative] and [positive, anchor]. Each text sequence contains a token sequence. For any input text sequence x, there is a corresponding token sequence for x. After word embedding and position encoding using torch.nn.Embedding, it is represented as follows:

[0089] H (0) =E token [x]+E pos [1,…,m]

[0090] Among them, H (0) Indicates that x passes through word embedding E token [·] and position code E pos The result after [·] indicates the length of the token; torch.nn.Embedding is a current technique.

[0091] S520: H (0) Context modeling is performed using an L-layer Transformer encoder to obtain the last hidden state.

[0092] The token vectors are aggregated using mean pooling to generate a fixed-dimensional sentence vector representation e:

[0093]

[0094] Where M∈{0,1} m A valid token mask;

[0095] S530: Select one text from Y with the structure [anchor, negative] and one text with the structure [positive, anchor]. [anchor, negative] corresponds to 'a', and [positive, anchor] corresponds to 'b'. Input the selected 'a' and 'b' into T and S respectively. Perform the above process independently on T and S to obtain the two embedding vectors corresponding to T. The two embedding vectors corresponding to S represent

[0096] S540: Will By concatenating and fusing the difference and interaction features, a classification input vector v for T is constructed. t :

[0097]

[0098] Will By concatenating and fusing the difference and interaction features, a classification input vector v for T is constructed. s :

[0099]

[0100] v t and v s The input is a shared fully connected classification header, which, after a non-linear transformation, outputs the corresponding connectability logit value. The calculation formula is as follows:

[0101] h = ReLU(W1v + b1)

[0102]

[0103] According to the above formula, when v = v t At that time, the connectability logit value of T is obtained. t When v = v s At that time, the connectability logit value of S is obtained. s ;

[0104] Where W1 represents the weights of the fully connected layer, and b1 represents the bias term of the fully connected layer. b1 represents the weights of the last fully connected layer, b2 represents the bias term of the last fully connected layer, and h represents the intermediate vector.

[0105] The teacher model is used only for inference to generate high-confidence softlabels, and its parameters remain unchanged throughout the training process; the student model uses the logit output to participate in loss calculation and updates its own parameters during backpropagation, achieving end-to-end optimization; S600: Construct the total loss function L total L total The calculation expression is as follows:

[0106] L total =λ·L soft +(1-λ)·L hard

[0107] Among them, L soft Let L represent the KL divergence loss function. hardLet λ represent the binary cross-entropy loss function, and let λ represent the balancing weight coefficient. This model employs a dual supervision mechanism: on the one hand, Soft Loss (KL divergence) prompts the Student model to mimic the Teacher's output distribution; on the other hand, Hard Loss (binary cross-entropy) is introduced based on the true labels in the dataset, forcing the model to learn explicit connectivity boundaries. Both mechanisms work together to guide the model to both "understand semantics" and "know right from wrong." λ in the formula is used to adjust the contributions of knowledge distillation and supervised learning, ensuring semantic consistency while guaranteeing the model's accurate judgment of true connectivity relationships, providing a unified objective function for end-to-end training. λ is between 0 and 1, and this coefficient is dynamically adjusted according to the model's performance on the validation set: when the model converges slowly or has insufficient discriminative ability, λ is reduced to enhance the supervisory role of Hard Loss; when the model's semantic generalization ability is weak, λ is increased to enhance the soft loss's guidance on semantic distribution learning. Both types of losses are normalized before fusion to ensure they are of similar magnitude, preventing one from dominating the optimization process.

[0108] The total loss function L is constructed in S600. total The steps are as follows:

[0109] S610: The KL divergence function is used to measure the difference in output probability distribution between the student model and the teacher model at the softmax temperature τ, and this difference is used as the loss function for SoftLoss. Defined as:

[0110]

[0111] Where σ is the softmax function;

[0112] The Hard Loss function is calculated using the binary cross-entropy loss function. Defined as:

[0113]

[0114] Where y∈{0,1} represents the true label. This represents the probability of predicted connectivity in the student model;

[0115] The two types of loss functions together constitute a multi-task supervision signal, guiding the model to balance the ability to understand table semantics and the accuracy of connectable columns;

[0116] S620: will and The total loss function is obtained by constructing the balance coefficient.

[0117] S700: Freeze the initialization parameters of T, use Y as the input of S, train S using the AdamW optimizer, perform gradient backpropagation to update the parameters of S, and stop training when the training reaches the maximum number of iterations or the total loss function converges, resulting in the trained S′; during training, the T model remains frozen to provide a stable knowledge supply; when using the AdamW optimizer to update parameters, the learning rate is set to α, the batch size is set to batch size, the maximum number of training epochs is set to epochs, and the early stopping mechanism is set to terminate training if the F1 score does not improve after n consecutive validation sets; the gradient clipping value is set to w to prevent gradient explosion; backpropagation is only performed on the parameters of the student model, while the teacher model remains completely frozen; after each training epoch, the current optimal model weights are saved, and finally, the checkpoint with the highest F1 score on the validation set is selected as the final trained connectable table discovery model;

[0118] S800: Input all columns from D into S′, output the embedding vector representations of all columns, and then store the embedding vector representations of all columns in the index structure to build an ANN index; the embedding vector representations are uniformly stored in the index structure of the Approximate Nearest Neighbor ANN to support efficient retrieval when used; all column vectors are centrally stored in the HNSW Approximate Nearest Neighbor Index built by FAISS, with index parameters set to efConstruction=200, M=16; when searching, set efSearch=50; the vector database supports incremental updates, automatically extracting column information and appending it to the index structure when a new table is added, without needing to rebuild the entire index;

[0119] S900: Select the data table d to be tested, which comes from dataset C. Repeat S300-S800 to obtain the student model S” adapted to C. Then input C into S” to obtain the embedding vector representation of all columns in all data tables in C and the corresponding ANN index of C.

[0120] Select the embedding vector representation corresponding to any column in d, calculate the cosine similarity value between the corresponding embedding vector representation and the embedding vector representations corresponding to all other columns in C, and form a similarity value set;

[0121] Set a similarity threshold K, sort the values ​​in the similarity value set in descending order, and select the data table containing the columns corresponding to the top-K cosine similarity values ​​as the joinable table discovery results of d.

[0122] Example:

[0123] The university innovation and entrepreneurship project management database contains three tables: js_all (project summary table), js_award (project award table), and js_teacher (supervisor table). The js_all table records basic information about various competition projects (e.g., contest_id represents the project number); the js_award table stores the award information for each competition project (e.g., award_id, contest_id, contest_name); and the js_teacher table records basic information about the supervising teachers (e.g., teacher_id, name, contest_id).

[0124] In the calculation of connectability semantic similarity, the model identifies potential related fields between tables through multi-granularity semantic fusion. For example, the model calculates that the connectability similarity between js_all.contest_id and js_award.contest_id is 0.78, the similarity between js_all.contest_id and js_teacher.contest_id is 0.75, and the similarity between js_award.contest_id and js_teacher.contest_id is the highest, reaching 0.87.

[0125] This demonstrates that these fields are highly consistent at both the semantic and numerical levels, and can all be considered unique identifiers for the competition projects. Through this multi-granularity semantic fusion method, the system can automatically identify the connectible relationships between these fields, enabling intelligent discovery of potential foreign key relationships in the database.

[0126] Experimental content and results

[0127] The datasets selected for this invention comprise 20 datasets from publicly available websites, totaling 504 tables and 2700 columns. All data are labeled with verifiable joinable column pairs (based on foreign keys or manual verification). Dataset information is shown in Table 1.

[0128] Table 1: Database Statistics

[0129] Database name Number of tables Number of columns size PTE 38 76 ～4M AdventureWorks 71 486 ～234M Mondial 40 167 ～3M MooneyFamily 68 135 ～477 SAT 36 69 ～3M Airline 19 119 ～456M Geneea 19 128 ～61M Grants 12 46 ~891MB Hockey 22 300 ～16M Lahman 25 353 ～74M Mesh 29 37 ～1M Northwind 29 191 ～1M ErgastF1 14 98 ～60M Credit 9 75 ～318M Financial 8 55 ～79M Sakila 16 89 ～6M TPC-DS 24 61 ～4.8G TPC-D 8 61 ～2.5G TPC-C 9 93 ～166M TPC-H 8 61 ～2G

[0130] The experiment computed inclusion dependencies for each database, obtaining candidate foreign key dependencies (C) for each database. i ,P j Since the ER plots for each dataset are already provided on the relational-data website, positive and negative samples will be distinguished according to the ER plots. At the database level, a 10-fold cross-validation method was used for training. In each round, data from 18 databases were used for training, and data from two databases were used for validation. The average metric from 10 validation runs was recorded.

[0131]

[0132] The experiment uses three evaluation metrics—Precision@K, Recall@K, and F1@K—to assess the model's performance in finding connectable tables. The accuracy formula is: This represents the proportion of columns that can actually be joined in the Top-K candidate list returned by the model; the coverage formula is: This indicates how many truly joinable tables are covered by Top-K results; F1@K is the harmonic mean of Precision@K and Recall@K, calculated using the following formula: Where U represents all query columns; u represents a single query column; R(u) is the Top-K candidate columns returned by query u; T(u) is the actual set of joinable tables for query u; |·| represents the size of the set; Indicates the precision of a single query; This represents the recall rate of a single query.

[0133] The experimental model and parameter settings are shown in Table 2:

[0134] Table 2 Experimental Parameter Settings

[0135]

[0136]

[0137] To further verify the effectiveness of this invention, it was compared with popular mainstream recommendation algorithms, namely Joise and Aurum.

[0138] Table 2 Statistical Table of Results

[0139]

[0140] The experimental results show that the proposed method exhibits superior performance under various Top-K settings. Specifically, in terms of accuracy, the proposed method achieves 0.867, 0.908, and 0.926 for K=5, K=10, and K=20, respectively, which is higher than the comparative method, indicating that it can retrieve a higher proportion of true joinable columns in the candidate set. In terms of coverage, the proposed method steadily improves with increasing K, reaching 0.887 at K=20, which is on par with or even slightly better than mainstream methods, demonstrating strong recall capability. In terms of the comprehensive index F1, the proposed method outperforms the comparative method at all thresholds, especially reaching 0.916 at K=20, significantly outperforming other methods. In summary, the proposed method achieves a better balance between accuracy and coverage, providing higher stability and generalization ability in joinable table discovery tasks in large-scale relational databases.

[0141] Regarding the efficiency of joinable table discovery, this model introduces ANN technology in the candidate retrieval stage and utilizes the FAISSHNSW index for vector retrieval, significantly reducing the overhead of high-dimensional similarity calculation. While ensuring retrieval accuracy, the model can quickly return candidate columns in large-scale relational databases, greatly improving the efficiency of joinable table discovery.

[0142] This invention discloses a joinable table discovery method based on multi-granularity semantic fusion. By fusing multi-level information such as column names, table names, column value distribution, and statistical features, and combining multi-task learning and contrastive learning mechanisms, it achieves high-precision identification of potential join relationships between tables. Experiments show that this method significantly outperforms existing mainstream methods on multiple real-world datasets, especially demonstrating stronger robustness and practicality in challenging scenarios such as ambiguous naming and semantic complexity.

[0143] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention, and all such modifications or substitutions should be covered within the scope of the claims of the present invention.

Claims

1. A method for discovering joinable tables based on multi-granularity semantic fusion, characterized in that: Includes the following steps: S100: Select a publicly available dataset containing connectable information, extract column pairs with explicit connectable labels from the dataset containing connectable information to form a column set, and randomly select one column from the column set as the query sample column anchor; Search the column set and select columns that are connectable to the anchor as positive sample columns. Randomly select one or more columns from the remaining columns other than the anchor and positive as negative sample columns. The selected anchor and its corresponding positive and negative elements are combined to form a triplet sample; Iterate through all columns in the column set to obtain the triplet sample for each column, and collect all triplet samples as the triplet sample set D. S200: Data augmentation is performed on D to obtain a set of adversarial examples X; S300: Convert the data information of each column in X into a text sequence format to obtain the column text sequence set Y; S400: Select teacher model T and student model S, and initialize the parameters of T and S; both teacher model T and student model S adopt the Sentence-BERT architecture based on pre-trained language models. The Sentence-BERT architecture includes several Transformer encoder layers and mean pooling layers. S500: Input Y into T and S respectively, and output the connectivity logit prediction value corresponding to T. t The logit prediction value of the connectivity corresponding to S s ; S600: Constructing the total loss function L total L total The calculation expression is as follows: L total =λ·L soft +(1-λ)·L hard Among them, L soft Let L represent the KL divergence loss function. hard Let λ represent the binary cross-entropy loss function, and λ represent the balancing weight coefficient. S700: Freeze the initialization parameters of T, take Y as the input of S, use the AdamW optimizer to train S, perform gradient backpropagation to update the parameters of S, stop training when the training reaches the maximum number of iterations or the total loss function converges, and obtain the trained S′. S800: Input all columns in D into S′, output the embedding vector representation of all columns, and then store the embedding vector representation of all columns in the index structure to build the ANN index; S900: Select the data table d to be tested, which comes from dataset C. Repeat S300-S800 to obtain the student model S” adapted to C. Then input C into S” to obtain the embedding vector representation of all columns in all data tables in C and the corresponding ANN index of C. Select the embedding vector representation corresponding to any column in d, calculate the cosine similarity value between the corresponding embedding vector representation and the embedding vector representations corresponding to all other columns in C, and form a similarity value set; Set a similarity threshold K, sort the values ​​in the similarity value set in descending order, and select the data table containing the columns corresponding to the top-K cosine similarity values ​​as the joinable table discovery results of d.

2. The joinable table discovery method based on multi-granularity semantic fusion as described in claim 1, characterized in that: The data augmentation of the training set in S200 is as follows: Data augmentation of the training set involves injecting hard positive and hard negative samples into the training set. The steps for injecting hard positive and hard negative samples are as follows: S210: Generation of difficult positive samples: Apply semantically preserving perturbation to each pair of positive samples (anchor, positive) to generate multiple variants with the same semantics but different expressions as difficult positive samples; Generation of difficult-to-bear samples: Select columns from the column set that have similar characteristics to the anchor column but are actually not connected as difficult-to-bear samples; S220: Inject the generated hard positive samples and hard negative samples into the training set in proportion to obtain the adversarial sample set.

3. The joinable table discovery method based on multi-granularity semantic fusion as described in claim 2, characterized in that: The contents of the column text sequence set obtained in S300 are as follows: Define the data information of each column in the adversarial sample set as metadata, and determine the required number of metadata n. Concatenate the metadata of each column in a structured manner to obtain a set of column text sequences. The format of each column text sequence in the set of column text sequences is: "Table:[table_name];Column:[column_name];Other Column:[other_column_names];Type:[data_type];Values:[val_1,val_2,...,val_n];Unique Count:[unique_count]", where [val_1,...,val_n] takes the first n non-empty deduplicated sample values ​​of the column. If there are less than n items, padding is added to the end.

4. The joinable table discovery method based on multi-granularity semantic fusion as described in claim 3, characterized in that: The S500 outputs the connectivity logit prediction values ​​corresponding to T and S, respectively. t and logit s The steps are as follows: S510: Y contains two text sequences in two formats: [anchor, negative] and [positive, anchor]. Each text sequence contains a token sequence. For any input text sequence x, there is a corresponding token sequence for x. After word embedding and position encoding using torch.nn.Embedding, it is represented as follows: H (0) =E token [x]+E pos [1,…,m] Among them, H (0) Indicates that x passes through word embedding E token [·] and position code E pos The result after [·] indicates the length of the token; S520: H (0) Context modeling is performed using an L-layer Transformer encoder to obtain the last hidden state. The token vectors are aggregated using mean pooling to generate a fixed-dimensional sentence vector representation e: Where M∈{0,1} m A valid token mask; S530: Select one text from Y with the structure [anchor, negative] and one text with the structure [positive, anchor]. [anchor, negative] corresponds to 'a', and [positive, anchor] corresponds to 'b'. Input the selected 'a' and 'b' into T and S respectively. Perform the above process independently on T and S to obtain the two embedding vectors corresponding to T. The two embedding vectors corresponding to S represent S540: Will By concatenating and fusing the difference and interaction features, a classification input vector v for T is constructed. t : Will By concatenating and fusing the difference and interaction features, a classification input vector v for T is constructed. s : v t and v s The input is a shared fully connected classification header, which, after a non-linear transformation, outputs the corresponding connectability logit value. The calculation formula is as follows: h = ReLU(W1v + b1) According to the above formula, when v = v t At that time, the connectability logit value of T is obtained. t When v = v s At that time, the connectability logit value of S is obtained. s ; Where W1 represents the weights of the fully connected layer, and b1 represents the bias term of the fully connected layer. b1 represents the weight of the last fully connected layer, b2 represents the bias term of the last fully connected layer, and h represents the intermediate vector.

5. The joinable table discovery method based on multi-granularity semantic fusion as described in claim 3, characterized in that: The total loss function L is constructed in S600. total The steps are as follows: S610: The KL divergence function is used to measure the difference in output probability distribution between the student model and the teacher model at the softmax temperature τ, and this difference is used as the loss function for SoftLoss. Defined as: Where σ is the softmax function; The Hard Loss function is calculated using the binary cross-entropy loss function. Defined as: Where y∈{0,1} represents the true label. This represents the probability of predicted connectivity in the student model; S620: will and The total loss function is obtained by constructing the balance coefficient.

Images