Machine learning-based etl task automatic configuration method and device

Abstract

This invention discloses a machine learning-based method and apparatus for automatic ETL task configuration. The method involves collecting historical data and corresponding configuration results, training the ETL configuration model using machine learning, and extracting the mapping relationships of task keyword groups from the historical data as a task rule base. The task rule base is then used to match target ETL tasks, obtaining the matching degree of different configuration results. When the matching degree of all different configuration results is less than or equal to a preset threshold, the ETL configuration model is used to match configuration results for the target ETL task. This approach improves the accuracy of matching results and reduces computational cost by implementing most of the matching tasks using a simple keyword-based task rule base, while still providing accurate matching results.

Description

Technical Field

[0001] The embodiments of the present invention relate to computer data processing technology, and in particular to an automatic configuration method and apparatus for ETL tasks based on machine learning. Background Technology

[0002] ETL (Extract-Transform-Load, data warehouse technology) is the process of extracting, cleaning, and transforming data from business systems and then loading it into a data warehouse. The purpose is to integrate scattered, disorganized, and inconsistent data within an enterprise to provide analytical support for decision-making.

[0003] A typical ETL task flow requires defining rules for each task stage to map the source dataset to the target dataset. During the task flow development process, due to the large number of tasks, some tasks may be configured repeatedly, leading to low configuration efficiency.

[0004] The current approach involves users dragging and dropping task flow components to generate a first task flow. The system then matches the metadata of the input task flow with the metadata of the first task flow in a task flow library, identifying input task flows that exceed a semantic similarity threshold as the second task flow. Finally, the first and second task flows are merged to form a single task flow. This method automatically matches the second task flow, reduces redundant configuration, and increases the reusability of existing tasks.

[0005] However, the accuracy of combining the above semantic matching methods in the task is relatively low. Summary of the Invention

[0006] This invention provides an automatic ETL task configuration method to improve the accuracy of automatic ETL task configuration without significantly increasing computing performance.

[0007] In a first aspect, embodiments of the present invention provide an automatic ETL task configuration method, comprising:

[0008] The target ETL task is matched using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results.

[0009] When the matching degree of the different configuration results is less than or equal to the preset threshold, the feature data of the target ETL task is extracted.

[0010] The feature data of the target ETL task is input into a pre-trained ETL configuration model to obtain the configuration result of the target ETL task.

[0011] Preferably, after the step of matching the target ETL task using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results, the method further includes:

[0012] When the matching degree of the different configuration results is greater than the preset threshold, the matching result with the highest matching degree is used as the configuration result of the target ETL task.

[0013] Preferably, before the step of matching the target ETL task using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results, the method further includes:

[0014] Historical data and corresponding configuration results are collected and trained using machine learning to obtain the ETL configuration model. The mapping relationship of task keyword groups in the historical data is extracted as the task rule base.

[0015] Preferably, the steps of collecting historical data and corresponding configuration results for machine learning training to obtain the ETL configuration model, and extracting the mapping relationship of task keyword groups from the historical data as the task rule base specifically include:

[0016] Collect the historical data and the feature data from the corresponding configuration results;

[0017] The mapping rules from the historical data to the corresponding configuration results are used as the target variable to construct the dataset;

[0018] The dataset is divided into a training set, a validation set, and a test set according to a preset ratio;

[0019] The random forest model is used to learn from the training set data, the validation set data is used to verify the model performance, the model parameters are adjusted according to the validation results, the model with adjusted parameters is tested using the test set, and the model and parameters with the highest matching degree are selected as the ETL configuration model.

[0020] The mapping relationship of task keyword groups extracted from the historical data is used as the task rule base.

[0021] Preferably, after the step of collecting the historical data and the feature data in the corresponding configuration results, the method further includes:

[0022] The collected historical data is cleaned, and the cleaned data is then updated to reflect the historical data.

[0023] Preferably, the step of cleaning the collected historical data and updating the historical data with the cleaned data specifically includes:

[0024] Obtain column data from the historical data, count duplicate data in the column data, and perform a deduplication step.

[0025] Preferably, it further includes:

[0026] Obtain column data from the historical source table, and statistically analyze the continuous data, missing values ​​in the continuous data, discrete data, and missing values ​​in the discrete data in the column data.

[0027] Outliers in the continuous data are removed and treated as missing values. The mean of the data in the column is used to fill the missing values ​​in the continuous variable. If the continuous data in the column is of time series type, the mean of the two data before and after the missing value is used to fill it. The frequency of all numbers in the corresponding column of the discrete data is calculated, and the number with the highest frequency is used to fill the missing value in the corresponding column of the discrete data.

[0028] The present invention also proposes a server, the server comprising: a memory, a processor, and an ETL task auto-configuration program stored on the memory and capable of running on the processor, wherein the ETL task auto-configuration program, when executed by the processor, implements the ETL task auto-configuration method as described above.

[0029] The present invention also proposes an automatic ETL task configuration device, the automatic ETL task configuration device comprising:

[0030] The matching degree calculation unit is used to match the target ETL task using a preset task rule library based on keyword group matching, and obtain the matching degree of different configuration results.

[0031] The feature extraction unit is used to extract feature data of the target ETL task when the matching degree of the different configuration results is less than or equal to a preset threshold.

[0032] The ETL configuration unit is used to input the feature data of the target ETL task into a pre-trained ETL configuration model to obtain the configuration result of the target ETL task.

[0033] The present invention also proposes a readable storage medium storing an ETL task auto-configuration program, which, when executed by a processor, implements the ETL task auto-configuration method as described above.

[0034] This invention addresses the problem of low accuracy in traditional keyword matching by setting a matching threshold and using machine learning to configure ETL tasks with low matching accuracy. This achieves the goal of improving the accuracy of ETL task configuration using machine learning without significantly increasing computing performance. Attached Figure Description

[0035] Figure 1 This is a flowchart illustrating an embodiment of the automatic task configuration method of the present invention;

[0036] Figure 2 This is a flowchart illustrating another embodiment of the automatic task configuration method of the present invention;

[0037] Figure 3 This is a flowchart illustrating another embodiment of the automatic task configuration method of the present invention;

[0038] Figure 4 This is a schematic diagram of the model establishment process in the automatic task configuration method of the present invention;

[0039] Figure 5 This is a schematic diagram of the structure of an embodiment of the automatic task configuration device of the present invention;

[0040] Figure 6 This is a schematic diagram of the server structure in an embodiment of the present invention. Detailed Implementation

[0041] The present invention will now be described in further detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and not intended to limit it. Furthermore, it should be noted that, for ease of description, the accompanying drawings show only the parts relevant to the present invention, and not all of the structures.

[0042] Figure 1 This is a flowchart of an automatic ETL task configuration method provided in Embodiment 1 of the present invention. This embodiment is applicable to the rule definition of the ETL task process. The method can be executed by a computing device with ETL functionality, and specifically includes:

[0043] S100. Use a preset task rule base based on keyword group matching to match the target ETL task and obtain the matching degree of different configuration results.

[0044] It should be noted that since the task rule base is implemented based on keyword group matching, the matching degree can be calculated simply by weighting the proportion of keyword groups in the entire business description. The required computation is minimal, and the configuration results can be obtained quickly. Furthermore, since business personnel have a deep understanding of this method, they can choose descriptive terms that are more conducive to the task rule base when describing their business, thereby further improving the accuracy of ETL task matching based on the task rule base.

[0045] For example, in an ETL task in a healthcare system, the metadata for hospitalno (varchar) is the healthcare institution code; the metadata for hospitalname (varchar) is the healthcare institution name. Since the keyword phrases are relatively clear, and the metadata and table phrases are Chinese-English translations, the accuracy of matching based on keyword phrases is close to 100%. However, the metadata for paykind (varchar) is the Alipay type code sys_codeitems.collect code = 02_158; the accuracy of its matching method is lower than the preset threshold. In this case, it is necessary to use machine learning-based methods to improve the accuracy of the matching results.

[0046] S200. When the matching degree of the different configuration results is less than or equal to a preset threshold, extract the feature data of the target ETL task.

[0047] It should be noted that when the matching degree of different configurations is less than or equal to the preset threshold, the accuracy of the matching result of the configuration with the highest matching degree is relatively low. Therefore, in order to improve the accuracy of the matching results, other matching methods need to be used. This embodiment improves the accuracy of the matching results by using machine learning. Therefore, the target ETL task needs to be preprocessed, that is, the feature data of the target ETL task needs to be extracted.

