A data processing method and system

Abstract

The application provides a data processing method and system. When a task request exists in a sqlite database, a task execution module in the data processing system scans the task request in the sqlite database and submits the task request to an Executor tool. The Executor tool loads and prepares data in the task request, performs feature engineering and wide table splicing, and submits the data to a Spark server. The Spark server performs data loading and preparation to obtain a plurality of data tables. Feature engineering is performed based on each data table to obtain independent variable binning and encoding mapping tables for each data table. Wide table splicing is performed based on the plurality of independent variable binning and encoding mapping tables, and a target splicing table and a wide table splicing completion notification are sent to the Executor tool. The Executor tool receives the splicing completion notification, sends the target splicing table and model construction to a server corresponding to the task type of the Executor tool for model construction, and obtains a target model.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and more specifically, to a data processing method and system. Background Technology

[0002] With the continuous development of computer technology, industries such as the Internet, banking, securities, and insurance have widely used computer technology for big data analysis and model building, such as the CRISP-DM machine learning data mining standard process developed using computer technology.

[0003] In the CRISP-DM machine learning data mining standard workflow, the data understanding and preparation stages involve loading and preparing dozens or even hundreds of original data tables, as well as feature engineering and wide table processing for modeling. These processes require modelers to be very familiar with the business meaning and metadata of the original data, and to be proficient in SQL programming syntax, able to write SQL code that meets both functional and performance requirements. The remaining stages include model building, but this process also requires modelers to understand the mathematical principles of various modeling algorithms, be proficient in various programming syntaxes and the interface calling specifications of various modeling algorithms, and be able to implement model building end-to-end using the corresponding programming language, making it a high-barrier-to-entry process. Summary of the Invention

[0004] In view of this, the present invention provides a data processing method and system that can automatically realize data loading and preparation, feature engineering, wide table splicing, and model building without human intervention, thereby reducing the threshold for use.

[0005] The first aspect of this invention discloses a data processing system, which includes at least an SQLite database, a task execution service module, an Executor tool, and at least one server, wherein the at least one server includes a Spark server and a Python server.

[0006] The task execution module is used to retrieve the task request from the SQLite database when a task request is detected in the SQLite database, and submit the task request to the Executor tool; wherein the task request is used to execute multiple tasks, and the multiple tasks include at least: data loading and preparation, feature engineering, wide table concatenation, and model building;

[0007] The Executor tool is used to load and prepare the data, perform feature engineering, and concatenate the wide table to submit it to the Spark server.

[0008] The Spark server is used to perform data loading and preparation to obtain multiple data tables; perform feature engineering based on each data table to obtain the independent variable binning and encoding mapping table corresponding to each data table; perform wide table concatenation based on the multiple independent variable binning and encoding mapping tables to obtain the target concatenation table, and send the target concatenation table and wide table concatenation completion notification to the Executor tool;

[0009] The Executor tool is used to determine the task type corresponding to the model building when it receives the splicing completion notification, and send the target splicing table and the model building to the server corresponding to its task type to build the model using the target splicing table to obtain the target model;

[0010] The task type is either a standalone version or a cluster version; the model building for the standalone version is submitted to the Python server; the model building for the cluster version is submitted to the Spark server.

[0011] Optionally, the Spark server that performs data loading and preparation to obtain multiple data tables is specifically used for:

[0012] Create the HDFS data directory corresponding to the task described above;

[0013] Establish a connection with the GP database and retrieve multiple data tables from the GP database that match the HDFS data directory;

[0014] Retrieve the corresponding header fields from each of the data tables, and store each of the data tables and its corresponding header fields in the HDFS database.

[0015] Optionally, the Spark server that performs feature engineering based on each of the data tables to obtain the independent variable binning and encoding mapping table corresponding to each data table is specifically used for:

[0016] Retrieve the plurality of data tables from the HDFS database;

[0017] Dimensionality reduction is performed on each of the data tables to obtain the target independent variable corresponding to each data table;

[0018] The multiple target independent variables are binned to obtain the optimal variable binning.

[0019] Based on the optimal variable binning and the encoding of each target independent variable, an independent variable binning and encoding mapping table is generated.

[0020] Optionally, the Spark server that performs dimensionality reduction processing on each of the data tables to obtain the target independent variable corresponding to each data table is specifically used for:

[0021] Each of the data tables is clustered to obtain multiple variable groups corresponding to each data table. The independent variables within each variable group are highly correlated, while the independent variables between the variable groups are highly uncorrelated.

[0022] Based on the rule of minimizing statistics, a target independent variable is selected from each of the variable groups.

[0023] Optionally, the Spark server that performs dimensionality reduction processing on each of the data tables to obtain the target independent variable corresponding to each data table is specifically used for:

[0024] For each of the data tables, a preset algorithm is used to calculate the statistic for each independent variable in the data table;

[0025] The independent variables whose statistical values ​​are less than a preset threshold are removed to obtain the target independent variables corresponding to the data table.

[0026] Optionally, the Spark server that performs wide table concatenation based on multiple independent variable binning and encoding mapping tables to obtain the target concatenated table is specifically used for:

[0027] Obtain the table name of each independent variable binning and encoding mapping table;

[0028] Each of the table names is assembled into SQL code, and the SQL code is processed to obtain an AST (Abstract Syntax Tree).

[0029] The target code is obtained by parsing the AST (Abstract Syntax Tree).

[0030] If the number of table names in the independent variable binning and encoding mapping table in the target code is greater than the target threshold, and the target filtering condition is empty, the target independent variable binning and encoding mapping table corresponding to each table name are sequentially concatenated to obtain the target concatenation table.

[0031] Optionally, the Spark server is also used for:

[0032] If the number of bins for independent variables and the number of table names in the encoding mapping table in the target code is greater than the target threshold, and the target filtering condition is not empty, the AST abstract syntax tree is parsed using the BFS algorithm, and the obtained data is stored in a preset data structure.

[0033] Traverse the data in the preset data structure and obtain the cst_id column from the data in the preset data structure;

