CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-athena
Sumon
Sumon

Reputation: 289

how to read escape character in Athena

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

Upvotes: 2

Views: 8839

Answers (1)

jens walter
jens walter

Reputation: 14029

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');

Upvotes: 2

Related Questions