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.
in tMap, on the output panel you can define multiple columns to be key columns.
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)
I think the Key columns are only used for SQL update or delete statements.
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.
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).
[...] 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.
The first 100 community members completing the Open Studio survey win a $10 gift voucher.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Watch the recorded webinar!
Pick up some tips and tricks with Context Variables
Learn how media organizations have achieved success with Data Integration
Accelerate your data lake projects with an agile approach