CN108959411B

CN108959411B - Processing method, device and equipment of ETL (extract transform and load) task

Info

Publication number: CN108959411B
Application number: CN201810578640.9A
Authority: CN
Inventors: 蔡笑时
Original assignee: New H3C Big Data Technologies Co Ltd
Current assignee: New H3C Big Data Technologies Co Ltd
Priority date: 2018-06-07
Filing date: 2018-06-07
Publication date: 2020-12-08
Anticipated expiration: 2038-06-07
Also published as: CN108959411A

Abstract

The disclosure provides a processing method, a device and equipment of an ETL task; wherein the method is applied to equipment running with an ETL tool, and comprises the following steps: generating an XML file according to an ETL task creation request of a user; compressing the XML file to obtain a string-type character string; the string-type character string is stored in a specified field of a specified database. The ETL task data are stored in a mode of combining the XML file and the database, the ETL task data can be safely and stably stored under a high concurrency scene, and the reliability of ETL task storage is improved.

Description

Processing method, device and equipment of ETL (extract transform and load) task

Technical Field

The present disclosure relates to the field of data warehouse technologies, and in particular, to a method, an apparatus, and a device for processing an ETL task.

Background

An ETL (Extract-Transform-Load) is a data warehouse technology, and a user can Extract required data from a data source through an ETL task, and Load the data to a destination (such as a predefined data warehouse) through data cleaning, inter-conversion, and other processes.

In the process of designing an ETL task, an engineer needs to store the designed ETL task; one storage method is to establish a plurality of data tables in a database, and respectively store information such as connection lines, components, data sources and the like in an ETL task into the corresponding data tables; another way to save is to save the ETL job in a file. But the storage mode of the file is easy to cause the mistaken deletion, and the safety is low; for the above-mentioned storage form of the database, if a plurality of engineers use the same database to store ETL tasks at the same time, the database may only respond to one of the storage requests, which may cause a failure in storing another ETL task or an abnormal condition of data storage, resulting in a low reliability of ETL task storage.

Disclosure of Invention

In view of this, the present disclosure aims to provide a method, an apparatus and a device for processing an ETL task, so as to improve the reliability of storing the ETL task.

In order to achieve the above purpose, the technical scheme adopted by the disclosure is as follows:

in a first aspect, the present disclosure provides an ETL task processing method applied to a device running an ETL tool, including: generating an XML file according to an ETL task creation request of a user; compressing the XML file to obtain a string-type character string; the string-type character string is stored in a specified field of a specified database.

In a second aspect, the present disclosure provides an ETL task processing apparatus applied to a device running an ETL tool, including: the file generation module is used for generating an XML file according to the ETL task creation request of the user; the compression module is used for compressing the XML file to obtain a string-type character string; and the storage module is used for storing the string type character string in a specified field of a specified database.

In a third aspect, the disclosed embodiment provides an ETL task processing device, which includes a processor and a machine-readable storage medium, where the machine-readable storage medium stores machine-executable instructions capable of being executed by the processor, and the processor executes the machine-executable instructions to implement the processing method of the ETL task.

In a fourth aspect, the disclosed embodiments provide a machine-readable storage medium storing machine-executable instructions that, when invoked and executed by a processor, cause the processor to implement the processing method of the ETL task described above.

According to the processing method, the device, the equipment and the machine readable storage medium of the ETL task, the XML file corresponding to the ETL task creation request is generated firstly, the XML file is compressed, and then the string type character string obtained after compression is stored in the specified field of the specified database. The method adopts a mode of combining the XML file and the database to store the ETL task data, can ensure that the ETL task data is safely and stably stored under a high concurrency scene, and improves the reliability of ETL task storage.

Additional features and advantages of the disclosure will be set forth in the description which follows, or in part may be learned by the practice of the above-described techniques of the disclosure, or may be learned by practice of the disclosure.

In order to make the aforementioned and other objects, features and advantages of the present invention comprehensible, preferred embodiments accompanied with figures are described in detail below.

Drawings

In order to more clearly illustrate the embodiments of the present disclosure or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present disclosure, and other drawings can be obtained by those skilled in the art without creative efforts.

FIG. 1 is a schematic diagram illustrating the design of an ETL task provided by an embodiment of the present disclosure;

FIG. 2 is a schematic diagram of a data table for saving ETL tasks according to an embodiment of the present disclosure;

FIG. 3 is a schematic diagram of a database structure for storing ETL tasks according to an embodiment of the present disclosure;

