如何在Hive中加载含未引号逗号字段的CSV并修正数据

目标 #

将指定CSV文件正确加载至Hive表中。

问题背景 #

待加载的CSV文件中，Name字段未用引号包裹，但部分行的该字段包含逗号，导致这些行的逗号总数超过列数，直接按逗号拆分后数据完全错位。比如示例中第三行的Name为Gertrude's Cafe, L'angolo dell'amore，额外的逗号让该行被拆分成8列，与表结构的7列不匹配。

CSV示例数据 #

Id,Name,City,Ranking,Rating,PriceRange,NumberOfReviews
1,Cafe Q,Lisbon,104,4.5,exp,999
2,Quarto Burguer,Rome,1920,4.0,exp,569
3,Gertrude's Cafe, L'angolo dell'amore,Budapest,55,4.5,med,397
4,Rincon Bar,Valencia,23,4.5,cheap,904

现有错误操作 #

建表语句 #

CREATE TABLE IF NOT EXISTS restaurant 
(
    Id INT,
    Name STRING,
    City STRING,
    Ranking DOUBLE,
    Rating DOUBLE,
    PriceRange STRING,
    NumberOfReviews INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/hive/path_etc'
tblproperties("skip.header.line.count"="1");

数据加载语句 #

LOAD DATA INPATH '/path/to/my_file.csv' OVERWRITE INTO TABLE restaurant;

无效尝试 #

曾尝试用regexp_replace替换Name字段的逗号，但因为数据已经错位，Name列实际只取到了逗号前的部分，替换操作完全无效，还误删了首行：

-- 无效的替换操作
INSERT OVERWRITE TABLE restaurant
SELECT 
    Id,
    regexp_replace(Name, ',', '\'') as Name,
    City,
    Ranking,
    Rating,
    PriceRange,
    NumberOfReviews
FROM 
    restaurant;

正确解决方案 #

方案1：预处理CSV文件（推荐） #

在加载到Hive前，先用脚本修复CSV的格式问题，将Name字段中的逗号替换为其他符号（比如空格），同时修正列数。可以用awk脚本批量处理：

# 处理CSV：合并错位的Name字段，替换其中的逗号为空格，保持列数正确
awk -F ',' '
NR==1 {print; next}
NF==8 {
    $2 = $2 " " $3  # 合并第2、3列为Name，用空格替换原逗号
    for(i=3; i<=NF-1; i++) $i = $(i+1)  # 左移后续列
    NF--  # 列数减1，回到正常的7列
    print
}
NF==7 {print}  # 正常行直接输出
' input.csv > output.csv

处理完成后，用原有的建表和加载语句导入output.csv即可。

方案2：先解析再清洗 #

如果无法预处理文件，可以先创建一个临时表，用更灵活的方式读取数据，再清洗后导入正式表：

1. 创建临时表，存储整行字符串：

CREATE TABLE IF NOT EXISTS restaurant_temp 
(
    line STRING
)
STORED AS TEXTFILE
LOCATION '/user/hive/path_temp'
tblproperties("skip.header.line.count"="1");

2. 加载原始CSV到临时表：

LOAD DATA INPATH '/path/to/my_file.csv' OVERWRITE INTO TABLE restaurant_temp;

3. 解析每行数据，修复Name字段后插入正式表：

INSERT OVERWRITE TABLE restaurant
SELECT 
    split(line, ',')[0] as Id,
    regexp_replace(concat(split(line, ',')[1], ' ', split(line, ',')[2]), ',', ' ') as Name,
    split(line, ',')[3] as City,
    split(line, ',')[4] as Ranking,
    split(line, ',')[5] as Rating,
    split(line, ',')[6] as PriceRange,
    split(line, ',')[7] as NumberOfReviews
FROM restaurant_temp
WHERE size(split(line, ','))=8
UNION ALL
SELECT 
    split(line, ',')[0] as Id,
    split(line, ',')[1] as Name,
    split(line, ',')[2] as City,
    split(line, ',')[3] as Ranking,
    split(line, ',')[4] as Rating,
    split(line, ',')[5] as PriceRange,
    split(line, ',')[6] as NumberOfReviews
FROM restaurant_temp
WHERE size(split(line, ','))=7;

解释：通过判断每行拆分后的列数，分别处理正常行（7列）和异常行（8列），合并异常行的第2、3列为Name并替换逗号，保证数据匹配表结构。

内容的提问来源于stack exchange，提问作者T.K.