Recursive component for handling hierarchies?

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?
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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now