How to use "redshift-data" API with AWS CLI

In this post i will list the steps to use the "redshift-data" thru AWS CLI.

The Redshift Data API enables you to communicate from the outside of the cluster and execute statements or get results from it.

There are at least 2 popular ways to use it:

• BOTO3 Library BOT3 Documentation

• AWS CLI CLI Documentation (In this post we will use this method)

First of all you need to create a Redshift Cluster. I Highly recommend to create with default configuration, using the minimal instance types and nodes, if you want to test the "redshift-data" api.

AWS Recommendation is to create within the Cluster the user "redshift_data_api_user" because by default there is an AWS Manage Policy ("AmazonRedshiftDataFullAccess") with the necessary grants to connect from the outside and gives the grants to that user by default. If you want to grant the access to another user, you can get the entire policy document and replace the default user to preffer user.

DEFAULT

{
"Sid": "GetCredentialsForAPIUser",
"Effect": "Allow",
"Action": "redshift:GetClusterCredentials",
"Resource": [
"arn:aws:redshift:*:*:dbname:*/*",
"arn:aws:redshift:*:*:dbuser:*/redshift_data_api_user"
]
}

IF YOU WANT TO CHANGE DEFAULT USER

{
"Sid": "GetCredentialsForAPIUser",
"Effect": "Allow",
"Action": "redshift:GetClusterCredentials",
"Resource": [
"arn:aws:redshift:*:*:dbname:*/*",
"arn:aws:redshift:*:*:dbuser:*/<CUSTOM_USER/>"
]
}

Once you decide either to use default user or custom user, you need to make sure that your user/role has the policy attached. As you can see in the image below i am using the default Manage policy to my IAM User:

Note: It also has the policy to change the password & to access to the Redshift console for query the data. No other IAM permission are added to my example user.

After adding the permission to the user, it is needed to perform the following steps in order to enable the redshift-data CLI:

• Create the user within the cluster:

create user redshift_data_api_user password 'Password1234';

• Grant permission for USAGE and CREATE over an example SCHEMA:

GRANT USAGE on SCHEMA example to redshift_data_api_user
GRANT CREATE on SCHEMA example to redshift_data_api_user

• Finally we can use the "redshift-data" thru CLI to execute an statement. In this example i will create a sample table within the schema "example":

aws redshift-data execute-statement \
--region AWS-REGION \
--db-user redshift_data_api_user \
--cluster-identifier CLUSTER-ID \
--database DATABASE \
--sql "create table example.customer(name
varchar(10), surname varchar(10))"

Note: Please note that the region / cluster-identifier and database are values that you need to place based in your cluster creation. It is important to keep the "db-user" param with the "redshift_data_api_user" since that is the one created above and the user that we can getcredentials (policy).

Hope this example is helpful for you!.

Cheers,
Cristian.