Athena创建外部表时如何避免CSV字段内逗号导致拆分错误

原建表语句使用的ROW FORMAT DELIMITED分隔符解析规则不支持识别被引号包裹的字段内的逗号，Excel导出的CSV默认会将包含逗号、换行等特殊字符的字段用双引号包裹，因此需要替换为专门处理CSV格式的OpenCSV SerDe来解析。

修改后的建表脚本 #

CREATE EXTERNAL TABLE `itcfmetadata`(
  `itcf id` string, 
  `itcf control name` string, 
  `itcf control description` string, 
  `itcf process` string, 
  `standard` string, 
  `controlid` string, 
  `threshold` string, 
  `status` string, 
  `date reported` string, 
  `remediation (accs specific)` string, 
  `aws account id` string, 
  `aws resource id` string, 
  `aws account owner` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = ',',
  'quoteChar' = '"',
  'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION
  's3://itcfmetadata/'
TBLPROPERTIES (  
  'skip.header.line.count'='1');

适配说明 #

• 如果你的Excel导出的CSV中，字段内的双引号是用两个连续双引号转义的（比如字段内容是He said "hello"，存储为He said ""hello""），可以将escapeChar的值修改为"，匹配Excel的转义规则
• 如果你的CSV使用单引号包裹包含特殊字符的字段，将quoteChar的值修改为'即可

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