Hive的常⽤函数（nvl，casewhen，⾏转列，列转⾏，爆炸函数）
isea_you 2018-12-06 14:52:04 3623 收藏 20
分类专栏： Hive bigData Hive
版权
⽬录
综述：
NVL：
case when:
⾏转列，CONCAT，CONCAT_WS，COLLECT_SET：
列转⾏：EXPLODE，LATERAL VIEW：
综述：
在Hive中有六类常⽤的函数，除了本⽂讲述的四类函数之外，还有rank函数，和开窗函数，Hive中的⾃定义函数⼀般被分成了三类
UDF:uer-defined-function ⼀进⼀出
UDAF:user-defined-aggregation-function 多进⼀出
UDTF:user-defined-generating-function ⼀进多出
我们可以通过相关的命令来获取某个函数的⽤法：
show functions 查询所有的⾃带函数
desc function extended case; 获得例⼦+⽤法
desc function case; 获得函数的⽤法
NVL：
nvl(value,default_value)如果value为null，则返回default_value的值，否则返回value
select comm,nvl(comm,-1) from emp;
0: jdbc:hive2://hadoop108:10000> select comm,nvl(comm,-1) from emp;
±--------±--------±-+
| comm | _c1 |
±--------±--------±-+
| NULL | -1.0 |
| 300.0 | 300.0 |
| 500.0 | 500.0 |
| NULL | -1.0 |
| 1400.0 | 1400.0 |
| NULL | -1.0 |
| NULL | -1.0 |
| NULL | -1.0 |
| NULL | -1.0 |
| 0.0 | 0.0 |
| NULL | -1.0 |
| NULL | -1.0 |
| NULL | -1.0 |
| NULL | -1.0 |
±--------±--------±-+
如果员⼯的comm为NULL，则⽤领导id代替
select comm,nvl(comm,mgr) from emp;
0: jdbc:hive2://hadoop108:10000> select comm,nvl(comm,mgr) from emp;
±--------±--------±-+
| comm | _c1 |
±--------±--------±-+
| NULL | 7902.0 |
| 300.0 | 300.0 |
| 500.0 | 500.0 |
| NULL | 7839.0 |
| 1400.0 | 1400.0 |
| NULL | 7839.0 |
| NULL | 7839.0 |
| NULL | 7566.0 |
| NULL | NULL |
| 0.0 | 0.0 |
| NULL | 7788.0 |
| NULL | 7698.0 |
| NULL | 7566.0 |
| NULL | 7782.0 |
±--------±--------±-+
case when:
[isea@hadoop108 datas]$ cat
悟空 A 男
⼋戒 A 男
刘备 B 男
嫦娥 A ⼥
⼤乔 B ⼥
⼩乔 B ⼥
需求：求出部门和对应的员⼯的性别⼈数
A 2 1
B 1 2
创建hive表并导⼊数据：
create table emp_sex(name string,dept_id string,sex string)
row format delimited
fields terminated by ‘\t’;
load data local inpath ‘/opt/module/datas/’ into table emp_sex; 0: jdbc:hive2://hadoop108:10000> select * from emp_sex;
±--------------±-----------------±-------------±-+
| emp_sex.name | emp_sex.dept_id | emp_sex.sex |
±--------------±-----------------±-------------±-+
| 悟空 | A | 男 |
| ⼋戒 | A | 男 |
| 刘备 | B | 男 |
| 嫦娥 | A | ⼥ |
| ⼤乔 | B | ⼥ |
| ⼩乔 | B | ⼥ |
±--------------±-----------------±-------------±-+
select dept_id,count(sex) from emp_sex group by dept_id;
±---------±-----±-+
| dept_id | _c1 |
±---------±-----±-+
| A | 3 |
| B | 3 |
±---------±-----±-+
我们来尝试分析⼀下，如果单纯的使⽤group by 对dept_id 分组和聚合函数sum，我们求的是部门下所有的员⼯的总数，并不是对于区分性别，所以我们需要⼀种⽅式，当检查到sex为男的时候，记录⼀下man变量为1，当检查到sex为⼥的时候对women 加1
所以使⽤下⾯的查询语句：
select dept_id,sum(case sex when ‘男’ then 1 else 0 end) male_count,
sum(case sex when ‘⼥’ then 1 else 0 end) female_count
from emp_sex group by dept_id;
±---------±------------±--------------±-+
| dept_id | male_count | female_count |
±---------±------------±--------------±-+
| A | 2 | 1 |
| B | 1 | 2 |
±---------±------------±--------------±-+
或者使⽤下⾯这种功能类似的写法
select dept_id,sum(case when sex = ‘男’ then 1 else 0 end) male_count,
sum(case when sex = ‘⼥’ then 1 else 0 end) female_count
from emp_sex group by dept_id;
±---------±------------±--------------±-+
| dept_id | male_count | female_count |
±---------±------------±--------------±-+
| A | 2 | 1 |
| B | 1 | 2 |
±---------±------------±--------------±-+
⾏转列，CONCAT，CONCAT_WS，COLLECT_SET：
什么是⾏转列呢？
将类似于下⾯的形式：
A
B
C
转化为类似于下⾯的形式：
A B C
即将多⾏转化为⼀⾏，排在⼀⾏了，就成了⼀列对吧
关于⾏转列主要有三个函数，
concat:
select concat(‘liubei’,‘xihuan’,‘xiaoqiao’);
0: jdbc:hive2://hadoop108:10000> select concat(‘liubei’,‘xihuan’,‘xiaoqiao’);
OK
±----------------------±-+
| _c0 |
±----------------------±-+
| liubeixihuanxiaoqiao |
±----------------------±-+
concat_ws:
0: jdbc:hive2://hadoop108:10000> select concat_ws(’|’,array(‘liiubei’,‘xihuan’,‘xiaoqiao’)); OK
±-------------------------±-+
| _c0 |
±-------------------------±-+
| liiubei|xihuan|xiaoqiao |
±-------------------------±-+
1 row selected (0.096 seconds)
collect_set(col)将传⼊的内容去重，并放置到⼀个数组中。
[isea@hadoop108 datas]$
悟空 ⽩⽺座 A
张飞 射⼿座 A
刘备 ⽩⽺座 B
⼋戒 ⽩⽺座 A
⼩乔 射⼿座 A
创建hive表并导⼊数据：
create table person_info(name string,contellation string,blood_type string)
row format delimited
fields terminated by ‘\t’;
load data local inpath ‘/opt/module/’ into table person_info;
0: jdbc:hive2://hadoop108:10000> select * from person_info;
OK
±------------------±--------------------------±------------------------±-+
| person_info.name | llation | person_info.blood_type |
±------------------±--------------------------±------------------------±-+
| 悟空 | ⽩⽺座 | A |
| 张飞 | 射⼿座 | A |
| 刘备 | ⽩⽺座 | B |
| ⼋戒 | ⽩⽺座 | A |
| ⼩乔 | 射⼿座 | A |
±------------------±--------------------------±------------------------±-+
把星座和⾎型⼀样的⼈归类到⼀起:
t：先将星座和⾎型归类到⼀起，
select name,concat(contellation,",",blood_type) base
from person_info;
±------±-------±-+
| name | base |
±------±-------±-+
| 悟空 | ⽩⽺座,A |
| 张飞 | 射⼿座,A |
| 刘备 | ⽩⽺座,B |
| ⼋戒 | ⽩⽺座,A |
| ⼩乔 | 射⼿座,A |
±------±-------±-+
select t.base,concat_ws(’|’,collect_set(name)) names
from (
select name,concat(contellation,",",blood_type) base
from person_info
) t
group by t.base;
±--------±-------±-+
| t.base | names |
±--------±-------±-+
| 射⼿座,A | 张飞|⼩乔 |rank函数的用法
| ⽩⽺座,A | 悟空|⼋戒 |
| ⽩⽺座,B | 刘备 |
±--------±-------±-+
这⾥使⽤group by 将base分组，使⽤collect_set将所有name去重后存放到⼀个数组中，在使⽤concat_ws
将数组中的元素连接起来。
列转⾏：EXPLODE，LATERAL VIEW：
什么是列转⾏呢？
把类似于
A ,
B , C
转化为
A
B
C
列转⾏主要有两个函数：
select explode(array(‘liubei’,‘zhangfei’,‘guanyu’));
±----------±-+
| col |
±----------±-+
| liubei |
| zhangfei |
| guanyu |
±----------±-+
select explode(map(‘liubei’,‘18’,‘zhangfei’,‘19’));
±----------±-------±-+
| key | value |
±----------±-------±-+
| liubei | 18 |
| zhangfei | 19 |
±----------±-------±-+
来看⼀下爆炸函数的局限性：在使⽤爆炸函数的时候，select后⾯只能跟爆炸函数，其他的不能跟
select ‘1’,explode(array(‘liubei’,‘zhangfei’,‘guanyu’));
0: jdbc:hive2://hadoop108:10000> select ‘1’,explode(array(‘liubei’,‘zhangfei’,‘guanyu’));
FAILED: SemanticException [Error 10081]: UDTF s are not supported outside the SELECT clause, nor nested in expressions
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论