Hive函數(shù)怎么用

這篇文章主要介紹了Hive函數(shù)怎么用，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

1、函數(shù)

1.1 、系統(tǒng)內(nèi)置函數(shù)

1）查看系統(tǒng)自帶的函數(shù)
 show functions;   --289個(gè)
 
2）顯示自帶的函數(shù)的用法
 desc function upper;
 
3）詳細(xì)顯示自帶的函數(shù)的用法
 desc function extended upper;

1.2、常用內(nèi)置函數(shù)

1.2.1、空字段賦值（NVL）

--格式：NVL(value，default_value)  value為字段 default_value返回默認(rèn)值

--案例：當(dāng)獎(jiǎng)金為Null時(shí),用0替代
select ename, comm, sal, nvl(comm,0)
from emp;

1.2.2、CASE WHEN THEN ELSE END

--格式：case 字段 when 'char' then 1 else 0 end;

--案例：
+---------------+------------------+--------------+
| emp_sex.name  | emp_sex.dept_id  | emp_sex.sex  |
+---------------+------------------+--------------+
| 悟空           | A                | 男           |
| 大海           | A                | 男           |
| 宋宋           | B                | 男           |
| 鳳姐           | A                | 女           |
| 婷姐           | B                | 女           |
| 婷婷           | B                | 女           |
+---------------+------------------+--------------+
+----------+-------+---------+
| dept_id  | male  | female  |
+----------+-------+---------+
| A        | 2     | 1       |
| B        | 1     | 2       |
+----------+-------+---------+
select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male,
  sum(case sex when '女' then 1 else 0 end) female
from emp_sex
group by dept_id;

--拓展函數(shù)之if:  格式:if(sex='男',1,0)  true返回1，false返回0
select 
  dept_id,
  sum(if(sex='男',1,0)) male,
  sum(if(sex='女',1,0)) female
from emp_sex
group by dept_id;

1.2.3、行轉(zhuǎn)列

1.2.3.1、相關(guān)函數(shù)說(shuō)明

--CONCAT(string A/col, string B/col…)：//返回輸入字符串連接后的結(jié)果，支持任意個(gè)輸入字符串;

--CONCAT_WS(separator, str1, str2,...)：//它是一個(gè)特殊形式的 CONCAT()。第一個(gè)參數(shù)為參數(shù)間的分隔符。
分隔符可以是與剩余參數(shù)一樣的字符串。如果分隔符是 NULL，返回值也將為 NULL。這個(gè)函數(shù)會(huì)跳過(guò)分隔符參數(shù)后的任
何NULL 和空字符串。分隔符將被加到被連接的字符串之間;

注意：CONCAT_WS must be "string or array<string>"

--COLLECT_SET(col)：函數(shù)只接受基本數(shù)據(jù)類型，它的主要作用是將某字段的值進(jìn)行去重匯總，產(chǎn)生array類型字段。
--COLLECT_LIST(col)：函數(shù)只接受基本數(shù)據(jù)類型，主要作用是將某字段的值進(jìn)行不去重匯總，產(chǎn)生array類型字段。

1.2.3.2、案例實(shí)操

數(shù)據(jù)準(zhǔn)備：
+---------------+------------------+--------------+
|     name      |  constellation   |  blood_type  |
+---------------+------------------+--------------+
| 悟空           | 射手座           | A            |
| 八戒           | 天秤座           | A            |
| 路飛           | 射手座           | B            |
| 娜美           | 射手座           | A            |
| 女帝           | 天秤座           | A            |
| 羅賓           | 射手座           | B            |
+---------------+------------------+--------------+

需求：把星座和血型一樣的人歸類到一起。結(jié)果如下：
+-------------+-----------------+
|  天秤座，A   |   八戒|女帝     |
|  射手座，A   |   悟空|娜美     |
|  射手座，B   |   路飛|羅賓     |
+-------------+-----------------+

按需求查詢數(shù)據(jù)：
SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT NAME ,CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info
)t1
GROUP BY t1.c_b

1.2.4、列轉(zhuǎn)行

1.2.4.1、相關(guān)函數(shù)說(shuō)明

--Split(str, separator)：將字符串按照后面的分隔符切割，轉(zhuǎn)換成字符array。

--EXPLODE(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行。

--LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：lateral view用于和split, explode等UDTF一起使用，它能夠?qū)⒁恍袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對(duì)拆
分后的數(shù)據(jù)進(jìn)行聚合。

lateral view首先為原始表的每行調(diào)用UDTF，UTDF會(huì)把一行拆分成一或者多行，lateral view再把結(jié)果組合，產(chǎn)生一
個(gè)支持別名表的虛擬表。

1.2.4.2、案例實(shí)操

數(shù)據(jù)準(zhǔn)備：
+------------------+--------------------------+
|      movie       |  category                |
+------------------+--------------------------+
| 《疑犯追蹤》      |  懸疑,動(dòng)作,科幻,劇情      |
| 《Lie to me》    |  懸疑,警匪,動(dòng)作,心理,劇情  |
| 《戰(zhàn)狼2》        |  戰(zhàn)爭(zhēng),動(dòng)作,災(zāi)難            |
+-----------------+---------------------------+

需求：將電影分類中的數(shù)組數(shù)據(jù)展開。結(jié)果如下
《疑犯追蹤》      懸疑
《疑犯追蹤》      動(dòng)作
《疑犯追蹤》      科幻
《疑犯追蹤》      劇情
《Lie to me》    懸疑
《Lie to me》    警匪
《Lie to me》    動(dòng)作
《Lie to me》    心理
《Lie to me》    劇情
《戰(zhàn)狼2》        戰(zhàn)爭(zhēng)
《戰(zhàn)狼2》        動(dòng)作
《戰(zhàn)狼2》        災(zāi)難

按需求查詢數(shù)據(jù)：
SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ;

1.2.5、窗口函數(shù)（開窗函數(shù)）

1.2.5.1、相關(guān)函數(shù)說(shuō)明

--OVER()：指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化。
--CURRENT ROW：當(dāng)前行
--n PRECEDING：往前n行數(shù)據(jù)
--n FOLLOWING：往后n行數(shù)據(jù)
--UNBOUNDED：無(wú)邊界
	--UNBOUNDED PRECEDING 前無(wú)邊界，表示從前面的起點(diǎn)， 
	--UNBOUNDED FOLLOWING 后無(wú)邊界，表示到后面的終點(diǎn)

--LAG(col,n,default_val)：往前第n行數(shù)據(jù)
--LEAD(col,n, default_val)：往后第n行數(shù)據(jù)

--FIRST_VALUE (col,true/false)：當(dāng)前窗口下的第一個(gè)值，第二個(gè)參數(shù)為true，跳過(guò)空值
--LAST_VALUE (col,true/false)：當(dāng)前窗口下的最后一個(gè)值，第二個(gè)參數(shù)為true，跳過(guò)空值

--NTILE(n)：把有序窗口的行分發(fā)到指定數(shù)據(jù)的組中，各個(gè)組有編號(hào)，編號(hào)從1開始，對(duì)于每一行，NTILE返回此行所屬
的組的編號(hào)。注意：n必須為int類型。

1.2.5.2、案例實(shí)操

數(shù)據(jù)準(zhǔn)備：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

需求：
（1）查詢?cè)?017年4月份購(gòu)買過(guò)的顧客及總?cè)藬?shù)
（2）查詢顧客的購(gòu)買明細(xì)及月購(gòu)買總額
（3）上述的場(chǎng)景, 將每個(gè)顧客的cost按照日期進(jìn)行累加
（4）查詢顧客購(gòu)買明細(xì)以及上次的購(gòu)買時(shí)間和下次購(gòu)買時(shí)間
（5）查詢顧客每個(gè)月第一次的購(gòu)買時(shí)間 和 每個(gè)月的最后一次購(gòu)買時(shí)間
（6）查詢前20%時(shí)間的訂單信息

按需求查詢數(shù)據(jù)：
（1）查詢?cè)?017年4月份購(gòu)買過(guò)的顧客及總?cè)藬?shù)
	select name,count(name) over() Person_num
	from business
	where month(orderdate)=4  //或者where substring(orderdate,1,7)='2017-04'
	group by name;

（2）查詢顧客的購(gòu)買明細(xì)及月購(gòu)買總額
	select name,orderdate,cost,
		sum(cost) over(partition by name,month(orderdate)) month_amount
	from business;

（3）將每個(gè)顧客的cost按照日期進(jìn)行累加
	select name,orderdate,cost,
		sum(cost) over(partition by name order by orderdate 
		//rows between unbounded preceding and current row) accu_cost --默認(rèn) 可以不加
	from business;
注意：
rows必須跟在Order by子句之后，對(duì)排序的結(jié)果進(jìn)行限制，使用固定的行數(shù)來(lái)限制分區(qū)中的數(shù)據(jù)行數(shù)量
order by子句后面不加 rows between and,默認(rèn)為rows between unbounded preceding and current row

（4）查詢顧客購(gòu)買明細(xì)以及上次的購(gòu)買時(shí)間和下次購(gòu)買時(shí)間
	select name,orderdate,cost,
	     lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate) last_date,
	     lead(orderdate,1,'1970-01-01') over(partition by name order by orderdate) next_date
	from business;

（5）查詢顧客每個(gè)月第一次的購(gòu)買時(shí)間 和 每個(gè)月的最后一次購(gòu)買時(shí)間
	select name,orderdate,cost,
	     first_value(orderdate)
		     over(partition by name,month(orderdate) order by orderdate 
		     rows between unbounded preceding and unbounded following) first_date,
	     last_value(orderdate) 
		     over(partition by name,month(orderdate) order by orderdate 
		     rows between unbounded preceding and unbounded following) last_date
	from business;

（6）查詢前20%時(shí)間的訂單信息
	select *
	from (
		select name, orderdate, cost, ntile(5) over (order by orderdate) date_rank
		from business
		 )t1
	where t1.date_rank=1;

1.2.6、Rank

1.2.6.1、相關(guān)函數(shù)說(shuō)明

--RANK() 排序相同時(shí)會(huì)重復(fù)(考慮并列,會(huì)跳號(hào))，總數(shù)不會(huì)變
--DENSE_RANK() 排序相同時(shí)會(huì)重復(fù)(考慮并列,不跳號(hào))，總數(shù)會(huì)減少
--ROW_NUMBER() 會(huì)根據(jù)順序計(jì)算(不考慮并列,不跳號(hào),行號(hào))

1.2.6.2、案例實(shí)操

數(shù)據(jù)準(zhǔn)備：
	name	subject	score
	孫悟空	語(yǔ)文	87
	孫悟空	數(shù)學(xué)	95
	孫悟空	英語(yǔ)	68
	路飛	語(yǔ)文	94
	路飛	數(shù)學(xué)	56
	路飛	英語(yǔ)	84
	柯南	語(yǔ)文	64
	柯南	數(shù)學(xué)	86
	柯南	英語(yǔ)	84
	艾倫	語(yǔ)文	65
	艾倫	數(shù)學(xué)	85
	艾倫	英語(yǔ)	78

需求：計(jì)算每門學(xué)科成績(jī)排名。

按需求查詢數(shù)據(jù)：
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;

結(jié)果：
name    subject score   rp      drp     rmp
孫悟空   數(shù)學(xué)    95      1       1       1
柯南     數(shù)學(xué)    86      2       2       2
艾倫     數(shù)學(xué)    85      3       3       3
路飛     數(shù)學(xué)    56      4       4       4
柯南     英語(yǔ)    84      1       1       1
路飛     英語(yǔ)    84      1       1       2
艾倫     英語(yǔ)    78      3       2       3
孫悟空   英語(yǔ)    68      4       3       4
路飛     語(yǔ)文    94      1       1       1
孫悟空   語(yǔ)文    87      2       2       2
艾倫     語(yǔ)文    65      3       3       3
柯南     語(yǔ)文    64      4       4       4

1.3、自定義函數(shù)

1）Hive 自帶了一些函數(shù)，比如：max/min等，但是數(shù)量有限，自己可以通過(guò)自定義UDF來(lái)方便的擴(kuò)展。
2）當(dāng)Hive提供的內(nèi)置函數(shù)無(wú)法滿足你的業(yè)務(wù)處理需要時(shí)，此時(shí)就可以考慮使用用戶自定義函數(shù)
3）根據(jù)用戶自定義函數(shù)類別分為以下三種：
	--UDF（User-Defined-Function）
		一進(jìn)一出

	--UDAF（User-Defined Aggregation Function）
		用戶自定義聚合函數(shù)，多進(jìn)一出
		類似于：count/max/min

	--UDTF（User-Defined Table-Generating Functions）
		用戶自定義表生成函數(shù)，一進(jìn)多出
		如lateral view explode()
4）官方文檔地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5）編程步驟：
	①繼承Hive提供的類
 		org.apache.hadoop.hive.ql.udf.generic.GenericUDF  
		org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
	②實(shí)現(xiàn)類中的抽象方法
	③在hive的命令行窗口創(chuàng)建函數(shù)
		添加jar
			add jar linux_jar_path
		創(chuàng)建function
			create [temporary] function [dbname.]function_name AS class_name;
		④在hive的命令行窗口刪除函數(shù)
			drop [temporary] function [if exists] [dbname.]function_name;

1.4、自定義UDF函數(shù)

需求:自定義一個(gè)UDF實(shí)現(xiàn)計(jì)算給定字符串的長(zhǎng)度，例如：
	select my_len("abcd");
	4
