RedShift Bulk upsert execute irrespective of data coming or not after router snap

Hi,

I’m not sure if you solved this yet but if you did please share your solution.

If you still have this issue, couldn’t you add a record count step and then filter snap for the routes? That way you only proceed to the BulkUpsert step when you have records in each route and avoid a failure. This should also skip your dynamic parameter error in your step.

Hi,

I was doing that only, counting the number of keys first and then based on the count I am routing my records. Unfortunately, it doesn’t matter if you have records proceed to bulkupsert snap or not it does a validation check to make sure the upsert keys mentioned in the snap actually exists or not and it happen before the pipeline actually execute. However, I fixed this problem with a workaround.

As I have mentioned in my initial post that I am using multiple bulkupsert snaps based on the number of upsert keys in my tables. For example my X table runs which has only 2 upsert keys which I am getting as comma separated so, bulkupsert snap which takes 2 parameters will validate fine but the other bulk upsert snaps which takes either 1 or 3 keys doesn’t validate and gives error lke $upsertKey31 is null etc. To overcome this problem what I did is, I checked how many bulk keys I have for that table for eg 2, I am passing these 2 keys to bulk upsert snap which takes 2 keys and for other I am passing one column by default which I know exist in that table or you can just pass one of the column key you are getting so that snap doesn’t fail during validation and it doesn’t matter you can pass same column name for all the upsert keys.

Here is how my mapper looks like where I check how many upsert keys I have and based on the keys I am setting values to my upsert keys variables:

Expression ---------------------------------------------------------------------------------------TargetPath

$upsert_key1.split(‘,’).length--------------------------------------------------------------------$upsertKeysCount
$upsert_key1.split(‘,’).length == 1 ? $upsert_key1.trim() : ‘event_id’------------------------$upsertKey
$upsert_key1.split(‘,’).length == 2 ? $upsert_key1.split(‘,’).sort()[0].trim() : ‘event_id’------$upsertKey21
$upsert_key1.split(‘,’).length == 2 ? $upsert_key1.split(‘,’).sort()[1].trim() : ‘event_id’-------$upsertKey22
$upsert_key1.split(‘,’).length == 3 ? $upsert_key1.split(‘,’).sort()[0].trim() : ‘event_id’-------$upsertKey31
$upsert_key1.split(‘,’).length == 3 ? $upsert_key1.split(‘,’).sort()[1].trim() : ‘event_id’-------$upsertKey32
$upsert_key1.split(‘,’).length == 3 ? $upsert_key1.split(‘,’).sort()[2].trim() : ‘event_id’-------$upsertKey33

Thanks
Aditya