02 Feb 2016 Copy rows to result in Kettle | Tips and Tricks

Posted at 14:57h in Enterprise BI, ETL by

Introduction

Managing variables is one of the most complex operations in kettle. In this entry a few tips are explained to make these operations easier.

‘Copy rows to results’ step is the most versatile when we talk about sharing information between objects. This step only exists in transformations and it can convert the row values to parameters, variables, arguments or even pass them to other transformation.

1. The Case

Assuming the following job in kettle (root job):
Image 1

The transformation T1 is the following:

Image 2

The properties file content is:

Image 3

Now, these properties have to be used in a new transformation or job. The root job will now looks like:

Image 4

2. The Possibilities

Depending on how these parameters are going to be used in T2, different methods can be used

a. To be used as rows:

If these rows are needed in the flow, a get rows from result have to be placed into T2 transformation.

Image 5

Now, main job can be executed and log looks like the following.

code 1

b. To be used as parameter/variable:

We have to follow these steps:

  • In the root job open T2 options (Double left click on it).
  • Open the advance tab
  • Check the ‘execute for every input row?’ option 

    Image 6

  • Open Parameters tab
  • Define every Column you want to use as a parameter (A,B,C) 

    Image 7

     

  • Open the T2 transformation (double middle click on it)
  • Define the variables in the T2 transformation.
    Image 8

     

  • Place a Get Variables step and fill the fields with the proper values 

    Image 9

     

Now, main job can be executed and log looks like the following.

code 2

This can be used in a job call instead of a transformation one.

c. To be used as argument:

 

  • In the root job open T2 options (Double left click on it).
  • Open the advance tab
  • Check the ‘execute for every input row?’ and ‘Copy previous results to args?’
  • Open the T2 transformation (double middle click)
  • Place a ‘Get system info’ step.
  • Define Get system Info step as following:

Image 10

Now main job can be executed and log looks like the following:

code 3

3. Conclusion:

When working with Kettle, the rows in the flow can be used in different ways. You can execute jobs or transformations with data created or calculated in previous steps, so Kettle becomes into a powerful and versatile ETL tool, which offers big scalability and dynamic executions.

Tags:
Daniel A
Daniel.albendin@clearpeaks.com