We have an SQL file that we would like to run on our Redshift cluster, we're already aware that this is possible via psql as described in this Stackoverflow answer and this Stackoverflow answer. However, we were wondering whether this was possible using the Redshift Data API?
We looked through the documentation but were unable to find anything apart from batch-execute-statement which takes a space delimited list of SQL statements. We're happy to resort to this but would prefer a method of running a file directly against the cluster.
Also, we'd like to parameterise the file as well, can this be done?
Our Current Attempt
This is what we've tried so far:
PARAMETERS="[\
{\"name\": \"param1\", \"value\": \"${PARAM1}\"}, \
{\"name\": \"param2\", \"value\": \"${PARAM2}\"}, \
{\"name\": \"param3\", \"value\": \"${PARAM3}\"}, \
{\"name\": \"param4\", \"value\": \"${PARAM4}\"}, \
{\"name\": \"param5\", \"value\": \"${PARAM5}\"}, \
{\"name\": \"param6\", \"value\": \"${PARAM6}\"}\
]"
SCRIPT_SQL=$(tr -d '\n' <./sql/script.sql)
AWS_RESPONSE=$(aws redshift-data execute-statement \
--region $AWS_REGION \
--cluster-identifier $CLUSTER_IDENTIFIER \
--sql "$SCRIPT_SQL" \
--parameters "$PARAMETERS" \
--database public \
--secret $CREDENTIALS_ARN)
Where all undeclared variables are variables set earlier in the script.