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	是	小	慢