DeepJoin: Joinable Table Discovery with Pre-trained Language Models

Given a data lake of tables, we extract all the columns in these tables, except those unlikely to appear in a join predicate (e.g., BLOBs), and create a repository of tables, denoted by $\mathcal{X}$. Given a query column $Q$, our task is to search $\mathcal{X}$ and find the columns joinable to $Q$. In this paper, we target equi-joins and semantic joins. Next, we define the joinability for these two types, respectively.

Given a query column $Q$ and a target column $X$ in $\mathcal{X}$, the joinability from $Q$ to $X$ is defined by the following equation.

where $|\cdot|$ measures the size (i.e., the number of cells) of a column, and $Q_{M}$ is composed of the cells in $Q$ that have at least one match in $X$. Here, the term “match” depends on the join type, i.e., an equi-join or a semantic join. We normalize the size of $Q_{M}$ by the size of $Q$ to return a value between 0 and 1. Moreover, the joinability is not always symmetric, depending on the definition of $Q_{M}$.

For equi-joins, we model each column as a set of cells by removing duplicate cell values, and define the equi-joinability as follows.

The equi-joinability defined above counts the the distinct number of cells in $Q$ that match those in $X$, and thus can be used to measure the equi-joinability (josie, ). Our method can be also extended to the case when columns are modeled as multisets, so as to support one-to-many, many-to-one, and many-to-many joins. In this case, we may measure the joinability by the number of join results and normalize it by the product of $|Q|$ and $|X|$, instead of $|Q|$ in Equation 1.

For semantic joins, we consider string columns and embed the value of each cell to a metric space $\mathcal{V}$ (e.g., word embedding by fastText (fasttext, )). As such, each string column is transformed to a multiset of vectors. We abuse the notation of a column to denote its multiset of vectors. Then, the notion of vector matching is defined as follows.

Given a query column $Q$ and a target column $X$, the semantic-joinability is defined using the number of matching vectors ¹¹1Besides this definition, semantic joins are also investigated in (sema-join, ), yet it studies the problem of performing joins rather than searching for joinable columns..

An advantage of the above definitions is that for both equi- and semantic-joinability, the training data can be labeled by an exact algorithm (e.g., JOSIE (josie, ) and PEXESO (pexeso, )) rather than experts, so that our model can be trained in a self-supervised manner. Following the above definitions, we model the problem of joinable table discovery as the following top-$k$ search problem, where joinability $jn$ is defined using either Definition 2.1 or 2.3.

In this paper, we focus on dealing with textual columns. For numerical columns, a typical solution is utilizing the statistical feature vector in Sherlock (sherlock, ), which converts a numerical column to a vector, and then we can invoke a vector search to look for joinable columns having similar statistics to the query column.

JOSIE (josie, ), a state-of-the-art solution to the equi-joinable table discovery problem, regards the problem as a top-$k$ set similarity search with overlap $|Q\cap X|$ as similarity function, and builds its search algorithm upon prefix filter (prefix-filter, ) and positional filter (positional-filter, ), which have been extensively used for solving set similarity queries. JOSIE creates an inverted index over $\mathcal{X}$, which regards each cell value as a token and maps each token to a postings list of columns having the token. Then, it finds a set of candidate columns by retrieving the postings lists for a subset of the tokens in $Q$ (called prefix). Candidates are verified for joinability and the top-$k$ is updated. While index access and candidate verification are processed in an alternate manner, JOSIE features the techniques to determine the their order, so as to optimize for long columns and large token universes, which are often observed in data lakes.

PEXESO (pexeso, ) is an exact solution to semantic-joinable table discovery. It employs pivot-based filtering (pivot-filtering, ), which selects a set of vectors as pivots and pre-computes distances to these pivots for all the vecotrs in the columns of $\mathcal{X}$. Then, given the vectors of the query $Q$, non-matching vectors can be pruned by the triangle inequality. A hierarchical grid index is built to filter non-joinable columns when counting the number of matching vectors.

As for the weakness, JOSIE inherits the limitation of prefix filter, whose performance highly depends on the data distribution and yields a worst-case time complexity of $O(|\mathcal{X}|\cdot(|Q|+\overline{|X|}))$, where $\overline{|X|}$ stands for the average size of the columns in $\mathcal{X}$. For PEXESO, despite a claimed sublinear search time complexity of $O(\log|\mathcal{X}_{V}|\cdot\log|Q|)$, where $\mathcal{X}_{V}$ denotes the multiset of all the vectors in the repository, it relies on a user-specific threshold for the count of matching vectors. This does not apply to the top-$k$ case, and the algorithm is downgraded to be linear in $|\mathcal{X}_{V}|\cdot|Q|$, because at the early stage of search, due to the low count of matching vectors in the temporary top-$k$ results, the pruning power of the grid index is next to none. In general, for search time, both JOSIE and PEXESO are linear in the product of column size and repository size, which compromises their scalability to long columns and large datasets. On the other hand, it is unnecessary to always find an exact answer, because data scientists are usually concerned with only part of the top-$k$ results and will choose a subset of them for the join. For this reason, we will design our solution with the following two goals: (A) it returns an approximate answer with sublinear time in $|\mathcal{X}|$, and (B) by encoding the query column to a fixed length, it is independent of $|Q|$ and $\overline{|X|}$ during index lookup and search.

Apart from exact solutions, LSH Ensemble (lsh-ensemble, ) is an approximate solution to equi-joinability. It partitions the repository and computes MinHash sketches (minhash, ) with parameters tuned for each partition. Unlike JOSIE, it targets the thresholded problem (i.e., $\frac{|Q\cap X|}{|Q|}\geq t$), which requires a user-specified threshold $t$ and thus is less flexible than computing top-$k$. Although adaptation for the top-$k$ problem is available, it suffers from low precision due to the many false positives introduced by transforming overlap similarity to Jaccard similarity for the use of MinHash, and it sometimes runs even slower than JOSIE (josie, ).

The column-to-text transformation belongs to prompt engineering, which works by including the description of the task in the input to train a model and has shown effectiveness in natural language processing tasks such as question answering (cot, ). In DeepJoin, we take advantage of metadata and consider seven options, shown in Table 1, where variables are quoted in dollar signs. In particular, n denotes the number of distinct cell values of the column; cell_i denotes the value of the $i$-th cell of the column, with duplicate values removed; stat denotes the statistics of the column, including the maximum, minimum, and average numbers of words in a cell; and table_context denotes the accompanied context of the table (e.g., a brief description). Some patterns are also used for creating other patterns. For example, col stands for the concatenation of all the cell values, with a comma as delimiter, and colname-col stands for the column name followed by a colon and the content of col.

In DeepJoin, we fine-tune two state-of-the-art PLMs: DistilBERT (distilbert, ), a faster and lighter variant of BERT (bert, ), and MPNet (mpnet, ), which leverages the dependency among predicted tokens through permuted language modeling and takes auxiliary position information as input to make the model see a full sentence, thereby reducing position discrepancy. We use sentence-transformers (sbert, ) to output a sentence embedding for a sequence of input text. It is also noteworthy to mention that for semantic joins, unlike PEXESO, we do not need to generate embeddings in the vector space $\mathcal{V}$ (Definition 2.2). Instead, the semantics is captured by the fine-tuned PLM.

Since PLMs have an input length limit max_seq_length (e.g., 512 tokens for BERT), in the case of a tall input column, we choose a frequency-based approach, e.g., taking a sample of the most frequent cell values from the column, whose number of tokens is just within max_seq_length. Then, we use the sample for column-to-text transformation. The reason is that they are more likely to yield join results. Here, the frequency of a cell value is defined as document frequency, i.e., the number of target columns in the repository that have this cell value. If columns are modeled as multisets, we may resort to other frequency-based approaches, such as TF-IDF and BM25 (DBLP:books/daglib/0021593, ).

In order to scale to large table repositories, we resort to approximate nearest neighbor search (ANNS). The embeddings of the columns in $\mathcal{X}$ are indexed offline. For online search, we find the $k$ nearest neighbors (kNN) of the query column embedding under Euclidean distance as search results. In particular, we use hierarchical navigable small world (HNSW) (hnsw, ), which is among the most prevalent approaches to ANNS (knn-tutorial, ). Since the search time complexity of HNSW is sublinear in the number of indexed objects (hnsw, ), the search can be done with a time complexity sublinear in the number of target columns, thereby achieving goal (A) stated in stated in Section 2.2. Moreover, for billion-scale datasets, we may use an inverted index over product quantization (IVFPQ) (ivfpq, ), and construct HNSW over the coarse quantizer of IVFPQ. Such choice has become the common practice of billion-scale kNN search (e.g., using the Faiss library (faiss, )).

The search consists of two parts: query encoding and ANNS. In query encoding, we transform the column to text, with a time complexity of $O(|Q|)$, and then we feed the text to the PLM, with a time complexity of $O(|M|\cdot|Q|)$, where $|M|$ denotes the model size. In ANNS, thanks to the use of HNSW, the time complexity ²²2We follow the complexity analysis in (hnsw, ). is $O(vl\log|\mathcal{X}|$, where $v$ is the maximum out-degree (controlled by a parameter for index construction) in HNSW’s index and $l$ is the dimensionality of column embedding. Compared to JOSIE and PEXESO, which are linear in $|\mathcal{X}|\cdot(|Q|+\overline{|X|})$, we reduce the time complexity to sublinear in $|\mathcal{X}|$ and it is independent of $|Q|$ and $\overline{|X|}$ in the ANNS. Although the query encoding is still linear in $|Q|$, the column embedding procedure can be accelerated by GPUs.

Given a repository $\mathcal{X}$, we collect column pairs in $\mathcal{X}$ with high joinability as positive examples. This can be done by a self-join on $\mathcal{X}$ with a threshold $t$, i.e., finding column pairs $(X,Y)$ such that $X\in\mathcal{X}$, $Y\in\mathcal{X}$, and $jn(X,Y)\geq t$. To this end, we invoke a set similarity join (prefix-filter, ) for equi-joins or use PEXESO for semantic joins. In case $\mathcal{X}$ is large, we can perform the self-join on a sample of $\mathcal{X}$.

In Definitions 2.1 and 2.3, the joinability is insensitive to the order of cells in a column, whereas PLMs are order-sensitive in their input. In order to make our model learn that the joinability is order-insensitive, we consider data augmentation by shuffling the cells in a column. In particular, we pick a percentage (called shuffle rate) of pairs $(X,Y)$ in the aforementioned positive examples, generate a random permutation of the cells of $X$, denoted by $X^{\prime}$, and insert $(X^{\prime},Y)$ to the set of positive examples. As such, the training set contains both $(X,Y)$ and $(X^{\prime},Y)$, hence to suggest the order-insensitive joinability. Given a shuffle rate of $r$, out of all the positive examples, $r/(1+r)$ of them are obtained from cell shuffle.

To define negative training examples, we choose to use in-batch negatives, an easy and memory-efficient way that reuses the negative examples in the batch and has demonstrated effectiveness in text retrieval tasks (dpr, ). Given a batch of positive training examples $\{\,(X_{i},Y_{i})\,\}$ (note that $X_{i}$ may be a shuffled column), we assume each $(X_{i},Y_{j}),Y_{i}\neq Y_{j}$ as a negative pair. Despite a very small chance that $(X_{i},Y_{j})$ are joinable, this can be regarded as noise in the training data and our model is robust against this case. In our experiments, it shows better empirical results than other options of making negatives such as removing matching cells from positives.

Given a batch of $N$ training examples $\{\,(X_{i},Y_{i})\,\}$, we minimize the multiple negative ranking loss (mnrloss, ), which measures the negative log-likelihood of softmax normalized scores:

The above loss function is one of the prevalent options (sbert-loss, ) for fine-tuning sentence-transformers (sbert, ). For the scoring function $S(\cdot,\cdot)$, we choose the cosine similarity of column embeddings, which shows the best empirical results. The subtlety here is that in the top-$k$ retrieval, Euclidean distance is used instead for the ANNS. The choice of metrics will be evaluated in Section 5.3.

In order to show that DeepJoin learned from a small subset of a corpus is able to generalize to a large subset, we randomly sample two subsets of 30k and 1M columns, respectively, from each corpus. From the 30k training set, we randomly sample column pairs whose $jn\geq 0.7$ as initial positive examples, where $jn$ is defined using Equation 2 for equi-joins or Equation 3 for semantic joins. We then apply the techniques in Section 4.1 for data augmentation and making negative examples. The 1M testing set is used as the repository $\mathcal{X}$ for search. To generate queries and avoid data leaks, we randomly sample 50 columns from the original corpus except those in $\mathcal{X}$. The dataset statistics are given in Table 2.

For equi-join, Table 3 reports the precision and the NDCG for $k$ from 10 to 50. JOSIE is omitted as it returns exact answers. For most competitors, the general trend is that both precision and NDCG increase with $k$. DeepJoin always outperforms alternatives and exhibits outstanding generalizability (trained on 30k columns and tested on 1M columns). The best performance, with an average precision of 72% and NDCG of 81%, is observed when MPNet is equipped. $\textsf{DeepJoin}_{\textsf{MPNet}}$ is better than $\textsf{DeepJoin}_{\textsf{DistilBERT}}$ because MPNet is pre-trained on a larger corpora and under a unified view of masked language modeling and permuted language modeling. LSH Ensemble’s performance is mediocre due to the conversion from overlap condition to Jaccard condition, which becomes very loose when the sizes of query and target significantly differ. For embedding methods, TURL is better than TaBERT because the pre-trained tasks (column type annotation, etc.) of TURL are closer to joinable table discovery than TaBERT’s question answering. Nonetheless, these tasks still significantly differ from joinable table discovery, and thus both are in general no better than fastText and BERT. Another reason why TaBERT and TURL exhibit inferior performance is due to the limited data for pre-training; e.g., TURL is pre-trained on entity-focused Wikipedia tables. fastText is better than BERT and MPNet, indicating that simply using PLMs without fine-tuning does not translate to higher accuracy than context-insensitive word embeddings. MLP roughly performs the best among the methods other than DeepJoin, showing that a regression on top of word embeddings further improves the performance.

For semantic join, Table 4 reports the precision and NDCG evaluated under PEXESO’s definition (Definition 2.3). $\textsf{DeepJoin}_{\textsf{MPNet}}$ reports an average precision of 91% and NDCG of 75%, delivering higher accuracy than alternatives for all the settings. fastText is competitive on Webtable but is not good on Wikitable. We also change the threshold $\tau$ for vector matching to 0.8 and 0.7, and report the accuracy in Tables 5 and 6, respectively. $\textsf{DeepJoin}_{\textsf{MPNet}}$ is still the best for low $\tau$ settings, though its precision and NDCG generally drop with $\tau$. Such trend is also observed in most other methods. This is because a lower $\tau$ suggests that more cell values are regarded as matching, and thus it tends to introduce less similar contents to the training examples, which are harder to deal with.

We then evaluate these methods using the labels from our database researchers. The precision, recall, and F1 score when $k=10$ are reported in Table 7. $\textsf{DeepJoin}_{\textsf{MPNet}}$ still performs the best. It is even better than PEXESO, and the advantage is remarkable, by a margin of 0.105 – 0.165 in F1 score. We believe there are two reasons. First, $\textsf{DeepJoin}_{\textsf{MPNet}}$ uses a fine-tuned PLM, which captures the semantics of table contents in a better way than PEXESO which uses fastText to embed cell values. Second, PEXESO defines matching cells with a threshold. When judged by experts for joinability, the matching condition may differ across cell values, queries, and target columns, whereas a fixed threshold may not fit all of them. For a detailed comparison, we show three typical win/lose examples in Table 8. “Win” means only DeepJoin is able to correctly identify this entry, while “lose” means a false positive for DeepJoin but a true negative for at least one other competitor. The first example, which pertains to headboards, shows that the attention mechanism focuses on the word “headboard” in the table title and the words indicating bed size in the cell values. The second example, which pertains to tall buildings, shows that the PLM captures the similarity between the titles of the query and the target, as well as the building names in their contents. The third example, a false positive of DeepJoin, is potentially due to incorrect alignment of metadata. While these examples suggest that PLMs perform better than context-insensitive word embeddings when there are phrases indicating strong joinability, we also observe opportunities for improvement.

To drill down the cases of semantic joins, we randomly sample 100 query columns from the original corpus and request our experts to label the search results ($k=10$) of four methods, LSH Ensemble, fastText, PEXESO, and $\textsf{DeepJoin}_{\textsf{MPNet}}$. We divide the results into two cases: joins for data cleaning (near duplicates) references and joins for related columns (attribute enrichment). In Webtable, there are 68 tables for near duplicates and 177 tables for attribute enrichment. In Wikitable, there are 164 tables for near duplicates and 330 tables for attribute enrichment. We report the recalls of the four competitors in Table 9. In general, lower recalls show that attribute enrichment is harder than near duplicates. This is expected, because for attribute enrichment, the matching condition is looser, meaning that we need to consider more columns that can be joined in a semantic manner. Nonetheless, $\textsf{DeepJoin}_{\textsf{MPNet}}$ exhibits superior performance in both cases, and the gap to the runner-up competitors are remarkable, especially for near duplicates, wherein the recall is around twice as much as the runner-up’s.

To investigate how the performance changes with column size, we divide target columns of Webtable into three groups according to their size: short (5 – 10 cells), medium (11 – 50 cells), and long ($>$ 50 cells). We only perform this experiment on Webtable because the number of columns in the long group is too small on Wikitable. For each group, we ensure that the query length is in the same range, and report the results in Table 10. For all the methods, the accuracy decreases with column size. This is because each column is transformed to a fixed-length object (MinHash sketch or vector). From the information perspective, the object after transformation has redundancy for short columns, but is compressed and more lossy for long columns. Nonetheless, $\textsf{DeepJoin}_{\textsf{MPNet}}$ is always the best, in line with what we have witnessed in the above experiments.

We also perform an experiment on a synthetic dataset with 512 to 4,096 rows, in order to investigate the case when the input sequence length exceeds the max_seq_length limit of PLMs (e.g., 512 tokens for DeepJoin). We synthesize 10k columns of 5 attributes: address, company, job, person, and profile, by using Faker (faker, ) with the default parameters that match real-world English word frequencies. We randomly take 50 columns as queries and the others are targets. The method that samples the most frequent cell values within max_seq_length tokens (see Section 3.2), is dubbed -frequency. For comparison, we consider another two options: -random, which randomly samples cell values with no more than max_seq_length tokens, and -truncate, which truncates to the first max_seq_length tokens. The results are reported in Table 11. $\textsf{DeepJoin}_{\textsf{MPNet}}$ still consistently outperforms other models. For the three sampling options, -random is generally better than -truncate, and -frequency is always the best, justifying our argument that frequent cell values are more likely to yield join results.

We first evaluate the impact of column-to-text transformation and test the seven options in Table 1. The results are reported in Tables 12 and 13. Adding column name at the beginning (colname-col) improves the performance of simply concatenating cell values (col). Adding table title at the beginning (options with title) also has a positive impact. Appending statistical information (options with stat) further improves the performance, whereas appending context (options with context) has a negative impact. The latter is because the context includes information irrelevant to the column. Among the seven options, title-colname-stat-col is the best.

We then evaluate the impact of cell shuffle for data augmentation. We vary the shuffle rate (defined in Section 4.1) and report the results in Tables 14 and 15. 0.0 means there is no shuffle. We observe that a moderate shuffle rate achieves the best performance (0.2 and 0.3 for equi-joins and semantic joins on Webtable, respectively, and 0.3 and 0.4 for equi-joins and semantic joins on Wikitable, respectively), indicating that shuffling the cells in columns helps the model learn that the joinability is order-insensitive. On the other hand, over-shuffling is negative and even worse than no shuffle. We suspect this is because the original order of cells in both datasets follows some distribution. The attention mechanism in the PLM can capture such distribution and focus on the cells that are more probable to match. When the order is too random, the attention mechanism loses focus and thus a detrimental impact is observed.

For the column embedding metrics used in offline training and online searching, we perform an evaluation of two metrics: cosine similarity and Euclidean distance. Since the Faiss library does not support cosine similarity for ANNS, we normalize column embeddings before ANNS and use the inner product as the metric, so as to output the same kNN results as using cosine similarity. The precisions when $k=10$ are reported in Table 19. Cosine similarity is better for training, while Euclidean distance is better for searching. The combination of cosine similarity for training and Euclidean distance for searching is overall the best. Since the performances of the two metrics are very close, users may address the discrepancy by choosing the same metric for training and searching. Nonetheless, we still use the best combination in our experiments.

We vary the number of target columns and report the average query processing time in Table 16. For embedding methods, we also report query encoding time, which includes column-to-text transformation and column embedding. JOSIE and PEXESO are the slowest for equi-joins and semantic joins, respectively, and both exhibit substantial growth of search time (e.g., around 2 times when we increase the Webtable size from 1M to 5M). LSH Ensemble is also slow, despite transforming columns to fixed-length sketches. In contrast, embedding methods are much faster, though the majority of time is spent on query encoding. For example, DeepJoin (with MPNet), even if equipped with a CPU, is 7 – 57 times (Webtable) and 3 – 32 times (Wikitable) faster than the above methods. The growth of search time is also slight (e.g., 1.09 times for equi-joins and 1.05 times for semantic joins, with Webtable’s size from 1M to 5M), showcasing its scalability. With the help of a GPU, DeepJoin is substantially accelerated and can be 103 and 421 times faster than JOSIE and PEXESO, respectively, and even faster than fastText.

Table 17 reports the query processing time when we vary $k$ from 10 to 50. The general trend is that we spend more time for a larger $k$. Nonetheless, the growth for DeepJoin is very slight, because most of its overhead is query encoding, which is independent of the choice of $k$. As such, we roughly observe a greater speedup over existing methods when we increase $k$ from 10 to 50.

To evaluate how the efficiency changes with column size, we use the same setting as in the corresponding accuracy evaluation (Table 10). Additionally, we sample and index only 300k target columns for each group, in order to eliminate the impact of the number of target columns. The results are reported in Table 18. The exact methods, JOSIE and PEXESO, exhibit considerable growth (1.9 and 1.5 times, respectively) of query processing time when we switch from short to long columns, which reflects the analysis in Section 2.2. In contrast, the growth for embedding methods is much slighter. For example, we only observe a growth of 1.09 times for DeepJoin with a CPU, and this only affects its query encoding rather than the ANNS. For DeepJoin with a GPU, we also observe a more remarkable speedup over the exact methods on longer columns.