最強最全面的Hive SQL開發指南，超四萬字全面解析

五分鐘學大資料發表於2021-11-30

HiveSQL

本文整體分為兩部分，第一部分是簡寫，如果能看懂會用，就直接從此部分查，方便快捷，如果不是很理解此SQL的用法，則檢視第二部分，是詳細說明，當然第二部分語句也會更全一些！

第一部分：

hive模糊搜尋表：show tables like '*name*';
檢視錶結構資訊：desc table_name;
檢視分割槽資訊：show partitions table_name;
載入本地檔案：load data local inpath '/xxx/test.txt' overwrite into table dm.table_name;
從查詢語句給table插入資料：insert overwrite table table_name partition(dt) select * from table_name;
匯出資料到本地系統：insert overwrite local directory '/tmp/text' select a.* from table_name a order by 1;
建立表時指定的一些屬性：

欄位分隔符：row format delimited fields terminated by '\t'
行分隔符：row format delimited lines terminated by '\n'
檔案格式為文字型儲存：stored as textfile

命令列操作：hive -e 'select table_cloum from table' 執行一個查詢,在終端上顯示mapreduce的進度，執行完畢後，最後把查詢結果輸出到終端上，接著hive程式退出，不會進入互動模式

hive -S -e 'select table_cloum from table' -S，終端上的輸出不會有mapreduce的進度，執行完畢，只會把查詢結果輸出到終端上。

hive修改表名：alter table old_table_name rename to new_table_name;
hive複製表結構：create table new_table_name like table_name;
hive新增欄位：alter table table_name add columns(columns_values bigint comment 'comm_text');
hive修改欄位：alter table table_name change old_column new_column string comment 'comm_text';
刪除分割槽：alter table table_name drop partition(dt='2021-11-30');
新增分割槽：alter table table_name add partition (dt='2021-11-30');
刪除空資料庫：drop database myhive2;
強制刪除資料庫：drop database myhive2 cascade;
刪除表：drop table score5;
清空表：truncate table score6;
向hive表中載入資料

直接向分割槽表中插入資料： insert into table score partition(month ='202107') values ('001','002','100');
通過load方式載入資料： load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');
通過查詢方式載入資料： insert overwrite table score2 partition(month = '202106') select s_id,c_id,s_score from score1;
查詢語句中建立表並載入資料： create table score2 as select * from score1;
在建立表是通過location指定載入資料的路徑： create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';
export匯出與import 匯入 hive表資料（內部表操作）：

create table techer2 like techer; --依據已有表結構建立表

export table techer to '/export/techer';

import table techer2 from '/export/techer';

最全面Hive SQL開發指南PDF版

hive表中資料匯出

insert匯出

將查詢的結果匯出到本地： insert overwrite local directory '/export/servers/exporthive' select * from score;

將查詢的結果格式化匯出到本地：insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;

將查詢的結果匯出到HDFS上(沒有local)：insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;

Hadoop命令匯出到本地： dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;
hive shell 命令匯出

基本語法：（hive -f/-e 執行語句或者指令碼 > file） hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt

hive -f export.sh > /export/servers/exporthive/score.txt

export匯出到HDFS上： export table score to '/export/exporthive/score';

Hive查詢語句

GROUP BY 分組：select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85; 對分組後的資料進行篩選，使用 having
join 連線：inner join 內連線；left join 左連線；right join 右連結；full join 全外連結。
order by 排序：ASC（ascend）: 升序（預設） DESC（descend）: 降序
sort by 區域性排序：每個MapReduce內部進行排序，對全域性結果集來說不是排序。
distribute by 分割槽排序：類似MR中partition，進行分割槽，結合sort by使用

Hive函式

1. 聚合函式

指定列值的數目：count()
指定列值求和：sum()
指定列的最大值：max()
指定列的最小值：min()
指定列的平均值：avg()
非空集合總體變數函式：var_pop(col)
非空集合樣本變數函式：var_samp (col)
總體標準偏離函式：stddev_pop(col)
分位數函式：percentile(BIGINT col, p)
中位數函式：percentile(BIGINT col, 0.5)

2. 關係運算

A LIKE B： LIKE比較，如果字串A符合表示式B 的正則語法，則為TRUE
A RLIKE B：JAVA的LIKE操作，如果字串A符合JAVA正規表示式B的正則語法，則為TRUE
A REGEXP B：功能與RLIKE相同

3. 數學運算

支援所有數值型別：加(+)、減(-)、乘(*)、除(/)、取餘(%)、位與(&)、位或(|)、位異或(^)、位取反(~)

4. 邏輯運算

支援：邏輯與(and)、邏輯或(or)、邏輯非(not)

5. 數值運算

取整函式：round(double a)
指定精度取整函式：round(double a, int d)
向下取整函式：floor(double a)
向上取整函式：ceil(double a)
取隨機數函式：rand(),rand(int seed)
自然指數函式：exp(double a)
以10為底對數函式：log10(double a)
以2為底對數函式：log2()
對數函式：log()
冪運算函式：pow(double a, double p)
開平方函式：sqrt(double a)
二進位制函式：bin(BIGINT a)
十六進位制函式：hex()
絕對值函式：abs()
正取餘函式：pmod()

6. 條件函式

if
case when
coalesce(c1,c2,c3)
nvl(c1，c2)

7. 日期函式

獲得當前時區的UNIX時間戳: unix_timestamp()
時間戳轉日期函式：from_unixtime()
日期轉時間戳：unix_timestamp(string date)
日期時間轉日期函式：to_date(string timestamp)
日期轉年函式：year(string date)
日期轉月函式：month (string date)
日期轉天函式: day (string date)
日期轉小時函式: hour (string date)
日期轉分鐘函式：minute (string date)
日期轉秒函式: second (string date)
日期轉周函式: weekofyear (string date)
日期比較函式: datediff(string enddate, string startdate)
日期增加函式: date_add(string startdate, int days)
日期減少函式：date_sub (string startdate, int days)

8. 字串函式

字串長度函式：length(string A)
字串反轉函式：reverse(string A)
字串連線函式: concat(string A, string B…)
帶分隔符字串連線函式：concat_ws(string SEP, string A, string B…)
字串擷取函式: substr(string A, int start, int len)
字串轉大寫函式: upper(string A)
字串轉小寫函式：lower(string A)
去空格函式：trim(string A)
左邊去空格函式：ltrim(string A)
右邊去空格函式：rtrim(string A)
正規表示式替換函式： regexp_replace(string A, string B, string C)
正規表示式解析函式: regexp_extract(string subject, string pattern, int index)
URL解析函式：parse_url(string urlString, string partToExtract [, string keyToExtract]) 返回值: string
json解析函式：get_json_object(string json_string, string path)
空格字串函式：space(int n)
重複字串函式：repeat(string str, int n)
首字元ascii函式：ascii(string str)
左補足函式：lpad(string str, int len, string pad)
右補足函式：rpad(string str, int len, string pad)
分割字串函式: split(string str, string pat)
集合查詢函式: find_in_set(string str, string strList)

9. 視窗函式

分組求和函式：sum(pv) over(partition by cookieid order by createtime) 有坑，加不加 order by 差別很大，具體詳情在下面第二部分。
分組內排序，從1開始順序排：ROW_NUMBER() 如：1234567
分組內排序，排名相等會在名次中留下空位：RANK() 如：1233567
分組內排序，排名相等不會在名次中留下空位：DENSE_RANK() 如：1233456
有序的資料集合平均分配到指定的數量（num）個桶中：NTILE()
統計視窗內往上第n行值：LAG(col,n,DEFAULT)
統計視窗內往下第n行值：LEAD(col,n,DEFAULT)
分組內排序後，截止到當前行，第一個值：FIRST_VALUE(col)
分組內排序後，截止到當前行，最後一個值: LAST_VALUE(col)
小於等於當前值的行數/分組內總行數：CUME_DIST()

以下函式建議看第二部分詳細理解下，此處僅簡寫，！

將多個group by 邏輯寫在一個sql語句中: GROUPING SETS
根據GROUP BY的維度的所有組合進行聚合：CUBE
CUBE的子集，以最左側的維度為主，從該維度進行層級聚合：ROLLUP

第二部分

1. 對資料庫的操作

建立資料庫:

create database if not exists myhive;
說明：hive的表存放位置模式是由hive-site.xml當中的一個屬性指定的 :hive.metastore.warehouse.dir

建立資料庫並指定hdfs儲存位置 :
create database myhive2 location '/myhive2';

修改資料庫:

alter  database  myhive2  set  dbproperties('createtime'='20210329');

說明：可以使用alter database 命令來修改資料庫的一些屬性。但是資料庫的後設資料資訊是不可更改的，包括資料庫的名稱以及資料庫所在的位置

檢視資料庫詳細資訊

檢視資料庫基本資訊
hive (myhive)> desc  database  myhive2;

檢視資料庫更多詳細資訊
hive (myhive)> desc database extended  myhive2;

刪除資料庫

刪除一個空資料庫，如果資料庫下面有資料表，那麼就會報錯
drop  database  myhive2;

強制刪除資料庫，包含資料庫下面的表一起刪除
drop  database  myhive  cascade;

2. 對資料表的操作

對管理表(內部表)的操作:

建內部表:

hive (myhive)> use myhive; -- 使用myhive資料庫
hive (myhive)> create table stu(id int,name string);
hive (myhive)> insert into stu values (1,"zhangsan");
hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi");  -- 一次插入多條資料
hive (myhive)> select * from stu;

hive建表時候的欄位型別:

分類	型別	描述	字面量示例
原始型別	BOOLEAN	true/false	TRUE
	TINYINT	1位元組的有符號整數 -128~127	1Y
	SMALLINT	2個位元組的有符號整數，-32768~32767	1S
	INT	4個位元組的帶符號整數	1
	BIGINT	8位元組帶符號整數	1L
	FLOAT	4位元組單精度浮點數1.0
	DOUBLE	8位元組雙精度浮點數	1.0
	DEICIMAL	任意精度的帶符號小數	1.0
	STRING	字串，變長	“a”,’b’
	VARCHAR	變長字串	“a”,’b’
	CHAR	固定長度字串	“a”,’b’
	BINARY	位元組陣列	無法表示
	TIMESTAMP	時間戳，毫秒值精度	122327493795
	DATE	日期	‘2016-03-29’
	INTERVAL	時間頻率間隔
複雜型別	ARRAY	有序的的同型別的集合	array(1,2)
	MAP	key-value,key必須為原始型別，value可以任意型別	map(‘a’,1,’b’,2)
	STRUCT	欄位集合,型別可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
	UNION	在有限取值範圍內的一個值	create_union(1,’a’,63)

對decimal型別簡單解釋下：
用法：decimal(11,2) 代表最多有11位數字，其中後2位是小數，整數部分是9位；如果整數部分超過9位，則這個欄位就會變成null；如果小數部分不足2位，則後面用0補齊兩位，如果小數部分超過兩位，則超出部分四捨五入
也可直接寫 decimal，後面不指定位數，預設是 decimal(10,0) 整數10位，沒有小數

建立表並指定欄位之間的分隔符

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';

row format delimited fields terminated by '\t' 指定欄位分隔符，預設分隔符為 '\001'
stored as 指定儲存格式
location 指定儲存位置

根據查詢結果建立表

create table stu3 as select * from stu2;

根據已經存在的表結構建立表

create table stu4 like stu2;

查詢表的結構

只查詢表內欄位及屬性
desc stu2;

詳細查詢
desc formatted  stu2;

查詢建立表的語句

show create table stu2;

對外部表操作

外部表因為是指定其他的hdfs路徑的資料載入到表當中來，所以hive表會認為自己不完全獨佔這份資料，所以刪除hive表的時候，資料仍然存放在hdfs當中，不會刪掉，只會刪除表的後設資料

構建外部表

create external table student (s_id string,s_name string) row format delimited fields terminated by '\t';

從本地檔案系統向表中載入資料

追加操作
load data local inpath '/export/servers/hivedatas/student.csv' into table student;

覆蓋操作
load data local inpath '/export/servers/hivedatas/student.csv' overwrite  into table student;

從hdfs檔案系統向表中載入資料

load data inpath '/hivedatas/techer.csv' into table techer;

載入資料到指定分割槽
load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201210);

注意：
1.使用 load data local 表示從本地檔案系統載入，檔案會拷貝到hdfs上
2.使用 load data 表示從hdfs檔案系統載入，檔案會直接移動到hive相關目錄下，注意不是拷貝過去，因為hive認為hdfs檔案已經有3副本了，沒必要再次拷貝了
3.如果表是分割槽表，load 時不指定分割槽會報錯
4.如果載入相同檔名的檔案，會被自動重新命名

對分割槽表的操作

建立分割槽表的語法

create table score(s_id string, s_score int) partitioned by (month string);

建立一個錶帶多個分割槽

create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);

注意：
hive表建立的時候可以用 location 指定一個檔案或者資料夾，當指定資料夾時，hive會載入資料夾下的所有檔案，當表中無分割槽時，這個資料夾下不能再有資料夾，否則報錯
當表是分割槽表時，比如 partitioned by (day string)，則這個資料夾下的每一個資料夾就是一個分割槽，且資料夾名為 day=20201123 這種格式，然後使用：msck repair table score; 修復表結構，成功之後即可看到資料已經全部載入到表當中去了

載入資料到一個分割槽的表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

載入資料到一個多分割槽的表中去

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

檢視分割槽

show  partitions  score;

新增一個分割槽

alter table score add partition(month='201805');

同時新增多個分割槽

 alter table score add partition(month='201804') partition(month = '201803');

注意：新增分割槽之後就可以在hdfs檔案系統當中看到表下面多了一個資料夾

刪除分割槽

 alter table score drop partition(month = '201806');

對分桶表操作

將資料按照指定的欄位進行分成多個桶中去，就是按照分桶欄位進行雜湊劃分到多個檔案當中去
分割槽就是分資料夾，分桶就是分檔案

分桶優點：
1. 提高join查詢效率
2. 提高抽樣效率

開啟hive的捅表功能

set hive.enforce.bucketing=true;

設定reduce的個數

set mapreduce.job.reduces=3;

建立桶表

create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

桶表的資料載入：由於桶表的資料載入通過hdfs dfs -put檔案或者通過load data均不可以，只能通過insert overwrite 進行載入
所以把檔案載入到桶表中，需要先建立普通表，並通過insert overwrite的方式將普通表的資料通過查詢的方式載入到桶表當中去

通過insert overwrite給桶表中載入資料

insert overwrite table course select * from course_common cluster by(c_id);  -- 最後指定桶欄位

修改表和刪除表

修改表名稱

alter  table  old_table_name  rename  to  new_table_name;

增加/修改列資訊

查詢表結構
desc score5;

新增列
alter table score5 add columns (mycol string, mysco string);

更新列
alter table score5 change column mysco mysconew int;

刪除表操作

drop table score5;

清空表操作

truncate table score6;

說明：只能清空管理表，也就是內部表；清空外部表，會產生錯誤

注意：truncate 和 drop：
如果 hdfs 開啟了回收站，drop 刪除的表資料是可以從回收站恢復的，表結構恢復不了，需要自己重新建立；truncate 清空的表是不進回收站的，所以無法恢復truncate清空的表
所以 truncate 一定慎用，一旦清空將無力迴天

向hive表中載入資料

直接向分割槽表中插入資料

insert into table score partition(month ='201807') values ('001','002','100');

通過load方式載入資料

 load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

通過查詢方式載入資料

insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;

查詢語句中建立表並載入資料

create table score2 as select * from score1;

在建立表是通過location指定載入資料的路徑

create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';

export匯出與import 匯入 hive表資料（內部表操作）

create table techer2 like techer; --依據已有表結構建立表

export table techer to  '/export/techer';

import table techer2 from '/export/techer';

hive表中資料匯出

insert匯出

將查詢的結果匯出到本地
insert overwrite local directory '/export/servers/exporthive' select * from score;

將查詢的結果格式化匯出到本地
insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;

將查詢的結果匯出到HDFS上(沒有local)
insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;

Hadoop命令匯出到本地

dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;

hive shell 命令匯出

基本語法：（hive -f/-e 執行語句或者指令碼 > file）

hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt

hive -f export.sh > /export/servers/exporthive/score.txt

export匯出到HDFS上

export table score to '/export/exporthive/score';

hive的DQL查詢語法

單表查詢

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

注意：
1、order by 會對輸入做全域性排序，因此只有一個reducer，會導致當輸入規模較大時，需要較長的計算時間。
2、sort by不是全域性排序，其在資料進入reducer前完成排序。因此，如果用sort by進行排序，並且設定mapred.reduce.tasks>1，則sort by只保證每個reducer的輸出有序，不保證全域性有序。
3、distribute by(欄位)根據指定的欄位將資料分到不同的reducer，且分發演算法是hash雜湊。
4、Cluster by(欄位) 除了具有Distribute by的功能外，還會對該欄位進行排序。
因此，如果分桶和sort欄位是同一個時，此時，cluster by = distribute by + sort by

WHERE語句

select * from score where s_score < 60;

注意：
小於某個值是不包含null的，如上查詢結果是把 s_score 為 null 的行剔除的

GROUP BY 分組

select s_id ,avg(s_score) from score group by s_id;

分組後對資料進行篩選，使用having
 select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

注意：
如果使用 group by 分組，則 select 後面只能寫分組的欄位或者聚合函式
where和having區別：
1 having是在 group by 分完組之後再對資料進行篩選，所以having 要篩選的欄位只能是分組欄位或者聚合函式
2 where 是從資料表中的欄位直接進行的篩選的，所以不能跟在gruop by後面，也不能使用聚合函式

join 連線

INNER JOIN 內連線：只有進行連線的兩個表中都存在與連線條件相匹配的資料才會被保留下來
select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略

LEFT OUTER JOIN 左外連線：左邊所有資料會被返回，右邊符合條件的被返回
select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略

RIGHT OUTER JOIN 右外連線：右邊所有資料會被返回，左邊符合條件的被返回、
select * from techer t right join course c on t.t_id = c.t_id;

FULL OUTER JOIN 滿外(全外)連線: 將會返回所有表中符合條件的所有記錄。如果任一表的指定欄位沒有符合條件的值的話，那麼就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;

注：1. hive2版本已經支援不等值連線，就是 join on條件後面可以使用大於小於符號了;並且也支援 join on 條件後跟or (早前版本 on 後只支援 = 和 and，不支援 > < 和 or)
2.如hive執行引擎使用MapReduce，一個join就會啟動一個job，一條sql語句中如有多個join，則會啟動多個job

注意：表之間用逗號(,)連線和 inner join 是一樣的
select * from table_a,table_b where table_a.id=table_b.id;
它們的執行效率沒有區別，只是書寫方式不同，用逗號是sql 89標準，join 是sql 92標準。用逗號連線後面過濾條件用 where ，用 join 連線後面過濾條件是 on。

order by 排序

全域性排序，只會有一個reduce
ASC（ascend）: 升序（預設） DESC（descend）: 降序
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;

注意：order by 是全域性排序，所以最後只有一個reduce，也就是在一個節點執行，如果資料量太大，就會耗費較長時間

sort by 區域性排序

每個MapReduce內部進行排序，對全域性結果集來說不是排序。

設定reduce個數
set mapreduce.job.reduces=3;

檢視設定reduce個數
set mapreduce.job.reduces;

查詢成績按照成績降序排列
select * from score sort by s_score;
 
將查詢結果匯入到檔案中（按照成績降序排列）
insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

distribute by 分割槽排序

distribute by：類似MR中partition，進行分割槽，結合sort by使用

設定reduce的個數，將我們對應的s_id劃分到對應的reduce當中去
set mapreduce.job.reduces=7;

通過distribute by  進行資料的分割槽
select * from score distribute by s_id sort by s_score;

注意：Hive要求 distribute by 語句要寫在 sort by 語句之前

cluster by

當distribute by和sort by欄位相同時，可以使用cluster by方式.
cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序只能是正序排序，不能指定排序規則為ASC或者DESC。

以下兩種寫法等價
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

Hive函式

聚合函式

hive支援 count(),max(),min(),sum(),avg() 等常用的聚合函式

注意：
聚合操作時要注意null值
count(*) 包含null值，統計所有行數
count(id) 不包含null值
min 求最小值是不包含null，除非所有值都是null
avg 求平均值也是不包含null

非空集合總體變數函式: var_pop

語法: var_pop(col)
返回值: double
說明: 統計結果集中col非空集合的總體變數（忽略null）

非空集合樣本變數函式: var_samp

語法: var_samp (col)
返回值: double
說明: 統計結果集中col非空集合的樣本變數（忽略null）

總體標準偏離函式: stddev_pop

語法: stddev_pop(col)
返回值: double
說明: 該函式計算總體標準偏離，並返回總體變數的平方根，其返回值與VAR_POP函式的平方根相同

中位數函式: percentile

語法: percentile(BIGINT col, p)
返回值: double
說明: 求準確的第pth個百分位數，p必須介於0和1之間，但是col欄位目前只支援整數，不支援浮點數型別

關係運算

支援：等值(=)、不等值(!= 或 <>)、小於(<)、小於等於(<=)、大於(>)、大於等於(>=)

空值判斷(is null)、非空判斷(is not null)

LIKE比較: LIKE

語法: A LIKE B
操作型別: strings
描述: 如果字串A或者字串B為NULL，則返回NULL；如果字串A符合表示式B 的正則語法，則為TRUE；否則為FALSE。B中字元”_”表示任意單個字元，而字元”%”表示任意數量的字元。

JAVA的LIKE操作: RLIKE

語法: A RLIKE B
操作型別: strings
描述: 如果字串A或者字串B為NULL，則返回NULL；如果字串A符合JAVA正規表示式B的正則語法，則為TRUE；否則為FALSE。

REGEXP操作: REGEXP

語法: A REGEXP B
操作型別: strings
描述: 功能與RLIKE相同
示例：select 1 from tableName where 'footbar' REGEXP '^f.*r$';
結果：1

數學運算

支援所有數值型別：加(+)、減(-)、乘(*)、除(/)、取餘(%)、位與(&)、位或(|)、位異或(^)、位取反(~)

邏輯運算

支援：邏輯與(and)、邏輯或(or)、邏輯非(not)

數值運算

取整函式: round

語法: round(double a)
返回值: BIGINT
說明: 返回double型別的整數值部分 （遵循四捨五入）
示例：select round(3.1415926) from tableName;
結果：3

指定精度取整函式: round

語法: round(double a, int d)
返回值: DOUBLE
說明: 返回指定精度d的double型別
hive> select round(3.1415926,4) from tableName;
3.1416

向下取整函式: floor

語法: floor(double a)
返回值: BIGINT
說明: 返回等於或者小於該double變數的最大的整數
hive> select floor(3.641) from tableName;
3

向上取整函式: ceil

語法: ceil(double a)
返回值: BIGINT
說明: 返回等於或者大於該double變數的最小的整數
hive> select ceil(3.1415926) from tableName;
4

取隨機數函式: rand

語法: rand(),rand(int seed)
返回值: double
說明: 返回一個0到1範圍內的隨機數。如果指定種子seed，則會等到一個穩定的隨機數序列
hive> select rand() from tableName; -- 每次執行此語句得到的結果都不同
0.5577432776034763

hive> select rand(100) ;  -- 只要指定種子，每次執行此語句得到的結果一樣的
0.7220096548596434

自然指數函式: exp

語法: exp(double a)
返回值: double
說明: 返回自然對數e的a次方
hive> select exp(2) ;
7.38905609893065

以10為底對數函式: log10

語法: log10(double a)
返回值: double
說明: 返回以10為底的a的對數
hive> select log10(100) ;
2.0

此外還有：以2為底對數函式: log2()、對數函式: log()

冪運算函式: pow

語法: pow(double a, double p)
返回值: double
說明: 返回a的p次冪
hive> select pow(2,4) ;
16.0

開平方函式: sqrt

語法: sqrt(double a)
返回值: double
說明: 返回a的平方根
hive> select sqrt(16) ;
4.0

二進位制函式: bin

語法: bin(BIGINT a)
返回值: string
說明: 返回a的二進位制程式碼表示
hive> select bin(7) ;
111

十六進位制函式: hex()、將十六進位制轉化為字串函式: unhex()
進位制轉換函式: conv(bigint num, int from_base, int to_base) 說明: 將數值num從from_base進位制轉化到to_base進位制

此外還有很多數學函式：絕對值函式: abs()、正取餘函式: pmod()、正弦函式: sin()、反正弦函式: asin()、餘弦函式: cos()、反餘弦函式: acos()、positive函式: positive()、negative函式: negative()

條件函式

If函式: if

語法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
說明: 當條件testCondition為TRUE時，返回valueTrue；否則返回valueFalseOrNull
hive> select if(1=2,100,200) ;
200
hive> select if(1=1,100,200) ;
100

非空查詢函式: coalesce

語法: coalesce(T v1, T v2, …)
返回值: T
說明: 返回引數中的第一個非空值；如果所有值都為NULL，那麼返回NULL
hive> select coalesce(null,'100','50') ;
100

條件判斷函式：case when (兩種寫法，其一)

語法: case when a then b [when c then d]* [else e] end
返回值: T
說明：如果a為TRUE,則返回b；如果c為TRUE，則返回d；否則返回e
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
mary

條件判斷函式：case when (兩種寫法，其二)

語法: case a when b then c [when d then e]* [else f] end
返回值: T
說明：如果a等於b，那麼返回c；如果a等於d，那麼返回e；否則返回f
hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
mary

日期函式

注：以下SQL語句中的 from tableName 可去掉，不影響查詢結果

1. 獲取當前UNIX時間戳函式: unix_timestamp

語法: unix_timestamp()
返回值: bigint
說明: 獲得當前時區的UNIX時間戳
hive> select unix_timestamp() from tableName;
1616906976

1. UNIX時間戳轉日期函式: from_unixtime

語法: from_unixtime(bigint unixtime[, string format])
返回值: string
說明: 轉化UNIX時間戳（從1970-01-01 00:00:00 UTC到指定時間的秒數）到當前時區的時間格式
hive> select from_unixtime(1616906976,'yyyyMMdd') from tableName;
20210328

1. 日期轉UNIX時間戳函式: unix_timestamp

語法: unix_timestamp(string date)
返回值: bigint
說明: 轉換格式為"yyyy-MM-dd HH:mm:ss"的日期到UNIX時間戳。如果轉化失敗，則返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15') from tableName;
1615184475

1. 指定格式日期轉UNIX時間戳函式: unix_timestamp

語法: unix_timestamp(string date, string pattern)
返回值: bigint
說明: 轉換pattern格式的日期到UNIX時間戳。如果轉化失敗，則返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName;
1615184475

1. 日期時間轉日期函式: to_date

語法: to_date(string timestamp)
返回值: string
說明: 返回日期時間欄位中的日期部分。
hive> select to_date('2021-03-28 14:03:01') from tableName;
2021-03-28

1. 日期轉年函式: year

語法: year(string date)
返回值: int
說明: 返回日期中的年。
hive> select year('2021-03-28 10:03:01') from tableName;
2021
hive> select year('2021-03-28') from tableName;
2021

1. 日期轉月函式: month

語法: month (string date)
返回值: int
說明: 返回日期中的月份。
hive> select month('2020-12-28 12:03:01') from tableName;
12
hive> select month('2021-03-08') from tableName;
8

1. 日期轉天函式: day

語法: day (string date)
返回值: int
說明: 返回日期中的天。
hive> select day('2020-12-08 10:03:01') from tableName;
8
hive> select day('2020-12-24') from tableName;
24

1. 日期轉小時函式: hour

語法: hour (string date)
返回值: int
說明: 返回日期中的小時。
hive> select hour('2020-12-08 10:03:01') from tableName;
10

1. 日期轉分鐘函式: minute

語法: minute (string date)
返回值: int
說明: 返回日期中的分鐘。
hive> select minute('2020-12-08 10:03:01') from tableName;
3

1. 日期轉秒函式: second

語法: second (string date)
返回值: int
說明: 返回日期中的秒。
hive> select second('2020-12-08 10:03:01') from tableName;
1

1. 日期轉周函式: weekofyear

語法: weekofyear (string date)
返回值: int
說明: 返回日期在當前的週數。
hive> select weekofyear('2020-12-08 10:03:01') from tableName;
49

1. 日期比較函式: datediff

語法: datediff(string enddate, string startdate)
返回值: int
說明: 返回結束日期減去開始日期的天數。
hive> select datediff('2020-12-08','2012-05-09') from tableName;
213

1. 日期增加函式: date_add

語法: date_add(string startdate, int days)
返回值: string
說明: 返回開始日期startdate增加days天后的日期。
hive> select date_add('2020-12-08',10) from tableName;
2020-12-18

1. 日期減少函式: date_sub

語法: date_sub (string startdate, int days)
返回值: string
說明: 返回開始日期startdate減少days天后的日期。
hive> select date_sub('2020-12-08',10) from tableName;
2020-11-28

字串函式

1. 字串長度函式：length

語法: length(string A)
返回值: int
說明：返回字串A的長度
hive> select length('abcedfg') from tableName;
7

1. 字串反轉函式：reverse

語法: reverse(string A)
返回值: string
說明：返回字串A的反轉結果
hive> select reverse('abcedfg') from tableName;
gfdecba

1. 字串連線函式：concat

語法: concat(string A, string B…)
返回值: string
說明：返回輸入字串連線後的結果，支援任意個輸入字串
hive> select concat('abc','def’,'gh')from tableName;
abcdefgh

1. 帶分隔符字串連線函式：concat_ws

語法: concat_ws(string SEP, string A, string B…)
返回值: string
說明：返回輸入字串連線後的結果，SEP表示各個字串間的分隔符
hive> select concat_ws(',','abc','def','gh')from tableName;
abc,def,gh

1. 字串擷取函式：substr,substring

語法: substr(string A, int start),substring(string A, int start)
返回值: string
說明：返回字串A從start位置到結尾的字串
hive> select substr('abcde',3) from tableName;
cde
hive> select substring('abcde',3) from tableName;
cde
hive> select substr('abcde',-1) from tableName; （和ORACLE相同）
e

1. 字串擷取函式：substr,substring

語法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
說明：返回字串A從start位置開始，長度為len的字串
hive> select substr('abcde',3,2) from tableName;
cd
hive> select substring('abcde',3,2) from tableName;
cd
hive>select substring('abcde',-2,2) from tableName;
de

1. 字串轉大寫函式：upper,ucase

語法: upper(string A) ucase(string A)
返回值: string
說明：返回字串A的大寫格式
hive> select upper('abSEd') from tableName;
ABSED
hive> select ucase('abSEd') from tableName;
ABSED

1. 字串轉小寫函式：lower,lcase

語法: lower(string A) lcase(string A)
返回值: string
說明：返回字串A的小寫格式
hive> select lower('abSEd') from tableName;
absed
hive> select lcase('abSEd') from tableName;
absed

1. 去空格函式：trim

語法: trim(string A)
返回值: string
說明：去除字串兩邊的空格
hive> select trim(' abc ') from tableName;
abc

1. 左邊去空格函式：ltrim

語法: ltrim(string A)
返回值: string
說明：去除字串左邊的空格
hive> select ltrim(' abc ') from tableName;
abc

1. 右邊去空格函式：rtrim

語法: rtrim(string A)
返回值: string
說明：去除字串右邊的空格
hive> select rtrim(' abc ') from tableName;
abc

1. 正規表示式替換函式：regexp_replace

語法: regexp_replace(string A, string B, string C)
返回值: string
說明：將字串A中的符合java正規表示式B的部分替換為C。注意，在有些情況下要使用轉義字元,類似oracle中的regexp_replace函式。
hive> select regexp_replace('foobar', 'oo|ar', '') from tableName;
fb

1. 正規表示式解析函式：regexp_extract

語法: regexp_extract(string subject, string pattern, int index)
返回值: string
說明：將字串subject按照pattern正規表示式的規則拆分，返回index指定的字元。
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from tableName;
the
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from tableName;
bar
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from tableName;
foothebar
strong>注意，在有些情況下要使用轉義字元，下面的等號要用雙豎線轉義，這是java正規表示式的規則。
select data_field,
regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa,
regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb,
regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc 
from pt_nginx_loginlog_st 
where pt = '2021-03-28' limit 2;

1. URL解析函式：parse_url

語法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
說明：返回URL中指定的部分。partToExtract的有效值為：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') 
from tableName;
www.tableName.com 
hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
 from tableName;
v1

1. json解析函式：get_json_object

語法: get_json_object(string json_string, string path)
返回值: string
說明：解析json的字串json_string,返回path指定的內容。如果輸入的json字串無效，那麼返回NULL。
hive> select  get_json_object('{"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"amy@only_for_json_udf_test.net","owner":"amy"}','$.owner') from tableName;

1. 空格字串函式：space

語法: space(int n)
返回值: string
說明：返回長度為n的字串
hive> select space(10) from tableName;
hive> select length(space(10)) from tableName;
10

1. 重複字串函式：repeat

語法: repeat(string str, int n)
返回值: string
說明：返回重複n次後的str字串
hive> select repeat('abc',5) from tableName;
abcabcabcabcabc

1. 首字元ascii函式：ascii

語法: ascii(string str)
返回值: int
說明：返回字串str第一個字元的ascii碼
hive> select ascii('abcde') from tableName;
97

1. 左補足函式：lpad

語法: lpad(string str, int len, string pad)
返回值: string
說明：將str進行用pad進行左補足到len位
hive> select lpad('abc',10,'td') from tableName;
tdtdtdtabc
注意：與GP，ORACLE不同，pad 不能預設

1. 右補足函式：rpad

語法: rpad(string str, int len, string pad)
返回值: string
說明：將str進行用pad進行右補足到len位
hive> select rpad('abc',10,'td') from tableName;
abctdtdtdt

1. 分割字串函式: split

語法: split(string str, string pat)
返回值: array
說明: 按照pat字串分割str，會返回分割後的字串陣列
hive> select split('abtcdtef','t') from tableName;
["ab","cd","ef"]

1. 集合查詢函式: find_in_set

語法: find_in_set(string str, string strList)
返回值: int
說明: 返回str在strlist第一次出現的位置，strlist是用逗號分割的字串。如果沒有找該str字元，則返回0
hive> select find_in_set('ab','ef,ab,de') from tableName;
2
hive> select find_in_set('at','ef,ab,de') from tableName;
0

複合型別構建操作

Map型別構建: map

語法: map (key1, value1, key2, value2, …)
說明：根據輸入的key和value對構建map型別
hive> Create table mapTable as select map('100','tom','200','mary') as t from tableName;
hive> describe mapTable;
t       map<string ,string>
hive> select t from tableName;
{"100":"tom","200":"mary"}

1. Struct型別構建: struct

語法: struct(val1, val2, val3, …)
說明：根據輸入的引數構建結構體struct型別
hive> create table struct_table as select struct('tom','mary','tim') as t from tableName;
hive> describe struct_table;
t       struct<col1:string ,col2:string,col3:string>
hive> select t from tableName;
{"col1":"tom","col2":"mary","col3":"tim"}

1. array型別構建: array

語法: array(val1, val2, …)
說明：根據輸入的引數構建陣列array型別
hive> create table arr_table as select array("tom","mary","tim") as t from tableName;
hive> describe tableName;
t       array<string>
hive> select t from tableName;
["tom","mary","tim"]

複雜型別訪問操作

1. array型別訪問: A[n]

語法: A[n]
操作型別: A為array型別，n為int型別
說明：返回陣列A中的第n個變數值。陣列的起始下標為0。比如，A是個值為['foo', 'bar']的陣列型別，那麼A[0]將返回'foo',而A[1]將返回'bar'
hive> create table arr_table2 as select array("tom","mary","tim") as t
 from tableName;
hive> select t[0],t[1] from arr_table2;
tom     mary    tim

1. map型別訪問: M[key]

語法: M[key]
操作型別: M為map型別，key為map中的key值
說明：返回map型別M中，key值為指定值的value值。比如，M是值為{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map型別，那麼M['all']將會返回'foobar'
hive> Create table map_table2 as select map('100','tom','200','mary') as t from tableName;
hive> select t['200'],t['100'] from map_table2;
mary    tom

1. struct型別訪問: S.x

語法: S.x
操作型別: S為struct型別
說明：返回結構體S中的x欄位。比如，對於結構體struct foobar {int foo, int bar}，foobar.foo返回結構體中的foo欄位
hive> create table str_table2 as select struct('tom','mary','tim') as t from tableName;
hive> describe tableName;
t       struct<col1:string ,col2:string,col3:string>
hive> select t.col1,t.col3 from str_table2;
tom     tim

複雜型別長度統計函式

1. Map型別長度函式: size(Map<k .V>)

語法: size(Map<k .V>)
返回值: int
說明: 返回map型別的長度
hive> select size(t) from map_table2;
2

1. array型別長度函式: size(Array)

語法: size(Array<T>)
返回值: int
說明: 返回array型別的長度
hive> select size(t) from arr_table2;
4

1. 型別轉換函式 ***

型別轉換函式: cast
語法: cast(expr as <type>)
返回值: Expected "=" to follow "type"
說明: 返回轉換後的資料型別
hive> select cast('1' as bigint) from tableName;
1

hive當中的lateral view 與 explode以及reflect

使用explode函式將hive表中的Map和Array欄位資料進行拆分

lateral view用於和split、explode等UDTF一起使用的，能將一行資料拆分成多行資料，在此基礎上可以對拆分的資料進行聚合，lateral view首先為原始表的每行呼叫UDTF，UDTF會把一行拆分成一行或者多行，lateral view在把結果組合，產生一個支援別名表的虛擬表。

其中explode還可以用於將hive一列中複雜的array或者map結構拆分成多行

需求：現在有資料格式如下

zhangsan child1,child2,child3,child4 k1:v1,k2:v2

lisi child5,child6,child7,child8 k3:v3,k4:v4

欄位之間使用\t分割，需求將所有的child進行拆開成為一列

+----------+--+
| mychild  |
+----------+--+
| child1   |
| child2   |
| child3   |
| child4   |
| child5   |
| child6   |
| child7   |
| child8   |
+----------+--+

將map的key和value也進行拆開，成為如下結果

+-----------+-------------+--+
| mymapkey  | mymapvalue  |
+-----------+-------------+--+
| k1        | v1          |
| k2        | v2          |
| k3        | v3          |
| k4        | v4          |
+-----------+-------------+--+

1. 建立hive資料庫

建立hive資料庫
hive (default)> create database hive_explode;
hive (default)> use hive_explode;

1. 建立hive表，然後使用explode拆分map和array

hive (hive_explode)> create  table t3(name string,children array<string>,address Map<string,string>) row format delimited fields terminated by '\t'  collection items terminated by ',' map keys terminated by ':' stored as textFile;

1. 載入資料

node03執行以下命令建立表資料檔案
 mkdir -p /export/servers/hivedatas/
 cd /export/servers/hivedatas/
 vim maparray
內容如下:
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4

hive表當中載入資料
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/maparray' into table t3;

1. 使用explode將hive當中資料拆開

將array當中的資料拆分開
hive (hive_explode)> SELECT explode(children) AS myChild FROM t3;

將map當中的資料拆分開

hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;

使用explode拆分json字串

需求: 需求：現在有一些資料格式如下：

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

其中欄位與欄位之間的分隔符是 |

我們要解析得到所有的monthSales對應的值為以下這一列（行轉列）

4900

2090

6987

1. 建立hive表

hive (hive_explode)> create table explode_lateral_view
                   > (`area` string,
                   > `goods_id` string,
                   > `sale_info` string)
                   > ROW FORMAT DELIMITED
                   > FIELDS TERMINATED BY '|'
                   > STORED AS textfile;

1. 準備資料並載入資料

準備資料如下
cd /export/servers/hivedatas
vim explode_json

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

載入資料到hive表當中去
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/explode_json' overwrite into table explode_lateral_view;

1. 使用explode拆分Array

hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from explode_lateral_view;

1. 使用explode拆解Map

hive (hive_explode)> select explode(split(area,',')) as area from explode_lateral_view;

1. 拆解json欄位

hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as  sale_info from explode_lateral_view;

然後我們想用get_json_object來獲取key為monthSales的資料：

hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as  sale_info from explode_lateral_view;


然後掛了FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF explode不能寫在別的函式內
如果你這麼寫，想查兩個欄位，select explode(split(area,',')) as area,good_id from explode_lateral_view;
會報錯FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'
使用UDTF的時候，只支援一個欄位，這時候就需要LATERAL VIEW出場了

配合LATERAL VIEW使用

配合lateral view查詢多個欄位

hive (hive_explode)> select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

其中LATERAL VIEW explode(split(goods_id,','))goods相當於一個虛擬表，與原表explode_lateral_view笛卡爾積關聯

也可以多重使用

hive (hive_explode)> select goods_id2,sale_info,area2
                    from explode_lateral_view 
                    LATERAL VIEW explode(split(goods_id,','))goods as goods_id2 
                    LATERAL VIEW explode(split(area,','))area as area2;也是三個表笛卡爾積的結果

最終，我們可以通過下面的句子，把這個json格式的一行資料，完全轉換成二維表的方式展現

hive (hive_explode)> select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales from explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;

總結：

Lateral View通常和UDTF一起出現，為了解決UDTF不允許在select欄位的問題。 Multiple Lateral View可以實現類似笛卡爾乘積。 Outer關鍵字可以把不輸出的UDTF的空結果，輸出成NULL，防止丟失資料。

行轉列

相關引數說明:

CONCAT(string A/col, string B/col…)：返回輸入字串連線後的結果，支援任意個輸入字串;

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

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

資料準備:

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

需求: 把星座和血型一樣的人歸類到一起。結果如下：

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

實現步驟:

1. 建立本地constellation.txt，匯入資料

node03伺服器執行以下命令建立檔案，注意資料使用\t進行分割
cd /export/servers/hivedatas
vim constellation.txt

資料如下: 
孫悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B       
豬八戒 白羊座 A
鳳姐 射手座 A

1. 建立hive表並匯入資料

建立hive表並載入資料
hive (hive_explode)> create table person_info(
                    name string, 
                    constellation string, 
                    blood_type string) 
                    row format delimited fields terminated by "\t";
                    
載入資料
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/constellation.txt' into table person_info;

1. 按需求查詢資料

hive (hive_explode)> 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;

列轉行

所需函式:

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

LATERAL VIEW

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

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

資料準備:

cd /export/servers/hivedatas
vim movie.txt
檔案內容如下:  資料欄位之間使用\t進行分割
《疑犯追蹤》 懸疑,動作,科幻,劇情
《Lie to me》 懸疑,警匪,動作,心理,劇情
《戰狼2》 戰爭,動作,災難

需求: 將電影分類中的陣列資料展開。結果如下：

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

實現步驟:

1. 建立hive表

create table movie_info(
    movie string, 
    category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

1. 載入資料

load data local inpath "/export/servers/hivedatas/movie.txt" into table movie_info;

1. 按需求查詢資料

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

reflect函式

reflect函式可以支援在sql中呼叫java中的自帶函式，秒殺一切udf函式。

需求1: 使用java.lang.Math當中的Max求兩列中最大值

實現步驟:

1. 建立hive表

create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';

1. 準備資料並載入資料

cd /export/servers/hivedatas
vim test_udf 

檔案內容如下:
1,2
4,3
6,4
7,5
5,6

1. 載入資料

hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf' overwrite into table test_udf;

1. 使用java.lang.Math當中的Max求兩列當中的最大值

hive (hive_explode)> select reflect("java.lang.Math","max",col1,col2) from test_udf;

需求2: 檔案中不同的記錄來執行不同的java的內建函式

實現步驟:

1. 建立hive表

hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';

1. 準備資料

cd /export/servers/hivedatas
vim test_udf2

檔案內容如下:
java.lang.Math,min,1,2
java.lang.Math,max,2,3

1. 載入資料

hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf2' overwrite into table test_udf2;

1. 執行查詢

hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;

需求3: 判斷是否為數字

實現方式:

使用apache commons中的函式，commons下的jar已經包含在hadoop的classpath中，所以可以直接使用。

select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123")

Hive 視窗函式

視窗函式最重要的關鍵字是 partition by 和 order by

具體語法如下：XXX over (partition by xxx order by xxx)

特別注意：over()裡面的 partition by 和 order by 都不是必選的，over()裡面可以只有partition by，也可以只有order by，也可以兩個都沒有，大家需根據需求靈活運用。

視窗函式我劃分了幾個大類，我們一類一類的講解。

1. SUM、AVG、MIN、MAX

講解這幾個視窗函式前，先建立一個表，以實際例子講解大家更容易理解。

首先建立使用者訪問頁面表：user_pv

create table user_pv(
cookieid string,  -- 使用者登入的cookie，即使用者標識
createtime string, -- 日期
pv int -- 頁面訪問量
);

給上面這個表加上如下資料：

cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4

SUM()使用

執行如下查詢語句：

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1 
from user_pv;

結果如下：（因命令列原因，下圖欄位名和值是錯位的，請注意辨別！）

執行如下查詢語句：

select cookieid,createtime,pv,
sum(pv) over(partition by cookieid ) as pv1 
from user_pv;

結果如下：

第一條SQL的over()裡面加 order by ，第二條SQL沒加order by ，結果差別很大

所以要注意了：

over()裡面加 order by 表示：分組內從起點到當前行的pv累積，如，11號的pv1=10號的pv+11號的pv, 12號=10號+11號+12號；
over()裡面不加 order by 表示：將分組內所有值累加。

AVG，MIN，MAX，和SUM用法一樣，這裡就不展開講了，但是要注意 AVG，MIN，MAX 的over()裡面加不加 order by 也和SUM一樣，如 AVG 求平均值，如果加上 order by，表示分組內從起點到當前行的平局值，不是全部的平局值。MIN，MAX 同理。

2. ROW_NUMBER、RANK、DENSE_RANK、NTILE

還是用上述的使用者登入日誌表：user_pv，裡面的資料換成如下所示：

cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4
cookie2,2021-05-10,2
cookie2,2021-05-11,3
cookie2,2021-05-12,5
cookie2,2021-05-13,6
cookie2,2021-05-14,3
cookie2,2021-05-15,9
cookie2,2021-05-16,7

ROW_NUMBER()使用：

ROW_NUMBER()從1開始，按照順序，生成分組內記錄的序列。

SELECT 
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn 
FROM user_pv;

結果如下：

RANK 和 DENSE_RANK 使用：

RANK() 生成資料項在分組中的排名，排名相等會在名次中留下空位。

DENSE_RANK()生成資料項在分組中的排名，排名相等會在名次中不會留下空位。

SELECT 
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM user_pv 
WHERE cookieid = 'cookie1';

結果如下：

NTILE的使用：

有時會有這樣的需求:如果資料排序後分為三部分，業務人員只關心其中的一部分，如何將這中間的三分之一資料拿出來呢?NTILE函式即可以滿足。

ntile可以看成是：把有序的資料集合平均分配到指定的數量（num）個桶中, 將桶號分配給每一行。如果不能平均分配，則優先分配較小編號的桶，並且各個桶中能放的行數最多相差1。

然後可以根據桶號，選取前或後 n分之幾的資料。資料會完整展示出來，只是給相應的資料打標籤；具體要取幾分之幾的資料，需要再巢狀一層根據標籤取出。

SELECT 
cookieid,
createtime,
pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
NTILE(4) OVER(ORDER BY createtime) AS rn3
FROM user_pv 
ORDER BY cookieid,createtime;

結果如下：

3. LAG、LEAD、FIRST_VALUE、LAST_VALUE

講解這幾個視窗函式時還是以例項講解，首先建立使用者訪問頁面表：user_url

CREATE TABLE user_url (
cookieid string,
createtime string,  --頁面訪問時間
url string       --被訪問頁面
);

表中加入如下資料：

cookie1,2021-06-10 10:00:02,url2
cookie1,2021-06-10 10:00:00,url1
cookie1,2021-06-10 10:03:04,1url3
cookie1,2021-06-10 10:50:05,url6
cookie1,2021-06-10 11:00:00,url7
cookie1,2021-06-10 10:10:00,url4
cookie1,2021-06-10 10:50:01,url5
cookie2,2021-06-10 10:00:02,url22
cookie2,2021-06-10 10:00:00,url11
cookie2,2021-06-10 10:03:04,1url33
cookie2,2021-06-10 10:50:05,url66
cookie2,2021-06-10 11:00:00,url77
cookie2,2021-06-10 10:10:00,url44
cookie2,2021-06-10 10:50:01,url55

LAG的使用：

LAG(col,n,DEFAULT) 用於統計視窗內往上第n行值。

第一個引數為列名，第二個引數為往上第n行（可選，預設為1），第三個引數為預設值（當往上第n行為NULL時候，取預設值，如不指定，則為NULL）

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
FROM user_url;

結果如下：

解釋：

last_1_time: 指定了往上第1行的值，default為'1970-01-01 00:00:00'  
                 cookie1第一行，往上1行為NULL,因此取預設值 1970-01-01 00:00:00
                 cookie1第三行，往上1行值為第二行值，2021-06-10 10:00:02
                 cookie1第六行，往上1行值為第五行值，2021-06-10 10:50:01
last_2_time: 指定了往上第2行的值，為指定預設值
       cookie1第一行，往上2行為NULL
       cookie1第二行，往上2行為NULL
       cookie1第四行，往上2行為第二行值，2021-06-10 10:00:02
       cookie1第七行，往上2行為第五行值，2021-06-10 10:50:01

LEAD的使用：

與LAG相反

LEAD(col,n,DEFAULT) 用於統計視窗內往下第n行值。

第一個引數為列名，第二個引數為往下第n行（可選，預設為1），第三個引數為預設值（當往下第n行為NULL時候，取預設值，如不指定，則為NULL）

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time 
FROM user_url;

結果如下：

FIRST_VALUE的使用：

取分組內排序後，截止到當前行，第一個值。

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1 
FROM user_url;

結果如下：

LAST_VALUE的使用：

取分組內排序後，截止到當前行，最後一個值。

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 
FROM user_url;

結果如下：

如果想要取分組內排序後最後一個值，則需要變通一下：

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 
FROM user_url 
ORDER BY cookieid,createtime;

注意上述SQL，使用的是 FIRST_VALUE 的倒序取出分組內排序最後一個值！

結果如下：

此處要特別注意order by

如果不指定ORDER BY，則進行排序混亂，會出現錯誤的結果

SELECT cookieid,
createtime,
url,
FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2  
FROM user_url;

結果如下：

上述 url2 和 url55 的createtime即不屬於最靠前的時間也不屬於最靠後的時間，所以結果是混亂的。

4. CUME_DIST

先建立一張員工薪水錶：staff_salary

CREATE EXTERNAL TABLE staff_salary (
dept string,
userid string,
sal int
);

表中加入如下資料：

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000

CUME_DIST的使用：

此函式的結果和order by的排序順序有關係。

CUME_DIST：小於等於當前值的行數/分組內總行數。 order預設順序：正序

比如，統計小於等於當前薪水的人數，所佔總人數的比例。

SELECT 
dept,
userid,
sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM staff_salary;

結果如下：

解釋：

rn1: 沒有partition,所有資料均為1組，總行數為5，
     第一行：小於等於1000的行數為1，因此，1/5=0.2
     第三行：小於等於3000的行數為3，因此，3/5=0.6
rn2: 按照部門分組，dpet=d1的行數為3,
     第二行：小於等於2000的行數為2，因此，2/3=0.6666666666666666

5. GROUPING SETS、GROUPING__ID、CUBE、ROLLUP

這幾個分析函式通常用於OLAP中，不能累加，而且需要根據不同維度上鑽和下鑽的指標統計，比如，分小時、天、月的UV數。

還是先建立一個使用者訪問表：user_date

CREATE TABLE user_date (
month STRING,
day STRING, 
cookieid STRING 
);

表中加入如下資料：

2021-03,2021-03-10,cookie1
2021-03,2021-03-10,cookie5
2021-03,2021-03-12,cookie7
2021-04,2021-04-12,cookie3
2021-04,2021-04-13,cookie2
2021-04,2021-04-13,cookie4
2021-04,2021-04-16,cookie4
2021-03,2021-03-10,cookie2
2021-03,2021-03-10,cookie3
2021-04,2021-04-12,cookie5
2021-04,2021-04-13,cookie6
2021-04,2021-04-15,cookie3
2021-04,2021-04-15,cookie2
2021-04,2021-04-16,cookie1

GROUPING SETS的使用：

grouping sets是一種將多個group by 邏輯寫在一個sql語句中的便利寫法。

等價於將不同維度的GROUP BY結果集進行UNION ALL。

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM user_date 
GROUP BY month,day 
GROUPING SETS (month,day) 
ORDER BY GROUPING__ID;

注：上述SQL中的GROUPING__ID，是個關鍵字，表示結果屬於哪一個分組集合，根據grouping sets中的分組條件month，day，1是代表month，2是代表day。

結果如下：

上述SQL等價於：

SELECT month,
NULL as day,
COUNT(DISTINCT cookieid) AS uv,
1 AS GROUPING__ID 
FROM user_date 
GROUP BY month 

UNION ALL 

SELECT NULL as month,
day,
COUNT(DISTINCT cookieid) AS uv,
2 AS GROUPING__ID 
FROM user_date 
GROUP BY day;

CUBE的使用：

根據GROUP BY的維度的所有組合進行聚合。

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM user_date 
GROUP BY month,day 
WITH CUBE 
ORDER BY GROUPING__ID;

結果如下：

上述SQL等價於：

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM user_date

UNION ALL 

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM user_date GROUP BY month 

UNION ALL 

SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM user_date GROUP BY day

UNION ALL 

SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM user_date GROUP BY month,day;

ROLLUP的使用：

是CUBE的子集，以最左側的維度為主，從該維度進行層級聚合。

比如，以month維度進行層級聚合：

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM user_date 
GROUP BY month,day
WITH ROLLUP 
ORDER BY GROUPING__ID;

結果如下：

把month和day調換順序，則以day維度進行層級聚合：

SELECT 
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM user_date 
GROUP BY day,month 
WITH ROLLUP 
ORDER BY GROUPING__ID;

結果如下：

這裡，根據日和月進行聚合，和根據日聚合結果一樣，因為有父子關係，如果是其他維度組合的話，就會不一樣。

最全面Hive SQL開發指南PDF版

最全面的前端開發指南
2015-12-15
前端
最強最全面的數倉建設規範指南
2021-11-11
Rxjava2最全面的解析
2019-03-04
RxJava
Android Service最全面的解析
2018-11-16
Android
最全面的 Sublime Text 使用指南
2016-01-05
最全面的Navigation的使用指南
2019-05-04
Navigation
最全面的 Android 編碼規範指南
2016-04-21
Android
最全面的Java多執行緒用法解析
2015-03-17
Java執行緒
最全面的UML教程
2018-04-20
深度解讀 MongoDB 最全面的增強版本 4.4 新特性
2020-11-25
MongoDB
最全面的前端資源分享
2017-12-27
前端
最全面的 MySQL 索引詳解
2016-08-09
MySql索引
最全面的 DNS 原理入門
2016-06-16
DNS
最全面的vue開源專案庫彙總
2018-11-20
Vue
SuperTextView 最全開發指南
2019-02-09
TextView
史上最全面的React-react基礎
2018-07-16
React
最全面的 Spring 學習筆記
2017-03-28
Spring筆記
最全面的JavaScript除錯技巧總結
2016-04-15
JavaScript除錯
最全面的 C++ 資源、框架大全
2016-05-07
C++框架
最全面的Android Studio使用教程
2014-11-24
Android
可能是最全面最易懂的解析前端浮動的文章
2018-10-10
前端
最全面的Android Intent機制講解
2015-03-19
AndroidIntent
史上最全面的Spring Boot Cache使用與整合
2019-05-06
Spring Boot
最新、最全面的LAMP+wordpress安裝過程！！
2017-11-14
LAMP
OPPO A59史上最全面的體驗評測
2016-07-04
史上最全面的程式設計師招聘建議
2015-11-16
程式設計師
這有可能是業界最最全面的移動遊戲營銷分析指南了
2019-04-25
遊戲
絕對專業！超全面的UI介面字型選取與使用指南
2016-07-09
UI
Android事件分發機制：基礎篇：最全面、最易懂
2021-09-09
Android事件
Oracle最權威、最全面官方文件簡集
2014-08-16
Oracle
iOS開發系列--UITableView全面解析
2014-08-23
iOSUIView
最全面的CQRS和事件溯源介紹 - Software House ASC
2019-05-23
事件
Linux流量監控工具 - iftop (最全面的iftop教程)
2019-03-13
Linux
可能是最全面的 python 字串拼接總結
2019-02-16
Python字串
Android 螢幕適配：最全面的解決方案
2020-11-21
Android
可能是最全面的python字串拼接總結
2018-07-04
Python字串
最全面的mac下的android studio快捷鍵
2015-08-11
MacAndroid
Oracle－業務分析領域最全面的商務智慧
2008-08-17
Oracle

最強最全面的Hive SQL開發指南，超四萬字全面解析

第一部分：

Hive查詢語句

Hive函式

1. 聚合函式

2. 關係運算

3. 數學運算

4. 邏輯運算

5. 數值運算

6. 條件函式

7. 日期函式

8. 字串函式

9. 視窗函式

第二部分

1. 對資料庫的操作

2. 對資料表的操作

對管理表(內部表)的操作:

對外部表操作

對分割槽表的操作

對分桶表操作

修改表和刪除表

向hive表中載入資料

hive表中資料匯出

hive的DQL查詢語法

單表查詢

Hive函式

聚合函式

關係運算

數學運算

邏輯運算

數值運算

條件函式

日期函式

字串函式

複合型別構建操作

複雜型別訪問操作

複雜型別長度統計函式

hive當中的lateral view 與 explode以及reflect

使用explode函式將hive表中的Map和Array欄位資料進行拆分

使用explode拆分json字串

配合LATERAL VIEW使用

行轉列

列轉行

reflect函式

Hive 視窗函式

1. SUM、AVG、MIN、MAX

2. ROW_NUMBER、RANK、DENSE_RANK、NTILE

3. LAG、LEAD、FIRST_VALUE、LAST_VALUE

4. CUME_DIST

5. GROUPING SETS、GROUPING__ID、CUBE、ROLLUP

相關文章