Data table composite primary key discovery method based on machine learning

Abstract

The invention discloses a data table composite primary key discovery method based on machine learning, which comprises the following steps: 1) for a sample set, firstly identifying a list of minimum UCC of each sample; wherein each sample is a data table, a combination column which does not contain repeated content and can be used for indexing is called as a unique combination column UCC, and a UCC which does not contain other UCCs is defined as a minimum unique combination column, namely a minimum UCC; 2) performing feature extraction combination on each minimum UCC of the sample to obtain a feature vector of each minimum UCC of the sample; 3) training a classifier by using the feature vector of each sample; and 4) for a to-be-processed data table A, obtaining the feature vector corresponding to each minimum UCC of the data table A, inputting the feature vector into the random forest classifier, obtaining the minimum UCC of the data table A for generating the composite primary key, and generating the composite primary key of the data table A according to the minimum UCC. According to the method, the composite primary key can be identified only by internal information of the data table.

Description

Technical Field

[0001] The present invention belongs to the field of machine learning, and relates to a method based on feature construction and random forest classification, and in particular to a method for discovering composite primary keys of a data table based on machine learning. Background Art

[0002] As the scale of mobile Internet data increases, the scale of data in the database also increases. Identifying the composite primary key of each data table is not only the key to understanding the structure of a single table, but also provides a basis for revealing the potential connections and relationships between different tables. Therefore, in the absence of external information, it is necessary to rely solely on the structure and content of the data table itself to discover the table primary key.

[0003] Most of the existing methods for discovering primary keys of data tables only focus on identifying single-column primary keys, but are unable to handle the common composite primary key situations in complex data tables. The few methods that take composite primary keys into consideration will bring exponential overhead and have poor practical value. The methods that use key features to predict fail to fully tap the potential of each feature in prediction and ignore the possible interactions and complex relationships between features. Summary of the invention

[0004] In view of the problems existing in the prior art, the purpose of the present invention is to provide a data table composite primary key discovery method based on machine learning. In the process of data dissemination and maintenance, some key metadata information may be lost, and the primary key is crucial to the data table. The present invention realizes the identification of the composite primary key only by the internal information of the data table without the support of external information.

[0005] The present invention first identifies the combinations of columns that can play the role of indexes together, that is, these combinations are regarded as candidates for composite primary keys because of their potential effectiveness in data organization and retrieval. Then, the feature vectors of the candidates are extracted and necessary processing is performed to meet the needs of subsequent machine learning classifiers. Finally, a classifier is trained based on the feature vectors using a random forest algorithm. Once the classifier is trained in a data set, it can be widely used in other data tables to automatically discover composite primary keys, thereby realizing the identification of composite primary keys by relying on the intrinsic information of the data table without the support of external information.

[0006] The present invention constructs a minimum unique column combination discovery algorithm, which screens out column combinations that may have composite primary key features from the exponential column combinations in the input table; and constructs a feature extraction and feature processing method for composite primary keys. The present invention ultimately realizes a composite primary key discovery method based on machine learning, which can discover the composite primary key of a data table by relying on a machine learning method without external information input.

[0007] To achieve the above objectives, in a first aspect of the present invention, a candidate column combination discovery module is designed and implemented, which is intended to identify those combinations of columns that are exponential in number in a data table and can work together to perform indexing functions. These combinations are considered candidates for composite primary keys because of their potential effectiveness in data organization and retrieval.

[0008] If a set of columns in a data table can work together to act as an index, then the column combination can be considered as a potential candidate for a composite primary key. We call those combination columns that do not contain duplicate content and can be used for indexing unique combination columns (UCCs), and the UCC that does not contain other UCCs is defined as the minimum unique combination column. However, given the performance overhead that may be caused by considering all UCCs, which reduces practicality, in order to balance speed and accuracy, we abstract the problem into a problem of finding the minimum unique combination column. This module develops a candidate column combination discovery algorithm, whose input is a tabular data set and output is a list of minimum unique combination columns. In most cases, when considering a combination of 4 to 5 columns, it is almost guaranteed that at least one set of unique combination values ​​will appear, so the algorithm is effective in practical applications.

