Method for kettle data integration based on unified scheduling platform

Abstract

The invention relates to the technical field of data integration, in particular to a kettle data integration method based on a unified scheduling platform. According to the method, the DolphinScheduler and the kettle are combined, and the configured kettle task is put into the DolphinScheduler scheduling platform to be executed, so that rich components in the kettle can be fully utilized to process data, the task is put into the distributed scheduling platform to be executed, the number of the synchronously executed tasks can be increased, the advantages of two pieces of software are matched, synchronous execution of a large batch of data integration tasks is realized, and the efficiency is improved. And the demand of processing mass data is met. Meanwhile, the new energy requirement of the server is reduced, and the number of deployed scheduling platform services can be increased to make up.

Description

Technical Field

[0001] The present invention relates to the technical field of data integration, and in particular to a method for kettle data integration based on a unified scheduling platform. Background Art

[0002] In the prior art, Kettle is the most powerful open source data integration tool currently available, and it is widely used and has rich data processing components, but there is no unified task scheduling method. Kettle is built separately on the server or in a cluster. The tasks configured by the client are saved in the resource library, and the timed scheduling is set by the server's own timer, or a custom JAVA program is developed to manage the kettle task and perform the timed scheduling execution of the task. However, the JAVA program can only be performed one-to-one, and distributed task distribution processing and execution cannot be performed. Therefore, the kettle task running in a single system is online. After project implementation verification: on a 16-core, 8G Linux server, there are about 200 tasks, which are executed once every half an hour. The CPU of the server reaches more than 80%, which will seriously affect other functions of the deployment, and the system begins to freeze. If multiple systems are deployed, multiple management interfaces are required for management and maintenance, which makes the operation very inconvenient and affects the user experience and use. Among the many open source scheduling platforms, there are almost no ones that support kettle task scheduling.

[0003] DolphinScheduler is a distributed and scalable open source visual DAG workflow task scheduling system. It is suitable for enterprise-level scenarios and provides a solution for visual operation tasks, workflows, and full life cycle data processing. It closely fits the big data ecosystem and is a good scheduling platform for processing large amounts of data. It provides nearly 20 task types such as Spark, Hive, M / R, Python, Sub_process, Shell, etc. However, the existing technology does not support the running of kettle tasks.

[0004] The present invention adopts the combination of DolphinScheduler and Kettle, combining the advantages of the two softwares, and can execute Kettle tasks in a distributed manner. It is necessary to adapt the existing Kettle configuration tasks without changing the original task structure, thereby reducing the workload and realizing distributed scheduling execution of tasks. At the same time, it realizes the synchronous execution of large-scale data integration tasks and handles the needs of massive data. Summary of the invention

[0005] In the process of data integration, we usually choose the kettle data integration tool, which is easy to get started and easy to operate. The Kettle tool has its own independent client operation interface, which needs to be installed locally, and is not convenient for users to use. Therefore, we web-based the kettle, and migrate the process of the kettle client operation to the operation through the web page through Java development. The kettle task execution is also started and executed through the Java system. The service is deployed to a 32-core Linux server. When there are more than 100 tasks executed at the same time, the server CPU will reach more than 80% when it is scheduled 2000 times a day, which has high requirements on server performance and seriously affects the operation of other systems. Therefore, we separate the task execution process from the use system. Choose to execute the task through the distributed scheduling platform, and then return the execution result to the original system through the interface. By expanding the nodes of the scheduling platform, the tasks are executed in a dispersed manner to meet the requirements of executing a large number of tasks at the same time.

[0006] The present invention adopts the combination of DolphinScheduler and Kettle, and puts the configured Kettle task into the DolphinScheduler scheduling platform for execution, so that the rich components in Kettle can be fully utilized to process data, and the task is placed in the distributed scheduling platform for execution, which can increase the number of tasks executed synchronously, and combine the advantages of the two software to achieve the synchronous execution of large-scale data integration tasks and handle the needs of massive data. At the same time, the requirements for server energy are reduced, and the number of deployed scheduling platform services can be expanded to compensate.

