Data blood relationship discovery method and device based on foreign key aggregation and feature selection

Abstract

The invention discloses a data consanguinity discovery method and device based on foreign key aggregation and feature selection, and the method comprises the steps: 1), enabling an aggregator to aggregate tables having a relationship with a table where a target column is located into a middle table aligned with a target table row according to an input table data set, the target column and an inter-table foreign key relationship; 2) a mapping detection module verifies whether each candidate mapping function can generate a target column according to the columns in the intermediate table, and selects one candidate mapping function as a final mapping function according to a verification result; backtracking each column having a data consanguinity relationship with the target column in the intermediate table by using a mapping function to obtain field-level data consanguinity of the target column; if each candidate mapping function cannot be used as the final mapping function, executing the step 3); 3) screening out a column which contributes to the generation of the target column most from the intermediate table, and taking the column as an optimal feature subset; and backtracking each column in the optimal feature subset to obtain the field-level data consanguinity of the target column.

Description

Technical Field

[0001] The present invention belongs to the field of machine learning and data processing, and relates to a data lineage discovery method and device based on foreign key aggregation and feature selection. This patent is based on foreign key aggregation association table, and uses random forest algorithm to screen features to obtain field-level data lineage. Background Art

[0002] Data lineage plays a core and indispensable role in the field of big data. The existing data lineage methods and their existing problems are as follows:

[0003] 1. The most commonly used parsing method relies on external information input, cannot obtain lineage in low-information scenarios, and focuses on SQL parsing, with insufficient support for data processing methods.

[0004] 2. The tracking and capture method is somewhat invasive, which may bring security risks, additional performance overhead and instability; each component provides data lineage separately, which makes it difficult to unify the data lineage between components and lacks a universal data lineage analysis solution.

[0005] During the process of data dissemination and maintenance, some key metadata information may be lost, and data lineage is crucial to data tables. How to obtain field-level data lineage in a non-intrusive, low-information scenario is an urgent problem to be solved. Summary of the invention

[0006] In view of the problems existing in the prior art, the purpose of the present invention is to provide a data lineage discovery method and device based on foreign key aggregation and feature selection. The present invention focuses on the problem of data lineage analysis in low-information scenarios, and focuses on discovering field-level data lineage in a non-invasive manner through machine learning technology without being restricted by large data processing components, without external information input, and relying only on data tables and foreign key information.

[0007] The present invention constructs an aggregator, realizes the convergence of the table where the target column is located (i.e., the target table) and related tables based on foreign keys, thereby constructing an intermediate table aligned with the target table row; the present invention provides a feature recursive elimination and cross-validation algorithm based on random forest construction. The present invention accepts a table data set, a target column, and a foreign key relationship between tables as inputs, and the data in the data relational database in the table data set, and the features in each column of the table are text features; through the built-in aggregator, the present invention can aggregate other tables that have a relationship with the table where the target column is located into an intermediate table aligned with the target column row (each column in the intermediate table is a feature vector). On this basis, the present invention cyclically constructs a random forest model, uses recursive feature elimination combined with a random forest model for feature selection, and uses a forward selection method to identify the optimal feature subset (i.e., several columns in the intermediate table) from the intermediate table, and the features in the selected optimal feature subset are columns with higher feature importance; columns with higher feature importance are identified as columns with mapping relationships. Through this method, the present invention can achieve non-invasive, low-information scene acquisition of field-level data lineage.

[0008] The method proposed in this patent aims to utilize the foreign key relationship of the data table to obtain the field-level data lineage of the target column in many tables. This method discovers field-level data lineage based on the foreign key aggregation table. First, an internal aggregator is constructed, which effectively merges other tables associated with the table where the target column is located to form an intermediate table aligned with the target column row. Next, the present invention develops a special mapping detector to avoid the problem of overfitting. Finally, the random forest model is iteratively constructed, recursive feature elimination combined with the random forest algorithm is used for feature selection, and the forward selection method is used to identify the optimal feature subset. This method enables this study to independently realize the identification and output of field-level data lineage through machine learning technology without the need for external information support. The work at this stage not only enhances the automation and intelligence level of data processing, but also provides a new technical path for the parsing and analysis of complex data sets.

[0009] To achieve the above-mentioned purpose, in the first aspect of the present invention, a foreign key-based aggregator is designed and implemented. By applying a predefined aggregation function and based on the association between tables (i.e., the foreign key relationship between tables), the module realizes the convergence of the target table and the related tables, thereby constructing an intermediate table aligned with the target table rows. The first is the conversion step, using the sum, count, minimum, maximum, average, standard deviation and variance basic aggregation functions to predefine the aggregator, and according to the detected foreign keys, the associated tables are aggregated and connected to the target table. The higher the information gain, the higher the predictive ability of the feature for the target variable. In the data lineage, the source field generates the target field, so the column in the intermediate table is used as a feature vector, and the higher its information gain, the higher the probability of being the source field, so the information gain is used for preliminary screening, and each column is used as a feature to calculate the information gain, and the columns with information gain greater than the set threshold are retained as candidate columns, and they are connected to the intermediate table.

[0010] In the second aspect of the present invention, a mapping detection module is designed and implemented, which is responsible for verifying whether the target column is generated by the column of the intermediate table through a set mapping function to meet the needs of the subsequent machine learning classifier. If the target column is generated by the set mapping function, the target column can be used to directly trace back to generate data lineage, otherwise, the data lineage is generated through subsequent machine learning related modules. The purpose of this module is to avoid overfitting problems affecting model performance.

[0011] The third aspect of the present invention uses a feature screening algorithm to finally achieve the output of data lineage. For the intermediate table and target column after screening, the purpose is to screen which fields generate the target column, so the problem can be abstracted as a feature screening problem. This paper predicts the column with significant feature importance as the source field of the target column. Because the importance of the feature can be reflected in the division of the node when the decision tree is constructed, the random forest algorithm can calculate the importance of the feature. The present invention evaluates the importance of the feature based on the average impurity reduction, that is, the importance of the feature is evaluated by calculating the contribution of each feature to the reduction of impurity when the decision tree node is split. Relying solely on the random forest algorithm may introduce a certain degree of random bias, which may cause the model to overemphasize the importance of a certain feature while ignoring other equally important features. In view of this, it is particularly important to optimize the feature selection process. The present invention uses recursive feature elimination combined with the random forest algorithm for feature selection, and uses the forward selection method to identify the optimal feature subset. The obtained feature set is the optimal feature subset, that is, the column in the intermediate table that contributes the most to the generation of the target column. Through the backtracking operation, the source field of the target column can be obtained, that is, the field-level data lineage of column y is found.

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

[0013] A data lineage discovery method based on foreign key aggregation and feature selection, the steps of which include:

[0014] 1) The aggregator aggregates the tables that have a relationship with the table where the target column y is located into an intermediate table R aligned with the target table row according to the input table data set, the target column y and the foreign key relationship between tables; each column in the intermediate table R corresponds to a feature vector, and the target table is the table where the target column y is located;

[0015] 2) The mapping detection module verifies whether each candidate mapping function can generate the target column y according to the columns in the intermediate table R, and selects a candidate mapping function as the final mapping function according to the verification result, which is used to identify the column in the intermediate table R that generates the target column y, that is, the column in the intermediate table R that has a data lineage relationship with the target column y; then, the mapping function is used to trace back each column in the intermediate table R that has a data lineage relationship with the target column y to obtain the field-level data lineage of the target column y; if none of the candidate mapping functions can be used as the final mapping function, step 3) is executed;

[0016] 3) Using the lineage prediction module, the column that contributes most to the generation of the target column y is selected from the intermediate table R as the optimal feature subset; each column in the optimal feature subset is backtracked to obtain the field-level data lineage of the target column y.

[0017] Furthermore, the method for generating the intermediate table R is:

[0018] 11) Obtain the foreign key that references the primary key of the target table from the table data set. Assume that column U of table A references the primary key P of the target table. Then apply each set aggregate function f to each column c in table A except column U to perform steps 111) to 114) to generate column c′:

[0019] 111) For each row i of the target table, obtain the primary key P in the row i and obtain the corresponding tuple p i =B[P][i];

[0020] 112) Search table A for each row j that references row i in the target table, and obtain set J i :={j|A[U][j]=p i};

[0021] 113) According to the result obtained in step 12), the set of values ​​of row i in column c that references the target table is obtained.

[0022] 114) Apply the aggregate function f to c i Perform aggregate calculation c′[i]=f(c i ) to get column c′;

[0023] 12) Calculate the information gain of each column obtained in step 11), and generate the intermediate table R according to the columns whose information gain is greater than the set threshold.

[0024] Furthermore, the aggregation function f includes a sum function, a count function, a minimum function, a maximum function, an average function, a standard deviation function and a variance function.

[0025] Furthermore, the method for obtaining the optimal feature subset is:

[0026] 31) constructing a random forest model to evaluate the feature importance of each column in the intermediate table;

[0027] 32) In each round of iteration, the random forest model is used to calculate the feature importance mean μ and standard deviation σ of each column in the intermediate table, and μ-2·σ is used as a threshold to delete the columns in the intermediate table whose feature importance is lower than μ-2·σ, until there are no columns in the intermediate table that are lower than the μ-2·σ threshold;

[0028] 33) Select the column with the highest feature importance score in the intermediate table updated in step 32) and add it to the feature set, and build a random forest model based on the feature set; then in each subsequent iteration, traverse the columns that have not been selected in the intermediate table updated in step 32) and add them to the feature set, build a random forest model based on the updated feature set, and verify and evaluate the performance of the currently constructed random forest model. If the performance of the currently constructed random forest model is improved, proceed to the next iteration, otherwise stop the iteration;

[0029] 34) The feature set finally obtained in step 33) is used as the optimal feature subset.

[0030] Furthermore, the aggregation function adopted by the aggregator is a sum function, a count function, a minimum function, a maximum function, an average function, a standard deviation function or a variance function.

[0031] A data lineage discovery device based on foreign key aggregation and feature selection, characterized in that it includes a memory and a processor, the memory stores a computer program, the computer program is configured to be executed by the processor, and the computer program includes instructions for executing each step in the above method.

[0032] 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.

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

[0034] The present invention provides a data lineage discovery method based on foreign key aggregation and feature selection, which can identify the field-level data lineage of the target column in a series of seemingly disorganized data tables in an information-scarce environment by using machine learning methods. It achieves the acquisition of field-level data lineage in a non-invasive, low-information scenario.

[0035] The present invention has strong practicality and application scope in the field of data processing and has very broad application prospects. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 This is a flow chart of the data lineage discovery method based on foreign key aggregation and feature selection of the present invention.

[0037] Figure 2 Schematic diagram of directed acyclic processing for big data.

[0038] Figure 3 This is a schematic diagram of field conversion. DETAILED DESCRIPTION

[0039] 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.

[0040] Figure 1 This is a flow chart of a data lineage discovery method based on foreign key aggregation and feature selection proposed by the present invention.

[0041] Step S101 is the aggregator conversion step. This module is responsible for using the predefined aggregator to aggregate the associated table to the target table based on the detected foreign key. The present invention adapts the following basic aggregation functions: sum function, count function, minimum function, maximum function, average function, standard deviation function and variance function. For each column that is not in the target table, consider the possible ways in which it can contribute to the target column by applying an aggregation function to itself, and store all the aggregated columns in a large table.

[0042] Specifically, check each foreign key that references the primary key of the target table. Assume that column U of table A references the primary key P of target table B. Then for each aggregate function f, and column c in table A that does not belong to column U, apply aggregate function f through the foreign key to generate column c'. The specific steps are as follows:

[0043] 1) For each row i of table B, get the value of the primary key P in the row and get the tuple p i =B[p][i].p i is the element value of the i-th row in the primary key P;

[0044] 2) Then search for all rows j in table A that reference row i in table B, that is, find set J i:={j|A[U][j]=p i}; That is, if the jth row of column c (not column U) of table A refers to the above element p i , then put the jth row into set J i middle;

[0045] 3) Thus, we can get the set of values ​​in column c that reference row i of table B, namely c i is a multiset, i.e. the same value may appear multiple times. c[j] is the jth row of column c (i.e. not column U) of table A.

[0046] 4) Finally, apply the aggregate function f to calculate the aggregate and set the corresponding value in column c' to c'[i] = f(c i ).

[0047] The present invention will generate such a column for each reasonable combination of foreign keys, columns, and aggregate functions. c' is a column that matches the rows of the target column and may directly contribute to it. However, directly fully connecting all columns will make the subsequent machine learning cost very high, so preliminary screening is performed through information gain. Information gain can be used to measure the predictive power of a given feature for the target variable, which is based on entropy measurement. Entropy is defined as follows:

[0048]

[0049] Where X is a random variable, x i is a possible value of this random variable, P(x i ) is the probability of taking a value. The information gain is calculated as follows:

[0050] IG(D, A) = H(D) - H(D|A)

