regex in Pentaho

Today , I will discuss about the Regular expression in Pentaho.Below are regex which can be used in Pentaho. There are many more as well. I worked on below Regex , so, I mentioned only those which I have used in the code. The values having only digits : ^[0-9]+$ The values having only lowercase characters : ^[a-z]+$ The values having only uppercase characters : ^[A-Z]+$ The values having only uppercase & lowercase characters : ^[a-z|A-Z]+$ The value having list of vowels characters ^[AEIOU]+$ The value having only these characters…

unzip component in Pentaho

Today, I will discuss about the “unzip” component in Pentaho. It will unzip the files present inside the zipped folder and archive the zipped file to another directory. See the Values which I filled in the unzip component.1. Zip file name : Mention the folder where the zipped file is present.2. Source Wildcard(Exp) : Mention the filename pattern of the zipped file. Here , I mentioned all files with zip extension.3. Target Directory : Give the path where you want to copy the unzipped files.4. Create folder : Check the…

Metadata Injection component in Pentaho

Today, I will discuss about the Metadata Injection component in Pentaho. This is one of the fine component in Pentaho. If you have come across scenario where your input file changes based on the number of columns or other way of saying it , that file changes dynamically. In such cases, we need to use Metadata Injection component. I have created the ETL job for such cases in PDI. See the below SS for the same.CRUX OF THE METADATA INJECTION COMPONENT IS INPUTS CONNECTED TO THIS COMPONENT AND THE JOB…

Extract column and value separated by Delimiter

Today, I will discuss about the scenario where data is present in a format column:value . See the below sample data for the same. EmpId :1 Emp_First_Name:Ram Emp_Last_Name:Kumar Address:GachiBowli ,Hyderabad Phone_Number:1234567890 Pin_code:122011 Such kind of data you might get in colleges,schools where admin team ask each student to fill in these details and in backend , it is getting stored in the format mentioned above.See the below ETL code where we fetch employee details from the above data.STEP1: Read Emp data using the “CSV file Input”. See the below SS.Here,…

Concept of Loops in Pentaho

Today, I will discuss about the how to apply loop in Pentaho. Here, first we need to understand why Loop is needed. For example, you need to run search a file and if file doesn’t exists , check the existence of same file again in every 2 minutes until you get the file or another way is to search x times and exit the Loop.See the below ETL job where i created a simple Loop.Set Variables : create a new variable NEW_LOOP and set its value to 0. File Exists…

Add attachment to email in Pentaho job

Today, I will discuss about how to add files in the mail component present in the job section. In order to do this , you have to use the “Add filenames to result” component. See the below SS for the same.Here, you need to perform two steps.1. Use wildcard for text files in the “Add filenames” component in case you need to attach text files. select wildcard according to the need of your project. See the below SS for the same. 2. Do the below Settings in the mail component.…

cassandra input component in Pentaho

Today, I will discuss about “How to use cassandra input component in Pentaho”. For this, the first and foremost criteria is that cassandra database should be downloaded and installed in your local Machine.Once installation is completed, you can start the Apache-cassandra services using below command.cassandra.bat -f ( This batch file is present inside bin folder).In order to create a sample table in the Cassandra database, you need to open another command prompt session where you need to run the cqlsh command(Run this command inside bin folder) which helps to run…

call stored procedure using Table Input component in Pentaho

Today, I will discuss about the scenario which might comes in someone’s development phase wherein we need to call procedure or function using Table input component. Here, I have created the sample function and procedure using sql server Database. See the code snippets. See the Data which is present in the TBL_DIM_CUSTOMER Now, we will call function using select statement inside Table input component. See the below SS. Here , I have passed customer_id value as 5. Click on Preview, you will get required result. In the similar Fashion, we…

Dimension Lookup in Pentaho

Today, I will discuss about the Dimension Lookup in Pentaho. I have seen various blogs and forums where users share their experience about the issues encountered during the implementation of this component. One of the major issue is null values in the Dimension tables. There are two scenarios. 1. When you use this component with “Use Auto Incremental Key” for Technical Field, then it inserts a null values on every run which is a concern .2. When you use this component using “Use table Maximum +1” for Technical Fields, then…

How to pass parameter in carte

Today, I will discuss about “Call Job using Carte with parameters”. I have already explained How to setup carte in windows machine and How to execute job or Transformation using Carte. https://www.allabouttechnologies.co.in/pentaho/run-transformation-as-web-service-using-carte-in-pentaho/ Now, we will extend this blog by passing the parameter to a job. Here I have a job where I have set parameter “file_name”. See the Below images for the same. This file_name parameter is used inside the transformation which can see below. Now, we will run the Job using carte . In order to do this, first…