1 问题描述

往 Hive 表 insert 数据后，查询时出现个别行字段错位，插入语句如下：

insert overwrite table A select col1,col2,col3 from table B where dayid = '';

首先测试源表数据查询：

select col1,col2,col3 from B

查询来的数据没发现有什么异常；照理说逐字段查出来没问题，再逐字段插入应该不会错位。实际上 hive 的 insert 跟想象中传统的 insert 不太一样。

2 排查过程

由于不是全表错位，而是个别行错位，首先根据关键字查询 hive 错位那行数据，导出文本到本地。肉眼查看发现有部分"乱码"(异常字符: ^M，如果经验丰富一眼就能看出这个是 \001，vim 下可以通过组合键 ctrl + a 输出），怀疑是异常字符导致，通过 linux od 命令查看 16 进制编码，如图所示：有好几个 \001 ，多么眼熟的数字啊 - 这是 hive 默认字段分隔符。

image.png

一般 insert A from select B 我们没有关注 A 表的字段分隔符，看到 \001 直觉跟 A 表的字段分隔符有关：
查看 A 的表结构，字段分隔符默认的 \001。存储类型：textfile。

进一步分析：textfile 是 hive 默认的存储结构，行存储，存储的实际数据结构跟表逻辑结构一致。导入数据时会直接把数据文件拷贝到 hdfs上不进行处理。源文件可以直接通过hadoop fs -cat 查看； 例如 text 字段分隔符: \001 , 换行符: \n，表在 hdfs 实际存储的格式为：
v1\001v2\001v3\n
v4\001v5\001v5

猜测字段值缺失错位的根源在于：文本中的不可见字符 \001 插入到表中，而表以 \001 作为字段分隔符，导致查询字段错位。

3 问题定位

再来看这条 SQL：

insert overwrite table A select col1,col2,col3 from table B where dayid = '';

我们可以还原这条 SQL 从插入到查询异常的全流程:

1. 查询：select col1,col2,col3 from table B where dayid = ''; 查询出的数据按照 A 表的字段分隔符以及定义的换行符拼接起来。
2. 插入：由于 A 表是 textfile ，直接把查询的数据 insert (append）到 hdfs 上
3. 查询：由于多余的字段符 \001 被插入到 A 表中，查询的时候必然错乱。

4 解决方案

• 入库前清洗掉数据中的 \001
• 修改 A 表的存储格式为 orc 等（不用关心字段分隔符）
• 修改 A 表字段分隔符为其他不可见字符

第一种方式可行且更加合理；
第二种方式可行，一种补救方案，但是 orc 等格式不支持 load 操作
第三种方式临时解决问题，不能根本上解决问题；

5 后续思考

对 hive 的基础知识了解不足，导致问题出现排查速度较慢。
数据源头进行必要的数据 ETL 清洗，对字段分隔符的处理必须谨慎。
Hive 表尽可能使用 orc parquet 这类存储方式，空间占用，查询效率相对 textfile 有大幅提升，同时可以规避字段分隔符，错位等问题。
更深入一步 了解 hive orc 这类存储方式实现原理。