Four Stars

Recursive component for handling hierarchies?

Hi,
How can I handle recursive actions to travel down/up a tree hierarchy. In SQL you have the Oracle 'connect by' or the DB2/SQL-Server/Teradata CTE syntax to do a query on a tree. See both examples in this document.
How would you do that in Talend?
Tags (1)
4 REPLIES
Employee

Re: Recursive component for handling hierarchies?

I don't think we can handle this feature with Talend Open Studio 1.1 (unless of course data come from Oracle or DB22...)
Let's resume how I understand the problem... In a data flow I have something like:
name               id   parent
------------------------------
Earth 1 5
Europe 2 1
Asia 3 1
Moon 4 5
Solar system 5 6
Universe 6 NULL
England 7 2
London 8 7
China 9 3
Trafalgar Square 10 7

This data flow describes a hierarchy that could be represented as:
Universe
`-- Solar system
|-- Earth
| |-- Asia
| | `-- China
| `-- Europe
| `-- England
| `-- London
| `-- Trafalgar Square
`-- Moon

Knowing this hierarchy, we might want to ask some questions:
* How many children does Asia has? (directly and indirectly)
* Is Europe a child of England?
* Is London a parent of Trafalgar Square?
As Talend Open Studio works in a "row by row" mode, we need to find to find
this kind of information on the current if we want to ask these question in a tMap for example.
My experience is to solve the problem "how to store a tree in a flat data structure" by adding a field representing the hierarchy from the current item to the root item.
name               id   parent       hierarchy  nb_direct_children  nb_children
-------------------------------------------------------------------------------
Earth 1 5 6,5,1 2 6
Europe 2 1 6,5,1,2 1 3
Asia 3 1 6,5,1,3 1 1
Moon 4 5 6,5,4 0 0
Solar system 5 6 6,5 1 8
Universe 6 NULL 6 1 9
England 7 2 6,5,1,2,7 1 2
London 8 7 6,5,1,2,7,8 1 1
China 9 3 6,5,1,3,9 0 0
Trafalgar Square 10 7 6,5,1,2,7,8,10 0 0

The calculation of the "hierarchy", "nb*_children" fields would have to be made in a dedicated component.
Then we would have a system routine module providing function to handle the "hierarchy" field, in a tMap, you could to isChildOf($row1, 4). In the isChildOf function Perl would analyse the "hierarchy" string and return true/false.
delostilos (and others) would this kind of solution be sufficient?
Four Stars

Re: Recursive component for handling hierarchies?

Hi,
do you mean that the 'hierarchy' is a path to the root, or something like the DB2 node type explained in this document?
Employee

Re: Recursive component for handling hierarchies?

do you mean that the 'hierarchy' is a path to the root, or something like the DB2 node type explained in this document?

This is exactly what I meant, thanks for the document link.
What's your opinion about that?
Four Stars

Re: Recursive component for handling hierarchies?

Hi,
I think it is a workable solution for hierarchies and tree's. It has some similarities with Xpath I think.