Create User defined variables in Pentaho Data Integration (Kettle)

 Create User-Defined Variables in Pentaho Data Integration (Kettle)

There are various situations where we have to use user-defined variables instead of static values for several components like DB connections, quey filters etc...

Here I am going to give one example where I defined DB connection details as variables and I am read the DB table using the variables as connection details. I can use those variables where I used the DB connections in transformations/Jobs.

Main Job:

Read XML file:

Using variables to Query DB (read_DB_data): 

XML file in which the connection defined: 

Preparation: 

Step 1:

Now, first, we are reading an XML file where the connection details are given. Then we select those connections details and set them as variables. 

Then We will select the fields defined in the XML file using the Select Values component

Click on each tab to get the fields from the previous step.

Then we will set the fields as variables using the Set Variables component: 

All the variables will be set to the Variables name fields. You can change those names. But it is best practice to give in caps.

Step 2:

Now we read the database using the variables defined in the above step. Pull the Tables input component from the design tab. click on new in the connection section to give the connection details.

Now a new window opens when you can connect to the DB from the list given. I am using MySql DB on my local machine. Select General=>MsSql=>Native(JDBC). Now give the connection details as below.

${HOSTNAME}

${DB}

${PORT}

${USER}

${PASS}

Note: password section still shows ...... even after you give the password as ${PASS}. 

Then save changes and write your own query in the SQL section as shown in the first screenshot in step2.

Step 3: 

After getting the data by executing the query successfully, I have tried to write to an external file. I am writing data to an Xls file.

Drag the component Microsoft Excel Output and give the file output to which you want to write the data.

Step 4: 

Finally step is to combine these two transformations ( setting variables + reading DB).

Now create a new job and drag start component and a transformation. Then add read_xml_file.ktr as reference transformation.

Then add one more transformation and give read_db_data.ktr as reference transformation.

Step 5:

Now run the transformation. The details log looks like below. You can see how variables are set and the data is read from DB and write to file.

I am attaching all the transformations, Jobs, and configuration files. Download here.

https://drive.google.com/file/d/1VGqIAkR5yr4a0l49T-0ScdEuMioqhq0q/view?usp=sharing