Invention content
For the defects in the prior art, a kind of ETL tasks automatic processing method of present invention offer and device, energy of the present invention
Enough effectively liberation manpowers, save human time's cost, while improving efficiency.
To achieve the above object, the present invention provides following technical scheme:
In a first aspect, the present invention provides a kind of ETL tasks automatic processing methods, including:
By from source database and LOG log collections to data be put into STG layer data tables;
Whether detection STG layer datas table has corresponding ODS layer datas table, if not having, creates corresponding ODS layer datas
Table, if so, whether the data sheet field then detected in STG layer data tables has corresponding field in ODS layer data tables, if not having
Have, then supplements corresponding field in ODS layer data tables.
Further, when creating corresponding ODS layer datas table, the table name of ODS layer data tables follows following Naming conventions:
Data Layer name _ source library name _ source table name _ renewal frequency and extraction mode.
Further, when supplementing corresponding field in ODS layer data tables, field name will in normal data dictionary
Field name is consistent, wherein includes following field in normal data dictionary:STG layers of table name, STG layers of literary name section English
Literary fame, STG layers of literary name section Chinese name, STG layers of literary name section data type, ODS layers of table name, ODS layers of literary name section standard English name,
ODS layers of literary name section standard Chinese name, ODS layers of literary name section standard data type and ODS layers of literary name section default value.
Further, the method further includes:Data are transferred to ODS layer data tables from STG layer data tables.
Further, the method further includes:When there are ETL mission failures, judge whether failure cause is ETL scheduling
System is unstable, is retried if so, ETL task tune is risen, and otherwise determines whether that programmer changes ETL tasks and causes, if so,
ETL tasks are then rolled back into the task version before modification, to ensure the normal execution of ETL tasks, and send out mail notification correspondence
Otherwise the responsible person of ETL tasks, the rollback of task version judge whether the ETL tasks to fail are that data assets grade is high
It is more than the ETL tasks of the second predetermined threshold value in the first predetermined threshold value or downstream influences range, if so, prompt message is sent out, with
O&M operator on duty's timely processing is prompted, the failed tasks are otherwise preserved, to wait for that operation maintenance personnel or ETL responsible person are normally going to work
Time-triggered protocol.
Second aspect, the present invention also provides a kind of ETL tasks automatic processing devices, including:
Shift module, for by from source database and LOG log collections to data be put into STG layer data tables;
Detection module, for detecting whether STG layer datas table has corresponding ODS layer datas table, if not having, establishment pair
The ODS layer data tables answered, if so, whether the data sheet field for then detecting in STG layer data tables has pair in ODS layer data tables
The field answered supplements corresponding field if not having in ODS layer data tables.
Further, when creating corresponding ODS layer datas table, the table name of ODS layer data tables follows the detection module
Following Naming conventions:Data Layer name _ source library name _ source table name _ renewal frequency and extraction mode.
Further, when the detection module supplements corresponding field in ODS layer data tables, field name is wanted and standard
Field name in data dictionary is consistent, wherein includes following field in normal data dictionary:STG layers of table name,
STG layers of literary name section English name, STG layers of literary name section Chinese name, STG layers of literary name section data type, ODS layers of table name, ODS layers of literary name section
Standard English name, ODS layers of literary name section standard Chinese name, ODS layers of literary name section standard data type and ODS layers of literary name section default value.
The third aspect, the present invention also provides a kind of electronic equipment, including memory, processor and storage are on a memory
And the computer program that can be run on a processor, the processor realize ETL as described in relation to the first aspect when executing described program
The step of task automatic processing method.
Fourth aspect, the present invention also provides a kind of computer readable storage mediums, are stored thereon with computer program, should
The step of ETL tasks automatic processing method as described in relation to the first aspect is realized when computer program is executed by processor.
As shown from the above technical solution, ETL tasks automatic processing method provided by the invention, first will from source database and
LOG log collections to data be put into STG layer data tables, then detect STG layer datas table whether have the corresponding ODS numbers of plies
According to table, if not having, corresponding ODS layer datas table is created, if so, then detecting data sheet field in STG layer data tables in ODS
Whether there is corresponding field in layer data table, if not having, corresponding field is supplemented in ODS layer data tables.As it can be seen that this hair
It is bright to realize automatically processing for ETL tasks, so as to effectively liberate manpower, human time's cost is saved, is improved simultaneously
Efficiency.
Specific implementation mode
In order to make the object, technical scheme and advantages of the embodiment of the invention clearer, below in conjunction with the embodiment of the present invention
In attached drawing, technical solution in the embodiment of the present invention carries out clear, complete description, it is clear that described embodiment is
A part of the embodiment of the present invention, instead of all the embodiments.Based on the embodiments of the present invention, those of ordinary skill in the art
The every other embodiment obtained without creative efforts, shall fall within the protection scope of the present invention.
One embodiment of the invention provides a kind of ETL tasks automatic processing method, and referring to Fig. 1 and Fig. 2, this method includes such as
Lower step:
Step 101:By from source database and LOG log collections to data be put into STG layer data tables.
Step 102:Whether detection STG layer datas table has corresponding ODS layer datas table, if not having, create corresponding
ODS layer data tables, if so, the data sheet field for then detecting in STG layer data tables whether have in ODS layer data tables it is corresponding
Field supplements corresponding field if not having in ODS layer data tables.
As seen from the above description, ETL tasks automatic processing method provided in this embodiment, first will from source database and
LOG log collections to data be put into STG layer data tables, then detect STG layer datas table whether have the corresponding ODS numbers of plies
According to table, if not having, corresponding ODS layer datas table is created, if so, then detecting data sheet field in STG layer data tables in ODS
Whether there is corresponding field in layer data table, if not having, corresponding field is supplemented in ODS layer data tables.As it can be seen that this reality
Automatically processing for ETL tasks can be realized by applying example, so as to effectively liberate manpower, saved human time's cost, improved simultaneously
Efficiency.
In a preferred embodiment, when creating corresponding ODS layer datas table, the table name of ODS layer data tables follows
Following Naming conventions:Data Layer name _ source library name _ source table name _ renewal frequency and extraction mode.Such as ods_uc_user_da,
In, name information is in the metadata of STG layer data tables.
In a preferred embodiment, when supplementing corresponding field in ODS layer data tables, field name is wanted and standard
Field name in data dictionary is consistent, wherein includes following field in normal data dictionary:STG layers of table name,
STG layers of literary name section English name, STG layers of literary name section Chinese name, STG layers of literary name section data type, ODS layers of table name, ODS layers of literary name section
Standard English name, ODS layers of literary name section standard Chinese name, ODS layers of literary name section standard data type and ODS layers of literary name section default value.
It is understood that with the data in Current standards data dictionary, as the training set of machine learning, it is based on machine
K- neighbour's scheduling algorithms in study, obtain name, data type and the default value of ODS layers of literary name section, finally complete generation and build table
Or the ETL tasks of addition field.
In a preferred embodiment, after carrying out above-mentioned steps 101-102, the method further includes:
Step 103:Data are transferred to ODS layer data tables from STG layer data tables.
It is understood that after the processing for having carried out above-mentioned steps 101-102, the number from STG layers to ODS layers is completed
According to cleaning and standardization effort, so as to which data are easily more transferred to ODS layer data tables from STG layer data tables.
During practical O&M, ETL mission failures are always the bad dream for perplexing O&M operator on duty.It must frequently rise
Night handles ETL failed tasks, otherwise will influence downstream ETL tasks, the work to decision support, analysis personnel and operation personnel
Etc. having an impact.In every case ETL report an error it is necessary to immediately treat, actually this and it is unreasonable, for this purpose, in a kind of preferred embodiment
In, ETL tasks automatic processing method provided in this embodiment further includes following processing procedure:
When there are ETL mission failures, judge whether failure cause is that ETL scheduling system is unstable, if so, ETL
Task tune rises and retries, and otherwise determines whether that programmer changes ETL tasks and causes, if so, before ETL tasks are rolled back to modification
Task version, to ensure the normal execution of ETL tasks, and send out the responsible person that mail notification corresponds to ETL tasks, task version
Otherwise rollback judges whether the ETL tasks to fail are that data assets grade is higher than the first predetermined threshold value or downstream influences
Range is more than the ETL tasks of the second predetermined threshold value, if so, prompt message is sent out, to prompt O&M operator on duty's timely processing,
Otherwise the failed tasks are preserved, to wait for operation maintenance personnel or ETL responsible person in normal workday processing.
It is understood that by judgement above and processing procedure, it can be unnecessary to avoid O&M operator on duty
Night, so as to reduce the working strength of O&M operator on duty.
It follows that ETL tasks automatic processing method provided in this embodiment, can build table and addition field automatically, it is right
Field is named and data type standardization has liberated manpower to improve efficiency, saved expensive engineer time at
This.In addition, ETL tasks automatic processing method provided in this embodiment, since ETL failed tasks can be automatically processed, thus can
To reduce task coverage, reduces O&M operator on duty and frequently get up in the night to urinate.
Based on identical inventive concept, another embodiment of the present invention provides a kind of ETL tasks automatic processing device, referring to
Fig. 3, the device include:Shift module 21 and detection module 22, wherein:
Shift module 21, for by from source database and LOG log collections to data be put into STG layer data tables;
Detection module 22, for detecting whether STG layer datas table has corresponding ODS layer datas table to be created if not having
Corresponding ODS layer datas table, if so, whether the data sheet field then detected in STG layer data tables has in ODS layer data tables
Corresponding field supplements corresponding field if not having in ODS layer data tables.
In a preferred embodiment, the detection module 22 is when creating corresponding ODS layer datas table, the ODS numbers of plies
Following Naming conventions are followed according to the table name of table:Data Layer name _ source library name _ source table name _ renewal frequency and extraction mode.
In a preferred embodiment, when the detection module 22 supplements corresponding field in ODS layer data tables, word
Section name will be named with the field in normal data dictionary and is consistent, wherein include following word in normal data dictionary
Section:STG layers of table name, STG layers of literary name section English name, STG layers of literary name section Chinese name, STG layers of literary name section data type, ODS layers of table
Name, ODS layers of literary name section standard English name, ODS layers of literary name section standard Chinese name, ODS layers of literary name section standard data type and ODS layers
Literary name section default value.
In a preferred embodiment, described device further includes processing module, the processing module be used for by data from
STG layer data tables are transferred to ODS layer data tables.
ETL tasks automatic processing device provided in an embodiment of the present invention can be used for executing the ETL described in above-described embodiment
Task automatic processing method, concrete operating principle is similar with advantageous effect, and and will not be described here in detail.
Based on identical inventive concept, further embodiment of this invention provides a kind of electronic equipment, referring to Fig. 4, the electricity
Sub- equipment specifically includes following content:Processor 701, memory 702, communication interface 703 and bus 704;
Wherein, the processor 701, memory 702, communication interface 703 complete mutual lead to by the bus 704
Letter;The communication interface 703 is for realizing the information between the relevant devices such as each modeling software and intelligent manufacturing equipment module library
Transmission;
The processor 701 is used to call the computer program in the memory 702, the processor to execute the meter
The Overall Steps in above-described embodiment one are realized when calculation machine program, for example, reality when the processor executes the computer program
Existing following step:
Step 101:By from source database and LOG log collections to data be put into STG layer data tables.
Step 102:Whether detection STG layer datas table has corresponding ODS layer datas table, if not having, create corresponding
ODS layer data tables, if so, the data sheet field for then detecting in STG layer data tables whether have in ODS layer data tables it is corresponding
Field supplements corresponding field if not having in ODS layer data tables.
Based on identical inventive concept, further embodiment of this invention provides a kind of computer readable storage medium, the meter
It is stored with computer program on calculation machine readable storage medium storing program for executing, which realizes above-described embodiment one when being executed by processor
Overall Steps, for example, the processor execute the computer program when realize following step:
Step 101:By from source database and LOG log collections to data be put into STG layer data tables.
Step 102:Whether detection STG layer datas table has corresponding ODS layer datas table, if not having, create corresponding
ODS layer data tables, if so, the data sheet field for then detecting in STG layer data tables whether have in ODS layer data tables it is corresponding
Field supplements corresponding field if not having in ODS layer data tables.
In the description of the present invention, it should be noted that the orientation or positional relationship of the instructions such as term "upper", "lower" is base
It in orientation or positional relationship shown in the drawings, is merely for convenience of description of the present invention and simplification of the description, rather than indicates or imply
Signified device or element must have a particular orientation, with specific azimuth configuration and operation, therefore should not be understood as to this
The limitation of invention.Unless otherwise clearly defined and limited, term " installation ", " connected ", " connection " shall be understood in a broad sense, example
Such as, it may be fixed connection or may be dismantle connection, or integral connection;It can be mechanical connection, can also be to be electrically connected
It connects;It can be directly connected, can also can be indirectly connected through an intermediary the connection inside two elements.For this
For the those of ordinary skill in field, the specific meanings of the above terms in the present invention can be understood according to specific conditions.
It should also be noted that, herein, relational terms such as first and second and the like are used merely to one
Entity or operation are distinguished with another entity or operation, without necessarily requiring or implying between these entities or operation
There are any actual relationship or orders.Moreover, the terms "include", "comprise" or its any other variant are intended to contain
Lid non-exclusive inclusion, so that the process, method, article or equipment including a series of elements is not only wanted including those
Element, but also include other elements that are not explicitly listed, or further include for this process, method, article or equipment
Intrinsic element.In the absence of more restrictions, the element limited by sentence "including a ...", it is not excluded that
There is also other identical elements in process, method, article or equipment including the element.
Above example is only used to illustrate the technical scheme of the present invention, rather than its limitations;Although with reference to the foregoing embodiments
Invention is explained in detail, it will be understood by those of ordinary skill in the art that:It still can be to aforementioned each implementation
Technical solution recorded in example is modified or equivalent replacement of some of the technical features;And these are changed or replace
It changes, the spirit and scope for various embodiments of the present invention technical solution that it does not separate the essence of the corresponding technical solution.