Constructing Fundamental Workflow Sequences in Pentaho Data Integration - Certbolt

This comprehensive guide delineates the systematic procedures for developing fundamental task flows within Pentaho Data Integration (PDI), often referred to as Kettle. PDI empowers users to design, execute, and monitor complex data transformations and job orchestrations. A key aspect of creating adaptable and reusable data integration solutions involves effectively managing arguments and parameters, which allow for dynamic execution based on external inputs.

The Paradigm of Dynamic Execution in Pentaho Data Integration

In the realm of data integration and ETL (Extract, Transform, Load) processes, the ultimate goal is to create workflows that are not only robust and reliable but also exceptionally flexible. Static, hard-coded jobs and transformations, while functional for a single, unchanging task, quickly become a liability in a dynamic business environment. They lack the adaptability to handle different input files, connect to various environments (development, testing, production), or process data based on ad-hoc criteria without manual intervention and code modification. This is where the true power of Pentaho Data Integration (PDI) shines through: its innate ability to ingest external arguments and parameters.

By designing PDI solutions to accept runtime inputs, you fundamentally transform them from rigid scripts into versatile, reusable tools. A single job can be orchestrated to perform a multitude of operations based on the parameters it receives, promoting a «write once, run anywhere» philosophy. This capability is the cornerstone of building sophisticated, automated, and maintainable data pipelines. This comprehensive guide will explore this concept in depth, moving beyond a simple «hello world» to provide a foundational understanding. We will meticulously deconstruct the process of parameterization, transforming a basic transformation and job into a fully dynamic workflow that responds to external directives.

Foundational Pillars: Understanding PDI’s Input Mechanisms

Before delving into the practical, step-by-step implementation, it is crucial to establish a clear understanding of the primary mechanisms PDI uses for external input: named parameters and command-line arguments. While often used in conjunction, they serve distinct purposes and are defined at different scopes within the Pentaho ecosystem.

Named Parameters are explicitly declared variables within a job or a transformation. Think of them as reserved placeholders that you define by name. Their key characteristic is that they are known to the PDI process before it even starts. You define a parameter, for instance, TARGET_DATABASE_HOST, and can then assign a value to it when you execute the job. This value is then available throughout the execution of that job and can be passed down to any transformations it calls. This makes them ideal for configuration-style data that might change between environments but is consistent for a single run, such as server names, file paths, usernames, or date-range filters.

Command-line arguments, conversely, are positional pieces of data passed to a transformation from an external source at the moment of execution. They are not pre-declared within the transformation’s properties. Instead, PDI simply captures a sequence of strings provided after the script name when running from a command line. The Get System Info step is then used to retrieve these arguments by their position (argument 1, argument 2, etc.). This method is perfectly suited for passing transient, execution-specific data that is not necessarily part of the core configuration, such as a specific customer ID to process or a single-use keyword.

Finally, it’s important to recognize PDI Variables. PDI has a rich set of built-in variables that provide contextual information about the job or transformation’s environment, such as ${Internal.Job.Filename.Directory} (the directory of the current job file) or ${PDI_VERSION}. You can also define your own variables in a kettle.properties file or as environment variables on the operating system. The ${LABSOUTPUT} mentioned in our example is a perfect illustration of an environment variable, allowing you to define a base output directory system-wide, making your PDI jobs portable across different machines without modification. The syntax ${…} is used to resolve both named parameters and variables, and PDI has a specific order of precedence for determining which value to use if names collide.

Architecting a Dynamic Transformation: A Practical Walkthrough

Our first objective is to construct a transformation (.ktr file) that is aware of and can utilize external inputs. We will move beyond a static message and create a transformation that generates a personalized greeting and writes it to a dynamically determined folder location. This process begins within Spoon, the graphical design tool for PDI.

Step 1: Establishing the Transformation Blueprint

Begin by launching the Spoon graphical user interface. Create a new, blank transformation by selecting «File» -> «New» -> «Transformation.» This provides you with an empty canvas, the digital easel upon which you will design your data flow.

Step 2: Infusing the Transformation with a Named Parameter

To control the output location of our file dynamically, we will define a named parameter. This parameter will represent the sub-folder where our final text file will reside.

Press Ctrl+T (or go to «Edit» -> «Transformation properties…») to open the properties window for your transformation. This dialog is the central nervous system for your transformation’s metadata and settings. Navigate to the «Parameters» tab. Click the «Add new» button. In the «Name» column, type HELLOFOLDER. In the «Default value» column, you can enter a fallback value, such as default_output. This default is a safety net; it will be used if the job is ever run without an explicit value being provided for HELLOFOLDER. This practice is invaluable for preventing errors in less-controlled executions. Click «OK» to save this parameter definition.

Step 3: Harnessing the Power of the ‘Get System Info’ Step

Now, we need a mechanism to capture a value passed directly from the command line at runtime. The ideal tool for this is the Get System Info step. In the «Design» tab’s step palette on the left, expand the «Input» category and drag a Get System Info step onto your canvas.

Double-click the step to open its configuration dialog. The purpose of this step is to create new fields in your data stream based on system-level information. In the grid at the bottom, click «Add new.» In the «Name» column, give your new field a descriptive name; let’s use captured_name. In the «Type» column, click the dropdown menu. This list showcases the incredible wealth of information this step can retrieve, from the system date to JVM memory stats. For our purpose, select command line argument 1. This configuration instructs the step to listen for the very first string of text provided after the script name during execution and place it into a field named captured_name. Click «OK.»

Step 4: Crafting a Personalized Message with the ‘Formula’ Step

With our incoming name captured, we can now construct our personalized greeting. While there are several ways to manipulate strings in PDI, the Formula step offers a powerful and intuitive method similar to spreadsheet functions.

Find the Formula step under the «Scripting» category in the step palette and drag it onto the canvas. Create a hop (a data-flow link) from the Get System Info step to the Formula step. Double-click the Formula step. We will add a new field to the stream that will contain our complete message. Click «Add new.» For the «New field» name, enter greeting_message. Set its «Formula» type to String. In the «Formula» box, enter the following expression: «Hello, » & [captured_name] & «! Welcome to the world of dynamic PDI.».

This expression concatenates three parts: the static string literal «Hello, «, the value from the captured_name field we created in the previous step (referenced by its name in square brackets), and another static string literal » Welcome to the world of dynamic PDI.». This demonstrates how easily you can combine static and dynamic data elements. Click «OK.»

Step 5: Directing the Output to a Dynamic Location

The final piece of our transformation is to write the generated message to a text file. The path to this file will be constructed using both an environment variable and our newly created named parameter.

From the «Output» category in the step palette, drag a Text file output step onto the canvas. Create a hop from the Formula step to this new step. Double-click the Text file output step to configure it. In the «Filename» field, you will specify the dynamic path. Enter the following: ${LABSOUTPUT}/${HELLOFOLDER}/greeting.txt.

Let’s dissect this path:

${LABSOUTPUT}: This is a reference to an environment variable. PDI will look for a variable named LABSOUTPUT defined in your operating system or your kettle.properties file. This should point to a base directory for all your exercise outputs, for example, C:\PDI_Labs\Output. Using this variable means you can move your project to another machine with a different directory structure, and you only need to change the variable’s value, not every single transformation.
${HELLOFOLDER}: This is the reference to the named parameter we defined in Step 2. At runtime, PDI will substitute this placeholder with the actual value provided for HELLOFOLDER.
/greeting.txt: This is the static name of our output file.

Next, navigate to the «Content» tab. Ensure the «Encoding» is set to something appropriate like UTF-8. Go to the «Fields» tab and click the «Get fields» button. This will populate the grid with the fields available from the incoming stream. We only want to write our final message, so you can remove all fields except for greeting_message. Click «OK.»

Step 6: Finalizing and Saving the Transformation

Your transformation is now complete. It is designed to accept a positional argument, use it to build a message, and write that message to a file in a location determined by a named parameter. Save your work in a designated transformations subfolder within your project directory. Name the file dynamic_hello_world.ktr.

Orchestrating the Dynamic Job: Passing Values from the Top Down

A transformation on its own is just one part of a larger workflow. To execute it in a controlled manner and to supply the value for our HELLOFOLDER parameter, we need a job (.kjb file). A job acts as an orchestrator, capable of running transformations, executing shell scripts, managing files and folders, and making logical decisions.

