topic Re: Flatten hierarchy - Iteration on left outer join in Talend Studio

Flatten hierarchy - Iteration on left outer join

Anonymous — Thu, 18 Jan 2018 03:11:44 GMT

Hello.

I have a table (from json) which have 3 columns:

id | description | parent_id

001 | Toys | (NULL)

002 | Vehicles | 001

003 | Cars | 002

... | ... | ...

I need to flatten this hierarchy structure.

One way I can think of, is first doing self join on the parent_id and id, then keep doing left outer join with the original table, until the output with the last column all NULL.

The result should look like the below:

003 | Cars | 002 | 001 | (NULL) |

... | ... | ... | ... | ... |

How can do this in Talend? Or is there any other easier way to flatten such hierarchy?

Re: Flatten hierarchy - Iteration on left outer join

Anonymous — Thu, 18 Jan 2018 23:10:24 GMT

I wrote this a long time ago as an experiment (https://www.rilhia.com/tutorials/talend-connect-example). It is very much along the same lines as what you are attempting. It may work, but it is tricky and was only written to see if I could do it.

The problem you have is that you need to use recursion for this and that is something Talend doesn't handle all that well. You *could* write a recursive routine to do this and return a comma separated list representing your hierarchy. After that you would split that up into different columns using a tMap. I'd probably opt for something like that if I had to do solely inside Talend.

Another way to do it would be to load the data into Oracle and carry out a connect by prior query. Of course that only works if you have the environment to do it that way.

Re: Flatten hierarchy - Iteration on left outer join

prabinpatroc1 — Mon, 10 Feb 2020 20:27:05 GMT

The link you have provided is not working. Can I see the solution in any other ways ?