推薦收藏的Hive語言大全

必須要看的前言

上次寫了一篇介紹Hive的博客，很高興上了綜合熱榜.

而這次，本人在連續肝了好幾天的情況下，完成了介紹Hive語言的詳細教程，收藏本篇文章，也就意味著你擁有了一份超級完善的Hive語言書籍，講得非常非常的詳細，希望大家能慢慢看，看完之后必定會識訓巨多，也可以幫助后續結合Hive進行更高端的操作，快開學了，本學生最后的假期時間都用在寫博客上了，希望大家能夠三連支持一下，先謝謝啦！

一、入門需知

1 創建資料庫

1.1 創建資料庫

create database if not exists csdn_test;

其中if not exists可以不寫，但如果已經存在了csdn_test這個資料庫就會報錯，

1.2 查看資料庫

show databases;

1.3 洗掉資料庫

洗掉空資料庫 drop database 資料庫名；

drop database csdn_test; 

為防止洗掉的資料庫不存在發生報錯，最好采用if exists判斷資料庫是否存在，

drop database if exists csdn_test;

如果資料庫不為空，但又想洗掉怎么辦？在最后加個cascade，

drop database csdn_test cascade;

1.4 進入資料庫

use csdn_test;

2 Hive資料型別

2.1 數字類

型別	長度	備注
TINYINT	1位元組	有符號整型
SMALLINT	2位元組	有符號整型
INT	4位元組	有符號整型
BIGINT	8位元組	有符號整型
FLOAT	4位元組	有符號單精度浮點數
DOUBLE	8位元組	有符號雙精度浮點數
DECIMAL	–	可帶小數的精確數字字串

1位元組可以存8個0/1，

2.2 日期時間類

型別	長度	備注
TIMESTAMP	–	時間戳，內容格式：yyyy-mm-dd hh:mm:ss[.f…]
DATE	–	日期，內容格式：YYYY- MM- DD
INTERVAL	–	–

這里的時間戳可以理解為離具體指定的某個時間點相差的時間（精確到秒），

2.3 字串類

型別	長度	備注
STRING	–	字串
VARCHAR	字符數范圍1 - 65535	長度不定字串
CHAR	最大的字符數：255	長度固定字串

2.4 Misc類

型別	長度	備注
BOOLEAN	–	布爾型別 TRUE/FALSE
BINARY	–	位元組序列

2.5 復合類

型別	長度	備注
ARRAY	–	包含同型別元素的陣列，索引從0開始 ARRAY<data_type>
MAP	–	字典 MAP<primitive_type, data_type>
STRUCT	–	結構體 STRUCT<col_name : data_type [COMMENT col_comment],…>
UNIONTYPE	–	聯合體UNIONTYPE<data_type, data_type, …>

這些的聯合體需要說一下，MAP型別就像Python中的DICT字典型資料，都是一個關鍵字key對應一個關鍵值value；UNIONTYPE 型別的資料可以存放任何資料，包括字串類、陣列型和MAP型等，后面會有例子詳細說明，

3 Hive建表

首先需要說明的是，Hive建表的時候可以選擇建立內部表或者是外部表，而內部表和外部表的區別主要如下，

• 從資料管理來看，內部表主要是由Hive自身管理，而外部表需要由HDFS管理；
• 從資料存盤的位置來看，內部表資料是在/user/hive/warehouse，外部表則是可以通過LOCATION關鍵詞來指定存盤位置，不然默認也是和內部表存盤位置相同；
• 從洗掉表來看，洗掉內部表時可以直接洗掉元資料及存盤資料，而洗掉外部表僅僅會洗掉元資料；
• 從修改表來看，內部表的修改會直接同步給元資料，而外部表需要進行修復操作（MSCK REPAIR TABLE table_name;）

3.1 直接建表法

先給大家列一個建表的陳述句格式：

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 test_Create_Table(id int,name string);

--查看當前庫表名
show tables;
--查看建表資訊 
show create table test_Create_Table; 
--查看表結構 
desc test_Create_Table;

和創建資料庫一樣，IF NOT EXIST可以忽略同名表的例外問題，

create table if not exists test_Create_Table(id int,name string);

EXTERNAL ，可以讓用戶創建一個外部表，在建表的同時指定一個指向實際資料的路徑（LOCATION）

--不指定路徑，同內表路徑一致 
create external table test_External_Table(id int,name string);

--指定路徑創建外部表（要求為新建路徑) 
create external table test_External_Location(id int, name string) location '/new_dir';

LIKE，可以復制表的結構，但注意并沒有復制表中的資料，感覺很容易理解：創建一個像…的表，

create table test_like like test_create_table;

COMMENT，就是給表或者欄位添加描述，

create table table_Comment(id int comment '編號',name string comment '姓名') comment '員工資訊表';

PARTITIONED BY，用于指定磁區，后文會詳細講解磁區和分桶，

ROW FORMAT，這里是指行資料的格式，后文也會專門講解，

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

LOCATION，指定表在HDFS的存盤路徑，

CLUSTERED，表示的是按照某列聚類，例如在插入資料中有兩項“張三，數學”和“張三，英語”，若是CLUSTERED BY name，則只會有一項，“張三，(數學，英語)”，這個機制也是為了加快查詢的操作，

3.2 查詢建表法

create table NewTableBySelect as select * from test_create_table;

需要注意的是，這里select 中選取的列名會作為新表的列名（所以通常是要取別名），會改變表的屬性、結構，比如只能是內部表、磁區分桶也沒了；另外，select 中選取的列名會作為新表的列名（所以通常是要取別名），會改變表的屬性、結構，比如只能是內部表、磁區分桶也沒了，同時，目標表不允許使用外部表，會報錯創建的表存盤格式會變成默認的格式 TEXTFILE ，不過可以指定表的存盤格式，行和列的分隔符等，

3.3 like建表法

這里在3.1 介紹like的時候說過了，

反正大體上建表就是這幾種方法，大家要先掌握好，

4 分隔符

4.1 欄位分隔符

語法：fields terminated by '\t' （hive 默認的欄位分隔符為ascii碼的控制符 \001 ctrl + V+A）

--設定欄位分隔符 
create table test_delimit(id int, name string) row format delimited fields terminated by '^B'; 
--查看欄位分隔符 
desc formatted test_delimit;

我們可以運行看一下，

可以看到，這里的分隔符資訊出現了我們設定好的^B，當然，你想設定啥都可以，

4.2 array 型別成員分隔符

語法：collection items terminated by ','

陣列分分隔符一般都是設定為逗號，

我們運行個例子看看結果，

已知"C:\Users\24721\Desktop\"目錄下的data1.txt檔案內容為：

123|華為Mate10|1235,345 
456|華為Mate30|89,635 
789|小米5|452,63 
1235|小米6|785,36 
4562|OPPO Findx|7875,3563

現在我們要做的就是建立一個表，并且把這個檔案中的資料正常匯入到該表中，具體實作代碼如下，

--指定 |為欄位分隔符 ，為陣列分隔符 
create table sales_info( 
sku_id string comment '商品id', 
sku_name string comment '商品名稱', 
id_array array<string> comment '商品相關id串列') 
row format delimited fields terminated by '|' 
collection items terminated by ',' ;

--裝載本地檔案到 sales_info中
load data local inpath "C:\Users\24721\Desktop\data1.txt" overwrite into table sales_info;

--通過查詢陳述句查看資料
select * from sales_info;

