Hive 操作

（一）表操作

Hive 和 Mysql 的表操作語句類似，如果熟悉 Mysql，那Hive 的表操作就非常容易，下面對 Hive 的表操作進行深入講解。

(1)先來建立一個表名為student1的內部表

hive> CREATE TABLE IF NOT EXISTS student1

(sno INT,sname STRING,age INT,sex STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

STORED AS TEXTFILE;

建表規則如下：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...)

[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format]

[STORED AS file_format] [LOCATION hdfs_path]

•CREATE TABLE 建立一個指定名字的表。如果相同名字的表已經存在，則丟擲異常；使用者可以用 IF NOT EXIST 選項來忽略這個異常

•EXTERNAL 關鍵字可以讓使用者建立一個外部表，在建表的同時指定一個指向實際資料的路徑

（LOCATION）

•LIKE 允許使用者複製現有的表結構，但是不復制資料

•COMMENT可以為表與欄位增加描述

•ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

使用者在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，使用者還需要為表指定列，使用者在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe 確定表的具體的列的資料。

•STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

如果檔案資料是純文字，可以使用 STORED AS TEXTFILE。如果資料需要壓縮，使用

STORED AS SEQUENCE 。

（2）建立外部表

hive> CREATE EXTERNAL TABLE IF NOT EXISTS student2

> (sno INT,sname STRING,age INT,sex STRING)

> ROW FORMAT DELIMITED

> FIELDS TERMINATED BY '\t'

> STORED AS TEXTFILE

> LOCATION '/user/external';

OK

Time taken: 0.331 seconds

hive> show tables;

OK student1 student2

Time taken: 0.06 seconds, Fetched: 12 row(s)

（3）刪除表

首先建立一個表名為test1的表

hive> CREATE TABLE IF NOT EXISTS test1 > (id INT,name STRING);

OK

Time taken: 0.07 seconds

然後檢視一下是否有test1表

hive> SHOW TABLES;

OK student1 student2 test1

Time taken: 0.042 seconds, Fetched: 12 row(s)

用命令刪test1表

hive> DROP TABLE test1;

OK

Time taken: 0.191 seconds

檢視test1表是否刪除

hive> SHOW TABLES;

OK student1 student2

Time taken: 0.027 seconds, Fetched: 11 row(s)

（4）修改表的結構，比如為表增加欄位

首先看一下student1表的結構

hive> DESC student1;

OK

sno int

sname string

age int

sex string

Time taken: 0.886 seconds, Fetched: 4 row(s)

為表student1增加兩個欄位

hive> ALTER TABLE student1 ADD COLUMNS > (address STRING,grade STRING);

OK

Time taken: 0.241 seconds

再檢視一下表的結構，看是否增加

hive> DESC student1;

OK

sno int

sname string

age int

sex string

address string

grade string

Time taken: 0.154 seconds, Fetched: 6 row(s)

（5）修改表名student1為student3

hive> ALTER TABLE student1 RENAME TO student3;

OK

Time taken: 0.172 seconds

檢視一下

hive> SHOW TABLES;

OK student2 student3

Time taken: 0.088 seconds, Fetched: 11 row(s)

下面我們再改回來

hive> ALTER TABLE student3 RENAME TO student1;

OK

Time taken: 0.153 seconds

檢視一下

hive> SHOW TABLES;

OK student1 student2

Time taken: 0.064 seconds, Fetched: 11 row(s)

（6）建立和已知表相同結構的表

hive> CREATE TABLE copy_student1 LIKE student1;

OK

Time taken: 1.109 seconds

檢視一下

hive> SHOW TABLES;

OK copy_student1 student1 student2

Time taken: 0.083 seconds, Fetched: 12 row(s)

2、加入匯入資料的方法

（資料裡可以包含重複記錄），只有匯入了資料，才能供後邊的查詢使用

（1）載入本地資料load

首先看一下表的結構

hive> DESC student1;

OK

sno int

sname string

age int

sex string

address string

grade string

Time taken: 1.018 seconds, Fetched: 6 row(s)

建立/home/hadoop/data目錄，並在該目錄下建立student1.txt檔案，新增如下內容

201501001	張三	22	男	北京	大三
201501003	李四	23	男	上海	大二
201501004	王娟	22	女	廣州	大三
201501010	周王	24	男	深圳	大四
201501011	李紅	23	女	北京	大三

載入資料到student1表中

hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/student1.txt' INTO TABLE student1; Loading data to table default.student1

Table default.student1 stats: [numFiles=1, numRows=0, totalSize=191, rawDataSize=0]

OK

Time taken: 0.766 seconds

檢視是否載入成功

hive> SELECT * FROM student1;
OK
201501001	張三	22	男	北京	大三
201501003	李四	23	男	上海	大二
201501004	王娟	22	女	廣州	大三
201501010	周王	24	男	深圳	大四
201501011	李紅	23	女	北京	大三

Time taken: 0.512 seconds, Fetched: 5 row(s)

（2）載入hdfs中的檔案