[0034] The cst_id column is concatenated with other tables to obtain the target concatenated table, wherein the other tables are the data tables selected by the user in the web browser interface.

[0035] Optionally, if the task type for model building is a standalone version, the Python server that uses the target concatenation table for model building is specifically used for:

[0036] The target concatenation table is stored in a local database, and a local directory corresponding to the target concatenation table is created in the local database;

[0037] Obtain the algorithm parameters corresponding to the standalone version of the algorithm, and train the algorithm according to the target splicing table and the algorithm parameters to obtain the target model;

[0038] The target model is stored in the storage space corresponding to the local directory.

[0039] Optionally, if the task type for model building is a cluster version, the Spark server that uses the target concatenation table for model building is specifically used for:

[0040] Create the cluster directory corresponding to the target concatenation table;

[0041] Obtain the algorithm parameters corresponding to the cluster version of the algorithm, and train the algorithm according to the target splicing table and the algorithm parameters to obtain the target model;

[0042] The target model is stored in the storage space corresponding to the cluster directory.

[0043] A second aspect of this invention discloses a data processing method applied to a data processing system, the data processing system including at least an SQLite database, a task execution service module, an Executor tool, and at least one server, the at least one server including a Spark server and a Python server, the method comprising:

[0044] When the task execution module detects a task request in the SQLite database, it retrieves the task request from the SQLite database and submits the task request to the Executor tool; wherein, the task request is used to execute multiple tasks, and the multiple tasks include at least: data loading and preparation, feature engineering, wide table concatenation, and model building;

[0045] The Executor tool loads and prepares the data, performs feature engineering, and concatenates the wide table before submitting it to the Spark server.

[0046] The Spark server performs data loading and preparation to obtain multiple data tables; based on each data table, it performs feature engineering to obtain the independent variable binning and encoding mapping table corresponding to each data table; based on the multiple independent variable binning and encoding mapping tables, it performs wide table concatenation to obtain the target concatenation table, and sends the target concatenation table and wide table concatenation completion notification to the Executor tool.

[0047] When the Executor tool receives the splicing completion notification, it determines the task type corresponding to the model construction and sends the target splicing table and the model construction to the server corresponding to its task type to perform model construction using the target splicing table to obtain the target model;

[0048] The task type is either a standalone version or a cluster version; the model building for the standalone version is submitted to the Python server; the model building for the cluster version is submitted to the Spark server.

[0049] This invention provides a data processing method and system. The data processing system includes at least an SQLite database, a task execution service module, an Executor tool, and at least one server, including a Spark server and a Python server. When the task execution module detects a task request in the SQLite database, it retrieves the task request from the SQLite database and submits it to the Executor tool. The task request is used to execute multiple tasks, which at least include: data loading and preparation, feature engineering, wide table concatenation, and model building. The Executor tool submits the data loading and preparation, feature engineering, and wide table concatenation to the Spark server. The Spark server performs the data loading and preparation... The process involves loading and preparing multiple data tables; performing feature engineering on each data table to obtain a binning and encoding mapping table for each table; performing wide table concatenation based on the multiple binning and encoding mapping tables to obtain a target concatenated table, and sending a notification of target concatenation and wide table concatenation completion to the Executor tool; upon receiving the concatenation completion notification, the Executor tool determines the task type corresponding to model building and sends the target concatenation table and model building to the server corresponding to its task type to build the target model using the target concatenation table; the task type can be either a standalone version or a cluster version; the model building for the standalone version is submitted to the Python server; the model building for the cluster version is submitted to the Spark server. This invention provides a technical solution that automates data loading and preparation, feature engineering, and wide table concatenation based on a pre-defined HDFS data directory, and builds a model based on the concatenated wide table. The entire process does not require programming or understanding of the mathematical principles of various modeling algorithms, thus lowering the barrier to entry. Attached Figure Description

[0050] To more clearly illustrate the technical solutions in the embodiments of the present 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 only embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the provided drawings without creative effort.

[0051] Figure 1 An architecture diagram of a data processing system provided in an embodiment of the present invention;

[0052] Figure 2 An example diagram illustrating the process of performing variable binning on multiple target independent variables to obtain the optimal variable binning, provided by an embodiment of the present invention;

[0053] Figure 3This is a flowchart illustrating a data processing system provided in an embodiment of the present invention. Detailed Implementation

[0054] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0055] In this application, relational terms such as "first" and "second" are used merely to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0056] See Figure 1 The diagram illustrates an architecture of a data processing system provided by an embodiment of the present invention. The data processing system includes at least a backend management service module (auto-data-admin), NAS, an SQLite database, a task execution service module, an Executor tool, and at least one server, including a Spark server and a Python server.

[0057] The task execution module is used to retrieve task requests from the SQLite database when a task request is detected, and then submit the task request to the Executor tool.

[0058] In this embodiment, business users can use a web browser to request the VIP address specified by Keepalived, and Keepalived will load balance the web browser request to the backend management service auto-data-admin module configured in Nginx.

[0059] Business users can use a web browser to submit task requests through the backend management service's auto-data-admin module and save the task requests to the sqlite database.

[0060] In this embodiment of the application, the Scheduler service in the task execution module can periodically scan the sqlite database for any pending task requests. When a pending task request is detected in the sqlite database, the task request is retrieved from the sqlite database and submitted to the Executor tool.

[0061] The task request is used to execute multiple tasks, including: data loading and preparation, data exploration, data cleaning, feature engineering, wide table concatenation, model building, model evaluation, and model cross-period validation.

[0062] The Executor tool is used to load and prepare data, perform feature engineering, and submit wide table concatenation to the Spark server. Upon receiving a concatenation completion notification, it determines the task type corresponding to model building and sends the target concatenation table and model to the server corresponding to that task type. The server then uses the target concatenation table to build the target model, resulting in the target model.

[0063] The tasks can be either standalone or clustered. Standalone tasks, including model building, model evaluation, and cross-period model validation, are submitted to a Python server. Clustered tasks, including model building, model evaluation, and cross-period model validation, are submitted to a Spark server.

[0064] The Spark server is used to load and prepare data to obtain multiple data tables; perform feature engineering on each data table to obtain the corresponding independent variable binning and encoding mapping table; perform wide table concatenation on multiple independent variable binning and encoding mapping tables to obtain the target concatenation table, and send the target concatenation table and wide table concatenation completion notification to the Executor tool.

[0065] In this embodiment, after receiving a task request from the task submission module, the Executor tool can submit tasks such as data loading and preparation, data exploration, data cleaning, feature engineering, and wide table concatenation from the task request to the Spark server, so that the Spark server can perform corresponding processing according to the received tasks.

[0066] Optionally, a Spark server is used to perform data loading and preparation to obtain multiple data tables. Specifically, this is used to: create an HDFS data directory corresponding to the task (in this case, data loading and preparation); establish a connection with the GP database and retrieve multiple data tables from the GP database that match the HDFS data directory; retrieve the corresponding header fields from each data table and store each data table and its corresponding header fields into the HDFS database.

[0067] It should be noted that before storing the data table to the HDFS database, you can also check whether the data table contains a primary key with the same format and name. If it does, delete the primary key with the duplicate format and name.

[0068] In this embodiment of the application, pySpark can also be used to explore the data tables stored in the HDFS database. Specifically, it can obtain basic information such as the number of samples, the number of fields, the field type, and the field missing rate. For numeric fields, it can obtain information such as the mean, quantiles, mode, skewness, kurtosis, and standard deviation. For discrete fields, it can obtain information such as the number of levels, the proportion of each level, and the maximum character length of each level.

[0069] It should be noted that Spark can also be used to probe the data quality and data distribution of data tables stored in the HDFS database in a parallel and distributed processing manner.

[0070] In this embodiment of the application, the results obtained from the above data exploration can be cleaned, specifically: numerical fields are processed differently according to the missing rate, including removing rows, removing columns, and filling in gaps; fields with excessive skewness are truncated, and values ​​above the quantile P95 and below the quantile P5 are replaced with P95 and P5 respectively, so that the field distribution is close to a normal distribution; fields with too small a standard deviation are removed, as fields with too small a standard deviation often carry less information.

[0071] For discrete type fields, differentiate processing is performed according to the number of levels. Fields with 1 level are directly eliminated, fields with 2 levels are encoded as 0 and 1, fields with excessively high number of levels are directly eliminated, or chi-square statistics are used to merge the number of levels, or relatively sparse levels are directly merged; levels with large character lengths are truncated or categorically encoded to prevent the original table from occupying too many bytes in memory.

[0072] Optionally, as a preferred embodiment of this application, a Spark server that performs feature engineering on each data table to obtain the independent variable binning and encoding mapping table corresponding to each data table is specifically used for: obtaining multiple data tables from the HDFS database; performing dimensionality reduction processing on each data table to obtain the target independent variable corresponding to each data table; performing variable binning processing on multiple target independent variables to obtain the optimal variable binning; and generating the independent variable binning and encoding mapping table based on the optimal variable binning and the encoding of each target independent variable.

[0073] Optionally, as a preferred embodiment of this application, dimensionality reduction processing is performed on each data table to obtain the Spark server for the target independent variable corresponding to each data table. Specifically, this is used to: perform variable clustering on each data table to obtain multiple variable groups corresponding to each data table, wherein the independent variables within each variable group are highly correlated, and the independent variables between the variable groups are highly uncorrelated.

[0074] In this embodiment, the data table is first divided into multiple clusters using variable clustering. The division criterion is that the independent variables within a cluster are highly correlated, while the independent variables between different clusters are uncorrelated. Then, based on minimizing 1-R... 2 The principle of ratio statistics is to select representative independent variables from each cluster, thereby achieving unsupervised feature dimensionality reduction. Among them, the minimization statistic is shown in formula (1).

[0075]

[0076] Optionally, as another preferred embodiment of this application, dimensionality reduction processing is performed on each data table to obtain the Spark server for the target independent variable corresponding to each data table: for each data table, a preset algorithm is used to calculate the statistic of each independent variable in the data table; independent variables with statistic values ​​less than a preset threshold are removed to obtain the target independent variable corresponding to the data table.

[0077] The preset algorithm can be IV, KS, chi-square, and Pearson correlation coefficient. Supervised feature dimensionality reduction is performed based on statistics such as IV, KS, chi-square, and Pearson correlation coefficient to remove independent variables that are significantly unrelated to the target variable. Taking IV as an example, its calculation formula is (2).

[0078]

[0079] Where i represents the bin number of the independent variable, bad i and good i These represent the number of positive and negative samples in each bin, respectively. total and good total This indicates the total number of positive and negative samples. i The magnitude of the index reflects the ability of each bin of the independent variable to distinguish between positive and negative samples, while the IV comprehensively evaluates the ability of each independent variable to distinguish between positive and negative samples.

[0080] Generally, an IV statistic below 0.2 indicates weak predictive power, and the corresponding variable can be removed; between 0.2 and 0.5, the variable is considered to have relatively strong predictive power; between 0.5 and 0.7, the variable is considered to have strong predictive power; and above 0.7, the variable is considered to be a post-hoc variable. Other statistics also have similar empirical thresholds for variable selection for reference.

[0081] In this embodiment, variable binning can significantly improve the stability and generalization ability of the model. The process of binning multiple target independent variables to obtain the optimal binning can be as follows: During the splitting process, the decision tree minimizes intra-bin differences and maximizes inter-bin differences based on the principle of maximizing entropy or Gini coefficient reduction. Then, the splitting is recursively performed until the number of bins reaches a preset threshold, thereby achieving the optimal binning of the independent variables. Figure 2 As shown.

[0082] It should be noted that after binning is completed, the optimal variable binning and each target independent variable are subjected to one-hot encoding, label encoding, or WOE encoding, and a mapping table of independent variable binning and encoding is generated and maintained.

[0083] Optionally, a Spark server with pre-defined filtering conditions performs wide table concatenation based on multiple independent variable bins and encoding mapping tables to obtain the target concatenated table. Specifically, this is used to: obtain the table name of each independent variable bin and encoding mapping table; assemble each table name into SQL code and process the SQL code to obtain an AST (Abstract Syntax Tree); parse the AST to obtain the target code; if the number of table names in the target code is greater than the target threshold and the target filtering conditions are empty, concatenate the target independent variable bins and encoding mapping tables corresponding to each table name in sequence to obtain the target concatenated table.

[0084] If the number of bins for independent variables and the number of table names in the encoding mapping table in the target code is greater than the target threshold, and the target filtering conditions are not empty, the Abstract Syntax Tree (AST) is parsed using the BFS algorithm, and the obtained data is stored in a preset data structure; the data in the preset data structure is traversed, and the cst_id column is obtained from the data in the preset data structure; the cst_id column is concatenated with other tables to obtain the target concatenation table, where the other tables are the data tables selected by the user in the web browser interface.

[0085] Optionally, if the model building task type is a standalone version, the Python server that uses the target concatenation table for model building is specifically used for: storing the target concatenation table in a local database and creating a local directory corresponding to the target concatenation table in the local database; obtaining the algorithm parameters of the algorithm corresponding to the standalone version, training the algorithm based on the target concatenation table and the algorithm parameters to obtain the target model; and storing the target model in the storage space corresponding to the local directory.

[0086] Optionally, if the task type for model building is a cluster version, the Spark server used for model building using the target concatenation table is specifically used for: creating the cluster directory corresponding to the target concatenation table; obtaining the algorithm parameters of the algorithm corresponding to the cluster version; training the algorithm based on the target concatenation table and the algorithm parameters to obtain the target model; and storing the target model in the storage space corresponding to the cluster directory.

[0087] In this embodiment, when executing a Spark server or Python server to build a model according to the user-defined task type, error information can be detected in real time during the model building process. If any errors are found, they can be recorded. Simultaneously, information such as model parameters, code, and files generated during the model building process can also be recorded.

[0088] In this embodiment of the application, the algorithm scenarios that the user wants to execute can also be set in the front end of the web browser, including binary classification, multi-class classification, regression, clustering, anomaly detection, etc.

[0089] For binary classification scenarios, algorithms such as logistic regression, decision tree, random forest, GBDT, XGboost, and LightGBM are used. Hyperparameter search is performed on each algorithm using grid search or random search. The models obtained under each algorithm scenario are compared, and the optimal model is selected based on statistical metrics including ROC, KS, response rate, precision, boost, and accuracy.

[0090] For multi-classification scenarios, algorithms such as logistic regression, decision tree, random forest, GBDT, XGboost, and LightGBM are used. Hyperparameter search is performed on each algorithm using grid search or random search. The models obtained by each algorithm are compared, and the optimal model is selected based on statistical measures such as chi-square, response rate, precision, F1 score, and accuracy.

[0091] For numerical regression scenarios, algorithms such as linear regression, decision tree, random forest, GBDT, XGboost, and LightGBM are used. Hyperparameter search is performed on each algorithm using grid search or random search. The models obtained under each algorithm scenario are compared, and the optimal model is selected based on indicators including R-squared, MSE, MAE, and other statistics.

[0092] For clustering scenarios, algorithms such as KMeans and hierarchical clustering are used. Hyperparameter search is performed on various algorithms using grid search or random search. The models obtained under each algorithm scenario are compared, and the optimal model is selected based on indicators including MSE, silhouette coefficient and other statistics.

[0093] For anomaly detection scenarios, algorithms such as LOF, Isolation Forest, and one-class SVM are used. Hyperparameter search is performed on various algorithms using grid search or random search. The optimal models obtained under each algorithm scenario are compared, and the optimal model is selected based on statistical measures such as ROC, KS, and anomaly score.

[0094] In the various algorithm scenarios described above, after model construction, a final list of input variables can be obtained. Combining this with the data processing and model construction processes described above, the relationships between the input variables can be recorded, and corresponding data processing SQL code can be automatically generated. Simultaneously, model training code and model scoring code for the optimal model can be generated.

[0095] This application supports output code formats including PMML, Python, and Spark. By assembling data processing SQL code and model training / scoring code, an end-to-end model training / scoring pipeline can be implemented, enabling automated data mining.

[0096] In this embodiment of the application, after the target model is obtained by using the target splicing table to construct the model, the obtained target model can also be evaluated.

[0097] Taking binary classification as an example, the target model is evaluated from multiple dimensions such as ROC curve, KS curve, response rate, precision, lift, accuracy, cross-entropy, confusion matrix, and variable importance. Corresponding charts are generated for the training set and validation set respectively, so as to determine whether the stability, accuracy, discriminativeness, and generalization of the target model on the out-of-sample validation set meet the business requirements.

[0098] In this embodiment of the application, the target model can also be validated across time periods.

[0099] The generated model scoring pipeline is used to score the cross-period test data. After generating the scores, taking a binary classification scenario as an example, the optimal model is evaluated from multiple dimensions such as ROC curve, KS curve, response rate, precision, lift, accuracy, cross entropy, confusion matrix, and variable importance. Corresponding charts are generated for the cross-period test set to determine whether the model's stability, accuracy, discriminativeness, and generalization on the cross-period dataset meet the business requirements.

[0100] In this embodiment of the application, if the task type of model building is a cluster version, the target model built on the Spark server is used to perform model testing, and the obtained test results are stored in the local NAS; the local Python script is used to determine whether there are any error values ​​in the obtained test results. If there are no errors, the obtained test results are stored in the GP database.

[0101] If the task type for model building is a standalone version, the target model built on the Python server is used to test the model and determine whether there are any error values ​​in the test results. If there are no errors, the test results are stored in the GP database.

[0102] This invention provides a data processing system, which includes at least an SQLite database, a task execution service module, an Executor tool, and at least one server, including a Spark server and a Python server. When the task execution module detects a task request in the SQLite database, it retrieves the task request from the SQLite database and submits it to the Executor tool. The task request is used to execute multiple tasks, which at least include: data loading and preparation, feature engineering, wide table concatenation, and model building. The Executor tool submits the data loading and preparation, feature engineering, and wide table concatenation to the Spark server. The Spark server performs the data loading... The process involves preparing multiple data tables; performing feature engineering on each data table to obtain the corresponding binning and encoding mapping tables for each table; performing wide table concatenation based on the multiple binning and encoding mapping tables to obtain the target concatenation table, and sending a notification of target concatenation table and wide table concatenation completion to the Executor tool; upon receiving the concatenation completion notification, the Executor tool determines the task type corresponding to model building and sends the target concatenation table and model building to the server corresponding to its task type to build the target model using the target concatenation table; the task type can be either a standalone version or a cluster version; the model building for the standalone version is submitted to the Python server; the model building for the cluster version is submitted to the Spark server. The technical solution provided by this invention can automatically load and prepare data, perform feature engineering, and concatenate wide tables based on a pre-defined HDFS data directory, and build a model based on the concatenated wide tables. The entire process does not require relevant personnel to program or understand the mathematical principles of various modeling algorithms, thus lowering the barrier to entry.

[0103] Corresponding to the data processing system provided in the above embodiments of the present invention, the present invention also discloses a data processing method, such as... Figure 3As shown, this method is applied to a data processing system, which includes at least an SQLite database, a task execution service module, an Executor tool, and at least one server, including a Spark server and a Python server. The data processing method specifically includes the following steps:

[0104] S301: When the task execution module detects a task request in the sqlite database, it retrieves the task request from the sqlite database and submits the task request to the Executor tool.

[0105] In this embodiment, business users can use a web browser to request the VIP address specified by Keepalived, and Keepalived will load balance the web browser request to the backend management service auto-data-admin module configured in Nginx.

[0106] Business users can use a web browser to submit task requests through the backend management service's auto-data-admin module and save the task requests to the sqlite database.

[0107] In this embodiment of the application, the Scheduler service in the task execution module can periodically scan the sqlite database for any pending task requests. When a pending task request is detected in the sqlite database, the task request is retrieved from the sqlite database and submitted to the Executor tool.

[0108] The task request is used to execute multiple tasks, including: data loading and preparation, data exploration, data cleaning, feature engineering, wide table concatenation, model building, model evaluation, and model cross-period validation.

[0109] S302: The Executor tool loads and prepares data, performs feature engineering, and submits wide table concatenation to the Spark server.

[0110] In this embodiment, after receiving a task request from the task submission module, the Executor tool can submit tasks such as data loading and preparation, data exploration, data cleaning, feature engineering, and wide table concatenation from the task request to the Spark server, so that the Spark server can perform corresponding processing according to the received tasks.

[0111] S303: The Spark server performs data loading and preparation to obtain multiple data tables; performs feature engineering based on each data table to obtain the independent variable binning and encoding mapping table corresponding to each data table; performs wide table concatenation based on multiple independent variable binning and encoding mapping tables to obtain the target concatenation table, and sends the target concatenation table and wide table concatenation completion notification to the Executor tool.

[0112] Optionally, a Spark server is used to perform data loading and preparation to obtain multiple data tables. Specifically, this is used to: create an HDFS data directory corresponding to the task (in this case, data loading and preparation); establish a connection with the GP database and retrieve multiple data tables from the GP database that match the HDFS data directory; retrieve the corresponding header fields from each data table and store each data table and its corresponding header fields into the HDFS database.

[0113] It should be noted that before storing the data table to the HDFS database, you can also check whether the data table contains a primary key with the same format and name. If it does, delete the primary key with the duplicate format and name.

[0114] In this embodiment of the application, pySpark can also be used to explore the data tables stored in the HDFS database. Specifically, it can obtain basic information such as the number of samples, the number of fields, the field type, and the field missing rate. For numeric fields, it can obtain information such as the mean, quantiles, mode, skewness, kurtosis, and standard deviation. For discrete fields, it can obtain information such as the number of levels, the proportion of each level, and the maximum character length of each level.

[0115] It should be noted that Spark can also be used to probe the data quality and data distribution of data tables stored in the HDFS database in a parallel and distributed processing manner.

[0116] In this embodiment of the application, the results obtained from the above data exploration can be cleaned, specifically: numerical fields are processed differently according to the missing rate, including removing rows, removing columns, and filling in gaps; fields with excessive skewness are truncated, and values ​​above the quantile P95 and below the quantile P5 are replaced with P95 and P5 respectively, so that the field distribution is close to a normal distribution; fields with too small a standard deviation are removed, as fields with too small a standard deviation often carry less information.

[0117] For discrete type fields, differentiate processing is performed according to the number of levels. Fields with 1 level are directly eliminated, fields with 2 levels are encoded as 0 and 1, fields with excessively high number of levels are directly eliminated, or chi-square statistics are used to merge the number of levels, or relatively sparse levels are directly merged; levels with large character lengths are truncated or categorically encoded to prevent the original table from occupying too many bytes in memory.

[0118] Optionally, as a preferred embodiment of this application, a Spark server that performs feature engineering on each of the multiple data tables to obtain the independent variable binning and encoding mapping table corresponding to each data table is specifically used for: obtaining multiple data tables from the HDFS database; performing dimensionality reduction processing on each data table to obtain the target independent variable corresponding to each data table; performing variable binning processing on the multiple target independent variables to obtain the optimal variable binning; and generating the independent variable binning and encoding mapping table based on the optimal variable binning and the encoding of each target independent variable.

[0119] Optionally, as a preferred embodiment of this application, dimensionality reduction processing is performed on each data table to obtain the Spark server for the target independent variable corresponding to each data table. Specifically, this is used to: perform variable clustering on each data table to obtain multiple variable groups corresponding to each data table, wherein the independent variables within each variable group are highly correlated, and the independent variables between the variable groups are highly uncorrelated.

[0120] In this embodiment, the data table is first divided into multiple clusters using variable clustering. The division criterion is that the independent variables within a cluster are highly correlated, while the independent variables between different clusters are uncorrelated. Then, based on minimizing 1-R... 2 The principle of ratio statistics is to select representative independent variables from each cluster, thereby achieving unsupervised feature dimensionality reduction. Among them, the minimization statistic is shown in formula (1).

[0121] Optionally, as another preferred embodiment of this application, dimensionality reduction processing is performed on each data table to obtain the Spark server for the target independent variable corresponding to each data table: for each data table, a preset algorithm is used to calculate the statistic of each independent variable in the data table; independent variables with statistic values ​​less than a preset threshold are removed to obtain the target independent variable corresponding to the data table.

[0122] The preset algorithm can be IV, KS, chi-square, and Pearson correlation coefficient. Supervised feature dimensionality reduction is performed based on statistics such as IV, KS, chi-square, and Pearson correlation coefficient to remove independent variables that are significantly unrelated to the target variable. Taking IV as an example, its calculation formula is (2).

[0123] Where i represents the bin number of the independent variable, bad i and good i These represent the number of positive and negative samples in each bin, respectively. total and good total This indicates the total number of positive and negative samples. i The magnitude of the index reflects the ability of each bin of the independent variable to distinguish between positive and negative samples, while the IV comprehensively evaluates the ability of each independent variable to distinguish between positive and negative samples.

[0124] Generally, an IV statistic below 0.2 indicates weak predictive power, and the corresponding variable can be removed; between 0.2 and 0.5, the variable is considered to have relatively strong predictive power; between 0.5 and 0.7, the variable is considered to have strong predictive power; and above 0.7, the variable is considered to be a post-hoc variable. Other statistics also have similar empirical thresholds for variable selection for reference.

[0125] In this embodiment, variable binning can significantly improve the stability and generalization ability of the model. The process of binning multiple target independent variables to obtain the optimal binning can be as follows: During the splitting process, the decision tree minimizes intra-bin differences and maximizes inter-bin differences based on the principle of maximizing entropy or Gini coefficient reduction. Then, the splitting is recursively performed until the number of bins reaches a preset threshold, thereby achieving the optimal binning of the independent variables. Figure 2 As shown.

[0126] It should be noted that after binning is completed, the optimal variable binning and each target independent variable are subjected to one-hot encoding, label encoding, or WOE encoding, and a mapping table of independent variable binning and encoding is generated and maintained.

[0127] Optionally, a Spark server with pre-defined filtering conditions performs wide table concatenation based on multiple independent variable bins and encoding mapping tables to obtain the target concatenated table. Specifically, this is used to: obtain the table name of each independent variable bin and encoding mapping table; assemble each table name into SQL code and process the SQL code to obtain an AST (Abstract Syntax Tree); parse the AST to obtain the target code; if the number of table names in the target code is greater than the target threshold and the target filtering conditions are empty, concatenate the target independent variable bins and encoding mapping tables corresponding to each table name in sequence to obtain the target concatenated table.

[0128] If the number of bins for independent variables and the number of table names in the encoding mapping table in the target code is greater than the target threshold, and the target filtering conditions are not empty, the Abstract Syntax Tree (AST) is parsed using the BFS algorithm, and the obtained data is stored in a preset data structure; the data in the preset data structure is traversed, and the cst_id column is obtained from the data in the preset data structure; the cst_id column is concatenated with other tables to obtain the target concatenation table, where the other tables are the data tables selected by the user in the web browser interface.

[0129] S304: When the Executor tool receives the splicing completion notification, it determines the task type corresponding to the model building and sends the target splicing table and model building to the server corresponding to its task type to build the model using the target splicing table and obtain the target model.

[0130] The tasks can be either standalone or clustered. Standalone tasks, including model building, model evaluation, and cross-period model validation, are submitted to a Python server. Clustered tasks, including model building, model evaluation, and cross-period model validation, are submitted to a Spark server.

[0131] Optionally, if the model building task type is a standalone version, the Python server that uses the target concatenation table for model building is specifically used for: storing the target concatenation table in a local database and creating a local directory corresponding to the target concatenation table in the local database; obtaining the algorithm parameters of the algorithm corresponding to the standalone version, training the algorithm based on the target concatenation table and the algorithm parameters to obtain the target model; and storing the target model in the storage space corresponding to the local directory.

[0132] Optionally, if the task type for model building is a cluster version, the Spark server used for model building using the target concatenation table is specifically used for: creating the cluster directory corresponding to the target concatenation table; obtaining the algorithm parameters of the algorithm corresponding to the cluster version; training the algorithm based on the target concatenation table and the algorithm parameters to obtain the target model; and storing the target model in the storage space corresponding to the cluster directory.

[0133] In this embodiment, when executing a Spark server or Python server to build a model according to the user-defined task type, error information can be detected in real time during the model building process. If any errors are found, they can be recorded. Simultaneously, information such as model parameters, code, and files generated during the model building process can also be recorded.

[0134] In this embodiment of the application, the algorithm scenarios that the user wants to execute can also be set in the front end of the web browser, including binary classification, multi-class classification, regression, clustering, anomaly detection, etc.

[0135] For binary classification scenarios, algorithms such as logistic regression, decision tree, random forest, GBDT, XGboost, and LightGBM are used. Hyperparameter search is performed on each algorithm using grid search or random search. The models obtained under each algorithm scenario are compared, and the optimal model is selected based on statistical metrics including ROC, KS, response rate, precision, boost, and accuracy.

[0136] For multi-classification scenarios, algorithms such as logistic regression, decision tree, random forest, GBDT, XGboost, and LightGBM are used. Hyperparameter search is performed on each algorithm using grid search or random search. The models obtained by each algorithm are compared, and the optimal model is selected based on statistical measures such as chi-square, response rate, precision, F1 score, and accuracy.

[0137] For numerical regression scenarios, algorithms such as linear regression, decision tree, random forest, GBDT, XGboost, and LightGBM are used. Hyperparameter search is performed on each algorithm using grid search or random search. The models obtained under each algorithm scenario are compared, and the optimal model is selected based on indicators including R-squared, MSE, MAE, and other statistics.

[0138] For clustering scenarios, algorithms such as KMeans and hierarchical clustering are used. Hyperparameter search is performed on various algorithms using grid search or random search. The models obtained under each algorithm scenario are compared, and the optimal model is selected based on indicators including MSE, silhouette coefficient and other statistics.

[0139] For anomaly detection scenarios, algorithms such as LOF, Isolation Forest, and one-class SVM are used. Hyperparameter search is performed on various algorithms using grid search or random search. The optimal models obtained under each algorithm scenario are compared, and the optimal model is selected based on statistical measures such as ROC, KS, and anomaly score.

[0140] In the various algorithm scenarios described above, after model construction, a final list of input variables can be obtained. Combining this with the data processing and model construction processes described above, the relationships between the input variables can be recorded, and corresponding data processing SQL code can be automatically generated. Simultaneously, model training code and model scoring code for the optimal model can be generated.

[0141] This application supports output code formats including PMML, Python, and Spark. By assembling data processing SQL code and model training / scoring code, an end-to-end model training / scoring pipeline can be implemented, enabling automated data mining.

[0142] In this embodiment of the application, after the target model is obtained by using the target splicing table to construct the model, the obtained target model can also be evaluated.

[0143] Taking binary classification as an example, the target model is evaluated from multiple dimensions such as ROC curve, KS curve, response rate, precision, lift, accuracy, cross-entropy, confusion matrix, and variable importance. Corresponding charts are generated for the training set and validation set respectively, so as to determine whether the stability, accuracy, discriminativeness, and generalization of the target model on the out-of-sample validation set meet the business requirements.

[0144] In this embodiment of the application, the target model can also be validated across time periods.

[0145] The generated model scoring pipeline is used to score the cross-period test data. After generating the scores, taking a binary classification scenario as an example, the optimal model is evaluated from multiple dimensions such as ROC curve, KS curve, response rate, precision, lift, accuracy, cross entropy, confusion matrix, and variable importance. Corresponding charts are generated for the cross-period test set to determine whether the model's stability, accuracy, discriminativeness, and generalization on the cross-period dataset meet the business requirements.

[0146] In this embodiment of the application, if the task type of model building is a cluster version, the target model built on the Spark server is used to perform model testing, and the obtained test results are stored in the local NAS; the local Python script is used to determine whether there are any error values ​​in the obtained test results. If there are no errors, the obtained test results are stored in the GP database.

[0147] If the task type for model building is a standalone version, the target model built on the Python server is used to test the model and determine whether there are any error values ​​in the test results. If there are no errors, the test results are stored in the GP database.

[0148] This invention provides a data processing system, which includes at least an SQLite database, a task execution service module, an Executor tool, and at least one server, including a Spark server and a Python server. When the task execution module detects a task request in the SQLite database, it retrieves the task request from the SQLite database and submits it to the Executor tool. The task request is used to execute multiple tasks, which at least include: data loading and preparation, feature engineering, wide table concatenation, and model building. The Executor tool submits the data loading and preparation, feature engineering, and wide table concatenation to the Spark server. The Spark server performs the data loading... The process involves preparing multiple data tables; performing feature engineering on each data table to obtain the corresponding binning and encoding mapping tables for each table; performing wide table concatenation based on the multiple binning and encoding mapping tables to obtain the target concatenation table, and sending a notification of target concatenation table and wide table concatenation completion to the Executor tool; upon receiving the concatenation completion notification, the Executor tool determines the task type corresponding to model building and sends the target concatenation table and model building to the server corresponding to its task type to build the target model using the target concatenation table; the task type can be either a standalone version or a cluster version; the model building for the standalone version is submitted to the Python server; the model building for the cluster version is submitted to the Spark server. The technical solution provided by this invention can automatically load and prepare data, perform feature engineering, and concatenate wide tables based on a pre-defined HDFS data directory, and build a model based on the concatenated wide tables. The entire process does not require relevant personnel to program or understand the mathematical principles of various modeling algorithms, thus lowering the barrier to entry.

[0149] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on its differences from other embodiments. In particular, for system or system embodiments, since they are basically similar to method embodiments, the description is relatively simple; relevant parts can be referred to the descriptions in the method embodiments. The systems and system embodiments described above are merely illustrative. Units described as separate components may or may not be physically separate. Components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without creative effort.

[0150] Those skilled in the art will further recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of the various examples have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this invention.

[0151] The above description of the disclosed embodiments enables those skilled in the art to make or use the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

[0152] The above are merely preferred embodiments of the present invention. It should be noted that those skilled in the art can make various improvements and modifications without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A data processing system, characterized in that, The data processing system includes at least an SQLite database, a task execution service module, an Executor tool, and at least one server, wherein the at least one server includes a Spark server and a Python server; The task execution module is used to retrieve the task request from the SQLite database when a task request is detected in the SQLite database, and submit the task request to the Executor tool; wherein the task request is used to execute multiple tasks, and the multiple tasks include at least: data loading and preparation, feature engineering, wide table concatenation, and model building; The Executor tool is used to load and prepare the data, perform feature engineering, and concatenate the wide table to submit it to the Spark server. The Spark server is used to perform data loading and preparation to obtain multiple data tables; perform feature engineering based on each data table to obtain the independent variable binning and encoding mapping table corresponding to each data table; perform wide table concatenation based on the multiple independent variable binning and encoding mapping tables to obtain the target concatenation table, and send the target concatenation table and wide table concatenation completion notification to the Executor tool; The Executor tool is used to determine the task type corresponding to the model building when it receives the splicing completion notification, and send the target splicing table and the model building to the server corresponding to its task type to build the model using the target splicing table to obtain the target model; The task type is either a standalone version or a cluster version; the model building for the standalone version is submitted to the Python server; the model building for the cluster version is submitted to the Spark server. The Spark server that performs feature engineering based on each of the data tables to obtain the independent variable binning and encoding mapping table corresponding to each data table is specifically used for: Retrieve the plurality of data tables from the HDFS database; Dimensionality reduction is performed on each of the data tables to obtain the target independent variable corresponding to each data table; The multiple target independent variables are binned to obtain the optimal variable binning. Based on the optimal variable binning and the encoding of each target independent variable, generate an independent variable binning and encoding mapping table; The Spark server that performs wide table concatenation based on multiple independent variable binning and encoding mapping tables to obtain the target concatenated table is specifically used for: Obtain the table name of each independent variable binning and encoding mapping table; Each of the table names is assembled into SQL code, and the SQL code is processed to obtain an AST (Abstract Syntax Tree). The target code is obtained by parsing the AST (Abstract Syntax Tree). If the number of table names in the independent variable binning and encoding mapping table in the target code is greater than the target threshold, and the target filtering condition is empty, the target independent variable binning and encoding mapping table corresponding to each table name are sequentially concatenated to obtain the target concatenation table.

2. The system according to claim 1, characterized in that, The Spark server that performs data loading and preparation to obtain multiple data tables is specifically used for: Create the HDFS data directory corresponding to the task described above; Establish a connection with the GP database and retrieve multiple data tables from the GP database that match the HDFS data directory; Retrieve the corresponding header fields from each of the data tables, and store each of the data tables and its corresponding header fields in the HDFS database.

3. The system according to claim 1, characterized in that, The Spark server that performs dimensionality reduction processing on each of the data tables to obtain the target independent variable corresponding to each data table is specifically used for: Each of the data tables is clustered to obtain multiple variable groups corresponding to each data table. The independent variables within each variable group are highly correlated, while the independent variables between the variable groups are highly uncorrelated. Based on the rule of minimizing statistics, a target independent variable is selected from each of the variable groups.

4. The system according to claim 1, characterized in that, The Spark server that performs dimensionality reduction processing on each of the data tables to obtain the target independent variable corresponding to each data table is specifically used for: For each of the data tables, a preset algorithm is used to calculate the statistic for each independent variable in the data table; The independent variables whose statistical values ​​are less than a preset threshold are removed to obtain the target independent variables corresponding to the data table.

5. The system according to claim 1, characterized in that, The Spark server is also used for: If the number of bins for independent variables and the number of table names in the encoding mapping table in the target code is greater than the target threshold, and the target filtering condition is not empty, the AST abstract syntax tree is parsed using the BFS algorithm, and the obtained data is stored in a preset data structure. Traverse the data in the preset data structure and obtain the cst_id column from the data in the preset data structure; The cst_id column is concatenated with other tables to obtain the target concatenated table, wherein the other tables are the data tables selected by the user in the web browser interface.

6. The system according to claim 1, characterized in that, If the task type for model building is a standalone version, the Python server that uses the target concatenation table for model building is specifically used for: The target concatenation table is stored in a local database, and a local directory corresponding to the target concatenation table is created in the local database; Obtain the algorithm parameters corresponding to the standalone version of the algorithm, and train the algorithm according to the target splicing table and the algorithm parameters to obtain the target model; The target model is stored in the storage space corresponding to the local directory.

7. The system according to claim 1, characterized in that, If the task type for model building is a cluster version, the Spark server that uses the target concatenation table for model building is specifically used for: Create the cluster directory corresponding to the target concatenation table; Obtain the algorithm parameters corresponding to the cluster version of the algorithm, and train the algorithm according to the target splicing table and the algorithm parameters to obtain the target model; The target model is stored in the storage space corresponding to the cluster directory.

8. A data processing method, characterized in that, Applied to a data processing system, the data processing system including at least an SQLite database, a task execution service module, an Executor tool, and at least one server, the at least one server including a Spark server and a Python server, the method includes: When the task execution module detects a task request in the SQLite database, it retrieves the task request from the SQLite database and submits the task request to the Executor tool; wherein, the task request is used to execute multiple tasks, and the multiple tasks include at least: data loading and preparation, feature engineering, wide table concatenation, and model building; The Executor tool loads and prepares the data, performs feature engineering, and concatenates the wide table before submitting it to the Spark server. The Spark server performs data loading and preparation to obtain multiple data tables; based on each data table, it performs feature engineering to obtain the independent variable binning and encoding mapping table corresponding to each data table; based on the multiple independent variable binning and encoding mapping tables, it performs wide table concatenation to obtain the target concatenation table, and sends the target concatenation table and wide table concatenation completion notification to the Executor tool. When the Executor tool receives the splicing completion notification, it determines the task type corresponding to the model construction and sends the target splicing table and the model construction to the server corresponding to its task type to perform model construction using the target splicing table to obtain the target model; The task type is either a standalone version or a cluster version; the model building for the standalone version is submitted to the Python server; the model building for the cluster version is submitted to the Spark server. The step of performing feature engineering based on each of the data tables to obtain the independent variable binning and encoding mapping table corresponding to each data table includes: Retrieve the plurality of data tables from the HDFS database; Dimensionality reduction is performed on each of the data tables to obtain the target independent variable corresponding to each data table; The multiple target independent variables are binned to obtain the optimal variable binning. Based on the optimal variable binning and the encoding of each target independent variable, generate an independent variable binning and encoding mapping table; The step of performing wide table concatenation based on multiple independent variable binning and encoding mapping tables to obtain the target concatenation table includes: Obtain the table name of each independent variable binning and encoding mapping table; Each of the table names is assembled into SQL code, and the SQL code is processed to obtain an AST (Abstract Syntax Tree). The target code is obtained by parsing the AST (Abstract Syntax Tree). If the number of table names in the independent variable binning and encoding mapping table in the target code is greater than the target threshold, and the target filtering condition is empty, the target independent variable binning and encoding mapping table corresponding to each table name are sequentially concatenated to obtain the target concatenation table.

Images