By using AWS re:Post, you agree to the Terms of Use
/Step Functions with call to Redshift Data API:ExecuteStatement does not wait for Redshift Function/Stored Procedure completion./

Step Functions with call to Redshift Data API:ExecuteStatement does not wait for Redshift Function/Stored Procedure completion.

0

I am using Redshift Data API : Execute Statement (https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ExecuteStatement.html) within a step function. There are 2 Step Function states/tasks sequentially to call 2 individual Redshift functions - 1) to Load data in intermediate stage table and 2) to load data in final table from stage table with upsert (update/insert) logic.

Upon execution of step function, I observed that Step function simply fires ExecuteStatement but doesn't wait for confirmation for StoredProcedure / Function to get completed in Redshift and simply runs through remaining tasks with success result.

How do I enforce step to ensure Redshift execution of stored procedure/Function is completed? Currently there is no option in Step Function similar to Glue Task which has Option to Wait for Child step to complete.

1 Answers
0
Accepted Answer

The ExecuteStatement is an asynchronous API, that means that you get back a statement ID that you then need to call DescribeStatement to check the status of the statement. When it finishes you can continue.

In your state machine you will add the ExecuteStatement step -> Wait state for a few seconds -> DescribeExecution step -> Choice state to check if it completed, and based on the result, either go back to the Wait state or go to the next step.

For some of the optimized integrations we have the .sync integration type that does it for you. RedShift is not one of them so you will need to do it yourself.

EXPERT
answered 21 days ago
  • @Uri - just to clarify, are you recommending to use DescribeStatement or DescribeExecution? further do you have any reference blog / post to capture Id / Execution Arn and pass it to Describe state?

  • As far as I saw there is no DescribeExecution bur rather DescribeStatement, which gets the ID that was returned by ExecuteStatement.

    I could not find some reference architecture, but this blog shows a similar approach.

  • Thanks @Uri - I am trying with your approach to complete this PoC... further i am accepting your answer.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions