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

[aditya_sharma]

Hi,

I have a parent child pipelines in which I am trying to use multiple Bulk Upsert snaps, as my target tables has different number of columns making unique/primary keys, some tables has only 1 column as PK some has 2 etc. To achieve this I am using a router to split the records based on the number of columns I have for that table which I have calculated before the Router Snap. When I am running the pipeline it fails for the BulkUpsert snap for which data is not coming. Also, I am using dynamic parameter within Bulk upsert snap in “keys column” and it is throwing error saying that value is not available, since that value will only come at run time based on the conditions.

[lzapart]

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.

[aditya_sharma]

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