1.Hive入門

1.1什么是Hive

Hive：由Facebook開源用于解決海量結構化日志的資料統計，

Hive是基于Hadoop的一個資料倉庫工具，可以將結構化的資料檔案映射為一張表，并提供類SQL查詢功能，

本質是：將HQL轉化成MapReduce程式

1）Hive處理的資料存盤在HDFS

2）Hive分析資料底層的實作是MapReduce

3）執行程式運行在Yarn上

1.2 Hive的優缺點

1.2.1 優點

1. 操作介面采用類SQL語法，提供快速開發的能力（簡單、容易上手），

2. 避免了去寫MapReduce，減少開發人員的學習成本，

3. Hive的執行延遲比較高，因此Hive常用于資料分析，對實時性要求不高的場合，

4)Hive優勢在于處理大資料，對于處理小資料沒有優勢，因為Hive的執行延遲比較高，

5. Hive支持用戶自定義函式，用戶可以根據自己的需求來實作自己的函式，

1.2.2 缺點

1．Hive的HQL表達能力有限

（1）迭代式演算法無法表達

（2）資料挖掘方面不擅長

2．Hive的效率比較低

（1）Hive自動生成的MapReduce作業，通常情況下不夠智能化

（2）Hive調優比較困難，粒度較粗

1.3Hive架構原理

1．用戶介面：Client

CLI（hive shell）、JDBC/ODBC(java訪問hive)、WEBUI（瀏覽器訪問hive）

2．元資料：Metastore

元資料包括：表名、表所屬的數據庫（默認是default）、表的擁有者、列/磁區欄位、表的型別（是否是外部表）、表的資料所在目錄等；

默認存盤在自帶的derby資料庫中，推薦使用MySQL存盤Metastore

3．Hadoop

使用HDFS進行存盤，使用MapReduce進行計算，

4．驅動器：Driver

（1）決議器（SQL Parser）：將SQL字串轉換成抽象語法樹AST，這一步一般都用第三方工具庫完成，比如antlr；對AST進行語法分析，比如表是否存在、欄位是否存在、SQL語意是否有誤，

（2）編譯器（Physical Plan）：將AST編譯生成邏輯執行計劃，

（3）優化器（Query Optimizer）：對邏輯執行計劃進行優化，

（4）執行器（Execution）：把邏輯執行計劃轉換成可以運行的物理計劃，對于Hive來說，就是MR/Spark，

Hive通過給用戶提供的一系列互動介面，接收到用戶的指令(SQL)，使用自己的Driver，結合元資料(MetaStore)，將這些指令翻譯成MapReduce，提交到Hadoop中執行，最后，將執行回傳的結果輸出到用戶互動介面，

2.Hive安裝

2.1Hive安裝

《Hive安裝教程》

2.2HiveJDBC訪問

2.2.1啟動hiveserver2服務

hiveserver2

2.2.2連接hiveserver2服務

新建命令視窗，輸入以下命令

beeline -u "jdbc:hive2://localhost:10000"

出現該圖代表成功連接

2.2.3注意

這里報錯通常是由于權限不夠，只需要對tmp和opt檔案夾賦權即可

hadoop fs -chmod -R 777 /tmp

hadoop fs -chmod -R 777 /opt

2.3Hive常用互動命令

1．“-e”不進入hive的互動視窗執行sql陳述句

hive -e "查詢陳述句"

2．“-f”執行腳本中sql陳述句

hive -f sql檔案路徑/sql檔案名稱

執行檔案中的sql陳述句并將結果寫入檔案中

hive -f sql檔案路徑/sql檔案名稱  > 保存結果的路徑

2.4Hive其他命令操作

1.退出hive視窗

exit；

quit；

2.在hive cli命令視窗中如何查看hdfs檔案系統

dfs -ls /;

3．在hive cli命令視窗中如何查看本地檔案系統

!ls /opt;

2.5Hive常見屬性配置

2.5.1資料倉庫位置配置

修改hive-site.xml檔案的value

<property>
<name>hive.metastore.warehouse.dir</name>
<value>/opt/hive/warehouse</value>
</property>

2.5.2查詢后資訊顯示配置

在hive-site.xml檔案中添加如下配置資訊，就可以實作顯示當前資料庫，以及查詢表的頭資訊配置，

<property>
	<name>hive.cli.print.header</name>
	<value>true</value>
</property>

<property>
	<name>hive.cli.print.current.db</name>
	<value>true</value>
</property>

重新啟動hive，對比配置前后差異，

2.5.3運行日志資訊配置

• Hive的log默認存放在/tmp/root/hive.log目錄下（root為當前用戶名）

• 修改hive的log存放日志到/opt/hive/logs

1.修改/opt/hive/conf/hive-log4j.properties.template檔案名稱為hive-log4j.properties

mv hive-log4j.properties.template hive-log4j.properties

2.在hive-log4j.properties檔案中修改log存放位置

hive.log.dir=/opt/hive/logs

重啟hive

3.Hive資料型別

3.1基本資料型別

Hive資料型別	Java資料型別	長度	例子
TINYINT	byte	1byte有符號整數	20
SMALINT	short	2byte有符號整數	20
INT	int	4byte有符號整數	20
BIGINT	long	8byte有符號整數	20
BOOLEAN	boolean	布爾型別，true或者false	TRUE FALSE
FLOAT	float	單精度浮點數	3.14159
DOUBLE	double	雙精度浮點數	3.14159
STRING	string	字符系列，可以指定字符集，可以使用單引號或者雙引號，	‘now is the time’ “for all good men”
TIMESTAMP		時間型別	'2013-01-31 00:13:00.345’
BINARY		位元組陣列（二進制）	1010

紅標為常用的資料型別；

對于Hive的String型別相當于資料庫的varchar型別，該型別是一個可變的字串，不過它不能宣告其中最多能存盤多少個字符，

3.2集合資料型別

