如何处理含未加引号内部逗号的CSV文件并加载至Hive

嘿，这个问题我之前帮团队解决过，确实挺棘手的——默认的Hive SerDe或者简单分隔符加载方式，一碰到字段内没加引号的逗号，直接就把数据切得七零八落了。给你几个实用的方案，你可以根据自己的数据规律来选：

方案1：预处理替换内部逗号（最省心的常规方案） #

如果你的数据有固定结构，比如只有特定位置的字段会包含逗号（比如地址、描述字段），那先通过脚本把这些内部逗号替换成一个不会和其他分隔符冲突的字符（比如|或者#），再加载到Hive，之后按需替换回去就行。

举个例子，假设你的数据格式是：用户ID,用户名,用户地址,手机号，其中用户地址可能包含逗号（比如北京市,朝阳区），其他字段都是纯文本无逗号。用awk来处理就很方便：

# 处理逻辑：提取第1、2字段，合并第3到倒数第2个字段作为地址，最后提取手机号
awk -F ',' '{
    user_id = $1;
    user_name = $2;
    phone = $NF;
    address = "";
    # 把第3到倒数第2个字段拼接起来，保留内部的逗号
    for(i=3; i<=NF-1; i++){
        address = address $i (i == NF-1 ? "" : ",");
    }
    # 用|替换地址里的逗号（或者你选的其他符号）
    gsub(",", "|", address);
    print user_id "," user_name "," address "," phone;
}' raw_data.txt > processed_data.txt

之后在Hive里建表时，用逗号作为分隔符加载处理后的文件，查询时再把|替换回逗号就行：

CREATE EXTERNAL TABLE user_info (
    user_id STRING,
    user_name STRING,
    user_address STRING,
    phone STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/hive/warehouse/user_info/';

-- 查询时还原逗号
SELECT user_id, user_name, REPLACE(user_address, '|', ',') AS user_address, phone FROM user_info;

方案2：用正则表达式直接提取字段 #

如果不想预处理数据，也可以把整行数据当成一个字符串加载，再用Hive的regexp_extract函数按规则提取每个字段。

首先建表时，指定一个不会出现在数据里的字符作为分隔符（本质是把整行当单个字段）：

CREATE EXTERNAL TABLE user_info_raw (
    line STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
    "serialization.format" = "
"  -- 用ASCII的SOH字符，几乎不会出现在业务数据里
)
LOCATION '/user/hive/warehouse/user_info_raw/';

然后通过正则提取字段——这里要利用数据的规律，比如前两个字段没有逗号，最后一个字段（手机号）也没有逗号，中间的地址字段包含逗号：

SELECT
    regexp_extract(line, '^(.*?),(.*?),(.*),(.*)$', 1) AS user_id,
    regexp_extract(line, '^(.*?),(.*?),(.*),(.*)$', 2) AS user_name,
    regexp_extract(line, '^(.*?),(.*?),(.*),(.*)$', 3) AS user_address,
    regexp_extract(line, '^(.*?),(.*?),(.*),(.*)$', 4) AS phone
FROM user_info_raw;

注意：正则里的.*?是非贪婪匹配，确保前两个字段不会把后面的逗号包含进去；.*则会匹配到最后一个逗号前的所有内容，正好对应含内部逗号的地址字段。

方案3：自定义SerDe（适合复杂场景） #

如果你的数据没有固定规律，或者上述方案都不适用，可以自己写一个自定义SerDe，根据业务规则来分割字段。比如用Java实现org.apache.hadoop.hive.serde2.SerDe接口，在deserialize方法里编写逻辑识别哪些逗号是字段分隔符，哪些是字段内部的。

不过这个需要一定的开发能力，如果你不想自己写，也可以找找开源的第三方SerDe（比如一些专门处理非标准CSV的SerDe），但要注意和你Hive版本的兼容性。

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