A data bloodline discovery method and device based on foreign key aggregation and feature selection

Abstract

The application discloses a data bloodline discovery method and device based on foreign key aggregation and feature selection, and steps of the method comprise the following steps: 1) an aggregator aggregates tables related to a table where a target column is located into an intermediate table aligned with rows of the target table according to input table data sets, the target column and inter-table foreign key relations; 2) a mapping detection module verifies whether each candidate mapping function can generate the target column according to columns in the intermediate table, and selects a candidate mapping function as a final mapping function according to a verification result; then, the mapping function is used to backtrack each column in the intermediate table having a data bloodline relation with the target column, so that field-level data bloodlines of the target column are obtained; if each candidate mapping function cannot be used as the final mapping function, step 3) is executed; 3) columns having the greatest contribution to generating the target column are selected from the intermediate table as an optimal feature subset; and each column in the optimal feature subset is backtracked, so that field-level data bloodlines of the target column are obtained.

Description

Technical Field

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

[0002] Data lineage plays a central and indispensable role in the field of big data. Existing data lineage methods and their inherent 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, which has insufficient support for data processing methods.

[0004] 2. The tracking and capture method is somewhat intrusive and may pose security risks, as well as additional performance overhead and instability. Each component provides its own data lineage, making it difficult to unify and combine the data lineages between the components, and lacking a universal data lineage resolution solution.

[0005] During the dissemination and maintenance of data, some key metadata information may be lost. Data lineage is crucial for data tables, and 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] To address the problems existing in the prior art, the present invention aims to provide a data lineage discovery method and apparatus based on foreign key aggregation and feature selection. This invention focuses on the data lineage analysis problem in low-information scenarios, emphasizing the use of machine learning techniques to discover field-level data lineage in a non-intrusive manner, without the limitations of restricted big data processing components and without external information input, relying solely on data tables and foreign key information.

[0007] This invention constructs an aggregator that aggregates the target table (containing the target column) and related tables based on foreign keys, thereby constructing an intermediate table aligned with the rows of the target table. This invention also provides a feature recursive elimination and cross-validation algorithm based on random forests. This invention accepts a table dataset, the target column, and the foreign key relationships between tables as input. The table dataset contains data from a relational database, and each column in the table contains text features. Through the built-in aggregator, this invention can aggregate other tables that are related to the target column into an intermediate table aligned with the target column (each column in the intermediate table is a feature vector). Based on this, this invention iteratively constructs a random forest model, using recursive feature elimination combined with the random forest model for feature selection. A forward selection method is used to identify the optimal feature subset (i.e., several columns in the intermediate table). The features in the selected optimal feature subset are the columns with high feature importance. Columns with high feature importance are identified as columns with mapping relationships. Through this method, this invention can achieve non-intrusive, low-information-scenario data lineage acquisition at the field level.

[0008] This patent proposes a method to obtain the field-level data lineage of a target column from numerous tables by utilizing foreign key relationships. Based on foreign key aggregation tables, this method discovers field-level data lineage by first constructing an internal aggregator that effectively merges other tables associated with the target column, forming an intermediate table aligned with the target column rows. Next, a specially designed mapping detector is developed to avoid overfitting. Finally, a random forest model is iteratively constructed, and recursive feature elimination combined with the random forest algorithm is used for feature selection. Forward selection is then used to identify the optimal feature subset. This method enables the independent identification and output of field-level data lineage through machine learning techniques without external information support. This stage of work not only enhances the automation and intelligence of data processing but also provides a new technical path for the parsing and analysis of complex datasets.

