How to Unload To and Copy From Client S3 Buckets

See How to Connect to Alli Data's Redshift Cluster for info on establishing a connection to Alli Data’s redshift cluster.

There are several environment variables that should be used to COPY or UNLOAD:

ALLI_DATA_REDSHIFT_IAM_ROLE - Contains a value to pass to IAM_ROLE in the COPY or UNLOAD statement
ALLI_DATA_REDSHIFT_KMS_KEY_ID - Contains a value to pass to KMS_KEY_ID during UNLOAD
ALLI_S3_KEY_TEMP_PREFIX - Path to use when unloading files. This path contains some stuff that will mark the files as temporary and auto-delete them after 7 days.

Python

import os

client_slug = os.environ['ALLI_CLIENT_SLUG']
iam_role = os.environ['ALLI_DATA_REDSHIFT_IAM_ROLE']
kms_key_id = os.environ['ALLI_DATA_REDSHIFT_KMS_KEY_ID']
prefix = os.environ['ALLI_S3_KEY_TEMP_PREFIX']
bucklet = os.environ['ALLI_S3_BUCKET']

upload_sql = f"""
UNLOAD ('SELECT * FROM {client_slug}.thing')
TO 's3://{bucket}/{prefix}'
IAM_ROLE '{iam_role}' 
KMS_KEY_ID '{kms_key_id}' ENCRYPTED
MANIFEST VERSBOSE
"""

copy_sql = f"""
COPY {client_slug}.thing
FROM s3://{bucket}/{prefix}manifest
IAM_ROLE '{iam_role}'
MANIFEST
"""

AWS Documentation