今天就说说hive多个分隔符的问题。

多分隔符(列)这个问题，也是之前同事给我一份文件我才发现的…

这份文件上列分隔符是@#|

hive建表时,ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘@#|’ 直接用@#|，发现输出的字段和文件不一致。

CREATE EXTERNAL TABLE IF NOT EXISTS hjx_temp_ua_190605
(
id String,ua_cut String,user_num String,ua String
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘@#|’
LINES TERMINATED BY ‘\n’
location ‘/user/jfa5d/hjx_temp/ua_temp/’;

查了资料，hive创建表指定分隔符时，不支持多个字符作为分隔符(只支持单个)。

Hive多分隔符支持

Hive在0.14及以后版本支持字段的多分隔符，参考https://cwiki.apache.org/confluence/display/Hive/MultiDelimitSerDe

CREATE EXTERNAL TABLE IF NOT EXISTS hjx_temp_ua_1906052
(
id String,ua_cut String,user_num String,ua String
)
ROW FORMAT SERDE ‘org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe’ WITH SERDEPROPERTIES (“field.delim”=”@#|”)
stored as textfile location ‘/user/jfa5d/hjx_temp/ua_temp2/’;

关键语句为：

ROW FORMAT SERDE ‘org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe’ WITH SERDEPROPERTIES (“field.delim”=”@#|”)
stored as textfile location ‘/user/jfa5d/hjx_temp/ua_temp2/’;

好了，菜鸡埋坑结束..