解决hive中导入text文件遇到的坑

 更新时间:2021年4月7日 15:01  
这篇文章主要介绍了解决hive中导入text文件遇到的坑,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧

今天帮一同学导入一个excel数据,我把excel保存为txt格式,然后建表导入,失败!分隔符格式不匹配,无法导入!!!!怎么看两边都是\t,怎么不匹配呢?

做为程序员,最不怕的就是失败,因为我们有一颗勇敢的心!再来!又特么失败。。。

想了好久,看看了看我的表格式,我犯了一个好低级的错误:

hive表的存储格式设置为了orcfile!!!

众所周知:orcfile为压缩格式,可以节约大量存储空间,但orc还有个特点就是不能直接load数据!要想load数据,我们要建一个存储格式为textfile的中间表,然后再把数据抽取过去。因为这个错误太简单,网上有相关科普,因此很少有把它当错误写出来。遇到问题的朋友们可能要走些弯路,我来补个漏~~~~~~

举个栗子:

1.首先,导出excel表格为txt格式,(这个过程不再赘述,网上教程一大把)。

123,小明,666,1990-09-23 12:00:18
256,小伙,555,1989-10-06 03:57:32
142,小兰,444,1992-07-04 05:05:45

2.在hive中创建表模型:

CREATE TABLE IF NOT EXISTS STUDENTS
(
ID INT COMMENT'学生',
SNAME STRING COMMENT '姓名',
SCORE INT COMMENT '得分',
STIME STRING COMMENT '考试时间'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS ORCFILE;

3.创建临时表(中间表):

CREATE TABLE IF NOT EXISTS STUDENTS_TMP
(
ID INT COMMENT'学生',
SNAME STRING COMMENT '姓名',
SCORE INT COMMENT '得分',
STIME STRING COMMENT '考试时间'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

与目标表只有名称和存储格式不同,其他都相同。

4.load 数据到临时表:

load data local inpath '/export/data/1.txt' into table students_tmp;

5.将数据导入目标表:

insert overwrite table students select * from students_tmp;

6.然后查看表数据,大功造成:

hive > select * from students;
OK
123 小明 666 1990-09-23 12:00:18
256 小伙 555 1989-10-06 03:57:32
142 小兰 444 1992-07-04 05:05:45
Time taken: 0.26 seconds, Fetched: 3 row(s)

其他存储格式如 SEQUENCEFILE、PARQUET 等,也要选存储为textfile格式,然后抽入目标表。

一定要按照导出格式的分隔符建表,不然load数据必然出错或全是null;

excel导出格式:

格式 分隔符 中文名称
text \t  制表符
csv , 逗号

7.还要注意一点是我们从excel导出的文件格式是gb2312 (无论是txt还是csv都是这个格式,都需要转码),我们需要把它转成utf-8才能Load。

所以在load之前,我们一般会采取两种办法:

1. 在文本编辑器中进行转码,带不带bom关系不大,然后上传;

2.在文件所在本地目录下执行以下命令转码:

piconv -f gb2312 -t UTF-8 1.txt > 2.txt

注意,在本地目录下命令转码会改变文件名,因为此命令会把所文件写入到另一个文件,并清空原文件内容,如果我们不改名,文件内容会完全丢失。所以,我们Load的时候一定要选择修改后的文件名哦。

示例:

转码前数据:

hive> select * from students;
OK
112	��	35	2017/8/19 15:30
113	����	45	
114	³��	55	2017/8/21 15:30
115	����	NULL	
116	������	75	2017/8/23 15:30
117	������	85	2017/8/24 15:30
118	�˽�	NULL	2017/8/25 15:30
119	������	90	
120	СѾ	NULL	2017/8/27 15:30
121	����	80	2017/8/28 15:30
122	��߸	75	
123	��«��	70	2017/8/30 15:30
124	����	NULL	2017/8/31 15:30
125	�繤	NULL	
126	�峤	NULL	2017/9/2 15:30
127	˾��	50	2017/9/3 15:30
128	������	58	2017/9/4 15:30
129	����	66	2017/9/5 15:30
Time taken: 0.134 seconds, Fetched: 18 row(s)

去所在目录下转码,再Load

piconv -f gb2312 -t UTF-8 2.csv > 3.csv
# 在hive中选择正确的文件Load:
hive> load data local inpath '/export/data/3.csv' into table students;

结果:

hive> select * from students;
OK
112	小宝	35	2017/8/19 15:30
113	王明	45	
114	鲁班	55	2017/8/21 15:30
115	苗苗	NULL	
116	少林寺	75	2017/8/23 15:30
117	体育界	85	2017/8/24 15:30
118	八戒	NULL	2017/8/25 15:30
119	周芷若	90	
120	小丫	NULL	2017/8/27 15:30
121	海宝	80	2017/8/28 15:30
122	哪吒	75	
123	葫芦娃	70	2017/8/30 15:30
124	丹枫	NULL	2017/8/31 15:30
125	电工	NULL	
126	村长	NULL	2017/9/2 15:30
127	司机	50	2017/9/3 15:30
128	王世间	58	2017/9/4 15:30
129	松鼠	66	2017/9/5 15:30
Time taken: 0.106 seconds, Fetched: 18 row(s)

补充:hive导入数据出现NULL

在把hdfs上数据迁移到hive中的表时,若出现数据位NULL,是因为没有指定列分隔符。

由于hive默认的分隔符是/u0001(Ctrl+A),为了平滑迁移,需要在创建表格时指定数据的分割符号,语法如下:

hive (default)> create external table et (time BIGINT, userid string, content string, urlrank int, urlnum int, url string)
  > partitioned by (filenum int) 
  > row format delimited fields terminated by '\t'; 

上面创建的是外部表,“导入”数据时可以用load,但若不想进行移动数据,就用命令alter来进行指向:

alter table et add partition (filenum=1) location '/input/SogouQueryLog/file1';

注意location后面跟的地址必须是个目录,若不是,可以用hdfs fs -mv src dest 进行移动数据:

hadoop fs -mv /input/SogouQueryLog/querylog_1 /input/SogouQueryLog/file1

以上为个人经验,希望能给大家一个参考,也希望大家多多支持猪先飞。如有错误或未考虑完全的地方,望不吝赐教。