Dynamic keys in Redshift Bulk Upsert

Hi,

You can do something like below to achieve your requirements.

In Redshift:

Create a control table which will take care of number of Snaplogic jobs going to run:

You can create schema of control table something like this:

Import_order: Start from 1 to n where n is the number of files for your source system
Source_system_name: XYZ
Source_table_name: In your case file name
target_schema_name: Redshift target schema name
target_table_name: Redshift table name where you need that file to be loaded.
Query: If the source is RDBMS then you can use this column to write the select query here.
Condition: Incremental condition for your next delta load.
Enable_Flag: Boolean, if you want to run the job for this table or not.
Status: Status of the job when it was ran last time.
upsert_key: comma separated values.

In Snaplogic:

Create 2 pipelines, one parent and another child.
1)In the parent pipeline use Redshift read snap to read the record from your control table like " select * from control table where source_system_name =‘files’ order by import_order.
2) Use mapper to read the upsert_key column values which is comma separated, split those values and create variable for each upsert key:
$upsert_key1.split(‘,’).length == 2 ? $upsert_key1.split(‘,’).sort()[0].trim() : ‘any_column_which_you_think_can be_available_in_every_table’ ---------- $upsertKey21 (you need to create like these variable equal to maximum upsert key you have. For example in 250 files if there is one table which has maximum upsert keys 5, you need to create these variable like this upsert51,upsert52,upsert53,upsert54,upsert55. For 2 upserkey: upsert21,upsert22

$upsert_key1.split(‘,’).length ------$upsertKeysCount

3. Send the output of above into pipeline execute snap, so if your control table has 250 entries for source_system files, you will 250 child pipeline running.

4. In the child pipeline check the upsertkey count and based on that route the flow to appropriate redshift bulk_upsert snap for eg: if table has 2 upsert keys, your redshift upsert will have upsert_key values like $upsertkey21 and $upsertkey22

Hope it will helps you.

Hi @aditya.sharma,

Many thanks for responding to my query in such detail.

I did see your post and solution on another thread and it is a decent work around, however we want to create something with more flexibility as there could be more than 2 upsert keys (or a fixed number).

Many thanks again