[0048] S300. Input the feature data of the target ETL task into the pre-trained ETL configuration model to obtain the configuration result of the target ETL task.

[0049] Machine learning-based configuration methods consume a lot of computing power, resulting in long matching times for ETL tasks. According to statistics, in the business application of this embodiment, historical data shows that when the matching degree is greater than 90%, the task rule base can achieve near 100% correct configuration. Therefore, this embodiment sets the preset threshold to 0.9 to effectively utilize the task rule base and reduce the overall computing load while ensuring accuracy.

[0050] The technical solution of this embodiment effectively utilizes a keyword-based configuration method while ensuring accuracy by setting a preset threshold based on daily statistics. This reduces the computational load added by using machine learning. Without upgrading hardware, ETL tasks with high matching accuracy can quickly match configuration results, while ETL tasks with low matching accuracy improve the accuracy of configuration results through a machine learning-based ETL configuration model. This solves the technical problem of low accuracy of configuration results in the automatic configuration of ETL tasks in the prior art. It achieves the effect of improving the accuracy of automatic configuration results of ETL tasks without upgrading hardware, simply by extending the processing time of ETL tasks with inaccurate configuration results.

[0051] Reference Figure 2 In addition to the steps S100-S300 described above, the automatic ETL task configuration method provided by this invention also includes the following steps:

[0052] S400. When the matching degree of the different configuration results is greater than the preset threshold, the matching result with the highest matching degree is used as the configuration result of the target ETL task.

[0053] Statistical analysis of historical data reveals that when different matching tasks exist, the target ETL task has a low but very close match with the two matched configuration results. For example, when a hospital adds Alipay and WeChat Pay, the ETL task includes an additional configuration for the payment type field `paykindname(varhar)`, which is very similar to the payment type code `paykind` and cannot be derived through translation. In this case, the task rule base needs to manually add mapping relationships, while a machine learning-based model can directly derive the result. This embodiment sets the minimum threshold based on the statistical results of historical data, referencing the erroneous task with the highest matching degree; the maximum threshold is within this range. In this embodiment, the threshold is set to 1, which gives the range of possible threshold values. Typically, the set value is slightly higher than the minimum value and rounded to an integer. This ensures that machine learning tasks only perform calculations on ETL tasks that may produce errors. Furthermore, by increasing the threshold, higher accuracy can be achieved at the expense of some ETL task processing speed. In the example above, the preset threshold is set to 0.9, meaning that the configuration method based on the task rule base is only used when there is a configuration result with a matching degree of 90%. This threshold is the threshold for achieving higher accuracy. By adjusting the threshold, a balance can be achieved between calculation time and accuracy based on the device's calculation speed, thereby improving matching accuracy and reducing matching time to a certain extent.

[0054] Specifically, before the step of matching the target ETL task using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results, the following steps are also included:

[0055] Historical data and corresponding configuration results are collected and trained using machine learning to obtain the ETL configuration model. The mapping relationship of task keyword groups in the historical data is extracted as the task rule base.

[0056] It is easy to understand that the mapping relationship of task keyword groups in historical data can be extracted from metadata. Existing technologies mostly use task rule base matching methods based on keyword groups. Metadata is the descriptive information of table data by data managers or business personnel based on their own understanding. For most common business, the descriptions of business personnel are mostly highly accurate. At this time, the configuration results of task rule base matching are also highly accurate. For example, the metadata of hospitalno(varchar) is the code of medical institutions. Its accuracy rate in historical records is 100% through the matching results of task rule base.

[0057] Reference Figure 3 and Figure 4 The steps of collecting historical data and corresponding configuration results, training them for machine learning to obtain the ETL configuration model, and extracting the mapping relationship of task keyword groups from the historical data as the task rule base specifically include:

[0058] S110. Collect the historical data and the feature data in the corresponding configuration results;

[0059] It is easy to understand that before using machine learning-based model algorithms, a large number of ETL tasks have been matched through task rule bases and manual adjustments. In order to query the detailed information of completed business at any time, a large amount of completed business data is stored in the database. Among them, historical data is the input data of ETL tasks, and the corresponding configuration result is the configuration result of ETL tasks. Feature data includes at least the column data of the source table. In this embodiment, it also includes: table name, whether there is a related table, column name, column type, whether it is not null, whether it is a foreign key, whether it is a primary key, etc. as feature data.

[0060] S120, Construct a dataset using the mapping rules from the historical data to the corresponding configuration results as the target variable;

[0061] Historical data includes data from historical source and target tables. The corresponding configuration result is the configuration result from the source table to the target table. Machine learning needs to be based on existing raw data and processed data, so historical data and the corresponding configuration result are required. In this embodiment, the mapping rule is used as the target variable. By adjusting the parameters, the training time and computational load can be reduced to a certain extent.

[0062] S130. Divide the dataset into a training set, a validation set, and a test set according to a preset ratio;

[0063] Since parameter adjustments are required during the training process in this embodiment, the dataset needs to be divided into a training set, a validation set, and a test set. In this embodiment, the division ratio is 6:2:2. When the amount of data is large, the proportion of the training set can be increased to ensure the training results.

[0064] S140. Use a random forest model to learn from the training set data, use the validation set data to verify the model effect, adjust the model parameters according to the validation results, use the test set to test the model with adjusted parameters, and select the model and parameters with the highest matching degree as the ETL configuration model.

[0065] During model training, by adjusting the model parameters, models with different effects can be trained. The model that performs best on the test set can be considered to be the model with the best generalization ability. This model and its configuration parameters are then regarded as the model and parameters with the highest matching degree and used as the ETL configuration model.

[0066] Finally, the model used to implement ETL configuration can take the column characteristics of the source and target tables as input. These characteristics include: table name, whether there are related tables, column name, column type, whether it is NOT null, whether it is a foreign key, and whether it is a primary key. The model can then determine the most likely mapping rule type for the source-to-target table relationship, such as column type conversion, sorting, or grouping. Based on the specified columns, it generates table-to-table column-based operation mapping rules, thus automatically configuring the ETL task.

[0067] It is easy to understand that an accuracy target is set before training based on business needs. Usually, when the model performance obtained from the validation set does not meet the set accuracy target, the model accuracy is further improved by adjusting the model parameters. Therefore, the historical data is divided into three datasets to meet the validation requirements before parameter adjustment and the testing requirements after parameter adjustment.

[0068] S150. Extract the mapping relationship of the task keyword groups in the historical data as the task rule base.

[0069] It should be noted that, due to the machine learning-based matching method, even without considering the computing power required to train the model, the computing power consumed during the matching process alone is higher than that consumed by the historical data task rule base. Without a significant improvement in computing performance, the task rule base is still needed to improve processing speed in order to ensure processing efficiency.

[0070] This embodiment improves the technical solution by disclosing specific training steps, and further enhances the accuracy of the model by setting up a validation set and a test set, and by adjusting parameters through training, thereby improving the accuracy of the overall configuration results, enhancing the configuration effect of ETL task automatic configuration, and improving the user experience.

[0071] The present invention, after the steps of collecting the historical data and the feature data in the corresponding configuration results described above, further includes the following steps:

[0072] S111. Clean the collected historical data and update the historical data with the cleaned data.

[0073] It is worth emphasizing that, since historical data often contains some missing and duplicate data, these problems have little impact on matching methods based on task keyword group mapping relationships, but have a greater impact on machine learning. Therefore, it is necessary to clean the data and improve the accuracy of the training results by improving the accuracy of the training data.

[0074] Specifically, the steps of cleaning the collected historical data and updating the historical data with the cleaned data include:

[0075] S112. Obtain column data from the historical data, count duplicate data in the column data, and perform a deduplication step.

[0076] It is easy to understand that duplicate data will increase the proportion of duplicate samples in machine learning, and the model may overlearn on the sample, which may lead to overfitting. If some duplicate samples appear in the training set and others appear in the test set, there is also a risk of data leakage. Therefore, it is necessary to perform a deduplication step. In this embodiment, the deduplication method includes keeping only the first one and matching and merging deduplication based on relevant information.

[0077] Specifically, it also includes:

[0078] S113. Obtain column data from the historical source table, and count the continuous data, missing values ​​in the continuous data, discrete data, and missing values ​​in the discrete data in the column data.

