A data table auditing method, system and product for a database

Abstract

The application relates to the technical field of database data auditing and analysis, and particularly discloses a data table auditing method, system and product for a database, the method comprising the following steps: obtaining a configuration file; loading a reference benchmark table and a data table to be audited; initializing a feature extractor based on KME technology according to the reference benchmark table, and creating a field processor; respectively performing feature conversion on the reference benchmark table and the data table to be audited by using the field processor to obtain a feature vector; training an XGBoost model by using the feature vector, testing the trained XGBoost model, and obtaining an AUC score of an ROC; performing SHAP analysis on the trained XGBoost model to obtain SHAP values of the features; and obtaining an auditing report according to the feature vector, the AUC score and the SHAP values. The application realizes comprehensive, efficient and accurate analysis of database table data.

Description

Technical Field

[0001] This invention relates to the field of database data auditing and analysis technology, and in particular to a method, system and product for auditing data tables in a database. Background Technology

[0002] Current data auditing technologies have many shortcomings, making it difficult to meet the needs for comprehensive, efficient, and accurate analysis of database table data. Specifically:

[0003] (1) Data feature extraction and processing

[0004] Poor data type compatibility: Existing technologies lack universal feature extraction methods, making it difficult to uniformly handle different types of fields in database tables, such as numeric and categorical fields. When faced with complex data structures, different processing methods are often required for different data types, increasing the complexity and difficulty of processing and easily leading to inconsistencies in data processing.

[0005] High computational complexity: When processing large-scale database tables, traditional data feature extraction and transformation methods involve enormous computational loads, resulting in low processing efficiency. For example, in complex operations such as calculating kernel matrices, time and space complexity increase exponentially with the amount of data, causing the system's performance to degrade severely when processing large-scale data, or even preventing it from operating normally.

[0006] (2) Data difference detection and quantification

[0007] Inaccurate difference identification: Existing data auditing techniques often lack effective models and algorithms when identifying data differences between different tables. They are unable to accurately capture subtle differences in data features, leading to biases in the judgment of whether data comes from different distributions and failing to detect potential data problems in a timely manner.

[0008] Difficulty in quantifying the degree of difference: The lack of unified and effective quantitative indicators to measure the degree of difference between two tables makes it difficult for users to intuitively understand the significance of data changes. Some technologies can only provide simple statistical information and cannot deeply analyze the nature and impact of data differences, which is not conducive to data managers making accurate decisions.

[0009] (3) Feature importance analysis and visualization

[0010] Weak ability to identify key features: Existing technologies lack scientific and comprehensive methods for analyzing the importance of data features, making it difficult to accurately identify features that play a crucial role in distinguishing data from different tables. This prevents data managers from effectively governing and optimizing data, reducing the efficiency and quality of data processing.

[0011] Insufficient visualization of analysis results: While some existing technologies can perform feature analysis, they lack effective visualization methods, failing to present the results to users in an intuitive way. Complex data and analysis results are difficult for users to understand and utilize, limiting the effectiveness of data auditing technology in actual business operations.

[0012] (4) Audit report and problem identification

[0013] Incomplete integration of audit information: Existing audit reports often only contain partial data information, such as simple statistical indicators or a small amount of feature information. They cannot integrate comprehensive audit information such as model evaluation indicators, field importance, and feature contribution, which makes it impossible for data managers to obtain complete audit information and affects the accuracy and scientific nature of decision-making.

[0014] Precise problem localization is difficult: After discovering data discrepancies, existing technologies struggle to trace back to specific data reference points based on feature names, making it impossible to accurately pinpoint the specific content of the data differences. This makes it difficult for data managers to analyze the root causes of problems and hinders their ability to take timely and targeted measures for data governance and business adjustments. Summary of the Invention

[0015] This invention aims to solve the challenges of discrepancy detection, feature analysis, and problem localization in database table data during practical business scenarios. To this end, this invention provides a data table auditing method, system, and product for databases. It utilizes Kernel Mean Embedding (KME) for general table feature extraction, combines the Nyström method to reduce computational complexity, employs the XGBoost model to identify table data feature patterns, quantifies the degree of discrepancy using the AUC metric, utilizes SHAP for feature importance analysis, visualizes the results, and ultimately generates an audit report containing key information, achieving comprehensive, efficient, and accurate analysis of database table data.

[0016] This invention provides a method for auditing data tables in a database, and the technical solution adopted is as follows: including the following steps:

[0017] S1: Obtain the configuration file;

[0018] S2: Load the reference baseline table and the data table to be audited from the database according to the configuration file;

[0019] S3: Based on the reference benchmark table, initialize the feature extractor using KME technology and create a field processor for each field of the reference benchmark table;

[0020] S4: Use the field processor to perform feature transformation on the reference benchmark table and the data table to be audited respectively to obtain feature vectors;

[0021] S5: Using the feature vectors of the reference benchmark table and the data table to be audited, train the XGBoost model and test the trained XGBoost model to obtain the AUC score of ROC.

[0022] S6: Perform SHAP analysis on the trained XGBoost model to obtain the SHAP values ​​of the features;

[0023] S7: Obtain the audit report based on the feature vector, AUC score, and SHAP value.

[0024] Furthermore, in step S3, a kernel function is selected based on the field type to create a field processor. The field types include numeric and quantifier types.

[0025] Numerical kernel functions:

[0026]

[0027] in, For kernel function, As the first data point, For the second data point, This is the bandwidth parameter of the kernel function;

[0028] Type-specific kernel functions:

[0029] .

[0030] Furthermore, in step S3, the field processor creation process is as follows:

[0031] Identify field types;

[0032] Assign kernel functions based on field types;

[0033] A field processor is created by sampling reference points from the reference benchmark table as the benchmark for kernel function calculation.

[0034] Furthermore, in step S4, when performing feature transformation, the probability distribution is mapped to the regenerating kernel Hilbert space using the KME method, or alternatively, the Nyström method can be used to approximate the calculation using anchor points.

[0035] Furthermore, in step S5,

[0036] The two feature vectors are merged and labeled as 0 or 1 according to the data source, and then divided into training and test sets;

[0037] The XGBoost model is trained using the training set, and the objective function of the XGBoost model is... for:

[0038]

[0039] in, It is the loss function, which measures the i-th predicted value of the model. With the i-th true value The differences between them It's the amount of data. It is a regularization term. It is the number of decision trees;

[0040] The AUC score of the trained XGBoost model is obtained using the test set.

[0041] Furthermore, in step S6,

[0042] The formula for calculating the SHAP value is:

[0043]

[0044] in, It is a feature SHAP value, It is a feature subset. It is the set of all features. It is a set The set of features j excluded from the middle. It is a feature subset Corresponding characteristic value, It is a feature subset and characteristics The corresponding characteristic value.

[0045] Furthermore, in step S7, the AUC score, importance of each field, SHAP value, feature vector, feature name list, and sample data from the reference benchmark table and the data to be audited are compiled into an audit report. The importance of a field is obtained by aggregating the SHAP values ​​of all features corresponding to that field.

[0046] Furthermore, it also includes: S8: Based on the audit report, perform visualization processing to obtain visualization results, and perform field analysis to obtain diagnostic results.

[0047] The present invention also provides a data table auditing system for a database, the technical solution of which is as follows: including: a configuration acquisition module, a connection sampling module, a feature extraction module, a model training module, a feature interpretation module, and a report generation module;

[0048] The configuration retrieval module is used to retrieve configuration files;

[0049] The connection sampling module is used to load the reference benchmark table and the data table to be audited from the database according to the configuration file;

[0050] The feature extraction module is used to initialize the feature extractor based on the reference benchmark table and KME technology, and to create a field processor for each field of the reference benchmark table; the field processors are used to perform feature transformation on the reference benchmark table and the data table to be audited respectively to obtain feature vectors.

[0051] The model training module is used to train the XGBoost model using the feature vectors of the reference benchmark table and the data table to be audited, and to test the trained XGBoost model to obtain the AUC score of ROC.

[0052] The feature interpretation module is used to perform SHAP analysis on the trained XGBoost model to obtain the SHAP values ​​of the features.

[0053] The report generation module is used to generate audit reports based on feature vectors, AUC scores, and SHAP values.

[0054] The present invention also provides a computer program product, including a computer program that, when executed by a processor, implements the above-described method for auditing data tables in a database.

[0055] The above-described one or more technical solutions in the embodiments of the present invention have at least one of the following technical effects:

[0056] 1. This invention develops a general table feature extraction method. Utilizing KME technology, it transforms input table data into feature vectors, supporting the processing of both numerical and categorical fields, achieving unified data normalization, and providing a standardized data foundation for subsequent analysis. During data transformation, this invention selectively introduces the Nyström method based on the data characteristics, approximating the kernel matrix by randomly selecting anchor points, thereby reducing the computational complexity of the feature transformation process and improving the system's efficiency when processing large-scale data.

[0057] 2. This invention achieves the effect of identifying feature patterns of different tables through XGBoost model training. This invention utilizes the XGBoost binary classification model to learn the feature patterns of data from two tables, accurately capturing the differences in features between data from different tables, determining whether the data comes from different distributions, and achieving effective detection of differences in database table data.

[0058] 3. This invention uses AUC as an evaluation metric for the XGBoost model to quantify the degree of difference between the data in two tables, providing users with an intuitive measure of data difference and facilitating the assessment of the significance of data changes.

[0059] 4. This invention uses the SHAP method to perform feature importance analysis, calculates the contribution of each feature to the output of the XGBoost model, identifies key features, and aggregates SHAP values ​​according to the original fields to determine the importance of each field, providing a basis for data governance and business decisions.

[0060] 5. This invention visualizes the analysis results by drawing field importance charts and detailed SHAP feature importance charts, intuitively presenting the contributions of important fields and features, making it easy for users to quickly understand data characteristics and differences.

[0061] 6. This invention generates detailed audit reports, including model AUC, field importance, SHAP values, and a list of feature names, providing comprehensive audit information for data managers, analysts, and decision-makers, supporting subsequent data analysis and decision-making. This invention enables backtracking to specific data reference points based on feature names, accurately pinpointing the specific content of data discrepancies, helping users deeply analyze the root causes of problems, and take targeted measures for data governance and business adjustments.

[0062] Additional aspects and advantages of the invention will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. Attached Figure Description

[0063] To more clearly illustrate the technical solutions in this invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0064] Figure 1 This is a flowchart of the method provided by the present invention.

[0065] Figure 2 This is the field importance graph provided by the present invention.

[0066] Figure 3 This is a detailed SHAP feature importance diagram provided by the present invention.

[0067] Figure 4 This is a structural block diagram of the system provided by the present invention.

[0068] Figure label:

[0069] 1. Configuration Acquisition Module; 2. Connection Sampling Module; 3. Feature Extraction Module; 4. Model Training Module; 5. Feature Interpretation Module; 6. Report Generation Module; 7. Visual Diagnosis Module. Detailed Implementation

[0070] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention. The following embodiments are used to illustrate this invention but should not be used to limit the scope of this invention.

[0071] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., refer to specific features, structures, or characteristics described in connection with that embodiment or example, which are included in at least one embodiment or example of the present invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, or characteristics described may be combined in any suitable manner in one or more embodiments or examples. Moreover, without contradiction, those skilled in the art can combine and integrate the different embodiments or examples described in this specification, as well as the features of different embodiments or examples.

[0072] The following is combined Figures 1 to 4 The present invention will be further described in detail below, including a method, system, and product for auditing data tables in a database:

[0073] In this embodiment, as Figure 1 As shown, a method, system, and product for auditing data tables in a database are provided, including the following steps:

[0074] S1: Get the configuration file.

[0075] The configuration file contains database connection parameters, processing parameters, and comparison objects.

[0076] Database connection parameters include host (host IP), port (port), user (username), password (password), and database (database name).

[0077] Comparison objects: the reference baseline table (table_a) and the data table to be audited (table_b).

[0078] Processing parameters: embed_dim, sample_size, use_nystrom, and number of anchor points.

[0079] `embed_dim` is the feature embedding dimension, which defaults to 64. `embed_dim` ensures that the feature vector length for each field is consistent. If the length of the calculated kernel function value list is less than `embed_dim`, it will be padded with 0s; if it is greater than `embed_dim`, the first `embed_dim` elements will be truncated.

[0080] `sample_size` is the sampling size, representing the upper limit of the number of data rows sampled from the database table. When the number of rows in the table exceeds 10,000, the data will be sampled. In this embodiment, the sampling frequency is 0.1%.

[0081] `use_nystrom` is a parameter that determines whether the Nyström method is used; its value determines whether the Nyström method is used.

[0082] S2: Based on the configuration file, load the reference baseline table and the data table to be audited from the database.

[0083] First, the configuration parameters are parsed: database connection and processing parameters are read from conf.ini (configuration file). If file reading fails, the program exits. The `connect_db` method of the TableAuditor class is used to attempt a database connection. If the connection fails, the program outputs an error message and terminates. Next, data is loaded: data for `table_a` and `table_b` is loaded from the database based on the table names specified in the comparison object in the configuration file, using the database connection. If the data volume is too large, sampling is performed. Generally, if the number of rows in a table exceeds 10,000, data is sampled and loaded at a sampling frequency of 0.1%.

[0084] S3: Based on the reference benchmark table, initialize the feature extractor using KME (Kernel Mean Embedding) technology, and create a field processor for each field (column) of the reference benchmark table.

[0085] The specific process is as follows: In the audit method of the TableAuditor class, a KME_FeatureExtractor (KME feature extractor) object is created. This object receives the reference benchmark table, embed_dim, use_nystrom, and the number of anchor points as parameters.

[0086] In the `init` method of `KME_FeatureExtractor`, a field processor is created for each field, and a different kernel function is selected based on the field type. Field types include numeric and fractional types.

[0087] Numerical kernel functions:

[0088]

[0089] in, For kernel function, As the first data point, For the second data point, is the bandwidth parameter of the kernel function, and is the standard deviation of the data.

[0090] Type-specific kernel functions:

[0091] .

[0092] The core function of the KME feature extractor is to establish a baseline feature space. Subsequently, data differences are detected by comparing the distribution differences between table_a and table_b in this space. table_a is a reference baseline table, which is usually historical data that has been verified in actual business operations. All kernel function parameters and reference points are calculated based on the data distribution of table_a. table_b is transformed using the same set of rules to ensure the consistency of the feature space.

[0093] Different field handlers are created based on the field type. For each field in table_a, a dedicated field handler is automatically created, and its processing logic is as follows:

[0094] Identify field types: Each column is divided into numeric and categorical types.

[0095] Based on the different field types, assign the above two different kernel functions.

[0096] To improve computational efficiency, a field processor is created by sampling reference points from table_a as the basis for kernel function calculation. For numeric data, several data points are randomly sampled from the column in table_a, such as selecting 100 typical age values ​​from the age column. For categorical data, all unique values ​​in the column are collected, such as using [male, female] as the reference points for the gender column.

[0097] During initialization, the features of table_a are learned first. For example, if the age is concentrated between 20-40 years old (σ≈5) and the gender distribution is 60% male and 40% female, a numeric field processor is created for the age column, and a categorical field processor is created for the gender column using σ=5 from table_a. In subsequent steps, when transforming table_b, all its age values ​​are compared with the reference age in table_a, and all its gender values ​​are compared with the gender category in table_a. Finally, the XGBoost model is used to determine whether the differences in the transformed features are significant. S4: The field processors are used to perform feature transformation on the reference table and the data table to be audited, respectively, to obtain feature vectors.

[0098] The `transform` method of the `KME_FeatureExtractor` class converts the input data into features. If `use_nystrom` is True, the Nyström method is used, and in this case, the `nystrom_transform` method is used for approximate computation; otherwise, the original KME feature transformation logic is used. The Nyström method is introduced to reduce the high computational complexity of traditional kernel methods; its computational complexity can be reduced from O(...). ) decreases to O(n ).

[0099] The probability distribution is mapped to the reproducing kernel Hilbert space (RKHS) using the KME method:

[0100]

[0101] in, The vector representation of data points mapped to the reproducing kernel Hilbert space serves as the feature vector. For input data points, As the first reference point, As the second reference point, As the nth reference point, for and The kernel function value.

