Updating a column or deleting rows of a table in SQL transform (saving to the source table)

Kaushik

Hello Everyone,

I have just started working in Domo for couple of weeks and currently working on the ETL transformation.

 

My requirement is to update or delete a column of a table using SQL transfrom and save the result to the same table. I have tried this using as individual transforms and in a Stored procedure, in vain. Help me in this. 

 

Analysis:

Output is mandatory for SQL transform in DOMO and when i assigh the same name of the source to output, a new target table is created with the same name (while i want to impact the source table)

 

Note: I am surprised that DOMO created a duplicate table with same name as it can cause some conflits/misunderstanding later. I am curious to know its usecase, as I think creating another object with same name is not desirable.

 

 

Thank you for your time. 

Regards,

Kaushik 

MrMiyagi

Thanks for sending that over. 

 

I see what you are trying to do now. What you are doing is correct (Update and Delete). The issue you are running into is that the output data set is not the same as the input data set, even if you name the output data set the same as the input data set (behind the scenes they are different tables).

 

This is intentional on Domo's part for a few reasons:

1) We want to preserver and protect the source data sets

2) If you had the same output data set as the input data set then it would create a recursive data flow that would never stop running. This is because when the input data set is updated it kicks of the data flow.

 

Could you help me understand the use case better? If I know what you are trying to accomplish perhaps we can recommend another approach.

 

[Deleted User]

Kaushik,

 

Do you have any screen shots you can upload as an image in the post or as an attachment so we get a clearer understanding of what you're trying to accomplish?

 

Thanks!

Dani

Kaushik

I have attached the screenshot of what i am trying to do.

 

I am trying to update one row for a student_id and delete one row for a student id in the source table kvk_temo_studnet_target. 

After i run that process, I get 2 datasets with the name kvk_temo_studnet_target while I would like to have one kvk_temo_studnet_target with the update and delete reflected. 

 

Note: I updated your previous post as solution by mistake

MrMiyagi

HI Kaushik,

 

Could you also open each of the transforms and attached a screen shot of the code you are using? It will help me to understand what is happening and provide some guidance.

 

Thank You!

Kaushik

Hello,

 

Apologies, I have included the screenshots of individual transforms as requested. 

As they were simple queries i did not post them earlier, but i see your point. 

 

To reiterate, I want to update/delete the source table within the same SQL dataflow task (if there is a way). 

 

Please let me know if you need anything. Thank you.

MrMiyagi

Thanks for sending that over. 

 

I see what you are trying to do now. What you are doing is correct (Update and Delete). The issue you are running into is that the output data set is not the same as the input data set, even if you name the output data set the same as the input data set (behind the scenes they are different tables).

 

This is intentional on Domo's part for a few reasons:

1) We want to preserver and protect the source data sets

2) If you had the same output data set as the input data set then it would create a recursive data flow that would never stop running. This is because when the input data set is updated it kicks of the data flow.

 

Could you help me understand the use case better? If I know what you are trying to accomplish perhaps we can recommend another approach.

 

Kaushik

Thank you for the response. It helped.

My scenario was to have a history of data with a week of delta data appened (after removing the overlap in history data). I was able to acheive this with recursive data flow. 

 

However, delete and update of a table in the dataflow was something I was not able to crack, and hence the question. Thanks for the help! 

RGranada

Hi,

I think that you are trying to accomplish an upsert. Update historical data with Delta data and add new rows if they exist, assuming that the historical table and delta table schemas are identical. This will give you a new output dataset containing the historical data upserted with the delta data.

You cas use the following 2 transforms in a RedShift dataflow:

Transform 1:

DELETE FROM Historical_Data
USING Delta_Data WHERE Historical_Datal.id = Delta_Data.Id

*Uncheck "Generate Output Table"

Transform 2:

insert into Historical_Data
select * from Delta_Data

*Uncheck "Generate Output Table"

Output Dataset :

*Dataset Name : UpSerted_Data

Select * Historical_Data

There are some changes that can be made to accommodate various sceneries.

 

Hope this helps.