Amazon S3 connector and Redshift

I would love to have Amazon S3 connector that is capable of using Redshift COPY command and pull in data from my S3 bucket.

 

The basic idea is to use Redshift UNLOAD command on client (my) side and use COPY command on Domo side to load data into some dataset.

 

Redshift UNLOAD command: http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html

 

 

For example:

 

I would unload my data into my S3 bucket as follows:

unload ('select * from public.sales;')
to 's3://rs-unloads/sales_'
credentials 'aws_access_key_id=XXX;aws_secret_access_key=XXX'
allowoverwrite
manifest;

 

My S3 bucket:

sales_0000_part_00
sales_0001_part_00
sales_0002_part_00
sales_0003_part_00
sales_manifest

 

Then, within Domo, I would load data from above S3 bucket into 'sales' dataset:

copy sales from 's3://rs-unloads/sales_'
credentials 'aws_access_key_id=XXX;aws_secret_access_key=XXX'
manifest;

 

Note that UNLOAD command is splitting data into multiple files, therefore there is a manifest file. Also, by using Redshift UNLOAD command, we gain better performance while unloading large datasets, and again using COPY command is recommended way of loading data into Redshift by amazon. 

Tagged:
2
2 votes

· Last Updated

Comments

This discussion has been closed.