hive的常用内置函数

1.日期函数

返回值

函数

案例/备注

string

date_add(开始日期,需要累加的天数

select date_add(‘2020-01-08 12:13:15’,5) ==> 累加5天，返回2020-01-13

string

date_sub(开始日期，需要减去天数）

select date_sub(2020-01-08 12:13:14’,5)==>减去5天，返回2020-01-03

int

datediff(结束日期，起始日期）

select datediff(‘2020-08-06 12:13:14’,’2020-06-05 08:09:10’) ==>相差1天，忽略时分秒

string

date_format(date/timestamp/string,’yyyy’)

select (current_timestamp,’MM-dd’)==>注意 这个函数只能是date／timestamp／string类型

int

unix_timestamp()

返回的是int类型，返回的是秒值，格式化这种数据使用的是from_unixtime(t,’yyyy-MM-dd’)

timestamp

current_timestamp

返回的是一个timestamp类型，返回的是带有毫秒值，格式化这种数据使用的是date_format(t,’MM-dd’)

bigint

unix_timestamp(‘yyyy-MM-dd HH:mm:ss’)

select unix_timestamp(‘2019-09-09 12:13:14’) ==>获取该时间的秒值

string

from_unixtime(unix_timestamp(),’yyyy-MM-dd HH:mm:ss’)

select from_unixtime(unix_timestamp(),’yyyy-MM’)==>通过时间戳我只想要年和月

string

to_date(‘yyyy-MM-dd HH:mm:ss’)

select to_date(‘2020-07-13 12:13:14’);==>返回的是字符串类型的时间，只有时间

int

year \month\ day \hour minute second weekofyear

2.类型转换函数

返回值

函数

案例/备注

type

cast( 1 as string)

select cast(1 as double); ===> 把1转换成double类型用于计算

3.条件函数

返回值

函数

案例/备注

T

if(条件,true,false)

条件为真，执行true，否则执行false

T

nvl(comm,0)

如果comm字段为null，执行0

T

coalesce(comm,v1,v2,v3…)

如果comm为null执行v1，如果v1为null执行v2以此类推

T

case when deptno=10 then ‘a’ else ‘b’ end

判断

T

case deptno when 10 then ‘a’ else ‘b’ end

判断

Boolean

isnull(comm)

如果comm为null，返回true

Boolean

isnotnull(comm)

如果comm不为null，返回true

4.标准函数

返回值

函数

案例/备注

string

concat(‘a’,’b’…)

select concat(‘a’,’b’,’c’) ==>abc

int

instr(‘ababab’,’ab’)

select instr(‘ababcabab’,’abc’) ====>返回子字符串的位置 3

int

length(‘aaaaaa’)

select length(‘aaaaaa’) ===>返回字符串的长度 6

int

locate(‘ab’,’ababababaab’,4)

select locate(‘abc’,’ababcccabcddab,4);===>返回子字符串的位置，从第4个位置开始

string

lower(‘AAA’)

select lower(‘AAA’)===>返回小写的字符串 aaa

string

upper(‘ssss’)

select upper(‘aaa’) ====> 返回大写的字符串AAA

string

regexp_replace(‘aa 123 bb’,’[1-9]{3}’,’w’)

select regexp_replace(‘aa 123 bb’,’[1-9]{3}’,’w’); 正则\s+ 代表的就是空白

array

split(‘aa bb cc’,’ ‘)

select split(‘a b c’, ‘ ‘); ==>产生一个字符串数组

string

substr/substring(‘aback’,1,2)

select substr(‘aback’,1,2); ==>截取子字符串，第一个字符代表1，从1开始，一共截取2个字符

string

trim(‘ aa bb ‘)

select trim(‘ aa bb cc ‘);===>只去掉前后空格

map

str_to_map(’name-zs&age-18’,’&’,’-“)

select str_to_map(’name-zs&age-18’,’&’,’-‘);====>最后产生一个map结构 {“name”:“zs”,“age”:"18”}

binary

encode(‘aaa’,’utf-8’)

select encode(‘aaa’,’utf-8’) ===> 以utf-8进行对aaa字符串进行编码

5.自定义函数

# 5.1 集成UDF
  5.2 需要编写一个方法 ，方法名必须是 evaluate 
  5.3 打包上传到linux
  5.4 在hive中加载jar包 add jar /tmp/xxxxx.jar
  5.5 创建函数 create function fun as ‘包名.类名’
  5.6 使用
# 案例演示：
public class StringToLower extends UDF {
    public Text evaluate(Text str){
        if(str == null){
            return null;
        }
        return new Text(str.toString().toLowerCase());
    }
}

# 打成jar包、虚拟机下输入以下命令
add jar /tmp/zhibo2_6-1.0-SNAPSHOT.jar;

create function string2lower as 'cn.kgc.hive.StringToLower’; 创建了一个永久函数

0: jdbc:hive2://localhost:10000> select string2lower('aaaSSSddddeeeggggAAAAAAAAAAAA');
+--------------------------------+--+
|              _c0               |
+--------------------------------+--+
| aaasssddddeeeggggaaaaaaaaaaaa  |
+--------------------------------+————————————————+


当退出hive之后，再次进入，需要重新加载jar包

（1）如果本地加载jar包，退出再次进入，需要重新加载jar包，并且重新创建函数，然后才能使用.

（2）如果是hdfs的方式加载jar包，退出再次进入，不需要重新弄加载jar包，也不需要重新创建函数，直接使用即可.
add jar hdfs://hadoop1:9000/home/zhibo2_6-1.0-SNAPSHOT.jar;
删除函数：drop funcation str2lower;

（3）直接把jar包放在 hive目录下的 auxlib目录下即可。/opt/install/hive-1.1.0-cdh5.14.2/auxlib,不需要手动加载，每次启动hive自动加载这个目录下的jar包。


临时函数与永久函数的区别？临时函数只要hive的客户端不退出，在任何数据库中都可以使用；；；永久函数，在哪个数据库中创建的，只能在哪个数据库中使用.
    
    
统计单词的个数:wordcount案例。
select word, count(word) 
    from (select explode(split('aa bb cc dd aa aa bb bb ee',' ')) word)t1 
    group by word;