0026-Hive使用十六進(jìn)制分隔符異常分析

溫馨提示：要看高清無(wú)碼套圖，請(qǐng)使用手機(jī)打開(kāi)并單擊圖片放大查看。

1.問(wèn)題描述

通過(guò)sqoop抽取Mysql表數(shù)據(jù)到hive表，發(fā)現(xiàn)hive表所有列顯示為null

Hive表的分隔符為“\u001B”,sqoop指定的分隔符也是“\u001B”

通過(guò)命令show create table test_hive_delimiter查看建表語(yǔ)句如下:

0: jdbc:hive2://localhost:10000/> show create table test_hive_delimiter;
...
INFO  : OK
+----------------------------------------------------+--+
|                   createtab_stmt                   |
+----------------------------------------------------+--+
| CREATE EXTERNAL TABLE `test_hive_delimiter`(       |
|   `id` int,                                        |
|   `name` string,                                   |
|   `address` string)                                |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| WITH SERDEPROPERTIES (                             |
|   'field.delim'='\u0015',                          |
|   'serialization.format'='\u0015')                 |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'hdfs://ip-172-31-6-148.fayson.com:8020/fayson/test_hive_delimiter' |
| TBLPROPERTIES (                                    |
|   'COLUMN_STATS_ACCURATE'='false',                 |
|   'numFiles'='0',                                  |
|   'numRows'='-1',                                  |
|   'rawDataSize'='-1',                              |
|   'totalSize'='0',                                 |
|   'transient_lastDdlTime'='1504705887')            |
+----------------------------------------------------+--+
22 rows selected (0.084 seconds)
0: jdbc:hive2://localhost:10000/> 

發(fā)現(xiàn)Hive的原始建表語(yǔ)句中的分隔符是“\u001B”而通過(guò)show create table test_hive_delimiter命令查詢出來(lái)的分隔符為“\u0015”，分隔符被修改了。

2.問(wèn)題復(fù)現(xiàn)

1.創(chuàng)建Hive表test_hive_delimiter，使用“\u001B”分隔符

create external table test_hive_delimiter
(
  id int,
  name string,
  address string
)
row format delimited fields terminated by '\u001B'
stored as textfile location '/fayson/test_hive_delimiter';

2.使用sqoop抽取MySQL中test表數(shù)據(jù)到hive表（test_hive_delimiter）

 [root@ip-172-31-6-148 ~]# sqoop import --connect jdbc:mysql://ip-172-31-6-148.fayson.com:3306/fayson -username root -password 123456 --table test -m 1 --hive-import --fields-terminated-by "\0x001B" --target-dir /fayson/test_hive_delimiter --hive-table test_hive_delimiter

數(shù)據(jù)抽取成功：

[root@ip-172-31-6-148 ~]# hadoop fs -ls /fayson/test_hive_delimiter
Found 2 items
-rw-r--r--   3 fayson supergroup          0 2017-09-06 13:46 /fayson/test_hive_delimiter/_SUCCESS
-rwxr-xr-x   3 fayson supergroup         56 2017-09-06 13:46 /fayson/test_hive_delimiter/part-m-00000
[root@ip-172-31-6-148 ~]# hadoop fs -ls /fayson/test_hive_delimiter/part-m-00000
-rwxr-xr-x   3 fayson supergroup         56 2017-09-06 13:46 /fayson/test_hive_delimiter/part-m-00000
[root@ip-172-31-6-148 ~]# 

3.查看test_hive_delimiter表數(shù)據(jù)

[root@ip-172-31-6-148 ~]# beeline 
Beeline version 1.1.0-cdh6.12.1 by Apache Hive
beeline> !connect jdbc:hive2://localhost:10000/;principal=hive/ip-172-31-6-148.fayson.com@FAYSON.COM
...
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000/> select * from test_hive_delimiter;
...
INFO  : OK
+-------------------------+---------------------------+------------------------------+--+
| test_hive_delimiter.id  | test_hive_delimiter.name  | test_hive_delimiter.address  |
+-------------------------+---------------------------+------------------------------+--+
| NULL                    | NULL                      | NULL                         |
| NULL                    | NULL                      | NULL                         |
| NULL                    | NULL                      | NULL                         |
+-------------------------+---------------------------+------------------------------+--+
3 rows selected (0.287 seconds)
0: jdbc:hive2://localhost:10000/> 

4.Hive表的建表語(yǔ)句如下

3.解決方法

分隔符“\u001B”為十六進(jìn)制，而Hive的分隔符實(shí)際是八進(jìn)制，所以在使用十六進(jìn)制的分隔符時(shí)會(huì)被Hive轉(zhuǎn)義，所以出現(xiàn)使用“\u001B”分隔符創(chuàng)建hive表后顯示的分隔符為“\u0015”。

在不改變數(shù)據(jù)文件分隔符的情況下，要先將十六進(jìn)制分隔符轉(zhuǎn)換成八進(jìn)制分隔符來(lái)創(chuàng)建Hive表。

1.將十六進(jìn)制分隔符轉(zhuǎn)換為八進(jìn)制分隔符

“\u001B”轉(zhuǎn)換八進(jìn)制為“\033”,在線轉(zhuǎn)換工具：http://tool.lu/hexconvert/

2.修改建表語(yǔ)句使用八進(jìn)制“\033”作為分隔符

create external table test_hive_delimiter
(
  id int,
  name string,
  address string
)
row format delimited fields terminated by '\033'
stored as textfile location '/fayson/test_hive_delimiter';

使用命令show create table test_hive_delimiter查看建表語(yǔ)句

0: jdbc:hive2://localhost:10000/> show create table test_hive_delimiter;
...
INFO  : OK
+----------------------------------------------------+--+
|                   createtab_stmt                   |
+----------------------------------------------------+--+
| CREATE EXTERNAL TABLE `test_hive_delimiter`(       |
|   `id` int,                                        |
|   `name` string,                                   |
|   `address` string)                                |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| WITH SERDEPROPERTIES (                             |
|   'field.delim'='\u001B',                          |
|   'serialization.format'='\u001B')                 |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'hdfs://ip-172-31-6-148.fayson.com:8020/fayson/test_hive_delimiter' |
| TBLPROPERTIES (                                    |
|   'COLUMN_STATS_ACCURATE'='false',                 |
|   'numFiles'='0',                                  |
|   'numRows'='-1',                                  |
|   'rawDataSize'='-1',                              |
|   'totalSize'='0',                                 |
|   'transient_lastDdlTime'='1504707693')            |
+----------------------------------------------------+--+
22 rows selected (0.079 seconds)
0: jdbc:hive2://localhost:10000/> 

3.查詢test_hive_delimiter表數(shù)據(jù)

0: jdbc:hive2://localhost:10000/> select * from test_hive_delimiter;
...
INFO  : OK
+-------------------------+---------------------------+------------------------------+--+
| test_hive_delimiter.id  | test_hive_delimiter.name  | test_hive_delimiter.address  |
+-------------------------+---------------------------+------------------------------+--+
| 1                       | fayson                    | guangdong                    |
| 2                       | zhangsan                  | shenzheng                    |
| 3                       | lisi                      | shanghai                     |
+-------------------------+---------------------------+------------------------------+--+
3 rows selected (0.107 seconds)
0: jdbc:hive2://localhost:10000/> 

將十六進(jìn)制的”\u001B”轉(zhuǎn)換為八進(jìn)制的”\033”建表，問(wèn)題解決。

4.備注

1. Hive建表時(shí)使用十六進(jìn)制分割符需要注意，部分分隔符會(huì)被轉(zhuǎn)義（如：001B/001C等）
2. Sqoop指定十六進(jìn)制分隔符，為什么是“\0x001B”而不是“\u001B”,可參考Sqoop

官網(wǎng)說(shuō)明：https://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#\_file\_formats

醉酒鞭名馬，少年多浮夸！ 嶺南浣溪沙，嘔吐酒肆下！摯友不肯放，數(shù)據(jù)玩的花！
溫馨提示：要看高清無(wú)碼套圖，請(qǐng)使用手機(jī)打開(kāi)并單擊圖片放大查看。

推薦關(guān)注Hadoop實(shí)操，第一時(shí)間，分享更多Hadoop干貨，歡迎轉(zhuǎn)發(fā)和分享。

原創(chuàng)文章，歡迎轉(zhuǎn)載，轉(zhuǎn)載請(qǐng)注明：轉(zhuǎn)載自微信公眾號(hào)Hadoop實(shí)操