ETL job distributed processing system and method based on dynamic clustering

[0001] The present invention relates to a dynamic clustering-based ETL JOB distributed processing system and method. In particular, the present invention relates to the dynamic clustering-based ETL JOB distributed processing system and the method, which can configure a multiplexed ETL system and use 100% of available resources, and dynamically support a high availability cluster even in the configuration of the multiplexed ETL, thereby ensuring not only a high availability cluster (H/A) configuration but also scalability.

[0002] ETL (Extract, Transform, Load) is a three-step process in which data is extracted, transformed, and loaded, and ETL software automates the entire process and can be executed manually or processed as a batch operation according to a repeated schedule.

[0003] Generally, an ETL system operates alone or supports a high availability cluster (H/A) service in an Active-Standby duplex manner. At this time, one unit is configured as an active state for processing the actual ETL, and the other unit is configured as a standby state, and when the active server fails, the standby server is operated in the active mode by the high availability software (dualization solution) to process the ETL.

[0004] Such an Active-Standby duplexing scheme has a limitation in that available resources are limited to 50% in terms of configuration characteristics.

[0005] As a prior art, there is Korean patent No. 10-1806497 (a middleware device and a method thereof for performing ETL job processes in a distributed manner), but a system configuration in which a separate server (an ETL JOB distributed management server) for performing ETL JOB distribution is configured, and JOBs are distributed to a plurality of ETL servers using the server is supported, but this is not an H/A configuration, but a modified structure focusing on a JOB distributed processing function. If an ‘ETL JOB distributed management server’ failure occurs, the JOB cannot be distributed and the ETL function cannot be performed.

[0006] In particular, for two or more active/active multiplexed ETL system configurations, functional requirements such as multiple system configurations, data sharing and communication, node management, debugging and monitoring, etc., need to be taken into consideration in architectural design from the software design stage.

[0007] The problem to be solved by the present invention is to provide a dynamic clustering-based ETL JOB distributed processing system and method capable of dynamically supporting a high availability cluster (H/A) in a multiplexed ETL system configuration environment as well as ensuring scalability so that 100% of available resources can be used.

[0008] According to an embodiment of the disclosure, an ETL JOB distributed processing method based on dynamic clustering includes: extracting, by a cluster leader node, data from a source DB; distributing, by the cluster leader node, an ETL (Extract, Transform, Load) JOB for performing an ETL operation to a plurality of cluster normal nodes based on the extracted data; processing, by the plurality of cluster normal nodes, the ETL JOB by calling the cluster leader node; and loading, by the cluster leader node and the plurality of cluster normal node, the processed ETL JOB into a target DB.

[0009] An ETL JOB distributed processing system based on dynamic clustering according to an embodiment of the present invention includes: a cluster leader node that receives data extracted from a source DB and distributes an ETL (Extract, Transform, Load) JOB for performing an ETL operation to a plurality of cluster normal nodes; and a plurality of cluster normal nodes that process the ETL JOB by invoking the cluster leader node and a repository DB that stores the ETL JOB processed by the cluster leader node or the plurality of cluster normal nodes.

[0010] According to the present invention, it is possible to configure a multiplexed ETL system and use 100% of available resources.

[0011] In addition, the high availability cluster (H/A) configuration as well as scalability can be guaranteed by dynamically supporting the high availability cluster in the multiplexed ETL system configuration.

[0016] The specific structural or functional descriptions of the embodiments in accordance with the present inventive concept disclosed herein are merely illustrative for the purpose of describing the embodiments according to the present inventive concept, and the embodiments in accordance to the present inventive concept may be embodied in various forms and are not limited to the embodiments described herein.

[0017] Embodiments in accordance with the inventive concept are capable of various modifications and of various forms, and thus are illustrated in the drawings and described herein in detail. It should be understood, however, that there is no intent to limit embodiments in accordance with the inventive concept to the particular forms disclosed, but on the contrary, the intention is to cover all modifications, equivalents, or alternatives falling within the spirit and scope of the invention.

[0018] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. The singular forms “a,” “an,” and “the” include plural referents unless the context clearly dictates otherwise. It should be understood that terms such as “comprises” or “comprising” used herein are intended to specify the presence of a feature, a number, a step, an operation, a component, a part, or a combination thereof described herein, but do not preclude the presence or addition of one or more other features, numbers, steps, operations, components, parts, or combinations thereof.

[0019] The term “cloud” means that data can be stored in a central computer connected to the Internet and can be used anytime, anywhere by simply accessing the Internet, and cloud computing refers to providing IT resources on-demand through the Internet and paying only for the amount used, and the types of cloud services include Iaas (infrastructure), Pass (platform), and Saas (software). Availability refers to the degree to which an information system such as a server, a network, or a program can be normally used. When solubility is expressed by a formula, solubility refers to a value obtained by dividing a normal use time (Uptime) by a total use time (Uptime+Downtime). High solubility is referred to as high availability (HA).

