How to execute AWS S3 to Redshift Copy command from SQL script?

Question

I am trying to copy some files from S3 to Redshift using copy command. I used following command through SQL workbench and it worked fine, it copied the data to Redshift Table.

copy <Redshift table name> 
from 's3://my-bucket/path/to/directory/part' 
iam_role 'arn:aws:iam::<IAM ROLE>' 
delimiter '|'  dateformat 'auto' IGNOREHEADER AS 1;

but when I copied the same command into .sql file and tried to execute this SQL file using AWS data pipeline, pipeline just fails without giving any explicit error.

Due to some issues with internally developed pipeline definition generation tool, I am not able to use CopyToRedshift type activity.

I would like to know how do I execute this copy command from an file?

Siva Vadde Siva Vadde · Accepted Answer · 2017-09-13T07:13:30

Try this out !!

COPY Table_Name

FROM S3File_PATH

credentials "*AWS Credentails"

ignoreheader as 1

ACCEPTINVCHARS

delimiter '|'

This copy command should work from a sql file .If not try to check for any errors in stl_load_error_details

How to execute AWS S3 to Redshift Copy command from SQL script?

1 Answers