Dynamic keys in Redshift Bulk Upsert
Hello,
We have a pipeline design(in concept) which will pick up files from source and upsert into the corresponding redshift table (currently scoping approximately 250 source files and target table...
Forum Discussion
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
Hi Nisar,
You can make the solution flexible, but you need to do little bit of some work before you design the pipeline and I do the same. I assume that whenever you work with any kind of source system you know the schema of all the tables/files, so what I mean to say if somehow you know the number of upsert_keys for each table may be by writing a small python script if the metadata is in files or if it is a RDMS you can easily get it from information_schema, you need to just see what is the max number of upsert keys you have for that source system and then in the mapper as I suggested in my initial response add those number of values.
I have attached sample pipeline just to show the logic. I have designed pipelines for different source systems in my project based on this logic and they are working fine. Our table count is minimum 300 for each source system.Dynamic_upsert_keys_parent_2018_02_02.slp (10.4 KB)
Dynamic_upsert_keys_child_2018_02_02.slp (11.6 KB)Thank you Aditya - will respond regardin your solution shortly - apologies for the delays in my response!
We have decided to take a different route right now. We’ve moved away from using the bulk upsert snap and instead place the new data in a staging area, delete the rows from the table which need to be updated and insert the new ones to replace them. The keys for the where condition are assigned using a script and this is used in the expression to build the sql statement to be executed.
Np Shasta. Let me know if you need more explanation or help regarding the solution I have provided to you.
Thanks
Aditya
