tMap - Insertion to mySQL based on composite key

Seven Stars

tMap - Insertion to mySQL based on composite key

I need to read data from a raw file and insert into a MySQL table. However, my target table is unique only at combination of two fields. I do not have the option to create a composite key in the target table. 

 

Is there a way I can do the update or insert, based on this key combination? Essentially I am trying to understand how the multilevel key mapping happens in the tMap component.

Seven Stars

Re: tMap - Insertion to mySQL based on composite key

Hi sushantV,

 

in tMap, on the output panel you can define multiple columns to be key columns.

For example: 

 

tmap_output_composite_key.jpg

 

 

Best regards,

 

Thomas

Seven Stars

Re: tMap - Insertion to mySQL based on composite key

I thought so however I am still confused about the interpretation. If I provide two key columns in the tMap (on both sides), would Talend create a temp composite key and process the records based on combination of both the keys? 

 

Further extending my question, what happens if I select two key columns on one side and just one key column on another. How would the system interpret?

 

My task is either insert or update, based on the pre-existence of the match key (which is a combination of two columns)

Seven Stars

Re: tMap - Insertion to mySQL based on composite key

Hi sushantV,

 

I think the Key columns are only used for SQL update or delete statements.

 

  • For an insert, Talend generates an SQL statement like 
    insert into mytable (customer_name, country, value) values ('Smith', 'Germany', 123);
    If customer_name and country are defined as the primary keys in the database table, the database will make sure that the combination of 'Smith' and 'Germany' is unique.
  • For an update, Talend generates an SQL statement like
    update mytable set value = 123 where customer_name = 'Smith' and country = 'Germany'
    The Key columns are used in the where clause (to uniquely determine which row should be updated).

 

@sushantV wrote:

 

[...] what happens if I select two key columns on one side and just one key column on another. 


It depends on how you (want to) connect the input side to the output side. You can calculate one unique key for the output panel from the two keys on your input panel.

 

Best regards,

 

Thomas

 

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

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

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch