Introduction

One of the most efficient ways to load data from Amazon Redshift to s3 is using the UNLOAD command:  http://docs.aws.amazon.com/redshift/latest/dg/t_Unloading_tables.html

Use case(s)

• A financial customer would like to quickly unload financial reports into S3 that have been generated from processing that is happening in Redshift. The pipeline would have a Redshift to S3 action at the beginning, and then leverage the s3 source to read that data into a processing pipeline.

User Storie(s)

• As a user, i would like to unload data from redshift to s3 using the unload command.
• I would like to authenticate with IAM credentials as well as id and secret key pairs.
• I would like to use that s3 data as an input into a hydrator pipeline
• I would like the location of the data to be passed via workflow token so that they next plugin can use it in a macro

Plugin Type

•  Action

Configurables

This section defines properties that are configurable for this plugin. 

Design / Implementation Tips

• Follow the tutorial here to get a better understanding of what might be http://docs.aws.amazon.com/redshift/latest/dg/t_Unloading_tables.html

Design

1. User can connect to the S3 buckets using Either access and secret access key or using IAM role.
2. By default unload command assumes that Redshift and s3 are in the same region, If S3 bucket is not in same region as of the Redshift cluster, then user can provide the region using 'Region for S3'.

1. For using the IAM role to unload the data, the IAM role should have GET, LIST and PUT access on the bucket.
2. UNLOAD automatically creates files using Amazon S3 server-side encryption with AWS-managed encryption keys (SSE-S3). UNLOAD does not support Amazon S3 server-side encryption with encryption keys from SSE-KMS or a customer-supplied key (SSE-C).
3. Plugin would always unload data with Amazon S3 server-side encryption.

Conditions

1. The Amazon S3 bucket where Amazon Redshift will write the output files must reside in the same region as your cluster.(http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html)

Approach(s)

Properties

{
  "name": "RedshiftToS3Action",
  "type": "action",
  "properties": {
    "accessKey": "accessKey",
    "secretAccessKey": "secretAccessKey",
    "iamRole": "arn:aws:iam::0123456789012:role/MyRedshiftRole",
    "query": "select * from venue",
    "s3Bucket": "s3://mybucket/test/",
    "manifest": "false",
    "delimiter": ",",
    "fixedWidth": "",
    "parallel": "off",
    "compression" : "none",
    "allowOverwrite" : "true",
    "addQuotes": "true",
    "redshiftClusterAddress" : "jdbc:redshift://test.cntuu3e3qg5h.us-west-1.redshift.amazonaws.com:5439/redshiftdb",
    "redshiftMasterUser" : "admin",
    "redshiftMasterPassword": "admin",
    "redshiftTableName": "testTable"
  }
}

Security

Limitation(s)

Future Work

• Some future work – HYDRATOR-99999
• Another future work – HYDRATOR-99999

Test Case(s)

• Test case #1
• Test case #2

Sample Pipeline

Please attach one or more sample pipeline(s) and associated data. 

Pipeline #1

Pipeline #2