FIG. 4 is a flowchart of a processing method of an ETL task according to an embodiment of the present disclosure;

FIG. 5 is a flow chart of another ETL task processing method provided by the disclosed embodiment;

FIG. 6 is a schematic structural diagram of an ETL task table according to an embodiment of the present disclosure;

FIG. 7 is a flow chart of another ETL task processing method provided by the disclosed embodiment;

fig. 8 is a schematic structural diagram of a processing apparatus for an ETL task according to an embodiment of the present disclosure;

fig. 9 is a schematic structural diagram of a processing device for an ETL task according to an embodiment of the present disclosure.

Detailed Description

To make the objects, technical solutions and advantages of the embodiments of the present disclosure more apparent, the embodiments of the present disclosure will be described clearly and completely with reference to the accompanying drawings, and it is to be understood that the described embodiments are some, but not all embodiments of the present disclosure. All other embodiments, which can be derived by one of ordinary skill in the art from the embodiments disclosed herein without making any creative effort, shall fall within the protection scope of the present disclosure.

In order to better understand the technical solution of the present disclosure, the ETL task is briefly described below, and in this embodiment, the ETL task may also be referred to as an ETL process. An ETL task usually includes elements such as data source, component, and connection information; the connection information comprises the connection relation between the data source and the components or between the components; FIG. 1 is a schematic diagram of the design of an ETL task; in the ETL task, data is extracted from two data sources, namely a data source A and a data source B; the extracted data are converged by the convergence component, cleaned, converted and the like by the action execution component (possibly comprising a plurality of components), and finally loaded into a data warehouse.

After the ETL task is designed, the ETL task needs to be stored; because the ETL task may need to be used repeatedly, the storage form of the ETL task needs to have higher safety; the security coefficient of the database is high, and the user can operate the data only after identity authentication, so that all elements in the ETL are often stored in the database.

Specifically, an ETL task can be stored by multiple data tables, such as the data table shown in fig. 2 that stores all the information of an ETL task; the data table beginning with r _ database is generally used to store data source information in the ETL task, such as the identifier, path, etc. of the data source a and the data source B in fig. 1; the data table beginning with r _ step is generally used for storing component information in the ETL task, such as information of the above-mentioned aggregation component and each action execution component; the r _ trans _ lock data table is used for storing connection information, including connection relations between data sources and components, connection relations between the components and a data warehouse, and the like. When the ETL tasks stored in the database are used again, the complete ETL tasks can be assembled through the information recorded in the data tables.

FIG. 3 is a diagram illustrating a database structure for storing ETL tasks; the database structure comprises a service database and a database for storing ETL task data; the business database stores an ETL task table, the ETL task table stores a plurality of table entries, and each table entry can correspond to one ETL task; each table entry comprises fields such as an ETL task identifier (id), a name (name), a user (user), a key element (key element) and the like; the key element may be used to store a path of a database in which ETL task data is stored in association, specifically including paths of a plurality of ETL data tables corresponding to the ETL task; typically, an ETL job corresponds to over ten data tables; the data table shown in fig. 2 may be stored in the manner in the database storing ETL job data in fig. 3.

The storage mode of the ETL task is more suitable for a single-machine user scene, namely, an engineer installs an ETL development tool in a local computer and independently designs and develops the ETL task; and after the development of the ETL task is completed, storing the ETL task in a database. However, if an engineer designs an ETL task using an ETL development tool of a web version, there may be a plurality of engineers who design an ETL task using the ETL development tool and access a database to store the designed ETL task; at this time, under the influence of a database storage mechanism, if two engineers send storage requests to the database at the same time, the database is likely to allocate the same ID to the two storage requests, and normally respond to one of the storage requests; for another save request with the same ID, the corresponding ETL task will fail to insert data, resulting in no normal save, but the engineer will get feedback that the save was successful.

Because the storage logic of the database is relatively complex, partial data cannot be rolled back when the storage is abnormal (namely, the data is recovered to the behavior of the last correct state when the data is abnormal), and partial redundant data may remain in the database for the ETL task data which is abnormally stored; these redundant data are likely to cause the normally stored data to become problematic data, which in turn may cause a series of more serious problems.

From the above, in a scenario of storing high-concurrency ETL tasks, the reliability of the database storage method is significantly reduced. Based on the above, the embodiment of the present disclosure provides a method, an apparatus and a device for processing an ETL task; the techniques may be applied in data storage for storing ETL tasks, or other data mining processes, as described in more detail below.

