如何解决Redshift Spectrum加载S3数据时列值错误拼接问题

问题根因 #

你配置的OpenCSVSerde转义字符为\，而website列原始值末尾的\会被识别为转义字符，转义了后续的字段分隔符\001，导致Serde错误将website列和其后的firm_type列识别为同一字段，最终出现内容拼接的异常。

解决方案 #

方案1：调整转义字符配置（优先选择） #

如果你的原始数据中没有需要用反斜杠转义的内容，直接修改SERDEPROPERTIES配置，将转义字符修改为数据中不存在的字符即可，调整后的建表语句对应部分如下：

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES (
  'quoteChar' = '"',
  'escapeChar' = '\0',
  'separatorChar' = '\001'
)

这里将escapeChar设置为\0（空字符），只要原始数据中没有用空字符做转义的场景，末尾的反斜杠就会被识别为普通字符保留，分隔符也能正常识别拆分字段。

方案2：预处理原始数据 #

如果你的业务场景确实需要保留\作为转义字符，可在数据写入S3的环节做预处理，将字段末尾的单个\转义为\\，这样Serde解析时会将\\还原为单个\，也不会转义后续的分隔符。

验证操作 #

调整建表语句重建外部表后，如果是分区表先执行MSCK REPAIR TABLE schema.dhs_account_ovrvw;刷新元数据，再执行查询验证字段拆分是否符合预期。

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