topic Re: [resolved] Fetch LastInsertID Amazon Aurora DB & Insert into another table in Talend Studio

[resolved] Fetch LastInsertID Amazon Aurora DB & Insert into another table

Anonymous — Tue, 14 Jun 2016 16:06:08 GMT

Hi All,
In my one Talend Job, I requires below flow:
1. tmap1 output few columns inserted in Amazon Aurora DB - Done
2. Fetch last inserted id for the same- Pending
3. Map last inserted id with rest of columns available in tmap1 component. This has to be done for each row inserted. - Pending
I am done with first step, but did not know how to proceed with second and third step. All work to be done on Amazon Aurora Db
Please help

Re: [resolved] Fetch LastInsertID Amazon Aurora DB & Insert into another table

Anonymous — Tue, 14 Jun 2016 16:48:33 GMT

Hello,
As there's not the Mysql connector to retrieve the last insert id, you can :
[list=*]

in the db, insert inserted' id by a trigger in a temp table if it exists in Amazon Aurora and then retrieve it in a job,

[list=*]

in Talend, make two workflows in your job. One to insert, second to catch all columns used in the insert to make a lookup in a second job into a csv file. Then in your second job just lookup with the table to retrieve the id inserted. Not beautiful development but it works for me in Postgresql.

HTH

Re: [resolved] Fetch LastInsertID Amazon Aurora DB & Insert into another table

Anonymous — Wed, 15 Jun 2016 06:01:58 GMT

Thanks Carlos for replying, but to be more informative here:
1. tmap1 component contains data for two tables ( Account & Portfolio).
2. tmap1 for Portfolio table insertion it contains Portfolio Name which will be inserted in Portfolio table and a new ID will be generated which is auto-incremented column
3. Fetch that portfolioID which is a auto-increment column
3. Then map portfolioID key with rest of columns (Account data) available in tmap1 component as foreign key column and then insert these columns with portfolioID key in Account table.
Hope this makes requirement more clear.

Re: [resolved] Fetch LastInsertID Amazon Aurora DB & Insert into another table

Anonymous — Wed, 15 Jun 2016 07:27:47 GMT

Hello,

That's what i said.
[list=*]

in your 1st job, do your insertions to Portfolio table and to a csv file.

in your 2nd job, make a lookup with a tMap to retrieve the Portfolio id's row inserted from 1st job with data from the csv. Then you can insert into the Account table.

HTH

Re: [resolved] Fetch LastInsertID Amazon Aurora DB & Insert into another table

Anonymous — Wed, 15 Jun 2016 07:43:38 GMT

Carlos, How we will get to know which portfolioID will be mapped with which Account Row data?

As for one portfolio there can be multiple Accounts. So we can't made lookup with Portfolio Name only.

Re: [resolved] Fetch LastInsertID Amazon Aurora DB & Insert into another table

Anonymous — Fri, 17 Jun 2016 09:09:34 GMT

One of my colleague was able to do this by following way:

1. Used tsequence generator after fetching Max Portfolio ID from table and generated all Portfolio IDs in Talend instead of in Database Table
2. Then Mapped those genrated id's with Account Table using tMap
3. Inserted whole data into db tables.

Thanks Carlos for your help.