First, refer to a flowchart of a processing method of an ETL task shown in fig. 4; the method is applied to equipment running with an ETL tool; the ETL tool is typically software that can design ETL tasks, e.g., datastage, informatics, keytle, etc.; the equipment for running the ETL tool is typically a server, a PC (personal computer) or other dedicated human-computer interaction equipment, etc. The ETL tool can be installed on a device, and can also be operated in a webpage mode through a browser.

The method comprises the following steps:

step 402, generating an XML file according to an ETL task creation request of a user;

the XML (Extensible Markup Language) file is a Markup Language for marking an electronic file to be structured. The data of the ETL task may be saved in an XML file. The XML file comprises a plurality of tags, and the tags are used for marking the attributes of the data; the data in the XML file may be parsed by parsing the form of the tag. Wherein, the tags in the XML file are all closed, for example, the < connection > tag, and the content stored in the < connection > </connection > tag pair is the connection information of the database in the ETL task.

Step 404, compressing the XML file to obtain a string-type character string;

in the specific implementation, the XML file may be directly compressed, or the XML file may be encoded first and then compressed, or the XML file may be compressed first and then encoded; the string-type character string can be obtained through the operation steps, and the purpose of reducing the XML file is achieved. According to the service requirement, the XML file can be encoded, for example, non-ASCII characters in the XML file are converted into ASCII characters; if the XML file needs to be coded, a native coding interface Base64. encodedBase64 in JDK API (Java Development Kit Application Programming interface) can be adopted to code the XML file by Base 64; in general, the XML file encoded by the Base64 has no readability, and needs to be decoded to be read. In addition, the XML file can be subjected to Base32 or Base16 encoding by using related encoding tools, or other encoding forms.

When compressing the XML file, the XML file may be compressed by using a native compression interface gziputput stream in the JDK API to obtain a string-type character string. The XML file is reduced through encoding, compressing and the like, the storage pressure of the XML file on the storage space can be reduced, and meanwhile, the reliability of data storage can be improved through the encoding process.

At step 406, the string-type string is stored in a designated field of a designated database.

If the string-type character string is directly stored in the storage resource of the local computer or the cloud, the string-type character string may be deleted by mistake; to ensure the security of the file, the string-type character string may be saved in a database.

In the embodiment, a string-type character string corresponding to one ETL task is stored in a designated field in a database, and compared with the prior art, the method has a form that the ETL task is directly stored in a plurality of data tables of the database after an ID is allocated to a storage request.

The processing method of the ETL task comprises the steps of firstly generating an XML file corresponding to an ETL task creation request, compressing the XML file, and then storing the obtained string-type character string in a specified field of a specified database. The method adopts a mode of combining the XML file and the database to store the ETL task data, can ensure that the ETL task data is safely and stably stored under a high concurrency scene, and improves the reliability of ETL task storage.

In another embodiment, the ETL tool in the high concurrency scenario is typically a web page version tool, and supports multi-user access usage, such as a software service model of SaaS (software as a service). An engineer can use an ETL tool to design an ETL task by opening a browser; the ETL tool is also typically provided with an interface to access the database, through which the engineer can save the processed ETL tasks to the database.

FIG. 5 is a flow chart illustrating another ETL task processing method; in the method, firstly, an XML file is coded, and then the coded XML file is compressed, so that the data volume and the structure of the XML file are further optimized, and the reliability of data storage is improved; the method specifically comprises the following steps:

step S502, generating an XML file according to an ETL task creation request of a user;

step S504, encode the byte stream of the XML file according to the preset encoding rule;

the preset coding rule can be the Base64 coding, the Base32 coding or the Base16 coding mentioned above, and can also be other coding rules.

Step S506, compressing the encoded XML file to obtain a string-type character string;

in practical implementation, the Base64 encoding can be performed on the XML file by adopting the Base64. encodeBase64; and compressing the encoded XML file by adopting a GZIPOutputStream method to obtain a string-type character string.

In another embodiment, the execution sequence of the step S504 and the step S506 may also be interchanged, that is, the XML file is compressed; and coding the compressed XML file according to a preset coding rule to obtain a string-type character string.

Step S508, a task index identifier is established in the ETL task table of the service database, and the string-type character string is stored in the designated field corresponding to the task index identifier.

