Incorrect output when exporting AWS Redshift data to S3 using UNLOAD command

Question

I'm not a database guy and i have just started exploring Redshift. I created a cluster and used their sample scripts to populate tables. I want to copy data from Redshift to Postgres. I first wanted to export the data to S3 and then import into Postgres. After i ran the following command, a file got generated in my S3 bucket:

UNLOAD ('select * from date') TO 's3://sample-dwh-data/date_' credentials 'aws_access_key_id=******;aws_secret_access_key=*************' PARALLEL OFF;

This is the script i used to create date table in Redshift:

create table date( dateid smallint not null distkey sortkey, caldate date not null, day character(3) not null, week smallint not null, month character(5) not null, qtr character(5) not null, year smallint not null, holiday boolean default('N'));

Problem is that when i open the exported file present in S3, i see the last column for each row has values either set as f or t. Sample output from exported file:

2070|2008-09-01|MO|36|SEP|3|2008|t 2071|2008-09-02|TU|36|SEP|3|2008|f

However, if i query the Redshift table, last column value is either true or false. So, why is the UNLOAD command trimming the last column's value?

UPDATE: @Yankee: When i ran the following command: 'SELECT dateid,caldate,day,week,month,qtr,year,CASE WHEN holiday = true THEN 'TRUE' ELSE 'FALSE' END AS holiday from date', i got this error:

An error occurred when executing the SQL command: UNLOAD ('SELECT dateid,caldate,day,week,month,qtr,year,CASE WHEN holiday = true THEN 'TRUE' ELSE 'FALSE' END AS holiday from date') TO 's3://sample-dwh-data/date_... [Amazon](500310) Invalid operation: syntax error at or near "TRUE" Position: 87; UNLOAD ('SELECT dateid,caldate,day,week,month,qtr,year,CASE WHEN holiday = true THEN 'TRUE' ELSE 'FALSE' END AS holiday from date') ^ 1 statement failed.

why do you think this is a problem? the unload command uses t and f to represent boolean. the copy command would accept these as valid values. — Jon Scott
– Jon Scott, Commented Apr 22, 2018 at 8:46
The problem is that i want to load the exported data to a Postgres instance running on Heroku. I'm not sure whether this will change f to false or t to true while importing it to another database (in my case, Heroku Postgres). Also, is this common for UNLOAD operation to trim the output in case of boolean values? — Technext
– Technext, Commented Apr 22, 2018 at 9:59

Shailesh · Accepted Answer · 2018-04-25 07:43:42Z

2

Instead of doing a SELECT * FROM DATE, you can specify the column names and there you can customize how you want the data to be exported, by using CASE IF ELSE conditions.

SELECT dateid,caldate,day,week,CASE WHEN holiday = true THEN \'TRUE\' ELSE \'FALSE\' END AS holiday from date;

edited Apr 25, 2018 at 7:43

answered Apr 23, 2018 at 9:36

Shailesh

2,2964 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Technext Over a year ago

Sorry for the delay in reply. Please check the UPDATE section above.

Shailesh Over a year ago

@Technext You can try these changes: 1. Remove the single quotes from the 'TRUE' and 'FALSE' , ie WHEN holiday = true THEN TRUE ELSE FALSE or 2. From this, you should escape the single quotes with backslashes, so that'll get you: WHEN holiday = true THEN \'TRUE\' ELSE \'FALSE\' END . Both of these should work.

Technext Over a year ago

Thanks for the correction in syntax. The command did execute but result was still the same when i used your first suggestion. However, trying your second suggestion did the trick. :) Thanks. I am still interested in knowing why UNLOAD doesn't honor the actual value and instead trims it.

Shailesh Over a year ago

@Technext Great, glad it helps. I'll edit my answer, please mark it as accepted as it solves your problem. Here, you can see that there's no documentation as to why Redshift does that.

Technext Over a year ago

Thanks for the link @Yankee! :)

Collectives™ on Stack Overflow

Incorrect output when exporting AWS Redshift data to S3 using UNLOAD command

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related