How do you UNLOAD data to S3 from Redshift in AWS Pipeline and include a date in the filename

Question

I have a database table on Redshift I want to UNLOAD to S3 each month [through AWS Pipeline]. I have this code that works, but ideally I'd like to add in the current month to the filename too

UNLOAD ('
select  *
from reportingsandbox.tmp_test


')
TO 's3://reporting-team-bucket/importfiles/test_123.csv' CREDENTIALS             'aws_access_key_id=123456678;aws_secret_access_key=abcdefg' 
ALLOWOVERWRITE
delimiter ',' 
PARALLEL OFF ;

I've tried this to add in the month, but it has not worked, do you know if it is possible?

Thanks

's3://reporting-team-bucket/importfiles/test_123{month(myDateTime)}.csv'

This is related to: https://stackoverflow.com/questions/36616796/amazon-redshift-unload-to-s3-dynamic-s3-file-name which suggests that it cannot be done inside Redshift, but instead you'll need for whatever system is kicking off the query to do the string interpolation itself. — Harlan, Sep 12 '17 at 20:50

score 10 · Accepted Answer · edited Mar 11 '22 at 22:25

10

I worked it out [in aws data pipeline]!

's3://reporting-team-bucket/importfiles/test_123-#{format(@scheduledStartTime,'YYYY-MM-dd-HH')}.csv'

Thanks

edited Mar 11 '22 at 22:25

WEBjuju

5,797
4
27
36

answered Jun 14 '16 at 14:24

ewan123uk

305
2
4
12

You should be able to accept your own anwer, that way anyone else will know that your question got resolved. – Dolfa Jun 14 '16 at 18:34
It is not working when I perform unload activity from SQLWorkbenchJ. What RS client you are using? – soup_boy Jul 27 '17 at 06:34
This has a syntax error, with single quotes inside single quotes. – Harlan Sep 12 '17 at 20:44
1

Soup_boy, this was from within AWS Pipeline, it runs as a scheduled job – ewan123uk Sep 14 '17 at 09:10
@ewan123uk would you be willing to share the datapipeline template? – John Sep 14 '17 at 15:51
@John I'm not sure how to export a template, but I use an SQLActivity. The resource is runs on is an ec2 instance, and uses a schedule which has a parameter Start Date Time. I'm pretty sure this parameter is the key, as this is what this picks up: _test_123-#{format(@scheduledStartTime,'YYYY-MM-dd-HH')}_ – ewan123uk Sep 19 '17 at 10:51
@john this is the unload statement `UNLOAD (' select * from reportinglongterm.test ') TO 's3://test/test-#{format(@scheduledStartTime,'YYYY-MM-dd-HH'‌)}.csv' CREDENTIALS 'aws_access_key_id=12345;aws_secret_access_key=abcde' ALLOWOVERWRITE delimiter ',' PARALLEL OFF gzip ;` – ewan123uk Sep 19 '17 at 12:13

How do you UNLOAD data to S3 from Redshift in AWS Pipeline and include a date in the filename

1 Answers1