[0079] It should be noted that, in this embodiment, continuous data can be continuous data with a time series. When the total amount of data is large, the proportion of samples with missing data is not high, and the samples can be directly deleted. However, when the total amount of data is small, in order to increase the number of samples, it is necessary to reasonably supplement and improve the missing data. In discrete data, the data lacks regularity, and it is difficult to estimate the missing data using adjacent data. Usually, in this case, the sample will be deleted. Only when the number of samples is seriously insufficient is it necessary to supplement the missing data.

[0080] S114. Remove outliers from the continuous data as missing values, fill the missing values ​​in the continuous variable with the mean of the data in the column. If the continuous data in the column is a time series, fill it with the mean of the two data before and after the missing value. Calculate the frequency of all numbers in the corresponding column of the discrete data, and fill the missing value in the corresponding column of the discrete data with the number with the highest frequency.

[0081] It is easy to understand that in continuous data of the time series type, apart from the data at the peak and trough endpoints, the missing data mostly exists between adjacent data. Therefore, using the mean of adjacent data as the value of the missing data has a small error. Even for data at the peak and trough endpoints, a small error can be achieved.

[0082] When a sample in discrete data contains missing data that has not been deleted, the missing data can be supplemented by counting the frequency of each data point in the discrete data of the missing data portion of that sample. The data with the highest frequency can then be used.

[0083] During the initial training, due to the small number of samples, when missing or duplicate data is found, it will be sent to the business personnel first. After the data is supplemented or automatically supplemented, it will be sent to the business personnel to confirm whether the data discrepancies are too large. If the data with too large discrepancies is returned and cannot be supplemented, the sample will be deleted.

[0084] This embodiment improves the technical solution by disclosing detailed data cleaning methods, which to a certain extent improves the speed and accuracy of automatic matching of ETL tasks. At the same time, it discloses detailed information and training process of collecting historical data and corresponding configuration results of feature data, so that the training of machine learning can be adjusted according to the ETL task. As more and more tasks are completed, more and more historical data and corresponding configuration results are generated, the trained model becomes more accurate, and the final matching configuration results become more and more accurate.

[0085] Reference Figure 5 The present invention also proposes an automatic ETL task configuration device, the automatic ETL task configuration device comprising:

[0086] The matching degree calculation unit 10 is used to match the target ETL task using a preset task rule library based on keyword group matching to obtain the matching degree of different configuration results.

[0087] The feature extraction unit 20 is used to extract feature data of the target ETL task when the matching degree of the different configuration results is less than or equal to a preset threshold.

[0088] ETL configuration unit 30 is used to input the feature data of the target ETL task into a pre-trained ETL configuration model to obtain the configuration result of the target ETL task.

[0089] The ETL task automatic configuration device provided in the embodiments of the present invention can execute the ETL task automatic configuration method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method, which will not be described in detail here.

[0090] Figure 6 This is a schematic diagram of the structure of a server provided in Embodiment C of the present invention, as shown below. Figure 6 As shown, the server includes a processor 70, a memory 71, an input device 72, and an output device 73; the number of processors 70 in the server can be one or more. Figure 6 Taking a processor 70 as an example; the processor 70, memory 71, input device 72, and output device 73 in the server can be connected via a bus or other means. Figure 6 Taking the example of a connection between China and Israel via a bus.

[0091] The memory 71, as a computer-readable storage medium, can be used to store software programs, computer-executable programs, and modules, such as the program instructions corresponding to the ETL task automatic configuration method in this embodiment of the invention. The processor 70 executes various server functions and data processing by running the software programs, instructions, and modules stored in the memory 71, thereby implementing the aforementioned ETL task automatic configuration method.

[0092] The memory 71 may primarily include a program storage area and a data storage area. The program storage area may store the operating system and at least one application program required for a given function; the data storage area may store data created based on terminal usage. Furthermore, the memory 71 may include high-speed random access memory and non-volatile memory, such as at least one disk storage device, flash memory, or other non-volatile solid-state storage device. In some instances, the memory 71 may further include memory remotely located relative to the processor 70, which can be connected to a server via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0093] Input device 72 can be used to receive input numerical or character information, and to generate key signal inputs related to user settings and function control of the server. Output device 73 may include a display server such as a display screen.

