Pentaho Data Integration — using parameters in Transformations

In this article we will see how to use parameters for the input and output file names in pentaho transformation.

Below is the simple transformation to read the input and write to the output file.Here instead of hard coding the filenames and directory we will use parameters to pass the file names in runtime.

In this transformation I am defining two parameter.

1)InputFile

2)OutputFIle

Right click on the canvas and select Transformation Settings.

Assign the parameter in the input file as shown below.

Run the transformation you will see the below screen and accept the defaults.

Transformation ran successfully and created files based on the parameter values

 

Parameters are very useful in the ETL development ,you don’t have to modify the transformations or jobs if you need to change the output file name or etl directory.

 

Pentaho Data Integration — Remove Duplicate rows

In this article I will explain how to use uniquerows transform to remove duplicates.

1)sorting the rows using Sort Rows Transform based on the key field.

2)Use the uniquerows to remove the duplicates.

Sample Input Data:

100,UMA,CYPRESS
100,UMA,CYPRESS
101,POOJI,CYPRESS

Click on input File and complete all required options.

We are reading Comma separated file and also we don’t have any header in the input file.Please check the highlighted options and select them according to your input.

If you want to trim the incoming string fields make sure you don’t specify length of the string field and if we specify the length the trim function will not work.

Now We need to configure Sort Rows transformation.

You can define temp directory if sort stage requires scratch space and also depending on the system memory you can specify number of rows use the memory.If the number exceeds or memory is not available then it will use the specified scratch space.

Based on your requirement you can capture duplicate rows in the error file by checking  Redirect Duplicate row option.And also warning messaged appeared unique rows stage requires sorted input otherwise you don’t get desired results.

We are using output file stage to write the text file output.

After Executing the tranformation here is the output.

100,UMA,CYPRESS
101,POOJI,CYPRESS

Thanks