can anyone please explain the "key" column in a tMap schema?

[soowork]

Hi there, I am wondering if someone can explain the meaning, and best practice use of, the "Key" column indicator in a tMap?  The Talend doc I could find says The Key shows if the expression key data should be used to retrieve data through the Join link. If unchecked, the Join relation is disabled. (from https://help.talend.com/reader/JdTBzKszzXoWvjpEJD3EBA/_o6UbJAUslPX9TbYsnIzSA). But I don't really know what this means.

In the example pictured, many of the columns marked "Key" are columns that were set from other (lookup) tables in this job/subjob.  However, that is not always the case.

I would like to understand the value/purpose and know that I am using this correctly.  Thanks in advance.

Sue 

[manodwhb]

@soowork , when you doing update or insert /Insert or Update om action data in tDBoutput ,based on the key columns it will do update or insert.

Even when you are inserting data based on the key column if you created the table,if there is duplicate records based on Key column it will give constrain error.

[soowork]

hey Manohar - thanks for the response. 

That would make a great deal of sense to me if it was the key columns in the schema specified in the tDBOutput component.  But how do the key columns in that database output component schema interact with those of any upstream tMap components?  My question was about Key columns in the tMaps, specifically.  I will attach a picture of one of our jobs.

In our case, with a series of tMaps, it sounds like perhaps there should be no Key columns in the tMaps, and the schema of the final tDBOutput component should indicate the keys that should be used when inserting or updating into the database table - is that correct?

job_with_a_series_of_tmaps.png

[PhilHibbs]

It's the key that it is doing the lookup on.

So you've got data flowing through the links, like out10, out11, out12.  Each of those tMap components has a reference link, you're doing some kind of lookup, like the "createDtEST". Presumably that lookup is looking to see if something exists, or is looking to translate something. Well, that "something" is the key. It's what the looukp is looking up. Some of them look like a date or timestamp column, others are an order, member, or category.

[soowork]

Thanks Phil. 

So sounds like - in a tMap which is looking up a (replacement) value from a lookup table/source - that the columns in the join condition should be marked as "Key".  What about the database table key itself?

In the example I am attaching, I have marked the columns for my join condition as "Key", and that is it.  There is nothing marked as "Key" on the right hand side.  And I have not marked the actual (database key) in the lookup table as "Key" (DT_KEY) - is this correct?  

And then - from the earlier respond - it sounds like if I have a tMap that is leading to a database output component, I would mark (on the right hand side) the actual key values in the DB table that will be used for the upsert.

use_of_key_columns_for_tMap_for_lookup.png