HIVE特殊分隔符处理
Hive对文件中的分隔符默认情况下只支持单字节分隔符，,默认单字符是\001。当然你也可以在创建表格时指定数据的分割符号。但是如果数据文件中的分隔符是多字符的，如下图：
01||zhangsan
02||lisi
03||wangwu
补充：hive读取数据的机制
1、首先用inputformat的一个具体的实现类读取文件数据，返回一条条的记录（可以是行，或者是你逻辑中的“行”）
2、然后利用SerDe<默认：org.apache.hadoop.hive.serde2.LazySimpleSerDe>的一个具体的实现类，对上面返回的一条条记录进行字段切割
使用RegexSerDe通过正则表达式来抽取字段
1、建表
|
create table t_bi_reg(id string,name string) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties( 'input.regex'='(.*)\\|\\|(.*)', 'output.format.string'='%1$s%2$s' ) stored as textfile; |
2、加载数据
|
01||zhangsan 02||lisi 03||wangwu load data local inpath '/root/lianggang.txt' into table t_bi_reg; |
3、查询
|
hive> select * from t_bi_reg; OK 01 zhangsan 02 lisi 03 wangwu |
通过自定义inputformat解决特殊分隔符问题
其原理是在inputformat读取行的时候将数据中的“多字节分隔符”替换为hive默认的分隔符（ctrl+A 亦即 \001）或用于替代的单字符分隔符。以便hive在serde操作的时候按照默认的单字节分隔符进行字段抽取
|
package cn.gec.bigdata.hive.inputformat; import java.io.IOException; import org.apache.hadoop.io.LongWritable; import org.apache.hadoop.io.Text; import org.apache.hadoop.mapred.FileSplit; import org.apache.hadoop.mapred.InputSplit; import org.apache.hadoop.mapred.JobConf; import org.apache.hadoop.mapred.LineRecordReader; import org.apache.hadoop.mapred.RecordReader; import org.apache.hadoop.mapred.Reporter; import org.apache.hadoop.mapred.TextInputFormat; public class BiDelimiterInputFormat extends TextInputFormat { @Override public RecordReader<LongWritable, Text> getRecordReader( InputSplit genericSplit, JobConf job, Reporter reporter) throws IOException { reporter.setStatus(genericSplit.toString()); MyDemoRecordReader reader = new MyDemoRecordReader(new LineRecordReader(job, (FileSplit) genericSplit)); // BiRecordReader reader = new BiRecordReader(job, (FileSplit)genericSplit); return reader; } public static class MyDemoRecordReader implements RecordReader<LongWritable, Text> { LineRecordReader reader; Text text; public MyDemoRecordReader(LineRecordReader reader) { this.reader = reader; text = reader.createValue(); } @Override public void close() throws IOException { reader.close(); } @Override public LongWritable createKey() { return reader.createKey(); } @Override public Text createValue() { return new Text(); } @Override public long getPos() throws IOException { return reader.getPos(); } @Override public float getProgress() throws IOException { return reader.getProgress(); } @Override public boolean next(LongWritable key, Text value) throws IOException { boolean next = reader.next(key, text); if(next){ String replaceText = text.toString().replaceAll("\\|\\|", "\\|"); value.set(replaceText); } return next; } } } |
1.打包成jar，放到$HIVE_HOME/lib下
2.建表指明自定义的inputformat
|
create table t_lianggang(id string,name string) row format delimited fields terminated by '|' stored as inputformat 'cn.gec.bigdata.hive.inputformat.BiDelimiterInputFormat' outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'; |
3.加载数据
|
01||zhangsan 02||lisi 03||wangwu load data local inpath '/root/lianggang.txt' into table t_lianggang; |
4.查询
|
hive> select * from t_lianggang; OK 01 zhangsan 02 lisi 03 wangwu |