[0102] The input data points can be data from either table_a or table_b. When transforming table_a, It is a row of data in table_a; when converting table_b, It refers to a row in table_b. The data in both tables is transformed using the same set of rules to ensure consistency in the feature space.

[0103] , ,..., These are reference points, derived solely from table_a, and are typical values ​​sampled from each field of table_a during the initialization phase. For example, if the reference points for the age column in table_a are [25, 30, 45], then the age values ​​of all data (regardless of whether they come from table_a or table_b) will be used to calculate kernel function values ​​with these three reference points.

[0104] The kernel function value is used to measure the similarity between input data points and reference points; the larger the value, the more similar they are. Numerical type: calculated using a Gaussian kernel, the closer the values ​​are, the higher the similarity. Categorical type: 1.0 for a perfect match, 0.2 for a no-match.

[0105] When use_nystrom=True is enabled, the complexity is reduced by approximating calculations using anchor points. Nystrom method:

[0106]

[0107] in, The kernel matrix between the test data points and the anchor points. For the first test data point, For the second test data point, For the nth test data point, For the first anchor point, For the second anchor point, Let m be the anchor point. for and The kernel function value. , ,..., It comes from table_a.

[0108] Singular Value Decomposition (SVD): ,in, It is the kernel matrix between anchor points. It is a left singular matrix. It is a singular value matrix. It is the transpose of a right singular matrix.

[0109] The kernel matrix is ​​approximated and reconstructed to obtain an approximate full kernel matrix, which is then used as the eigenvector.

[0110] S5: Use the feature vectors of the reference benchmark table and the data table to be audited to train the XGBoost model, and test the trained XGBoost model to obtain the AUC score of ROC.

[0111] The two feature vectors obtained in step S4 are merged and labeled as 0 or 1 according to the data source, where 0 indicates that the data comes from table_a and 1 indicates that the data comes from table_b. Then, the dataset is divided into training and test sets. Using the training set, an XGBoost model (binary classification model) is trained using the XGBoost (gradient boosting decision tree) library. The performance of this model is evaluated by calculating the AUC (Area Under the ROCCurve) score of the ROC (Receiver Operating Characteristic Curve) on the test set, thus obtaining the AUC score of the trained XGBoost model.

[0112] The objective function of the XGBoost model for:

[0113]

[0114] in, It is the loss function, which measures the i-th predicted value of the model. With the i-th true value The differences between them; It is a regularization term used to control the complexity of the decision tree; It is the number of decision trees; This refers to the amount of data. AUC calculation: The AUC score of the model is calculated using the `roc_auc_score` function (using the `sklearn.metrics` module to calculate the area under the ROC curve), which measures the model's classification performance. The AUC value ranges from 0 to 1; the closer the value is to 1, the better the model's classification performance.

[0115] In the feature vectors of the reference benchmark table and the data table to be audited, each dimension value of each feature vector is a similarity score calculated using a kernel function, with a default dimension of 64. For example: for numerical fields, the Gaussian kernel similarity with the reference point in table_a is 0.8, such as age 30 having a similarity of 0.8 with reference point 25; for categorical fields, the matching score with the reference point in table_a is 1.0, such as gender (male) having a score of 1.0 with the reference point (male).

[0116] The input to the XGBoost model is (X, Y), where X is a two-dimensional array formed by vertically concatenating the feature vectors of table_a and table_b, Y is a label vector used to tell the model which class (source) each sample belongs to, and Y is a one-dimensional array composed of 0s and 1s with a length equal to the total number of samples.

[0117] True value Representing the true label, it comes from Y; the data in table_a is 0, and the data in table_b is 1. Predicted value. Represents the predicted probability, which is the model's prediction result for the feature vector. It is the probability feature vector output by the model that belongs to table_b. It is the calculated loss value, which is used to measure the difference between the prediction and the actual source.

[0118] when =1 (data in table_b) When the value is very small, the loss value will increase. For example, if a piece of data comes from table_b( =1), the model predicts the probability that it belongs to table_b. =0.9, then the loss value is small (the prediction is accurate); if a certain data comes from table_a( =0), the model predicts the probability that it belongs to table_b. If the value is 0.8, the loss value is relatively large (prediction error).

