Hive基本操作

01.Hive是什么

1. Hive介紹

• Hive是基于Hadoop的一個數據倉庫工具，可以將結構化的數據文件映射為一張數據庫表，并提供類SQL查詢功能。

• Hive是SQL解析引擎，它將SQL語句轉譯成M/R Job然后在Hadoop執行。

2. Hive架構

• 用戶接口，包括 CLI，JDBC/ODBC，WebUI

• 元數據存儲，通常是存儲在關系數據庫如 mysql, derby 中

• 解釋器、編譯器、優化器、執行器

• Hadoop：用 HDFS 進行存儲，利用 MapReduce 進行計算

Ps:Hive的元數據并不存放在hdfs上，而是存儲在數據庫中(metastore)，目前只支持 MySQL、derby。Hive 中的元數據包括表的名字，表的列和分區及其屬性，表的屬性（是否為外部表等），表的數據所在目錄等。

元數據就是描述數據的數據，而Hive的數據存儲在Hadoop HDFS
數據還是原來的文本數據，但是現在有了個目錄規劃。

3. Hive與Hadoop的關系

Hive利用HDFS存儲數據，利用MapReduce查詢數據。

4. Hive安裝部署

Hive只是一個工具，不需要集群配置。

export HIVE_HOME=/usr/local/hive-2.0.1
export PATH=PATH:PATH:HIVE_HOME/bin
配置MySql，如果不進行配置，默認使用derby數據庫，但是不好用，在哪個地方執行./hive命令，哪兒就會創建一個metastore_db
MySQL安裝到其中某一個節點上即可。

5. Hive的thrift服務

可以安裝在某一個節點，并發布成標準服務，在其他節點使用beeline方法。

啟動方式，（假如是在master上）：
啟動為前臺服務：bin/hiveserver2
啟動為后臺：nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err &

連接方法：
hive/bin/beeline 回車，進入beeline的命令界面
輸入命令連接hiveserver2
beeline> !connect jdbc:hive2://master:10000
beeline> !connect jdbc:hive2://localhost:10000
（master是hiveserver2所啟動的那臺主機名，端口默認是10000）

02.Hive的基本操作

1. 創建數據庫

hive > create database tabletest;

• 建立一個新數據庫，就會在HDFS的/user/hive/warehouse/中生成一個tabletest.db文件夾。

• 如果不創建新數據庫，不使用hive>use <數據庫名>，系統默認的數據庫。可以顯式使用hive> use default;默認/user/hive/warehouse/中建表

2. 創建表

語法：

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 t_order(id int,name string,rongliang string,price double) 
row format delimited fields terminated by '\t'；

創建了一個t_order表，對應在Mysql的元數據中TBLS表會增加表的信息，和列的信息，如下：

同時，會在HDFS的中的tabletest.db文件夾中增加一個t_order文件夾。所有的 Table 數據（不包括 External Table）都保存在這個目錄中。

3. 導入數據

可以直接使用HDFS上傳文件到t_order文件夾中，或者使用Hive的load命令。

load data local inpath '/home/hadoop/ip.txt' [OVERWRITE] into table tab_ext;

作用和上傳本地Linux文件到HDFS系統一樣；但需要注意，如果inpath 后面路徑是HDFS路徑，則將是將其刪除后，剪切到目標文件夾，不好！

4. external表

EXTERNAL關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際數據的路徑（LOCATION），Hive 創建內部表時，會將數據移動到數據倉庫指向的路徑；若創建外部表，僅記錄數據所在的路徑，不對數據的位置做任何改變。
為了避免源文件丟失的問題，可以建立external表，數據源可以在任意位置。

CREATE EXTERNAL TABLE tab_ip_ext(id int, name string,
     ip STRING,
     country STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
 STORED AS TEXTFILE
 LOCATION '/external/hive';

在創建表的時候，就指定了HDFS文件路徑，因此，源文件上傳到/external/hive/文件夾即可。

外部表刪除時，只刪除元數據信息，存儲在HDFS中的數據沒有被刪除。

5. Partition表（分區表）

作用：如果文件很大，用分區表可以快過濾出按分區字段劃分的數據。
t_order中有兩個分區part1和part2.
實際就是在HDFS中的t_order文件夾下面再建立兩個文件夾，每個文件名就是part1和part2。

create table t_order(id int,name string,rongliang string,price double)
partitioned by (part_flag string)row format delimited fields terminated by '\t';

插入數據：

load data local inpath '/home/hadoop/ip.txt' overwrite into table t_orderpartition(part_flag='part1');

就會把ip.txt文件上傳到/t_order/part_flag='part1'/文件夾中。

查看分區表中的數據：

select * from t_order  where part_flag='part1';

查詢時，分區字段也會顯示，但是實際表中是沒有這個字段的，是偽字段。

hive中的分區 就是再多建一個目錄， 優點：便于統計，效率更高，縮小數據集。

相關命令：

• SHOW TABLES; # 查看所有的表

• SHOW TABLES 'TMP'; #支持模糊查詢

• SHOW PARTITIONS TMP_TABLE; #查看表有哪些分區

• DESCRIBE TMP_TABLE; #查看表結構

6. 分桶表

Hive里的分桶=MapReduce中的分區，而Hive中的分區只是將數據分到了不同的文件夾。

1. 創建分桶表

create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)
clustered by(Sno) 
sorted by(Sno DESC)into 4 bucketsrow format delimitedfields terminated by ',';

含義：根據Sno字段分桶，每個桶按照Sno字段局部有序，4個桶。建桶的時候不會的數據做處理，只是要求插入的數據是被分好桶的。

2. 分桶表內插入數據

一般不適用load數據進入分桶表，因為load進入的數據不會自動按照分桶規則分為四個小文件。所以，一般使用select查詢向分桶表插入文件。

設置變量,設置分桶為true, 設置reduce數量是分桶的數量個數

set hive.enforce.bucketing = true;set mapreduce.job.reduces=4;insert overwrite table student_buckselect * from student cluster by(Sno);insert overwrite table stu_buckselect * from student distribute by(Sno) sort by(Sno asc);

其中，可以用distribute by(sno) sort by(sno asc)替代cluster by(Sno)，這是等價的。cluster by(Sno) = 分桶+排序
先分發，再局部排序。區別是distribute更加靈活，可以根據一個字段分區，另外字段排序。
第二個子查詢的輸出了4個文件作為主查詢的輸入。

3. 分桶表的原理與作用

原理：
Hive是針對某一列進行桶的組織。Hive采用對列值哈希，然后除以桶的個數求余的方式決定該條記錄存放在哪個桶當中。（原理和MapReduce的getPartition方法相同）

作用：
（1） 最大的作用是用來提高join操作的效率；
前提是兩個都是分桶表且分桶數量相同或者倍數關系？

思考這個問題：
select a.id,a.name,b.addr from a join b on a.id = b.id;
如果a表和b表已經是分桶表，而且分桶的字段是id字段
做這個join操作時，還需要全表做笛卡爾積嗎？
對于JOIN操作兩個表有一個相同的列，如果對這兩個表都進行了桶操作。那么將保存相同列值的桶進行JOIN操作就可以，可以大大較少JOIN的數據量。

（2）取樣（sampling）更高效。在處理大規模數據集時，在開發和修改查詢的階段，如果能在數據集的一小部分數據上試運行查詢，會帶來很多方便。

7. insert語句

Hive一條一條的insert太慢。
但是可以批量的insert.實際就是想文件夾中追加文件。

create table tab_ip_like like tab_ip;insert overwrite table tab_ip_likeselect * from tab_ip;

向tab_ip_like中追加文件

8. 保存select查詢結果的幾種方式

1、將查詢結果保存到一張新的hive表中
create table t_tmp
as
select * from t_p;

2、將查詢結果保存到一張已經存在的hive表中
insert into table t_tmp
select * from t_p;

3、將查詢結果保存到指定的文件目錄（可以是本地，也可以是HDFS）
insert overwrite local directory '/home/hadoop/test'
select * from t_p;

插入HDFS
insert overwrite directory '/aaa/test'
select * from t_p;

9. 查看、刪除

語法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference
[WHERE where_condition] 
[GROUP BY col_list [HAVING condition]] 
[CLUSTER BY col_list 
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] 
] 
[LIMIT number]

注：

1. CLUSTER BY字段含義：根據這個字段進行分區，需要注意設置reduce_num數量。

2. order by 會對輸入做全局排序，因此只有一個reducer，會導致當輸入規模較大時，需要較長的計算時間。

3. sort by不是全局排序，其在數據進入reducer前完成排序。因此，如果用sort by進行排序，并且設置mapred.reduce.tasks>1，則sort by只保證每個reducer的輸出有序，不保證全局有序。

4. distribute by(字段)根據指定的字段將數據分到不同的reducer，且分發算法是hash散列。

5. Cluster by(字段) 除了具有Distribute by的功能外，還會對該字段進行排序。

因此，如果分桶和sort字段是同一個時，此時，cluster by = distribute by + sort by

select * from inner_tableselect count(*) from inner_table

刪除表時，元數據與數據都會被刪除

drop table inner_table

10. Hive中的join

LEFT JOIN，RIGHT JOIN， FULL OUTER JOIN ,inner join, left semi join

準備數據
1,a
2,b
3,c
4,d
7,y
8,u

2,bb
3,cc
7,yy
9,pp

建表：

create table a(id int,name string)row format delimited fields terminated by ',';create table b(id int,name string)row format delimited fields terminated by ',';

導入數據：

load data local inpath '/home/hadoop/a.txt' into table a;load data local inpath '/home/hadoop/b.txt' into table b;

1. inner join

select * from a inner join b on a.id=b.id;

+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
+-------+---------+-------+---------+--+
就是求交集。

2. inner join

select * from a left join b on a.id=b.id;

+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
+-------+---------+-------+---------+--+
左邊沒有找到連接的置空。

3. right join

select * from a right join b on a.id=b.id;

4. full outer join

select * from a full outer join b on a.id=b.id;

+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+
兩邊數據都顯示。

5. left semi join

select * from a left semi join b on a.id = b.id;

+-------+---------+--+
| a.id | a.name |
+-------+---------+--+
| 2 | b |
| 3 | c |
| 7 | y |
+-------+---------+--+
只返回左邊一半，即a的東西，效率高一點。

11. 創建臨時表

可以存儲中間結果。
CREATE TABLE tab_ip_ctas
AS
SELECT id new_id, name new_name, ip new_ip,country new_country
FROM tab_ip_ext
SORT BY new_id;

03. Hive 的自定義函數和Transform

當Hive提供的內置函數無法滿足你的業務處理需要時，此時就可以考慮使用用戶自定義函數（UDF：user-defined function）。

1. 自定義函數類別

UDF 作用于單個數據行，產生一個數據行作為輸出。（數學函數，字符串函數）
UDAF（用戶定義聚集函數）：接收多個輸入數據行，并產生一個輸出數據行。（count，max）

2.UDF開發實例

1、先開發一個Java類，繼承UDF，并重載evaluate方法

public class ToLowerCase extends UDF {    // 必須是public
    public String evaluate(String field) {        String result = field.toLowerCase();        return result;
    }
}

2、打成jar包上傳到服務器

3、將jar包添加到hive的classpath

hive>add JAR /home/hadoop/udf.jar;

4、創建臨時函數與開發好的Java class關聯

Hive>create temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';

5、即可在hql中使用自定義的函數strip

select id,tolowercase(name) from t_p;

3.Transform實現

Hive的 TRANSFORM 關鍵字提供了在SQL中調用自寫腳本的功能
適合實現Hive中沒有的功能又不想寫UDF的情況。

1、先加載rating.json文件到hive的一個原始表 rat_json

//{"movie":"1721","rate":"3","timeStamp":"965440048","uid":"5114"}
create table rat_json(line string) row format delimited;load data local inpath '/opt/rating.json' into table rat_json

2、需要解析json數據成四個字段，插入一張新的表 t_rating(用于存放處理的數據，需要有四個字段)

create table t_rating(movieid string,rate int,timestring string,uid string)row format delimited fields terminated by '\t';insert overwrite table t_ratingselect get_json_object(line,'$.movie') as moive,get_json_object(line,'$.rate') as rate  from rat_json;
//get_json_object是內置jason函數，也可以自定義UDF函數實現

3、使用transform+Python的方式去轉換unixtime為weekday

（1）先編輯一個python腳本文件
python代碼：

vi weekday_mapper.py
#!/bin/pythonimport sysimport datetimefor line in sys.stdin:
  line = line.strip()
  movieid, rating, unixtime,userid = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()  print '\t'.join([movieid, rating, str(weekday),userid])

（2）將文件加入hive的classpath：

hive>add FILE /home/hadoop/weekday_mapper.py;
hive>create TABLE u_data_new asSELECT
  TRANSFORM (movieid, rate, timestring,uid)  USING 'python weekday_mapper.py'
  AS (movieid, rate, weekday,uid)FROM t_rating;select distinct(weekday) from u_data_new limit 10;

將查詢處理過的新數據插入u_data_new文件中。