A method for kettle data integration based on a unified scheduling platform

Abstract

This invention relates to the field of data integration technology, and in particular to a method for Kettle data integration based on a unified scheduling platform. This invention combines DolphinScheduler and Kettle, placing configured Kettle tasks on the DolphinScheduler scheduling platform for execution. This fully utilizes the rich components within Kettle for data processing, while placing tasks on a distributed scheduling platform increases the number of tasks executed simultaneously. By combining the advantages of both software programs, it enables the simultaneous execution of large-scale data integration tasks, meeting the needs of processing massive amounts of data. Simultaneously, it reduces server performance requirements, which can be compensated for by expanding the number of services deployed on the scheduling platform.

Description

Technical Field

[0001] This invention relates to the field of data integration technology, and in particular to a method for Kettle data integration based on a unified scheduling platform. Background Technology

[0002] Among existing technologies, Kettle is currently the most powerful open-source data integration tool, widely used and possessing rich data processing components. However, it lacks a unified task scheduling method. Kettle is typically built on a standalone server or in a cluster, with tasks configured on the client saved to a repository. Scheduled execution is achieved through server-side timers or by custom-developing Java programs. However, Java programs can only perform one-to-one tasks and cannot handle distributed task distribution and execution. Therefore, there is a limit to the number of Kettle tasks that can run on a single system. Project implementation verification shows that on a 16-core, 8GB Linux server, with approximately 200 tasks executed every half hour, the server's CPU usage reaches over 80%, severely impacting other deployed functions and causing system lag. Deploying on multiple systems requires multiple management interfaces for management and maintenance, leading to significant inconvenience and negatively impacting user experience. Furthermore, among numerous open-source scheduling platforms, almost none support Kettle task scheduling.

[0003] DolphinScheduler is a distributed, scalable, and visual DAG workflow task scheduling open-source system. Suitable for enterprise-level scenarios, it provides a solution for visually managing tasks, workflows, and the entire lifecycle of data processing. Closely integrated with the big data ecosystem, it is a superior scheduling platform for handling large volumes of data. It offers nearly 20 task types, including Spark, Hive, M / R, Python, Sub_process, and Shell, but currently does not support Kettle tasks.

[0004] This invention combines DolphinScheduler and Kettle, integrating the advantages of both software. It enables distributed execution of Kettle tasks, requiring adaptation of existing Kettle configurations. Without altering the original task structure, it reduces workload and achieves distributed scheduling and execution of tasks. Simultaneously, it enables the synchronous execution of large-scale data integration tasks, meeting the needs of processing massive amounts of data. Summary of the Invention

[0005] In data integration processing, we typically choose Kettle, a data integration tool that is easy to learn and operate. However, Kettle has its own independent client interface, requiring local installation, which is inconvenient for users. Therefore, we implemented a web-based solution for Kettle, developing it in Java and migrating the Kettle client operation to a web page. Kettle task execution is also initiated and executed through the Java system, with the service deployed on a 32-core Linux server. When more than 100 tasks are executed simultaneously, scheduling 2000 times per day causes the server CPU to reach over 80%, placing high demands on server performance and severely impacting other system operations. Therefore, we separated the task execution process from the system usage. We chose to execute tasks through a distributed scheduling platform, then return the results to the original system via an interface. By expanding the nodes of the scheduling platform and distributing task execution, we can meet the requirement of executing a large number of tasks simultaneously.

[0006] This invention combines DolphinScheduler and Kettle, placing configured Kettle tasks on the DolphinScheduler platform for execution. This fully utilizes Kettle's rich components for data processing, while placing tasks on a distributed scheduling platform increases the number of tasks executed simultaneously. By combining the advantages of both software programs, it enables the synchronous execution of large-scale data integration tasks, meeting the needs of processing massive amounts of data. Simultaneously, it reduces server performance requirements, which can be compensated for by expanding the number of services deployed on the scheduling platform.

[0007] Step 1: Add a new task execution plugin package task-kettle. According to the architecture design of the scheduling platform, perform secondary development on WorkerServer and add a service package for executing Kettle tasks; create a factory channel, create the channel, and build the task implementation method; add KettleTaskChannelFactory to implement TaskChannelFactory; create a new KettleTaskChannel and implement TaskChannel; create a new KettleTask that inherits from AbstractTask and build the task implementation method.

[0008] Step 2: Import the necessary packages for Kettle execution. These include: `kettle-engine.jar` (Kettle engine, responsible for executing Kettle jobs and transformation logic, and calling the core module); `kettle-xml-plugin.jar` (Kettle plugin extension, responsible for loading Kettle plugin services); `kettle-metastore.jar` (Kettle metadata, responsible for managing metadata for all Kettle databases, jobs, and transformations); `kettle-db.jar` (Kettle data source, integrating and adapting database modules); and `kettle-core.jar` (Kettle core modules, including data processing, etc.).

[0009] Step 3: Integrate the DolphinScheduler scheduling platform into the newly added task-kettle service.

[0010] dolphinscheduler-spi.jar is a service discovery package for the scheduling platform plugin.

[0011] dolphinscheduler-datasource-api.jar is a unified database management package for the scheduling platform.

[0012] dolphinscheduler-task-api.jar is a unified task management package for the scheduling platform.

[0013] Step 4: Construct the task and obtain the TaskParams parameter required to execute the scheduled task;

[0014] Step 5: Depending on the source of the Kettle task, there are two scenarios:

[0015] a: By reading the Kettle resource repository and obtaining the configured task information, the integration method is as follows:

[0016] Add the Kettle component in the dolphinscheduler management page by dragging and dropping it into the task definition. In the Add or Modify Kettle Task Configuration page, select Kettle resource library information and configure the task path and name, maximum execution duration, and callback address. Other settings will use the default configuration information provided by the scheduling platform. In the dolphinscheduler task scheduler, read the data source, task name, and path configured for the task. Obtain the Kettle task information through the loadTransformation method and perform the task execution operation.

[0017] b: Manage Kettle tasks by calling the dolphinscheduler API interface through other systems; the interface information contains task information and running parameters in XML format; convert the XML format tasks into ktr files and save them to a local folder; the system reads the ktr files on the local machine to perform Kettle task execution operations;

[0018] Step 6: During execution, determine the task execution time and output the execution log; when the task time is exceeded, automatically stop the task to prevent the task from being blocked due to long-term execution;

[0019] Step 7: Task execution callback. After the task is completed, if it is necessary to transfer the execution result data to other systems, configure the callback address when configuring the task. This will allow the task execution log information and result information to be transferred to other systems for storage and processing.

[0020] Step 8: After completing the above coding integration, package dolphinscheduler, redeploy it, and complete the secondary development of dolphinscheduler; adjust the number of exec-threads for dolphinscheduler task execution processes.

[0021] The beneficial effects of this invention are:

[0022] By combining Kettle, a software with rich data processing components, with the distributed scheduling platform DolphinScheduler, the advantages of both software are combined to expand the application methods of data integration. Large batches of Kettle data integration tasks can be executed simultaneously, improving data integration efficiency. This approach and application are key technical features of this invention.

[0023] The original system could only handle a maximum of 100 tasks simultaneously, and it frequently crashed. Now, with task scheduling moved to the DolphinScheduler platform (2 master nodes, 1 API node, and 3 worker nodes), it can schedule around 800 tasks daily, averaging about 180,000 scheduling operations, with server CPU utilization averaging 30%. If the task volume continues to increase, additional worker nodes will be added to the scheduling platform to meet the demand for large-scale task execution. Attached Figure Description

[0024] Figure 1 This is a schematic diagram of the design architecture;

[0025] Figure 2 Flowchart for Kettle integration;

[0026] Figure 3 This is a diagram illustrating the task execution. Detailed Implementation

[0027] DolphinScheduler is a distributed, open-source scheduling system that provides a visual, scalable, and highly available solution for task scheduling and data processing.

[0028] Kettle is an open-source ETL tool written entirely in Java. It can run on Windows, Linux, and Unix, is portable and requires no installation, offers highly efficient and stable data extraction, and provides rich transformation and processing functions. It supports data cleaning, transformation, filtering, aggregation, and other operations, enabling users to flexibly process and transform data.

[0029] This invention combines the open-source scheduling platform DolphinScheduler with the open-source data collection tool Kettle. The configured Kettle tasks are centrally scheduled and executed on the DolphinScheduler platform. This allows full utilization of the rich components in Kettle for data processing, while the tasks are executed on a distributed scheduling platform. At the same time, by expanding the nodes of the scheduling platform, the number of tasks executed synchronously can be increased, reducing the performance requirements of the server and compensating for this by expanding the number of services.