[0007] Step 1: Add a task execution plug-in package task-kettle, perform secondary development on WorkerServer according to the architecture design of the scheduling platform, add a service package for executing kettle tasks; create a factory channel, create a channel, and build a task implementation method; add KettleTaskChannelFactory to implement TaskChannelFactory respectively; create a new KettleTaskChannel and implement TaskChannel, create a new KettleTask to inherit AbstractTask, and build a task implementation method; Step 2: Import the file package required for Kettle execution, which includes the Kettle engine in the role of kettle-engine.jar, which is responsible for executing the specific jobs and conversion logic of Kettle and calling the core module; the Kettle plug-in extension in the role of kettle-xml-plugin.jar, which is responsible for loading the plug-in service module of Kettle; the Kettle metadata in the role of kettle-metastore.jar, which is responsible for managing all databases, jobs and conversion metadata modules of Kettle; the Kettle data source in the role of kettle-db.jar, which integrates the adaptation database module; the Kettle core module in the role of kettle-core.jar, including some data processing, etc. Step 3: Introduce the DolphinScheduler scheduling platform into the newly added task-kettle service dolphinscheduler-spi.jar is a scheduling platform plug-in service discovery package. 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: Build the task and obtain the TaskParams parameters required to execute the scheduled task; Step 5: According to different sources of kettle tasks, there are two situations: a: Get the configured task information by reading the kettle resource library. Integration method: Add the kettle component in the Dolphinscheduler management page and drag it to the task definition. In the Add or Modify Kettle Task Configuration page, select the kettle resource library information, configure the task path and name, maximum execution time, and callback address; for other settings, use the configuration information provided by the scheduling platform by default. In the Dolphinscheduler task scheduling, read the data source, task name, and path of the task configuration; obtain the kettle task information through the loadTransformation method and perform the task operation; b: Call the API interface of Dolphinscheduler through other systems to manage kettle tasks; the interface information contains task information and running parameters in XML format; convert the XML format task into a ktr file and save it to a local folder. The system reads the ktr file of the local machine and performs the kettle task execution operation; Step 6: During the execution process, determine the task execution time and output the execution log; when the task setting time is exceeded, the task is automatically stopped to prevent the task from being blocked due to long-term execution; Step 7: Task execution callback. After the task is completed, if the execution result data needs to be transmitted to other systems, configure the callback address when configuring the task. In this way, the log information and result information of the task execution can be transmitted to other systems for storage and processing. Step 8: After completing the above code integration, package and redeploy Dolphinscheduler to complete the secondary development of Dolphinscheduler; adjust the number of exec-threads for Dolphinscheduler task execution processes.

[0008] Beneficial effects of the present invention: Combining Kettle, which has rich data processing components, with the distributed scheduling platform DolphinScheduler, the advantages of the two software are combined to expand the application method of data integration. Large batches of Kettle data integration tasks can be executed simultaneously, improving data integration efficiency. Such ideas and applications are important technical points embodied in the present invention.

[0009] The original system can only handle 100 tasks at a time, and the system often freezes. Now, after putting the task scheduling on the DolphinScheduler scheduling platform, there are 2 master nodes, 1 api node, and 3 worker nodes. Now the number of scheduled tasks is about 800, the daily scheduling is about 180,000 times, and the server CPU usage rate is 30% on average. If the task volume continues to grow, the worker nodes of the new scheduling platform can be added to meet the needs of large-scale task execution. BRIEF DESCRIPTION OF THE DRAWINGS

[0010] Figure 1 This is a schematic diagram of the design architecture; Figure 2 Integrate flow chart for Kettle; Figure 3 Schematic diagram of task execution. DETAILED DESCRIPTION

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

[0012] Kettle is an open source ETL tool, written in pure Java, which can run on Windows, Linux, and Unix. It is green and does not require installation. It extracts data efficiently and stably, and has rich conversion and processing functions. It supports data cleaning, conversion, filtering, aggregation and other operations, allowing users to flexibly process and convert data.

