hive的内置函数大全

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;