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;