Is it possible to pass SqlParameters to Redshift Data API ExecuteStatement from Step Functions?

0

I have a Step Function that uses the ExecuteStatement integration from the Redshift Data API. I want to execute a SQL statement that includes named parameters, which I want to use JSONPath to pass in to the API.

According to the API reference, the Parameters should look like this:

{
   "Parameters": [ 
      { 
         "name": "string",
         "value": "string"
      }
   ]
}

However, when I try to pass parameters in this way, the Step Function refuses to start the task:

..."Parameters":[{"name":"foo","value":"bar"},{"name":"baz","value":"bat"}] ...could not be used to start the Task: [The field "name" is not supported by Step Functions]

Is there any way to pass parameters in without using the field "name"?

David
gefragt vor einem Jahr1090 Aufrufe
2 Antworten
0

Hello,

In this context, please note that, you can control the SQL text submitted to the database engine by calling the Data API operation using parameters for parts of the SQL statement.

[+] - https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html#data-api-calling-considerations-parameters

Therefore, it looks like an issue from the Step functions.

However, please note that, if you are using Redshift Serverless the above method is not compatible with it and there is an existing Feature Request currently in place regarding this issue.

Since features are required to be tested and pre-configured as per the existing ecosystem because of which I unfortunately would not be able to provide an ETA/timeline for the same.

That being said, to further look into this issue more closely, if you would like to do a resource based troubleshooting, please raise a support case with AWS for further information. If a support case has already been created please be assured that we will get back to you and assist you in the best way possible.

Thankyou!

AWS
beantwortet vor einem Jahr
-1

According to Using the Amazon Redshift Data API it should work - you are right. However, maybe it is not the Redshift API problem but rather Step functions(error is coming from it). Did you try passing parameters as a list(no named parameters) to confirm if this way it will work?

like this:

{
   "Parameters":[
      "string1",
      "string2"
   ]
}

and if SQL statement that looks like this you can use JSONPath to map each string to the appropriate parameter in your SQL statement.:

SELECT * FROM YOUR_TABLE WHERE column1 = :param1 AND column2 = :param2

You can use JSONPath to map the first parameter to ":param1" and the second parameter to ":param2". Here's an example of what the JSON might look like:

{
   "Parameters":[
      "foo",
      "bar"
   ],
   "SqlStatement":"SELECT * FROM YOUR_TABLE WHERE column1 = :$.Parameters[0] AND column2 = :$.Parameters[1]"
}

Let me know if this something that will work for you.

profile pictureAWS
Niko
beantwortet vor einem Jahr
  • I'm pretty skeptical of this answer. Firstly, in order to do substitution into a string in Step Functions, you would need to name the SqlStatement "SqlStatement.$". Next, when you DO use JSONPath, it doesn't just substitute in to a larger string; it expects a JMESPath expression. You would need to pass "States.Format('select * from ...', $.Parameters[0], ...)". Finally, the entire point of getting parameters sent to the Redshift Data API is so that the SQL engine interprets the parameters itself and handles the potential quoting issues. Maybe give it a shot in the Step Functions console and test your theory?

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen