Hive insert 字段表错位踩坑

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

首先测试源表数据查询：

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

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

再来看这条 SQL：

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

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

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

hive
load数据只是单纯的把文件拷贝到hdfs的相应目录下面，并不作格式检查和解析
只有在查询数据的时候，才会根据创建表时定义的序列化方式解析数据
建表的时候可以指定分隔符
create
table
test(t1
String,t2
String,t3
String,t4
String,t5
String,t6
String,t7
String,t8
String,t9
String,t10
String)
row
format
delimited
fields
terminated
by
'\|'

一次比较复杂的从Hive复杂格式字段解析出多个hotelid的过程

1    所需数据信息在value字段中，value字段是json格式，首先要提取出该字段中的htllist信息

get_json_object(value,'$htllist')

2 get_json_object的返回值是string格式，具体信息如下

字符串前后有'['和']'，每个{}内是单个酒店的信息，{}之前以','分隔

4为了之后把每个{}分隔出来，需要先将','分隔符替换成'|'分隔符，并且将'[]'替换成‘’

regexp_replace(regexp_replace(get_json_object(value,'$htllist'),'},','}\\|'),'\\]|\\[','')

5将上一步得到的字符串按照'|'分隔，得到每个酒店信息的数组

lateral view explode(split(mhtllist,'\\|')) n as htlinfo

6str_to_map函数将酒店信息数组的每个元素转换成map格式，取其中的['hotelid']信息

str_to_map(regexp_replace(nhtlinfo,'\\}|\\{|"',''),',',':')['hotelid']

7完整的提取sql如下

select str_to_map(regexp_replace(nhtlinfo,'\\}|\\{|"',''),',',':')['hotelid'] htlinfo,mvid,msid,mpvid,muid,mcid

  from (select regexp_replace(regexp_replace(get_json_object(value,'$htllist'),'},','}\\|'),'\\]|\\[','') htllist,vid,sid,pvid,uid,cid

          from table_xxx

        where d='2018-08-08'

          and pagecode='condition1'

          and key ='condition2' limit 100)m

  lateral view explode(split(mhtllist,'\\|')) n as htlinfo

where mhtllist is not null

---