The structure of the ETL task table can be as shown in fig. 6, where the ETL task table can include a plurality of ETL tasks, each ETL task corresponds to an entry, and each entry generally includes the following fields: ETL task identity (id), name (name), user (user), key element (key element), etc.; wherein, the id field can be used for storing the established task index identifier, and the key element field can be used as the designated field for storing the string-type character string; of course, the fields of the above-mentioned entries are not limited to the above-mentioned fields in general; the role of each field, and the designated field for holding the string-type character string, can be set by the user himself.

When the engineer needs to execute the ETL task stored in the database again, the ETL task can be obtained and restored in the manner shown in fig. 7, which specifically includes:

step S702, extracting string type character strings corresponding to ETL tasks from specified fields in a specified database;

an engineer can send a data extraction request to a database, wherein the request can carry a task index identifier, a name or other identification information which can uniquely identify an ETL task; after the database receives the request, the corresponding string-type character string can be searched from the specified database according to the identification information in the request.

Specifically, when the string-type character string corresponding to the XML file is stored in a designated field in the database, the corresponding entry may be searched from the ETL task table through the identification information, and the string-type character string may be extracted from the corresponding field of the entry, such as the key element field.

Step S704, carrying out decompression operation on the extracted string-type character string to obtain an XML file; wherein the decompression operation and the compression operation are in a reciprocal relationship;

step S706, apply XML file to execute ETL task.

If the XML file is directly coded in the storage process, the string-type character string is decompressed at the moment; if the XML file is encoded first and then the encoded XML file is compressed, the string-type character string needs to be decompressed first and then decoded to obtain the original XML file. If the XML file is compressed and then encoded during the storage process, the string-type character string needs to be decoded and then decompressed to obtain the original XML file. The original XML file contains elements such as data sources, components, and link information that can be identified by the ETL execution tool, and then the ETL task is executed according to the elements.

In the above manner, the XML is encoded and compressed, the format of the XML file can be further optimized, and the file is compressed. The method can reduce the storage pressure of the XML file on the database, solve the problem of abnormity caused by the performance of the database when a large number of users store the ETL conversion design process at high concurrency, and ensure the accuracy of ETL task storage.

It should be noted that the above method embodiments are all described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same and similar parts among the embodiments may be referred to each other.

Corresponding to the above method embodiment, refer to a schematic structural diagram of a processing apparatus of an ETL task shown in fig. 8; the device is applied to equipment running with an ETL tool, and comprises:

a file generating module 80, configured to generate an XML file according to the ETL task creation request of the user;

the compression module 81 is configured to compress the XML file to obtain a string-type character string;

a storage module 82, configured to store the string-type character string in a specified field of a specified database.

The ETL tool is a webpage version tool and supports multi-user access.

The compression module is further configured to perform one of: compressing the XML file to obtain a string-type character string; compressing the XML file; coding the compressed XML file according to a preset coding rule to obtain a string-type character string; coding the XML file according to a preset coding rule; and compressing the encoded XML file to obtain a string-type character string.

The storage module is further configured to: and establishing a task index identifier in an ETL task table of the service database, and storing the string-type character string in a designated field corresponding to the task index identifier.

The embodiment provides processing equipment of an ETL task corresponding to the method embodiment. Fig. 9 is a schematic structural diagram of the apparatus, and as shown in fig. 9, the apparatus includes a processor 901 and a memory 902; the memory 902 is used for storing one or more computer instructions, and the one or more computer instructions are executed by the processor to implement the processing method of the ETL task.

The device shown in fig. 9 further comprises a bus 903 and a forwarding chip 904, the processor 901, the forwarding chip 904 and the memory 902 being connected via the bus 903. The processing device of the ETL task may be a network edge device.

The Memory 902 may include a high-speed Random Access Memory (RAM) and may also include a non-volatile Memory (non-volatile Memory), such as at least one disk Memory. Bus 903 may be an ISA bus, PCI bus, EISA bus, or the like. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of illustration, only one double-headed arrow is shown in FIG. 9, but this does not indicate only one bus or one type of bus.

The forwarding chip 904 is configured to connect with at least one user terminal and other network units through a network interface, and send the packaged IPv4 message or IPv6 message to the user terminal through the network interface.

