Escaping in Text Formatted Files

By default, the escape character is a \ (backslash) for text-formatted files. You can declare a different escape character in the ESCAPE clause of COPY, CREATE EXTERNAL TABLE, or the hawq load control file. If your escape character appears in your data, use it to escape itself.

For example, suppose you have a table with three columns and you want to load the following three fields:

  • backslash = \
  • vertical bar = |
  • exclamation point = !

Your designated delimiter character is | (pipe character), and your designated escape character is \ (backslash). The formatted row in your data file looks like this:

  1. backslash = \\ | vertical bar = \| | exclamation point = !

Notice how the backslash character that is part of the data is escaped with another backslash character, and the pipe character that is part of the data is escaped with a backslash character.

You can use the escape character to escape octal and hexidecimal sequences. The escaped value is converted to the equivalent character when loaded into HAWQ. For example, to load the ampersand character (&), use the escape character to escape its equivalent hexidecimal (\0x26) or octal (\046) representation.

You can disable escaping in TEXT-formatted files using the ESCAPE clause of COPY, CREATE EXTERNAL TABLE or the hawq load control file as follows:

  1. ESCAPE 'OFF'

This is useful for input data that contains many backslash characters, such as web log data.