[0030] Leveraging the decentralized architecture of DolphinScheduler, Master and Worker register heartbeats with ZooKeeper. The Master processes its own Commands based on resource slots and distributes tasks to workers via selectors, achieving a decentralized Master and Worker cluster. Kettle tasks are then executed by configuring Kettle programs within the Workers, and the results are finally saved or synchronized to other systems. See also... Figure 1 .

[0031] In this invention, the Kettle task configuration can be sourced through two methods: 1. Configuring tasks through the Kettle client and saving the configured tasks to a resource database. When the server executes a task, it only needs to read the data from the resource database. 2. Strings generated through web page configuration or other methods can be converted into ktr resource files that can be run in the Kettle tool, and data integration tasks can be executed through the ktr file.

[0032] This is achieved through the following steps:

[0033] I. System Development and Integration (See attached document) Figure 2

[0034] This invention is based on DolphinScheduler and integrates Kettle data execution. The integration method includes the following steps:

[0035] Step 1: Add a new task execution plugin package task-kettle. According to the architecture design of the scheduling platform, refer to the official website of the scheduling platform [1], perform secondary development on the WorkerServer, add a service package that can execute Kettle tasks; create a factory channel, create a channel, and build a task implementation method. Add a KettleTaskChannelFactory to implement TaskChannelFactory. Create a new KettleTaskChannel and implement TaskChannel.

[0036] Create a new KettleTask that inherits from AbstractTask, and construct the implementation method of the task.

[0037] Step 2: Import the necessary packages for Kettle to run, which include...

[0038] The purpose of kettle-engine.jar is to act as the Kettle engine, responsible for executing Kettle's specific jobs and transformation logic, and it calls the core module.

[0039] The purpose of kettle-xml-plugin.jar is to extend Kettle's plugin functionality and load Kettle's plugin service modules.

[0040] The purpose of kettle-metastore.jar is to manage Kettle's metadata, specifically the metadata module for all Kettle databases, jobs, and transformations.

[0041] The purpose of kettle-db.jar is to act as a data source for Kettle, integrating and adapting database modules.

[0042] The purpose of kettle-core.jar: The core module of Kettle, including data processing, etc.

[0043] Step 3: Integrate the DolphinScheduler scheduling platform into the newly added task-kettle service.

[0044] dolphinscheduler-spi.jar is a service discovery package for the scheduling platform plugin.

[0045] The purpose of dolphinscheduler-datasource-api.jar is to provide a unified database management package for the scheduling platform.

[0046] The purpose of dolphinscheduler-task-api.jar is to provide a unified task management package for the scheduling platform.

[0047] Step 4: Construct the task and obtain the parameters TaskParams required to execute the scheduled task.

[0048] Step 5: Depending on the source of the Kettle task, there are two scenarios:

[0049] a: By reading the Kettle resource repository and obtaining the configured task information, the integration method is as follows:

[0050] In the dolphinscheduler management page, you can add the Kettle component by dragging and dropping it into the task definition. On the add or modify Kettle task configuration page, select the Kettle repository information and configure the task path and name, maximum execution duration, and callback address. Other settings can use the default configuration information provided by the scheduling platform. In the dolphinscheduler task scheduler, the data source, task name, and path configured in the task configuration are read. The `loadTransformation` method is used to obtain the Kettle task information and execute the task.

[0051] b: Manage Kettle tasks by calling the dolphinscheduler API interface from other systems. The interface information contains task information and execution parameters in XML format. The XML tasks are converted into KTR files and saved to a local folder. The system then reads the local KTR file to execute the Kettle tasks.

[0052] Step 6: During execution, determine the task execution time and output the execution log. The task will automatically stop after the set time has elapsed to prevent it from running for an extended period and causing blockage.

[0053] Step 7: Task execution callback. After the task is completed, if it is necessary to transfer the execution result data to other systems, configure the callback address when configuring the task. This will allow the task execution log information and result information to be transferred to other systems for storage and processing.

[0054] Step 8: After completing the above code integration, package dolphinscheduler, redeploy it, and complete the secondary development of dolphinscheduler. Adjust the number of exec-threads for dolphinscheduler task execution; the default is 100 processes. If the task volume is large, this parameter can be increased.

[0055] Note: During Kettle execution, some special components require separate registration and reference to the Kettle component package. This can be done simply by adding the package to the final packaged folder. For example, if Kettle needs to perform XML file parsing tasks, simply copy the `pdi-xml-plugin-core` JAR file from the `pdi-xml-plugin` folder within the `plugins` folder of Kettle to the `lib` folder of each worker node in the DolphInscheduler scheduling platform. After restarting the worker node, Kettle can then execute the relevant XML file parsing tasks.

[0056] II. System operation and task execution scheduling process, please refer to... Figure 3

[0057] Step 1: After completing system integration, add Kettle tasks through the interface or API, go online, publish the tasks, and then execute them. You can also set up timed scheduling in the interface to complete automatic task scheduling.

[0058] Step 2: Start the task execution in the dolphinscheduler platform through the management interface or API.

[0059] Step 3: The started tasks will be managed and distributed uniformly by the master node of dolphinscheduler.

[0060] Step 4: Distribute tasks to different worker nodes for execution and load balancing.

[0061] Step 5: After the task is completed, if it is necessary to transfer the execution result data to other systems, configure the callback address when configuring the task. This will allow the task execution log information and result information to be transferred to other systems for storage and processing.

[0062] Glossary

[0063] Kettle is an open-source ETL tool written entirely in Java. It can run on Windows, Linux, and Unix, is portable and requires no installation, offers highly efficient and stable data extraction, and provides rich transformation and processing functions. It supports data cleaning, transformation, filtering, aggregation, and other operations, enabling users to flexibly process and transform data.

[0064] DolphinScheduler is a distributed, open-source scheduling system that provides a visual, scalable, and highly available solution for task scheduling and data processing.

Claims

1. A method for integrating Kettle data based on a unified scheduling platform, characterized in that... Includes the following steps: Step 1: Add a new task execution plugin package task-kettle. Based on the architecture design of the scheduling platform, perform secondary development on the WorkerServer and add a service package for executing Kettle tasks. Create a factory channel, create the channel, and construct the task implementation method; Each of the following is implemented: a new KettleTaskChannelFactory is created to implement TaskChannelFactory; a new KettleTaskChannel is created and implemented to implement TaskChannel; a new KettleTask is created that inherits from AbstractTask, and the implementation method of the task is constructed. Step 2: Import the necessary packages for Kettle execution. These include: `kettle-engine.jar` (Kettle engine, responsible for executing Kettle jobs and transformation logic, and calling the core module); `kettle-xml-plugin.jar` (Kettle plugin extension, responsible for loading Kettle's plugin service module); `kettle-metastore.jar` (Kettle metadata, responsible for managing metadata for all Kettle databases, jobs, and transformations); `kettle-db.jar` (Kettle data source, integrating and adapting database modules); and `kettle-core.jar` (Kettle core module, including some data processing...). Step 3: Integrate the DolphinScheduler scheduling platform into the newly added task-kettle service. dolphinscheduler-spi.jar is a service discovery package for the scheduling platform plugin. dolphinscheduler-datasource-api.jar is a unified database management package for the scheduling platform. dolphinscheduler-task-api.jar is a unified task management package for the scheduling platform. Step 4: Construct the task and obtain the TaskParams parameter required to execute the scheduled task; Step 5: Depending on the source of the Kettle task, there are two scenarios: a: By reading the Kettle resource repository and obtaining the configured task information, the integration method is as follows: Add the Kettle component in the dolphinscheduler management page by dragging and dropping it into the task definition. In the Add or Modify Kettle Task Configuration page, select Kettle resource library information and configure the task path and name, maximum execution duration, and callback address. Other settings will use the default configuration information provided by the scheduling platform. In the dolphinscheduler task scheduler, read the data source, task name, and path configured for the task. Obtain the Kettle task information through the loadTransformation method and perform the task execution operation. b: Manage Kettle tasks by calling the dolphinscheduler API interface through other systems; the interface information contains task information and running parameters in XML format; convert the XML format tasks into ktr files and save them to a local folder; the system reads the ktr files on the local machine to perform Kettle task execution operations; Step 6: During execution, determine the task execution time and output the execution log; when the task time is exceeded, automatically stop the task to prevent the task from being blocked due to long-term execution; Step 7: Task execution callback. After the task is completed, if it is necessary to transfer the execution result data to other systems, configure the callback address when configuring the task. This will allow the task execution log information and result information to be transferred to other systems for storage and processing. Step 8: After completing the above coding integration, package dolphinscheduler, redeploy it, and complete the secondary development of dolphinscheduler; adjust the number of exec-threads for dolphinscheduler task execution processes.

Images