hive高频函数(一)

符串处理类

nvl

第一个参数是null则用第二个值显示

hive> select nvl(null,1);
1

hive> select nvl('a',1);
a

这个函数我们经常不希望有null出现的时候，把null转化成一个默认值

trim

去掉一头一尾的空格

hive> select trim(' Jeremy Jhon ');
Jeremy Jhon

regexp

这个函数就是正则匹配的函数，返回匹配成功或者失败

hive> select 'QQqq' regexp('.*qq.*');
true

regexp_replace

这个函数本来的意思是匹配到替换正则匹配到的内容

hive> select regexp_replace('qaa91a9a9b','\\d{1}','替换部分');
qaa替换部分替换部分a替换部分a替换部分b

我们经常用的操作是把字符串中的空格干掉

hive> select regexp_replace('qaa a b','\\s','');
qaaab

我们想要把中文的空格也一起去掉的话，这么干:

select regexp_replace(nvl(" aa aa   bbfg  d   你好 ",''),'[\\s]+|[\\u3000]+|[\,]','');
aaaabbfgd你好

coalesce

这个函数可以传入多列，遇到第一个值不为零的时候停止

hive> select coalesce(null,1);
1

hive> select coalesce(null,null,null,1);
1

这个操作我们经常 在hive etl中做数据合并的时大量使用到，我们需要把前一天的数据和当天的数据做合并，这种时候如果当天数据是null，我们则使用前一天的数据，这样子达到一个merge的效果
类似这样：

insert overwrite table dw.dw_business partition (dt='${dt}')
 select
 coalesce(t1.id, t2.id) as id
,coalesce(t1.name, t2.name) as name
...
,coalesce(t1.data_from, t2.data_from) as data_from
 from (select * from dw.dw_business where dt='${dt}') t1
 full outer join (select * from dw.dw_business where dt=get_dt_date('${dt}',-1) ) t2
 on (t1.id = t2.id and t1.data_from = t2.data_from) ;

concat

这个就是简单把几个字符串连接起来

hive>  select concat('a','b','c','d');
abcd

这个函数在连接的时候发现null了，就会返回null

hive> select concat('a','b',null);
NULL

concat_ws

这个函数允许我们指定一个连接符号去做字符串链接

hive> select concat_ws('-','b','c','d');
b-c-d

这个函数会把null给忽略掉

hive> select concat_ws('','a','b',null);
ab

instr

这个函数会返回后面字符串在原有字符串的位置

hive> select instr("abcde",'b');
2
b在abcde中出现位置是2，不存在的时候则返回0，我们用这个函数可以判断字符串的包含关系

条件判断

条件判断其实是为了实现一些程序上面的逻辑，到了sql下面也有条件判断的操作

if

hive> select if(1<2,'a','b');
a

hive> select if(1>2,'a','b');
b

基本操作就是if后面带条件，true就输出第一个，false就输出第二个,这个操作在我们判断某些结果的时候大量使用到:

select if(cnt==0,'数据为空','有数据') as res from 
( select  count(1) as cnt   from dw.dw_business where dt='${dt}'  ) t

case when

case when就是多分枝的情况，我们希望满足什么场景就取什么值。

hive> select
    >  case 
    >  when a>=90  then '优秀' 
    >  when a<90 and a>70  then '良好'
    >  when a<70 and a>60  then '及格' 
    >  else '不及格' end as level from (select 90 as a ) t;
优秀