character slash is not being read by hive on using OpenCSVSerde

Question

I have defined a table on top of files present in hdfs. I am using the OpenCSV Serde to read from the file. But, '\' slash characters in the data are getting omitted in the final result set.

Is there a hive serde property that I am not using correctly. As per the documentation, escapeChar = '\' should fix this problem. But, the problem persists.

   CREATE EXTERNAL TABLE `tsr`(
    `last_update_user` string COMMENT 'from deserializer',
    `last_update_datetime` string COMMENT 'from deserializer')
    ROW FORMAT SERDE
    'org.apache.hadoop.hive.serde2.OpenCSVSerde'
    WITH SERDEPROPERTIES (
    'escapeChar'='\',
    'quoteChar'='\"',
    'separatorChar'=',',
    'serialization.encoding'='UTF-8')
    STORED AS INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
    OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    LOCATION
    'hdfs://edl/hive/db/tsr'
    TBLPROPERTIES (
    'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}',
    'numFiles'='1',
    'numRows'='1869',
    'rawDataSize'='0',
    'serialization.null.format'='',
    'totalSize'='144640',
    'transient_lastDdlTime'='1524479930')

Sample Output:

DomainUser1 , 2017-07-04 19:07:27

Expected Result:

Domain\User1 , 2017-07-04 19:07:27

EDIT 1: I have tried both '\\' and '\' as the escapeChar and both have the same problem

can you provide sample data for the input file? – hlagos Apr 23 '18 at 12:31 — hlagos, Apr 23 '18 at 12:31

score 2 · Accepted Answer · answered Apr 23 '18 at 12:53

Unfortunately the csv serde in Hive does not support multiple characters as separator/quote/escape, it looks like you want to use 2 backlslahes as escapeChar (which is not possible) consideirng than OpenCSVSerde only support a single character as escape (actually it is using CSVReader which only supports one). I am not aware about any other SerDe that supports multiple characters in Hive, you can always implement your own udf with other library, not the most popular option (nobody wants to support its own stuffs :)). I would recommend use a different character as escape, hopefully one not present in your data. A second option would be modify your data during your ingestion to replace \ by \\

Talked to source teams, and zeroed in on a character that can be safely used for escapeChar. Thanks for your help!! — ForeverLearner, Apr 24 '18 at 06:18

score 0 · Answer 2 · answered Apr 23 '18 at 11:00

0

In the document the "escapeChar"= "\\" is mentioned with two backslashes. please check it.

WITH SERDEPROPERTIES (
   "separatorChar" = "\t",
   "quoteChar"     = "'",
   "escapeChar"    = "\\"
)

answered Apr 23 '18 at 11:00

'\\' is to over-ride the default escape character which is '\'. I have tried both and neither of them work – ForeverLearner Apr 23 '18 at 11:05
set 'escapeChar' in your serde properties to something other than backslash – Apr 23 '18 at 11:14

score 0 · Answer 3 · answered Apr 29 '19 at 13:14

0

I had a similar issue it can be solved changing the "escapeChar" = "\" to something else for example "escapeChar" ="\n".

answered Apr 29 '19 at 13:14

petikun

1
2

character slash is not being read by hive on using OpenCSVSerde

3 Answers3

Linked