[0119] S6: Perform SHAP analysis on the trained XGBoost model to obtain the SHAP values ​​of the features.

[0120] Use the shap.TreeExplainer tool from the shap library to perform SHAP (SHapley Additive exPlanations) analysis on the trained model to determine the contribution of each feature to the model's predictions.

[0121] The formula for calculating the SHAP value is:

[0122]

[0123] in, It is a feature SHAP value, It is a feature subset. It is the set of all features. It is a set The set of features j excluded from the middle. It is a feature subset Corresponding characteristic value, It is a feature subset and characteristics The corresponding characteristic value.

[0124] The essence is to use only the features in a subset S, i.e., the model's predicted values ​​for the samples. The calculation steps are as follows: Feature subset selection and transformation: For a given feature subset S, such as S={age, gender}, extract the features from subset S. Model prediction based on subset features: Using the selected subset features, call the predict method of the XGBoost model to obtain the predicted values, and then calculate the original prediction margin value of the model (a non-probability value, which is easier to calculate the feature contribution difference).

[0125] For the same batch of samples, the differences between different subsets S directly reflect the impact of the newly added feature j on the prediction. The feature vector corresponds to the formula in... .

[0126] Since traversing all subsets S calculates The complexity is extremely high (exponential). The shap library uses a feature vector approximation algorithm and a model tree structure to optimize the feature vectors to accelerate the process.

[0127] S7: Obtain the audit report based on the feature vector, AUC score, and SHAP value.

[0128] An audit report was compiled from the model's AUC score, the importance of each field, the SHAP value, feature vectors, the list of feature names, and sample data from both tables. The importance of a field was calculated by aggregating the SHAP values ​​of all features corresponding to that field. The audit report clearly identifies the fields that significantly impact the differences between the two tables.

[0129] S8: Based on the audit report, perform visualization processing to obtain visualization results, and perform field analysis to obtain diagnostic results.

[0130] Visualization Results: Based on the information in the audit report, a field importance chart and a detailed SHAP feature importance chart are generated. This example uses the `visualize_results` method of the `TableAuditor` class to visualize the field importance and detailed SHAP feature importance, and saves it as an image. Generally, the field importance chart shows all fields. The detailed SHAP feature importance chart shows the top 20 features.

[0131] Diagnostic Results: Based on the audit report and visualization results, key features are specified, and then the analysis traces back to the reference point value of the specific data. This reference point value represents the specific value of the feature in the original data. For a specified key feature, such as lo_shipmode_32 (the 32nd feature of the lo_shipmode field), the reference point value corresponding to its field and dimension is analyzed, and the data distribution near that reference point is examined. This embodiment uses the `diagnose_important_feature` method of the `TableAuditor` class to diagnose the specified key feature dimension, analyzing its reference point and data distribution. Mapping features to reference points in the specific data facilitates accurate identification of differences caused by specific field values.

[0132] This embodiment illustrates the implementation process of the method through the following content.

[0133] Hardware and software environment: CPU: 16 cores; Memory: 128GB; Disk: 2TB; Network card: 10 Gigabit; Operating system: CentOS 7.6; Database version: GBase8aMPPClusterV95; Number of nodes: 3.

[0134] Test data: Data tables: lineorder and lineorder_m; both contain 60 million records and 17 fields. lineorder serves as the baseline table, and lineorder_m is the table to be audited. The two tables have identical structures, and lineorder has no duplicate values. lineorder_m is generated by copying data from lineorder, and the value "TRUCK" in the lo_shipmode field of lineorder_m is updated to "MAIL". For example: update lineorder_m set lo_shipmode='MAIL'where lo_shipmode='TRUCK'.

[0135] The configuration file conf.ini contains the following:

[0136] [database]

[0137] host=192.168.1.5

[0138] user=gbase

[0139] password=gbase20110531

[0140] database=test

[0141] port=5258

[0142] [tables]

[0143] table_a=lineorder

[0144] table_b=lineorder_m

[0145] [parameters]

[0146] embed_dim=64

[0147] sample_size=100000.

[0148] The audit report contains the following excerpt:

[0149] Model AUC score: 0.7329;

