AWS Step function And Athena : can I configure query string from inputpath?-CodePudding

in my step function, I would like to execute an Athena query. I am able to define a step and execute a query successfully. However, I would like to pass some parameters as input and use them in the query string. For example.

Let's say, my query string is:

select * from <Data Source>.<database>.<tablename> where partition_0 = '2021';

I want to be able to pass the year as a input json to the step function, something like:

{
"YYYY": 2021
}

Is it possible to insert the input "YYYY" in the query string? If so, how?

Sample step function configuration:

{
  "Comment": "Start athena exececution",
  "StartAt": "athena",
  "States": {
    "athena": {
      "Type": "Task",
      "InputPath": "$",
      "Resource": "arn:aws:states:::athena:startQueryExecution.sync",
      "Parameters": {
        "QueryString": "select * from mycatalog.mydatabase.mytable where partition_0 = '2021'",
        "WorkGroup": "primary",
        "ResultConfiguration": {
          "OutputLocation": "s3://mys3bucket"
        }
      },
      "Next": "Pass"
    },
    "Pass": {
      "Comment": "A Pass state passes its input to its output, without performing work. Pass states are useful when constructing and debugging state machines.",
      "Type": "Pass",
      "End": true
    }
  }
}

CodePudding user response：

Use a Pass State to interpolate the input YYYY with the query string:

"QueryPassTask": {
  "Type": "Pass",
  "ResultPath": "$.athena",
  "Parameters": {
    "query.$": "States.Format('select * from mycatalog.mydatabase.mytable where partition_0 = \\'{}\\'', $.YYYY)"
  },

Pass task output is:

{
  "YYYY": 2021,
  "athena": {
    "query": "select * from mycatalog.mydatabase.mytable where partition_0 = '2021'"
  }
}

Next, provide the query string to the Athena task. Don't forget the '.$' suffix on the key. This tells Step Functions that the key's value contains a substitution.

 "Parameters": {
        "QueryString.$": "$.athena.query",