A method for automatically identifying similarity between different table data of relational database tables

Abstract

The application discloses a scheme for automatically discovering the similarity between different table data of a relational database based on a Bloom filter algorithm, reads all table information from the relational database, forms a table information dictionary and stores the table information dictionary; a filter is established based on a unique value set of N by using the Bloom filter algorithm; the established Bloom filter is transmitted to a unique value set of M, the value is filtered, and a return result set {R1, R2, R3, … Rn: R epsilon (True, False)} is counted; a probability W is counted: ∑R{true} / (R{true|false}); when W>ɑ (ɑ is a given threshold value), it is determined that M and N are similar, otherwise, M and N are not similar. The application can analyze all tables and all fields, and judge the data similarity between tables without relying on any artificial rules, artificial judgment or predefined instructions, and can be used for main data discovery, data reference relationship generation and data table relationship construction in a data management process, so that the traditional data management process is changed from manual to intelligent, and the management accuracy and management efficiency are effectively improved.

Description

A method for automatically identifying the similarity between data in different tables of a relational database. Technical Field

[0001] This invention belongs to the field of data governance technology and relates to a method for automatically identifying the similarity between data in different tables of a relational database. Background Technology

[0002] In the process of data governance, including data warehouse construction, master data, metadata, reference data, and business intelligence, it is often necessary to analyze the similarity between data. This includes determining master data based on data similarity density, determining table relationships based on data similarity and cardinality, and establishing data reference relationships based on data similarity. This approach abandons the traditional, manual methods for convenient data governance. Traditional technical solutions primarily rely on field naming conventions, underlying table primary / foreign key relationships, and manual identification to determine data similarity. However, in the absence of corresponding conventions, unidentified primary / foreign keys in underlying tables, and a lack of understanding of data structures by personnel, it is difficult to automatically establish data relationships and discover master and reference data. This results in a large workload, poor governance effectiveness, and difficulty in sustaining governance efforts.

[0003] In data governance, it is often necessary to establish relationships between table data and identify reference data or master data. Traditional methods generally rely on manual identification, which is inefficient and inaccurate. There is an urgent need for a means to achieve automatic identification and accomplish the above objectives. Summary of the Invention

[0004] The purpose of this invention is to provide a method for automatically identifying the similarity between data in different tables of a relational database, which solves the problem that it is difficult to automatically identify the similarity between data in different tables of a relational database when there is a lack of corresponding standards, the underlying tables do not identify primary / foreign keys, and personnel are not clear about the data structure.

[0005] The technical solution adopted in this invention is a method for automatically identifying the similarity between data in different tables of a relational database, comprising the following steps:

[0006] Step 1: Extract table information, column information, and column value information from the relational database and store the table information dictionary T, column information dictionary C, and unique value set V;

[0007] Step 2: Determine the Bloom filter column and filter column based on the column value count. First, select a set of unique values ​​for one column of the table from the table information dictionary. Then, determine the filter column N with the smaller count and the filter column M with the larger count from the unique value sets of columns of other tables.

[0008] Step 3: Use the Bloom filter algorithm to build a filter based on the unique set of N values;

[0009] Step 4: Input the Bloom filter created in Step 3 into the unique value set of M, filter the values ​​and record the returned set R={R1,R2,R3,…Rn:R∈(True,False)};

[0010] Step 5: Calculate the probability W = ∑R{true} / (R{true|false}). If W > α, where α is a given threshold, then M and N are considered similar; otherwise, they are not similar.

[0011] The invention is further characterized by:

[0012] Step 1 includes the following steps:

[0013] Step 1.1: Read all table information from the relational database, form a table information dictionary, and store the table information dictionary T;

[0014] Step 1.2: Extract the column information of the table, delete columns of numeric type and non-integer type, form the column information dictionary of the table, and store the column information dictionary C;

[0015] Step 1.3: Remove duplicate values ​​from the column information to form a unique value set V, count the values, and then store the data.

[0016] Step 2 includes the following steps:

[0017] Step 2.1: For the established table information dictionary T, take any table T1, and then take a column C1 from T1, denoted as N;

[0018] Step 2.2: For the established table information dictionary T, take any table T2 that is not repeated from step 1, and then take a column C2 from T2, denoted as M;

[0019] Step 2.3: Determine the value of N based on the unique value count and the unique value count of M. The one with the smaller count is used to build a Bloom filter, and the one with the larger count is used to apply the filter.

[0020] Step 3 includes the following steps:

[0021] Step 3.1: Create an m-bit BitSet, and initialize all bits to 0.

[0022] Step 3.2: Determine the number of hash functions and select k different hash functions;

[0023] Step 3.3: Select a hash function. The i-th hash function hashes each value in the column value set, and the result is denoted as h(i, value), where h(i, value) ranges from 0 to m-1.

[0024] Step 4 includes the following steps:

[0025] Step 4.1: Input the set of unique values ​​to be filtered, and filter the values ​​using the created Bloom filter.

[0026] Step 4.2: Count the K hash function return values ​​of the input value. If all return values ​​are 1, record it as True; otherwise, record it as False.

[0027] Step 4.3: Record all results as a set {R, R∈(True,False)}.

[0028] It also includes step 6, repeating steps 2 to 5 to complete the similarity determination of all columns in all tables, and finally generating the column similarity calculation between all tables.

[0029] The beneficial effects of this invention are:

[0030] 1. This invention achieves intelligent table data similarity analysis without relying on any human rules, manual judgments, or predefined descriptions.

[0031] 2. This invention can achieve intelligent identification of master data by judging the number of similarities in the association table.

[0032] 3. This invention can identify relationships between tables by combining the similarity data of the tables with the direction of the unique value characteristics of the data.

[0033] 4. This invention can identify reference data in table columns by judging the similarity of data associated with the table and the degree of uniqueness of the data. Attached Figure Description

[0034] Figure 1 is a flowchart of a method for automatically identifying the similarity between data in different tables of a relational database according to the present invention. Detailed Implementation

[0035] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments.

[0036] In data governance, it is often necessary to establish relationships between table data and identify reference data or master data. Traditional methods generally rely on manual identification, which is inefficient and inaccurate. There is an urgent need for a means to achieve automatic identification. To achieve the above objectives, this invention proposes a method for automatically identifying data similarity, which can meet business needs by identifying data similarity.

[0037] The flowchart of a method for automatically identifying the similarity between data in different tables of a relational database is shown in Figure 1.

[0038] Step 1: It is necessary to periodically extract table information, column information, and column value information from the relational database through collection methods, and perform deduplication and non-integer value removal. Then, count the unique values ​​after processing and store the above processed data to form a table information dictionary T, a column information dictionary C, a unique value set V data, and a statistical count V.

[0039] The table information dictionary includes T(table ID, table name, table description), C(column ID, table ID, column name, column type, column description), and the unique value set V(column ID, value). The items in the above information dictionary can be added or replaced with other items as needed.

[0040] After the dictionary and sets are created, they are stored in the storage medium. Based on the above operations, the insertion column and filter column are determined, i.e., step 2 is performed according to the following steps:

[0041] Step 2.1: Take any table T1 from the table information dictionary T, and take any column C1 and the set of unique values ​​V1 corresponding to the column from the column information dictionary C of the table;

[0042] Step 2.2: Take any table T2 from the table information dictionary T, excluding the tables that have already been taken, and take any column and the set of unique values ​​V2 corresponding to the column from the column information dictionary C2 of the table;

[0043] Step 2.3: Calculate the size of the set of index column values ​​in Step 2.1 and Step 2.2. The column with the smaller count is determined as the insertion column, and the column with the larger count is used as the filtering column.

[0044] Based on the above judgments, define the insertion column count as the number of inserted elements, n; set the false positive rate p to 0.01, and proceed to step 3: start building the Bloom filter, including the following steps:

[0045] Step 3.1: Calculate the filter length m. p is the false positive rate;

[0046] Step 3.2: Calculate the number of hash functions, k. ;

[0047] Step 3.3: Create an m-bit BitSet, first initializing all bits to 0;

[0048] Step 3.4: Determine the number of hash functions; here, we choose k different hash functions.

[0049] Step 3.5: Select a hash function. The i-th hash function hashes each value in the inserted element, and the result is denoted as h(i, value), where h(i, value) ranges from 0 to m-1.

[0050] The Bloom filter is now complete after completing the above steps. Next, we can use the completed Bloom filter to filter the data and obtain the decision set. Step 4 will be implemented according to the following steps:

[0051] Step 4.1: Input the set of unique values ​​to be filtered, and filter the values ​​using the created Bloom filter.

[0052] Step 4.2: Count the K hash function return values ​​of the input value. If all return values ​​are 1, record it as True; otherwise, record it as False.

[0053] Step 4.3: Record all results as a set {R, R∈(True,False)}.

[0054] Step 5: Now we have the data similarity judgment set R. After obtaining the set R, we need to calculate the judgment probability and judge the similarity based on the calculated probability. The statistical probability W = ∑R{true} / (R{true|false}). When W>ɑ (ɑ is a given threshold, ɑ=1-p, p is the false positive rate), the inserted data and the filtered data are judged to be similar; otherwise, they are not similar.

[0055] Step 6: Finally, repeat the process of determining the insertion columns and filtering columns to calculate the similarity probability, completing the similarity determination of all columns in all tables, and finally generating the similarity calculation of column relationships between all table data. Example 1

[0056] The first step is to connect to the database, analyze the database metadata, extract table information from it, and form a table information dictionary.

[0057]

[0058] After extracting the table information, the column metadata information of the table is extracted from the database metadata based on the table information to form a column information dictionary.

[0059] 1. Order Dictionary

[0060]

[0061] 2. Customer Dictionary

[0062]

[0063] After extracting column information, the unique values ​​of the table's column data are retrieved from the database metadata based on the column information, and a column value dictionary is formed.

[0064] 1. Dictionary of Customer ID column values ​​in the order table

[0065]

[0066] 2. Dictionary of Customer ID column values ​​in the Customer table

[0067]

[0068] The second step, after completing the above data dictionary creation, is to select the order customer ID column from the order dictionary table and the ID column from the customer dictionary table.

[0069] First, analyze the column values ​​to determine their counts. Column values ​​with smaller counts are used to create filters, while those with larger counts are used for filtering.

[0070] Secondly, the false positive rate p is set to 0.01, indicating that a 1% false positive rate is allowed; the filter length m is calculated, where... ; Calculate the number of hash functions k, where Create an m-bit BitSet, first initializing all bits to 0; determine the number of hash functions, here we choose K different hash functions;

[0071] Finally, a filter is established based on the above definition, where a hash function is selected. The i-th hash function hashes each value in the inserted element, and the result is denoted as h(i, value). The range of h(i, value) is from 0 to m-1. A Bloom filter is established through the above selection.

[0072] In this case, the ID column value from the customer dictionary table is selected to create the filter, and the order customer ID column value from the order dictionary table is used to apply the filter.

[0073] The third step involves passing the order customer ID column values ​​from the order dictionary table to a pre-built Bloom filter for filtering. The filter counts the K hash function returns from the input values; if all returns are 1, the record is True; otherwise, it's False. All results are then evaluated, and the inserted and filtered data are determined to be similar within a given threshold range; otherwise, they are considered dissimilar. In this example, the ID column values ​​from the customer dictionary table and the order customer ID column values ​​from the order dictionary table are determined to be similar.

[0074] The fourth step is to repeat steps two and three, taking different tables and columns, and using value combinations to complete the similarity determination of all columns in all tables, ultimately generating the column similarity calculation between all table data.

[0075] The flowchart of the method for automatically identifying the similarity between data in different tables of a relational database according to the present invention has the following advantages:

[0076] This invention automatically extracts table information, column information, column value sets, and column value counts from a relational database. Then, it filters other column value data using a Bloom filter based on the column values. By statistically analyzing the probability of the returned values, it achieves data similarity determination. This invention does not rely on any manual rules, human judgment, or predefined specifications, and realizes intelligent similarity analysis between large-scale data tables. It can be used for reference data analysis, master data discovery, and relationship discovery between data tables.

Claims

1. A method for automatically identifying the similarity between data in different tables of a relational database, characterized in that, Includes the following steps: Step 1: Extract table information, column information, and column value information from the relational database and store the table information dictionary T, column information dictionary C, and unique value set V; Step 2: Determine the Bloom filter column and filter column based on the column value count. First, select a unique value set from the table information dictionary, and then determine the filter column N with the smaller count and the filter column M with the larger count from the unique value sets of columns in other tables; Step 3: Use the Bloom filter algorithm to establish the filter based on the unique value set of N; Step 4: Input the Bloom filter established in Step 3 into the unique value set of M, filter the values, and record the returned set R = {R1, R2, R3, ..., Rn: R ∈ (True, False)}; Step 5: Calculate the probability W = ∑R{true} / (R{true|false}). If W > α, where α is a given threshold, then M and N are considered similar; otherwise, they are not similar. Step 3 includes the following steps: Step 3.1: Create an m-bit BitSet, first initializing all bits to 0; where, Where p is the false positive rate and n is the number of elements inserted; Step 3.2: Determine the number of hash functions and select k different hash functions; among them, Step 3.3: Select a hash function. The i-th hash function hashes each value in the column value set, and the result is denoted as h(i, value), where h(i, value) ranges from 0 to m-1.

2. The method for automatically identifying the similarity between data in different tables of a relational database according to claim 1, characterized in that, Step 1 includes the following steps: Step 1.1: Read all table information from the relational database, form a table information dictionary, and store the table information dictionary T; Step 1.2: Extract the column information of the table, delete columns of numeric type and non-integer type, form a column information dictionary of the table, and store the column information dictionary C; Step 1.3: Remove duplicate column value information, form a unique value set V, count the values, and then store it.

3. The method for automatically identifying the similarity between data in different tables of a relational database according to claim 2, characterized in that, Step 2 includes the following steps: Step 2.1: For the established table information dictionary T, take any table T1, and then take a column C1 of T1, denoted as N; Step 2.2: For the established table information dictionary T, take any table T2 that does not repeat from step 1, and then take a column C2 of T2, denoted as M; Step 2.3: Determine the unique value count based on the unique value count of N and M, and use the table with the smaller count to establish a Bloom filter, and use the table with the larger count to apply the filter.

4. The method for automatically identifying the similarity between data in different tables of a relational database according to claim 3, characterized in that, Step 4 includes the following steps: Step 4.1: Input the set of unique values ​​to be filtered and filter the values ​​using the established Bloom filter. Step 4.2: Count the K hash function return values ​​of the input values. If all return values ​​are 1, record them as True; otherwise, record them as False. Step 4.3: Record all results as a set {R, R∈(True,False)}.

5. The method for automatically identifying the similarity between data in different tables of a relational database according to claim 4, characterized in that, It also includes step 6, repeating steps 2 to 5 to complete the similarity determination of all columns in all tables, and finally generating the column similarity calculation between all tables.

Images