Hive是基于Hadoop的一個資料倉庫工具,可以将結構化的資料檔案映射為一張資料庫表,并提供簡單的SQL查詢功能,可以将SQL語句轉換為MapReduce任務進行運作。其優點是學習成本低,可以通過類SQL語句快速實作簡單的MapReduce統計,不必開發專門的MapReduce應用,十分适合資料倉庫的統計分析。
Hive會将常用的邏輯封裝成函數給使用者進行使用,類似于Java中的函數。好處在于可以避免使用者反複寫邏輯,可以直接拿來使用。重點在于使用者需要知道函數叫什麼,能做什麼。
Hive函數大緻分為五類:單行函數、聚合函數、炸裂函數、視窗函數、自定義函數,今天我們主要介紹一下除自定義函數外的函數部分案例使用。
自定義函數咱們之前已經細緻的講過了,可以根據連接配接檢視:HIVE函數講解之自定義函數
一、單行函數
單行函數的特點是一進一出,大緻可以分為數學函數,日期函數,字元串函數,流程控制函數,集合函數。
1. 數學函數
數學函數針對于數值形式的計算,傳回值均為數值類型。
示例:
select 1 + 1; 2
select 1 - 1; 0
select 2 * 2; 4
select 3 / 2; 1.5
select 5 % 2; 1
select 1 & 2; 0
select 1 | 2; 3
select 1 ^ 2; 3
select ~1; -2
2. 日期函數
① unix_timestamp(string time):傳回值為bigint類型。
select unix_timestamp('2023-04-06 15:31:26');
1680795086
② from_unixtime(bigint time [,string format]):傳回值為
string類型。
select from_unixtime(1680795086);
2023-04-06 15:31:26
③ year(string time):傳回值為int類型。
select year('2023-04-06 15:31:26');
2023
④ month(string time):傳回值為int類型。
select month('2023-04-06 15:31:26');
4
⑤ day(string time):傳回值為int類型。
select day('2023-04-06 15:31:26');
6
⑥ datediff(string time,string time):傳回值類型為int類型。
select datediff('2023-04-07','2023-04-06');
1
⑦ date_add(string date,int day):傳回值為date類型。
select date_add('2023-04-06',1);
2023-04-07
⑧ date_sub(string date,int day):傳回值為date類型。
select date_sub('2023-04-06',1);
2023-04-05
⑨ date_format(string date,string format):傳回值為string類型。
select date_format('2023-04-06 15:45:25','yyyy-MM-dd');
2023-04-06
3. 字元串函數
① upper(string str):傳回值為string類型。
select upper('abc');
ABC
② lower(string str):傳回值為string類型。
select lower('ABC');
abc
③ length(string str):傳回值為int類型。
select length('atguigu');
7
④ trim(string str):傳回值為string類型。
select trim(' abc ');
abc
⑤ substring(string str,int start[,int end]):傳回值為string類型。
select substring('atguigu',2,5);
tguig
⑥ replace(string str,string bef,string las):傳回值為string類型。
select replace('atguigu','a','b');
btguigu
⑦ split(string str,string par):傳回值為array類型。
select split('atguigu','g');
["at","ui","u"]
⑧concat_ws(string spl,string s1,string s2,...):傳回值為string類型。
select concat_ws('-','a','b','c');
a-b-c
⑨ get_json_object(string json,string path):傳回值為string類型。
select get_json_object('{"data1":1,"data2":2}','$.data2');
2
4. 流程控制函數
示例:
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
mary
select if(2>3,2,3);
3
5. 集合函數
① size(array/map m):傳回值為int類型。
select size(array('a','b','c'));
3
② map_keys(map m):傳回值為array類型。
select map_keys(test) from test;
[1,2,3]
test字段值為{"1":"a","2":"b","3","c"}
③ map_values(map m):傳回值為array類型。
select map_values(test) from test;
["a","b","c"]
④ array_contains(array arr,string str):傳回值為boolean類型。
select array_contains(array('1','2','3'),'4');
false
⑤ sort_array(array arr):傳回值為array類型。
select sort_array(array(4,2,3,1));
[1,2,3,4]
二、聚合函數
聚合函數對一組資料進行計算,并傳回單個值。
測試資料test表:
1. 普通聚合
示例:
select count(*) from test;
4
select sum(score) from test;
260
select max(score) from test;
80
select min(score) from test;
50
select avg(score) from test;
65
2. 進階聚合
示例:
select collect_list(name) from test;
["atguigu1","atguigu2","atguigu3","atguigu3"]
select collect_set(name) from test;
["atguigu1","atguigu2","atguigu3"]
3. 分組
Group By語句通常會和聚合函數一起使用,按照一個或者多個列隊結果進行分組,然後對每個組執行聚合操作,同時可以使用having進行過濾。
示例:
select
id,
avg(score) avg_s
from test
group by id
having avg_s > 60;
三、炸裂函數
Hive處理的資料字段是可再分的,不滿足原子性,即DDL可以定義一個字段類型為數組,是以才有了explode()函數,用于給這個字段展開降維,把指定的數組字段拆分降維展開為多行。類似于UDTF函數,作用于單/多個資料行,并且産生多個資料行,以一個表作為輸出。
1. explode 将數組或者map展開
例:
select explode(array('a','b','c','d'));
結果:
a b c d
2. json_tuple 取出json字元串中的屬性值
例:
select json_tuple('{"name":"王二狗","sex":"男","age":"25"}','name','sex','age');
結果:
王二狗 男 25
3. lateral view 側寫
炸裂函數和聚合函數一樣不支援和普通列一起查詢,是以一般會配合側寫進行查詢。
用法:lateral view udtf(expression) tableAlias AS columnAlias
解釋:lateral view用于和split,explode等UDTF一起使用,它能夠将一行資料拆分成多行資料,在此基礎上可以對拆分後的資料進行聚合。
lateral view首先為原始表的每行調用UDTF,UDTF會把每一行拆分成一行或者多行,lateral view再把結果組合,産生一個支援别名表的虛拟表。
例:
select movie, category_namefrom movie_infolateral viewexplode(split(category,",")) movie_info_tmp AS category_name;
四、視窗函數
視窗函數,也可以叫開窗函數,其從本質來看是将Hive 中一些複雜的查詢封裝成了視窗的形式,進行資料統計時使用并且操 作十分友善,視窗函數為統計時使用的聚合函數指定一個聚合的範圍。
1. 文法
基本文法:函數 + over( [partition by ...] [order by ...] [視窗子句] )
- over表示開窗,預設視窗大小會包含所有資料。
- partition by表示根據字段再劃分一個細視窗,相同字段進入同一個細視窗裡面,每個視窗之間互相獨立,視窗子句對于每個細視窗獨立生效。
- order by表示視窗内按什麼排序,如果隻有over表示直接最大視窗排序;如果有partition by每個細視窗單獨排序。
- 視窗子句,可以進一步限定範圍
(rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following
(rows | range) between current row and (current row | (unbounded | [num]) following)
(rows | range) between [num] following and (unbounded | [num]) following
示例:
rows between unbounded preceding and unbounded following
行的範圍為上無邊界到下無邊界(第一行到最後一行)。
注:視窗函數是一行一行執行的。
2. 使用
① 視窗函數
lag(col,n,default_val):往前第n行資料。
lead(col,n, default_val):往後第n行資料。
first_value (col,true/false):目前視窗下的第一個值,第二個參數為true,跳過空值。
last_value (col,true/false):目前視窗下的最後一個值,第二個參數為true,跳過空值。
② 聚合函數
max:最大值。
min:最小值。
sum:求和。
avg:平均值。
count:計數。
③ 排名分析函數
rank:排名相同時會重複總數不會減少。
dense_rank:排名相同時會重複總數會減少。
row_number:行号。
ntile:分組并給上組号。
3. 注意事項
- 視窗函數的執行次序是在group by之後。
- 有時視窗子句加與不加結果是一緻的,原因是視窗子句有預設值,當有order by 但是缺少視窗子句時,範圍是上無邊界到目前行,當order by和視窗子句都缺少時,範圍為上無邊界到下無邊界。
- 不是所有函數都需要寫視窗子句,rank、dense_rank、ntile、row_number、lag、lead 這些函數不支援視窗子句。
- 排名分析函數中不需要寫參數,會将排好序資料進行标号。
4. 使用示例
例如,表table,字段為id,sub,score,需求出表每門學科的成績排名。
select
id,
sub,
score,
rank() over(distribute by sub sort by score desc) rank
from table;
五、總結
本文給大家介紹了Hive中一些常用的基本函數、炸裂函數和視窗函數的理論及使用,各位程式猿們需要多多練習函數的使用,體會函數的效果以及它們之間的聯合使用!下期為大家介紹HIVE自定義函數的方法,實作我們自己想要的效果。