[0150] The most important field:

[0151] lo_shipmode:0.0099

[0152] lo_suppkey:0.0064

[0153] lo_ordtotalprice:0.0059

[0154] lo_custkey:0.0058

[0155] lo_supplycost:0.0057

[0156] lo_partkey:0.0056

[0157] lo_revenue:0.0045

[0158] lo_extendedprice:0.0040

[0159] lo_quantity:0.0035

[0160] lo_orderkey:0.0029

[0161] lo_commitdate:0.0021

[0162] lo_orderdate:0.0016

[0163] lo_linenumber:0.0013

[0164] lo_tax:0.0013

[0165] lo_discount:0.0012

[0166] lo_orderpriority:0.0007

[0167] lo_shippriority:0.0000.

[0168] The most important fields include the names of 17 fields, such as lo_shipmode, lo_suppkey, lo_ordtotalprice, and their corresponding SHAP values. For example, the SHAP value of the lo_shipmode field is 0.0099.

[0169] Visualization results are generated based on the audit report. Figure 2 This is a field importance chart, showing the SHAP values ​​of all 17 fields. Figure 2 As can be seen, the lo_shipmode field has the greatest impact on the differences between tables. Figure 3 A detailed SHAP feature importance plot is provided, showing the SHAP values ​​of the top 20 features. Figure 3As can be seen, the lo_shipmode_1 feature has the highest influence. lo_shipmode_1 represents the first feature of the lo_shipmode field. Similarly, lo_suppkey_26 represents the 26th feature of the lo_suppkey field.

[0170] Lo_shipmode_1 is designated as an important feature. A diagnosis is performed on feature lo_shipmode_1, and the diagnostic results are as follows:

[0171] Diagnostic report:

[0172] Field: lo_shipmode

[0173] Key dimension: 1

[0174] Reference point value: TRUCK.

[0175] The above experiments have verified that this method can achieve the purpose of auditing data tables. Users can accurately locate and analyze data with audit problems based on visualization and diagnostic results.

[0176] In this embodiment, as Figure 4 As shown, a data table auditing system for databases is also provided, and the technical solution adopted is as follows: including: configuration acquisition module 1, connection sampling module 2, feature extraction module 3, model training module 4, feature interpretation module 5, report generation module 6, and visualization diagnosis module 7.

[0177] The configuration retrieval module is used to retrieve configuration files;

[0178] The connection sampling module is used to load the reference benchmark table and the data table to be audited from the database according to the configuration file;

[0179] The feature extraction module is used to initialize the feature extractor based on the reference benchmark table and KME technology, and to create a field processor for each field of the reference benchmark table; the field processors are used to perform feature transformation on the reference benchmark table and the data table to be audited respectively to obtain feature vectors.

[0180] The model training module is used to train the XGBoost model using the feature vectors of the reference benchmark table and the data table to be audited, and to test the trained XGBoost model to obtain the AUC score of ROC.

[0181] The feature interpretation module is used to perform SHAP analysis on the trained XGBoost model to obtain the SHAP values ​​of the features.

[0182] The report generation module is used to generate an audit report based on the feature vector, AUC score, and SHAP value.

[0183] The visualization diagnostic module is used to perform visualization processing based on audit reports, obtain visualization results, and perform field analysis to obtain diagnostic results.

[0184] The present invention also provides a computer program product, including a computer program that, when executed by a processor, implements the aforementioned method for auditing data tables in a database. This auditing method includes the following steps:

[0185] S1: Obtain the configuration file;

[0186] S2: Load the reference baseline table and the data table to be audited from the database according to the configuration file;

[0187] S3: Based on the reference benchmark table, initialize the feature extractor using KME technology and create a field processor for each field of the reference benchmark table;

[0188] S4: Use the field processor to perform feature transformation on the reference benchmark table and the data table to be audited respectively to obtain feature vectors;

[0189] S5: Using the feature vectors of the reference benchmark table and the data table to be audited, train the XGBoost model and test the trained XGBoost model to obtain the AUC score of ROC.

[0190] S6: Perform SHAP analysis on the trained XGBoost model to obtain the SHAP values ​​of the features;

[0191] S7: Obtain the audit report based on the feature vector, AUC score, and SHAP value.

[0192] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for auditing a data table for a database, the method comprising: The method comprises the following steps: S1: obtaining a configuration file; S2: loading a reference benchmark table and a to-be-audited data table from a database according to the configuration file; S3: initializing a feature extractor based on a kernel mean embedding technology according to the reference benchmark table, and creating a field processor for each field of the reference benchmark table; S4: performing feature conversion on the reference benchmark table and the to-be-audited data table respectively by using the field processor to obtain feature vectors; S5: training an XGBoost model by using the feature vectors of the reference benchmark table and the to-be-audited data table, and testing the trained XGBoost model to obtain an AUC score of an ROC; S6: performing SHAP analysis on the trained XGBoost model to obtain SHAP values of features; S7: obtaining an audit report according to the feature vectors, the AUC score, and the SHAP values.

2. The method for auditing data tables for a database of claim 1, wherein, In step S3, a kernel function is selected according to a field type, and a field processor is created, the field type including a numerical type and a categorical type; The kernel function of the numerical type is: wherein, is a kernel function, is a first data point, is a second data point, is a bandwidth parameter of the kernel function; The kernel function of the categorical type is: 。 3. A method for auditing data tables for a database as claimed in claim 1 or 2, wherein, In step S3, the field processor creation process is: identifying a field type; allocating a kernel function according to the field type; sampling a reference point from the reference benchmark table as a kernel function calculation benchmark to create the field processor.

4. The method for auditing data tables for a database of claim 1, wherein, In step S4, when performing feature conversion, a probability distribution is mapped to a reproducing kernel Hilbert space by using a KME method, or an anchor point is approximated by using a Nyström method.

5. The method for auditing data tables for a database of claim 1, wherein, In step S5, the two feature vectors are combined, and are marked as 0 or 1 according to data sources, and are then divided into a training set and a test set; Training the XGBoost model using the training set, the objective function of the XGBoost model is: minimize: in, It is the loss function, which measures the i-th predicted value of the model. With the i-th true value The differences between them It's the amount of data. It is a regularization term. It is the number of decision trees; the AUC score of the trained XGBoost model is obtained by using the test set.

6. The method for auditing data tables for a database of claim 1, wherein, In step S6, the calculation formula of the SHAP value is: wherein, is the SHAP value of feature is the subset of features is the set of all features is the set excluding feature j in the set is the subset of features corresponding to the feature value is the subset of features and feature corresponding to the feature value.

7. The method for auditing data tables for a database of claim 1, wherein, In step S7, the AUC score, the importance of each field, the SHAP value, the feature vector, the feature name list, and sample data of the reference benchmark table and the to-be-audited data table are arranged into an audit report, wherein the importance of the field is obtained by aggregating the SHAP values of all features corresponding to the field.

8. The method for auditing data tables for a database of claim 1, wherein, S8: performing visual processing according to the audit report to obtain a visual result, and performing field analysis to obtain a diagnosis result.

9. A data table auditing system for a database, characterized in that, A data table auditing method for a database is used to perform the method according to any one of claims 1 to 8, comprising: a configuration obtaining module, a connection sampling module, a feature extraction module, a model training module, a feature interpretation module, and a report generation module; The configuration obtaining module is used to obtain a configuration file; The connection sampling module is used to load a reference benchmark table and a to-be-audited data table from a database according to the configuration file; The feature extraction module is used to initialize a feature extractor based on a kernel mean embedding technology according to the reference benchmark table, create a field processor for each field of the reference benchmark table, and perform feature conversion on the reference benchmark table and the to-be-audited data table respectively by using the field processor to obtain feature vectors; The model training module is used to train an XGBoost model by using the feature vectors of the reference benchmark table and the to-be-audited data table, test the trained XGBoost model, and obtain an AUC score of an ROC; The feature explanation module is configured to perform SHAP analysis on the trained XGBoost model to obtain SHAP values of the features. The report generation module is configured to obtain an audit report according to the feature vector, the AUC score and the SHAP values.

10. A computer program product comprising a computer program, characterized in that, The computer program is executed by the processor to implement the data table auditing method for the database according to any one of claims 1 to 8.

Images