hive的常見函數(shù)

查看函數(shù)的介紹（必讀）：

show functions ; #查看hive中的所有內(nèi)置函數(shù)

desc function extended 函數(shù)名; #查看某個函數(shù)的詳細介紹

1.hive中常見的內(nèi)置函數(shù)

1）集合函數(shù)

 創(chuàng)建數(shù)組
  array(ele1,ele2,ele3)
  例：select array(1,2,3,4);
  
 判斷值是否在數(shù)組中
  array_contains(arr,value)
  例：select array_contains(array(1,2,3,4),5);
  
 創(chuàng)建一個map（基數(shù)為key,偶數(shù)為value）
  map(key0,value0,key1,value1…)
  例：select map('zs',1,'ls',2)
  
 返回map中所有的key
  map_keys(map)
  例：select map_keys(map('zs',1,'ls',2)
  
 返回map中所有的value
  map_values(map)
  例：select map_values(map('zs',1,'ls',2)
  

2）數(shù)值函數(shù)

 小數(shù)四舍五入：
   round(x,[d]) 參數(shù)1：浮點數(shù),參數(shù)二：保留的位數(shù)
   例：select round(4.5,1) 返回5
   例：select round(5.1) 返回5 默認保留整數(shù)位

 向上取整：
   celi(num)
  例：ceil(5.1) #6
 向下取整：
   floor(num)
  例：floor(5.1) #6

3）字符串操作

 substr(str,pos,len) #截取字符串（下標從1開始）
  例：select substr(‘a(chǎn)bcd’,1) abcd
  例：select substr(‘a(chǎn)bcd’,1,1) a
  ps：substr和substring用法相同
 instr(str,substr) #返回子串開始的位置
  例：instr(‘a(chǎn)bcd’,’cd’) 3
  例：instr(‘a(chǎn)bcd’ ,’zy’) 0 #沒有的默認返回0

 split(str,regex) #字符串切分，返回一個數(shù)組
  例：select split(‘hello world’,’ ’)

 concat(str1,str2…) #字符串拼接
  例：concat(‘a(chǎn)b’,’cd’,’ef’) ‘a(chǎn)bcdef’

 concat_ws(separarot,[string|array<string>]) #字符串拼接
  例：select concat_ws(‘,’,’ab’,’cd’,’ef’) #’ab,cd,ef’
  例：select concat_ws(',','a',array('b','c')); ‘a(chǎn),b,c’
 大小寫轉(zhuǎn)化
lcase /lower #字符串轉(zhuǎn)化為小寫
ucase /upper #字符串轉(zhuǎn)化為大寫

 nvl #字符串判斷
  例：select nvl(value,'delfaut') #如果前者為null,返回后者

 if
 語法：if(表達式,返回值1,,返回值2)
  例： if(value is null ,'default',value) ,表達式為true，返回返回值1，否則返回返回值2

4）日期操作

unix_timestamp(data,format) #返回指定日期的時間戳
  例：

  例：select unix_timestamp('2018-9-1','yyyy-MM-dd'); #返回給定日期的時間戳

from_unixtime(timestamp,format) #返回相應(yīng)時間戳的時間
例：from_unixtime(1151561,’yyyy-MM-dd’)

year(data) #返回給定日期的年
  例： year('2018-5-4') #返回2018
  相應(yīng)的函數(shù)還有：month、day、hour、minute、second

weekofyear(data) #返回相應(yīng)日期，是一年中的第幾周
  例：select weekofyear('2018-5-5') ;

datediff(date1,date2) #兩個日期相差的天數(shù)
  例：select datediff('2018-5-9','2018-5-10');

5）表生成函數(shù)

  語法：explode(a) a可以是一個array,或者map,將數(shù)組或者map炸裂為多行
 例：select explode(array(1,2,3));

 例：select explode(map('zs',1,'ls',2));

實際應(yīng)用：

#建表語句
create table user_info(name string,info map<string,string>) row format delimited fields terminated by ‘\t’ collection items terminated by ‘,’ map keys terminated by ‘:’

#數(shù)據(jù)格式：zs age:28,salary:20000,address:beijing

#生成數(shù)據(jù)：
zs age:28
zs salary:20000
zs address:Beijing

#使用表生成函數(shù)解決：
select name,t.* from user_info lateral view explode(info) t;

2. 自定義函數(shù)

在hive中函數(shù)的分類：

這里我們自定義UDF，一路經(jīng)一路出。
第一步：自定義Java類（導(dǎo)入hive依賴，編寫類繼承UDF）
注意：方法的名稱一定要是evaluate?。。?！

package com.zy.mr.hive;
import org.apache.hadoop.hive.ql.exec.UDF;
public class MyUDF extends UDF{
    /**
     * 參數(shù)：參數(shù)就是調(diào)用函數(shù)傳入的參數(shù)
     * 返回值就是，調(diào)用的函數(shù)的返回值
     * 
     * 
     * 注意：
     * 1.方法的修飾符必須為public
     * 2.返回值不能為void
     * 3.一般的參數(shù)也不能
     */
    //三個數(shù)求和
    public int evaluate(int num1,int num2,int num3) {
        return num1+num2+num3;
    }
    //ip位數(shù)補齊  192.166.1.1 -----192.168.001.001
    public  String evaluate(String ip) {
        String[] split = ip.split("\\.");
        for(int i=0;i<split.length;i++) {
            split[i]="000"+split[i];
            split[i]=split[i].substring(split[i].length()-3);
        }
        return split[0]+"."+split[1]+"."+split[2]+"."+split[3];
    }
}

第二步：打jar包，上傳到Linux
第三步：將jar包放入hive的classpath下：add jar ../xx..jar
第四步：驗證是否添加成功：list jars;
第五步：創(chuàng)建臨時函數(shù)，關(guān)聯(lián)自定義函數(shù)：create temporary function func_my as '類的權(quán)限定名稱'
第六步：驗證是否關(guān)聯(lián)成功：show functions; 此時hive的內(nèi)置函數(shù)庫中會多一個函數(shù)
第七步：使用自定義函數(shù)

3. hive中的分析函數(shù)

 分析函數(shù)的介紹： 分析函數(shù)有三種：row_number()，rank()，dense_rank() 三種函數(shù)需要與聚合函數(shù)共同使用。也可以與over()一起使用。
  語法：
  row_number() over(partition by /distribute by order by /sort by )
  rank () over(partition by /distribute by order by /sort by )
  dense_rank() over(partition by /distribute by order by /sort by )

 三種函數(shù)的區(qū)別：

 實際應(yīng)用：
數(shù)據(jù)：
95002 劉晨 女 19 IS
95017 王風(fēng)娟 女 18 IS
95018 王一 女 19 IS
95013 馮偉 男 21 CS
95014 王小麗 女 19 CS
95019 邢小麗 女 19 IS
95020 趙錢 男 21 IS
95003 王敏 女 22 MA
95004 張立 男 19 IS
95012 孫花 女 20 CS
95010 孔小濤 男 19 CS
95005 劉剛 男 18 MA
95006 孫慶 男 23 CS
95007 易思玲 女 19 MA
95008 李娜 女 18 CS
95021 周二 男 17 MA
95022 鄭明 男 20 MA
95001 李勇 男 20 CS
95011 包小柏 男 18 MA
95009 夢圓圓 女 18 MA
95015 王君 男 18 MA

需求：列出每個部門中年齡最小的三個
解決：

#step1：
create table stu_step1 as select * ,row_number over(partition by department  order by age desc ) as top from student_manager  ;
# step2:
selecet * from stu_step1  where top <=3 ;

4. hive中處理json

以一個案例為準：
數(shù)據(jù)：
['{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}','{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}' ,'{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}' ,'{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}' ,'{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}' ,'{"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}' ,'{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}' ,'{"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"}' ,'{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}']
使用的函數(shù)：get_json_object(json,path) ，json是一個json字符串
path是解析的路徑。
例：
select get_json_object('{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}',$.movie)

解釋：在這個函數(shù)的path中
$：表示根目錄 {}
.：表示子節(jié)點 moive rate timestamp
[]：表示數(shù)組的元素
*：表示數(shù)組中的所有
例：以上面的數(shù)據(jù)為例：
select get_json_object(json,$[*].movie) 取上面數(shù)據(jù)的所有的json中的movie的值。

5. transform

 Transform是一個hive的腳本解析方式（shell和python 腳本）
 需求：統(tǒng)計周一到周日哪一天的觀影人數(shù)最多？
 數(shù)據(jù)：
{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"} {"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"} {"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"} {"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"} {"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"} {"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"} {"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"} {"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"} {"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}
第一步 ： 建表：

#建表：
create table movie_01(line string);
#加載數(shù)據(jù)
load data local inpath '/home/hadoop/movie' into table movie_01;
#Json解析原始表
create table movie_02 as  
select  
get_json_object(line,'$.movie') as moive_id ,  
get_json_object(line,'$.rate') as rate ,  
get_json_object(line,'$.timeStamp') as `timeStamp`,  
get_json_object(line,'$.uid') as  userid 
from movie_01; 

第二步 ： 編寫Python腳本：

#!/usr/bin/python
import sys
import datetime
for line in sys.stdin:
line = line.strip()
movie,rate,unixtime,userid = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movie, rate, str(weekday),userid])

第三步 ： 在hive中調(diào)用腳本解析數(shù)據(jù)
將腳本文件加載到hive的classpath下：add file /home/hadoop/datas/my.py;

檢驗：list files;/ list file;

第四步 ： 查詢解析

## 
select transform(moive_id,rate,timestamp,userid) using 'python my.py' as (movieid,rate,week_day,userid)  from movie_02;
#transform:向腳本中傳入的參數(shù)
#using:調(diào)用腳本（shell 是  sh  xxx.sh）
# as后面是別名