Step Functions with call to Redshift Data API:ExecuteStatement does not wait for Redshift Function/Stored Procedure completion.
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.
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.
@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.
Setting up refresh schedules for Amazon Redshift materialized viewAccepted Answerasked 2 years ago
Call a stored procedure in Redshift from GlueAccepted Answerasked 3 years ago
How is data returned from Spectrum to Redshift cluster?Accepted Answerasked 2 years ago
Redshift Metric No Data Available.asked 2 months ago
Redshift Data API not recognized by Boto3Accepted Answerasked 2 years ago
Connecting AppSync with Redshift via Data APIasked a year ago
Call a redshift stored procedure with dynamic parameter from boto3 lambdaAccepted Answerasked 3 months ago
Step Functions with call to Redshift Data API:ExecuteStatement does not wait for Redshift Function/Stored Procedure completion.Accepted Answerasked 21 days ago
Using redshift-data boto3 to make cross account redshift callsasked 5 months ago
Redshift Data Sharing - Play and PauseAccepted AnswerMODERATORasked 2 years ago