08-hive中的函式

hive內置函式

在Hive中，函式主要分兩大型別，一種是內置函式，一種是用戶自定義函式，

函式查看

show functions;
desc function functionName;

日期函式

1）當前系統時間函式: current_date(). current_timestamp()、unix_timestamp()

-- 函式1:current_date();
當前系統日期  格式:"yyyy-MM-dd"
-- 函式2:current_timestomp();
當前系統時問戳:格式:"yyyy-MM-dd HH:mmm:ss.ms"
-- 函式3: unix_timestamp();
當前系統時間戳格式:距離1970年1月1日0點的秒數，

2)日期轉時間戳函式: unix_timestamp()

格式: unix_timestamp([date[,pattern]])
案例:
select unix_timestamp( '1970-01-01 0:0:0'); -- 傳入的日期時間是東八區的時間，回傳值是相對于子午線的時間來說的 
select unix_timestamp('1970-01-01 8:0:0')  #0
-- 自定義格式
select unix_timestamp('0:0:0 1970-01-01' ,"HH:mm:ss yyyy-MM-dd");
select unix_timestamp(current_date());

3)時間戳轉日期函式: from_unixtime

語法:from__unixtime(unix_time[,pattern])
案例;
select from_unixtime(1574092800);
select from_unixtime(1574096401,'yyyy/MM/dd');
select from_unixtime(1574896401,'yyyy-MM-dd HH:mm:ss');
select from_unixtime(0,'yyyy-MM-dd HH:mm:ss');
select from_unixtime(-28800,"yyyy-MM-dd HH:mm:ss";

4)計算時間差函式: datediff().months_between0

格式: datediff(date1，date2) - Returns the number of days between date1 and date2
select datediff("2019-11-20","2019-11-01");  #回傳19
select datediff("2019-11-01","2019-11-19");  #回傳-18

格式: months_between(date1,date2) - returns number of months between dates date1 and date2
select months_between( '2019-11-20','2019-11-01'); #回傳0.6 （個月）
select months_between( '2019-10-30','2019-11-30'); #回傳 1 （個月）
select months_betweenC '2019-10-31','2019-11-30');
select months_between( '2019-11-00','2019-11-30');

5)日期時間分量函式: year()、 month0、day0、 hour()、minute()、 second()

-- 案例
select year(current_date);
select month(current_date);
select day(current_date);
select year(current_timestamp);
select month(current_timestamp);
select day(current_timestamp);
select hour(current_timestamp);
select minute(current_timestamp);
select second(current_timestamp);

select dayofmonth(current_date); #今天是這個月的第幾天
select weekofyear(current_date); #這周是今年的第幾周

6)日期定位函式:last_day()、next_day()

-- 月末:
select last_day(current_date)
-- 下周
select next_day(current_date,'thursday') ; #下一個thursday是什么日期

7)日期加減函式: date_addo.date_subo、add_months()

select date_add(current_date,1);
select date_sub(current_date,90);
select add_months(current_date,12;
select date_add('2019-12-10',1); #回傳 2019-12-11

8)字串轉日期: to_date(

(字串必須為: yyyy-MM-dd格式)
select to_date('2017-01-01 12:12:12');

9)日期轉字串(格式化) 函式: date_format

select date_format(current_timestamp(),'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(),'yyyyMMdd');
select date_format( '2017-21-01','yyyy-MM-dd HH:mm:ss');

字串函式

lower -- (轉小寫)
select lower('ABC');

upper -- (轉大寫)
select upper('abc');

length -- (字串長度，字符數)
select length('abc');

concat -- (字串拼接)
select concat('A','B','C');   # ABC

concat_ws -- (指定分隔符)
select concat_ws('-','a','b','c'); # a-b-c

substr-- (求子串)
select substr('abcde',3); 	# cde
select substr('abcde',2,3);	# bc

split(str ,regex) -- 切分字串，回傳陣列，
select split("a-b-c-d-e-f","-");

型別轉換函式

cast(value as type) -- 型別轉換
select cast("123" as int)+1;

數學函式

round -- 四舍五入((42.3 ->42))
select round(42.3);

ceil -- 向上取整(42.3=>43)
select ceil(42.3);

floor -- 向下取整(42.3=>42)
select floor(42.3);

其他常用函式

nvl(value ,default value):如果value為null,則使用default value，否則使用本身value.


select if(1>2 ,ture,false);  # 回傳false

select coalesce(1,2,3,4,5) # 回傳第一個不為空的 本題回傳 1
select coalesce(NULL,2,3,4,5) # 本題回傳 2

hive的視窗函式（重點）

視窗函式over簡介
先來看一下這個需求:求每個部門的員工資訊以及部門的平均工資，在mysql中如何實作呢

SELECT emp.*,avg_sal
FROM emp
J0IN (
SELECT deptno
,round(AVG(ifnull(sal, 0))) AS avg_sal
    FR0M emp
	GROUP BY deptno
) t
	ON emp.deptno = t.deptno
	ORDER BY deptno;
	
select emp.*,(select avg(ifnull(sal,0)) from emp B where B.deptno = A.deptno) from emp A;

通過這個需求我們可以看到，如果要查詢詳細記錄和聚合資料，必須要經過兩次查詢，比較麻煩，這個時候，我們使用視窗函式，會方便很多，那么視窗函式是什么呢?

-1)視窗函式又名開窗函式，屬于分析函式的一種，
-2)是一種用于解決復雜報表統計需求的函式，
-3）視窗函式常用于計算基于組的某種值，它和聚合函式的不同之處是:對于每個組回傳多行，而聚合函式對于每個組只回傳一行，簡單的說視窗函故對每條詳細記錄開一個面口,進行聚合遶計的查詢
-4)開窗函式指定了分析函式作業的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化，
-5)視窗函式一般不單獨使用
-6)視窗函式內也可以分組和排序

