topic Re: tMap - Insertion to mySQL based on composite key in Talend Studio

tMap - Insertion to mySQL based on composite key

sushantV — Wed, 15 Aug 2018 16:25:04 GMT

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.

Re: tMap - Insertion to mySQL based on composite key

ThWabi — Thu, 16 Aug 2018 10:16:47 GMT

Hi sushantV,

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

For example:

Best regards,

Thomas

Re: tMap - Insertion to mySQL based on composite key

sushantV — Thu, 16 Aug 2018 14:52:14 GMT

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)

Re: tMap - Insertion to mySQL based on composite key

ThWabi — Tue, 21 Aug 2018 09:36:04 GMT

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