Step 1: Establishing the Job Framework

If you have an existing basic job, open it. Otherwise, create a new one («File» -> «New» -> «Job»). Immediately save this job under a new name, such as dynamic_workflow_runner.kjb, in the root of your project directory. This practice of saving under a new name preserves the original and allows for safe experimentation.

Step 2: Propagating the Named Parameter at the Job Level

This is a step of paramount importance and a common point of confusion for beginners. For a job to be able to pass a parameter value down to a transformation, it must first be aware that such a parameter exists. Therefore, we must declare the exact same named parameter at the job level.

Press Ctrl+J (or go to «Edit» -> «Job properties…») to open the job’s properties window. Navigate to the «Parameters» tab. Just as you did in the transformation, click «Add new» and declare a parameter with the exact same name: HELLOFOLDER. You do not need to provide a default value here if you don’t want to, as the job will prompt you for it upon execution. Clicking «OK» registers this parameter with the job.

Step 3: Implementing a Dynamic Folder Creation Step

A well-designed workflow should be self-contained and idempotent, meaning it can be run multiple times without causing errors. A good practice is to ensure the target directory for our output file exists before the transformation attempts to write to it.

From the «File management» category in the job entry palette, drag a Create a folder entry onto the canvas. This will be our starting point, so it won’t have any incoming hops. Double-click it. In the «Folder name» text box, you will use the same dynamic path structure. Enter ${LABSOUTPUT}/${HELLOFOLDER}. This instructs the job to create a folder whose name is determined at runtime by the value of the HELLOFOLDER parameter. Leave the «Abort job if folder exists» option unchecked to ensure the job doesn’t fail on subsequent runs. Click «OK.»

Step 4: Configuring the Transformation Job Entry

Now we will add the core component: the entry that calls and executes our parameterized transformation. From the «General» category, drag a Transformation job entry onto the canvas. Create a hop from the Create a folder entry to this Transformation entry. This ensures that the folder creation attempt always happens before the transformation runs.

Double-click the Transformation entry. Under «Transformation specification,» click the radio button for «Specify by filename.» Click the «Browse» button and navigate to your transformations subfolder to select the dynamic_hello_world.ktr file you created. For superior portability, it is best practice to replace the absolute path with a relative one using an internal PDI variable. Modify the path to look like this: ${Internal.Job.Filename.Directory}/transformations/dynamic_hello_world.ktr. The ${Internal.Job.Filename.Directory} variable automatically resolves to the directory where the current .kjb job file is located, making your project self-contained and movable.

Next, navigate to the «Parameters» tab within this same dialog window. You will see the parameters you defined in the target transformation listed here. In our case, HELLOFOLDER will be present. In the «Value» column next to it, you must enter the variable that will provide its value. Enter ${HELLOFOLDER}. This is the crucial link: you are telling the job entry to take the value of the job’s HELLOFOLDER parameter and pass it into the transformation’s HELLOFOLDER parameter.

Now, switch to the «Arguments» tab. This is where you provide the value for the positional command-line argument that the Get System Info step is expecting. In the grid, you can enter the value directly. For our test, type your name or a test phrase like «PDI Professional» into the first argument slot. Click «OK.»

The Moment of Truth: Execution and Verification

Your fully dynamic workflow is now configured. The job is set to create a folder based on a parameter, then execute a transformation, passing that same parameter and a hard-coded argument to it.

Step 1: Launching the Job from Spoon

Save the dynamic_workflow_runner.kjb job. Click the «Play» (Execute) button in the Spoon toolbar. A «Job execution configuration» window will appear. Because your job has a named parameter defined (HELLOFOLDER), Spoon is now prompting you to provide a value for it. This is the runtime interaction we sought to create. In the «Value» field for HELLOFOLDER, enter a unique folder name, for example, July4th_Dynamic_Run.

Notice that the argument we hard-coded in the job entry («PDI Professional») is also displayed. You could override it here if needed. Click the «Launch» button.

Step 2: Analyzing the Outcome

The job should execute, and you should see green checkmarks on all job entries in the canvas and a success message in the logging panel at the bottom of the screen. Now for the verification.

Navigate to the output directory defined by your LABSOUTPUT environment variable (e.g., C:\PDI_Labs\Output). Inside this directory, you should now see a new folder named July4th_Dynamic_Run. This confirms that the Create a folder job entry correctly received and used the parameter value.

Open this new folder. Inside, you will find a file named greeting.txt. Open this text file. The content should read: Hello, PDI Professional! Welcome to the world of dynamic PDI.. This confirms that the transformation job entry successfully passed the argument to the Get System Info step and that the rest of the transformation logic functioned as designed.

You have successfully created a PDI workflow that is controlled by external inputs, demonstrating the powerful interplay between job-level parameters and transformation-level arguments. This foundational skill is the gateway to building enterprise-grade, automated, and supremely flexible data solutions with Pentaho.

Executing Pentaho Jobs from a Terminal Interface

While Spoon provides a convenient graphical interface for developing and running jobs, there are often scenarios—especially in automated or production environments—where executing jobs directly from a terminal window is preferred or necessary. This section outlines how to launch a Pentaho job and supply required parameters and arguments from a command-line interface.

Running the «Hello World» Job via a Terminal Session

The «hello world» job, which now requires both a named parameter and a command-line argument, can be executed and configured directly from a terminal. This method bypasses the Spoon dialog window, offering greater automation capabilities.

Opening a Terminal Window: Begin by opening your preferred terminal or command prompt window.
Navigating to Kettle Installation Directory: Change your current directory to the location where Pentaho Data Integration (Kettle/PDI) is installed. This directory typically contains the executable scripts.

Executing on Windows Systems: For Windows environments, use the kitchen.bat executable. Type the following command, ensuring paths are correct for your setup:
Bash
C:\pdi-ce>kitchen /file:c:/pdi_labs/hello_world_param.kjb Maria -param:»HELLOFOLDER=my_work» /norep

In this command:
- kitchen is the executable for running jobs.
- /file:c:/pdi_labs/hello_world_param.kjb specifies the full path to your job file.
- Maria is the first command-line argument passed to the transformation.
- -param:»HELLOFOLDER=my_work» sets the value of the HELLOFOLDER named parameter to «my_work».
- /norep suppresses repository connection attempts if not needed.

Executing on Unix, Linux, and macOS Systems: For Unix-like operating systems, use the kitchen.sh shell script. Type:
Bash
/home/yourself/pdi-ce/kitchen.sh /file:/home/yourself/pdi_labs/hello_world_param.kjb Maria -param:»HELLOFOLDER=my_work» /norep

Ensure that the paths (/home/yourself/pdi-ce and /home/yourself/pdi_labs) are correctly adjusted to your actual installation and job file locations.
Important Note: If your job file is in a different directory, modify the /file: path accordingly. You can also substitute «Maria» with your own name. If your name contains spaces (e.g., «John Doe»), remember to enclose the entire command-line argument within double quotation marks (e.g., «John Doe»).
Observing Job Execution Log: As the command executes, you will observe the job’s progress directly in the terminal window, with logging output detailing each step of the job’s execution.
Verifying Output: Once the execution concludes, navigate to your designated output folder (the directory pointed to by your LABS_OUTPUT environment variable). You should find a new folder, specifically named «my_work» (or whatever value you supplied for the HELLOFOLDER parameter). Within this folder, there will be a file named hello.txt. Upon editing this file, you will confirm its content reads: «Hello,Maria!». This demonstrates the successful command-line invocation and dynamic parameter/argument passing.

Leveraging Named Parameters and Command-Line Arguments in Transformations

It is a fundamental capability within Pentaho Data Integration that transformations can inherently accept both arguments passed from the command line and named parameters. This dual input mechanism provides exceptional flexibility. When you initiate the execution of a transformation directly from Spoon, the graphical user interface, a dedicated dialog window will materialize, prompting you to supply the necessary values for both the command-line arguments and the named parameters. Conversely, when executing a transformation via a terminal window, these values are furnished directly within the Pan command line (Pan is the command-line utility specifically for transformations, analogous to Kitchen for jobs).

Invoking the «Hello World» Transformation with Fixed Arguments and Parameters

To illustrate the direct passing of fixed arguments and parameters to a transformation from within a job, we will modify an existing job to hardcode these values. This approach is suitable when the input values are constant and do not need to be prompted at runtime.

