Building hierarchical data in Kettle (alternative of recursive CTE)

Today I got a requirement to build the hierarchy structure of the available data (like RECURSIVE CTE in SQL Server) in Amazon Redshift database but unfortunately Redshift doesn't support RECURSIVE CTE or PROCEDURAL langugage like variables, stored procedures or user-defined functions.

Requirement:
- Pick the data from table (item) in Redshift database.
- Build the hierarchical data.
- Populate the data into another table (tblParkingItem) in same Redshift database.

So I was able to achieve this by using an ETL tool - in this example using open-source tool named as Penthao Kettle:

Script to create sample table(s) along with some data:

CREATE TABLE item (assetid INT, assetname varchar(25), parentassetid INT)

CREATE TABLE tblparkingitem (assetid INT, assetname varchar(25), parentassetname varchar(25), Lvl INT)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(1,'aa',null)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(100,'a',1)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(101,'b',100)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(102,'c',100)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(103,'d',101)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(105,'e',102)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(106,'f',108)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(107,'e',102)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(108,'g',106)

INSERT INTO item (assetid,assetname,parentassetid) VALUES(109,'bb',null)

Kettle Job/Transformation:

Job: Executes the transformation in specified order & do the looping stuff

Transfomation1: "Get the parent data":

Queries:

• "Populate parent data" component: 

insert into tblparkingitem (assetid , assetname , parentassetname , Lvl)
select assetid, assetname, null, '${Level}' as lvl from item where parentassetid is null;

• "Table input" component: 

select count(1) as RowCnt, '${Level}' + 1 as Lvl from tblparkingitem where lvl = '${Level}';

Here ${Level} is variable whose value is assigned at Job level.

Transfomation2: "Get the child data":

Queries:

• "Populate child data" component: 

insert into tblparkingitem (assetid , assetname , parentassetname , Lvl)
select a.assetid, a.assetname, b.assetname as parentassetname, '${Level}' from item a inner join tblparkingitem b on a.parentassetid=b.assetid and exists (SELECT 1 FROM tblparkingitem c WHERE b.assetid = c.assetid and c.lvl = ('${Level}'-1));

• "Table input" component: 

select count(1) as RowCnt, '${Level}' + 1 as Lvl from tblparkingitem where lvl = '${Level}';


Here ${Level} is variable whose value is assigned at Job level.

To validate the result, you can check the data in tblparkingitem table by executing:
Select * from tblparkingitem;


Attached is the sample code.
Kettle sample code

Database lookup not fetching the latest database field in Penthao - Kettle

Today I was developing a transformation in Kettle & using "Database lookup" step in it. During development I realised that one more column need to be added into the lookup table and then I added that column in database using ALTER TABLE statement.

But in transformation, when I clicked on "Get Lookup Fields" button of "Database lookup" it was not showing the newly added column. I removed the "Database lookup" step from the transformation and then added it back (hoping that it will now show all the columns present in database) but still it was not showing the newly added column.

After googling, I found the following option to handle the above issue:

Go to the main menu action, select Tools -> Database -> Clear Cache

After clearing cache, the newly added column start appearing into the "Database lookup" step.