By using AWS re:Post, you agree to the Terms of Use

Unload is broken when a column contains certain characters

0

We are using the UNLOAD command to dump data to S3 in a TSV format and then read it using some other programs. Our UNLOAD command looks like the following:

UNLOAD ('SELECT column1, column2, column2 FROM some_table')
TO 's3://bucket/and/a/prefix/'
CREDENTIALS 'aws_access_key_id=<put_access_key_id_here>;aws_secret_access_key=<put_secret_access_key_here>'
ALLOWOVERWRITE
NULL AS '$$NULL$$'
ESCAPE
ADDQUOTES
DELIMITER '\t';

This works fine, but we noticed it breaks the output format when column1 contains the following string: "1% G".

Just to be clear:
column1 = "some text 1% General more text"
column2 = 10
column3 = "whatever"

We would expect to get a TSV with that looks like the following:
"some text 1% General more text"\t"10"\t"whatever"

Instead, we get:
"some text 1-NANeneral more text\t"10"\t"whatever"

Please note:

  1. The TSV we get is broken - the first column isn't enclosed in quotes.
  2. 1% G has been turned into 1-NAN.
asked 4 years ago39 views
1 Answer
0
Accepted Answer

Sorry you've experienced this problem. I think you are currently on 1.0.6145. We identified the issue and are releasing a new version 1.0.6230 that resolves it.

You can either upgrade in the console to 1.0.6230 or later or you can rollback to the previous version temporarily. https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-clusters.html#rs-mgmt-cluster-version

Please contact AWS Support if you need further assistance.

answered 4 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