Detailed Description
In order to make the objects, technical solutions and advantages of the embodiments of the present invention more apparent, the embodiments of the present invention are further described in detail below with reference to the accompanying drawings. The exemplary embodiments and descriptions of the present invention are provided to explain the present invention, but not to limit the present invention. It should be noted that the embodiments and features of the embodiments in the present application may be arbitrarily combined with each other without conflict.
In order to facilitate understanding of the technical solutions provided in the present application, the following first describes relevant contents of the technical solutions in the present application. In the invention, the number of pieces of data acquired from the source end by the ETL software each time is called the extraction step length. In the prior art, the extraction step length is a static value and is preset. The dynamic adjustment method for the ETL workload provided by the embodiment of the invention dynamically adjusts the extraction step length based on the running environment of the ETL software so as to improve the working efficiency of the ETL software.
Fig. 1 is a schematic flow chart of a method for dynamically adjusting an ETL workload according to an embodiment of the present invention, and as shown in fig. 1, the method for dynamically adjusting an ETL workload according to an embodiment of the present invention includes:
s101, periodically obtaining performance data of the running environment of the ETL software;
specifically, the server may obtain performance data of the operating environment of the ETL software periodically through a performance collection tool, where the performance data includes, but is not limited to, a CPU usage amount of the ETL software, a memory usage amount of the ETL software, a bandwidth usage amount of the ETL software, and a data width corresponding to the ETL software. And the data width corresponding to the ETL software is the data width of a data table in a database processed by the ETL software. The performance tool is pre-set. The period is set according to actual needs, for example, once in 10 seconds, and the embodiment of the invention is not limited.
S102, performing normalization processing on the performance data to obtain characteristic parameters;
specifically, after the server obtains the performance data, the server performs normalization processing on the performance data to obtain a characteristic parameter.
For example, the performance data includes a CPU usage amount of the ETL software, a memory usage amount of the ETL software, a bandwidth usage amount of the ETL software, and a data width corresponding to the ETL software. Performing normalization processing on the performance data comprises calculating and obtaining the CPU utilization rate of the ETL software according to the CPU usage of the ETL software and the total amount of CPUs corresponding to the ETL software; calculating and obtaining the memory utilization rate of the ETL software according to the memory usage of the ETL software and the total memory amount corresponding to the ETL software; calculating and obtaining the bandwidth utilization rate of the ETL software according to the bandwidth usage of the ETL software and the total bandwidth amount corresponding to the ETL software; and calculating to obtain the data enrichment rate of the ETL software according to the data width corresponding to the ETL software and the maximum data width. The CPU utilization rate of the ETL software, the memory utilization rate of the ETL software, the bandwidth utilization rate of the ETL software and the data enrichment rate of the ETL software are used as the characteristic parameters
S103, obtaining a step size value according to the characteristic parameters and the dynamic estimation model; wherein the dynamic estimation model is obtained based on the performance training data training of ETL software;
specifically, after obtaining the characteristic parameters, the server inputs the characteristic parameters into a dynamic estimation model, and outputs a step size value through processing of the dynamic estimation model. Wherein the dynamic estimation model is obtained based on the performance training data training of the ETL software.
And S104, taking the product of the step size numerical value and a preset coefficient as the current extraction step size.
Specifically, after obtaining the step size value, the server calculates a product result of the step size value and a preset coefficient, takes the product result as a current extraction step size, and then updates the extraction step size of the ETL software to the current extraction step size, thereby realizing the dynamic adjustment of the extraction step size of the ETL software at regular intervals. The preset coefficient is set according to an actual situation, and the embodiment of the present invention is not limited.
The dynamic adjustment method for the ETL workload, provided by the embodiment of the invention, regularly obtains the performance data of the running environment of the ETL software, performs normalization processing on the performance data to obtain the characteristic parameters, obtains the step size value according to the characteristic parameters and the dynamic estimation model, and takes the product of the step size value and the preset coefficient as the current extraction step size, thereby realizing the dynamic adjustment of the extraction step size of the ETL software based on the running environment of the ETL software and improving the working efficiency of the ETL software.
Fig. 2 is a schematic flow chart of a method for dynamically adjusting an ETL workload according to another embodiment of the present invention, and as shown in fig. 2, on the basis of the foregoing embodiments, the step of training to obtain a dynamic estimation model based on performance training data of ETL software further includes:
s201, acquiring the performance training data;
specifically, performance acquisition software may be deployed on each server where the ETL software operates, and the CPU usage, the memory usage, the broadband usage, the corresponding data width, the preset extraction step length, and the like when the ETL software operates are acquired as performance raw data, and the performance raw data is subjected to data cleaning to remove abnormal values such as null values and singular values, and then the CPU usage, the memory usage, the broadband usage, the corresponding data width, the preset extraction step length, and the like when the ETL software has a high working efficiency are manually selected as the performance training data. The server may obtain the performance training data. The data size included in the performance training data is set according to actual needs, and the embodiment of the invention is not limited.
S202, performing normalization processing on the performance training data to obtain a feature training set;
specifically, after obtaining the performance training data, the server performs normalization processing on the performance training data to obtain a feature training set.
For example, the performance training data includes the CPU usage of the ETL software, the memory usage of the ETL software, the bandwidth usage of the ETL software, the data width corresponding to the ETL software, and the preset extraction step size of the ETL software. The server calculates the result of dividing the CPU usage of the ETL software by the total CPU of the ETL software operation server to obtain the CPU utilization rate of the ETL software; the server calculates the result of dividing the memory usage of the ETL software by the total memory of the ETL software operation server to obtain the memory usage rate of the ETL software; the server calculates the result of dividing the bandwidth usage of the ETL software by the total bandwidth of the ETL software operating server to obtain the bandwidth utilization rate of the ETL software; the server calculates the result of dividing the data width corresponding to the ETL software by the maximum data width to obtain the data enrichment rate of the ETL software; and the server calculates the extraction step length preset by the ETL software to be divided by the preset value, so as to obtain a step length characteristic value. Each piece of feature training data of the feature training set comprises the CPU utilization rate of the ETL software, the memory utilization rate of the ETL software, the bandwidth utilization rate of the ETL software, the data enrichment rate of the ETL software and the step characteristic value. The CPU utilization rate of the ETL software, the memory utilization rate of the ETL software, the bandwidth utilization rate of the ETL software, the data enrichment rate of the ETL software and the value of the step characteristic value are between 0 and 1. The preset value is set according to practical experience, for example, set to 20 ten thousand, and the embodiment of the present invention is not limited.
S203, obtaining the dynamic estimation model according to the feature training set and the initial model.
Specifically, the server may input the feature training set into an initial model, and train to obtain the dynamic estimation model. Wherein the initial model is preset, and the initial model includes but is not limited to a linear regression model, a deep learning model, and a neural network model.
For example, the initial model employs a linear regression model, represented as [ x ]1,x2,x3,...,xn]×[b1,b2,b3,...,bn]=y,x1,x2,x3,...,xnAs input variables, b1,b2,b3,...,bnIs the weight coefficient and y is the output variable.
The feature training set comprises 4 pieces of feature training data, each piece of feature training data comprises a CPU utilization rate c of ETL software, a memory utilization rate m of ETL software, a bandwidth utilization rate n of ETL software, a data enrichment rate w of ETL software and a step length feature value u, and the 4 pieces of feature data are brought into the linear regression model to obtain the following expression:
solving the above expression to obtain the weight coefficient b1,b2,b3,b4Thereby obtaining the dynamic estimation model.
On the basis of the foregoing embodiments, further, the acquiring the performance training data includes:
and (4) performing data cleaning on the performance original data to remove abnormal values.
Specifically, in the process of acquiring the performance training data, the server performs data cleaning on the acquired performance raw data to remove abnormal values in the performance raw data, such as null values and singular values, where the singular values refer to values outside a normal range.
On the basis of the foregoing embodiments, further, the performance data includes a CPU usage amount of the ETL software, a memory usage amount of the ETL software, a bandwidth usage amount of the ETL software, and a data width corresponding to the ETL software; correspondingly, the normalizing the performance data to obtain the characteristic parameters includes:
calculating and obtaining the CPU utilization rate of the ETL software according to the CPU usage of the ETL software and the total amount of CPUs corresponding to the ETL software;
calculating and obtaining the memory utilization rate of the ETL software according to the memory usage of the ETL software and the total memory amount corresponding to the ETL software;
calculating and obtaining the bandwidth utilization rate of the ETL software according to the bandwidth usage of the ETL software and the total bandwidth amount corresponding to the ETL software;
and calculating to obtain the data enrichment rate of the ETL software according to the data width corresponding to the ETL software and the maximum data width.
Specifically, the performance data may include a CPU usage amount of the ETL software, a memory usage amount of the ETL software, a bandwidth usage amount of the ETL software, and a data width corresponding to the ETL software.
And the server calculates the quotient of the CPU usage of the ETL software and the total CPU amount corresponding to the ETL software, so as to realize the normalization processing of the CPU usage of the ETL software, wherein the quotient is used as the CPU utilization rate of the ETL software. The total amount of the CPU corresponding to the ETL software is the total amount of the CPU of the ETL software running server, and is obtained in advance.
And the server calculates the quotient of the memory usage of the ETL software and the total memory amount corresponding to the ETL software, so as to realize the normalization processing of the memory usage of the ETL software, wherein the quotient is used as the memory usage rate of the ETL software. The total amount of the memory corresponding to the ETL software is the total amount of the memory of the ETL software running server, and is obtained in advance.
And the server calculates the quotient of the bandwidth usage of the ETL software and the total bandwidth amount corresponding to the ETL software, so as to realize the normalization processing of the bandwidth usage of the ETL software, wherein the quotient is used as the bandwidth utilization rate of the ETL software. The bandwidth total amount corresponding to the ETL software is the bandwidth total amount of the ETL software running server and is obtained in advance.
And the server calculates a quotient value of the data width corresponding to the ETL software and the maximum data width to realize the normalization processing of the data width corresponding to the ETL software, wherein the quotient value is used as the data enrichment rate of the ETL software. The maximum data width is preset and is set according to practical experience, and the embodiment of the invention is not limited.
And the CPU utilization rate of the ETL software, the memory utilization rate of the ETL software, the bandwidth utilization rate of the ETL software and the data enrichment rate of the ETL software are used as the characteristic parameters.
Further, after obtaining the CPU utilization C of the ETL software, the memory utilization M of the ETL software, the bandwidth utilization N of the ETL software, and the data enrichment W of the ETL software, the server inputs the above feature parameters into a dynamic estimation model obtained by training a linear regression model, as shown in the following formula:
[C,M,N,W]×[b1,b2,b3,b4]=U
and obtaining a step size value U through calculation, and multiplying the step size value U by a preset coefficient k to obtain the current extraction step size kU. Wherein k is equal to the preset value.
According to the dynamic adjustment method of the ETL workload, provided by the embodiment of the invention, the performance data of the running environment of the ETL software and the corresponding extraction step length are analyzed by a machine learning method to obtain a dynamic estimation model, then the dynamic estimation model is embedded into the ETL software, the current extraction step length is obtained by periodically collecting the performance data of the running environment of the ETL software and the dynamic estimation model in the running process of the ETL software, and the current extraction step length is configured to the ETL software, so that the working efficiency of the ELT software is improved. By periodically calculating the extraction step length, the dynamic configuration of the extraction step length can be realized.
Fig. 3 is a schematic structural diagram of an apparatus for dynamically adjusting an ETL workload according to an embodiment of the present invention, and as shown in fig. 3, the apparatus for dynamically adjusting an ETL workload according to an embodiment of the present invention includes an obtaining unit 301, a first processing unit 302, an estimating unit 303, and a calculating unit 304, where:
the obtaining unit 301 is configured to periodically obtain performance data of an operating environment of the ETL software; the first processing unit 302 is configured to perform normalization processing on the performance data to obtain a characteristic parameter; the estimating unit 303 is configured to obtain a step size value according to the feature parameter and the dynamic estimation model; wherein the dynamic estimation model is obtained based on the performance training data training of ETL software; the calculating unit 304 is configured to calculate a product of the step value and a preset coefficient as a current decimation step.
Specifically, the obtaining unit 301 may obtain, periodically through a performance collecting tool, performance data of an operating environment of the ETL software, where the performance data includes, but is not limited to, a CPU usage amount of the ETL software, a memory usage amount of the ETL software, a bandwidth usage amount of the ETL software, and a data width corresponding to the ETL software. And the data width corresponding to the ETL software is the data width of a data table in a database processed by the ETL software. The performance tool is pre-set. The period is set according to actual needs, for example, once in 10 seconds, and the embodiment of the invention is not limited.
After obtaining the performance data, the first processing unit 302 performs normalization processing on the performance data to obtain a feature parameter.
After obtaining the characteristic parameters, the estimating unit 303 inputs the characteristic parameters into a dynamic estimation model, and outputs a step value through processing of the dynamic estimation model. Wherein the dynamic estimation model is obtained based on the performance training data training of the ETL software.
After obtaining the step size value, the calculating unit 304 calculates a result of multiplying the step size value by a preset coefficient, takes the result of multiplying as a current extraction step size, and then updates the extraction step size of the ETL software to the current extraction step size, thereby implementing the dynamic adjustment of the extraction step size of the ETL software at regular intervals. The preset coefficient is set according to an actual situation, and the embodiment of the present invention is not limited.
The dynamic adjustment device for the ETL workload provided by the embodiment of the invention periodically obtains the performance data of the running environment of the ETL software, performs normalization processing on the performance data to obtain the characteristic parameters, obtains the step size value according to the characteristic parameters and the dynamic estimation model, and takes the product of the step size value and the preset coefficient as the current extraction step size, thereby realizing the dynamic adjustment of the extraction step size of the ETL software based on the running environment of the ETL software and improving the working efficiency of the ETL software.
Fig. 4 is a schematic structural diagram of an ETL workload dynamic adjustment apparatus according to another embodiment of the present invention, and as shown in fig. 4, on the basis of the foregoing embodiments, further, the ETL workload dynamic adjustment apparatus according to the embodiment of the present invention further includes an obtaining unit 305, a second processing unit 306, and a training unit 307, where:
the obtaining unit 305 is configured to obtain the performance training data; the second processing unit 306 is configured to perform normalization processing on the performance training data to obtain a feature training set; the training unit 307 is configured to obtain the dynamic estimation model according to the feature training set and the initial model.
Specifically, performance acquisition software may be deployed on each server where the ETL software operates, and the CPU usage, the memory usage, the broadband usage, the corresponding data width, the preset extraction step length, and the like when the ETL software operates are acquired as performance raw data, and the performance raw data is subjected to data cleaning to remove abnormal values such as null values and singular values, and then the CPU usage, the memory usage, the broadband usage, the corresponding data width, the preset extraction step length, and the like when the ETL software has a high working efficiency are manually selected as the performance training data. The acquisition unit 305 may acquire the performance training data. The data size included in the performance training data is set according to actual needs, and the embodiment of the invention is not limited.
After obtaining the performance training data, the second processing unit 306 performs normalization processing on the performance training data to obtain a feature training set.
The training unit 307 may input the feature training set into an initial model, and train to obtain the dynamic estimation model. Wherein the initial model is preset, and the initial model includes but is not limited to a linear regression model, a deep learning model, and a neural network model.
On the basis of the foregoing embodiments, further, the obtaining unit 305 is specifically configured to:
and (4) carrying out data cleaning on the original data to remove abnormal values.
Specifically, in the process of acquiring the performance training data, the acquiring unit 305 performs data cleaning on the acquired performance raw data to remove abnormal values in the performance raw data, such as null values and singular values, where the singular values refer to values out of a normal range.
On the basis of the foregoing embodiments, further, the performance data includes a CPU usage amount of the ETL software, a memory usage amount of the ETL software, a bandwidth usage amount of the ETL software, and a data width corresponding to the ETL software; correspondingly, the first processing unit 302 is specifically configured to:
calculating and obtaining the CPU utilization rate of the ETL software according to the CPU usage of the ETL software and the total amount of CPUs corresponding to the ETL software; calculating and obtaining the memory utilization rate of the ETL software according to the memory usage of the ETL software and the total memory amount corresponding to the ETL software; calculating and obtaining the bandwidth utilization rate of the ETL software according to the bandwidth usage of the ETL software and the total bandwidth amount corresponding to the ETL software; and calculating to obtain the data enrichment rate of the ETL software according to the data width corresponding to the ETL software and the maximum data width.
Specifically, the performance data may include a CPU usage amount of the ETL software, a memory usage amount of the ETL software, a bandwidth usage amount of the ETL software, and a data width corresponding to the ETL software.
The first processing unit 302 calculates a quotient of the CPU usage of the ETL software and the total CPU amount corresponding to the ETL software, so as to realize normalization processing of the CPU usage of the ETL software, where the quotient is used as the CPU utilization of the ETL software. The total amount of the CPU corresponding to the ETL software is the total amount of the CPU of the ETL software running server, and is obtained in advance.
The first processing unit 302 calculates a quotient of the memory usage of the ETL software and the total memory amount corresponding to the ETL software, so as to realize normalization processing of the memory usage of the ETL software, where the quotient is used as the memory usage rate of the ETL software. The total amount of the memory corresponding to the ETL software is the total amount of the memory of the ETL software running server, and is obtained in advance.
The first processing unit 302 calculates a quotient of the bandwidth usage of the ETL software and the total bandwidth amount corresponding to the ETL software, so as to realize normalization processing of the bandwidth usage of the ETL software, where the quotient is used as the bandwidth utilization of the ETL software. The bandwidth total amount corresponding to the ETL software is the bandwidth total amount of the ETL software running server and is obtained in advance.
The first processing unit 302 calculates a quotient of the data width corresponding to the ETL software and the maximum data width, and implements normalization processing on the data width corresponding to the ETL software, where the quotient is used as a data enrichment rate of the ETL software. The maximum data width is preset and is set according to practical experience, and the embodiment of the invention is not limited.
And the CPU utilization rate of the ETL software, the memory utilization rate of the ETL software, the bandwidth utilization rate of the ETL software and the data enrichment rate of the ETL software are used as the characteristic parameters.
The embodiment of the apparatus provided in the embodiment of the present invention may be specifically configured to execute the processing flows of the above method embodiments, and the functions of the apparatus are not described herein again, and refer to the detailed description of the above method embodiments.
It should be noted that the method and the device for dynamically adjusting the ETL workload provided by the embodiment of the present invention can be used in the financial field and can also be used in any technical field except the financial field.
Fig. 5 is a schematic physical structure diagram of an electronic device according to an embodiment of the present invention, and as shown in fig. 5, the electronic device may include: a processor (processor)501, a communication Interface (Communications Interface)502, a memory (memory)503, and a communication bus 504, wherein the processor 501, the communication Interface 502, and the memory 503 are configured to communicate with each other via the communication bus 504. The processor 501 may call logic instructions in the memory 503 to perform the following method: periodically obtaining performance data of the running environment of the ETL software; normalizing the performance data to obtain characteristic parameters; obtaining a step length value according to the characteristic parameters and a dynamic estimation model; wherein the dynamic estimation model is obtained based on the performance training data training of ETL software; and calculating the product of the step length value and a preset coefficient to serve as the current extraction step length.
In addition, the logic instructions in the memory 503 may be implemented in the form of software functional units and stored in a computer readable storage medium when the logic instructions are sold or used as independent products. Based on such understanding, the technical solution of the present invention may be embodied in the form of a software product, which is stored in a storage medium and includes instructions for causing a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present invention. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.
The present embodiment discloses a computer program product comprising a computer program stored on a non-transitory computer readable storage medium, the computer program comprising program instructions which, when executed by a computer, enable the computer to perform the method provided by the above-mentioned method embodiments, for example, comprising: periodically obtaining performance data of the running environment of the ETL software; normalizing the performance data to obtain characteristic parameters; obtaining a step length value according to the characteristic parameters and a dynamic estimation model; wherein the dynamic estimation model is obtained based on the performance training data training of ETL software; and calculating the product of the step length value and a preset coefficient to serve as the current extraction step length.
The present embodiment provides a computer-readable storage medium, which stores a computer program, where the computer program causes the computer to execute the method provided by the above method embodiments, for example, the method includes: periodically obtaining performance data of the running environment of the ETL software; normalizing the performance data to obtain characteristic parameters; obtaining a step length value according to the characteristic parameters and a dynamic estimation model; wherein the dynamic estimation model is obtained based on the performance training data training of ETL software; and calculating the product of the step length value and a preset coefficient to serve as the current extraction step length.
As will be appreciated by one skilled in the art, embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
In the description herein, reference to the description of the terms "one embodiment," "a particular embodiment," "some embodiments," "for example," "an example," "a particular example," or "some examples," etc., means that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the invention. In this specification, the schematic representations of the terms used above do not necessarily refer to the same embodiment or example. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.
The above-mentioned embodiments are intended to illustrate the objects, technical solutions and advantages of the present invention in further detail, and it should be understood that the above-mentioned embodiments are only exemplary embodiments of the present invention, and are not intended to limit the scope of the present invention, and any modifications, equivalent substitutions, improvements and the like made within the spirit and principle of the present invention should be included in the scope of the present invention.