Unload with timestamp filter

0

I was attempting to unload all data prior to 2017 from a database and hit what seems to be a bug...

my query was:

unload ('select * from traffic where timestamp < '2017-01-01' order by timestamp asc')
to 's3://xxx/export/unload/2019-01-22-3/'
iam_role 'arn:aws:iam::xxx:role/xxx'
delimiter as ','
addquotes
escape
manifest;

what I ended up with was only data prior to 2015, which I assume is a result of the date being evaluated as subtraction and looking at the query in the inspector:

unload ('select * from traffic where timestamp < 2017-01-01') to 's3://xxx/export/unload/2019-01-22/' iam_role '' delimiter as ',' addquotes escape allowoverwrite manifest

asked 5 years ago753 views
3 Answers
0
Accepted Answer

It's likely that the backslash escape is being stripped by something that handles the query text before the query gets to Redshift.

Try doubling up the quotes instead as that also works.

unload ('select * from traffic where timestamp < '''2017-01-01'' order by timestamp asc')…
answered 5 years ago
0

which I assume is a result of the date being evaluated as subtraction

(lols!! I shouldn't laugh, but that is funny =-)

Maybe try using DATE_CMP() and seeing if the literal in the function is interprented correctly?

I would try it myself but I do not have access to a cluster.

Toebs2
answered 5 years ago
0

One general approach to avoid bugs or limitations in the select clause of the unload statement is to define a view where the view definition is what you want to unload, then refer to the view in the unload statement, e.g.:

create view #vname as
select * from traffic where timestamp < 2017-01-01 order by timestamp asc
;

unload (select * from #vname) to ..
;

Edited by: karbjonn on Jan 29, 2019 12:38 AM
Removed the quotes around the date criteria, and the unload select clause, as on posting the original message AWS gets confused and do not display the statements correctly. Of course, in real life, quotes must be added.

answered 5 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions