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.


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

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog