解決hive中導入text文件遇到的坑

Posted on by

今天幫一同學導入一個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

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：

    None Found