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

Highlighted
One Star

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

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 Smiley Happy

 


Accepted Solutions
One Star

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

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. Smiley Happy

All Replies
One Star

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

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
    One Star

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

    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.
    One Star

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

    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
    One Star

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

    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.
    One Star

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

    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. Smiley Happy

    15TH OCTOBER, COUNTY HALL, LONDON

    Join us at the Community Lounge.

    Register Now

    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

    Definitive Guide to Data Quality

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

    Download