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 toIAM_ROLE
in theCOPY
orUNLOAD
statementALLI_DATA_REDSHIFT_KMS_KEY_ID
- Contains a value to pass toKMS_KEY_ID
duringUNLOAD
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.
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
"""