Athena使用OpenCSVSerde解析CSV时尾部斜杠导致数据异常问题咨询

问题根因 #

你遇到的解析异常是SerDe转义字符配置与CSV文件实际使用的转义规则不匹配导致的，不属于OpenCSVSerde的已知缺陷：

• 你当前建表语句中escapeChar设置为"，仅能识别双引号本身的转义规则（即两个连续双引号""转义为单个双引号"），这也是前两行数据可以正常解析的原因。
• 你的第3、4行CSV使用了反斜杠\作为转义字符，用于转义逗号、双引号，当前配置完全不识别\的转义作用，所以会把\后的逗号、双引号当成普通的字段分隔符、引号闭合标记，最终出现字段截断、空值的问题。

解决方案 #

方案1：调整CSV转义规则（最推荐） #

遵循RFC4180标准CSV规范，统一使用双引号作为转义符，修改源CSV的异常行：

• 原第3行1,2\,3改为1,"2,3"
• 原第4行1,"2,0\",3改为1,"2,0""",3
  修改后无需调整现有表配置，即可正常解析所有数据。

方案2：修改SerDe转义字符配置 #

如果无法修改源CSV，且CSV中没有双引号重复转义的场景，可以调整建表时的escapeChar配置为反斜杠，注意Athena中建表时反斜杠需要额外转义：

CREATE EXTERNAL TABLE `david_korean_test`(
`a` string COMMENT 'from deserializer', 
`b` string COMMENT 'from deserializer', 
`c` string COMMENT 'from deserializer')
ROW FORMAT SERDE 
'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
'escapeChar'='\\', 
'quoteChar'='\"', 
'separatorChar'=',') 
STORED AS INPUTFORMAT 
'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://...'
TBLPROPERTIES (
'skip.header.line.count'='1')

注意：该方案会导致原第二行She said ""Hi!""的解析结果异常，因为此时双引号重复不再被识别为转义规则。

方案3：使用RegexSerDe自定义解析（适配混合转义场景） #

如果既不能修改源CSV，又需要同时支持两种转义规则，可以替换为RegexSerDe自定义正则匹配每行数据，示例建表配置：

CREATE EXTERNAL TABLE `david_korean_test`(
`a` string, 
`b` string, 
`c` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "^([^,]*),((?:\"(?:\\\\.|[^\"])*\"|[^,])*),([^,]*)$"
)
LOCATION 's3://...'
TBLPROPERTIES ('skip.header.line.count'='1');

该正则可以同时兼容反斜杠转义、双引号转义的场景，不需要修改源数据。

内容的提问来源于stack exchange，提问作者DJ180