[0009] In the second aspect of the present invention, a feature extraction / processing module is designed and implemented, which is responsible for extracting the features of the minimum unique combination column output by the candidate column combination discovery module and performing necessary data balancing processing to meet the needs of the subsequent machine learning classifier. The module first includes a feature extraction algorithm, which extracts the following features for each column of each minimum UCC:

[0010] 1) Position information. The position of the column in the table, expressed as a percentage (i.e. index / total number of columns). This is because in actual scenarios, the key columns are related to their position in the table.

[0011] 2) Name. Whether the field name contains keywords such as "key" or "id". This is because these keywords usually indicate that the corresponding column is the primary key.

[0012] 3) Data type. Not only is the primary key often an integer or string, but also because floating point data is often unique and has a high probability of appearing in UCC, which can easily cause interference.

[0013] 4) Text length. The maximum length of the value in this column. Primary keys are often shorter.

[0014] Finally, for each UCC, its feature is the average of its column features, plus an additional feature: the UCC size.

[0015] 5) UCC size. The number of columns in the UCC. The composite primary key column should not contain too many columns.

[0016] For a data table, even if the column combinations that may have the key column combination features are screened out after screening, there may be many such combinations, and the real key columns of a table are often few. Therefore, most of the actual data sets have data imbalance problems. This imbalance will cause the machine learning model to be biased towards the majority class, thereby reducing the recognition ability of minority class samples. The present invention first performs Smote oversampling on the extracted UCC features (including the average value of location information features, the average value of name features, the average value of data type features, the average value of text length features and the number of columns), and uses the Smote algorithm to oversample the minority class samples to increase the number of minority class samples to solve the data imbalance problem. Next, TomekLinks cleaning is performed by identifying Tomek Links in the newly generated samples; a Tomek Link consists of a pair of samples that meet the following conditions: 1. They belong to different categories; 2. In the entire data set, each sample is the nearest neighbor of another sample in the other category. That is, given a pair of samples x i , x j , belong to different categories y i ,y j (y i ≠y j ), if for any third-party sample x k , all meet the conditions or d(x j , x k )>d(x i , x j ) where d(x i , x j ) represents the sample x i and x j This indicates that there are no other samples than x i and x j The distance between them is closer; these TomekLinks may be introduced due to oversampling or exist in the original dataset. Then, remove the samples belonging to the majority class in these Tomek Links. The purpose of this step is to reduce the overlap between categories and improve the clarity of classification boundaries. Through the above two steps, not only the minority class samples are increased and the balance of the dataset is improved, but also the boundaries between categories are optimized by removing Tomek Links, which improves the performance of the classification model.

[0017] The third aspect of the present invention is to construct a machine learning classifier, which adopts a random forest algorithm, uses an integrated learning idea based on bagging, and improves the stability and accuracy of the model by constructing multiple decision trees and performing combined prediction. In the construction process of each tree, diversity is introduced by randomly selecting samples and features, and the classification result is finally determined by a voting mechanism. The random forest classifier is trained with the feature vector obtained by the feature extraction / processing module. Once the training is completed, the classifier can be widely used in other data tables to automatically discover composite primary keys, thereby realizing the identification of composite primary keys by relying on the intrinsic information of the data table without the support of external information.

[0018] The technical solution of the present invention is:

[0019] A method for discovering composite primary keys of a data table based on machine learning, the steps of which include:

[0020] 1) For a sample set, first identify the list of minimum UCCs for each sample; where each sample is a data table, the combination column that does not contain duplicate content and can be used for indexing is called the unique combination column UCC, and the UCC that does not contain other UCCs is defined as the minimum unique combination column, that is, the minimum UCC;

[0021] 2) Perform feature extraction and combination on each minimum UCC of the sample to obtain a feature vector of each minimum UCC of the sample;

[0022] 3) Using the feature vectors of each sample to train the classifier;

[0023] 4) For a data table A to be processed, obtain a list of the minimum UCCs of the data table A, then perform feature extraction on each minimum UCC of the data table A, obtain the feature vectors corresponding to each minimum UCC of the data table A and input them into the trained random forest classifier to obtain the minimum UCC of the data table A for generating a composite primary key, and generate the composite primary key of the data table A accordingly.

[0024] Furthermore, the method for obtaining the feature vector of the sample is:

[0025] 1) For each column in each minimum UCC of the sample, extract the position feature, name feature, data type feature and text length feature of the column;

[0026] 2) taking the average of the position feature, name feature, data type feature and text length feature of each column in each minimum UCC of the sample, and obtaining the average value of the position information feature, the average value of the name feature, the average value of the data type feature and the average value of the text length feature of the sample;

[0027] 3) Combine the number of columns in the minimum UCC of the sample with the corresponding average values ​​of the position information feature, the name feature, the data type feature, and the text length feature to obtain the feature vector corresponding to the minimum UCC of the sample.

[0028] Further, a method for identifying a list of minimum UCCs for each sample is:

[0029] 11) Set k = 1, and divide all single columns in each sample into 1-NUCC or 1-UCC; for each 1-NUCC, recursively find all k-NUCCs and the minimum k-UCC; where N-UCC refers to a column combination containing at least one repeated value, k-NUCC is the N-UCC containing k columns, and the minimum k-UCC is the minimum UCC containing k columns;

[0030] 12) After obtaining a list of all minimum k-UCCs in the sample and a list of all k-NUCCs in the sample, for the current k, combine all 1-NUCCs and k-NUCCs to form a (k+1) column combination;

[0031] 13) Check whether the (k+1) column combination consisting of all 1-NUCCs and k-NUCCs already contains any other UCC classified so far; if it does not contain any other UCC, classify it as a (k+1)-NUCC or a minimum (k+1)-UCC; then add the minimum (k+1)-UCC to the output list;

[0032] 14) Update k=k+1 and repeat step 12) with the updated k value until a new minimum UCC is not found or k+1 exceeds the number of columns of the sample.

[0033] Furthermore, in step 1), the Smote hybrid Tomek-Links algorithm is first used to solve the data imbalance problem, and the number of minority class samples is increased by generating new samples between the minority class samples and their nearest neighbors.

[0034] Furthermore, the feature vector of each sample is used to train the classifier using the random forest algorithm.

[0035] Furthermore, the method of training a classifier using the random forest algorithm is: first, m training subsets are generated from the sample set by a self-service sampling method, and each training subset is obtained by sampling with replacement; then a decision tree is constructed based on each training subset, and when constructing the decision tree, a number of feature vectors are randomly selected from all the feature vectors to construct a feature subset, and then the feature that can best improve the splitting quality is found in the feature subset as the splitting feature.

[0036] Furthermore, the data cleaning technology Tomek Links is used to identify and remove overlapping samples in the sample set.

[0037] A server, characterized in that it comprises a memory and a processor, wherein the memory stores a computer program, the computer program is configured to be executed by the processor, and the computer program comprises instructions for executing each step in the above method.

[0038] A computer-readable storage medium stores a computer program, wherein the computer program implements the steps of the above method when executed by a processor.

[0039] The advantages of the present invention are as follows:

[0040] The present invention accepts a tabular data set as input, and carefully screens the possible combination columns of each data table in the existing data set to identify candidate combination columns with potential importance. Further, the features of these candidate combination columns are extracted, and these features are appropriately preprocessed to optimize their contribution to the performance of subsequent classifiers. Finally, a machine learning classifier is trained so that it can be trained on a data set supported only by basic metadata. Once the training is completed, the classifier can be widely used in other data tables to automatically discover composite primary keys, thereby realizing the identification of composite primary keys by relying on the intrinsic information of the data table without the support of external information.

[0041] In an environment where information is scarce, the present invention uses a machine learning method to identify the composite primary key in each data table in a series of seemingly disorganized data tables; it has strong practicality and application scope in the field of data processing and has a very broad application prospect. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Figure 1 A flow chart of a composite primary key discovery method based on machine learning provided by the present invention.

[0043] Figure 2 Execute the flowchart for the minimum unique column combination discovery algorithm.

[0044] Figure 3 This is a schematic diagram of the Smote algorithm;