[0051] This determines the contribution of feature A to reducing uncertainty in the data set, that is, the higher the information gain, the higher the predictive power of the feature for the target variable. In the data lineage, the source field generates the target field, so the column in the intermediate table is used as a feature vector. The higher its information gain, the higher the probability of being the source field. Therefore, the information gain is used for preliminary screening, and each column is used as a feature to calculate the information gain. The columns with higher information gain are retained as candidate columns and connected to the intermediate table R.

[0052] Step S102 is mapping detection. After obtaining the intermediate table R, the ultimate goal is to identify which columns can generate the target column y by applying the mapping function. In many practical application systems, some basic mapping functions, such as the sum, average and difference of columns, are extremely common. However, if these functions are directly applied to the machine learning model, it may cause overfitting problems, thereby affecting the performance of the model. In view of this, this paper designs and implements a special mapping detection module, the task of which is to verify whether the above-mentioned candidate mapping functions (such as the sum function of the columns in the calculation table, the average function of the columns in the calculation table, and the difference function of the columns in the calculation table) are suitable as mapping functions. Only when the module fails to successfully identify that the target column can be represented by the sum, difference or average of other columns, will the subsequent machine learning module be enabled. On the contrary, if these basic mapping functions are applicable, the backtracking strategy can be directly adopted to output the blood relationship of the data.

[0053] Step S103 is the blood relationship prediction module. In this module, for the screened intermediate table R and target column y, the purpose is to screen which fields generate the target column y, so the problem can be abstracted as a feature screening problem. The present invention predicts the columns with significant feature importance as the source fields of the target column. The present invention will evaluate the importance of features based on the average impurity reduction, that is, the importance of features is evaluated by calculating the contribution of each feature to the reduction of impurity when the decision tree node is split. Impurity reduction is measured by the frequency and depth of feature use in the tree. Features contribute more to prediction when used at the top of the tree, so they are considered more important. The feature importance calculation formula is calculated as follows:

[0054] Importance(X m )=∑ t∈T p(t)ΔI(X m , t)

[0055] Among them, x m is feature m, T is the set of nodes in the decision tree, p(t) is the sample ratio at node t, ΔI(X m , t) is the reduction in impurity of feature m at node t. Each column of the intermediate table R is a feature X m , the sample ratio at node t refers to the ratio of similar samples at node t of the decision tree.

[0056] However, when evaluating the importance of features, relying solely on the random forest algorithm may introduce a certain degree of random bias, which may cause the model to overemphasize the importance of a certain feature while ignoring other equally important features. In addition, random forests may overfit when processing noisy data, which in turn affects the accuracy of feature importance assessment. Due to the existence of this randomness, some inherently important features may receive lower scores in the constructed random forest model. In view of this, it is particularly important to optimize the feature selection process. This study used recursive feature elimination combined with the random forest algorithm for feature selection, and used the forward selection method to identify the optimal feature subset. The specific process is as follows:

[0057] First, a random forest model is constructed as the basis for the preliminary assessment of feature importance. Then the mean μ and standard deviation σ of feature importance are calculated, and μ-2·σ is used as the threshold. In each round of iteration, μ-2·σ is calculated separately, and features with importance lower than μ-2·σ are deleted before continuing to the next round of iteration. This process continues until there are no features lower than the μ-2·σ threshold. The remaining features are sorted in descending order according to the mean of their cumulative scores over multiple iterations.

[0058] Then select the feature with the highest current score and add it to the feature set, and build a random forest model based on the feature set. Then start the iteration. In each iteration, traverse the features that have not been selected, add them to the feature set, build a random forest model, evaluate the performance of the random forest through cross-validation, select the features that improve the model, and add them permanently to the feature set. Repeat this process until adding new features no longer significantly improves model performance. The resulting feature set is the optimal feature subset, that is, the column in the intermediate table R that contributes the most to the generation of the target column y. Through the backtracking operation, the source field of column y can be obtained, that is, the field-level data lineage of column y is discovered.

[0059] Step S104 is the backtracking stage, in which the columns in the intermediate table are traced back to the columns in the original table to obtain the field-level data lineage of the target column.

[0060] Figure 2 The diagram is a directed acyclic diagram for big data processing. The discovery of data lineage is the process of tracing the data obtained after data processing. Each data processing step is defined as a transformation T, and the input data set is recorded as D in , the output data set is recorded as D out , the set of transformation rules is denoted as F, then the transformation T can be defined as a triple:

[0061]

[0062] This allows the big data processing process to be optimized into a directed acyclic graph. Figure 3 The edges represent the input and output of data, and the nodes represent the transformations T.

[0063] Figure 3 The figure is a schematic diagram of field conversion. Data lineage can be divided into table-level data lineage and field-level data lineage according to granularity. This paper will focus on the field-level data lineage with finer granularity. Field-level data lineage tracks the flow of data at the field level and obtains the mapping relationship between the target field of the target table and the source field of the source table. Figure 3 As shown in the figure, fields a1, a2, and a3 contribute to the generation of c1 and c2, so they are source fields. b1 and b2 do not contribute to data, so they are not source fields. It can be seen that not all input fields of the source table are source fields. To obtain the data lineage at the field level, it is necessary to obtain the field mapping relationship between the source table and the target table. The definition of the field mapping relationship is as follows:

[0064] For the set of all input fields D in And the set of all fields output D out , let D in ′∈D in , D out ′∈D out , there exists a transformation rule f such that f acts on D in ' can produce D out ′, then it is called D in ' to D out ′ constitutes a field mapping. in ′ is the source field, D out ' is the target field.

[0065] 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 data lineage discovery method based on foreign key aggregation and feature selection, the steps of which include: 1) The aggregator aggregates the tables that have a relationship with the table where the target column y is located into an intermediate table R aligned with the target table row according to the input table data set, the target column y and the foreign key relationship between tables; each column in the intermediate table R corresponds to a feature vector, and the target table is the table where the target column y is located; 2) The mapping detection module verifies whether each candidate mapping function can generate the target column y according to the columns in the intermediate table R, and selects a candidate mapping function as the final mapping function according to the verification result, which is used to identify the column in the intermediate table R that generates the target column y, that is, the column in the intermediate table R that has a data lineage relationship with the target column y; then, the mapping function is used to trace back each column in the intermediate table R that has a data lineage relationship with the target column y to obtain the field-level data lineage of the target column y; if none of the candidate mapping functions can be used as the final mapping function, step 3) is executed; 3) Using the lineage prediction module, the column that contributes most to the generation of the target column y is selected from the intermediate table R as the optimal feature subset; each column in the optimal feature subset is backtracked to obtain the field-level data lineage of the target column y.

2. The method according to claim 1, characterized in that The method for generating the intermediate table R is: 11) Obtain the foreign key that references the primary key of the target table from the table data set. Assume that column U of table A references the primary key P of the target table. Then apply each set aggregate function f to each column c in table A except column U to perform steps 111) to 114) to generate column c′: 111) For each row i of the target table, obtain the primary key P in the row i and obtain the corresponding tuple p i =B[P][i]; 112) Search table A for each row j that references row i in the target table, and obtain set J i :={j|A[U][j]=p i }; 113) According to the result obtained in step 12), the set of values ​​of row i in column c that references the target table is obtained. 114) Apply the aggregate function f to c i Perform aggregate calculation c′[i]=f(c i ) to get column c′; 12) Calculate the information gain of each column obtained in step 11), and generate the intermediate table R according to the columns whose information gain is greater than the set threshold.

3. The method according to claim 2, characterized in that The aggregation function f includes a sum function, a count function, a minimum function, a maximum function, an average function, a standard deviation function and a variance function.

4. The method according to claim 1, characterized in that: The method for obtaining the optimal feature subset is: 31) constructing a random forest model to evaluate the feature importance of each column in the intermediate table; 32) In each round of iteration, the random forest model is used to calculate the feature importance mean μ and standard deviation σ of each column in the intermediate table, and μ-2·σ is used as a threshold to delete the columns in the intermediate table whose feature importance is lower than μ-2·σ, until there are no columns in the intermediate table that are lower than the μ-2·σ threshold; 33) Select the column with the highest feature importance score in the intermediate table updated in step 32) and add it to the feature set, and build a random forest model based on the feature set; then in each subsequent iteration, traverse the columns that have not been selected in the intermediate table updated in step 32) and add them to the feature set, build a random forest model based on the updated feature set, and verify and evaluate the performance of the currently constructed random forest model. If the performance of the currently constructed random forest model is improved, proceed to the next iteration, otherwise stop the iteration; 34) The feature set finally obtained in step 33) is used as the optimal feature subset.

5. The method according to claim 1, 2 or 3, characterized in that: The aggregation function adopted by the aggregator is a sum function, a count function, a minimum function, a maximum function, an average function, a standard deviation function or a variance function.

6. A data lineage discovery device based on foreign key aggregation and feature selection, 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 5.

7. 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 5 are implemented.

Images