CODEWITHSUNDEEP
amazon-web-servicesescapingamazon-redshift
Sachiko
Sachiko

Reputation: 924

Redshift : How to keep \ as a part of string at Copy Command?

We'd like to import \ as part of data into Redshift table (Since it's same with our currency mark, Japanese yen).

As you know, Redshift handles \ as default escape, so, usually we replace \ to \\ and we can import \ as part of string into the tables.

ex. \365 --> \\365

But this time, we need to import very huge file, over 25GB and found out it consumes huge memory to replace \ to \\ as usual.

So I'm wondering we can use ESCAPE option in COPY command, for example, ESCAPE '^', to use other word as escape, instead of default escape , but it doesn't work so far.

Is there any better idea, kindly let me know. Thank you.

--

Let me add the actual command and error message. Maybe something wrong in the combination of the copy options.

-- Command (Executed from Workbench/J 

copy $[VarODS_DB]."$[VarODS_Table]" 
from $[VarS3FileName]
$[VarIAM]

--REMOVEQUOTES
--IGNOREHEADER 2
--CSV
GZIP
DATEFORMAT 'auto'
TIMEFORMAT 'auto'
DELIMITER ',' 
ACCEPTINVCHARS '?' 
COMPUPDATE TRUE 
STATUPDATE TRUE 
MAXERROR 0 
TRUNCATECOLUMNS  
NULL AS '\000' 
EXPLICIT_IDS
ESCAPE '^'
;

--Error Message
[Amazon](500310) Invalid operation: syntax error at or near "'^'"  
Position: 1311; [SQL State=42601, DB Errorcode=500310] 
In file: C:\temp\test.sql

Upvotes: 1

Views: 694

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

Yes, removing the escape character as \ will make backslash no longer a special character. It will be read just like any other character. To do this remove the ESCAPE parameter from the COPY command.

Now, if you are using backslash as an escape character in other locations in your file(s) you will have just moved the problem. If not, then you should be good to go.

Upvotes: 1

Related Questions