[0094] This invention also provides a storage medium containing computer-executable instructions, which, when executed by a computer processor, are used to perform an ETL task auto-configuration method, the method comprising:

[0095] The target ETL task is matched using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results.

[0096] When the matching degree of the different configuration results is less than or equal to the preset threshold, the feature data of the target ETL task is extracted.

[0097] The feature data of the target ETL task is input into a pre-trained ETL configuration model to obtain the configuration result of the target ETL task.

[0098] Specifically, after the step of matching the target ETL task using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results, it also includes:

[0099] When the matching degree of the different configuration results is greater than the preset threshold, the matching result with the highest matching degree is used as the configuration result of the target ETL task.

[0100] Specifically, before the step of matching the target ETL task using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results, the following steps are also included:

[0101] Historical data and corresponding configuration results are collected and trained using machine learning to obtain the ETL configuration model. The mapping relationship of task keyword groups in the historical data is extracted as the task rule base.

[0102] Specifically, the steps of collecting historical data and corresponding configuration results for machine learning training to obtain the ETL configuration model, and extracting the mapping relationship of task keyword groups from the historical data as the task rule base, specifically include:

[0103] Collect the historical data and the feature data from the corresponding configuration results;

[0104] The mapping rules from the historical data to the corresponding configuration results are used as the target variable to construct the dataset;

[0105] The dataset is divided into a training set, a validation set, and a test set according to a preset ratio;

[0106] The random forest model is used to learn from the training set data, the validation set data is used to verify the model performance, the model parameters are adjusted according to the validation results, the model with adjusted parameters is tested using the test set, and the model and parameters with the highest matching degree are selected as the ETL configuration model.

[0107] The mapping relationship of task keyword groups extracted from the historical data is used as the task rule base.

[0108] Specifically, after the step of collecting the historical data and the feature data in the corresponding configuration results, the method further includes:

[0109] The collected historical data is cleaned, and the cleaned data is then updated to reflect the historical data.

[0110] Specifically, the steps of cleaning the collected historical data and updating the historical data with the cleaned data include:

[0111] Obtain column data from the historical data, count duplicate data in the column data, and perform a deduplication step.

[0112] Specifically, it also includes:

[0113] Obtain column data from the historical source table, and statistically analyze the continuous data, missing values ​​in the continuous data, discrete data, and missing values ​​in the discrete data in the column data.

[0114] Outliers in the continuous data are removed and treated as missing values. The mean of the data in the column is used to fill the missing values ​​in the continuous variable. If the continuous data in the column is of time series type, the mean of the two data before and after the missing value is used to fill it. The frequency of all numbers in the corresponding column of the discrete data is calculated, and the number with the highest frequency is used to fill the missing value in the corresponding column of the discrete data.

[0115] Of course, the computer-executable instructions provided in the embodiments of the present invention are not limited to the method operations described above. They can also execute related operations in the ETL task automatic configuration method provided in any embodiment of the present invention, and have the corresponding functional modules and beneficial effects of the execution method. They will not be described in detail here.

[0116] Based on the above description of the implementation methods, those skilled in the art can clearly understand that the present invention can be implemented using software and necessary general-purpose hardware, and of course, it can also be implemented using hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as a computer floppy disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk, or optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments of the present invention.

[0117] It is worth noting that in the embodiments of the search device described above, the various units and modules included are only divided according to functional logic, but are not limited to the above division, as long as the corresponding functions can be achieved; in addition, the specific names of each functional unit are only for easy differentiation and are not used to limit the scope of protection of the present invention.

[0118] Note that the above description is merely a preferred embodiment of the present invention and the technical principles employed. Those skilled in the art will understand that the present invention is not limited to the specific embodiments described herein, and various obvious changes, readjustments, and substitutions can be made without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in detail through the above embodiments, the present invention is not limited to the above embodiments, and may include many other equivalent embodiments without departing from the concept of the present invention, the scope of which is determined by the scope of the appended claims.

Claims

