Transform 1 table into 3 tables using foreign keys and associative entity

Highlighted
Six Stars

Transform 1 table into 3 tables using foreign keys and associative entity

Hello,

 

I'm quite beginner with talend and i don't know how to load data from one table into three new tables (associative entity), see picture below.

 

Capture d’écran_2018-10-19_11-40-42.png

 

Both ID's from person and coordinate are generated automatically (auto_incremented ID's) and I need to get them in order to fulfill my last table person_coordinate. I have already find and use solution about one similar case when there is only one "parent". I had to disable "extend insert" then add a rule with the expression "LAST_INSERT_ID()" but that can't be applied in my current case because I need two ID's...

 

Thanks,

Guillaume


Accepted Solutions
Five Stars

Re: Transform 1 table into 3 tables using foreign keys and associative entity

You are asking tMap to do something both before (batch records for output) and after the database has done something (assign unique ids). That is probably not going to work.

 

You could follow this tMap (just the first two files, Person and Coordiates), with another tMap reading the same input and doing a lookup of Person and a lookup of Coordinates from the data, but you will probably have duplicate names and duplicate coordinates in your file over time.

 

Maybe a better solution would be to assign the IDs yourself by using a sequential number function. You would need to find the last id entered into the Person table and the last id entered into the Coordinates table. Then you could use a formula in the tMap output field "Numeric.sequence("s1",1,1)" where S1 is the sequence identifier and the first number parameter is the starting value (from your last table id plus 1) and the last number parameter is the increment value. You would need four sequence formulas:

For ID field in Person table: Numeric.sequence("person1", (lastPersonId+1),1)

For ID field in Coordinates table: Numeric.sequence("coordinate1",(lastCoordId+1),1)

For PeronID field in third table: Numeric.sequence("person2", (lastPersonId+1),1)

For CoordinateID field in third table: Numeric.sequence("coordinate2",(lastCoordId+1),1)

If your database will let you override the ID in an AutoID field, this would be the way to go, but you could only do this if no one else was updating the file at the same time.

 

Hope this gives you some ideas,

dg


All Replies
Five Stars

Re: Transform 1 table into 3 tables using foreign keys and associative entity

You are asking tMap to do something both before (batch records for output) and after the database has done something (assign unique ids). That is probably not going to work.

 

You could follow this tMap (just the first two files, Person and Coordiates), with another tMap reading the same input and doing a lookup of Person and a lookup of Coordinates from the data, but you will probably have duplicate names and duplicate coordinates in your file over time.

 

Maybe a better solution would be to assign the IDs yourself by using a sequential number function. You would need to find the last id entered into the Person table and the last id entered into the Coordinates table. Then you could use a formula in the tMap output field "Numeric.sequence("s1",1,1)" where S1 is the sequence identifier and the first number parameter is the starting value (from your last table id plus 1) and the last number parameter is the increment value. You would need four sequence formulas:

For ID field in Person table: Numeric.sequence("person1", (lastPersonId+1),1)

For ID field in Coordinates table: Numeric.sequence("coordinate1",(lastCoordId+1),1)

For PeronID field in third table: Numeric.sequence("person2", (lastPersonId+1),1)

For CoordinateID field in third table: Numeric.sequence("coordinate2",(lastCoordId+1),1)

If your database will let you override the ID in an AutoID field, this would be the way to go, but you could only do this if no one else was updating the file at the same time.

 

Hope this gives you some ideas,

dg

Six Stars

Re: Transform 1 table into 3 tables using foreign keys and associative entity

Thank you very much for all your clear explanations, that seems great ! Smiley Happy I may probably use the first solution with some modifications in order to avoid duplicate names and/or coordinates.

Five Stars

Re: Transform 1 table into 3 tables using foreign keys and associative entity

Sounds good. I figured you have more data that might make the records more specific. Good luck.


Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.