1）創(chuàng)建一個(gè)Maven工程Hive
2）導(dǎo)入依賴
<dependencies>
	<dependency>
		<groupId>org.apache.hive</groupId>
		<artifactId>hive-exec</artifactId>
		<version>3.1.2</version>
	</dependency>
</dependencies>

3）創(chuàng)建一個(gè)類
/**
 * 一、自定義UDF函數(shù)，需要繼承GenericUDF類
 * 需求: 計(jì)算指定字符串的長(zhǎng)度
 */
public class Mylength extends GenericUDF {

    /**
     * 二、初始化方法,里面要做三件事
     * 1.約束函數(shù)傳入?yún)?shù)的個(gè)數(shù)
     * 2.約束函數(shù)傳入?yún)?shù)的類型
     * 3.約束函數(shù)返回值的類型
     * @param arguments  函數(shù)傳入?yún)?shù)的類型
     * @return
     * @throws UDFArgumentException
     */
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        //1.約束函數(shù)傳入?yún)?shù)的個(gè)數(shù)
        if (arguments.length != 1){
            throw new UDFArgumentLengthException("Input args num error!!!");
        }
        //2.約束函數(shù)傳入?yún)?shù)的類型
        if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE))
            //第一個(gè)參數(shù)：第幾個(gè)數(shù)據(jù)
            //第二個(gè)參數(shù) ：錯(cuò)誤信息
            throw new UDFArgumentTypeException(0,"Input args type error!!!");
        //3.約束函數(shù)返回值的類型
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    /**
     * 函數(shù)邏輯處理方法
     * @param arguments  函數(shù)傳入?yún)?shù)的值
     * @return
     * @throws HiveException
     */
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        //獲取函數(shù)傳入?yún)?shù)的值
        Object o = arguments[0].get();
        //將object轉(zhuǎn)換為字符串
        int length = o.toString().length();
        //因?yàn)樵谏厦娴某跏蓟椒ɡ锩嬉呀?jīng)對(duì)函數(shù)返回值類型做了約束,必須返回一個(gè)int類型
        //所以我們要在這個(gè)地方直接返回length
        return length;
    }

    /**
     * 返回顯示字符串方法,這個(gè)方法不用管,直接返回一個(gè)空字符串
     * @param children
     * @return
     */
    public String getDisplayString(String[] children) {
        return "";
    }
}

4）創(chuàng)建臨時(shí)函數(shù)
	①打成jar包上傳到服務(wù)器/opt/module/hive/datas/myudf.jar
	②將jar包添加到hive的classpath，臨時(shí)生效
		hive (default)> add jar /opt/module/hive/datas/myudf.jar;
	③創(chuàng)建臨時(shí)函數(shù)與開發(fā)好的java class關(guān)聯(lián)
		hive (default)> create temporary function my_len as "com.atguigu.test.Mylength";
	④即可在hql中使用自定義的臨時(shí)函數(shù)
		hive (default)> select ename,my_len(ename) ename_len from emp;
	⑤刪除臨時(shí)函數(shù)
		hive (default)> drop  temporary function my_len;

--注意：臨時(shí)函數(shù)只跟會(huì)話有關(guān)系，跟庫(kù)沒(méi)有關(guān)系。只要?jiǎng)?chuàng)建臨時(shí)函數(shù)的會(huì)話不斷，在當(dāng)前會(huì)話下，任意一個(gè)庫(kù)都可以
使用，其他會(huì)話全都不能使用。

5）創(chuàng)建永久函數(shù)
	①在$HIVE_HOME下面創(chuàng)建auxlib目錄(固定名稱不能更改)
		mkdir auxlib
	②將jar包上傳到$HIVE_HOME/auxlib下，然后重啟hive
	③創(chuàng)建永久函數(shù)
		hive (default)> create function my_len2 as " com.atguigu.test.Mylength";
	④即可在hql中使用自定義的永久函數(shù) 
		hive (default)> select ename,my_len2(ename) ename_len from emp;
	⑤刪除永久函數(shù) 
		hive (default)> drop function my_len2;

--注意：永久函數(shù)跟會(huì)話沒(méi)有關(guān)系，創(chuàng)建函數(shù)的會(huì)話斷了以后，其他會(huì)話也可以使用。
永久函數(shù)創(chuàng)建的時(shí)候，在函數(shù)名之前需要自己加上庫(kù)名，如果不指定庫(kù)名的話，會(huì)默認(rèn)把當(dāng)前庫(kù)的庫(kù)名給加上。
永久函數(shù)使用的時(shí)候，需要在指定的庫(kù)里面操作,或者在其他庫(kù)里面使用的話加上 庫(kù)名.函數(shù)名

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“Hive函數(shù)怎么用”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!