[0009] To achieve the above objectives, a first aspect of the present invention designs and implements a foreign key-based aggregator. By applying predefined aggregation functions and based on inter-table associations (i.e., foreign key relationships between tables), this module aggregates the target table and related tables, thereby constructing an intermediate table aligned with the rows of the target table. The first step is a transformation step. An aggregator is predefined using basic aggregation functions such as sum, count, minimum, maximum, average, standard deviation, and variance. Based on the detected foreign keys, the associated tables are aggregated and connected to the target table. Higher information gain indicates a higher predictive power of the feature for the target variable. In data lineage, the source field generates the target field; therefore, columns in the intermediate table are used as feature vectors. Higher information gain indicates a higher probability that the column is a source field. Therefore, information gain is used for initial screening. Each column is treated as a feature, and its information gain is calculated. Columns with information gain greater than a set threshold are retained as candidate columns and connected to the intermediate table.

[0010] In a second aspect, this invention designs and implements a mapping detection module. This module is responsible for verifying whether a target column is generated from a column of an intermediate table via a predefined mapping function to meet the needs of a subsequent machine learning classifier. If the target column is generated via the predefined mapping function, data lineage can be directly generated using this target column; otherwise, data lineage is generated through processing by subsequent machine learning modules. The purpose of this module is to prevent overfitting from affecting model performance.

[0011] The third aspect of this invention utilizes a feature selection algorithm to ultimately output data lineage. For the filtered intermediate table and target column, the goal is to select which fields generated the target column. Therefore, this problem can be abstracted as a feature selection problem. This paper predicts columns with significant feature importance as the source fields of the target column. Because the importance of features can be reflected in the node splitting when constructing a decision tree, the random forest algorithm can calculate feature importance. This invention evaluates feature importance based on the reduction of average impurity, that is, by calculating the contribution of each feature to the reduction of impurity when splitting decision tree nodes. Relying solely on the random forest algorithm may introduce a certain degree of randomness bias, which may cause the model to overemphasize the importance of a certain feature while ignoring other equally important features. Therefore, optimizing the feature selection process is particularly important. This invention uses recursive feature elimination combined with the random forest algorithm for feature selection, and uses forward selection to identify the optimal feature subset. The resulting feature set is the optimal feature subset, that is, the column in the intermediate table that contributes the most to generating the target column. Through backtracking, the source fields of the target column can be obtained, thus discovering the field-level data lineage of column y.

[0012] The technical solution of this invention is as follows:

[0013] A data lineage discovery method based on foreign key aggregation and feature selection includes the following steps:

[0014] 1) The aggregator, based on the input tabular dataset, the target column y, and the foreign key relationships between tables, aggregates tables that are related to the table containing the target column y into an intermediate table R aligned with the rows of the target table; each column in the intermediate table R corresponds to a feature vector, and the target table is the table containing the target column y;

[0015] 2) The mapping detection module verifies whether each candidate mapping function can generate the target column y based on the columns in the intermediate table R. Based on the verification results, a candidate mapping function is selected as the final mapping function to identify the columns in the intermediate table R that generate the target column y, i.e., the columns in the intermediate table R that have a data lineage relationship with the target column y. Then, the mapping function is used to backtrack 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, then step 3) is executed.

[0016] 3) Use the lineage prediction module to select the column that contributes the most to the generation of the target column y from the intermediate table R, and use it as the optimal feature subset; backtrack on each column in the optimal feature subset to obtain the field-level data lineage of the target column y.

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

[0018] 11) Obtain the foreign keys that reference the primary key of the target table from the dataset. Suppose column U of table A references the primary key P of the target table. Then, apply each set aggregate function f to process each column c in table A except column U using steps 111) to 114) to generate column c':

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

[0020] 112) Search table A references each row j of row i in the target table, resulting in set J. i :={j|A[U][j]=p i};

[0021] 113) Based on the results obtained in step 12), obtain the set of values ​​in column c that reference row i of the target table.

[0022] 114) Applying the aggregate function f to c i Perform aggregate calculations c′[i]=f(c i ) to obtain column c';

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

[0024] Furthermore, the aggregation function f includes a sum function, a counting function, a minimum value function, a maximum value function, a mean function, a standard deviation function, and a variance function.

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

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

[0027] 32) In each round of loop iteration, the average value μ and standard deviation σ of the feature importance of each column in the intermediate table are calculated using the random forest model. Using μ-2·σ as the threshold, columns in the intermediate table with feature importance lower than μ-2·σ are deleted until there are no columns in the intermediate table with feature importance 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. Construct a random forest model based on the feature set. Then, in each subsequent iteration, traverse the columns that have not yet been selected in the intermediate table updated in step 32) and add them to the feature set. Construct 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) Take the feature set obtained in step 33) as the optimal feature subset.

[0030] Furthermore, the aggregation function used by the aggregator is a sum function, a counting function, a minimum value function, a maximum value function, an average value 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 storing a computer program configured to be executed by the processor, the computer program including instructions for performing the steps of the above-described method.

[0032] A computer-readable storage medium having a computer program stored thereon, characterized in that the computer program, when executed by a processor, implements the steps of the above-described method.

[0033] The advantages of this invention are as follows:

[0034] This invention provides a data lineage discovery method based on foreign key aggregation and feature selection. It can identify field-level data lineage of target columns in seemingly disorganized data tables, even in information-scarce environments, using machine learning methods. This achieves non-intrusive, low-information data lineage acquisition.

[0035] This invention has strong practicality and wide application scope in the field of data processing, and has a very broad application prospect. Attached Figure Description

[0036] Figure 1 This is a flowchart of the data lineage discovery method based on foreign bond aggregation and feature selection according to the present invention.

[0037] Figure 2 This is a schematic diagram of directed acyclic networks (DAGs) for big data processing.

[0038] Figure 3 This is a diagram illustrating field conversion. Detailed Implementation

[0039] The present invention will now be described in further detail with reference to the accompanying drawings. The examples given are only for explaining the present invention and are not intended to limit the scope of the present invention.

[0040] Figure 1 This is a flowchart of a data lineage discovery method based on foreign key aggregation and feature selection proposed in this invention.

[0041] Step S101 is the aggregator transformation step. This module is responsible for using a predefined aggregator to aggregate and connect the related tables to the target table based on the detected foreign keys. This invention adapts the following basic aggregation functions: sum function, counting function, minimum value function, maximum value function, average function, standard deviation function, and variance function. For each column not in the target table, consider the possible ways it can contribute to the target column by applying aggregation functions to itself, and store all aggregated columns in a large table.

[0042] Specifically, examine every foreign key that references the primary key of the target table. Assume column U of table A references the primary key P of target table B. Then, for each aggregate function f, and a column c in table A that is not a column U, generate column c' by applying the aggregate function f through the foreign key. The specific steps are as follows:

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

[0044] 2) Then search table A for all rows j that reference row i in table B, i.e., find set J. i:={j|A[U][j]=p i}; that is, if the j-th row of column c (i.e., not column U) of table A references the above element p. i Then the j-th row is placed in set J. i middle;

[0045] 3) Therefore, we can obtain the set of values ​​in column c that reference row i of table B, i.e. c i It is a multiset, meaning that the same value may appear multiple times. c[j] is the j-th row of column c (i.e., not column U) in 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] This invention generates such a column for each reasonable combination of foreign keys, columns, and aggregation functions. c′ is the column that matches the row of the target column and may directly contribute to it. However, performing a full join operation on all columns directly would make subsequent machine learning very costly; therefore, initial screening is performed using information gain. Information gain measures the predictive power of a given feature for a target variable and is based on entropy. Entropy is defined as follows:

[0048]

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

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

[0051] This determines the contribution of feature A to reducing uncertainty in the dataset; 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. Therefore, columns in the intermediate table are used as feature vectors. The higher their information gain, the higher the probability that they are source fields. Thus, information gain is used for initial screening. Each column is treated as a feature, and its information gain is calculated. Columns with high information gain are retained as candidate columns and connected to the intermediate table R.

