topic Re: Using tHashOutput and tHashInput to migrate parent/child relationship in Talend Studio

Using tHashOutput and tHashInput to migrate parent/child relationship

Anonymous — Tue, 13 Dec 2016 10:11:29 GMT

Hello,

I'm fairly new to Talend and I've spent quite a bit of time trying to figure this out this past week and have come across some posts that have gotten me up to this point, and any help would be appreciated. I'm trying migrate some data from a legacy DB to a new system, whose schema containing parent/child relationships is mapped out differently. In the legacy database, I have an child entity (TaPieceJointe) containing document attachments which hold the foreign key to its parent (PceIns). In the target database, this relationship is held in a separate table (modules_relation):

OLD DB Maps TO ==> NEW DB

TaPieceJointe PceCod docs.count
TaPieceJointe PceIns
TaPieceJointe PceNature
TaPieceJointe PcePME
TaPieceJointe PceName docs.title
TaPieceJointe PcePath docs.original_file_name
docs.file_type
docs.file

modules_relation.module_from
modules_relation.id_from (FK to equipment table which I used as lookup in tmap1)
modules_relation.module_to
modules_relation.id_to (FK to docs.count)

Between the posts I've come across, they've suggested using tMysqlLastInsertId, tHashOutput and tHashInput. So essentially what I've done is to:
-insert into the first target table "docs"
-then try to get the auto-increment key value from "docs" by using tMysqlLastInsertId in order to insert into modules-relation in the target DB, which holds the parent/child relationship between the entities "doc" and "equipment" (entity equivalant to foreign key PceIns).

I've tried to follow as best as possible on how to achieve this, but alas I've come across this latest obstacle where tHashInput is not initialized.

Re: Using tHashOutput and tHashInput to migrate parent/child relationship

TRF — Tue, 13 Dec 2016 14:15:25 GMT

Hi,
You cannot you a tHashInput before it has been initialized.
So, you need a first step to populate the tHashOutput (in a sepated subjob), then you can use tHashInput attached to this tHashOutput.
A trivial, but typical, example should looks like this:

tFileInputDelimited(1)-->tHashOutput
|
|
|on Subjob OK
|
|                      tHashInput
|                          |
tFileInputDelimited(2)-->tMap-->tLogRow

Hope this helps,
TRF

Re: Using tHashOutput and tHashInput to migrate parent/child relationship

Anonymous — Fri, 16 Dec 2016 14:20:57 GMT

Hello,

Thanks for the response. What I had trouble with, if you look at the first image was between the first and second tMaps in the flow. I was not able to create a subjob in order to have the tHashOutput initialized. The problem I have is that parent/child reference is modeled differently in the old and new DB, a foreign key is in the old DB, an association in the new DB:

OLD DB NEW DB

instruments =====> equipment (this has already been migrated, not in images)

pieceJointe (holds foreign key to associated instrument) =====> document (does NOT hold FK to equipment table; tried to migrate data in first tMap, which also includes a lookup to equipment ID needed to migrate relation into modules_relations table)

=====> modules_relations (holds the relation between equipment and document; tried to migrate using second tMap, so I tried to map the flow from first tMap which also contains the equipment ID)

So basically, I first need to make an insert into the document table, and then its relation to equipment in the modules_relation. From what I've understood and seen, and what TRF has suggested, is that that there needs to be a subjob to do the insert into the modules_relation table, which looks like would be the link between the first and second tMaps, but then I lose the equipment ID value that I got from doing the lookup. How do I carry that value (equipment ID from lookup) *AS WELL* as get the value of *auto_increment* key (document ID) from the insert into documents, as I need both of these values in the modules_relation table?
Thanks again for any insight or guidance!