Unload from Redshift to S3 with multiple character delimiter

Question

Can I unload from Redshift to an S3 bucket with a multiple character delimiter? For example to use a double pipe delimiter as below? Because when doing so the file created in S3 has only the first | as a delimiter and disregards the second.

unload ('select * from venue')
to 's3://mybucket/unload/'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
CSV DELIMITER AS '||';

I tried also with other multiple characters such as '|~|' or '~~' but again it keeps only the first character.

Answer 1

You can only specify a single character as delimiter. See the UNLOAD documentation:

DELIMITER AS 'delimiter_character'

Specifies a single ASCII character that is used to separate fields in the output file, such as a pipe character ( | ), a comma ( , ), or a tab ( \t ). The default delimiter for text files is a pipe character. The default delimiter for CSV files is a comma character. The AS keyword is optional. You can't use DELIMITER with FIXEDWIDTH. If the data contains the delimiter character, you need to specify the ESCAPE option to escape the delimiter, or use ADDQUOTES to enclose the data in double quotation marks. Alternatively, specify a delimiter that isn't contained in the data.