資料型別	描述	語法示例
STRUCT	相當于java語言當中沒有方法的物件，只有屬性，例如，如果某個列的資料型別是STRUCT{first STRING, last STRING},那么第1個元素可以通過欄位.first來參考，	struct()
MAP	MAP是一組鍵-值對元組集合，使用陣串列示法可以訪問資料，例如，如果某個列的資料型別是MAP，其中鍵->值對是’first’->’John’和’last’->’Doe’，那么可以通過欄位名[‘last’]獲取最后一個元素	map()
ARRAY	陣列是一組具有相同型別和名稱的變數的集合，這些變數稱為陣列的元素，每個陣列元素都有一個編號，編號從零開始，例如，陣列值為[‘John’, ‘Doe’]，那么第2個元素可以通過陣列名[1]進行參考，	Array()

3.3型別轉化

可以使用CAST操作顯示進行資料型別轉換

例如CAST(‘1’ AS INT)將把字串’1’ 轉換成整數1；如果強制型別轉換失敗，如執行CAST(‘X’ AS INT)，運算式回傳空值 NULL，

4.DDL資料定義

4.1創建資料庫

• 創建一個資料庫，資料庫在HDFS上的默認存盤路徑是/opt/hive/warehouse/*.db

create database hivetest;

• 避免要創建的資料庫已經存在錯誤，增加if not exists判斷，（標準寫法）

create database if not exists hivetest;

• 創建一個資料庫，指定資料庫在HDFS上存放的位置

create database if not exists hivetest location 'hdfs路徑';

4.2查詢資料庫

• 顯示資料庫

show databases;

? 過濾顯示查詢的資料庫

show databases like 'hivetest*';

• 查看資料庫詳情

desc database hivetest;

• 切換當前資料庫

use 目標資料庫名稱;

4.3洗掉資料庫

• 洗掉空資料庫

drop database 庫名;

• 如果洗掉的資料庫不存在，最好采用 if exists判斷資料庫是否存在

drop database if exists 庫名;

• 如果資料庫不為空，可以采用cascade命令，強制洗掉

drop database 庫名 cascade;

4.4創建表

• 建表語法

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]

• 欄位解釋說明

（1）CREATE TABLE 創建一個指定名字的表，如果相同名字的表已經存在，則拋出例外；用戶可以用 IF NOT EXISTS 選項來忽略這個例外，

（2）EXTERNAL關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際資料的路徑（LOCATION），Hive創建內部表時，會將資料移動到資料倉庫指向的路徑；若創建外部表，僅記錄資料所在的路徑，不對資料的位置做任何改變，在洗掉表的時候，內部表的元資料和資料會被一起洗掉，而外部表只洗掉元資料，不洗掉資料，

（3）COMMENT：為表和列添加注釋，

（4）PARTITIONED BY創建磁區表

（5）CLUSTERED BY創建分桶表

（6）SORTED BY不常用

（7）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確定表的具體的列的資料，

SerDe是Serialize/Deserilize的簡稱，目的是用于序列化和反序列化，

（8）STORED AS指定存盤檔案型別

常用的存盤檔案型別：SEQUENCEFILE（二進制序列檔案）、TEXTFILE（文本）、RCFILE（列式存盤格式檔案）

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

（9）LOCATION ：指定表在HDFS上的存盤位置，

（10）LIKE允許用戶復制現有的表結構，但是不復制資料，

4.4.1內部表

默認創建的表都是所謂的管理表，有時也被稱為內部表，因為這種表，Hive會（或多或少地）控制著資料的生命周期，Hive默認情況下會將這些表的資料存盤在由配置項hive.metastore.warehouse.dir(例如，/opt/hive/warehouse)所定義的目錄的子目錄下， 當我們洗掉一個管理表時，Hive也會洗掉這個表中資料，管理表不適合和其他工具共享資料，

• 普通創建表

create table if not exists student2(
id int, name string
)
row format delimited fields terminated by '\t';

• 根據查詢結果創建表（查詢的結果會添加到新創建的表中）

create table if not exists student3 as select id, name from student;

• 根據已經存在的表結構創建表

create table if not exists student4 like student;

• 查詢表的型別

desc formatted student2;

4.4.2外部表

因為表是外部表，所以Hive并非認為其完全擁有這份資料，洗掉該表并不會洗掉掉這份資料，不過描述表的元資料資訊會被洗掉掉，

• 管理表和外部表的使用場景

每天將收集到的網站日志定期流入HDFS文本檔案，在外部表（原始日志表）的基礎上做大量的統計分析，用到的中間表、結果表使用內部表存盤，資料通過SELECT+INSERT進入內部表，

案例詳解

分別創建employee外部表，并向表中匯入資料，

Michael|Montreal,Toronto|Male,30|DB:80|Product:DeveloperLead
Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Shelley|New York|Female,27|Python:80|Test:Lead,COE:Architect
Lucy|Vancouver|Female,57|Sales:89|Sales:Lead

• 建表陳述句

創建員工表

create external table if not exists employee(
name string,
address array<string>,
personalInfo array<string>,
technol map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

向外部表中匯入資料

load data local inpath '/root/employee.txt' into table employee;

查詢結果

select * from employee;

4.4.3管理表與外部表的互相轉換

• 修改內部表student2為外部表

alter table student2 set tblproperties('EXTERNAL'='TRUE');

• 修改外部表student2為內部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')為固定寫法，區分大小寫！

4.5磁區表（partition）

磁區表實際上就是對應一個HDFS檔案系統上的獨立的檔案夾，該檔案夾下是該磁區所有的資料檔案，Hive中的磁區就是分目錄，把一個大的資料集根據業務需要分割成小的資料集，在查詢時通過WHERE子句中的運算式選擇查詢所需要的指定的磁區，這樣的查詢效率會提高很多，

4.5.1磁區表基本操作

資料

10,ACCOUNTING,NEW YORK
10,ACCOUNTING,NEW YORK
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
20,RESEARCH,DALLAS
20,RESEARCH,DALLAS
30,SALES,CHICAGO
30,SALES,CHICAGO

1．引入磁區表（需要根據日期對日志進行管理）

/opt/hive/warehouse/log_partition/20170702/20170702.log
/opt/hive/warehouse/log_partition/20170703/20170703.log
/opt/hive/warehouse/log_partition/20170704/20170704.log

2．創建磁區表語法

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by ',';

3．加載資料到磁區表中

load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201707’);
load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201708’);
load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201709’);

4．查詢磁區表中資料

單磁區查詢

select * from dept_partition where month='201709';

多磁區聯合查詢

select * from dept_partition where month='201709'
union
select * from dept_partition where month='201708'
union
select * from dept_partition where month='201707';

注意

Hive 1.2.0之前的版本僅支持UNION ALL，其中重復的行不會被洗掉，

Hive 1.2.0和更高版本中，UNION的默認行為是從結果中洗掉重復的行，

5．增加磁區

alter table dept_partition add partition(month='201706') ;

alter table dept_partition add partition(month='201705') ,partition(month='201704');

6．洗掉磁區

alter table dept_partition drop partition (month='201704');

alter table dept_partition drop partition (month='201705'), partition (month='201706')

7．查看磁區表有多少磁區

show partitions dept_partition;

8．查看磁區表結構

desc formatted dept_partition;

4.6修改表

4.6.1重命名表

• 語法

ALTER TABLE table_name RENAME TO new_table_name

• 實體

alter table dept_partition2 rename to dept_partition3;

4.6.2增加/修改/替換列資訊

• 語法

更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替換列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

注：ADD是代表新增一欄位，欄位位置在所有列后面(partition列前)，REPLACE則是表示替換表中所有欄位，

• 案例

添加列

alter table dept_partition add columns(deptdesc string);

更新列

alter table dept_partition change column deptdesc desc int;

替換列

alter table dept_partition replace columns(deptno string, dname string, loc string);

4.6.3洗掉表

drop table dept_partition;

注意：外部表不能簡單的通過這個命令洗掉，這個命令只能洗掉外部表的元資料，沒有辦法洗掉hdfs上面的資料，如果需要將外部表徹底洗掉，有以下方法：

• 方案一：轉換為內部表再洗掉

ALTER TABLE xxx SET TBLPROPERTIES('EXTERNAL'='False');

drop table xxx;

• 方案二：洗掉元資料，然后使用hdfs洗掉資料

5.DML資料操作

5.1 資料匯入

5.1.1 向表中裝載資料（Load）

• 語法

hive> load data [local] inpath '路徑' [overwrite] into table 表名 [partition (partcol1=val1,…)];

（1）load data:表示加載資料

（2）local:表示從本地加載資料到hive表；否則從HDFS加載資料到hive表

（3）inpath:表示加載資料的路徑

（4）overwrite:表示覆寫表中已有資料，否則表示追加

（5）into table:表示加載到哪張表

（6）表名:表示具體的表

（7）partition:表示上傳到指定磁區

5.1.2 通過查詢陳述句向表中插入資料（Insert）

• 案例

基本插入

insert into table  student partition(month='201709') values(1,'wangwu');

insert overwrite table student partition(month='201708') select id, name from student where month='201709';

多插入

from dept_partition
              insert overwrite table dept_partition partition(month='201707')
              select deptno,dname,loc where month='201709'
              insert overwrite table dept_partition partition(month='201706')
              select deptno,dname,loc  where month='201709';

5.1.3 查詢陳述句中創建表并加載資料（As Select）

根據查詢結果創建表（查詢的結果會添加到新創建的表中）

create table if not exists student3 as select id, name from student;

5.1.4 創建表時通過Location指定加載資料路徑

創建表，并指定在hdfs上的位置

create table if not exists student5(
id int, name string)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';

上傳資料到hdfs上

dfs -put /opt/datas/student.txt /opt/hive/warehouse/student5;

5.1.5 Import資料到指定Hive表中

注意：先用export匯出后，再將資料匯入，

import table student2 partition(month='201709') from '/opt/hive/warehouse/export/student';

5.2 資料匯出

5.2.1 Insert匯出

1．將查詢的結果匯出到本地

insert overwrite local directory '/opt/datas' select * from dept_partition;

2．將查詢的結果格式化匯出到本地

insert overwrite local directory '/opt/datas/dept1'
row format delimited
fields terminated by '|'
select * from dept_partition;

3．將查詢的結果匯出到HDFS上(沒有local)

insert overwrite directory '/opt/datas/dept'
row format delimited
fields terminated by '|'
select * from dept_partition;

5.2.2 Hadoop命令匯出到本地

dfs -get /opt/hive/warehouse/employee/employee.txt /opt/datas/dept2/dept.txt;

5.2.3 Hive Shell 命令匯出

基本語法：（hive -f/-e 執行陳述句或者腳本 > file）

hive -e 'select * from hivetest.dept_partition;' > /opt/datas/dept3/dept.txt;

注意：需要在shell視窗執行，需要庫名.表名，需要本地檔案夾存在，

5.2.4 Export匯出到HDFS上

export table hivetest.dept_partition to '/opt/datas/dept2';

5.2.5 Sqoop匯出

后續

5.3 清除表中資料（Truncate）

注意：Truncate只能洗掉管理表，不能洗掉外部表中資料

truncate table student;

6.查詢

查詢陳述句語法：

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available
 starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

6.1 基本查詢（Select…From）

6.1.1 全表和特定列查詢

select * from emp;

select empno, ename from emp;

注意：

（1）SQL 語言大小寫不敏感，

（2）SQL 可以寫在一行或者多行

（3）關鍵字不能被縮寫也不能分行

（4）各子句一般要分行寫，

（5）使用縮進提高陳述句的可讀性，

6.1.2 列別名

1．重命名一個列

2．便于計算

3．緊跟列名，可以在列名和別名之間加入關鍵字‘AS’

select ename AS name, deptno dn from emp;

6.1.3 算術運算子

運算子	描述
A+B	A和B 相加
A-B	A減去B
A*B	A和B 相乘
A/B	A除以B
A%B	A對B取余
A&B	A和B按位取與
A|B	A和B按位取或
A^B	A和B按位取異或
~A	A按位取反

6.1.4 常用函式

1．求總數（count）

2．求最大值（max）

3．求最小值（min）

4．求總和（sum）

5．求平均值（avg）

select count(*) cnt from emp;

6.1.5 Limit陳述句

典型的查詢會回傳多行資料，LIMIT子句用于限制回傳的行數，

select * from emp limit 5;

6.2 Where陳述句

1．使用WHERE子句，將不滿足條件的行過濾掉

2．WHERE子句緊隨FROM子句

案例：查詢出薪水大于1000的所有員工

select * from emp where sal >1000;

6.2.1 比較運算子（Between/In/ Is Null）

下面表中描述了謂詞運算子，這些運算子同樣可以用于JOIN…ON和HAVING陳述句中，

運算子	支持的資料型別	描述
A=B	基本資料型別	如果A等于B則回傳TRUE，反之回傳FALSE
A<=>B	基本資料型別	如果A和B都為NULL，則回傳TRUE，其他的和等號（=）運算子的結果一致，如果任一為NULL則結果為NULL
A<>B, A!=B	基本資料型別	A或者B為NULL則回傳NULL；如果A不等于B，則回傳TRUE，反之回傳FALSE
A<B	基本資料型別	A或者B為NULL，則回傳NULL；如果A小于B，則回傳TRUE，反之回傳FALSE
A<=B	基本資料型別	A或者B為NULL，則回傳NULL；如果A小于等于B，則回傳TRUE，反之回傳FALSE
A>B	基本資料型別	A或者B為NULL，則回傳NULL；如果A大于B，則回傳TRUE，反之回傳FALSE
A>=B	基本資料型別	A或者B為NULL，則回傳NULL；如果A大于等于B，則回傳TRUE，反之回傳FALSE
A [NOT] BETWEEN B AND C	基本資料型別	如果A，B或者C任一為NULL，則結果為NULL，如果A的值大于等于B而且小于或等于C，則結果為TRUE，反之為FALSE，如果使用NOT關鍵字則可達到相反的效果，
A IS NULL	所有資料型別	如果A等于NULL，則回傳TRUE，反之回傳FALSE
A IS NOT NULL	所有資料型別	如果A不等于NULL，則回傳TRUE，反之回傳FALSE
IN(數值1, 數值2)	所有資料型別	使用 IN運算顯示串列中的值
A [NOT] LIKE B	STRING 型別	B是一個SQL下的簡單正則運算式，如果A與其匹配的話，則回傳TRUE；反之回傳FALSE，B的運算式說明如下：‘x%’表示A必須以字母‘x’開頭，‘%x’表示A必須以字母’x’結尾，而‘%x%’表示A包含有字母’x’,可以位于開頭，結尾或者字串中間，如果使用NOT關鍵字則可達到相反的效果，
A RLIKE B, A REGEXP B	STRING 型別	B是一個正則運算式，如果A與其匹配，則回傳TRUE；反之回傳FALSE，匹配使用的是JDK中的正則運算式介面實作的，因為正則也依據其中的規則，例如，正則運算式必須和整個字串A相匹配，而不是只需與其字串匹配，

6.2.2 Like和RLike

1）使用LIKE運算選擇類似的值

2）選擇條件可以包含字符或數字:

% 代表零個或多個字符(任意個字符)，

_ 代表一個字符，

3）RLIKE子句是Hive中這個功能的一個擴展，其可以通過Java的正則運算式這個更強大的語言來指定匹配條件，

案例：

查找以2開頭薪水的員工資訊

select * from emp where sal LIKE '2%';

查找第二個數值為2的薪水的員工資訊

select * from emp where sal LIKE '_2%';

查找薪水中含有2的員工資訊

select * from emp where sal RLIKE '[2]';

6.2.3 邏輯運算子（And/Or/Not）

運算子	含義
AND	邏輯并
OR	邏輯或
NOT	邏輯否

案例：查詢薪水大于1000，部門是30

select * from emp where sal>1000 and deptno=30;

6.3 分組

6.3.1 Group By陳述句

GROUP BY陳述句通常會和聚合函式一起使用，按照一個或者多個列隊結果進行分組，然后對每個組執行聚合操作，

案例：計算emp表每個部門的平均工資

select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

6.3.2 Having陳述句

1．having與where不同點

（1）where針對表中的列發揮作用，查詢資料；having針對查詢結果中的列發揮作用，篩選資料，

（2）where后面不能寫分組函式，而having后面可以使用分組函式，

（3）having只用于group by分組統計陳述句，

案例：求每個部門的平均薪水大于2000的部門

hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

6.4 Join陳述句

6.4.1 等值Join

Hive支持通常的SQL JOIN陳述句，但是只支持等值連接，不支持非等值連接，

select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

6.4.2 表的別名

1．好處

（1）使用別名可以簡化查詢，

（2）使用表名前綴可以提高執行效率，

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

6.4.3 內連接

內連接：只有進行連接的兩個表中都存在與連接條件相匹配的資料才會被保留下來，

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

6.4.4 左外連接

左外連接：JOIN運算子左邊表中符合WHERE子句的所有記錄將會被回傳，

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

6.4.5右外連接

右外連接：JOIN運算子右邊表中符合WHERE子句的所有記錄將會被回傳，

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

6.4.6 滿外連接

滿外連接：將會回傳所有表中符合WHERE陳述句條件的所有記錄，如果任一表的指定欄位沒有符合條件的值的話，那么就使用NULL值替代，

 select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

6.4.7 笛卡爾積

1．笛卡爾集會在下面條件下產生

（1）省略連接條件

（2）連接條件無效

（3）所有表中的所有行互相連接

6.4.8 連接謂詞中不支持or

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename;   錯誤的

6.5 排序

6.5.1 全域排序（Order By）

Order By：全域排序，一個Reducer

1．使用 ORDER BY 子句排序

ASC（ascend）: 升序（默認）

DESC（descend）: 降序

2．ORDER BY 子句在SELECT陳述句的結尾

3．案例實操 ：查詢員工資訊按工資升序排列

select * from emp order by sal;

6.5.2 每個MapReduce內部排序（Sort By）

Sort By：每個Reducer內部進行排序，對全域結果集來說不是排序，叢林

1．設定reduce個數

set mapreduce.job.reduces=3;

2．查看設定reduce個數

set mapreduce.job.reduces;

3．根據部門編號降序查看員工資訊

select *from dept_partition sort by deptno;

對于全域結果來說并沒有排序，只是對每個reduce的結果進行了排序，

6.5.3 磁區排序（Distribute By）

Distribute By：類似MR中partition，進行磁區，結合sort by使用，

? 注意，Hive要求DISTRIBUTE BY陳述句要寫在SORT BY陳述句之前，

對于distribute by進行測驗，一定要分配多reduce進行處理，否則無法看到distribute by的效果，

select *from dept_partition distribute by deptno sort by month;

insert overwrite  local directory '/opt/datas/dept3' select *from dept_partition distribute by deptno sort by month;

6.5.4 Cluster By

當distribute by和sorts by欄位相同時，可以使用cluster by方式，

cluster by除了具有distribute by的功能外還兼具sort by的功能，但是排序只能是升序排序，不能指定排序規則為ASC或者DESC，

以下兩種方法等價：

select *from dept_partition distribute by deptno sort by deptno;
select *from dept_partition cluster by deptno;

注意：按照部門編號磁區，不一定就是固定死的數值，可以是20號和30號部門分到一個磁區里面去，

6.6 分桶（buckets）及抽樣查詢

6.6.1 分桶表資料存盤

磁區針對的是資料的存盤路徑；分桶針對的是資料檔案，
磁區提供一個隔離資料和優化查詢的便利方式，不過，并非所有的資料集都可形成合理的磁區，特別是之前所提到過的要確定合適的劃分大小這個疑慮，
分桶是將資料集分解成更容易管理的若干部分的另一個技術，

設定分捅屬性

set hive.enforce.bucketing=true;

創建分桶表

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

匯入資料到分桶表，通過子查詢的方式

insert into table stu_buck select id, name from stu;

分捅表只能通過insert插入資料，load讀取資料是無效的，

6.6.2 分桶抽樣查詢

對于非常大的資料集，有時用戶需要使用的是一個具有代表性的查詢結果而不是全部結果，Hive可以通過對表進行抽樣來滿足這個需求，

select * from customers_buck1 tablesample(bucket 1 out of 4 on customer_fname);

注：tablesample是抽樣陳述句，語法：TABLESAMPLE(BUCKET x OUT OF y) ，

y必須是table總bucket數的倍數或者因子，hive根據y的大小，決定抽樣的比例，例如，table總共分了4份，當y=2時，抽取(4/2=)2個bucket的資料，當y=8時，抽取(4/8=)1/2個bucket的資料，

==x表示從第幾個bucket開始抽取，如果需要取多個磁區，以后的磁區號為當前磁區號加上y，==例如，table總bucket數為4，tablesample(bucket 1 out of 2)，表示總共抽取（4/2=）2個bucket的資料，抽取第1(x)個和第3(x+y)個bucket的資料，

注意：x的值必須小于等于y的值，否則

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

表總共有：

分捅相當于預覽了部分資料，

6.7 其他常用查詢函式

6.7.1 空欄位賦值

1.函式說明
NVL：給值為NULL的資料賦值，它的格式是NVL( string1, replace_with)，它的功能是如果string1為NULL，則NVL函式回傳replace_with的值，否則回傳string1的值，如果兩個引數都為NULL ，則回傳NULL，

c表資料：

select c1,nvl(c2,1) from c;

6.7.2 CASE WHEN

1. 資料準備

name	dept_id	sex
悟空	A	男
大海	A	男
宋宋	B	男
鳳姐	A	女
婷姐	B	女
婷婷	B	女

2．需求

求出不同部門男女各多少人，結果如下：

A     2       1
B     1       2

3．按需求查詢資料

select dept_id,
sum(case when sex='男' then 1 else 0 end) man,
sum(case when sex='女' then 1 else 0 end) woman
from emp_sex group by dept_id;

6.7.3 行轉列

1．相關函式說明

CONCAT(string A/col, string B/col…)：回傳輸入字串連接后的結果，支持任意個輸入字串;

CONCAT_WS(separator, str1, str2,...)：它是一個特殊形式的 CONCAT()，第一個引數剩余引數間的分隔符，分隔符可以是與剩余引數一樣的字串，如果分隔符是 NULL，回傳值也將為 NULL，這個函式會跳過分隔符引數后的任何 NULL 和空字串，分隔符將被加到被連接的字串之間;

COLLECT_SET(col)：函式只接受基本資料型別，它的主要作用是將某欄位的值進行去重匯總，產生array型別欄位，

2．資料準備

name	constellation	blood_type
孫悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
豬八戒	白羊座	A
鳳姐	射手座	A

3．需求

把星座和血型一樣的人歸類到一起，結果如下：

射手座,A            大海|鳳姐
白羊座,A            孫悟空|豬八戒
白羊座,B            宋宋

4．按需求查詢資料

select
    t1.base,
    concat_ws('|', collect_set(t1.name)) name
from
    (select
        name,
        concat(constellation, ",", blood_type) base
    from
        person_info) t1
group by
    t1.base;

6.7.4 列轉行

1．函式說明

EXPLODE(col)：將hive一列中復雜的array或者map結構拆分成多行，

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解釋：用于和split, explode等UDTF一起使用，它能夠將一列資料拆成多行資料，在此基礎上可以對拆分后的資料進行聚合，

2．資料準備

movie	category
《疑犯追蹤》	懸疑,動作,科幻,劇情
《Lie to me》	懸疑,警匪,動作,心理,劇情
《戰狼2》	戰爭,動作,災難

3．需求

將電影分類中的陣列資料展開，結果如下：

《疑犯追蹤》      懸疑
《疑犯追蹤》      動作
《疑犯追蹤》      科幻
《疑犯追蹤》      劇情
《Lie to me》   懸疑
《Lie to me》   警匪
《Lie to me》   動作
《Lie to me》   心理
《Lie to me》   劇情
《戰狼2》        戰爭
《戰狼2》        動作
《戰狼2》        災難

4．按需求查詢資料

select  movie,category_name from movie_info
 lateral view explode(category) table_tmp  as category_name;

6.7.5 視窗函式

1．相關函式說明

OVER()：指定分析函式作業的資料視窗大小，這個資料視窗大小可能會隨著行的變而變化

CURRENT ROW：當前行

n PRECEDING：往前n行資料

n FOLLOWING：往后n行資料

UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點， UNBOUNDED FOLLOWING表示到后面的終點

LAG(col,n)：往前第n行資料

LEAD(col,n)：往后第n行資料

NTILE(n)：把有序磁區中的行分發到指定資料的組中，各個組有編號，編號從1開始，對于每一行，NTILE回傳此行所屬的組的編號，注意：n必須為int型別，

2．資料準備：name，orderdate，cost

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

3．需求

（1）查詢在2017年4月份購買過的顧客及總人數

（2）查詢顧客的購買明細及月購買總額

（3）上述的場景,要將cost按照日期進行累加

（4）查詢顧客上次的購買時間

（5）查詢前20%時間的訂單資訊

4．按需求查詢資料

（1）查詢在2017年4月份購買過的顧客及總人數

select name,count(*) over () from business where substring(orderdate,1,7) = '2017-04' group by name;

（2）查詢顧客的購買明細及月購買總額

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;

（3）上述的場景,要將cost按照日期進行累加

select name,orderdate,cost, sum(cost) over() as sample1,--所有行相加 sum(cost) over(partition by name) as sample2,--按name分組，組內資料相加 sum(cost) over(partition by name order by orderdate) as sample3,--按name分組，組內資料累加 sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一樣,由起點到當前行的聚合 sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --當前行和前面一行做聚合 sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--當前行和前邊一行及后面一行 sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --當前行及后面所有行 from business;

（4）查看顧客上次的購買時間

select name,orderdate,cost, lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 from business;

（5）查詢前20%時間的訂單資訊

select * from (  select name,orderdate,cost, ntile(5) over(order by orderdate) sorted  from business) twhere sorted = 1;

6.7.6 Rank

1．函式說明

RANK() 排序相同時會重復，總數不會變

DENSE_RANK() 排序相同時會重復，總數會減少

ROW_NUMBER() 會根據順序計算

2．資料準備

name	subject	score
孫悟空	語文	87
孫悟空	數學	95
孫悟空	英語	68
大海	語文	94
大海	數學	56
大海	英語	84
宋宋	語文	64
宋宋	數學	86
宋宋	英語	84
婷婷	語文	65
婷婷	數學	85
婷婷	英語	78

3．需求

計算每門學科成績排名，

4．按需求查詢資料

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

7.函式

7.1Hive函式分類

• 從輸入輸出角度分類
  標準函式：一行資料中的一列或多列為輸入，結果為單一值
  聚合函式：多行的零列到多列為輸入，結果為單一值
  表生成函式：零個或多個輸入，結果為多列或多行
• 從實作方式分類
  內置函式
  自定義函式:
  UDF：自定義標準函式
  UDAF：自定義聚合函式
  UDTF：自定義表生成函式

7.2內置函式

Hive提供大量內置函式供開發者使用

• 標準函式
  字符函式
  型別轉換函式
  數學函式
  日期函式
  集合函式
  條件函式
• 聚合函式
• 表生成函式

7.2.1字符函式

回傳值	函式	描述
string	concat(string|binary A, string|binary B…)	對二進制位元組碼或字串按次序進行拼接
int	instr(string str, string substr)	查找字串str中子字串substr出現的位置
int	length(string A)	回傳字串的長度
int	locate(string substr, string str[, int pos])	查找字串str中的pos位置后字串substr第一次出現的位置
string	lower(string A) /upper(string A)	將字串A的所有字母轉換成小寫/大寫字母
string	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	按正則運算式PATTERN將字串中符合條件的部分替換成REPLACEMENT所指定的字串
array	split(string str, string pat)	按照正則運算式pat來分割字串str
string	substr(string|binary A, int start, int len)substring(string|binary A, int start, int len)	對字串A,從start位置開始截取長度為len的字串并回傳
string	trim(string A)	將字串A前后出現的空格去掉
map	str_to_map(text[, delimiter1, delimiter2])	將字串str按照指定分隔符轉換成Map
binary	encode(string src, string charset)	用指定字符集charset將字串編碼成二進制值

7.2.2 型別轉換函式和數學函式

回傳值	型別轉換函式	描述
“type”	cast(expr as )	將expr轉換成type型別 如：cast(“1” as BIGINT) 將字串1轉換成了BIGINT型別
binary	binary(string|binary)	將輸入的值轉換成二進制

回傳值	數學函式	描述
DOUBLE	round(DOUBLE a)	回傳對a四舍五入的BIGINT值
binary	round(DOUBLE a, INT d)	回傳對a四舍五入并保留d位小數位的值
BIGINT	floor(DOUBLE a)	向下取整，如：6.10->6 -3.4->-4
DOUBLE	rand(INT seed)	回傳一個DOUBLE型亂數，seed是隨機因子
DOUBLE	power(DOUBLE a, DOUBLE p)	計算a的p次冪
DOUBLE	abs(DOUBLE a)	計算a的絕對值

7.2.3 日期函式

回傳值	函式	描述
string	from_unixtime(bigint unixtime[, string format])	將時間戳轉換成format格式
int	unix_timestamp()	獲取本地時區下的時間戳
bigint	unix_timestamp(string date)	將格式為yyyy-MM-dd HH:mm:ss的時間字串轉換成時間戳
string	to_date(string timestamp)	回傳時間字串的日期部分
int	year(string date)month/day/hour/minute/second/weekofyear	回傳時間字串的年份部分回傳月/天/時/分/秒/第幾周
int	datediff(string enddate, string startdate)	計算開始時間到結束時間相差的天數
string	date_add(string startdate, int days)	從開始時間startdate加上days
string	date_sub(string startdate, int days)	從開始時間startdate減去days
date	current_date	回傳當前時間的日期
timestamp	current_timestamp	回傳當前時間戳
string	date_format(date/timestamp/string ts, string fmt)	按指定格式回傳時間date 如：date_format(“2016-06-22”,“MM-dd”)=06-22

7.2.4 集合函式

回傳值	函式	描述
int	size(Map<K.V>)	回傳map中鍵值對個數
int	size(Array)	回傳陣列的長度
array	map_keys(Map<K.V>)	回傳map中的所有key
array	map_values(Map<K.V>)	回傳map中的所有value
boolean	array_contains(Array, value)	如該陣列Array包含value回傳true，，否則回傳false
array	sort_array(Array)	對陣列進行排序

7.2.5 條件函式

回傳值	函式	描述
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	如果testCondition 為true就回傳valueTrue,否則回傳valueFalseOrNull
T	nvl(T value, T default_value)	value為NULL回傳default_value,否則回傳value
T	COALESCE(T v1, T v2, …)	回傳第一非null的值，如果全部都為NULL就回傳NULL
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	如果a=b就回傳c,a=d就回傳e，否則回傳f
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	如果a=ture就回傳b,c= ture就回傳d,否則回傳e
boolean	isnull( a )	如果a為null就回傳true，否則回傳false
boolean	isnotnull ( a )	如果a為非null就回傳true，否則回傳false

7.2.6 聚合函式和表生成函式

• 聚合函式
  count、sum、max、min、avg、var_samp等
• 表生成函式：輸出可以作為表使用

回傳值	函式	描述
N rows	explode(array)	對于array中的每個元素生成一行且包含該元素
N rows	explode(MAP)	每行對應每個map鍵值對其中一個欄位是map的鍵，另一個欄位是map的值
N rows	posexplode(ARRAY)	與explode類似，不同的是還回傳各元素在陣列中的位置
N rows	stack(INT n, v_1, v_2, …, v_k)	把k列轉換成n行，每行有k/n個欄位，其中n必須是常數
tuple	json_tuple(jsonStr, k1, k2, …)	從一個JSON字串中獲取多個鍵并作為一個元組回傳，與get_json_object不同的是此函式能一次獲取多個鍵值

7.3 自定義UDF函式

Hive UDF開發流程

• 繼承UDF類或GenericUDF類
• 重寫evaluate()方法并實作函式邏輯
• 編譯打包為jar檔案
• 復制到正確的HDFS路徑
• 使用jar創建臨時/永久函式
• 呼叫函式

1．創建一個Maven工程Hive

https://blog.csdn.net/zmzdmx/article/details/108401283

2．匯入依賴

<dependency>
      <groupId>org.apache.hadoop</groupId>
      <artifactId>hadoop-common</artifactId>
      <version>2.6.0</version>
    </dependency>
    <dependency>
      <groupId>org.apache.hive</groupId>
      <artifactId>hive-exec</artifactId>
      <version>1.2.1</version>
    </dependency>

3．創建一個類

package cn.kgc.kb09;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;


public class TestUDF extends UDF {
    public Text evaluate(Text str){
        if(null==str){
            return null;
        }
        return new Text(str.toString().toUpperCase());
    }

    public static void main(String[] args) {
        TestUDF tu=new TestUDF();
        Text rst = tu.evaluate(new Text());
        System.out.println(rst);

    }
}

方法一(創建臨時函式)：

4．打成jar包上傳到服務器/opt/testudf.jar

5．將jar包添加到hive的classpath（linux路徑）

add jar /opt/testudf.jar;

6．創建臨時函式與開發好的java class關聯

create temporary function mylower as "cn.kgc.kb09.TestUDF";

方法二（創建永久函式）：

4.在linux命令列使用hdfs命令把jar包上傳到hdfs的路徑

hdfs dfs -put 路徑

create function 函式名 as ‘方法的全路徑’using as ‘jar包的hdfs路徑’

7．即可在hql中使用自定義的函式

select ename, mylower(ename) lowername from emp;

注意

• 報錯在linux執行

報錯

java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.FunctionTask
	at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:294)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
	at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:736)
	at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:819)
	at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:103)
	at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:632)
	at org.apache.zeppelin.scheduler.Job.run(Job.java:188)
	at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

執行命令，不行就退出hive，再次執行

zip -d testUdf.jar 'META-INF/.SF' 'META-INF/.RSA' 'META-INF/*SF'

• 臨時函式可以垮庫運行，永久函式需要庫名.函式名

8.性能優化

8.1 Hive事務

1.事務（Transaction ）指一組單元化操作，這些操作要么都執行，要么都不執行

ACID特性：

• Atomicity：原子性
• Consistency：一致性
• Isolation：隔離性
• Durability：持久性

2.Hive事務的特點和局限

• V0.14版本開始支持行級事務
  • 支持INSERT、DELETE、UPDATE(v2.2.0開始支持Merge)
  • 檔案格式只支持ORC
• 局限
  • 表必須是bucketed表
  • 需要消耗額外的時間、資源和空間
  • 不支持開始、提交、回滾、桶或磁區列上的更新
  • 鎖可以為共享鎖或排它鎖(串聯的而不是并發)
  • 不允許從一個非ACID連接讀寫ACID表
  • 使用較少

3. Hive事務的開啟和設定

• 通過Hive命令列方式設定,當前session有效
• 通過組態檔設定，全局有效
• 通過UI工具（如Ambari)設定

-- 通過命令列方式開啟事務
set hive.support.concurrency = true;
set hive.enforce.bucketing = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.compactor.initiator.on = true;
set hive.compactor.worker.threads = 1; 

-- 通過組態檔hive-site.xml
<property> 
<name>hive.support.concurrency</name> 
<value>true</value>
 </property>
 <property> 
<name>hive.txn.manager</name> <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>

8.2 Hive PLSQL

• Hive PLSQL：Hive存盤程序（v2.0之后）
  • 支持SparkSQL和Impala
  • 兼容Oracle、DB2、MySQL、TSQL標準
  • 使將現有的程序遷移到Hive變得簡單和高效
  • 使撰寫UDF不需要Java技能
  • 它的性能比Java UDF稍微慢一些
  • 功能較新
• 在Hive2 bin目錄下運行./hplsql

./hplsql -f plsql_demo.pl
RETURNS STRING 
BEGIN RETURN 'Hello, ' || text || '!'; 
END;
Print hello(' word') 

CREATE PROCEDURE getCount()
BEGIN DECLARE cnt INT = 0;	
SELECT COUNT(*) INTO cnt FROM employee;
PRINT 'Users cnt: ' || cnt;
END;
call getCount();

8.3 Hive性能調優工具

1.EXPLAIN

2.ANALYZE

• ANALYZE：分析表資料，用于執行計劃選擇的參考
  • 收集表的統計資訊，如行數、最大值等
  • 使用時呼叫該資訊加速查詢
• 語法

ANALYZE TABLE employee COMPUTE STATISTICS; 

ANALYZE TABLE employee_partitioned 
PARTITION(year=2014, month=12) COMPUTE STATISTICS;

ANALYZE TABLE employee_id COMPUTE STATISTICS 
FOR COLUMNS employee_id;

8.4 Hive優化設計

• 使用磁區表、桶表
• 使用索引
• 使用適當的檔案格式，如orc, avro, parquet
• 使用適當的壓縮格式，如snappy
• 考慮資料本地化 - 增加一些副本
• 避免小檔案
• 使用Tez引擎代替MapReduce
• 使用Hive LLAP(在記憶體中讀取快取)
• 考慮在不需要時關閉并發

8.5 Job優化

1.本地模式運行

Hive支持將作業自動轉換為本地模式運行
當要處理的資料很小時，完全分布式模式的啟動時間比作業處理時間要長

-- 通過以下設定開啟本地模式
SET hive.exec.mode.local.auto=true; --default false 
SET hive.exec.mode.local.auto.inputbytes.max=50000000; 
SET hive.exec.mode.local.auto.input.files.max=5; --default 4

• Job必須滿足以下條件才能在本地模式下運行
  Job總輸入大小小于 hive.exec.mode.local.auto. inputbytes.max
  map任務總數小于 hive.exec.mode.local.auto. input.files.max
  所需的Reduce任務總數為1或0

2.JVM重用（JVM Reuse）

• 通過JVM重用減少JVM啟動的消耗
  • 默認每個Map或Reduce啟動一個新的JVM
  • Map或Reduce運行時間很短時，JVM啟動程序占很大開銷
  • 通過共享JVM來重用JVM，以串行方式運行MapReduce Job
  • 適用于同一個Job中的Map或Reduce任務
  • 對于不同Job的任務，總是在獨立的JVM中運行

-- 通過以下設定開啟JVM重用
set mapred.job.reuse.jvm.num.tasks = 5;  -- 默認值為1

3.并行執行

• 并行執行可提高集群利用率
  • Hive查詢通常被轉換成許多按默認順序執行的階段
  • 這些階段并不總是相互依賴的
  • 它們可以并行運行以節省總體作業運行時間
  • 如果集群的利用率已經很高，并行執行幫助不大

-- 通過以下設定開啟并行執行
SET hive.exec.parallel=true;  -- default false 
SET hive.exec.parallel.thread.number=16;  -- default 8,定義并行運行的最大數量

8.6 查詢優化

• 自動啟動Map端Join
• 防止資料傾斜

set hive.optimize.skewjoin=true;	

• 啟用CBO(Cost based Optimizer)

  set hive.cbo.enable=true; 
  set hive.compute.query.using.stats=true; 
  set hive.stats.fetch.column.stats=true; 
  set hive.stats.fetch.partition.stats=true;	
  

  啟動Vectorization（矢量化）

  set hive.vectorized.execution.enabled = true; 
  set hive.vectorized.execution.reduce.enabled = true;
  
  

  使用CTE、臨時表、視窗函式等正確的編碼約定

8.7 壓縮演算法

• 減少傳輸資料量，會極大提升MapReduce性能
• 采用資料壓縮是減少資料量的很好的方式
• 常用壓縮方法對比

壓縮方式	可分割	壓縮后大小	壓縮解壓速度
gzip	否	中	中
lzo	是	大	快
snappy	否	大	快

nappy

• 考慮資料本地化 - 增加一些副本
• 避免小檔案
• 使用Tez引擎代替MapReduce
• 使用Hive LLAP(在記憶體中讀取快取)
• 考慮在不需要時關閉并發

8.5 Job優化

1.本地模式運行

Hive支持將作業自動轉換為本地模式運行
當要處理的資料很小時，完全分布式模式的啟動時間比作業處理時間要長

-- 通過以下設定開啟本地模式
SET hive.exec.mode.local.auto=true; --default false 
SET hive.exec.mode.local.auto.inputbytes.max=50000000; 
SET hive.exec.mode.local.auto.input.files.max=5; --default 4

• Job必須滿足以下條件才能在本地模式下運行
  Job總輸入大小小于 hive.exec.mode.local.auto. inputbytes.max
  map任務總數小于 hive.exec.mode.local.auto. input.files.max
  所需的Reduce任務總數為1或0

2.JVM重用（JVM Reuse）

• 通過JVM重用減少JVM啟動的消耗
  • 默認每個Map或Reduce啟動一個新的JVM
  • Map或Reduce運行時間很短時，JVM啟動程序占很大開銷
  • 通過共享JVM來重用JVM，以串行方式運行MapReduce Job
  • 適用于同一個Job中的Map或Reduce任務
  • 對于不同Job的任務，總是在獨立的JVM中運行

-- 通過以下設定開啟JVM重用
set mapred.job.reuse.jvm.num.tasks = 5;  -- 默認值為1

3.并行執行

• 并行執行可提高集群利用率
  • Hive查詢通常被轉換成許多按默認順序執行的階段
  • 這些階段并不總是相互依賴的
  • 它們可以并行運行以節省總體作業運行時間
  • 如果集群的利用率已經很高，并行執行幫助不大

-- 通過以下設定開啟并行執行
SET hive.exec.parallel=true;  -- default false 
SET hive.exec.parallel.thread.number=16;  -- default 8,定義并行運行的最大數量

8.6 查詢優化

• 自動啟動Map端Join
• 防止資料傾斜

set hive.optimize.skewjoin=true;	

• 啟用CBO(Cost based Optimizer)

  set hive.cbo.enable=true; 
  set hive.compute.query.using.stats=true; 
  set hive.stats.fetch.column.stats=true; 
  set hive.stats.fetch.partition.stats=true;	
  

  啟動Vectorization（矢量化）

  set hive.vectorized.execution.enabled = true; 
  set hive.vectorized.execution.reduce.enabled = true;
  
  

  使用CTE、臨時表、視窗函式等正確的編碼約定

8.7 壓縮演算法

• 減少傳輸資料量，會極大提升MapReduce性能
• 采用資料壓縮是減少資料量的很好的方式
• 常用壓縮方法對比

壓縮方式	可分割	壓縮后大小	壓縮解壓速度
gzip	否	中	中
lzo	是	大	快
snappy	否	大	快
bzip2	是	小	慢