[0045] (a) Calculate X i K nearest neighbor samples, (b) synthesizing new samples.

[0046] Figure 4 Flowchart of using machine learning to model classification for the present invention. DETAILED DESCRIPTION

[0047] The present invention is further described in detail below in conjunction with the accompanying drawings. The examples given are only used to explain the present invention but not to limit the scope of the present invention.

[0048] Figure 1 This is the specific process of a composite primary key discovery method based on machine learning proposed by the present invention.

[0049] Step S101 is the minimum unique column combination discovery module, which selects the column combination that may have the characteristics of a composite primary key from the exponential column combinations in the input table. If a group of columns (i.e., UCC) in a data table can jointly act as an index, then the column combination can be identified as a potential candidate for a key combination column. This step uses the minimum unique column combination as a candidate for a composite primary key, with the input being a table data set and the output being a list of minimum unique column combinations.

[0050] Step S102 is a feature extraction / processing module, which extracts location information, name, data type, text length and UCC size to construct a feature vector, and uses the Smote mixed Tomek-Links algorithm to solve the data imbalance problem, by generating new samples between minority class samples and their nearest neighbors to increase the number of minority class samples, and by identifying and processing those samples that may cause the classification decision boundary to be blurred, so as to make the boundaries between categories clearer. First, Smote oversampling is performed, and the Smote algorithm is used to oversample the minority class samples to increase the number of minority class samples to solve the data imbalance problem. Next, Tomek Links cleaning is performed, by identifying Tomek Links in the newly generated data set, these Tomek Links may be introduced due to oversampling or exist in the original data set. Then, the samples belonging to the majority class in these Tomek Links are removed. The purpose of this step is to reduce the overlap between categories and improve the clarity of the classification boundaries. Through the above two steps, not only the minority class samples are increased, the balance of the data set is improved, but also by removing Tomek Links, the boundaries between categories are optimized, and the performance of the classification model is improved.

[0051] Step S103 is a machine learning module, which uses a random forest algorithm to train a classifier. Through the self-service sampling method, m new training subsets are generated from the original training data set, and each subset is obtained by sampling with replacement, which ensures the diversity of the samples. When constructing each decision tree, instead of looking for the optimal splitting feature among all available features, a feature subset is first randomly selected from all features, and then only the feature that can best improve the splitting quality is found in this subset. Through the above-mentioned double random sampling mechanism, random forest realizes a comprehensive exploration of training data and feature space, effectively avoiding the overfitting problem that is prone to occur in a single decision tree. The core idea of ​​ensemble learning - improving overall performance by integrating the prediction results of multiple models, is fully reflected in random forests. The splitting quality of the feature is determined according to the information gain of the feature, where the information gain of feature F is the amount of information uncertainty reduction before and after using feature F to split the data set D, and the calculation formula is: IG(D, F) = H(D)-H(D|F), where H(D) is the entropy of the data set D, and H(D|F) is the conditional entropy of the data set D after the given feature F.

[0052] Figure 2 This is the execution flow chart of the algorithm for finding the minimum unique column combination. First, some basic definitions are given: R and S represent data tables, which are two empty data tables, specifying the number of columns and column names; r and s are instances of R and S, that is, actual data is added to R and S, |R| represents the number of columns in R, |r| represents the number of tuples in r (the number of rows in r, each row of data is a tuple), and r i represents the i-th tuple in r, s j represents the jth tuple in s. Individual columns are represented by capital letters A, B, and C. Subsets of columns are represented by X, Y, and Z. |X| represents the number of columns in X. i [X] is defined as r i The projection on subset X, that is, the i-th row data of all columns contained in subset X is r i [X]. Then UCC can be defined as a column set X, And satisfy:

[0053]

[0054] Corresponding to UCC, N-UCC refers to a column combination that contains at least one repeated value. N-UCC can be defined as a column set X, And satisfy:

[0055]

[0056] The minimum UCC is the UCC that does not contain other UCCs, which can be defined as a column set X. And satisfy: is an N-UCC. Similarly, the maximum N-UCC can be defined as a column set X, And satisfy: is a UCC. A UCC containing k columns is called a k-UCC, and an N-UCC containing k columns is called a k-NUCC.

[0057] The minimum unique column combination discovery algorithm process is as follows:

[0058] Step 1. Set k = 1 and classify all single columns as 1-NUCC or 1-UCC. 1-UCC automatically becomes the minimum UCC. It cannot be judged here that all 1-UCCs are key columns, because there are many reasons why the values ​​in a column can be unique. Since 1-UCC columns cannot be included in any other minimum UCC, they will be set aside and only 1-NUCCs will be considered in the next steps. Then, continue recursively to find all k-NUCCs and the minimum k-UCC; the minimum k-UCC is the minimum UCC containing k columns.

[0059] Step 2: After obtaining a list of all minimum k-UCCs and a list of all k-NUCCs, we can continue to study the (k+1) column combination. Since the ultimate goal is to obtain the minimum UCC, it is not necessary to study all (k+1) column combinations, but only all 1-NUCC and k-NUCC combinations. Therefore, for the current k, all 1-NUCCs and k-NUCCs are combined to form a (k+1) column combination.

[0060] Step 3. For each of these (k+1)-column combinations of 1-NUCC and k-NUCC, first check if it already contains any other UCC classified so far. If it does not contain any other UCC, classify it as a (k+1)-NUCC or a minimum (k+1)-UCC. Add the minimum (k+1)-UCC to the output list.

[0061] Step 4: Update k=k+1 and repeat step 2 with the new k value until a new minimum UCC is not found or k+1 exceeds the number of columns.

[0062] Figure 3 Figure 1 is a schematic diagram of the Smote algorithm. Smote is an oversampling method that solves the data imbalance problem by interpolating between minority class samples to generate new, synthetic sample points. This method first randomly selects a minority class sample x i , and then randomly select a sample x from its k nearest neighbors j , and then randomly generate new sample points on the line between the two samples. The formula for generating new samples is as follows:

[0063]

[0064] Among them, α is a number randomly selected between 0 and 1, which is used to control the i and the nearest neighbor sample x j The location where new samples are generated between the two classes. By generating synthetic samples of the minority class instead of simply repeating the minority class samples, the Smote algorithm can increase the diversity of the minority class and help improve the generalization ability of the classifier. Based on Smote, the present invention introduces the Tomek Links algorithm for undersampling, reduces the overlap between categories and improves the clarity of classification boundaries. TomekLinks is a data cleaning technology used to identify and remove overlapping samples in a data set to improve the performance of the classifier. The core idea of ​​Tomek Links is to make the boundaries between categories clearer by identifying and processing samples that may cause blurred classification decision boundaries. In the binary classification problem, a Tomek Link consists of a pair of samples that satisfy the following conditions: they belong to different categories, and in the entire data set, each sample is the nearest neighbor of the other sample in the other category. That is, given a pair of samples x i , x j , belong to different categories y i ,y j (y i ≠y j ), if for any third-party sample x k , all satisfy the following conditions:

[0065] or d(x j , x k )>d(x i , x j )

[0066] Among them, d(x i , x j ) represents the sample x i and x j This indicates that there are no other samples than x i and x j The distance between them is closer, making the pair of samples form a Tomek Link.

[0067] The present invention first performs Smote oversampling on the extracted features, and uses the Smote algorithm to oversample the minority class samples to increase the number of minority class samples to solve the data imbalance problem. Next, Tomek Links cleaning is performed by identifying Tomek Links in the newly generated data set, which may be introduced due to oversampling or exist in the original data set. Then, samples belonging to the majority class in these Tomek Links are removed. The purpose of this step is to reduce the overlap between categories and improve the clarity of classification boundaries.

[0068] Figure 4 The flowchart of the classification modeling using machine learning for the present invention. First, the data set is divided into a training set and a test set. Training is performed on the training data set with label features. The feature vector obtained by the feature extraction / processing module is used to train the random forest classifier. Once the training is completed, the classifier can be widely used in other data tables to automatically discover composite primary keys, thereby realizing the identification of composite primary keys by relying on the intrinsic information of the data table without the support of external information. The model performance is verified on the unlabeled test data set.

[0069] Although the specific embodiments of the present invention are disclosed for the purpose of illustration, the purpose is to help understand the content of the present invention and implement it accordingly, those skilled in the art will understand that various substitutions, changes and modifications are possible without departing from the spirit and scope of the present invention and the appended claims. Therefore, the present invention should not be limited to the content disclosed in the best embodiment, and the scope of the present invention is subject to the scope defined in the claims.

Claims

1. A method for discovering composite primary keys of a data table based on machine learning, the steps comprising: 1) For a sample set, first identify the list of minimum UCCs for each sample; where each sample is a data table, the combination column that does not contain duplicate content and can be used for indexing is called the unique combination column UCC, and the UCC that does not contain other UCCs is defined as the minimum unique combination column, that is, the minimum UCC; 2) Perform feature extraction and combination on each minimum UCC of the sample to obtain a feature vector of each minimum UCC of the sample; 3) Using the feature vectors of each sample to train the classifier; 4) For a data table A to be processed, obtain a list of the minimum UCCs of the data table A, then perform feature extraction on each minimum UCC of the data table A, obtain the feature vectors corresponding to each minimum UCC of the data table A and input them into the trained random forest classifier to obtain the minimum UCC of the data table A for generating a composite primary key, and generate the composite primary key of the data table A accordingly.

2. The method according to claim 1, characterized in that The method to obtain the feature vector of the sample is: 1) For each column in each minimum UCC of the sample, extract the position feature, name feature, data type feature and text length feature of the column; 2) taking the average of the position feature, name feature, data type feature and text length feature of each column in each minimum UCC of the sample, and obtaining the average value of the position information feature, the average value of the name feature, the average value of the data type feature and the average value of the text length feature of the sample; 3) Combine the number of columns in the minimum UCC of the sample with the corresponding average values ​​of the position information feature, the name feature, the data type feature, and the text length feature to obtain the feature vector corresponding to the minimum UCC of the sample.

3. The method according to claim 1, characterized in that The method for identifying the list of minimum UCCs for each sample is as follows: 11), set k = 1, and divide all single columns in each sample into 1-NUCC or 1-UCC; for each 1-NUCC, recursively find all k-NUCCs and the minimum k-UCC; where N-UCC refers to a column combination containing at least one repeated value, k-NUCC is an N-UCC containing k columns, and the minimum k-UCC is the minimum UCC containing k columns; 12) After obtaining a list of all minimum k-UCCs in the sample and a list of all k-NUCCs in the sample, for the current k, combine all 1-NUCCs and k-NUCCs to form a (k+1) column combination; 13) Check whether the (k+1) column combination consisting of all 1-NUCCs and k-NUCCs already contains any other UCC classified so far; if it does not contain any other UCC, classify it as a (k+1)-NUCC or a minimum (k+1)-UCC; then add the minimum (k+1)-UCC to the output list; 14) Update k=k+1 and repeat step 12) with the updated k value until a new minimum UCC is not found or k+1 exceeds the number of columns of the sample.

4. The method according to claim 1, 2 or 3, characterized in that: In step 1), the Smote hybrid Tomek-Links algorithm is first used to solve the data imbalance problem, and the number of minority class samples is increased by generating new samples between the minority class samples and their nearest neighbors.

5. The method according to claim 1, 2 or 3, characterized in that: The feature vector of each sample is used to train the classifier using the random forest algorithm.

6. The method according to claim 5, characterized in that The method for training a classifier using a random forest algorithm is as follows: first, m training subsets are generated from the sample set by a self-service sampling method, and each training subset is obtained by sampling with replacement; then, a decision tree is constructed based on each training subset, and when constructing the decision tree, a number of feature vectors are randomly selected from all the feature vectors to construct a feature subset, and then the feature that can best improve the splitting quality is found in the feature subset as the splitting feature.

7. The method according to claim 1, characterized in that The data cleaning technology Tomek Links is used to identify and remove overlapping samples in the sample set.

8. A server, characterized in that: The method comprises a memory and a processor, wherein the memory stores a computer program, the computer program is configured to be executed by the processor, and the computer program comprises instructions for executing each step in the method according to any one of claims 1 to 7.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images