[0052] Step S102 is the mapping detection. After obtaining the intermediate table R, the ultimate goal is to identify which columns can generate the target column y by applying mapping functions. In many practical application systems, some basic mapping functions, such as column sums, averages, and differences, are extremely common. However, if these functions are directly applied to machine learning models, it may lead to overfitting problems, thereby affecting the model's performance. Therefore, this paper designs and implements a dedicated mapping detection module. The task of this module is to verify whether the above candidate mapping functions (such as the function to calculate the sum of columns in the table, the function to calculate the average of columns in the table, and the function to calculate the difference of columns in the table) are suitable as mapping functions. The subsequent machine learning module will only be activated if this module fails to successfully identify that the target column can be represented by the sum, difference, or average of other columns. Conversely, if these basic mapping functions are applicable, a backtracking strategy can be directly adopted to output the data lineage.

[0053] Step S103 is the lineage prediction module. In this module, for the filtered intermediate table R and the target column y, the goal is to select which fields generated the target column y. Therefore, this problem can be abstracted as a feature selection problem. This invention predicts that columns with significant feature importance are the source fields of the target column. This invention evaluates feature importance based on the reduction of average impurity, that is, by calculating the contribution of each feature to the reduction of impurity when splitting at a decision tree node. The reduction of impurity is measured by the frequency and depth of feature usage in the tree. Features used at the top of the tree contribute more to the prediction and are therefore considered more important. The feature importance calculation formula is as follows:

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

[0055] Where, x m Let m be the feature, T be the set of nodes in the decision tree, p(t) be the proportion of samples at node t, and ΔI(X) be the value of the feature m. m ,t) represents the reduction in impurity of feature m at node t. Each column of the intermediate table R represents a feature X. m The sample proportion at node t refers to the proportion of samples of the same type at node t in the decision tree.

[0056] However, relying solely on the random forest algorithm when evaluating feature importance may introduce a degree of randomness bias. This bias could lead the model to overemphasize the importance of a particular feature while neglecting other equally important features. Furthermore, random forests may overfit when dealing with noisy data, thus affecting the accuracy of feature importance evaluation. Due to this randomness, some inherently important features may receive lower scores in the constructed random forest model. Therefore, optimizing the feature selection process is particularly important. This study employs a recursive feature elimination combined with the random forest algorithm for feature selection, using forward selection to identify the optimal feature subset. The specific process is as follows:

[0057] First, a random forest model is constructed as the basis for preliminary feature importance assessment. Then, the mean μ and standard deviation σ of feature importance are calculated. Using μ⁻²σ as the threshold, in each iteration, μ⁻²σ is calculated again, and features with importance below μ⁻²σ are removed before proceeding to the next iteration. This process continues until no features below the μ⁻²σ threshold remain. The remaining features are then sorted in descending order based on the average cumulative score across multiple iterations.

[0058] Then, select the feature with the highest current score and add it to the feature set. Construct a random forest model based on this feature set. Iterate through the features that haven't yet been selected, adding them to the feature set and building the random forest model. Evaluate the performance of the random forest using cross-validation, selecting features that improve the model and permanently adding them 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, which is the column in the intermediate table R that contributes the most to generating the target column y. By backtracking, the source fields of column y can be obtained, thus discovering the field-level data lineage of column y.

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

[0060] Figure 2 This is a schematic diagram of directed acyclic flow (DAG) data processing. Data lineage discovery is the process of tracing the origins of the data obtained after processing. Each data processing step is defined as a transformation T, and the input data set is denoted as D. in The output data set is denoted as D. out Let F be the set of transformation rules, then 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 middle edge represents the input and output of data, and the node represents the transformation T.

[0063] Figure 3 This is a diagram illustrating field transformation. Data lineage can be categorized into table-level and field-level lineages based on granularity. This invention focuses on the finer-grained field-level data lineage. Field-level data lineage tracks the flow of data at the field level, obtaining the mapping relationship between the target field of the target table and the source field of the source table. For example... Figure 3 As shown, fields a1, a2, and a3 contribute to the generation of c1 and c2, and are therefore source fields. Fields b1 and b2 do not contribute any data and are therefore not source fields. This demonstrates that input fields of the source table are not necessarily source fields. To obtain field-level data lineage, the field mapping relationship between the source and target tables needs to be determined. The field mapping relationship is defined as follows:

[0064] For the set of all input fields D in and the complete set of fields D output 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 If ′, then D is called D. in ′ to D out ' ' forms a field mapping. D in ′ represents the source field, D out ′ represents the target field.

[0065] Although specific embodiments of the invention have been disclosed for illustrative purposes to aid in understanding and implementing the invention, those skilled in the art will understand that various substitutions, variations, and modifications are possible without departing from the spirit and scope of the invention and the appended claims. Therefore, the invention should not be limited to the content disclosed in the preferred embodiments, and the scope of protection claimed by the invention is defined by the claims.

Claims

1. A data lineage discovery method based on foreign key aggregation and feature selection, comprising the following steps: 1) The aggregator, based on the input tabular dataset, the target column y, and the foreign key relationships between tables, aggregates tables that are related to the table containing the target column y into an intermediate table R aligned with the rows of the target table; each column in the intermediate table R corresponds to a feature vector, and the target table is the table containing the target column y; 2) The mapping detection module verifies whether each candidate mapping function can generate the target column y based on the columns in the intermediate table R. Based on the verification results, a candidate mapping function is selected as the final mapping function to identify the columns in the intermediate table R that generate the target column y, i.e., the columns in the intermediate table R that have a data lineage relationship with the target column y. Then, the mapping function is used to backtrack 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, then step 3) is executed. 3) Use the lineage prediction module to select the column that contributes the most to the generation of the target column y from the intermediate table R, and use it as the optimal feature subset; backtrack on each column in the optimal feature subset 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 as follows: 11) Obtain the foreign keys that reference the primary key of the target table from the dataset. Suppose column U of table A references the primary key P of the target table. Then, apply each set aggregate function f to process each column c in table A except column U using steps 111) to 114) to generate column c': 111) For each row i in the target table, obtain the primary key P in row i and get the corresponding tuple p. i =B[P][i]; 112) Search table A references each row j of row i in the target table, resulting in set J. i :={j|A[U][j]=p i }; 113) Based on the results obtained in step 12), obtain the set of values ​​in column c that reference row i of the target table. 114) Applying the aggregate function f to c i Perform aggregate calculations c′[i]=f(c i ) to obtain column c'; 12) Calculate the information gain of each column obtained in step 11), and generate the intermediate table R based on the columns whose information gain is greater than a set threshold.

3. The method according to claim 2, characterized in that, The aggregation function f includes summation function, counting function, minimum value function, maximum value function, average function, standard deviation function, and variance function.

4. The method according to claim 1, characterized in that, The method for obtaining the optimal feature subset is as follows: 31) Construct a random forest model to evaluate the feature importance of each column in the intermediate table R; 32) In each round of loop iteration, the average value μ and standard deviation σ of the feature importance of each column in the intermediate table R are calculated using the random forest model. Using μ-2·σ as the threshold, columns in the intermediate table R with feature importance lower than μ-2·σ are deleted until there are no columns in the intermediate table R with feature importance lower than the μ-2·σ threshold. 33) Select the column with the highest feature importance score in the intermediate table R updated in step 32) and add it to the feature set. Construct a random forest model based on the feature set. Then, in each subsequent iteration, traverse the columns in the intermediate table R updated in step 32) that have not yet been selected and add them to the feature set. Construct 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) Take the feature set obtained in step 33) as the optimal feature subset.

5. The method according to claim 1, characterized in that, The aggregation function used by the aggregator is a sum function, a counting function, a minimum value function, a maximum value function, an average value 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, It includes a memory and a processor, the memory storing a computer program configured to be executed by the processor, the computer program including instructions for performing each step of the method of 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, it implements the steps of the method according to any one of claims 1 to 5.

Images