Hive加载含特殊字符的管道分隔数据导致列错位问题

这个问题我之前也碰到过，核心就是你用的分隔符|和数据里的转义\|冲突了——Hive默认的ROW FORMAT DELIMITED解析器根本识别不了这种带转义的分隔符，直接把所有|都当成列分隔符，自然就导致COL3被拆成两列，后面的字段全偏移了。下面给你几个实用的解决办法：

方法1：使用OpenCSVSerDe（最推荐） #

Hive自带的OpenCSVSerDe就是专门处理带转义、引号的结构化数据的，完美适配你的场景。你只需要重新建表时指定这个SerDe，同时配置转义字符为\就行：

CREATE TABLE TEST_1 (
    COL1 string,
    COL2 string,
    COL3 string,
    COL4 string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    "separatorChar" = "|",
    "escapeChar" = "\\"
)
STORED AS TEXTFILE;

然后加载你的原始数据：

LOAD DATA LOCAL INPATH '/path/to/your/data.txt' INTO TABLE TEST_1;

查询验证的话，SELECT * FROM TEST_1;就能得到正确结果：

这个方法不用修改原始数据，靠SerDe的配置直接搞定，省心又稳定。

方法2：预处理数据（适合临时小批量场景） #

如果不想换SerDe，也可以先把原始数据里的转义\|替换成一个不会和分隔符冲突的临时字符（比如#），加载到表后再替换回来：

1. 预处理数据（用Linux命令举例）：

sed 's/\\|/#/g' your_raw_data.txt > processed_data.txt

处理后的数据会变成123|456|Dasani Bottled # Water|789

2. 用你原来的表结构建表，加载预处理后的数据：

CREATE table TEST_1 (
    COL1 string,
    COL2 string,
    COL3 string,
    COL4 string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' ;

LOAD DATA LOCAL INPATH '/path/to/processed_data.txt' INTO TABLE TEST_1;

3. 替换回原来的转义字符：

UPDATE TEST_1 SET COL3 = REPLACE(COL3, '#', '\\|');

注意Hive里转义\需要写两个\\，不然会被当成转义符处理掉。

方法3：使用正则表达式解析行（灵活但繁琐） #

如果上面两种方法都不适用，还可以用Hive的正则表达式直接解析每一行数据，精准提取4列：

-- 先创建临时表存原始行数据
CREATE TABLE TEST_1_raw (
    raw_line string
) STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/path/to/your/data.txt' INTO TABLE TEST_1_raw;

-- 创建最终表并插入解析后的数据
CREATE TABLE TEST_1 AS
SELECT
    regexp_extract(raw_line, '^(.*?)\\|(.*?)\\|(.*?\\\\\\|.*?)\\|(.*)$', 1) AS COL1,
    regexp_extract(raw_line, '^(.*?)\\|(.*?)\\|(.*?\\\\\\|.*?)\\|(.*)$', 2) AS COL2,
    regexp_extract(raw_line, '^(.*?)\\|(.*?)\\|(.*?\\\\\\|.*?)\\|(.*)$', 3) AS COL3,
    regexp_extract(raw_line, '^(.*?)\\|(.*?)\\|(.*?\\\\\\|.*?)\\|(.*)$', 4) AS COL4
FROM TEST_1_raw;

这个正则的逻辑是：先匹配前两个用|分隔的字段，再匹配包含\|的第三个字段，最后匹配第四个字段。不过正则写法比较复杂，要是数据格式有变化容易出错，只推荐在特殊场景下用。

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