Method for improving Kettle conversion efficiency based on k-means optimization algorithm

Abstract

The present invention discloses a method for improving the Kettle conversion efficiency based on an optimized k-means algorithm. The method preprocesses the execution time of Kettle conversion steps, uses the maximum-minimum normalization algorithm to standardize the data, and obtains a set of standardized data; groups are generated through the optimized k-means algorithm, the number of groups is generated, and the centroid is selected in a non-random manner. The present invention dynamically records the time of Kettle conversion steps, uses an improved k-means algorithm to group the steps, automatically manages parameters and expands threads, improves the conversion efficiency of Kettle, and reduces the time for manual parameter adjustment. Compared with not using this method, the conversion efficiency is increased by 20%.

Description

Technical Field

[0001] The present invention relates to the technical field of data extraction and data mining, and specifically provides a method for improving the Kettle conversion efficiency based on the k-means optimization algorithm. Background Art

[0002] With the development and popularization of computers in various fields, the total amount of information generated and processed in all walks of life is growing exponentially. However, people's ability to analyze data is limited. More and more useless information makes it difficult to analyze enterprise information and make correct decisions. Under this premise, the data warehouse technology has emerged.

[0003] ETL describes the process of extracting data from the source end, transforming it, and loading it to the destination end, which is an important process for building a data warehouse. Kettle is a free and open-source ETL tool that can complete basic ETL operations, extract the required data from various data sources, and complete subsequent operations of transformation and loading. However, if the same method is used to extract and transform data with different data sources, different table structures, and different orders of magnitude, it will surely cause a decrease in the execution efficiency of Kettle. Moreover, blindly stacking hardware resources (such as memory, etc.) is an act of low efficiency and resource waste. Summary of the Invention

[0004] In order to handle different extraction and transformation tasks, it is necessary to find a better method to improve the extraction and transformation efficiency of Kettle without stacking hardware resources, and to solve the bottleneck problem of the transformation efficiency of different Kettle transformation tasks in the existing scenarios.

[0005] In view of the above problems and technologies, the present invention provides the following technical solutions:

[0006] A method for improving the Kettle conversion efficiency based on the k-means optimization algorithm. The method preprocesses the execution time of the Kettle conversion steps, uses the maximum-minimum normalization algorithm to standardize the data, and obtains a set of standardized data;

[0007] Groups are generated through the optimized algorithm of k-means to obtain the number of groups, and the centroids are selected in a non-random manner;

[0008] Among them, the formula of the maximum-minimum normalization algorithm is:

[0009] Final data = (Original data - Minimum data) / (Maximum data - Minimum data).

[0010] Through the maximum - minimum normalization algorithm, it is ensured that all data is within a relatively ideal range, thus guaranteeing that there are no outliers.

[0011] Furthermore, the process of determining the number of groups is as follows:

[0012] In the area where the samples are located, randomly generate as many random samples as the original number of samples according to a uniform distribution, and perform K - Means processing on this random sample, thus obtaining a D k

[0013] Repeat the above steps n times to obtain n log D k ;

[0014] Calculate the average of these n values to obtain an approximation of E(log D k );

[0015] Using the Gap statistic method, the calculation formula is as follows:

[0016] Gap(K) = E(log D k ) - log D k

[0017] where D k is the loss function, and E(log D k ) is the expectation of log D k ; this value is usually generated through Monte Carlo simulation

[0018] The K corresponding to the maximum value of the Gap statistic is the optimal number of groups K.

[0019] Furthermore, the process of selecting the centroid is as follows:

[0020] 1) First, randomly select a point from all the data point sets as the centroid of the first group;

[0021] 2) For each point x in the data set, calculate its distance D(x) from the nearest cluster center (referring to the already selected cluster centers);

[0022] 3) Select a new data point as the new cluster center. The selection principle is: the points with larger D(x) have a greater probability of being selected as the cluster center;

[0023] 4) Repeat steps 2) and 3) until k cluster centers are selected;

[0024] 5) Use these k initial cluster centers to run the standard k - means algorithm.

[0025] Furthermore, the calculation process of the distance D(x) is as follows:

[0026] 1) First, randomly select a random point from the database as the "seed point";

[0027] 2) For each point, calculate its distance D(x) to the nearest "seed point" and save it in an array, and then add up these distances to get Sum(D(x));

[0028] 3) Take a random value and use the weight method to calculate the next "seed point". The specific algorithm is as follows:

[0029] First, take a random value Random that can fall within Sum(D(x)), and then use Random -= D(x) until it <= 0. At this time, the point is the next "seed point".

[0030] Furthermore, the implementation process of the method further includes:

[0031] After the number of groups is generated and the non-random selection of the centroid, the points closer to each centroid are divided into each group to complete the grouping;

[0032] After the grouping is completed, the execution time range of each group and the number of steps in each group can be visually seen.

[0033] Furthermore, the implementation process of the method further includes:

[0034] By presetting different numbers of threads corresponding to each group, and then setting the copy number by calling the setCopiesString method of the StepMeta class, the number of threads for each step in the group is increased;

[0035] By calling the addParameterDefinition method of the TransMeta class to add named parameters, the execution time of each step is dynamically collected, and automatic grouping is performed based on the collected information, and appropriate parameters and the number of threads are set to improve the conversion efficiency of Kettle.

[0036] Furthermore, the implementation process of the method further includes:

[0037] In this process, it may occur that the step metric of a certain step is much longer than that of other steps. We can also issue a warning for this type of data because this type of data increases the overall dispersion of the data and will increase the number of iterations of k-means, thus slowing down the algorithm. For this type of step metric, the long step should be split into multiple short steps to increase concurrency and speed up the conversion efficiency of Kettle; if it is a problem with the sql execution efficiency, then the explain should be considered to analyze where the sql execution is slow.

[0038] Further, the implementation process of the method further includes: modifying Kettle parameters and optimizing performance by adjusting the size of the JVM. The JVM parameters include:

[0039] The maximum available memory of the JVM, the size of the young generation of the JVM, and the stack size of each thread;

[0040] Modify the commit size of Kettle's own batch processing to around 10,000.

[0041] Adjusting to an appropriate size can generate more threads with the same memory size, thereby improving concurrency. The commit size of Kettle's own batch processing is 1,000. After testing, modifying it to around 10,000 can effectively improve the execution efficiency.

[0042] Further, the method uses the merge statement to compare the source table and the local table, updates the data that exists in the local table but does not match, and inserts the data that does not exist in the local table, thereby completing the incremental update, reducing resource consumption, and improving the extraction and transformation efficiency.

[0043] After completing the modification of the above Kettle parameters, it will be found that there are still performance bottlenecks for some extraction and transformation tasks. At this time, we should add a thread to monitor the extraction and transformation steps.

[0044] Further, the method monitors the extraction and transformation steps by adding a thread. The monitoring thread can obtain the step metrics (i.e., the specific execution time of each step) from the Kettle log based on the thread names of these steps. The implementation process is as follows:

[0045] 1) Clear the parameter settings of each step;

[0046] 2) Perform a normal data extraction and transformation. The monitoring thread obtains all the step names of the transformation through getSteps();

[0047] 3) Obtain the transformation name through getName();

[0048] 4) After obtaining the step names and the transformation name, the thread name corresponding to each step can be obtained.

[0049] At this time, the monitoring thread can obtain the step metrics (i.e., the specific execution time of each step) from the Kettle log based on the thread names of these steps. After obtaining, record them in a text document.

[0050] Compared with the prior art, the method for improving the Kettle conversion efficiency based on the k-means optimization algorithm of the present invention has the following outstanding beneficial effects:

[0051] The present invention dynamically records the time of Kettle conversion steps, uses an improved k-means algorithm to group the steps, automatically manages parameters and expands threads, improves the conversion efficiency of Kettle, and reduces the time for manual parameter adjustment. Compared with not using this method, the conversion efficiency is increased by 20%. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] Figure 1 is a schematic diagram of thread monitoring of the method of the present invention;

[0053] Figure 2 is a schematic diagram of Gap Statisitc calculation. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0054] The present invention will be further described in detail below with reference to the drawings and embodiments.

[0055] A method for improving the Kettle conversion efficiency based on the k-means optimization algorithm, and the implementation process of the method is as follows:

[0056] 1. Optimization of Kettle itself:

[0057] The commit size of the batch processing of Kettle itself is 1000. After testing, modifying it to about 10000 can effectively improve the execution efficiency, and it can be adjusted according to specific situations;

[0058] In Kettle, the commonly used JVM parameters are: the maximum available memory of JVM, the size of the young generation of JVM, and the stack size of each thread; adjusting the size of JVM for performance optimization, adjusting it to an appropriate size can generate more threads without changing the memory size, thereby improving concurrency, and can be configured as OPT=-Xmx1024m-Xms512m.

[0059] At the same time, corresponding optimizations can also be made for the extraction steps. For example, a task of an extraction conversion is: 1. Full extraction from the source table, 2. Delete the local table, 3. Perform specific data extraction; if the incremental sql is not restricted in the third step, then each time data extraction and conversion are performed, full extraction is carried out, which will cause a large amount of resource waste. In fact, only the transformed data needs to be modified and updated, and the newly added data in the source table needs to be added. The merge statement can be used in the third step to compare the source table and the local table, update the data that exists in the local table but does not match, and add the data that does not exist in the local table, so as to complete the incremental update, reduce resource consumption, and improve the extraction conversion efficiency.

[0060] 2. Add a monitoring thread to obtain the execution time of steps

[0061] After completing the modification of the parameters of Kettle itself as described above, there are still performance bottlenecks for some extraction and transformation tasks. At this time, a thread should be added to monitor the steps of extraction and transformation, as follows:

[0062] First, clear the parameter settings of each step, and then perform a normal data extraction and transformation. At this time, the monitoring thread obtains the names of all steps of the transformation through getSteps();

[0063] Then obtain the transformation name through getName(); after obtaining the step name and the transformation name, the thread name corresponding to each step can be obtained. At this time, the monitoring thread can obtain the step metrics (i.e., the specific execution time of each step) from the Kettle log based on these step thread names. After obtaining, record them in a text document, as Figure 1 shown.

[0064] 3. Group through the optimized algorithm of k-means

[0065] The K-means algorithm is a very typical distance-based clustering algorithm. It uses distance as the evaluation index of similarity, that is, it is considered that the closer the distance between two objects, the greater their similarity. This algorithm believes that clusters are composed of objects with close distances, so the ultimate goal is to obtain compact and independent clusters. Its algorithm process is roughly as follows: 1. Randomly select N centroids; 2. Calculate which group each data point in the dataset belongs to the centroid of, and cluster all data points in the dataset into N groups; 3. According to the N groups of data points calculated in the previous step, calculate a new centroid respectively; 4. Repeat steps 2-3 until the distance change between the calculated centroid and the centroid calculated in the previous time is very small (meeting the specified threshold, or converging); 5. Read in all observations again, and classify each observation into the class where the centroid with the closest distance is located, and the classification ends.

[0066] The K-means algorithm has some obvious disadvantages: that is, the number of cluster classes needs to be specified manually, and different numbers of cluster classes will lead to different classifications; the centroids are randomly selected at the beginning, there is uncertainty, and a bad random centroid selection may lead to a longer execution time of the algorithm, that is, it is sensitive to centroids; it is sensitive to outliers. Here, some improvements to the k-means algorithm are proposed in combination with the Kettle transformation steps. The main directions of improvement are the automatic generation of the number of groups; the non-random selection of centroids.

[0067] The discreteness of the execution time of Kettle transformation steps is relatively low, but there may still be cases where the time of a step is much longer than that of other steps. Based on the above situation, the step time data should be preprocessed first. Here, the maximum-minimum normalization algorithm is used for data standardization. The formula of the maximum-minimum normalization algorithm is:

[0068] Final data = (Original data - Minimum data) / (Maximum data - Minimum data),

[0069] Through the maximum-minimum normalization algorithm, it is ensured that all data is within a relatively ideal range, thus ensuring that there are no outliers.

[0070] After processing by the maximum-minimum normalization algorithm, we obtain a set of standardized data. At this time, we automatically generate the number of groups for the data. When performing the automatic generation, we use the Gap statistic method. The formula of the Gap statistic method is as follows: where D_k is the loss function, and here refers to the expectation of. This value is usually generated by Monte Carlo simulation. We randomly generate as many random samples as the original sample number in the area where the samples are located according to a uniform distribution, and perform K-Means on this random sample, so as to obtain a D_k. Repeat this 20 times, and we can get 20 Taking the average of these 20 values, we get the approximate value. Finally, the Gap Statisitc can be calculated. And the K corresponding to the maximum value of the Gap statistic is the best K. As Figure 2 shown, when K = 3, the value of Gap(K) is the largest, so the best number of clusters is K = 3.

[0071] After determining the number of groups, we then improve the selection of the initial values of the centroids for each group; the improvement directions are: 1. First, randomly select a point from all the data point sets as the centroid of the first group; 2. For each point x in the data set, calculate its distance D(x) from the nearest clustering center (referring to the already selected clustering centers); 3. Select a new data point as the new clustering center. The selection principle is: the points with larger D(x) have a greater probability of being selected as the clustering center; 4. Repeat steps 2 and 3 until k clustering centers are selected; 5. Use these k initial clustering centers to run the standard k-means algorithm.

[0072] The most critical point is the third step, calculating the distance D(x). This method proposes an algorithm: 1. First, randomly select a random point from our database as a "seed point"; 2. For each point, we calculate the distance D(x) between it and the nearest "seed point" and save it in an array, then add up these distances to get Sum(D(x)); 3. Then, take another random value and use a weighted method to calculate the next "seed point". The implementation of this algorithm is to first take a random value Random that falls within Sum(D(x)), then use Random-=D(x) until it is <= 0. The point at this time is the next "seed point".