The processor 901 may be an integrated circuit chip having signal processing capabilities. In implementation, the steps of the above method may be implemented by integrated logic circuits of hardware or instructions in the form of software in the processor 901. The Processor 901 may be a general-purpose Processor, and includes a Central Processing Unit (CPU), a Network Processor (NP), and the like; the device can also be a Digital Signal Processor (DSP), an Application Specific Integrated Circuit (ASIC), a Field-Programmable Gate Array (FPGA), or other Programmable logic devices, discrete Gate or transistor logic devices, discrete hardware components. The various methods, steps and logic blocks disclosed in the embodiments of the present invention may be implemented or performed. A general purpose processor may be a microprocessor or the processor may be any conventional processor or the like. The steps of the method disclosed in connection with the embodiments of the present invention may be directly implemented by a hardware decoding processor, or implemented by a combination of hardware and software modules in the decoding processor. The software module may be located in ram, flash memory, rom, prom, or eprom, registers, etc. storage media as is well known in the art. The storage medium is located in the memory 902, and the processor 901 reads the information in the memory 902, and completes the steps of the method of the foregoing embodiment in combination with the hardware thereof.

Embodiments of the present invention further provide a machine-readable storage medium, where the machine-readable storage medium stores machine-executable instructions, and when the machine-executable instructions are called and executed by a processor, the machine-executable instructions cause the processor to implement the processing method of the ETL task, and specific implementation may refer to method implementation embodiments, and is not described herein again.

The implementation principle and the generated technical effect of the processing device and the equipment of the ETL task provided by the embodiment of the present invention are the same as those of the method embodiments described above, and for the sake of brief description, corresponding contents in the method embodiments can be referred to where the device embodiments are not mentioned.

In the several embodiments provided in the present application, it should be understood that the disclosed apparatus and method may be implemented in other manners. The apparatus embodiments described above are merely illustrative, and the flowcharts and block diagrams in the figures, for example, illustrate the architecture, functionality, and operation of possible implementations of apparatus, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

Finally, it should be noted that: the above-mentioned embodiments are merely specific embodiments of the present disclosure, which are used for illustrating the technical solutions of the present disclosure and not for limiting the same, and the scope of the present disclosure is not limited thereto, and although the present disclosure is described in detail with reference to the foregoing embodiments, those skilled in the art should understand that: any person skilled in the art can modify or easily conceive of the technical solutions described in the foregoing embodiments or equivalent technical features thereof within the technical scope of the present disclosure; such modifications, changes or substitutions do not depart from the spirit and scope of the embodiments of the present disclosure, and should be construed as being included therein. Therefore, the protection scope of the present disclosure shall be subject to the protection scope of the claims.

Claims

1. A processing method of an ETL task is characterized in that the method is applied to equipment running an ETL tool and comprises the following steps:

generating an XML file according to an ETL task creation request of a user;

compressing the XML file to obtain a string-type character string;

storing the string-type character string in a designated field of a designated database;

wherein the step of storing the string-type character string in a designated field of a designated database comprises: establishing a task index identifier in an ETL task table of a service database, and storing the string-type character string in a designated field corresponding to the task index identifier;

the step of compressing the XML file to obtain a string-type character string includes one of the following steps: compressing the XML file to obtain a string-type character string; and compressing the XML file, and encoding the compressed XML file according to a preset encoding rule to obtain a string-type character string.

2. The method of claim 1, wherein the ETL tool is a netpage version tool.

3. The method of claim 1, further comprising:

if ETL tasks need to be executed, extracting the string-type character strings from the specified fields of the specified database;

decompressing the extracted string-type character string to obtain an XML file; wherein the decompression operation is in a reciprocal relationship to the compression operation;

and executing the ETL task by applying the XML file.

4. An ETL task processing device, which is applied to equipment running an ETL tool, and comprises:

the file generation module is used for generating an XML file according to the ETL task creation request of the user;

the compression module is used for compressing the XML file to obtain a string-type character string;

the storage module is used for storing the string type character string in a specified field of a specified database; wherein the storage module is further configured to: establishing a task index identifier in an ETL task table of a service database, and storing the string-type character string in a designated field corresponding to the task index identifier;

the compression module is further configured to perform one of: compressing the XML file to obtain a string-type character string; compressing the XML file; and coding the compressed XML file according to a preset coding rule to obtain a string-type character string.

5. The apparatus of claim 4, wherein the ETL tool is a netpage version tool, supporting multi-user access usage.

6. An ETL task processing device comprising a processor and a machine-readable storage medium storing machine-executable instructions executable by the processor, the processor executing the machine-executable instructions to implement the method of any of claims 1 to 3.