Try to Update record, Insert if not found

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

desmond
Posts: 1
Joined: Thu Feb 03, 2011 8:05 pm

Try to Update record, Insert if not found

Postby desmond » Thu Feb 03, 2011 8:16 pm

Hi, I'm building a graph that reads from a remote database, does a simple transform, and loads the information into my local database. I need Clover to attempt an update of my existing data and, if no rows are found to update, insert a new row. I can currently update properly, but I don't know how to tell Clover to perform an insert if no rows are updated (the update operation "succeeds" even if no rows are updated, so I can't use error handling).

I couldn't find a cookbook recipe for this "upsert." Is there a way to try the update first and then do the insert?

thanks
desmond

avackova
Posts: 841
Joined: Fri Jul 20, 2007 9:28 am

Re: Try to Update record, Insert if not found

Postby avackova » Mon Feb 07, 2011 1:25 pm

Hello Desmond,
you can use dictionary for storing the information about number of processed records:
updateInsert.png
updateInsert.png (45.36 KiB) Viewed 8161 times
Agata Vackova
Javlin a.s.
agata.vackova@javlin.eu

julrych
Posts: 36
Joined: Mon Dec 13, 2010 6:52 pm

Re: Try to Update record, Insert if not found

Postby julrych » Thu Feb 17, 2011 6:27 am

Hi Desmond,

the idea behind upsert implementation split the data into two/three parts
* records you already have in local database -> update them in local database
* records you do not have in local database yet -> insert them into local database
* (possibly also records, which have been deleted from the remote database -> delete them from your local database).
The record identification is usually done by primary key.

Implementation can be done in several ways. Most convenient way is using DataIntersection. You can get similar results using any of Join operations (ExtHashJoin, MergeJoin or DbJoin) depending on the expected volume of data coming from remote database and local database.

Please find below two sample implementations - one using DataIntersection and one using ExtMergeJoin.
Attachments
Upsert.png
Sample upsert implementation in CloverETL
Upsert.png (118.81 KiB) Viewed 8127 times
Jan Ulrych
Javlin Inc.
jan.ulrych@javlininc.com

julrych
Posts: 36
Joined: Mon Dec 13, 2010 6:52 pm

Re: Try to Update record, Insert if not found

Postby julrych » Fri Dec 16, 2011 7:12 pm

Upsert implementation with DBJoin; offers great performance especially when input file size (in number of records) is fraction of the database table size (in number of records).
Attachments
Screenshot.png
Screenshot.png (42.19 KiB) Viewed 7591 times
Jan Ulrych
Javlin Inc.
jan.ulrych@javlininc.com

cassydeb
Posts: 7
Joined: Tue Feb 02, 2016 8:06 am

Re: Try to Update record, Insert if not found

Postby cassydeb » Tue Feb 02, 2016 8:11 am

hi,
I don't see join type = Inner join in DBJoin component, can you attach a .grf please?
Thanks for your help.

slechtaj
Posts: 192
Joined: Wed Aug 15, 2012 8:18 am

Re: Try to Update record, Insert if not found

Postby slechtaj » Wed Feb 03, 2016 8:10 pm

Hi,

Inner join is the default join type. So if Left outer join is false, which is its default value, the inner join type is automatically used.

Hope it helps.
Jan Slechta
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com

How to speed up communication with CloverCARE support

cassydeb
Posts: 7
Joined: Tue Feb 02, 2016 8:06 am

Re: Try to Update record, Insert if not found

Postby cassydeb » Mon Feb 15, 2016 11:01 pm

Thank you.

But I still have a problem with update.
When I debug the edge between DBJoin and DBOutputTable (update) and view data, it's the data of the table (Port 0 joined records)...So it try to update data with data already in table not the data of the .csv file!

It's maybe a problem with "Field Mapping" property of DBOutputTable that I set like documentation ($CloverField:=DBField)
$COUNTRY_CODE:="COUNTRY_CODE";$WORDING:="WORDING";...

How to set Field Mapping property to update table with the data of .csv file, not with the data already present in the destination table?

cassydeb
Posts: 7
Joined: Tue Feb 02, 2016 8:06 am

Re: Try to Update record, Insert if not found

Postby cassydeb » Wed Feb 17, 2016 12:51 am

I understand the problem...In these screenshots there is no information about transformation.

Thanks to this post:
viewtopic.php?f=4&t=6500&p=9954&hilit=upsert#p9954

I have to transform data in DBJoin to get ouput data as input data.

Code: Select all

$out.0.* = $in.0.*;
in transform function.