[0013] The present invention adopts a combination of the open source scheduling platform DolphinScheduler and the open source collection tool Kettle, and puts the configured Kettle tasks into the DolphinScheduler scheduling platform for centralized scheduling and execution. In this way, the rich components in Kettle can be fully utilized to process data, and the tasks are placed in the distributed scheduling platform for execution. At the same time, by expanding the nodes of the scheduling platform, the number of tasks executed simultaneously can be increased, and the requirements for server performance can be reduced, which can be compensated by expanding the number of services.

[0014] Using the decentralized architecture design of DolphinScheduler, Master / Worker register heartbeats in Zookeeper. The Master processes its own Command based on resource slots and distributes tasks to workers through selectors, thus achieving a decentralized Master and Worker cluster. Then, by configuring the Kettle execution task program in the Worker, the Kettle task is started and run, and finally the results are saved or synchronized to other systems. Figure 1 .

[0015] The kettle task configuration source in the present invention has a way: 1. Configure the task through the kettle client, save the configured task to the resource database, and when the server executes the task, it only needs to read the resource library data to execute it. 2. The string generated by web page configuration or other methods can be converted into a ktr resource file that can be run in the kettle tool, and the data integration task is executed through the ktr file.

[0016] This is achieved through the following steps: 1. System development and integration, see Figure 2 The present invention is based on DolphinScheduler, and combines kettle data integration execution. The integration method includes the following steps: Step 1: Add a task execution plug-in package task-kettle. According to the architecture design of the scheduling platform, refer to the official website of the scheduling platform [1], conduct secondary development of WorkerServer, add a service package that can execute kettle tasks; create a factory channel, create a channel, and build a task task implementation method. Add KettleTaskChannelFactory to implement TaskChannelFactory respectively. Create a new KettleTaskChannel and implement TaskChannel. Create a new KettleTask that inherits AbstractTask and constructs the implementation method of the task.

[0017] Step 2: Import the file package required for kettle execution, which includes Kettle-engine.jar Function: Kettle engine, responsible for executing the specific operations and conversion logic of kettle, and calling the core module, kettle-xml-plugin.jar Function: kettle plug-in extension, responsible for loading kettle plug-in service module, kettle-metastore.jar Function: Kettle metadata, responsible for managing all databases, jobs and conversion metadata modules of Kettle, kettle-db.jar Function: Kettle data source, integrated adaptation database module, kettle-core.jar Function: Kettle's core module, including some data processing, etc. Step 3: Introduce the DolphinScheduler scheduling platform into the newly added task-kettle service dolphinscheduler-spi.jar Function: Scheduling platform plug-in service discovery package, dolphinscheduler-datasource-api.jar Function: Unified database management package for scheduling platform, dolphinscheduler-task-api.jar Function: Unified task management package for scheduling platform.

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

[0019] Step 5: According to different sources of kettle tasks, there are two situations: a: Get the configured task information by reading the kettle resource library. Integration method: To add a kettle component in the dolphinscheduler management page, you can drag it into the task definition. In the add or modify kettle task configuration page, select the kettle resource library information, configure the task path and name, maximum execution time, and callback address. For other settings, you can use the configuration information provided by the scheduling platform by default. In the dolphinscheduler task scheduling, read the data source, task name, and path of the task configuration. Get the kettle task information through the loadTransformation method and execute the task.

[0020] b: Use other systems to call the API interface of Dolphinscheduler to manage kettle tasks. The interface information contains the task information and running parameters in XML format. Convert the XML format task into a ktr file and save it to a local folder. The system reads the ktr file on the local machine to perform the kettle task execution operation.

[0021] Step 6: During the execution process, determine the task execution time and output the execution log. When the task setting time is exceeded, the task will be automatically stopped to prevent the task from being blocked due to long-term execution.

[0022] Step 7: Task execution callback. After the task is completed, if the execution result data needs to be transmitted to other systems, configure the callback address when configuring the task. In this way, the log information and result information of the task execution can be transmitted to other systems for storage and processing.

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

[0024] Note: During the execution of kettle, for some special components, you need to register and reference the kettle component package separately. You only need to add it to the folder after the final package is successfully completed. For example, if kettle needs to perform the task of parsing XML files, you only need to copy the jar package of pdi-xml-plugin-core in the plugins folder of kettle to the lib folder of each worker node of the dolphinscheduler scheduling platform. Restart the worker node, and kettle can perform the tasks related to parsing XML files.

[0025] 2. System operation, task execution scheduling process, see Figure 3 Step 1: After completing the system integration, add a kettle task through the interface or interface, launch the task, publish the operation, and then execute the task. You can also set a scheduled schedule in the interface to complete automatic task scheduling.

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

[0027] Step 3: The started tasks will be managed and distributed to the master node of Dolphinscheduler.

[0028] Step 4: The tasks are distributed to different worker nodes for execution and load processing.

[0029] Step 5: After the task is completed, if the execution result data needs to be transmitted to other systems, configure the callback address when configuring the task, so that the log information and result information of the task execution can be transmitted to other systems for storage and processing.

[0030] Glossary Kettle is an open source ETL tool, written in pure Java, which can run on Windows, Linux, and Unix. It is green and does not require installation. It extracts data efficiently and stably, and has rich conversion and processing functions. It supports data cleaning, conversion, filtering, aggregation and other operations, allowing users to flexibly process and convert data.

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

Claims

1. A method for kettle data integration based on a unified scheduling platform, characterized in that The steps include: Step 1: Add a new task execution plug-in package task-kettle, perform secondary development on WorkerServer according to the architecture design of the scheduling platform, and add a service package to execute the kettle task; Create a factory channel, create a channel, and build a task implementation method; Add KettleTaskChannelFactory to implement TaskChannelFactory; create KettleTaskChannel and implement TaskChannel, create KettleTask to inherit AbstractTask, and build the implementation method of task; Step 2: Import the file package required for Kettle execution, which includes the Kettle engine in the role of kettle-engine.jar, which is responsible for executing the specific jobs and conversion logic of Kettle and calling the core module; the Kettle plug-in extension in the role of kettle-xml-plugin.jar, which is responsible for loading the plug-in service module of Kettle; the Kettle metadata in the role of kettle-metastore.jar, which is responsible for managing all databases, jobs and conversion metadata modules of Kettle; the Kettle data source in the role of kettle-db.jar, which integrates the adaptation database module; the Kettle core module in the role of kettle-core.jar, including some data processing, etc. Step 3: Introduce the DolphinScheduler scheduling platform into the newly added task-kettle service dolphinscheduler-spi.jar is a scheduling platform plug-in service discovery package. 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: Build the task and obtain the TaskParams parameters required to execute the scheduled task; Step 5: According to different sources of kettle tasks, there are two situations: a: Get the configured task information by reading the kettle resource library. Integration method: Add the kettle component in the Dolphinscheduler management page and drag it to the task definition. In the Add or Modify Kettle Task Configuration page, select the kettle resource library information, configure the task path and name, maximum execution time, and callback address; for other settings, use the configuration information provided by the scheduling platform by default. In the Dolphinscheduler task scheduling, read the data source, task name, and path of the task configuration; obtain the kettle task information through the loadTransformation method and perform the task operation; b: Call the API interface of Dolphinscheduler through other systems to manage kettle tasks; the interface information contains task information and running parameters in XML format; convert the XML format task into a ktr file and save it to a local folder. The system reads the ktr file of the local machine and performs the kettle task execution operation; Step 6: During the execution process, determine the task execution time and output the execution log; when the task setting time is exceeded, the task is automatically stopped to prevent the task from being blocked due to long-term execution; Step 7: Task execution callback. After the task is completed, if the execution result data needs to be transmitted to other systems, configure the callback address when configuring the task. In this way, the log information and result information of the task execution can be transmitted to other systems for storage and processing. Step 8: After completing the above code integration, package and redeploy Dolphinscheduler to complete the secondary development of Dolphinscheduler; adjust the number of exec-threads for Dolphinscheduler task execution processes.

Images