[0020] Scalability refers to the ease with which an IT system can be scaled without the need for large-scale redesign and reinstallation. High availability and scalability need to be considered in a service system, and in particular, in a service system in a cloud environment in which a service needs to be provided so as to allow an error and scale according to a customer's requirement, the high availability and the scalability are essential conditions.

[0021] In a general clustering method, each node has information of a node configuring clustering, and is referred to as a static clustering configuration. On the other hand, there are cases where nodes need to be dynamically added. That is, a separately set node may be added to the operating cluster, and for this purpose, if all the information of the nodes included in the clustering is set and executed only for the nodes to be dynamically added, the dynamically started node attempts to connect to each node. This is referred to as dynamic clustering, and the present invention relates to an ETL JOB distributed processing system and method based on dynamic clustering. ETL is an abbreviation for Extract, Transform, and Load, and ETL JOB refers to an operation required to perform ETL (Extract, Transform, Load).

[0022] Hereinafter, embodiments of the present invention will be described in detail with reference to the accompanying drawings.

[0023] FIG. 1 is a flowchart illustrating a dynamic clustering-based ETL JOB distributed processing method according to an embodiment of the present invention.

[0024] Referring to FIG. 1, the cluster leader node 100 extracts data from the source DB 400 (S101).

[0025] The cluster leader node 100 may manage data communication for clustering, and may extract data to distribute an ETL JOB that performs an extract, transform, and load (ETL) operation in the source DB 400. Based on the data extracted by the cluster leader node 100, the ETL JOB is distributed to a plurality of cluster normal nodes 200 (S103). The cluster leader node 100 may distribute the ETL JOB to a plurality of cluster normal nodes 200 according to the job distribution policy setting, and may call one of the plurality of cluster normal nodes 200 including the cluster leader node 100 according to a predefined condition to distribute the ETL JOB.

[0026] A plurality of cluster normal nodes 200 process the ETL JOB by calling the cluster leader node 100 (S105). The ETL JOB processed by the cluster leader node 100 and the plurality of cluster normal nodes 200 is loaded into the target DB 500 (S107).

[0027] FIG. 2 is a flowchart illustrating a cluster leader node automatic re-selection method in the event of a cluster leader node failure according to an embodiment of the present invention.

[0028] Referring to FIG. 2, the cluster normal node 200 queries the cluster leader IP in the repository DB 300 (S201), and the repository DB 300 transfers the cluster leader IP query result to the cluster normal node 200 (S203). The cluster normal node 200 attempts to ping with the cluster leader IP received by the cluster leader node 100 (S205), and the cluster leader node 200 transmits a cluster leader IP ping response to the cluster normal node 200 (S207). When the cluster normal node 200 attempts cluster leader IP ping to the cluster leader node 100, three retries may be performed, and if there is no response as a result of the three retries, a “cluster leader IP ping response error” may be transmitted to the cluster normal node 200. When the cluster normal node 200 attempts cluster leader IP ping to the cluster leader node 100, if there is a response among the results of the three retries, the cluster normal node 200 may be notified that it is not a “cluster leader IP ping response error”. The above-described number of times of three retries is an example, and is not necessarily limited thereto.

[0029] When the cluster leader IP ping response received by the cluster normal node 200 is “cluster leader IP ping reply error” (S209), the cluster normal node 200 requests the repository DB 300 to update the cluster normal node 100 to the cluster leader node 100 (S211). The repository DB 300 checks the cluster normal node 200 for an update with the cluster leader (S213). The cluster normal node 200 requests the repository DB 300 to change the cluster leader node IP to the cluster normal node IP (S215), and the repository DB 300 confirms the change of the cluster leader node ID to the cluster normal node 200 (S217).

[0030] If the cluster leader IP ping response received by the cluster normal node 200 from the cluster leader node 100 is “not a cluster leader IP ting response error”, the cluster normal node 200 is changed to the cluster leader node 110, and the normality of the cluster leader node 150 is confirmed (S219).

[0031] FIG. 3 is a schematic diagram of a dynamic clustering-based ETL JOB distributed processing system according to an embodiment of the present disclosure, and FIG. 4 is a schematic structural diagram of a dynamic clusters-based ETL JOB distributed processing systems according to embodiments of the present disclosure.

[0032] Referring to FIGS. 3 and 4, the dynamic clustering-based ETL JOB distributed processing system 10 includes a cluster leader node 100, a plurality of cluster normal nodes 200 (200a, 200b, and 200N), a repository DB 300, a source DB 400, and a target DB 500.

[0033] The cluster leader node 100 includes a clustering DSM 110 and a node information unit 120, and the clustering DSM 100 includes a cluster 111, a scheduler 112, a log manager 113, a JOB monitor 114, a work bench designer 115, a version manager 116, a data processing engine 117, a frame builder 118, and an NTAS 119. Here, DSM stands for Data Sync Manager.

[0034] The cluster 111 may serve as at least one of data communication management, node management, leader selection, distribution management, and synchronization management for clustering. The cluster 110 may manage data communication through message passing and communication functions. This enables session integration management for all nodes in a network environment without a Layer 4 switch. In addition, the cluster 111 is capable of node management including addition, deletion, and management in the event of failure of the cluster normal node 200 through a function of managing clustering node information. In addition, the cluster 111 may select the cluster leader node 100 through a function of automatically re-selecting a leader node from among the plurality of cluster normal nodes 200 when a failure occurs in the cluster leader node 110.

[0035] The cluster 111 may also distribute the ETL JOB according to a job distribution policy setting. At this time, the cluster 111 may call one of the plurality of cluster normal nodes 200 including the cluster leader node 100 to distribute the ETL JOB according to a predefined condition. In addition, the cluster 111 may perform single or mixed operation support functions such as cyclic distribution, node evaluation, and server failure frequency. In this case, the evaluation element used when the cluster 111 evaluates the node may be, but is not limited to, at least one of the number of CPUs, the memory capacity, and the number of concurrent JOB settings. In addition, the cluster 111 may perform a synchronization management function of preventing data inconsistency through synchronization of all cluster normal nodes (the cluster leader node 100 and a plurality of cluster normal nodes 200).

[0036] The scheduler 112 is a batch scheduler function and is capable of performing detailed scheduling control in units of seconds. The scheduler 112 may cause the preset schedule to be executed only in the cluster leader node 100 among the cluster leader node 110 and the plurality of cluster normal nodes 200. In addition, the scheduler 112 may invoke and manage a schedule corresponding to the ETL JOB distributed to the plurality of cluster normal nodes 200.

[0037] When the ETL JOB is executed, the log manager 113 may manage comprehensive log information including the number of processing events, successes, failures, warnings, and the like as an execution result. The log manager 113 may manage the log information so that the JOB processing results of the cluster leader node 100 and the plurality of cluster normal nodes 200 may be collectively managed in the common repository DB 300 to check the log position of the node in which the error has occurred, as well as the order of the log order error that occurs when managing the log information in each server. In addition, the log manager 113 may also integrate and manage a clustering log in which the cluster 110 distributes an ETL JOB to the cluster normal node 200 to invoke a schedule. In an embodiment, the log manager 113 may store an identifier of at least one of a name and an IP of a node on which the ETL JOB is executed in the log.

[0038] The JOB monitor 114 may monitor and control the ETL JOB and task currently being performed by the cluster lead node 100 and the plurality of cluster normal nodes 200. The JOB monitor 114 enables real-time querying of the ETL JOB being executed in the entire cluster normal node (the cluster leader node 100 and the plurality of cluster normal nodes 200), and also manages information of the node being actually executed.

[0039] The work bench designer 115 may support a task of visually designing a data flow and a work flow.

[0040] The version manager 116 may function to support version management and collaboration.

[0041] The data processing engine 117 may be a core engine that supports various tasks such as DB, File, Sorter, LDAP, and BigData by performing data processing.

[0042] The Frame Builder 118 may be a user interface framework.

[0043] The network transaction application server (NTAS 119) may be server middleware.

[0044] The node information unit 120 may be, but is not necessarily limited to, information corresponding to a cluster leader node among table list information of all cluster normal nodes stored in the repository DB 300.

[0045] The cluster normal node 200 includes a clustering DSM 210 and a node information unit 220.

[0046] The clustering DSM 210 has the same configuration as that of the clustering DSM 110 constituting the cluster leader node 100, and when a failure occurs in the cluster leader node 110, the cluster leader node 110 may be selected from among a plurality of cluster normal nodes 200 according to a cluster leader node 100 selection method to perform a role of the cluster leader node. Since the clustering DSM 210 of the plurality of cluster normal nodes 200 has the same configuration as that of the clustering DSM 110 constituting the cluster leader node 100, all the cluster normal nodes can serve as both the cluster leader node and the cluster normal nodes, and have a dynamic clustering structure.

[0047] The node information unit 220 may be, but is not necessarily limited to, information corresponding to a cluster normal node among table list information of all cluster normal nodes stored in the repository DB 300.

[0048] The repository DB 300 is a meta storage DB, and may be a database that stores all information of all cluster normal nodes (cluster leader node 100 and a plurality of cluster normal nodes 200) participating in clustering. The repository DB 300 may store solution meta information including schedule information of the clustering DSM 110, 210, and the like. The repository DB 300 may store meta-information related to the clusters 111 of the clustering DSM 110 and 220. All the cluster normal nodes may check the repository DB 300 to identify the node.

[0049] The source DB 400 may refer to a database in which source data on which the ETL JOB is to be performed is stored, and the target DB 500 may refer to a data on which the conversion is completed after the ETL JOB is completed.

[0050] While the invention has been described with reference to the embodiments shown in the drawings, this is for illustrative purposes only and it will be understood by those skilled in the art that various modifications and equivalent arrangements may be made therefrom. Therefore, the true technical protection scope of the present invention should be defined by the technical spirit of the appended claims.