結果如下：

4.3 map：Key和Value之間的分隔符

語法：map keys terminated by ':'

我們在舉一個例子：已知"C:\Users\24721\Desktop\"下的data2.txt中的內容如下：

123|華為Mate10|id:1111,token:2222,user_name:zhangsan1 
456|華為Mate30|id:1113,token:2224,user_name:zhangsan3 
789|小米5|id:1114,token:2225,user_name:zhangsan4 
1235|小米6|id:1115,token:2226,user_name:zhangsan5 
4562|OPPO Findx|id:1116,token:2227,user_name:zhangsan6

現在我們要做的就是建立一個表，并且把這個檔案中的資料匯入到該表中，具體實作代碼如下，

--創建含有map型別的資料表 
create table mapkeys( 
sku_id string comment '商品id', 
sku_name string comment '商品名稱', 
state_map map<string,string> comment '商品狀態資訊') 
row format delimited 
fields terminated by '|' 
map keys terminated by ':';

--將本地data2.txt資料加載到mapkeys表 
load data local inpath "C:\Users\24721\Desktop\data2.txt" overwrite into table mapkeys;

--查看表內資料是否與檔案一致 
select * from mapkeys;

運行結果如下：

如圖，建表插入資料成功，

但大家仔細看的話可以發現，state_map欄位中的只有一個key（“id”），剩下全被當作value，這很明顯不是我們最終想要的，那怎么辦，我們可以加上一個collection items terminated by ','，

具體如下：

--創建含有map型別的資料表 
create table mapkeys2( 
sku_id string comment '商品id', 
sku_name string comment '商品名稱', 
state_map map<string,string> comment '商品狀態資訊') 
row format delimited 
fields terminated by '|' 
collection items terminated by ','
map keys terminated by ':';

--將本地data2.txt資料加載到mapkeys表 
load data local inpath "C:\Users\24721\Desktop\data2.txt" overwrite into table mapkeys2;

--查看表內資料是否與檔案一致 
select * from mapkeys2;

此時的結果才是我們想要的，

4.4 行分隔符

語法：lines terminated by '\n'，

建表時得放在最后，不過這個建表時一般沒有人寫上去，因為目前默認"\n"同時也只支持"\n"，

4.5 使用多字符作為分隔符

• 使用MultiDelimitSerDe的方法來實作

--創建多分隔符表 
CREATE TABLE test_MultiDelimit(id int, name string ,tel string) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' WITH SERDEPROPERTIES ("field.delim"="##") 
STORED AS TEXTFILE;

--查看表 
desc formatted test_MultiDelimit;

運行結果如下：

• 使用RegexSerDe的方法實作，但注意RegexSerDe僅支持字串型別的，不能有其他型別，

CREATE TABLE test1(id string, name string ,tel string) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' 
WITH SERDEPROPERTIES ("input.regex" = "^(.*)\\#\\#(.*)$") 
STORED AS TEXTFILE;

5 磁區表創建

這部分是我在上一篇博客中的Hive資料模型就提到了的，可以說著部分是Hive較為特色的部分，

5.1 使用磁區表的意義

使用磁區表的意義大致如下：使用磁區技術，避免hive全表掃描，提升查詢效率；同時能夠減少資料冗余進而提高特定(指定磁區)查詢分析的效率，

注意，在邏輯上磁區表與未磁區表沒有區別，在物理上磁區表會將資料按照磁區鍵的列值存盤在表目錄的子目錄中，目錄名為“磁區鍵=鍵值”，你可以把建立磁區想象成我建了個檔案夾，把一些相似（或者說你想要的型別）的資料存放到檔案夾中，

磁區表這么好用，所以查詢時盡量利用磁區欄位，如果不使用磁區欄位，就會全部掃描，

5.2 磁區表型別

磁區表型別：靜態磁區和動態磁區，區別在于前者是我們手動指定的，后者是通過資料來判斷磁區的，

5.3 建立磁區

靜態磁區和動態磁區的建表陳述句是一樣的，

注意：PARTITIONED BY ()括號中指定的磁區名不能跟表中的欄位名一樣，

-- 創建磁區表 PARTITIONED BY (磁區欄位名 磁區欄位型別) 
create table test_partition1( 
sku_id string comment '商品id', 
sku_name string comment '商品名稱') 
PARTITIONED BY (sku_class string); 

--建立磁區表之后，此時沒有資料，也沒有磁區，需要建立磁區 
--創建磁區 
alter table test_partition1 add partition(sku_class='xiaomi') ;

--查看表現有磁區 
show partitions test_partition1;

此時磁區test_partition1以及建好，

當然，我們可以通過多欄位磁區，具體如下，

-- 創建磁區表 PARTITIONED BY (磁區欄位名 磁區欄位型別,磁區欄位名2 磁區欄位型別2) 多欄位磁區
create table test_partition_mul( 
sku_id string comment '商品id', 
sku_name string comment '商品名稱') 
PARTITIONED BY (sku_class string,sku_lable string); 

--添加磁區 
alter table test_partition_mul add IF NOT EXISTS 
partition(sku_class='xiaomi',sku_lable='dianzi');

--查看現有磁區 
show partitions test_partition_mul;

此時磁區test_partition_mul以及建好，

往靜態磁區添加資料的陳述句如下：

insert into table test_partition_mul 
partition(sku_class='xiaomi',sku_lable='dianzi') values('001','xiaomi1'); 

insert into table test_partition_mul 
partition(sku_class='xiaomi',sku_lable='dianzi') select sku_id,sku_name from sales_info;

不過使用insert插入資料的速度往往會偏慢，大家可以采用load data的方法加載本地檔案到磁區表中，

load data local inpath '本地檔案路徑' into table test_partition partition (sku_class='xiaomi',sku_lable='dianzi');

動態磁區插入資料的方法如下：

insert into table test_partition_mul partition(sku_class,sku_lable) values ('001','xiaomi2','xiaomi','dianzi'); 

5.4 洗掉磁區 drop

語法：alter table 表名 drop partition(磁區欄位名=取值);

alter table test_partition1 drop partition(sku_class='xiaomi');

--查看磁區：
show partitions test_partition1;

此時說明洗掉成功，

6 分桶表創建

為什么要有分桶技術？分桶是啥意思，有啥作用？磁區和分桶的區別有哪些？

這就給你一一解答，

1. 當單個磁區或者表中的資料量越來越大的時候，磁區不能更細粒地劃分資料時，可以采用分桶技術進行更細粒度的劃分和管理；
2. 分桶的實質其實就是對分桶的欄位做了hash，然后存放到對應的檔案中；
3. 分桶可以提高join查詢效率，方便進行抽樣；

至于分桶和磁區的區別呢，主要有一下幾個地方：

1. 磁區使用的是表外欄位，而分桶使用的是表內欄位（也就是說磁區時磁區名不能取表欄位名，而分桶是得指定表欄位名嗎）；
2. 分桶是更細粒度的劃分、管理資料，更多用來做資料抽樣、JOIN操作；
3. 分桶隨機分割資料庫，而磁區是非隨機分割資料庫；
4. 分桶是對應不同的檔案（細粒度），而磁區是對應不同的檔案夾（粗粒度）；
5. 普通表（外部表、內部表）、磁區表這三個都是對應HDFS上的目錄，同表對應的是目錄里的檔案，

如果到這沒聽懂，沒關系，接著往下看，

