Toward Dynamic Data-Driven Time-Slicing LSH for Joinable Table Discovery

Abstract

In legacy industrial systems, discovering joinable information between database tables is important for applications such as data integration and data analysis. Locality-Sensitive Hashing-based methods have been proven to be capable of handling chaotic and diverse table relationships, but these methods often rely on an incorrect assumption—that the similarity of table columns in the database directly reflects their joinability, causing problems related to the accuracy of their results. To solve this problem, this study proposes a dynamic data-driven time-slicing Locality-Sensitive Hashing method for joinable table discovery. This method introduces database log information and within different time slices, uses the co-occurrence matrix of data tables to determine their joinability. Specifically, it first performs a MinHash dimensionality reduction on database columns and then uses Locality-Sensitive Hashing to calculate the static similarity. Next, it identifies business modular time slices through database logs, calculates the dynamic similarity of the slice time data, and builds a co-occurrence matrix between tables. Finally, the joinability between data tables is calculated using the static similarity, dynamic similarity, and co-occurrence matrix. The experimental results demonstrate that this method effectively excludes tables that only have similarity but no business relationship for data cleaning, and its accuracy exceeds that of methods that only depend on similarity.

1. Introduction

The construction of factories has become the main driving force triggering growth in the digital economy, opening a new era of data-driven production and enterprise transformation. As a key element of production, the value of data is becoming increasingly apparent in this era. To fully utilize the core role of data, we need to organize and clean the information contained in the databases of various industrial systems, especially when considering the manufacturing sector, due to the complexity of manufacturing processes and technology. The relationships between data tables are very complex, with the number of tables far exceeding the number of ordinary systems, and it is necessary to join dozens of table fields to query and sort the required results. In the field of data element analysis, joining two or more database tables is a fundamental and necessary task. Database table joining is not only widely used in the fields of database management and business intelligence but is also commonly applied in the field of machine learning [1,2]. Finding tables that can be joined is essentially an inverse analysis process of the current database design; that is, a type of data schema analysis. At present, data schema analysis approaches can be divided into two types: schema-level and instance-level methods. Schema-level methods focus on the structure and design of the database; however, for small- and medium-sized manufacturing enterprises, information about the design of many legacy system databases is often difficult to obtain. Meanwhile, instance-level analysis methods focus on the actual data in databases. Existing instance-level association rule mining algorithms include Apriori [3] and FP-Growth [4], among others. Most research has focused on mining association rules; however, in the cleaning of data elements, more attention is paid to whether database columns can be subjected to joinable query operations to obtain detailed information about a particular business.

Existing column relationship schema-matching techniques mainly use simple statistics- and string-based comparisons, such as prefix/suffix detection, edit distance, value range, minimum/maximum similarity, and mutual information measurements based on the q-gram distribution [5,6,7,8]. Set similarity search is another version of this problem [9], which has been widely used in various applications such as document searches, keyword querying, entity recognition, and data cleaning [10]. As examples of relevant research, Zhu et al. [11] proposed the Intersection Estimation algorithm to find joinable tables; Zhang et al. [12] clustered data columns to discover joinable tables; Esmailoghli et al. [13] aggregated row values into hash values to construct a super key, facilitating the search for joinable tables across multiple columns; Dong et al. [14] used a pre-trained model to convert text attributes into high-dimensional vectors, thereby semantically connecting records through similarity predicates to obtain more accurate results; Grace et al. [15] proposed the Starmie framework, which uses a pre-training strategy to capture contextual semantic information in a table and uses the cosine similarity between column vectors as the join degree score; Santos [16] added binary relations to tables to improve the accuracy of joinable table retrieval using a knowledge base approach; and Ibraheem [17] outlined various popular methods for finding joinable tables in data lakes and their associated performance.

Regrettably, in the existing literature, similarity and joinability are often regarded as the same concept. Similar columns can be joined, and joined columns carry a certain degree of similarity, but similarity does not necessarily denote business relevance; this is because similarity tends to be analyzed from a data perspective, whereas joinability tends to be analyzed from a business perspective. Therefore, joinability can sometimes be meaningless. For instance, while the key attributes of a sales order table and an attendance information table may meet certain similarity requirements, they will not be joined during data cleaning. In this study, we found that the tables representing a user’s business activities are modular and correlated in a given time period. Therefore, we propose a dynamic data-driven time-slicing Locality-Sensitive Hashing (LSH) method (DTL) for joinable table discovery. This method considers both static and dynamic similarity, combined with a co-occurrence matrix (i.e., the number of times that the tables co-appear in the time segment) in order to determine joinability. When calculating the static similarity, all the data in the database are used, MinHash [18] is applied to reduce the dimensionality, and LSH [19] is used for calculation. Dynamic similarity is achieved through analyzing database logs and constructing time slices with business modularity; then, in these modularized data, the similarity between tables is calculated, allowing for a co-occurrence matrix to be constructed on this basis. This method can exclude tables that have high similarity but are irrelevant to the business. The proposed method was tested on a real enterprise data set, and the experimental results demonstrate that its accuracy in obtaining joined tables surpasses that of methods that rely solely on similarity.

The main contributions of this study include the following:

(1)

It investigates and defines a method for representing joinability through combining static and dynamic similarity.

(2)

It innovatively uses changes in database logs to study the joinability information between tables and reveals how to use database operation logs for dynamic similarity analysis.

(3)

It proposes a data-partitioning method based on time slices. By stacking different time slices, we can obtain the co-occurrence matrix of each table regarding specific operations. Using this co-occurrence matrix as a parameter in combination with the static and dynamic similarity, we can extract collections of joined tables.

2. Methodology

2.1. Preliminaries

In this subsection, the basic methods adopted in this study are introduced, including MinHash, LSH, and the use of database logs.

Table 1. Notation used in this study.

Symbol	Description
$A,B$	Data collection tables in the database.
$A_i,B_j$	$\mathrm{A} \mathrm{collection} \mathrm{of} \mathrm{data} \mathrm{in} \mathrm{column} i \mathrm{of} \mathrm{table} A$; collection of data in column j of table B.
$a_{ij}$	$\mathrm{The} \mathrm{data} \mathrm{value} \mathrm{in} \mathrm{row} i \mathrm{and} \mathrm{column} j \mathrm{of} \mathrm{table} A$.
$d(\dots )$	A distance function used to determine similarity.
$\rho$	The similarity value.
$Q$	A query column.
$R$	A target column in the repository.
$\tau$	The similarity threshold.
$l$	The relevance calculation function.
$h_{min}$	The minimum hash function.
$h$	The hash function.
$R_t$	A collection of logs in time period t.
$Field,{Field}_{rf}$	A collection of field names in the log within time period t; attribute f in table r.
$DATA\left(Field\right)$	A data collection of all fields.
$G\left(x\right)$	The hash function group
$s_{ij}$	The dynamic similarity between columns i and j.
$C_{AB},\sigma$	The number of co-occurrences within the time slice between tables A and B; probability of co-occurrence.

summarizes the notation frequently used in this study.

In this study, attributes and columns are considered equivalent, representing the collection of data in a table. Suppose we have a given table $A$. The i^th column of $A$ is labeled $A_i$, and the value in the i^th row and j^th column of $A$ is denoted by $a_{ij}$. Then, table $A$ can be denoted as

$$A=[A_1,A_2\dots A_n]=\left[\begin{array}{cccc}{a}_{11}& a_{12}& \dots & a_{1n}\\ a_{21}& a_{22}& \dots & a_{2n}\\ \dots & \dots & \dots & \dots \\ a_{m1}& a_{m2}& \dots & a_{mn}\end{array}\right]$$

(1)

The similarity between the attribute domains $A_i$ and $B_j$ is denoted as $\rho$, which is calculated as follows:

$$\rho =d(A_i,B_j)={\displaystyle \frac{A_i\cap B_j}{A_i\cup B_j}}$$

(2)

Given a similarity calculation method and a threshold $\tau$, the attributes $A_i$ and $B_j$ are considered a match if (and only if) $\rho \ge \tau$; otherwise, they are considered a mismatch. The matching degree $\rho$ measures the similarity between the two columns of data.

Similarity is generally considered as the basis of joinability. In a simple method for calculating column similarity, for the data in attribute $Q$, the similarity distance with respect to all elements in $R$ is calculated in turn. When the database contains $n$ columns of attributes, the time complexity of this method is $O\left(n^2\right)$. However, when the number of attributes reaches a certain order of magnitude (e.g., over 10,000), and the number of rows in each column set exceeds 1 million, the time consumption of this brute force calculation method will be very large, making it unfeasible.

In the literature [18], a method for calculating the Jaccard similarity between sets of any size was proposed. In this technique, a set of attributes is converted into a MinHash signature using a set of Minwise hash functions. Suppose $h_{min}$ is one such hash function; then, the minimum hash values for domains $A_i$ and $B_j$ are $h_{min}\left(A_i\right)$ and $h_{min}\left(B_j\right)$. The probability $\rho$ of two minimum hash values being equal is considered as the similarity between $A_i$ and $B_j$ [18]:

$$\rho =p(h_{\min }(A_i)=h_{\min }(B_j))=d(A_i,B_j)$$

(3)

LSH is a solution for the nearest neighbor search problem in high-dimensional space [19], which can also be applied to approximate R-nearest neighbor searches [20]. In LSH, if two objects are similar, their distances after projection using the distance-sensitive hash function will be very close; meanwhile, if they are dissimilar, their distances will be relatively far. William et al. [21] proposed the Multi-Probe LSH method; its core concept is to check all hash buckets that may contain neighboring points of the query point according to a specific probe sequence; that is, if an object is close to the query object but has not been projected into the same hash bucket, it might be projected into a bucket nearby. This method can effectively reduce memory and time overheads by reducing the use of hash tables and expanding the query range, and it can also accurately obtain the candidate set.

When a program runs normally, the database continuously generates logs, recording all operations in detail. Prior studies [22,23,24] have outlined the development trends and attention in this field of log analysis. For example, system administrators can obtain a better understanding of the health and operating status of the program [25] by regularly checking the log data. When a user performs a business operation on the system page, it may trigger changes in the data of multiple tables. For example, adding a new sales order involves inter-related tables such as the order information table, the detail table, the inventory table, and the shipping table. If the operation is successful, the data changes in these tables will be sequentially recorded in the database log; therefore, once a table changes, joinable tables are likely to appear in its context.

As the log record data are closely related to the business information, combining this information with respect to the similarity of existing data allows us to more effectively determine the joinability of tables.

2.2. Dynamic Data-Driven Time-Slicing LSH Method

This subsection describes a novel method for calculating the joinability of tables based on dynamic data-driven time-slicing LSH (denoted as DTL). This method processes the table data in a database using MinHash and LSH, obtaining the static similarity; then, it analyzes the database logs, constructs a time slice with modularity (with respect to the business information) to calculate the dynamic similarity, and forms a co-occurrence matrix. The co-occurrence matrix is then combined with the static and dynamic similarity data to obtain effective joinability information. The framework of the proposed method is depicted in Figure 1.

Figure 1 indicates the relevant modules of this study. Initially, LSH hash tables are generated by traversing the column data to establish static similarity. Next, the database logs are analyzed to extract time slices, and the dynamic similarity information between fields and the co-occurrence matrix between tables is constructed. Finally, using the LSH hash table and the dynamic similarity information, attribute elements that satisfy the similarity conditions are extracted and added to the candidate set, and the joinable table of the candidate set with the element to be queried is computed.

2.2.1. Constructing the Static Similarity

• Defining the Minwise Function

When defining several hash functions $h_i$, these functions can randomly change the order of the elements. A hash function can be defined as

$$h_i(x)=(a_i·x+b_i)\mathrm{mod}p$$

(4)

where $a_i$ and $b_i$ are randomly chosen constants, $p$ is a large prime number, and $x$ is a data set of attributes. For each column $Q$ in the database, the MinHash signature is the minimum value calculated by multiple hash functions. Using $k$ hash functions, the signature can be expressed as

$$MinHas{h}_Q=[\min (h_1(Q)),\min (h_2(Q)),\dots ,\min (h_k(Q))]$$

(5)

where $\mathrm{m}\mathrm{i}\mathrm{n}\left(h_i\left(Q\right)\right)$ represents the minimum value calculated by the column through the hash function $h_i$; thus, the high-dimensional attribute set can be reduced to k dimensions.

2.

Defining the LSH Function

In this method, $L$ hash functions $h_1,h_2,\dots ,h_L$ are chosen to form a hash function group $G\left(x\right)=(h_1\left(x\right),h_2\left(x\right),\dots ,h_L\left(x\right))$. For each hash function group $G_i\left(x\right)$, a hash table is constructed; this process is then repeated $S$ times to obtain $S$ hash tables.

3.

Calculating LSH Tables

Due to the large number of tables, fields, and data in the database, loading them all into memory will put pressure on the server and the database. To solve this problem, this method dynamically concatenates SQL to read column data based on the database configuration information (including table name, field name, field type, and field length), reducing the relative load on the server and database. After obtaining the necessary information, we can use the above function to calculate the MinHash signature and create an LSH hash table. The similarity set of the object being queried can be obtained through the LSH hash table, and the resulting similarity value is the static similarity.

2.2.2. Dynamic Data Acquisition

In small- and medium-sized manufacturing industries, database management systems such as MSSQL, Oracle, MySQL, PostgreSQL, and MongoDB are widely used, which all provide features for logging changes in data. To improve the efficiency of capturing changes in database logs, the Change Data Capture (CDC) strategy [26,27,28] can be employed. The core idea of CDC is to track and capture changes in the database, including insertions, updates, and deletions of data or tables, and then record these changes in the order they occurred in a complete manner. For subsequent analysis needs, the captured log data are processed into a specific format and stored in a high-throughput and durable Kafka [29] message queue, making it convenient for applications to consume and analyze data at different time intervals, as shown in Figure 2.

In Kafka, log information is unitized by row. Each row of the record corresponds to a data change in the table, and its contents include the change in table name, the field name, the field value, and the type of operation. It is read in time slices as the calculation unit, where a time slice is $t$ seconds. If $R_t$ represents the log set within time $t$, then $R_t=\{R_{t1},R_{t2},\dots ,R_{tm}\}$, where $R_{ti}$ represents the i^th log record in time slice $t$. Each $R_{ti}$ can be represented by four-tuple $(o_{ti},r_{ti},f_{ti},v_{ti})$, where $o_{ti}$ represents the operation type, including addition, modification, and deletion; $r_{ti}$ represents the table name; $f_{ti}$ is the field information in the table; and $v_{ti}$ is the list of field values. The latter is expressed as $v=\{old,new\}$; for the addition and deletion operations, the value of $old$ is empty; meanwhile, for modification operations, $old$ is the value before the modification and $new$ is the value after the update.

Let $Field=\cup \left\{\right(r_{ti},f_{ti}\left)\right|(o_{ti},r_{ti},f_{ti},v_{ti})\in R_t\}$ denote the union of combinations of table names and field names within time slice $t$. $Data\left(Field\right)$ represents the data set of all fields, and $Data\left({Field}_{rf}\right)$ represents the data set of field $f$ in table $r$. The initial condition is $Data\left(Field\right)=\varnothing$.

For the addition operation, the data in the corresponding fields are added to the data set. In the deletion operation, before they are deleted, these data are similar to some of the columns. Therefore, for the deleted data, we also perform the following addition operation:

$$Data(Fiel{d}_{rf})=Data(Fiel{d}_{rf})\cup \left\{v_{ti}\right|(o_{ti},r_{ti},f_{ti},v_{ti})\in R_t\wedge r=r_{ti}\wedge f=f_{ti})\}$$

(6)

For the editing operation, we record the data before and after the edit. Regarding joinability, if the data were joinable before, then the data after the edit are also joinable. Therefore, we add both the data before and after the edit to the collection, as follows:

$$\begin{array}{l}Data(Fiel{d}_{rf})=Data(Fiel{d}_{rf})\cup \{v_{ti}(old)\cup v_{ti}(new)|\\ (o_{ti},r_{ti},f_{ti},v_{ti})\in R_t\wedge r=r_{ti}\wedge f=f_{ti})\}\end{array}$$

(7)

In this way, we can obtain all dynamic attribute data within time slice $t$.

2.2.3. Time Slice and Dynamic Similarity

Choosing the appropriate time slice is key to ensuring the accuracy of the obtained dynamic similarity value. First, the optimal splitting point must be found, which is the maximum value for the time interval between two log records in the Kafka message queue after the end of the last read. However, this interval needs to be within a certain pre-defined threshold range of the last read time. Therefore, the minimum time length $I_{min}$ and the maximum time length $I_{max}$ of the read slice are taken as constraints for timestamp filtering, in order to prevent the time slice from being too large or small. Let $K$ be the timestamp of the last log read, $threshold$ be the minimum value of the time interval, and $T$ be the sequence of log timestamps; then, $T$ can be expressed as $T=[t_1,t_2,\dots ,t_n]$, where $t_i$ is the timestamp of the i^th log. First, we need to filter out the data information, denoted as $T^{*}$, with timestamps after $K$ and in the range between $I_{min}$ and $I_{max}$, as follows:

$$T^{\ast }=[t_i|t_i>K\wedge t_i-K\ge I_{\min }\wedge t_i-K\le I_{\max }]$$

(8)

Then, calculate the time interval between adjacent records in the data set $T^{*}$ and obtain the maximum value:

$$\Delta T=[\Delta t_i=t_{i+1}-t_i|t_i,t_{i+1}\in T^{\ast }]$$

(9)

$$\Delta t_{\max }=\max (\Delta T)$$

(10)

$$\Delta t=\left\{\begin{array}{l}\Delta t_{\max },\Delta t_{\max }\ge threshold\\ I_{\max },\Delta t_{\max }<threshold\end{array}\right.$$

(11)

To determine the optimal module, the value of the $threshold$ parameter must be set, which has a significant impact on the correlation results. In extreme cases, if the $∆t_{max}$ interval is very small and the current user is constantly operating, the log time may not distinguish the business modules as they are operating, so $I_{max}$ will be directly used as the slice time. Conversely, if the $threshold$ value is set too large, it may lead to slices that have a missed modularity, thus affecting the quality of time slicing and the accuracy of the results.

Through this method, we obtain the optimal timing point, time slice, and finally, the dynamic data information $R_{\Delta t}=\{R_{t1},R_{t2},\dots ,R_{tm}\}$ within this time slice. According to the above method, we can calculate the dynamic data set $Data\left(Field\right)$. We then use the MinHash signature and build an LSH hash table, using the Multi-Probe LSH method for similarity retrieval. A four-tuple $({field}_i,{field}_j,s, count)$ for the similarity between two candidate sets is defined, where ${field}_i$ represents the column name, $s$ represents the similarity value between columns i and j, and $count$ represents the cumulative number of time slices—that is, the number of times the table where the i^th column is located co-occurs, which is initially 0. Every time it occurs, an operation is performed to add 1 to $count$; as such, for the L^th time slice, it can be expressed as follows: $({field}_i,{field}_j,s_L, 1)$. For the existing time slices, the similarities $s$ between the same columns can be superimposed, as follows:

$$s={\displaystyle \frac{s·count+s_L}{count+1}}$$

(12)

$$\mathrm{count}=count+1$$

(13)

Using this method, after processing multiple time slices, we can obtain attribute matrix information that represents the dynamic similarity values.

2.2.4. Joinability Construction

When the data in a certain table change, joined tables often synchronize in the context of the log. This study uses a co-occurrence matrix to represent the number of times that tables appear together in a single time slice. If two tables frequently appear together in a time slice, their co-occurrence value and the corresponding probability of joinability are both larger. Assume that there are two data tables, $A$ and $B$, which appear at the same time in time slice $T_i$; as the number of time slices increases, the number of times that they appear together can be expressed as

$$C_{AB}(T_i)=C_{AB}(T_{i-1})+1$$

(14)

The value of co-occurrences $C_{AB}$ is initially 0 and will be increased by 1 when two tables appear at the same time in the same time slice. The larger $C_{AB}$ is, the more likely they are to belong to the same business module, and the stronger the join. If two columns can be joined with each other, they must have a certain similarity, so the attribute $A_i$ is queried through the LSH hash table and dynamic similarity information to obtain the candidate set that meets the similarity conditions; then, an element $B_j$ from it is selected, in turn, to obtain the static similarity between the attributes $A_i$ and $B_j$ for $d(A_i,B_j)$ and dynamic similarity $s(A_i,B_j)$. In the data cleaning, the tables that can be joined have a business module, and there is similarity in the data. The joinable degree of attributes $A_i$ and $B_j$ can be expressed as:

$$l(A_i,B_j)=\sigma ·d(A_i,B_j)+(1-\sigma )·s(A_i,B_j)$$

(15)

$$\sigma ={\displaystyle \frac{{\displaystyle \sum _i{C}_{AB}}}{i}}$$

(16)

where $\sigma$ represents the probability of tables $A$ and $B$ appearing together in all time slices. As an example, when a user generates a sales order, it will not be related to the meeting information table; however, this does not rule out the similarity between the data in the order module and the data in the meeting record table. Because they do not appear together, at this time, $\sigma =0$, and the dynamic similarity $s(A_i,B_j)$ is 0; thus, $l(A_i,B_j)$ is also 0. Although they are similar, they are not related. Through this method, we can exclude tables with no business relationships. If tables $A$ and $B$ have business relevance, when the time slice reaches a certain number, $\sigma \ne 0$. At this time, $s(A_i,B_j)$ tends to be similar to $d(A_i,B_j)$, and their similarity can represent their joinability. The joinability calculation process is shown in Figure 3.

The joinability between tables $A$ and $B$ is the maximum joinability value between the two table columns:

$$l(A,B)=\max (l(A_i,B_j))$$

(17)

In this way, the elements in the candidate set are computed to obtain the set of correlatability tables.

3. Experiments

3.1. Data Sets

In this study, we used publicly available data sets that are representative of the similarity processing field. However, this research required complete data log information; thus, to more closely approach the actual situation, we chose to use the most commonly used, most complex enterprise resource planning (ERP) database in the telecom cable industry, which has been used in the production environment for many years. It contains 5574 tables and 95,370 columns, with a total size of about 3 GB; the column type details are shown in Figure 4.

Figure 4 shows that most fields use char, float, varchar, and datetime; during the cleaning process, the joinability between data tables is mainly determined according to primary keys or business codes. We directly filtered out non-joinability fields such as datetime, binary, and short char, resulting in 6081 fields that satisfied the joinability requirements. Consequently, 713 tables containing records remained, with an average of 68,000 items per table. Therefore, we only needed to calculate the joinability of the filtered table fields; the common types of joinable fields are shown in

Table 2. Commonly used joined field information.

Join Key	Type	Length	Description
Primary Key	INT	Default, 11 bits	A self-incrementing approach is typically used, usually starting at 0. This approach results in higher similarity between columns.
	BIGINT	20 bits and above	A snowflake algorithm generates a unique value; there will be no duplicates.
Business Code	BIGINT	20 bits and above	Custom codes are unique in businesses, such as prefix + date + serial number.
	VARCHAR	10 bits and above
	CHAR	10 bits and above

.

The experiment was carried out on a computer equipped with an AMD 7840HS CPU (8 cores) with 64 GB of DDR5 memory. The operating system for this machine is Windows 11 Professional Edition, and the method was implemented on Python 3.8, using libraries such as numpy, pandas, and datasketch for data processing and analysis.

3.2. Evaluation Metrics

This study focused on improving the accuracy of joined table discovery by eliminating tables that only show similarity but are irrelevant to the business during the data cleaning process; thus, precision and recall, which are widely used, were adopted as the measurement standards. The evaluation criteria in this study were determined during manual data cleaning. For a query column $Q$, $TP$ denotes the set of tables with businesses joined with $Q$ identified through tagging, and $FN$ is the set of joinable tables calculated with the present method. Precision represents the proportion of correctly identified joinable tables out of all the predicted joinable tables, as follows:

$$p={\displaystyle \frac{TP\cap FN}{\mathrm{TP}\cup FN}}$$

(18)

Recall represents the proportion of correctly identified joinable tables out of the total set of annotated joinable tables and is defined as follows:

$$r={\displaystyle \frac{TP\cap FN}{TP}}$$

(19)

3.3. Performance Comparison

To highlight the effectiveness of the proposed approach, we chose to compare it with the previously proposed techniques LSH Ensemble [30], Aurum [31], Josie [11], and LSH. LSH Ensemble (L32, with the number of shards set to 32) uses a transformation that allows MinHash LSH to be employed with a set containment similarity; then, the algorithm reads and computes the exact set intersection size for candidates to obtain the ranked final results. Aurum utilizes schema- and instance-level information to identify different relationships between the attributes of a data lake. Though performing a two-step dissection and indexing the data, we can establish a graph structure for keyword searches and/or determination of joinability. Josie is a powerful database associative table discovery framework that uses an intersection-estimated connection search to evaluate the joinability of tables and return the top k joinable tables as results. The original algorithm only supports a top k search, so we modified the algorithm to set the intersection-estimated connection value as a condition for ending the search, and the intersection-estimated value is the same as $\tau$. At the same time, in order to better illustrate the perspective of this article, LSH is also selected as a comparison method.

For the experiment, $I_{min}$ was set to 20 s, $I_{max}$ to 5 min, and $threshold$ to 10 s. A total of 16 tables were selected, for which we obtained the average precision of the compared methods under different values of the similarity threshold $\tau$, as shown in Figure 5.

Figure 5 shows that when applied to data obtained from small- and medium-sized manufacturing industries, DTL was superior to Aurum, L32, and Josie in terms of joinability accuracy, with an average advantage of about 7% compared to the Josie framework. The methods of L32, Aurum, and DTL are based on the LSH index type. When the LSH method is used alone, its accuracy is very low because it can only represent static similarity, indicating that column similarity cannot not intuitively reflect the joinability between tables. The Aurum method achieves a relatively higher result than LSH alone because the relationship between the extracted data sets is used as a knowledge graph and is combined with database schema information. Although L32 divides the data set into different sections based on the distribution of column values, it does not incorporate time and cannot capture the shared contextual information of all tables within a specific timestamp, so its effectiveness is limited. Although Josie, as a powerful framework for discovering related tables, is insensitive to data distribution, its effectiveness still has some gaps compared with DTL. This also proves the feasibility and advantages of using static and dynamic similarity in finding joinable tables in small- and medium-sized manufacturing enterprises; the use of log data to construct business modules to remove similar but non-business joinable table information is the reason why the DTL method works well in small- and medium-sized manufacturing industries. Under the same conditions, the recall rate is shown in Figure 6.

Figure 6 indicates that the recall rate of the DTL was somewhat lower than those of Aurum, L32, and Josie; this lower recall rate is because these comparison methods solely rely on the similarity analysis of data dimensions, resulting in them obtaining higher recall than our method. In particular, the LSH method has a recall rate of over 90% because it completely regards similarity as joinable and covers many tables that have no business relationship, so its precision is very low. Additionally, this study employs a method that combines dynamic and static similarity in a modular manner. As a result, tables that are seldom manipulated do not appear in the log, and when $\sigma =0$, their degree of joinability is perceived as zero, implying that there is no business joinability. This table information will then be filtered out.

This experiment showed that, although the recall of the proposed method was slightly lower than the values for the compared methods, as the table set $FN$ covers truly business-related tables and filters out some irrelevant table sets, $TP$ tends to be similar to $FN$; therefore, the higher precision of the proposed DTL method was more meaningful.

The time complexity of the processing phase includes the creation of the hash table and the analysis of the logs, whereas the time complexity of the query phase remains the same as Multi-Probe LSH.

3.4. Investigation of Parameter Sensitivity

This subsection describes the effects of different parameter values on the experimental results.

3.4.1. Threshold Experiment

The $threshold$ value directly affects the size of the time slice. If it is too large, a situation where $\Delta t_{max}<threshold$ will occur, and the time slice will be adjusted to $I_{max}$. Experiments were conducted with different $threshold$ values under the same parameters, and the results are shown in Figure 7.

The best results were achieved when $threshold$ was in the range of 3 to 10 s. Considering the complexity of page operations in the industrial sector, the impact of $threshold$ is relatively limited when the value is small; users need to perform page switching or button-clicking operations when an operation is completed and then trigger another operation, which takes a certain amount of time to complete. However, when the time interval exceeded 15 s, the detection effect significantly decreased. This decrease is due to an inability to effectively divide the time slices, resulting in the filtering out of reasonable business modules, thus affecting the frequency of the co-occurrence matrix and leading to decreased accuracy.

3.4.2. $I_{max}$ Experiment

$I_{max}$ can directly affect the structure of the business modules. We used minutes as the unit of measurement and analyzed the impact of this parameter on the results, as shown in Figure 8.

Figure 8 indicates that when $I_{max}$ exceeded 1 min, the impact on the results was not significant. This is partly because, when acquiring time slices, there will be several slices that meet the required time within $I_{max}$. Only a few slices are taken as $I_{max}$, due to not finding an appropriate interval. When it was set to more than 10 min, a minor impact on the experimental results was observed, as all the data tables in the entire $I_{max}$ tend to be considered as one module. At this point, as long as the columns are similar, they will be judged as being joinable.

3.4.3. Time Slice Statistics Experiment

Next, the proportion of time slices of different lengths was calculated based on the obtained slice information, as shown in Figure 9.

The area marked by two green lines represents the time slices from 8 to 44 s, which occupy 82.7% of the total time slices. The most common slice time was concentrated around 22 s, representing the time it takes for a user to perform a certain business operation. For operations that are logically complex, involve multiple tables, or have a large amount of data, more time is needed for business logic processing and database operations, which can lead to an increase in the length of the time slices.

3.4.4. Time Slice Quantity Experiment

Based on the characteristics of user behaviors, 22 s was selected as the average length of a time slice. A statistical unit was set to contain 1000 time slices, and by analyzing the user’s usage, this statistical unit can generally cover the number of logs for about one day. Nearly 120 statistical units were used for the analysis, as shown in Figure 10.

Figure 10 shows that the joinability results significantly depended on the number of time slices. Specifically, when the number of time slices was less than 30 units, it was difficult to obtain evaluation results; however, the accuracy only significantly improved when the number of time slices reaches a certain level. When reaching 80 units, the influence of the number of time slices on the results tended to become stable, as this period basically covers the common operations of users.

4. Conclusions

This paper addresses the problem of discovering joinable tables in the context of data cleaning in the manufacturing and related industries. A time-slicing LSH (DTL) method based on database logs was proposed, designed to improve the accuracy of the discovery of joinable tables. The proposed method is particularly effective in small and medium-sized manufacturing enterprises, in which it accurately identifies joinable tables and reduces the time spent finding joinable tables, thereby providing a more efficient strategy for data cleaning.

The improvement in accuracy achieved using this method is primarily due to the integration of dynamic and static similarity measures. Traditional approaches typically rely solely on static similarity, which overlooks the temporal dynamics of data. In contrast, our method captures the business modularity between data tables by extracting effective time slices from database logs. This approach represents the relationships between tables using a co-occurrence matrix, while also calculating dynamic similarity within each slice. By incorporating the co-occurrence matrix as a parameter and combining both dynamic and static similarity to determine joinability, our method facilitates a more effective discovery of joinable tables. The experimental results demonstrate that when dealing with complex ERP tables, our method improves the accuracy of identifying joinable tables by almost 7% compared to traditional frameworks, particularly in small and medium-sized enterprises in which table structures are complex and design practices are inconsistent.

However, this study has certain limitations. The proposed method relies heavily on the availability of a sufficient number of time slices. While all databases generate log files, these logs are often periodically purged due to their large size in practical applications, which may result in an insufficient number of time slices, thereby affecting the accuracy of the results. Future research could consider exploring the use of the time attribute in static data in conjunction with contextual information from other attributes to mine time shards with business modularity, further improving the efficiency and convenience of discoverable joinable tables.

Overall, this research provides a new idea and method for the discovery of joinable tables. The method significantly improves the accuracy of the results and reduces the time spent in finding joinable tables by combining business modularity and similarity as joinability, which has significance for improving the efficiency of data cleaning.