如何在Athena中从含JSON列的CSV文件创建外部表（解决JSON列逗号引发的解析错误）

这个问题我之前处理过，普通的ROW FORMAT DELIMITED方式没法正确识别被引号包裹且内部包含分隔符的字段——就像你CSV里的tags列，它是用双引号括起来的JSON字符串，里面的逗号会被误判为列分隔符，导致表数据解析错乱。

解决办法很简单，咱们换用Athena支持的OpenCSVSerDe就行，它专门针对符合RFC4180标准的CSV文件设计，能自动识别引号包裹的字段，忽略字段内部的分隔符。

下面是修改后的建表语句：

CREATE EXTERNAL TABLE IF NOT EXISTS `test_table`
(
  id STRING,
  name STRING,
  secondary_id STRING,
  created_at TIMESTAMP,
  last_modified_at TIMESTAMP,
  tags STRING,
  report STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  "quoteChar" = "\"",
  "separatorChar" = ",",
  "escapeChar" = "\\"
)
LOCATION 's3://location/'
TBLPROPERTIES (
  'skip.header.line.count' = '1'
);

关键修改点说明： #

• 替换原有的ROW FORMAT DELIMITED为ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'，指定使用OpenCSV序列化/反序列化器来处理CSV文件
• 通过SERDEPROPERTIES配置适配你CSV的格式参数：
  • quoteChar：设置为双引号"，匹配你CSV中包裹JSON字段的引号
  • separatorChar：保留逗号,作为列分隔符
  • escapeChar：设置为反斜杠\，用于识别字段内的转义引号（比如JSON里的\"）

创建完表之后，你再查询tags列，就能得到完整的JSON字符串，不会再被内部的逗号拆分啦。如果之后需要解析这个JSON列的内容，还可以用Athena的json_extract或者json_parse函数进一步处理。

备注：内容来源于stack exchange，提问作者Siddarth Patil