java hive默认分隔符,Hive 中使用多字符字符串作为字段分隔符

Hive建表语句中得FIELDS TERMINATED BY 只能是单字符，遇到多字符作为分隔符的就尴尬了。目前我们的字段分隔符是’@#@’ 。遇到这个问题除了变更分隔符外，hive也可以使用serde的方式来支持多字符作为分隔符。

例如一个分隔符为’@#@’的数据，有3个字段

create table hive_test(

id string,

tour_cd string,

flt_statis_cd string )

ROW FORMAT

SERDE ‘org.apache.hadoop.hive.contrib.serde2.RegexSerDe’

WITH SERDEPROPERTIES

( ‘input.regex’ = ‘^([^@#]*)@#@([^@#]*)@#@([^@#]*)’,

‘output.format.string’ = ‘%1$s %2$s %3$s ‘)

STORED AS TEXTFILE;

input.regex 就是按照java的字段分割正则表达式方式编写。

output.format.string 按照顺序往后递增即可。

需要注意的是，字段类型只支持string，不然就会报错：

FAILED: Error in metadata: java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException org.apache.hadoop.hive.contrib.serde2.RegexSerDe only accepts string columns, but column[3] named id_valid_ind has type int)

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

建完以后就可以往hive表里面load数据了。但是用的时候很可能报这个错。

Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.contrib.serde2.RegexSerDe

at java.net.URLClassLoader$1.run(URLClassLoader.java:202)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(URLClassLoader.java:190)

at java.lang.ClassLoader.loadClass(ClassLoader.java:306)

at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)

at java.lang.ClassLoader.loadClass(ClassLoader.java:247)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:247)

at org.apache.hadoop.conf.Configuration.getClassByName(Configuration.java:891)

at org.apache.hadoop.hive.ql.exec.MapOperator.initObjectInspector(MapOperator.java:233)

at org.apache.hadoop.hive.ql.exec.MapOperator.setChildren(MapOperator.java:366)

... 33 more

执行add jar 命令 将hive-contrib.jar 加入再执行hive语句即可

hive> add jar /usr/lib/hive/lib/hive-contrib-0.9.0-Intel.jar;

Added /usr/lib/hive/lib/hive-contrib-0.9.0-Intel.jar to class path

Added resource: /usr/lib/hive/lib/hive-contrib-0.9.0-Intel.jar

一个带分区的外部表，自定义多字符字符串作为分隔符的建表语句例子

create EXTERNAL table hive_test(

seg_fr_bs string,

tour_cd string,

flt_statis_cd string )

PARTITIONED BY(dt STRING)

ROW FORMAT

SERDE ‘org.apache.hadoop.hive.contrib.serde2.RegexSerDe’

WITH SERDEPROPERTIES

( ‘input.regex’ = ‘^([^@#]*)@#@([^@#]*)@#@([^@#]*))’,

‘output.format.string’ = ‘%1$s %2$s %3$s’)

STORED AS TEXTFILE

LOCATION ‘/user/adhoc/file/pir2_base_ics_wxl’;