For a simple task in Redshift, such as refreshing a materialized view, you can use a scheduled query; but sometimes you really want a proper scripting language, rather than SQL.
You can use a docker image as a lambda now, but I still find uploading a zip easier. And while it’s possible to set up the db creds as env vars, it’s better to use temp creds:
import boto3
import psycopg2
def handler(event, context):
client = boto3.client('redshift')
cluster_credentials = client.get_cluster_credentials(
DbUser='user',
DbName='db',
ClusterIdentifier='cluster',
)
conn = psycopg2.connect(
host="foo.bar.region.redshift.amazonaws.com",
port="5439",
dbname="db",
user=cluster_credentials["DbUser"],
password=cluster_credentials["DbPassword"],
)
with conn.cursor() as cursor:
...
Once you have the bundle ready:
pip install -r requirements.txt -t ./package
cd package && zip -r ../foo.zip . && cd ..
zip -g foo.zip app.py
You need a trust policy, to allow lambda to assume the role:
{
"Version": "2012-10-17",
"Statement": [
{
"Action": "sts:AssumeRole",
"Principal": {
"Service": "lambda.amazonaws.com"
},
"Effect": "Allow",
"Sid": ""
}
]
}
And a policy for the redshift creds:
{
"Version": "2012-10-17",
"Statement": [{
"Sid": "GetClusterCredsStatement",
"Effect": "Allow",
"Action": [
"redshift:GetClusterCredentials"
],
"Resource": [
"arn:aws:redshift:region:account:dbuser:cluster/db",
"arn:aws:redshift:region:account:dbname:cluster/db"
]
}]
}
In order to create an IAM role:
docker run --rm -it -v ~/.aws:/root/.aws -v $PWD:/data -w /data -e AWS_PROFILE amazon/aws-cli iam create-role --role-name role --assume-role-policy-document file://trust-policy.json
docker run --rm -it -v ~/.aws:/root/.aws -v $PWD:/data -w /data -e AWS_PROFILE amazon/aws-cli iam attach-role-policy --role-name role --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole
docker run --rm -it -v ~/.aws:/root/.aws -v $PWD:/data -w /data -e AWS_PROFILE amazon/aws-cli iam attach-role-policy --role-name remove-duplicates --policy-arn arn:aws:iam::aws:policy/service-role/AWSXRayDaemonWriteAccess
docker run --rm -it -v ~/.aws:/root/.aws -v $PWD:/data -w /data -e AWS_PROFILE amazon/aws-cli iam put-role-policy --role-name role --policy-name GetClusterCredentials --policy-document file://get-cluster-credentials.json
And, finally, the lambda itself:
docker run --rm -it -v ~/.aws:/root/.aws -v $PWD:/data -w /data -e AWS_PROFILE amazon/aws-cli lambda create-function --function-name foo --runtime python3.7 --zip-file fileb://foo.zip --handler app.handler --role arn:aws:iam::account:role/role --timeout 900
If you need to update the code, after:
docker run --rm -it -v ~/.aws:/root/.aws -v $PWD:/data -w /data -e AWS_PROFILE amazon/aws-cli lambda update-function-code --function-name foo --zip-file fileb://foo.zip
You can test the lambda in the console. Next time, we’ll look at how to trigger it, using EventBridge.
Look on my works, ye Mighty, and despair! 