語法：create table 表名(欄位1 型別1，欄位2，型別2 ) clustered by(表內欄位) sorted by(表內欄位) into 分桶數 buckets

--創建分桶表 
create table test_buckets( 
sku_id string comment '商品id', 
sku_name string comment '商品名稱') 
clustered by(sku_id) into 3 buckets; 

但記得先設定自動分桶開關：

set hive.enforce.bucketing=true; 

添加資料到分桶表：

insert into test_buckets select sku_id,sku_name from sales_info;

7 非hive環境下執行hql

雖然內容很簡單，但是還是得介紹一下，

怎么執行一堆命令，可以將hql陳述句放到一個檔案中，采用hive -f "檔案路徑"即可，注意這個是在非hive環境下運行，

而hive -e "hql陳述句"則是直接執行hive陳述句，

二、Hive操作語言

1 加載資料

1.1 從本地裝載資料

這個其實在前面也已經提到了，

普通表：load data local inpath '資料檔案路徑' [overwrite] into table 表名 ;
其中overwrite表示覆寫原有資料，沒有的話則表示添加資料，

load data local inpath 'E:hadoop/datas/payments.txt' into table payments;

磁區表：load data local inpath '資料檔案路徑' [overwrite] into table 表名 partition (磁區欄位=值);

load data local inpath 'E:hadoop/datas/product_category_level1.txt' into table product_category partition(level=1);

分桶表：load data local inpath '資料檔案路徑' [overwrite] into table 表名;

--開啟分桶功能 
set hive.enforce.bucketing=true; 

-- 忽略掉安全檢查 
hive.strict.checks.bucketing=false; 

load data local inpath 'E:hadoop/datas/shop.txt' into table shops;

1.2 從HDFS加載資料

1. cmd上傳到HDFS檔案系統中，
   語法：hdfs dfs -put 本地檔案路徑 hdfs路徑

hdfs dfs -put "C:\Users\24721\Desktop\product_info.txt" "/datas"

2. 檔案資料加載到Hive表中，
   語法：load data inpath 'hdfs資料檔案路徑' into table 表名;

load data inpath '/datas/product_info.txt' into table product_info;

同理磁區表要指定磁區，分桶表與普通表加載資料一樣，

2 插入資料

2.1 普通表

語法：insert into 表名 values(值)或者insert overwrite 表名 values(值)，

2.2 磁區表

靜態磁區：

insert into test_partition1 partition(sku_class="xiaomi") values(1,'sku_new');

動態磁區：

insert into test_partition1 partition(sku_class) values(1,'sku_new','蘋果');

2.3 分桶表

語法：insert into 分桶表表名

insert into test_buckets values(1,'sku_new');

3 匯出資料

3.1 匯出到本地檔案系統

語法：INSERT OVERWRITE LOCAL DIRECTORY '檔案夾路徑' ROW FORMAT DELIMITED FIELDS TERMINATED by '欄位分隔符' 查詢陳述句;
PS: 會重寫指定檔案夾 （一定要小心覆寫掉有用的檔案），有新建檔案夾功能， 匯出的檔案都名都為000000_0，另外，默認分隔符是用系統指定的，和本身建表陳述句指定的沒有關系，

INSERT OVERWRITE LOCAL DIRECTORY 'E:/hadoop/datas/test_output' ROW FORMAT DELIMITED FIELDS TERMINATED by '\t' select * from sales_info;

3.2 匯出到HDFS

語法：INSERT OVERWRITE DIRECTORY '檔案夾路徑' 查詢陳述句;

與匯出到本地檔案系統相比，少了個local，

4 洗掉表

4.1 洗掉所有資料

語法：truncate table 表名

使用truncate僅可洗掉內部表資料，不可洗掉表結構，
（就相當于你有個房子，現在只是把里面的東西都搬走，但是房子的架構啥的都沒變），

truncate table byselect;

使用cmd洗掉外部表資料(hdfs dfs -rm -r 外部表路徑):

hdfs dfs -rm -r /datas/test_Exteranl/*

4.2 洗掉表部分資料

有partition表

1. 洗掉具體partition
   語法：alter table table_name drop partition(partiton_name='value'))

--查看資料 
select * from test_partition1; 
show partitions test_partition1; 

--洗掉指定磁區 
alter table test_partition1 drop partition(sku_class = 'xiaomi');

2. 洗掉partition內的部分資訊(INSERT OVERWRITE TABLE）

INSERT OVERWRITE TABLE test_partition_mul 
PARTITION(sku_class='xiaomi',sku_lable='dianzi') 
SELECT sku_id,sku_name FROM test_partition_mul 
WHERE sku_id='1235';

重新把對應的partition資訊寫一遍，通過WHERE 來限定需要留下的資訊，沒有留下的資訊就被洗掉了，

無partiton表
語法：INSERT OVERWRITE TABLE 表名 SELECT * FROM 表名 WHERE 條件;，

--插入測驗資料 
insert into sales_info(sku_id,sku_name) values(1,'a'); 

--洗掉不為2的資料 
insert overwrite table sales_info(sku_id,sku_name) select sku_id,sku_name from sales_info where sku_id='2';

洗掉整個表
使用drop可洗掉整個表（drop table 表名）

drop table test_external;

三、Hive查詢語言

1 內置運算子

1.1 關系運算子

運算子	操作	描述
A = B	所有基本型別	如果表達A等于表達B，結果TRUE ，否則FALSE，
A != B	所有基本型別	如果A不等于運算式B表達回傳TRUE ，否則FALSE，
A < B	所有基本型別	如果運算式A小于運算式B為TRUE，否則FALSE，
A <= B	所有基本型別	如果運算式A小于或等于運算式B為TRUE，否則FALSE
A > B	所有基本型別	如果運算式A大于運算式B為TRUE，否則FALSE，
A >= B	所有基本型別	如果運算式A大于或等于運算式B為TRUE，否則FALSE，
A [NOT] BETWEEN B AND C	基本資料型別	如果A，B或者C任一為NULL，則結果為NULL，如果A的值大于等于B而且小于或等于C，則結果為TRUE，反之為FALSE，如果使用NOT關鍵字則可達到相反的效果，
A IS [NOT] NULL	所有型別	如果A等于NULL，則回傳TRUE，反之回傳FALSE, NOT 正好相反，
A IN(數值1, 數值2)	所有型別	如果A存在指定的資料中，則回傳TRUE，反之回傳FALSE，
A [NOT] LIKE B	字串	如果A與B匹配的話，則回傳TRUE；反之回傳FALSE，%代表任意多個字符，_代表一個字符 % _，
A RLIKE B	字串	如果A或B為NULL；如果A任何子字串匹配Java正則運算式B；否則FALSE，
A REGEXP B	字串	等同于RLIKE，

1.2 算術運算子

運算子	操作	描述
A + B	所有數字型別	A加B的結果
A - B	所有數字型別	A減去B的結果
A / B	所有數字型別	A除以B的結果
A % B	所有數字型別	A除以B.產生的余數

1.3 邏輯運算子

運算子	操作	描述
A AND B	boolean	如果A和B都是TRUE，否則FALSE，
A && B	boolean	類似于 A AND B.
A OR B	boolean	TRUE，如果A或B或兩者都是TRUE，否則FALSE，
A || B	boolean	類似于 A OR B.
NOT A	boolean	TRUE，如果A是FALSE，否則FALSE，
!A	boolean	類似于 NOT A.

1.4 復雜的運算子

運算子	操作	描述
A[n]	A是一個陣列，n是一個int	它回傳陣列A的第n個元素，第一個元素的索引0，
M[key]	M 是一個 Map<K, V> key 的型別為K	它回傳對應于映射中關鍵字的值，
S.x	S 是一個結構	它回傳S的s欄位

2 內置函式

2.1 數學函式

回傳型別	簽名	描述
BIGINT DOUBLE	round(double a) round(double a, int d)	回傳double型別的整數值部分 （遵循四舍五入） 回傳指定精度d的double型別
BIGINT	floor(double a)	回傳等于或者小于該double變數的最大的整數
BIGINT	ceil(double a)	回傳等于或者大于該double變數的最小的整數
DOUBLE	rand() rand(int seed)	回傳一個0到1范圍內的亂數，如果指定種子seed，則會等到一個穩定的亂數序列.
DOUBLE	pow(double a, double p)	回傳a的p次冪
DOUBLE	sqrt(double a)	回傳a的平方根
DOUBLE INT	abs(double a) abs(int a)	回傳數值a的絕對值

2.2 日期函式

回傳型別	簽名	描述
STRING	from_unixtime(bigint unixtime[, string format])	轉化UNIX時間戳（從1970-01-01 00:00:00 UTC到指定時間的秒數） 到當前時區的時間格式
BIGINT	unix_timestamp() unix_timestamp(string date) unix_timestamp(string date, string pattern)	獲得當前時區的UNIX時間戳 轉換格式為"yyyy-MM-dd HH:mm:ss"的日期到UNIX時間戳，如果轉化失敗，則回傳0 轉換pattern格式的日期到UNIX時間戳，如果轉化失敗，則回傳0
STRING	to_date(string timestamp)	回傳日期時間欄位中的日期部分
INT	year(string date) month (string date) day (string date)	分別回傳日期中的年 月 天
INT	hour (string date) minute (string date) second (string date)	分別回傳日期中的時 分 秒
INT	weekofyear (string date)	回傳日期在當年的第幾周
INT	datediff(string enddate, string startdate)	回傳結束日期減去開始日期的天數 日期有格式要求 yyyy-mm-dd hh:MM:ss 或 yyyy-mm-dd
STRING	date_add(string startdate, int days)	回傳開始日期startdate增加days天后的日期 add_months(string startdate, int months)
STRING	date_sub (string startdate, int days)	回傳開始日期startdate減少days天后的日期

2.3 條件判斷函式

回傳型別	簽名	描述
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	當條件testCondition為TRUE時，回傳valueTrue；否則回傳valueFalseOrNull
T	coalesce(T v1, T v2, …)	回傳引數中的第一個非空值；如果所有值都為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為TRUE,則回傳b；如果c為TRUE，則回傳 d；否則回傳e

2.4 字串函式

回傳型別	簽名	描述
INT	length(string A)	回傳字串A的長度
STRING	reverse(string A)	回傳字串A的反轉結果
STRING	concat(string A, string B…)	回傳輸入字串連接后的結果，支持任意個輸入字串
STRING	concat_ws(string SEP, string A, string B…)	回傳輸入字串連接后的結果，SEP表示各個字串間的分隔符
STRING	substr(string A, int start),substring(string A, int start) substr(string A, int start, int len),substring(string A, int start, int len)	回傳字串A從start位置到結尾的字串 回傳字串A從start位置開始，長度為len的字串
STRING	upper(string A) ucase(string A)	回傳字串A的大寫格式
STRING	lower(string A) lcase(string A)	回傳字串A的小寫格式
STRING	trim(string A) ltrim(string A) rtrim(string A)	去除字串兩邊的空格 除字串左邊的空格 去除字串右邊的空格
STRING	regexp_replace(string A, string B, string C)	將字串A中的符合java正則運算式B的部分替換為C
STRING	regexp_extract(string subject, string pattern, int index)	將字串subject按照pattern正則運算式的規則拆分，回傳index指定的字符
STRING	parse_url(string urlString, string partToExtract [, string keyToExtract])	回傳URL中指定的部分，partToExtract的有效值為：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
STRING	get_json_object(string json_string, string path)	決議json的字串json_string,回傳path指定的內容，如果輸入的json字串無效，那么回傳NULL
STRING	space(int n)	回傳長度為n的空格字串
STRING	repeat(string str, int n)	回傳重復n次后的str字串
STRING	lpad(string str, int len, string pad) rpad(string str, int len, string pad)	將str進行用pad進行左補足到len位 將str 進行用pad進行右補足到len位
ARRAY	split(string str, string pat)	按照pat字串分割str，會回傳分割后的字串陣列
INT	find_in_set(string str, string strList) find_in_set(‘ab’,‘aa,ab,ac’)	回傳str在strlist第一次出現的位置，strlist是用逗號分割的字串，如果沒有找該str字符，則回傳0
INT	instr(string str, string substr) instr(“abcde”,“ab”)	回傳substr在str中第一次出現的位置，未出現則回傳0（如果引數為NULL則回傳NULL；位置從1開始）

2.5 統計函式

回傳型別	簽名	描述
INT	count(*), count(expr), count(DISTINCT expr[, expr_.])	count(*)統計檢索出的行的個數，包括NULL值的行；count(expr)回傳指定欄位的非空值的個數；count(DISTINCTexpr[, expr_.])回傳指定欄位的不同的非空值的個數
DOUBLE	sum(col), sum(DISTINCT col)	sum(col)統計結果集中col的相加的結果；sum(DISTINCT col)統計結果中col不同值相加的結果
DOUBLE	avg(col), avg(DISTINCT col)	avg(col)統計結果集中col的平均值；avg(DISTINCT col)統計結果中col不同值相加的平均值
DOUBLE	min(col) max(col)	統計結果集中col欄位的最小值 統計結果集中col欄位的最大值
DOUBLE	var_pop(col) var_samp (col)	統計結果集中col非空集合的總體方差 統計結果集中col非空集合的樣本變數
DOUBLE	stddev_pop(col) stddev_samp (col)	統計結果集中col非空集合的總體標準差 統計結果集中col非空集合的樣本標準差
DOUBLE	percentile(BIGINT col, p)	求準確的第p個百分位數，p必須介于0和1之間，但是col欄位目前只支持整數，不支持浮點數型別
ARRAY	percentile(BIGINT col, array(p1 [, p2]…))	功能和上述類似，之后后面可以輸入多個百分位數，回傳型別也為array，其中為對應的百分位數

2.6 復合型別構建訪問函式

回傳型別	簽名	描述
MAP	map (key1, value1, key2, value2, …)	根據輸入的key和value對構建map型別
STRUCT	struct(val1, val2, val3, …)	根據輸入的引數構建結構體struct型別
ARRAY	array(val1, val2, …)	根據輸入的引數構建陣列array型別
…	A[n]	回傳陣列A中的第n個變數值，陣列的起始下標為0，
…	M[key]	回傳map型別M中，key值為指定值的value值
…	S.x	回傳結構體S中的x欄位
INT	size(Map<K.V>) size(Array)	回傳map型別的長度 回傳array型別的長度
…	explode（map	array)
Array	collect_set ( col)	對col行變列并 去重，
Array	collect_list ( col)	對col行變列并 不去重，
Array	map_keys(map)	取map型別的所有Key
Array	map_values(map)	取map型別的所有value
T	array_contains(array,obj)	判斷指定的obj 是否村在陣列中

3 Select 陳述句結構

語法如下：

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

hive陳述句的執行順序：
from -->where --> select --> group by -->聚合函式–> having --> order by -->limit

• 全表查詢 （select * from 表名）

select * from sales_info;

• 選擇特定列查詢(select col1,col2,… from 表名 )

select sku_id from sales_info;

• 列重命名（select col1 [as] ,… from 表名）

select sku_id as id from sales_info;

• 回傳陣列的第一個元素(索引從0開始)

select id_array[0] from sales_info;

• 查看陣列中的每個元素

select explode(id_array) from sales_info;

• 陣列各元素分行顯示，并要有與只對應的sku_id,sku_name

select sku_id,sku_name, id_list from sales_info lateral view explode(id_array) ids as id_list; 
--lateral view explode(陣列欄位) 虛擬表名 as 虛擬表欄位

• 把test_hv 表 以sku_id 分組 id_list 轉一列顯示

select sku_id,collect_set(id_list) from test_HV group by sku_id; --set 集合去重 
select sku_id,collect_list(id_list) from test_HV group by sku_id;--list 串列不去重

• 陣列轉與字串互換

select *， split(concat_ws(',',id_array),',') from sales_info;

• 回傳Map中key 為 id 的值

select state_map['id'] from mapkeys1

• 把map中Key ，value 分兩列顯示

select explode(state_map) from mapkeys1

• map各元素分行顯示，并要有與只對應的sku_id,sku_name

select sku_id,sku_name, infokey,infovalue from mapkeys1 lateral view explode(state_map) infos as infokey,infovalue;

– 查看所有map中的value

select map_values(state_map) from mapkeys1;

• map中不存在指定Key 時 顯示 “無”

select state_map['id'],if( state_map['user'] is null , '無',state_map['user']) from mapkeys1;

• 回傳結構體中為age元素值

select basic_info.age from test_student;

3.1 Java正則

指代內容的符號：

• \w 指代字符（下劃線，數字 ，字母）， \W指代非字符（也就是和\w反過來）
• \d 指代數字（0-9）， \D指代非數字
• . 指代任意字符
• \s 指代各種空白， \S同上，
• [內容] 包含指定內容，如 [A-Za-z]
• [^內容] 不包含指定的內容，如 [^\d]指除數字以外的任何字符，這里等同于\D，

指代個數的符號：

符號	代表個數
*	0-n
+	1-n
?	0-1
{n}	n
{n,}	至少n
{，n}	最多n
{n,m}	n-m

舉例補充：

符號	含義
^	字符開頭
$	字符結尾
\d{11}	11個數字
[a-zA-Z0-9]{8,}	有至少8個字母或者數字（而且是連續的）
^A.*	以A開頭的任何字符
.*8$	以8結尾的任何字符
[A], A	A,A

3.2 Select——Where

使用WHERE 子句，將不滿足條件的行過濾掉，where 后是 關系運算 和 邏輯運算的不同組合，

– Sale_info表中Sku_Name 包含 字母A的

select * from sales_info where sku_name like '%A%';

–Sale_info表中 id_array 有id含有8的

select * from sales_info where concat_ws("",id_array) like '%8%'; 
select * from sales_info where concat_ws("",id_array) RLIKE '[8]'; 
select * from sales_info where concat_ws("",id_array) Regexp '8';

–mapkeys1 表中 state_map 中key 含有id的

select * from mapkeys1 where array_contains(map_keys(state_map),"id");

–mapkeys1 表中 state_map user_name 中包含zhang的

select * from mapkeys1 where state_map["user_name"] like "%zhang%";

–mapkeys1 表中姓張的并且名字只有兩個字的

select * from mapkeys1 where state_map["user_name"] like "張_"; 
select * from mapkeys1 where state_map["user_name"] Rlike "^張.";'^張.' 
select * from mapkeys1 where state_map["user_name"] Rlike "^張.{1}";

–mapkeys1 表中 state_map欄位的 values 包含 A 的（不區分大小寫）

select * from mapkeys1 where upper(concat_ws('',map_values(state_map))) like '%A%';

3.3 Select——Group By & 聚合函式

having與where不同點

where	having
where針對表中的列發揮作用，查詢資料	having針對查詢結果中的列發揮作用，篩選資料
不能寫分組函式	可以使用分組函式
不受限制	只用于group by分組統計陳述句

–統計test_partition1 表中各 sku_class 的個數

select sku_class,count(*) from test_partition1 group by sku_class;

–統計test_student 表basic_info 各age的人數 并顯示大于1 的年齡組

select basic_info.age, count(*) renshu from test_student 
group by basic_info.age 
having renshu > 1;

–mapkeys1 以map列中指定key 對應的值進行分組 并統計記錄條數

select state_map["id"],sum(sku_id),count(1) from mapkeys1 group by state_map["id"];

–統計test_partition1 表中各磁區記錄并且找到大于1的磁區

select sku_class,count(*) from test_partition1 
group by sku_class 
having count(*) > 1;

3.4 Select——Order By

	order by	sort by	distribute by	cluster by
作用	order by會對輸入做全域排序	sort by 是單獨在各自的reduce中進行排序	控制map 中的輸出	在 reduce中是如何進行劃分的
缺點	只有一個Reduce，當輸入規模較大時，消耗較長的計算時間	不能保證全域有序	只是分，沒有排序	只能做升序

• order by (全域排序asc ,desc)

–單列排序

select * from sales_info order by sku_id;

–多列排序

select * from order_data1 order by quantity desc , sales desc limit 20 

--limit 可以進行區間取數的 語法 limit n ,m 結果： 從n+1 開始，取m條記錄

–別名排序

select order_id ,sales as jine from order_data1 order by jine limit 10;

–默認的升序，默認是的NULLS FIRST

select * from sales_info order by sku_id nulls last;

4 表關聯

這里就是重要知識點啦，熟悉MySQL的朋友應該會知道不管是日常使用還是面試提問都會經常出現表關聯的相關知識，所以請大家一定認真閱讀，

我們先說一下Hive join的限制，其實很明顯的一點是，Hive支持類似 mysql 的大部分Join 操作，但是注意只支持等值連接，并不支持不等連接，原因是Hive陳述句最終是要轉換為MapReduce 程式來執行的，但是 MapReduce程式很難實作這種不等判斷的連接方式，

另外，在on后面的運算式中，是不支持or的，

好，接著進入重點，

如果資料存盤在多個表中，怎樣用單條SELECT陳述句檢索出資料？

答案是使用聯結，簡單地說，聯結是一種機制，用來在一條SELECT陳述句中關聯表，因此稱之為聯結，使用特殊的語法，可以聯結多個表回傳一組輸出，聯結在運行時關聯表中正確的行，

4.1 內部鏈接

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
  ON vendors.vend_id = products.vend_id;

4.2 聯結多個表

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
  AND orderitems.prod_id = products.prod_id
  AND order_num = 123;

此種關聯處理是相當耗費資源的，

4.3 創建高級聯結

通過AS關鍵字使用別名，兩點好處：縮短SQL陳述句；允許在單挑SELECT陳述句中多次使用相同的表，

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
  AND p2.prod_id = 'DTNTR'

如上代碼中使用兩個相同的表分別作為p1, p2，WHERE中選取p2中產品ID為DTNTR的列，此時p2.vend_id就是DTNTR的生產商；p1.vend_id = p2.vend_id 此時意味在p1選取DTNTR的生產商，

4.4 外部聯結 OUTER JOIN

這個其實也是考點，

SQL中的關聯/查詢的方式一共有4種，分別是INNER JOIN（內連接，INNER可以省略）、LEFT OUTER JOIN（左連接，OUTER可以省略）RIGHT OUTER JOIN（右連接，OUTER可以省略）以及FULL JOIN（全連接），

連接方式	含義
INNER JOIN	只保留兩張表中完全匹配的結果集，
LEFT JOIN	回傳左表所有的行，而右表中沒有匹配的記錄則會表示為null，
RIGHT JOIN	回傳右表所有的行，而左表中沒有匹配的記錄則會表示為null，
FULL JOIN	在兩張表進行連接查詢時，回傳左表和右表中所有沒有匹配的行，

值得注意的是，MySQL中是不支持全連接的，不過hive是支持的，

SELECT id, name  FROM t1
full JOIN t2 ON t1.id = t2.id

-- 去掉重復值
SELECT distinct id, name  FROM t1
full JOIN t2 ON t1.id = t2.id

4.5 組合查詢 UNION

UNION對兩個結果集進行并集操作，不包括重復行，同時進行默認規則的排序，

如果說上述提到的各種連接方式概括為一種形式的話，那就是表與表之間左右連接，而UNION則是上下連接，

UNION的使用很簡單，所需做的只是給出每條SELECT陳述句，在各條陳述句之間放上關鍵字UNION，

select distinct customer_id from test_join_order 
union 
select distinct customer_id from user_info; 

--disctinct 使用與否并不影響最終結果， union 有去重功能 
select customer_id from test_join_order 
union 
select customer_id from user_info;

UNION和UNION ALL的區別
簡單來講，就是使用UNION會洗掉重復的記錄，而UNION ALL則不會;UNION會進行排序，而UNION ALL 不會；從效率上看，UNION ALL執行效率高于UNION，如果沒有去重的需求，就選用UNION ALL，

4.6 取交集 Intersect

Intersect 對兩個結果集進行交集操作，不包括重復行，同時進行默認規則的排序，

select customer_id from test_join_order 
Intersect 
select customer_id from user_info;

4.7 求差集 Minus

Minus 對兩個結果集進行差操作(第一個減去第二個），不包括重復行，同時進行默認規則的排序，

select customer_id from test_join_order Minus select customer_id from user_info

5 使用視圖

• 視圖是一個虛表，一個邏輯概念，可以跨越多張表，表是物理概念，資料放在表中，視圖是虛表，操作視圖和操作表是一樣的，所謂虛，是指視圖下并不存取資料，
• 視圖是建立在已有表的基礎上，視圖賴以建立的這些表稱為基表，
• 視圖也可以建立再已有的視圖上，
• 視圖可以簡化復雜的查詢，

5.1 創建視圖

語法：

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name -- 視圖名稱 
	[(column_name [COMMENT column_comment], ...) ] --列名 
	[COMMENT view_comment] --視圖注釋 
	[TBLPROPERTIES (property_name = property_value, ...)] --額外資訊 
	AS SELECT ...;

–不指定列創建視圖

create view test_view as 
select a.order_id, a.order_date,b.customer_id,b.age,b.sex from join_order as a 
inner join user_info as b on a.customer_id = b.customer_id 
where year=2018 and month=1;

–指定列名

create view test_view_liename(id,order_date,customer_id,age,sex) as 
select a.order_id, a.order_date,b.customer_id,b.age,b.sex from join_order as a 
inner join user_info as b on a.customer_id = b.customer_id 
where year=2018 and month=1;

–以視圖創建視圖

create view test_fu_view as 
select * from test_view_liename;

5.2 查看視圖

–查看視圖是否創建成功

show tables;

–查看test_view視圖結構

desc test_view;

–查看視圖的詳細資訊

desc formatted test_view;

5.3 洗掉視圖

語法：DROP VIEW [IF EXISTS] [db_name.]view_name;
–洗掉test_view 視圖

drop view test_view;

洗掉視圖時，如果被洗掉的視圖被其他視圖所參考，洗掉時不會發出警告，但是參考該視圖的其他視圖已經失效，需要進行重建或者刪除，

5.4 修改視圖

語法：ALTER VIEW [db_name.]view_name AS new_select;

其中：

• 在修改指定列的視圖后，指定的列名失效，
• 當基表的資料記錄增刪時，視圖也會生變化，
• 當基表洗掉視圖參考的列后，視圖會失效，
• 當基表添加列后，視圖還是原有的列，對新列不做參考，
• 當基表或基視圖被洗掉后，此視圖失效，

alter view test_fu_view as 
select a.order_id,b.customer_id from join_order as a 
inner join user_info as b on a.customer_id = b.customer_id 
where year=2018 and month=1;

5.5 視圖總結

1. 視圖是只讀的，不能用作 LOAD / INSERT / ALTER 的目標；
2. 在創建視圖時候視圖就已經固定，對基表的增加列操作將不會反映在視圖，洗掉視圖參考的列，圖會失效；
3. 洗掉基表并不會洗掉視圖，需要手動洗掉視圖；
4. 視圖可能包含 ORDER BY 和 LIMIT 子句，如果參考視圖的查詢陳述句也包含這類子句，其執行優先級低于視圖對應字句
5. 創建視圖時，如果未提供列名，則將從 SELECT 陳述句中自動派生列名；
6. 創建視圖時，如果 SELECT 陳述句中包含其他運算式，例如 x + y，則列名稱將以C0，C1 等形式生成，

6 視窗函式

這個很明顯，又是一個重頭戲，也是日常應用和面試提問經常會出現的知識點，真希望大家看過我之前寫過的這篇博客，雖說MySQL和Hive有點點區別，但只要你理解了MySQL，那還擔心不懂Hive？

語法：分析函式(如：sum(), max(), row_number()...) + 視窗子句(over函式)，

6.1 視窗子句

這里先講下一些視窗子句，后面來詳講視窗函式中的高級函式，

視窗子句	備注
PRECEDING	往前 n preceding 從當前行向前n行
FOLLOWING	往后 n following 從當前行向后n行
CURRENT ROW	當前行
UNBOUNDED	起點
UNBOUNDED PRECEDING	表示該視窗最前面的行（起點）
UNBOUNDED FOLLOWING	表示該視窗最后面的行（終點）

舉例如下：分析overData表中每三天（前一天，當前天，后一天）的銷售額合計（保留兩位小數點）

select order_date,sales,quantity, round(sum(sales) over(order by order_date rows 
between 1 preceding and 1 FOLLOWING ),2) sumday3 from overData;

接下來，我也是直接把我這篇博客中視窗函式的部分辦了過來，照用哈哈，

🔴 視窗函式的視窗表示范圍，可以理解為將原資料劃分范圍，也就是分組，然后用函式實作某些目的，相比于GROUP BY , 視窗函式不會減少原來的行數，

• 語法: SELECT 視窗函式 OVER (PARTITION BY 用于分組的列名 ORDER BY 用于排序的列)
• 專用視窗函式：rank(), dense_rank(), row_numer()

當然啦，那些聚合函式在這里要用也可以用，

SELECT *,
	rank() over (PARTITION BY class_id ORDER BY SCORE DESC ) AS ranking
FROM class

6.2 rank()、dense_rank()和row_number()

三個函式的主要區別是如何處理并列情況：

• rank()中的并列情況會占用下一個名詞的位置, 1,1,1,4
• dense_rank() 不會占用下一個名詞 1,1,1,2
• row_number()中，會忽略并列的情況, 1,2,3,4

6.3 CUME_DIST()和PERCENT_RANK()

🔴 CUME_DIST()是一個視窗函式，它回傳一組值中值的累積分布，它表示值小于或等于行的值除以總行數的行數，重復的列值接收相同的CUME_DIST()值，計算的時候，取重復值的最后一行的位置，

SELECT name,
    score,
    ROW_NUMBER() OVER (ORDER BY score) row_num,
    CUME_DIST() OVER (ORDER BY score) cume_dist_val
FROM scores; 

輸出結果：

name	score	row_num	cume_dist_val
Jones	55	1	0.2
Williams	55	2	0.2
Brown	62	3	0.4
Taylor	62	4	0.4
Thomas	72	5	0.6
Wilson	72	6	0.6
Smith	81	7	0.7
Davies	84	8	0.8
Evans	87	9	0.9
Johnson	100	10	1

🔴 PERCENT_RANK()和CUME_DIST()一樣，是計算某個值在一組有序的資料中累計的分布，但不同在于計算分布結果的方法：(rank - 1) / (total_rows - 1)，在此公式中，rank是指定行的等級，total_rows是要計算的行數，復的列值接收相同的CUME_DIST()值，計算的時候，取重復值的第一行的位，具體如下：

SELECT name,
    score,
    ROW_NUMBER() OVER (ORDER BY score) row_num,
    PERCENT_RANK() OVER (ORDER BY score) cume_dist_val
FROM scores; 

輸出結果：

name	score	row_num	percent_rank_val
Jones	55	1	0.2
Williams	55	2	0.2
Brown	62	3	0.4
Taylor	62	4	0.4
Thomas	72	5	0.6
Wilson	72	6	0.6
Smith	81	7	0.7
Davies	84	8	0.8
Evans	87	9	0.9
Johnson	100	10	1

6.4 FIRST_VALUE()和LAST_VALUE()

🔴 FIRST_VALUE()是一個視窗函式，允許您選擇視窗框架，磁區或結果集的第一行，

SELECT employee_name,
    hours,
    FIRST_VALUE(employee_name) OVER (
        ORDER BY hours
    ) least_over_time
FROM overtime; 

輸出結果：

employee_name	hours	least_over_time
Steve Patterson	29	Steve Patterson
Diane Murphy	37	Steve Patterson
Jeff Firrelli	40	Steve Patterson
Gerard Bondur	47	Steve Patterson
Loui Bondur	49	Steve Patterson
William Patterson	58	Steve Patterson

在此示例中，ORDER BY子句按結果集對行中的行進行了按小時排序，并FIRST_VALUE()選擇了第一行，表示加班時間最短的員工，

如果反過來想取加班時間最長的員工，則將FIRST_VALUE()改成LAST_VALUE()

6.5 LAG()和LEAD()

🔴 LAG()函式：從同一結果集中的當前行訪問上n行的資料，

語法：LAG(param1, param2, param3)

• param1 欄位名
• param2 前面第幾行
• param3 當前面第幾行沒有值時范圍的值，沒有設定的話就自動回傳NULL，

WITH productline_sales AS (
    SELECT productline,
           YEAR(orderDate) order_year,
           ROUND(SUM(quantityOrdered * priceEach),0) order_value
    FROM orders
    INNER JOIN orderdetails USING (orderNumber)
    INNER JOIN products USING (productCode)
    GROUP BY productline, order_year
)
SELECT productline, 
    order_year, 
    order_value,
    LAG(order_value, 1) OVER (
        PARTITION BY productLine
        ORDER BY order_year
    ) prev_year_order_value
FROM productline_sales; 

輸出結果：

productline	order_year	order_value	prev_year_order_value
Classic Cars	2013	1374832	NULL
Classic Cars	2014	1763137	1374832
Classic Cars	2015	715954	1763137
Motorcycles	2013	348909	NULL
Motorcycles	2014	527244	348909
Motorcycles	2015	245273	527244

🔴 LEAD()函式：從同一結果集中的當前行訪問后n行的資料，

將上述代碼中的LAG函式改為LEAD函式，

此時的輸出結果：

productline	order_year	order_value	prev_year_order_value
Classic Cars	2013	1374832	1763137
Classic Cars	2014	1763137	715954
Classic Cars	2015	715954	NULL
Motorcycles	2013	348909	527244
Motorcycles	2014	527244	245273
Motorcycles	2015	245273	NULL

6.6 NTH_VALUE()

🔴 NTH_VALUE()是一個視窗函式，允許您從有序行集中的第N行獲取值，

SELECT employee_name,
    salary,
    NTH_VALUE(employee_name, 2) OVER  (
        ORDER BY salary DESC
    ) second_highest_salary
FROM basic_pays; 

輸出結果：

employee_name	salary	second_highest_salary
Larry Bott	11798	NULL
Gerard Bondur	11472	Gerard Bondur
Pamela Castillo	11303	Gerard Bondur
Barry Jones	10586	Gerard Bondur
George Vanauf	10563	Gerard Bondur

6.7 NTILE()

🔴 NTILE()函式將排序磁區中的行劃分為特定數量的組，從每個組分配一個從一開始的桶號，對于每一行，NTILE()函式回傳一個桶號，表示行所屬的組，

SELECT val, 
    NTILE(4) OVER (
        ORDER BY val
    ) group_no
FROM ntileDemo;

輸出結果：

val	group_no
1	1
2	1
3	1
4	2
5	2
6	3
7	3
8	4
9	4

從輸出中可以看出，第一組有三行，而其他組有兩行，
也就是說，如果不平均，余n個資料，那排在前n的組就會各多一個，

7 子查詢

注意點：

• hive 3.1 支持select，from，where 子句中的子查詢
  select 子查詢限制：不支持 if / case when 里的子查詢
  where 子查詢限制：
  1.IN/NOT IN 子查詢只能選擇一列，
  2.EXISTS/NOT EXISTS 必須有一個或多個相關謂詞，
  3.對父查詢的參考僅在子查詢的WHERE子句中支持，
• 集合中如果含null資料，不可使用not in, 可以使用in
• 主查詢和子查詢可以不是同一張表

舉例如下：
–sales_info 表中 id_array欄位每個元素出現的次數

select id,count(*) count from ( 
	select explode(id_array) id from sales_info) sub 
group by id;

簡單講就是，在hive3.x中支持在SELECT或者FROM或者WHERE出出現另外一個完整的SELECT陳述句，但注意不能超出以上所講的限制，

8 抽樣查詢

在大資料時代，如果你針對全部資料進行分析的話，往往十分費時，也會占用大量資源，因此一般情況下會選擇抽取一小部分資料用于分析或者建模操作，

8.1 隨機抽樣

使用rand()函式與distribute by ，order by ,sort by 合用進行隨機抽樣，limit關鍵字限制抽樣回傳的資料，

select * from order_data1 order by rand() limit 20; 

--distribute和sort關鍵字可以保證資料在map和reduce階段是隨機分布的 
select * from order_data1 distribute by rand() sort by rand() limit 20;

8.2 資料塊抽樣

• tablesample(n percent) 根據hive表資料的大小（不是行數，而是資料大小）按比例抽取資料，并保存到新的hive表中，
  由于在HDFS塊層級進行抽樣，所以抽樣粒度為塊的大小，例如如果塊大小為128MB，即使輸入的n%僅為50MB，也會得到128MB的資料，

--select陳述句不能帶where條件且不支持子查詢 
create table sample_new as select * from order_data1 tablesample(10 percent) 
--10%的資料

如果希望在不同的塊中抽取相同的資料，可以改變下面的引數：

set hive.sample.seednumber=<INTEGER>;

• tablesample(nM) 指定抽樣資料的大小，單位為M 與PERCENT抽樣具有一樣的限制，因為該語法僅將百分比改為了具體值，但沒有改變基于塊抽樣這一前提條件，

SELECT * FROM order_data1 TABLESAMPLE(1M) ;

• tablesample(n rows) 指定抽樣資料的行數，其中n代表每個map任務均取n行資料

SELECT * FROM order_data1 TABLESAMPLE(10 ROWS);

8.3 分桶抽樣

hive中分桶其實就是根據某一個欄位Hash取模，放入指定資料的桶中，

語法：TABLESAMPLE(BUCKET x OUT OF y)
y必須是table總bucket數的倍數或者因子，hive根據y的大小，決定抽樣的比例，

未分桶的表

--將表隨機分成10組，抽取其中的第一個桶的資料 
select * from order_data1 tablesample(bucket 1 out of 10 on rand())

已分桶的表

-- 對第一個桶抽樣一半 
select * from test_bucket tablesample(bucket 1 out of 6 on sku_id) --總桶數/6 = 0.5 從第一個桶開始取，取0.5個桶的資料 

--假設是6個桶 
select * from test_bucket tablesample(bucket 1 out of 3 on sku_id) --從第一個桶開始 取，取2個桶的資料，第二個桶是 1+3，也就是第4個桶，

9 自定義函式

查看系統內置函式 show functions;
顯示內置函式用法 desc function 函式名;
詳細顯示內置函式用法 desc function extended 函式名;

自定義函式分為三個類別：
UDF(User Defined Function)：一進一出（upper(), lower())
UDAF(User Defined Aggregation Function)：聚集函式，多進一出(例如count/max/min)
UDTF(User Defined Table Generating Function)：一進多出，如lateral view explode()

但這里需要說明的是，創建自定義函式需要用java來撰寫，而不是用傳統的SQL來完成，感興趣的朋友可以再去了解，

10 一些技巧或建議

在這一部分，會列舉一些更好用方法，來代替我們平時常用實作方法，或者是平時使用HQL時的一些建議，

10.1 去重技巧——?group by來替換distinct

select distinct customer_id 
from test_join_order; 

select customer_id 
from test_join_order; group by customer_id

10.2 聚合技巧——利?窗?函式grouping sets、cube、rollup

--數量分布 
select quantity,count(*) from test_join_order 
group by quantity 

--性別分布 
select sex,count(distinct user_info.customer_id) from test_join_order 
inner join user_info on test_join_order.customer_id = user_info.customer_id 
group by sex 

--年齡分布 
select age,count(distinct user_info.customer_id) from test_join_order 
inner join user_info on test_join_order.customer_id = user_info.customer_id 
group by age 

--缺點：要分別寫三次SQL，需要執?三次，重復?作，且費時 

--優化方法(聚合結果均在同?列，分類欄位?不同列來進?區分) 
select quantity,sex,age,count(distinct user_info.customer_id) 
select * from test_join_order 
inner join user_info on test_join_order.customer_id = user_info.customer_id 
group by quantity,sex,age 
grouping sets(quantity,sex,age); 

10.3 cube：根據group by 維度的所有組合進?聚合

--數量、性別、年齡的各種組合的?戶分布 
SELECT quantity,sex,age, count(distinct user_info.customer_id) 
from test_join_order 
inner join user_info on test_join_order.customer_id = user_info.customer_id 
group by quantity,sex,age 
GROUPING SETS (quantity,sex,age,(quantity,sex),(quantity,age),(sex,age), (quantity,sex,age));

--優化寫法 
SELECT quantity,sex,age, count(distinct user_info.customer_id) 
from test_join_order 
inner join user_info on test_join_order.customer_id = user_info.customer_id 
group by quantity,sex,age with cube;

10.4 rollup：以最左側的維度為主，進?層級聚合，是cube的?集

SELECT a.dt, 
	sum(a.year_amount), 
	sum(a.month_amount) 
FROM( 
	SELECT substr(order_date,1,4) as dt, 
		sum(sales) year_amount, 
		0 as month_amount 
	FROM order_data1 
	GROUP BY substr(order_date,1,4) 
	UNION ALL 
	SELECT substr(order_date,1,7) as dt, 
		0 as year_amount, 
		sum(sales) as month_amount 
	FROM order_data1 
	GROUP BY substr(order_date,1,7) 
	) as a 
group by dt; 

--優化寫法 
SELECT year(order_date) as year, 
	month(order_date) as month, 
	sum(sales) 
FROM order_data1 
GROUP BY year(order_date), month(order_date) 
with rollup;

10.5 表連接優化

• ?表在前，?表在后
  Hive假定查詢中最后的?個表是?表，它會將其它表快取起來，然后掃描最后那個表，
• 使?相同的連接鍵
  當對3個或者更多個表進?join連接時，如果每個on?句都使?相同的連接鍵的話，那么只會產??個 MapReduce job，
• 盡早的過濾資料
  減少每個階段的資料量，對于磁區表要加磁區，同時只選擇需要使?到的欄位，
  邏輯過于復雜時，引?中間表

10.6 如何解決資料傾斜

資料傾斜的表現：
任務進度?時間維持在99%（或100%），查看任務監控??，發現只有少量（1個或?個）reduce?任務未完成，因為其處理的資料量和其他reduce差異過?，

資料傾斜的原因與解決辦法：

1. 空值產?的資料傾斜
   解決：如果兩個表連接時，使?的連接條件有很多空值，建議在連接條 件中增加過濾，

on a.user_id=b.user_id and a.user_id is not null

2. ??表連接(其中?張表很?，另?張表?常?)
   解決：將?表放到記憶體?，在map端做Join

select /*+ mapjoin(a) */ a.user_id,a.user_name from user_info a join join_order b where b.customer_id = a.customer_id;

3. 兩個表連接條件的欄位資料型別不?致
   解決：將連接條件的欄位資料型別轉換成?致的

on a.user_id = cast(b.user_id as string)

結束語

題外話：這幾天非常開心，自己的幾篇博客都上了排行榜，其中更有兩篇上了全站綜合熱榜和大資料領域排行榜第一，

并且今天這篇博客呢，也剛好是我在CSDN發布的第50篇博客，正好湊個半百，也順勢肝出這一篇巨無敵長的博客，或者可以算是對CSDN平臺的一次回饋？

你那么本篇博客大家也可以當作是一份有關Hive語言的參考書籍，“麻雀雖小五臟俱全”，對于新手而言，也可以在沒事的時候或者有疑問的時候翻閱一下，

以后我會出更多有用的文章，大家就三連加關注支持一下吧！當然我更希望的是大家能在這篇博客當中有所得有所獲，后續我還會針對Hive出相關的實戰專案，盡情期待，

CSDN@報告，今天也有好好學習