UPSERT with variable column list for INSERT and UPDATE

[krajew4]

Hi

I have a case where I load data from source system (SQL Server, 3rd party) into Snowflake (my environment) using tDBOutput component with output action of UPSERT.

In the Snowflake I have additional 2 columns in destination table: LoadDate and ChangeDate.

For now I set value for both of these columns to the same date value but I would like to change it so when MERGE UPDATE occurs only ChangeDate is affected. When MERGE INSERT occurs the behaviour will be so that LoadDate and ChangeDate is the same.

Is it possible to achieve that in Talend?

[gjeremy1617088143]

Hi @Rafał Krajewski​ , maybe the component tSQLTemplateMerge can do what you want, you can choose wich action you do on update and insert,

if you want to use it you have surely to fill a stagging table on snowflake before and after use the merge with this table on source and the table you want on target.

Send me love and kudos

[krajew4]

@guenneguez jeremy​ 

Thanks for the answer I didn't know about tSQLTemplateMerge component and it looked promising however: I think I need to put a name for the source table into Source table name on tSQLTemplateMerge component. I don't know the name since staging name is controlled by Snowflake. Technically it is possible to find the name using SHOW STAGES but in Talend I cannot do this because it would break the flow of data from the source:

Source data (tDBInput) -> ... -> Destination table (tDBOutput - Snowflake)

Instead of tDBOutput I could use tSQLTemplateMerge if I could find stage name in the middle between source and destination components.

[gjeremy1617088143]

If you have a stagging table your tDboutput will be for this one with a truncate option;

then you can use the tSQLTemplateMerge to merge your data between the stagging table and target table in snowflake

[krajew4]

@guenneguez jeremy​ 

I understand what you mean and I think the idea is correct but ~:> in my case I am doing an iteration on top level, each iteration migrates one table. I can't set properties for UPDATE and INSERT and MERGE ON on tSQLTemplateMerge before I run the job - I only know which columns I want to migrate once the job is running.

In the meantime I found SQL Templates in Talend Studio and analyzed Merge Update, Merge Insert and Snowflake Merge templates. From what I understood given how tSQLTemplateMerge component it needs to have properties set before you run the job.

I am using now tDBOutput for Snowflake and I am providing the schema in form of dynamic variable prepared in Java code one step before. The merge generated but tDBOutput (table action set to UPSERT) should be generated by that Snowflake Merge template as I understood. This template iterates on:

__UPDATE_MAPPING_SRC_COLUMN__

__INSERT_MAPPING_TRG_COLUMN__

to prepare list of columns for UPDATE MATCHED and INSERT NOT MATCHED unfortunately there is no way to set the name of the template for tDBOutput for snowflake and no way to change the internals of this component so I am stuck with black box behaviour I cannot modify.