Scope and Evolution of PDI Variables

In versions prior to PDI (Kettle) 3.2, the tool supported only variables and arguments. With the release of version 3.2, the concept of "parameters" was introduced. It is crucial to understand the scope of these three mechanisms:

• Variables: These act as environment or global variables. They retain their value across different transformations and jobs within the same session.
• Arguments & Parameters: These are treated as local variables. They are specific to a particular transformation or job execution, often used to limit result sets or define specific filter conditions.

1. Environment Variables (Global Scope)

Environment variables are typically used to configure the runtime environment, such as file repository paths, SMTP server details, or database connections. You can think of them as global constants available to the entire PDI session.

1.1 Configuration via kettle.properties

The most commmon way to define global variables is through the kettle.properties file. This file is loaded when Spoon (the GUI) or the command-line tools (Pan/Kitchen) start.

Default file locations:

• Unix/Linux/OSX: $HOME/.kettle/
• Windows (Legacy): C:\Documents and Settings\<username>\.kettle\
• Windows (Vista+): C:\Users\<username>\.kettle\

To define a variable, add a key-value pair. For example: START_DATE=2023-01-01. Note that values here are treated as literals. If you define TODAY=to_char(sysdate,'yyyy-mm-dd'), the variable value will be the string "to_char(sysdate,'yyyy-mm-dd')", not the result of the database function.

To reference these in a transformation, use the syntax ${START_DATE} or %%START_DATE%%. Remember to restart Spoon after editing the properties file for changes to take effect.

2. Positional Arguments (Command-Line)

Available in older versions, arguments are positional. When executing a transformation (pan.sh) or job (kitchen.sh), you can pass up to 10 arguments. Inside the PDI artifact, these are referenced by their position using the syntax ${1}, ${2}, etc.

3. Named Parameters

Introduced in version 3.2, named parameters allow you to pass values by name rather than position. They are defined within the settings dialog of a specific Job (.kjb) or Transformation (.ktr), where you can also assign default values.

During execution in Spoon, a dialog prompts for these values. In the command line, you use the -param:key=value syntax.

Example: Passing Parameters via CLI

Suppose you have a job that requires a directory path and a date limit:

# Linux Execution
./kitchen.sh -file:etl_process.kjb -param:input_dir=/data/inbox -param:limit_date=2023-10-01

# Windows Execution
Kitchen.bat -file:etl_process.kjb "-param:input_dir=/data/inbox" "-param:limit_date=2023-10-01"

Note: On Windows, its often necessary to wrap the parameter definition in quotes to prevent the shell from misinterpreting special characters.

Strategies for Variable Propagation

A common challenge in complex ETL flows is variable scope. If a "Set Variables" step is used in a parent job, child jobs or transformations usually do not inherit these variables automatically unless the "Copy variables to child jobs" option is checked or specific scope settings are applied.

Method 1: Localized Variable Setting

If a specific transformation relies on a variable (e.g., ${target_table}), and running it standalone fails because the parent job isn't setting it, you can add a "Get Variables" or "Set Variables" step directly inside that transformation (or a wrapper job) to hardcode a testing value or retrieve it from a properties file.

Method 2: Global Configuration

For values that rarely change (like a default schema name), define them in kettle.properties. This ensures that whenever Spoon or a Carte server starts, that variable is available globally without needing to pass it through the command line or parent job logic.