cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

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

aditya_sharma
New Contributor III

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.

2 REPLIES 2

lzapart
New Contributor III

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
New Contributor III

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