Enhancement to Support Running SQL Scripts Pre and Post Load about aws-lambda-redshift-loader HOT 5 OPEN

Hi Francis - thanks for your note, and it was good to meet you at re:invent. For this case, I have a few questions: do you want the entire workflow to be transactional? If not, can you accomplish the 'post load script' by just using the successSNSTopicARN configuration item to create SNS notifications and process those with Lambda? The pre-script is another matter - I can see how this could be very valuable, and I think a pre/post script could be very interesting. I'd say the design criteria should definitely be that the scripts are in the configuration as S3 paths, and that the loader reads them in rather than having inline SQL.

The complexity with this will come from issues around transaction coordination. For instance, if the SQL script run in 'pre' has a commit in it, then of course you wont be able to rollback on load failure - leaving the database in an inconsistent state. If you didn't have commits, if you run the pre-script, the load completes, but then for some reason you can't download the post script, what should happen? Rollback? Additional options to configure this? Starts to get complicated. Not to mention issues around exceptions and termination of the lambda container during this process. Food for thought.

The only other point is that I'm shortly going to ship a 2.2.0 version which gives much better dynamic path matching, so you should probably build your code on that base rather than on the 2.1.0 base. Hopefully will ship sometime this week.

Ian

from aws-lambda-redshift-loader.

Thanks for the prompt response and thoughts. We will wait for your 2.2 release before proceeding.

from aws-lambda-redshift-loader.

Ian

I am thinking that we update index.js to pull the pre and post scripts from S3 and then prepend and append to the copyCommand and then execute it as your code does below:

client.query(copyCommand, function(err, result)

Will this approach work?

Additionally, could we wrap the entire copyCommand script with begin trans and commit trans commands?

Francis

from aws-lambda-redshift-loader.

2.2.0 is now out, so feel free to build against that version. I've thought it through, and I think this would be a good addition. Let's make the load of the objects from S3 very resistant to failures, and have think about what configuration options make sense regarding failure tolerance (if any...).

Thanks!

from aws-lambda-redshift-loader.

We are on hold for this ER at this time, we are sorting out our needs for this feature at this time. We will know more on timing and requirements next week.

from aws-lambda-redshift-loader.