how to read escape character in Athena

Question

Hi I have created a table in Athena with following query which will read csv file form S3.

CREATE EXTERNAL TABLE IF NOT EXISTS axlargetable.AEGIntJnlTblStaging (
  `filename` string,
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar' = '\"'
   ) 
LOCATION 's3://ax-large-table/AEGIntJnlTblStaging/'
TBLPROPERTIES ('has_encrypted_data'='false');

But value in filename filed like "\\emdc1fas\HR_UK\ADPFreedom_Employee_20141114_11.04.00.csv"

When I read this table my values appears like

"\emdc1fasHR_UKADPFreedom_Employee_20141114_11.04.00.csv"

where I missing all the escape character (backslash) from the value.

How can I read the value which will show me the actual value with escape character.

Thanks

Answer 1

As long as you don't need the escaping, you can set the escape character to something unrelated (for example "|").

CREATE EXTERNAL TABLE IF NOT EXISTS axlargetable.AEGIntJnlTblStaging (
  filename string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar' = '\"',
   'escapeChar' = '|'
   ) 
LOCATION 's3://ax-large-table/AEGIntJnlTblStaging/'
TBLPROPERTIES ('has_encrypted_data'='false');