案例準備

資料準備order.txt

姓名 購買日期 購買數量

saml，2018-01-01，10

tomy，2018-01-02，15

saml，2018-02-03，23

…

-- 1.創建order表：
create table if not exists t_order(
	name string,
    orderdata string,
    cost int
)row format delimited fields terminated by ',';

-- 2.加載資料
load data local inpath "./root/order.txt" into table t_order;

需求:查詢每個訂單的資訊，以及訂單的總數

– 1.不使用視窗函式

-- 查詢所有明細
select * from t_order;
#查詢總量
select count(*) from t_order;

– 2.使用視窗函式:通常格式為可用函式+over()函式

select *, count(*) over() from t_order;
-- 查詢回傳的結果
saml，2018-01-01，10  3
tomy，2018-01-02，15  3
saml，2018-02-03，23  3

注意:

視窗函式是針對每一行資料的.
如果over中沒有指定引數默認視窗大小為全部結果集

需求:查詢在2018年1月份購買過的顧客購買明細及總人數

select * ,count(*) over()
from t_order
where substring(orderdate,1,7) = '2018-01';
-- 查詢回傳的結果
saml，2018-01-01，10  2
tomy，2018-01-02，15  2

distribute by 子句

在over視窗中進行分組,對某一欄位進行分組統計,視窗大小就是同一個組的所有記錄

需求:查看顧客的購買明細及月購買總額

select name,orderdate,cost,sum(cost) over (distribute by month(orderdate))
from t_order;

saml，2018-01-01，10  33
tomy，2018-01-02，15  15
saml，2018-02-03，23  33

需求:查看顧客的購買明細及每個顧客的月購買總額

select name,orderdate,cost,sum(cost) over (distribute by name, month(orderdate))
from t_order;

saml，2018-01-01，10  33
tomy，2018-01-02，15  15
saml，2018-02-03，23  33

sort by 子句

sort by子句會讓輸入的資料強制排序(強調:當使用排序時，視窗會在組內逐行變大)

需求:查看顧客的購買明細及每個顧客的月購買總額,并且按照日期降序排序

select name,orderdate,cost,
sum(cost) over (distribute by name, month(orderdate)
sort by orderdata desc)
from t_order;

saml，2018-01-01，10  10
saml，2018-02-03，23  33
saml，2018-02-03，53  86
tomy，2018-01-02，15  15

hive的window子句

如果要對視窗的結果做更細粒度的劃分,那么就使用window子句,常見的有下面幾個

PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:當前行
UNBOUNDED:起點,
UNBOUNDED PRECEDING:表示從前面的起點,
UNBOUNDED FOLLOWING:表示到后面的終點

需求：查看顧客到目前為止的購買總額

select name,
	t_order.orderdata,
	cost,
	sum(cost) over(partition by name order by orderdata rows between UNBOUNDED PRECEDING and current row) as allCount
from t_order;

需求：求每個顧客最近三次的消費總額

select name,orderdate,cost,
sum(cost) over(partition by name order by orderdata rows between 2 PRECEDING and current row)
from t_order;

Hive的序列函式

NTILE

ntile 是Hive很強大的一個分析函式，可以看成是:它把有序的資料集合平均分配到指定的數量(num)個榻中,將桶號分配給每一行，如果不能平均分配，則優先分配較小編號的桶，并且各個桶中能放的行數最多相差1，

例子:

select name,orderdate,cost,
ntile(3) over(partition by name), #按照name進行分組，在分組內將資料切成3份
from t_order;

hive的排名函式

準備資料 stu_score.txt

create table if not exists stu_score(
	userid int,
    classno string,
    score int
)
row format delimited
fields terminated by ' ';

local data inpath './root/stu_score.txt' overwrite into table stu_score;

hive的自定義UDF函式

介紹

案例

idea maven專案HiveFunction 在pom.xml加入以下maven的依賴包

<property>
	<groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
	<version>2.1.1</version>
</property>

寫個類 ConcatString

public class ConcatString extends UDF{
    public String evaluate(String str){
        return str+"!";
    }  
}

注意

1.繼承org.apache.hadoop.hive.al.exec.UDF
2.撰寫evaluate()，這個方法不是由介面定義的,因為它可接受的引數的個數,資料型別都是不確定的，Hive會檢查UDF ,看能否找到和函式呼叫相匹配的evaluate()方法

第一種：命令加載（只針對當前的session有效）

打包 雙擊package
HiveFunction-1.0.jar
上傳上去

[root@tianqinglong01 ~]# mv HiveFunction-1.0.jar function.jar

# 將撰寫好的UDF打包并上傳到服務器，將jar包添加到hive的classpath中
hive> add jar /root/function.jar
hive> create temporary function my_concot_str as 'com.qf.ConcatString'; #創建一個自定義的臨時函式名
hive > show functions; # 查看我們創建的自定義函式
hive > select my_concot_str("hello") # 使用函式 回傳hello!

# 洗掉自定義函式
hive> drop temporary function if exists my_concot_str;

第二種方式:組態檔加載(只要用hive命令列啟動都會加載函式)

1、將撰寫好的自定函式上傳到服務器
2、在hive的安裝目錄下的bin目錄中創建一個檔案，檔案名為.hiverc
[root@tianqinglong01 hive]#vi ./bin/.hiverc
3、將添加函式的陳述句寫入這檔案中
vi $HIVE_HOME/bin/.hiverc
add jar /root/function.jar
create temporary function my_concot_str as 'com.qf.ConcatString'; 4、直接啟動hive

jar一般放在lib目錄下