A non-equal joinable data table direct query method based on LSH

Abstract

The application provides a non-equal-value connectable data table direct query method based on LSH, and the method is based on the LSH method commonly used in a high-dimensional data approximate matching method, indexes of column dimensions are constructed in a candidate data table according to a similarity threshold, hash values of query columns in a query data table are generated by using the same hash function, and the connectable data table satisfying the similarity threshold is searched in the indexes of the created candidate data table; the method only needs to provide the data table needing rich data, namely the query data table, the candidate data set and the similarity threshold, and the connectable data table satisfying the similarity threshold can be quickly found in the candidate data set, and the query data is enriched; the algorithm does not have redundant pretreatment, does not need additional calculation steps, and is simple in design and easy to realize, and the query efficiency of the non-equal-value connectable data table query algorithm is improved.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of database and big data mining, and particularly relates to a non-equality joinable data table direct query method based on LSH. BACKGROUND

[0002] Table join is a basic operation to join two or more tables, which is an important technique applied in relational database management systems and business intelligence tools for data analysis. The benefits of joining tables are not only reflected in the database field (such as data integration), and data analysis tools such as Excel's Power Query, Informatica, but also in the field of machine learning (ML), such as feature enhancement and data enrichment. With the trend of open data movement and the spread of industry data solutions, a large number of valuable databases are published on the Internet, and governments around the world are developing data portals, and their data formats are highly heterogeneous, including a mixture of relational (CSV and spreadsheets), semi-structured (JSON and XML), graph-based (RDF) and geospatial formats. We have more opportunities to obtain more structured data from data lakes and websites (such as WDC WebTable Corpus), and use these structured data to enrich our local data, so that a local data table can be regarded as a query, and our request is to find joinable tables in a large number of structured tables to expand our data to mine the data value in a large number of structured data tables. A classic method is a character transformation and statistics-based similarity join method, and Erkang Zhu, Yeye He, etc. proposed an automatic join algorithm to realize character transformation-based automatic connection, which uses substring index to effectively identify joinable row pairs, and automatically connects after string transformation from these row pairs to complete non-equality joinable table automatic connection. Since some data have the feature that joinable columns have overlapping strings, Surajit Chaudhuri, etc. proposed fuzzy join, but the disadvantage of fuzzy join is that many parameters need to be set, such as distance function, threshold, etc., and these parameters need to be properly configured to work normally. The ideal parameter configuration will be different for different actual data, and it is difficult for users to determine. Since the above algorithms are not so efficient and too complex, and the user sets many parameters, the practicability is low, and it is not suitable for finding non-equality joinable data tables in large-scale data sets.

[0003] The data characteristics of non-equivalent joinable data tables are that the joinable columns have overlapping strings, and Surajit Chaudhuri et al. proposed fuzzy join. The data cleaning based on the fuzzy join of the similarity involves the identification of "tight" tuples, in which the tightness is evaluated by using various similarity measure functions, and the selection of the similarity measure functions is based on the field and application of the data. The current effective method for implementing such a similarity join is closely related to the selected similarity measure function. In this method, a new primitive operator is proposed, which can be used as a basis for implementing similarity join based on various popular string similarity functions, and a similarity concept beyond the text similarity is proposed, and then an effective implementation of the operator is proposed. In the experimental evaluation using real data sets, the similarity join implemented by using the operator is equivalent to the previous customized implementation for a specific similarity function, and is generally much better than the previous customized implementation.

[0004] The main defects of the fuzzy join based on the similarity proposed by Surajit Chaudhuri et al. are as follows: (1) a large number of parameters need to be set, such as distance function and threshold; (2) the ideal parameter configuration is different for different actual data, and it is difficult for users to determine; (3) the algorithm is too complex, the user sets a large number of parameters, the practicability is low, and it is not suitable for finding non-equivalent joinable data tables in large-scale data sets. SUMMARY

[0005] Therefore, the purpose of the present application is to provide a direct query method for non-equivalent joinable data tables based on LSH, which can improve the query efficiency.

[0006] A direct query method for non-equivalent joinable data tables based on LSH, comprising the following steps:

[0007] Step 1, a candidate data table set, a data table to be queried, and a set similarity threshold are given;

[0008] Step 2, the data in all cells in each column in the candidate data table is processed by n-gram, and connected into a set;

[0009] Step 3, for each set obtained in step 2, a hash function is used to calculate it according to the similarity threshold θ, and a plurality of groups of hash values are obtained for each set, the number of groups is set as b, and the number of hash values contained in each group is set as r; all hash values in each group are connected into a value, and then b final hash values are obtained for each set; then an array is formed by the hash values corresponding to the set and the data table number to which the set belongs, and all arrays corresponding to the sets are stored in a dictionary structure according to the hash values;

[0010] Step 4, for the query column in the query data table, the corresponding set is obtained according to the method of step 2;

[0011] Then, according to the method of step 3, the query column of the query data table is calculated by using the hash function according to the similarity threshold θ, and b groups of hash values are obtained, the hash values in the same group are connected to form a value, which is a final hash value corresponding to the set, and the set can finally obtain b hash values; for each hash value, the equal hash value is found in the array stored in the dictionary structure, and the data table corresponding to the found hash value is the connectable table.

[0012] Preferably, in step 3, the number of groups b and the number of groups r of the generated hash values are calculated according to the formula in LSH:

[0013] P = 1-(1-θ r ) b

[0014] Wherein, P = 1, and θ is the set similarity threshold.

[0015] Preferably, the value of n in the n-gram processing is 3.

[0016] Preferably, the hash value is a key value.

[0017] Preferably, for each column in the candidate data table and the set corresponding to the query column of the query data table, the same hash function is used to calculate the hash value.

[0018] Preferably, the LSH-based non-connectable data table direct query method is used for manufacturing industry production and sales data query.

[0019] Preferably, the LSH-based non-connectable data table direct query method is used for Changhong manufacturing industry big data query.

[0020] Preferably, the similarity threshold value ranges from 0 to 1.

[0021] Preferably, the similarity threshold value ranges from 0.5 to 0.8.

[0022] The present application has the following advantages:

[0023] The application provides a direct query method for non-equal connectable data tables based on LSH. The method is based on the LSH method commonly used in high-dimensional data approximate matching methods, and indexes the column dimension of a candidate data table according to a similarity threshold. Hash values of query columns in a query data table are generated by using the same hash function, and the connectable data table satisfying the similarity threshold is searched in the index of the created candidate data table. The method only needs to provide the data table with rich data, i.e. the query data table, the candidate data set and the similarity threshold, and can quickly find the connectable data table satisfying the similarity threshold in the candidate data set, thereby enriching the query data. The algorithm does not have redundant preprocessing, does not need additional calculation steps, and is simple in design and easy to implement, thereby improving the query efficiency of the non-equal connectable data table query algorithm. BRIEF DESCRIPTION OF DRAWINGS

[0024] Figure 1 The method flowchart of the application is shown. DETAILED DESCRIPTION

[0025] The application will be described in detail below with reference to the drawings and examples.

[0026] A direct query method for non-equal connectable data tables based on LSH of the application is shown in Fig. 1, and specifically includes the following steps. Figure 1

[0027] Step 1: Given a candidate data table set T, a query column Q in a query data table and a similarity threshold θ.

[0028] Step 2: Preprocessing of the candidate data table, specifically:

[0029] The data in all cells in each column in the candidate data table is subjected to n-gram processing and connected into a set. In this example, n is 3. Table 1 shows a column in the candidate data table.

[0030] Table 1: A column in the candidate data table

[0031]

[0032] After the n-gram processing of the data in Table 1, the following set is obtained:

[0033] {oba, bam, ama, bus, ush, cli…}

[0034] Step 3: One-step LSH (Locality Sensitive Hashing), i.e. locality sensitive hashing technology, is used to construct the index of the set dimension (which can be realized offline), and a data structure dictionary storage is established, specifically:

[0035] ​For each set obtained in step 2, a hash function is used to calculate according to the similarity threshold θ, and a corresponding number of hash values is obtained for each set; wherein the determination of the number of generated hash values is based on the formula in LSH based on the minimum independent permutation:

[0036] P = 1 - (1 - θ r ) b

[0037] The calculation method is to set P = 1, θ is the set similarity threshold, to calculate b, r, then the number of hash values needed to be generated is b*r, that is, the hash values generated by each set are divided into b groups, and each group has r hash values. The similarity threshold value ranges from 0 to 1, and the selection of the threshold depends on what kind of connectable data is wanted to be found in the candidate data set, and is generally set to 0.5-0.8. In actual application, different data can be queried by flexibly adjusting the similarity threshold to meet the needs of different application scenarios.

[0038] Connect the same group of hash values corresponding to each set to form a value as the final hash value corresponding to the set, then each set can finally obtain b hash values, and the b hash values all correspond to the set and point to the corresponding data table. Then the hash value corresponding to the set and the data table number are constructed into an array, and all the arrays corresponding to the sets are stored in a dictionary structure according to the hash value, wherein the hash value is the key value and the data table number is the value corresponding to the key value in the dictionary.

[0039] In this embodiment, the dictionary structure established is as follows:

[0040] {oba:[table1],ame:[table2]…}

[0041] Wherein, oba is the hash value, and [table1] represents the data table number.

[0042] Step 4, for the query column in the data table to be queried, the set is obtained according to the method of step 2;

[0043] In this embodiment, table 2 is the query column:

[0044] Table 2 is the query column of the data table to be queried

[0045]

[0046] Connect all cells in the query column to form a set after n-gram, in this embodiment, n is 3, and the following is obtained:

[0047] {oba,bam,ama,ma#,a#1,#19,196,961,61#,bus,ush…}

[0048] Then, according to the method of step 3, the query column of the query data table is calculated by using the hash function according to the similarity threshold θ, b groups of hash values are also obtained, the same group of hash values are connected into a value as a final hash value corresponding to the set, and the set can finally obtain b hash values; for each hash value, the equal hash value is found in the array stored in the dictionary structure, and then the data table corresponding to the found hash value is the connectable table.

[0049] The data selected in the embodiment is a foreign WDC Web Table Corpus public data set, and can also be used for manufacturing production and sales data, and the effect of the longrich manufacturing industry big data test is remarkable.

[0050] To sum up, the above is only a preferred embodiment of the present application, and is not used to limit the protection scope of the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principles of the present application shall be included in the protection scope of the present application.

Claims

1. A method for direct query on non-equi joinable data tables based on LSH, characterized in that, It comprises the following steps: Step 1, given a candidate data table set, a data table to be queried and a set similarity threshold; Step 2, all cells in each column in the candidate data table are subjected to n-gram processing to be connected into a set; Step 3, for each set obtained in step 2, a hash function is used to calculate it according to the similarity threshold θ, and each set corresponds to a plurality of groups of hash values, the number of groups is set to b, and the number of hash values contained in each group is set to r; all hash values in each group are connected into a value, and each set corresponds to b final hash values; then the hash values corresponding to the set and the data table number are constructed into an array, and all arrays corresponding to the set are stored in a dictionary structure according to the hash values; Step 4, for the query column in the data table to be queried, the corresponding set is obtained according to the method of step 2; Then according to the method of step 3, the query column of the query data table is calculated by using a hash function according to the similarity threshold θ, and b groups of hash values are also obtained, and the hash values in the same group are connected into a value as a final hash value corresponding to the set, so that the set can finally obtain b hash values; For each hash value, find the equal hash value in the array stored in the dictionary structure, and the data table corresponding to the found hash value is the connectable table.

2. The method of claim 1, wherein the method is based on LSH. In step 3, the number of groups b and the number r of each group in which the hash values are generated are calculated according to the formula in LSH: P = 1 - (1 - θ r ) b Wherein, P=1, θ is the set similarity threshold.

3. The method of claim 1, wherein the method is based on LSH. The value of n in the n-gram processing is 3.

4. The method of claim 1, wherein the method is based on LSH. The hash value is a key value.

5. The method of claim 1, wherein the method is based on LSH. For each set corresponding to each column in the candidate data table and the set corresponding to the query column of the data table to be queried, the same hash function is used to calculate the hash value.

6. The method of claim 1, wherein the method is a LSH-based direct query method for non-equivalence joinable data tables, characterized in that, The LSH-based non-connectable data table direct query method is used for manufacturing production and sales data query.

7. The method of claim 6, wherein the method is based on LSH. The LSH-based non-connectable data table direct query method is used for Changhong manufacturing industry big data query.

8. The method of claim 1, wherein the method is a LSH-based direct query method for non-equivalence joinable data tables, characterized in that, The similarity threshold value ranges from 0 to 1.

9. The LSH-based direct query method for non-equi joinable data tables according to claim 8, wherein, The similarity threshold value ranges from 0.5 to 0.8.

Images