How to concatenate character strings in step function for AWS Glue parameters?

Question

HI All,

I have a step function to call an AWS Glue job with below step definition:

```json
    "glue-redshift-call": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startJobRun.sync",
      "Parameters": {
        "JobName": "GlueJobRedshiftStoredProcCa-meJ8P3QMSC5p",
        "Arguments": {
          "--SQLStatement": "call dw_merge_vendor('TNG', 'ASENTINEL')"
        }
      },
      "Next": "post-processing"
    },
```
As shown above, SQLStatement parameter is hardcoded as `call dw_merge_vendor('TNG', 'ASENTINEL')` in this. But I need to make it dynamic, parameter based. As mentioned in [Step Function new features Blogpost](https://aws.amazon.com/blogs/aws/aws-step-functions-adds-updates-to-choice-state-global-access-to-context-object-dynamic-timeouts-result-selection-and-intrinsic-functions-to-amazon-states-languages/), I tried below syntax to make it dynamic, but it's not working. Would you be able to suggest the correct syntax if I am doing something wrong here?

```json
"Arguments": {
  "--SQLStatement": "States.Format('call dw_merge_vendor('{}', '{}'),  ', $.param1, $.param2)"
}
```

Accepted Answer

There are two issues with what you did. The first is that when you reference other variables or intrinsic functions, you need to append .$ to the key. The second is that when you need to include ' in the string, you need to escape them using \\. It should look like this:

```
"Arguments": {
"--SQLStatement.$": "States.Format('call dw_merge_vendor({}, {}),', $.param1, $.param2)"
}
 ```

If you need the params enclosed in ', you should use the following:
```
"Arguments": {
"--SQLStatement.$": "States.Format('call dw_merge_vendor(\'{}\', \'{}\'),', $.param1, $.param2)"
}
```

How to concatenate character strings in step function for AWS Glue parameters?

Relevant questions

Step Function Retry Metrics

Trigger Step Function with API Gateway and use Fargate within Step Function?

Glue Throttling Exception when starting > 15 Glue jobs in Parallel via Step Function

AWS Glue retry a job after an execution error

How is the function create_partition_index called in Glue Jobs?

How to concatenate character strings in step function for AWS Glue parameters?

Step function state to execute a Glue job seems to be stalling

Sophisticated Triggering of Glue Jobs

Step Function action GetJob (AWS Glue) does not return CodeGenConfigurationNodes despite the documentation saying it should

Step Function to Send Email on Error/Success