[0073] After the number of groups is automatically generated and the centroids are non-randomly selected, the points closest to each centroid are divided into groups. At this point, we have completed the grouping. After the grouping is completed, we can intuitively see the execution time range of each group and the number of steps in each group. At this time, you can preset the number of threads corresponding to each group, and then set the number of copies by calling the setCopiesString method of the StepMeta class to increase the number of threads for each step in the group; you can add named parameters by calling the addParameterDefinition method of the TransMeta class. This method dynamically collects the execution time of each step, automatically groups based on the collected information, and sets appropriate parameters and number of threads, thereby improving Kettle's conversion efficiency.

[0074] During this process, the step metric for a particular step may be significantly longer than that for other steps. Warnings should be issued for such data, as this increases the overall data dispersion and the number of k-means iterations, slowing the algorithm. For such step metrics, split the long step into multiple shorter steps to increase concurrency and speed up Kettle's conversion efficiency. If the issue is SQL execution efficiency, use explain to analyze where the SQL execution is slow.

[0075] The embodiments described above are only preferred specific implementations of the present invention. Common changes and substitutions made by those skilled in the art within the scope of the technical solution of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for improving Kettle conversion efficiency based on the k-means optimization algorithm, characterized in that: The method preprocesses the execution time of the Kettle conversion step and uses the maximum and minimum normalization algorithm to standardize the data to obtain a set of standardized data; The k-means optimization algorithm is used to group the groups, generate the number of groups, and select the centroid in a non-random way; Among them, the formula of the maximum and minimum normalization algorithm is: Final data = (original data - minimum data) / (maximum data - minimum data); The process of determining the number of groups is as follows: In the area where the sample is located, random samples as many as the original number of samples are randomly generated according to uniform distribution, and the random samples are processed by K-Means to obtain a D k Repeat the above steps n times to get n log D k ; Take the average of these n values and get E(log D k ) approximate value; Using the Gap statistic method, the calculation formula is as follows: Gap(K)=E(log D k )-log D k Among them D k is the loss function, E(log D k ) is log D k expectations; The K corresponding to the maximum value of Gap statistic is the optimal number of groups K; The centroid selection process is as follows: 1) First, randomly select a point from the set of all data points as the centroid of the first group; 2) For each point x in the data set, calculate the distance D(x) between it and the nearest cluster center; 3) Select a new data point as the new cluster center. The selection principle is: the point with larger D(x) has a greater probability of being selected as the cluster center; 4) Repeat 2) and 3) until k cluster centers are selected; 5) Use these k initial cluster centers to run the standard k-means algorithm; The calculation process of the distance D(x) is as follows: 1) First, randomly select a random point from the database as the "seed point"; 2) For each point, calculate the distance D(x) from the nearest "seed point" and save it in an array, then add up these distances to get Sum(D(x)); 3) Take a random value and use the weight method to calculate the next "seed point". The specific algorithm is as follows: First, take a random value Random that falls within Sum(D(x)), then use Random-=D(x) until it is <= 0. The point at this time is the next "seed point"; The implementation process of the method further includes: After the number of groups is generated and the centroids are non-randomly selected, the points closest to each centroid are divided into each group to complete the grouping; After the grouping is completed, you can intuitively see the execution time range of each group and the number of steps in each group; The implementation process of the method further includes: By presetting the number of threads for each group, and then setting the number of copies by calling the setCopiesString method of the StepMeta class, the number of threads for each step in the group can be increased. By calling the addParameterDefinition method of the TransMeta class to add named parameters, the execution time of each step is dynamically collected, and based on the collected information, the execution time is automatically grouped and the appropriate parameters and number of threads are set to improve the conversion efficiency of Kettle. The implementation process of the method further includes: For a step whose step measurement is much longer than that of other steps, split this long step into multiple short steps, which increases concurrency in disguise and speeds up Kettle's conversion efficiency.

2. The method for improving Kettle conversion efficiency based on the k-means optimization algorithm according to claim 1, characterized in that: The implementation process of the method further includes: modifying Kettle parameters to optimize performance by adjusting the size of the JVM, wherein the JVM parameters include: JVM maximum available memory, JVM young generation size, and stack size for each thread; Change the commit size of Kettle's own batch to 10000.

3. The method for improving Kettle conversion efficiency based on the k-means optimization algorithm according to claim 2, characterized in that: The method uses a merge statement to compare the source table and the local table, updates the data that exists in the local table but does not match, and adds the data that does not exist in the local table, thereby completing the incremental update.

4. The method for improving Kettle conversion efficiency based on the k-means optimization algorithm according to claim 3, characterized in that: The method adds a thread to monitor the extraction and conversion steps. The monitoring thread can obtain step metrics in the Kettle log based on the thread names of these steps. The implementation process is as follows: 1) Clear the parameter settings of each step; 2) Perform a normal data extraction and conversion, and the monitoring thread obtains all the step names of the conversion through getSteps(); 3) Get the conversion name through getName(); 4) After obtaining the step name and transformation name, you can get the thread name corresponding to each step.

Images