Duplicating the Job: Open the hello_world.kjb job (the original, non-parameterized version from the first section) and immediately save it under a new name: hello_world_fixedvalues.kjb. This preserves your previous work while allowing for a specific fixed-value iteration.
Modifying Folder Creation: Double-click the Create a folder job entry. In the «Folder name» textbox, replace any existing dynamic string (like chapter10) with a fixed string, for example, fixedfolder. This means the job will always create a folder with this exact name.
Updating Transformation Path: Double-click the Transformation job entry. Change the Transformation filename to point to the parameterized transformation we created earlier: ${Internal.Job.Filename.Directory}/transformations/hello_world_param.ktr. This ensures the job is calling the correct transformation that expects inputs.
Configuring Fixed Arguments: Navigate to the Argument tab within the transformation job entry’s properties. Here, you can specify fixed values for any command-line arguments expected by the transformation. For this example, add a fixed argument, perhaps «reader».
Setting Fixed Parameters: Switch to the Parameters tab within the same transformation job entry’s properties. Fill it in to set the value for the HELLOFOLDER named parameter directly within the job entry, overriding any default. For instance, set HELLOFOLDER to fixedfolder.
Saving the Job: Confirm all changes by clicking OK and then save the hello_world_fixedvalues.kjb job.
Executing from Terminal: Open a new terminal window and navigate to your Kettle installation directory.

Running on Windows Systems: For Windows, type:
Bash
C:\pdi-ce>kitchen /file:c:/pdi_labs/hello_world_fixedvalues.kjb /norep

Running on Unix, Linux, and macOS Systems: For Unix-like systems, type:
Bash
/home/yourself/pdi-ce/kitchen.sh /file:/home/yourself/pdi_labs/hello_world_fixedvalues.kjb /norep

Notice that this time, you do not provide any parameters or arguments directly on the kitchen command line, as they are now hardcoded within the job itself.
Verifying Output: Once the execution completes, inspect your output folder (defined by LABS_OUTPUT). A new folder named fixedfolder should have been created. Inside this folder, locate hello.txt. Upon opening the file, its content should now read: «Hello, reader!». This confirms that the job successfully passed fixed values as both command-line arguments and named parameters to the transformation.

Orchestrating Conditional Job Entry Execution

A robust Pentaho Data Integration job is not merely a linear sequence of steps; it often incorporates complex logic where the execution of certain job entries is contingent upon the outcome or results of preceding entries within the workflow. This capability allows for sophisticated error handling, branching logic, and dynamic process flows, making jobs highly adaptive to various operational scenarios.

Automating Sales Report Delivery with Error Notifications

Consider a practical business scenario where a daily or weekly sales report needs to be generated and dispatched. In addition to regular report delivery, there’s a critical requirement to notify an administrator immediately if any issues arise during the report generation or delivery process. This demonstrates conditional execution and robust error management.

Prerequisites for this scenario:

MySQL Server with Jigsaw Database: The report generation will rely on data housed within a Jigsaw database, which should be hosted on a running MySQL server. Ensure your MySQL instance is operational and accessible to PDI.
Valid Email Account for Notifications: To facilitate email notifications, you will need at least one valid Gmail account. Alternatively, if you possess familiarity with your own SMTP server configuration, you are free to utilize that instead. This flexibility allows for integration into existing IT infrastructure.

The job design for this scenario would involve a sequence like:

Start: The initial point of the job.
Generate Sales Report Transformation: This transformation would connect to the Jigsaw database, extract relevant sales data, and format it into a report.
Check Report Generation Status (Success/Failure): This entry would evaluate the outcome of the previous transformation. If it succeeded, the job proceeds to send the report. If it failed, a different path is taken.
Send Sales Report Email (on Success): If the report generation was successful, an email job entry would be triggered to send the generated report to the intended recipients (e.g., sales managers).
Send Administrator Warning Email (on Failure): If the report generation failed, a separate email job entry would be activated to send a specific warning email to the administrator, alerting them to the issue. This email would ideally contain details about the error.
End: The termination point of the job.

This conditional branching, facilitated by PDI’s job entry linking and result evaluation, showcases how dynamic, robust, and adaptive task flows can be constructed to meet complex business requirements, ensuring both routine operations and effective incident management.