首先將檔案student1.txt上傳到hdfs檔案系統對應目錄上

[hadoop@hadoop01 hadoop]$ hadoop fs			‐put /home/hadoop/data/student1.txt /user/hive
16/05/16 17:15:43 WARN util.NativeCodeLoader: Unable to load					native‐hadoop library for your platform..
[hadoop@hadoop01 hadoop]$ hadoop fs			‐ls /user/hive
16/05/16 17:16:15 WARN util.NativeCodeLoader: Unable to load					native‐hadoop library for your platform..
Found 2 items
‐rw‐r‐‐r‐‐		3 hadoop supergroup	191 2016‐05‐19	03:27	/user/hive/student1.txt
drwxr‐xr‐x		‐ hadoop supergroup	0 2016‐05‐19	02:46	/user/hive/warehouse

載入hdfs中的檔案資料到copy_student1表中

hive> LOAD DATA INPATH '/user/hive/student1.txt' INTO TABLE copy_student1; Loading data to table default.copy_student1

Table default.copy_student1 stats: [numFiles=1, totalSize=191]

OK

Time taken: 1.354 seconds

檢視是否載入成功

hive> SELECT * FROM copy_student1;
OK
201501001	張三	22	男	北京	大三
201501003	李四	23	男	上海	大二
201501004	王娟	22	女	廣州	大三
201501010	周王	24	男	深圳	大四
201501011	李紅	23	女	北京	大三

Time taken: 0.44 seconds, Fetched: 5 row(s)

（3）表插入資料（單表插入、多表插入）

1）單表插入

首先建立一個表copy_student2,表結構和student1相同

hive> CREATE TABLE copy_student2 LIKE student1;

OK

Time taken: 0.586 seconds

檢視一下是否建立成功

hive> SHOW TABLES;

OK copy_student1 copy_student2 student1 student2

Time taken: 0.073 seconds, Fetched: 13 row(s)

看一下copy_student2表的表結構

hive> DESC copy_student2;

OK

sno int

sname string

age int

sex string

address string

grade string

Time taken: 0.121 seconds, Fetched: 6 row(s)

把表student1中的資料插入到copy_student2表中

hive> INSERT OVERWRITE TABLE copy_student2 SELECT * FROM student1;

檢視資料是否插入

hive> SELECT * FROM copy_student2;
OK
201501001	張三	22	男	北京	大三
201501003	李四	23	男	上海	大二
201501004	王娟	22	女	廣州	大三
201501010	周王	24	男	深圳	大四
201501011	李紅	23	女	北京	大三

Time taken: 0.107 seconds, Fetched: 5 row(s)

2）多表插入先建立兩個表

hive> CREATE TABLE copy_student3 LIKE student1;

OK

Time taken: 0.622 seconds

hive> CREATE TABLE copy_student4 LIKE student1;

OK

Time taken: 0.162 seconds

向多表插入資料

hive> FROM student1

INSERT OVERWRITE TABLE copy_student3

SELECT *

INSERT OVERWRITE TABLE copy_student4

SELECT *;

檢視結果

hive> SELECT * FROM copy_student3;
OK
201501001	張三	22	男	北京	大三
201501003	李四	23	男	上海	大二
201501004	王娟	22	女	廣州	大三
201501010	周王	24	男	深圳	大四
201501011	李紅	23	女	北京	大三
Time taken: 0.103 seconds, Fetched: 5 row(s)
hive> SELECT * FROM copy_student4;
OK
201501001	張三	22	男	北京	大三
201501003	李四	23	男	上海	大二
201501004	王娟	22	女	廣州	大三
201501010	周王	24	男	深圳	大四
201501011	李紅	23	女	北京	大三

Time taken: 0.071 seconds, Fetched: 5 row(s)

3、有關表的內容的查詢

（1）查表的所有內容

hive> SELECT * FROM student1;
OK
201501001	張三	22	男	北京	大三
201501003	李四	23	男	上海	大二
201501004	王娟	22	女	廣州	大三
201501010	周王	24	男	深圳	大四
201501011	李紅	23	女	北京	大三

Time taken: 1.201 seconds, Fetched: 5 row(s)

 

（2）查表的某個欄位的屬性

hive> SELECT sname FROM student1;

OK

張三

李四

王娟

周王

李紅

Time taken: 1.22 seconds, Fetched: 5 row(s)

（3）where條件查詢

hive> SELECT * FROM student1 WHERE sno>201501004 AND address="北京";

OK

201501011 李紅 23 女 北京 大三 Time taken: 0.873 seconds, Fetched: 1 row(s)

（4）all和distinct的區別（這就要求表中要有重複的記錄，或者某個欄位要有重複的資料）

hive> SELECT ALL age,grade FROM student1;

OK

22 大三

23 大二

22	大三
24	大四
23	大三

Time taken: 0.448 seconds, Fetched: 5 row(s)

hive> SELECT age,grade FROM student1;

OK

22 大三

23 大二

22	大三
24	大四
23	大三

Time taken: 0.072 seconds, Fetched: 5 row(s)

hive> SELECT DISTINCT age,grade FROM student1;

OK

22 大三

23 大三

23 大二

24 大四

Time taken: 127.397 seconds, Fetched: 4 row(s)

hive> SELECT DISTINCT age FROM student1;

OK 22 23 24

Time taken: 106.21 seconds, Fetched: 3 row(s)

（5）limit限制查詢

hive> SELECT * FROM student1 LIMIT 4; 

OK

 201501001 張三 22 男北京大三 

201501003 李四 23 男上海大二 

201501004 王娟 22 女廣州大三 

201501010 周王 24 男深圳大四 

Time taken: 0.253 seconds, Fetched: 4 row(s)

(6) GROUP BY 分組查詢

group by 分組查詢在資料統計時比較常用，接下來講解 group by 的使用。

1) 建立一個表 group_test，表的內容如下。

hive> create table group_test(uid STRING,gender STRING,ip STRING) row format delimited fields terminat

向 group_test 表中匯入資料。

hive> LOAD DATA LOCAL INPATH '/home/hadoop/hadoop/user.txt' INTO TABLE group_test;

2) 計算表的行數命令如下。

hive> select count(*) from group_test;

3) 根據性別計算去重使用者數。

首先建立一個表 group_gender_sum

hive> create table group_gender_sum(gender STRING,sum INT);

將表 group_test 去重後的資料匯入表 group_gender_sum。

hive> insert overwrite table group_gender_sum select group_test.gender,count(distinct group_test.uid)

同時可以做多個聚合操作，但是不能有兩個聚合操作有不同的 distinct 列。下面正確合法的聚合操作語句。

首先建立一個表 group_gender_agg

hive> create table group_gender_agg(gender STRING,sum1 INT,sum2 INT,sum3 INT);

將表 group_test 聚合後的資料插入表 group_gender_agg。

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),

但是，不允許在同一個查詢內有多個 distinct 表示式。下面的查詢是不允許的。

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),

這條查詢語句是不合法的，因為 distinct group_test.uid 和 distinct group_test.ip 操作了

uid 和 ip 兩個不同的列。

(7) ORDER BY 排序查詢

ORDER BY 會對輸入做全域性排序，因此只有一個 Reduce（多個 Reduce 無法保證全域性有序）會導致當輸入規模較大時，需要較長的計算時間。使用 ORDER BY 查詢的時候，為了優

化查詢的速度，使用 hive.mapred.mode 屬性。

hive.mapred.mode = nonstrict;(default value/預設值) hive.mapred.mode=strict;

與資料庫中 ORDER BY 的區別在於，在 hive.mapred.mode=strict 模式下必須指定limit ，

否則執行會報錯。

hive> set hive.mapred.mode=strict;

hive> select * from group_test order by uid limit 5; Total jobs = 1

.............

Total MapReduce CPU Time Spent: 4 seconds 340 msec

OK
01	male	192.168.1.2
01	male	192.168.1.32
01	male	192.168.1.26
01	male	192.168.1.22
02	female	192.168.1.3

Time taken: 58.04 seconds, Fetched: 5 row(s)

(8) SORT BY 查詢

sort by 不受 hive.mapred.mode 的值是否為 strict 和 nostrict 的影響。sort by 的資料只

能保證在同一個 Reduce 中的資料可以按指定欄位排序。

使用 sort by 可以指定執行的 Reduce 個數（set mapred.reduce.tasks=< number>）這

樣可以輸出更多的資料。對輸出的資料再執行歸併排序，即可以得到全部結果。

hive> set hive.mapred.mode=strict;

hive> select * from group_test sort by uid ;

Total MapReduce CPU Time Spent: 4 seconds 450 msec

OK
01	male	192.168.1.2
01	male	192.168.1.32
01	male	192.168.1.26
01	male	192.168.1.22
02	female	192.168.1.3
03	male	192.168.1.23
03	male	192.168.1.5
04	male	192.168.1.9
05	male	192.168.1.8
05	male	192.168.1.29
06	female	192.168.1.201
06	female	192.168.1.52
06	female	192.168.1.7
07	female	192.168.1.11
08	female	192.168.1.21
08	female	192.168.1.62
08	female	192.168.1.88
08	female	192.168.1.42

Time taken: 77.875 seconds, Fetched: 18 row(s)

(9) DISTRIBUTE BY 排序查詢

按照指定的欄位對資料劃分到不同的輸出 Reduce 檔案中，操作如下。

hive> insert overwrite local directory '/home/hadoop/hadoop/test' select * from group_test distribute by

此方法根據 gender 的長度劃分到不同的 Reduce 中，最終輸出到不同的檔案中。length 是內建函式，也可以指定其它的函式或者使用自定義函式。

hive> insert overwrite local directory '/home/hadoop/hadoop/test' select * from group_test order by gende

order by gender 與 distribute by length(gender) 不能共用。

(10) CLUSTER BY 查詢

cluster by 除了具有 distribute by 的功能外還兼具 sort by 的功能。