1. An automatic configuration method for ETL tasks, characterized in that, include: Collect historical data and corresponding feature data from the configuration results; wherein, the historical data includes data from the historical source table and the target table, and the corresponding configuration results are the configuration results from the source table to the target table; The mapping rules from the historical data to the corresponding configuration results are used as the target variable to construct the dataset; The dataset is divided into a training set, a validation set, and a test set according to a preset ratio; The random forest model is used to learn from the training set data, the validation set data is used to verify the model performance, the model parameters are adjusted according to the validation results, the model with adjusted parameters is tested using the test set, and the model and parameters with the highest matching degree are selected as the ETL configuration model. The mapping relationship of task keyword groups extracted from the historical data is used as a task rule base; The target ETL task is matched using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results. When the matching degree of the different configuration results is less than or equal to the preset threshold, the feature data of the target ETL task is extracted; wherein, the feature data includes: column data of the source table, table name, whether there is a related table, column name, column type, whether it is not null, whether it belongs to a foreign key and whether it belongs to a primary key; The feature data of the target ETL task is input into a pre-trained ETL configuration model to obtain the configuration result of the target ETL task.

2. The ETL task automatic configuration method according to claim 1, characterized in that, After the step of matching the target ETL task using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results, it also includes: When the matching degree of the different configuration results is greater than the preset threshold, the matching result with the highest matching degree is used as the configuration result of the target ETL task.

3. The ETL task automatic configuration method according to claim 1, characterized in that, The step of collecting the historical data and the feature data in the corresponding configuration results further includes: The collected historical data is cleaned, and the cleaned data is then updated to reflect the historical data.

4. The ETL task automatic configuration method according to claim 3, characterized in that, The steps of cleaning the collected historical data and updating the historical data with the cleaned data specifically include: Obtain column data from the historical data, count duplicate data in the column data, and perform a deduplication step.

5. The automatic configuration method for ETL tasks according to claim 3, characterized in that, Also includes: Obtain column data from the historical source table, and statistically analyze the continuous data, missing values ​​in the continuous data, discrete data, and missing values ​​in the discrete data in the column data. Outliers in the continuous data are removed and treated as missing values. The mean of the data in the column is used to fill the missing values ​​in the continuous variable. If the continuous data in the column is of time series type, the mean of the two data before and after the missing value is used to fill it. The frequency of all numbers in the corresponding column of the discrete data is calculated, and the number with the highest frequency is used to fill the missing value in the corresponding column of the discrete data.

6. A server, characterized in that, The server includes: a memory, a processor, and an ETL task auto-configuration program stored on the memory and capable of running on the processor, wherein the ETL task auto-configuration program, when executed by the processor, implements the ETL task auto-configuration method as described in any one of claims 1 to 5.

7. An automatic ETL task configuration device, characterized in that, The ETL task automatic configuration device includes: The matching degree calculation unit is used to match the target ETL task using a preset task rule library based on keyword group matching, and obtain the matching degree of different configuration results. The feature extraction unit is used to extract feature data of the target ETL task when the matching degree of the different configuration results is less than or equal to a preset threshold; wherein, the feature data includes: column data of the source table, table name, whether there is a related table, column name, column type, whether it is not null, whether it belongs to a foreign key and whether it belongs to a primary key; The ETL configuration unit is used to input the feature data of the target ETL task into a pre-trained ETL configuration model to obtain the configuration result of the target ETL task. Before matching the target ETL task using a preset task rule base based on keyword group matching to obtain the matching degree of different configuration results, the following steps are also included: Collect historical data and corresponding feature data from the configuration results; wherein, the historical data includes data from the historical source table and the target table, and the corresponding configuration results are the configuration results from the source table to the target table; The mapping rules from the historical data to the corresponding configuration results are used as the target variable to construct the dataset; The dataset is divided into a training set, a validation set, and a test set according to a preset ratio; The random forest model is used to learn from the training set data, the validation set data is used to verify the model performance, the model parameters are adjusted according to the validation results, the model with adjusted parameters is tested using the test set, and the model and parameters with the highest matching degree are selected as the ETL configuration model. The mapping relationship of task keyword groups extracted from the historical data is used as a task rule base.

8. A readable storage medium, characterized in that, The readable storage medium stores an ETL task auto-configuration program, which, when executed by a processor, implements the ETL task auto-configuration method according to any one of claims 1 to 5.

Images