In this Blog, I am going to discuss about “How to delete data from Access Database using Pentaho ETL tool”. In order to understand how to setup Access Database connection in Pentaho, you can use this link for the same.In order to delete data from Access table, consider a table Table1 in a Access Database (Access_Sample_DB.accdb) having two rows . Follow below steps in order to delete record from Table1 Access table.Open Pentaho workspace using spoon.batOpen New TransformationCreate Access Database Connection as per the above link.Drag Execute SQL Script component.Run…
Category: Pentaho
select Query using Access database in Pentaho
In this Blog, I will discuss about ” How to Query the access database tables in Pentaho”. In my previous blog, where I have explained how to create Access database connection in Pentaho. Please go through this blog to have better understanding on Access database in Pentaho. I created one sample table(Table1) in Access database(Access_Sample_DB.accdb) and inserted two records. See the below SS. Open Pentaho using spoon.bat . Open New Transformation and drag “Table Input” Component and select the Access Database Connection. Write the simple select Query to fetch records…
Create Access Database Connection In Pentaho
In this Blog, I will discuss about “how to establish Access Database Connection in Pentaho”. In order to achieve this, Please follow the below steps.STEP-1 : Create Access Database in your local Machine. Go to Access->Blank Database . Give an appropriate name to the database. See the below SS for the same. Once you click on Create Button, Accesss_Sample_DB.accdb will be created. NOTE : You need to have a minimum one table in Access database. STEP-2: Download the access database JDBC Drivers from the below location. Unzip the file and…
Call Pentaho Job using Batch Script
In this post, I will discuss about the “How to call the Pentaho Job in the local Machine which is windows” . To put it differently, call the Pentaho Job using batch script. Below is the sample code of the batch script. call <local_Path>\data-integration\kitchen.bat /file:<Local-Path>\Pipe-delimited-file-variables.kjb In above command, I have mentioned Path of the kitchen.bat file and then Path of the Pentaho job along with Job Name.Below is the ETL code of above Job. This job does a very simple part to load the pipe delimited file to a Table.…
like operations in Pentaho
Let us discuss about How to implement Like operations in Pentaho.Here, I have designed the transformation to handle like operation which is same as like clause in database. See the below Transformation design. See the values in the Generate rows components. See the code written in Modified JavaScript Value Component.Here if you see I have used function “getOcuranceString” which will give value in terms of number of occurrences of a substring in the main string. In other words, we can say if it returns value 0 that means value which…
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…