HIVE-函数的使用(nvl/case when then else end/concat/concat_ws/explode)

系统内置函数

1)查看系统自带的函数

desc functions;

2)显示自带的函数的用法:显示的是nvl函数的意思

desc function nvl;

3)详细显示自带的函数的用法
显示的是nvl函数的意思,同时举出例子

desc function extended nvl;

常用内置函数

空字段赋值

1)函数说明
NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数 都为 NULL ，则返回 NULL。
2）数据准备：采用员工表
3）查询：如果员工的 comm 为 NULL，则用-1 代替

4)数据准备:
员工表:

7369	SMITH	CLERK	7902	1980-12-17 800.00	20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00 30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00 30
7566	JONES	MANAGER 7839	1981-4-2	2975.00	20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00 30
7698	BLAKE	MANAGER 7839	1981-5-1	2850.00	30
7782	CLARK	MANAGER 7839	1981-6-9	2450.00	10
7788	SCOTT	ANALYST 7566	1987-4-19	3000.00	20
7839	KING	PRESIDENT	1981-11-17 5000.00	10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00	20
7900	JAMES	CLERK	7698	1981-12-3	950.00	30
7902	FORD	ANALYST 7566	1981-12-3	3000.00	20
7934	MILLER	CLERK	7782	1982-1-23	1300.00	10

创建emp表:

create table if not exists emp( empno int,
ename string, job string, mgr int,
hiredate string, sal double,
comm double, deptno int)
row format delimited fields terminated by '\t';

查询结果:

hive (default)> select comm,nvl(comm,-1) from emp;
OK
comm	_c1
NULL	-1.0
300.0	300.0
500.0	500.0
20.0	20.0
1400.0	1400.0
30.0	30.0
NULL	-1.0
NULL	-1.0
NULL	-1.0
0.0		0.0
20.0	20.0
30.0	30.0
NULL	-1.0
10.0	10.0

如果comm为null,则用-1代替。

CASE WHEN THEN ELSE END

(1)数据准备

悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

(2)需求
求出不同部门男女各多少人。结果如下：

dept_Id	        男	    女
A	            2	    1  
B	            1	    2

统计A部门和B部门中的男和女的个数,这个时候用的是case when then else end

create table emp_sex(
    name string,
    dept_id string,
    sex string
)
row format delimited fields terminated by '\t';

Hive SQL语句如下:
case 后面接的是字段名称, when 接值,如果是该值,就取then 后面的值1,如果不是就取0.最后将1都累加起来为sum

select 
    dept_id,
    sum(case sex when '男' then 1 else 0 end) sum_male,
    sum(case sex when '女' then 1 else 0 end) sum_female
from
    emp_sex
group by  dept_id;

得出来的结果是:

OK
dept_id	sum_male	sum_female
A	2	1
B	1	2
Time taken: 92.571 seconds, Fetched: 2 row(s)

这里面除了用上面的函数来计算外,还可以用if函数来计算。
首先,if(exp1,exp2,exp3),如果exp1是true,则返回exp2,否则返回exp3

select 
    dept_id,
    sum(if(sex='男',1,0)) maleCount,
    sum(if(sex='女',1,0)) femaleCount
from
    emp_sex
group by  dept_id;

也能够得出相同的结果出来。

行转列

相关函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将 为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接 的字符串之间;
注意: CONCAT_WS must be "string or array
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段。

CONCAT

hive (default)> select concat('a','-','b','-','c');
OK
_c0
a-b-c

当我们中间都是用-拼接的时候,为了省略书写,可以才采用CONCAT_WS:

hive (default)> select concat_ws('-','a','b','c');
OK
_c0
a-b-c

CONCAT_WS

省略了多次编写字符的拼接符:
还可以进行拼接数组:

hive (default)> select concat_ws('.','www',array('baidu','com'));
OK
_c0
www.baidu.com

接下里做一个需求加深知识的运用,
编辑数据:vim person_info.txt:

孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A
苍老师	白羊座	B

实现如下格式的数据:

射手座,A   	大海|凤姐
白羊座,A	    孙悟空|猪八戒
白羊座,B  	宋宋|苍老师

建表person_info:

create table person_info(
name string,
constellation string,
blood_type string
)
row format delimited fields terminated by '\t';

导入数据:

load data local inpath '/opt/module/datas/person_info.txt'
into table default.person_info;

写sql语句的流程和思路:
1.根据目标格式需求,首先将星座和血型拼接在一起

select
    concat(constellation,',',blood_type) con_blood,
    name
from 
    person_info;t1

2.聚合相同星座血型人的姓名

select
    con_blood,
    collect_set(name) name_arr
from 
    t1
group by con_blood;t2

3.对name的数组进行处理

select
    con_blood,
    concat_ws("|",name_arr)
from 
    t2;

4.上面是写这个sql的一个流程,最后整理一下,形成如下完整的sql:

select
    con_blood,
    concat_ws("|",name_arr)
from 
(select
    con_blood,
    collect_set(name) name_arr
from
(select
    concat(constellation,',',blood_type) con_blood,
    name
from 
    person_info)t1
group by con_blood)t2;

注意:UDF:一进一出,UDTF:一进多出,UDAF:多进一出,其中collect_set,sum,count等是聚合函数,它是属于UDAF

列转行

1)函数说明:
EXPLODE(col)：将 hive一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此 基础上可以对拆分后的数据进行聚合。

数据准备,本地创建文件:test.txt:

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

根据数据的格式创建表:

create table test3(
name string,
friends array<string>,
children map<string,int>,
address struct<street:string,city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

hive (default)> select * from test3;
OK
test3.name	test3.friends	test3.children	test3.address
songsong	["bingbing","lili"]	{"xiao song":18,"xiaoxiao song":19} {"street":"hui long guan","city":"beijing"}
yangyang	["caicai","susu"]	{"xiao yang":18,"xiaoxiao yang":19} {"street":"chao yang","city":"beijing"}
Time taken: 2.381 seconds, Fetched: 2 row(s)

注意:
1.自己在写创建这个表的时候,有一些坑做下记录:写map的类型的时候,string后面接逗号,而不是冒号.
2.指定完每种格式的分隔符之后,不用逗号进行切分,直接换行就行,
3.在写行分割的时候,是lines而不是line

使用explode炸裂函数,一进多出,结果如下:

hive (default)> select explode(friends) from test3;
OK
col
bingbing
lili
caicai
susu

用另一组数据做测试:

create table movie_info(
    movie string,
    category string
)
row format delimited fields terminated by '\t';

vi movie.txt:

《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼 2》	战争,动作,灾难

导入数据:

load data local inpath '/opt/module/datas/movie.txt'
into table movie_info;

这里要将category列里面的每一个数据分隔开,如果直接使用explode函数,

select explode(category) from movie_info; 

就会报下面的错误:

UDFArgumentException explode() takes an array or a map as a parameter

explode函数的参数必须要为array或者map,我们在创建表的时候,category列指定为string,因此要先将其转化为数组,可以通过split方法来实现。

hive (default)> select split(category,',') from movie_info;
OK
_c0
["悬疑","动作","科幻","剧情"]
["悬疑","警匪","动作","心理","剧情"]
["战争","动作","灾难"]

如上图所示,通过split方法,将其转化为数组。
接着再通过explode炸裂函数,就能将每行中元素切割:

hive (default)> select explode(split(category,',')) from movie_info;
OK
col
悬疑
动作
科幻
剧情
悬疑
警匪
动作
心理
剧情
战争
动作
灾难

在上面的基础上,如果在select 后面再添加一个字段,就会出现报错,例如

select movie,explode(split(category,',')) from movie_info;

报错如下:

ILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

我们希望在上面的每个类型前面加上所对应的电影名称,也就是需要跟原表当中的字段进行关联,但是行数并不对称,因此,需要采用lateral view函数进行添加侧写表:

select
    movie,
    category_name
from movie_info
lateral view 
explode(split(category,",")) movie_info_tmp as category_name;

这样原本一行和多行无法一起显示的问题,就通过开窗函数解决了,查询结果如下:

OK
movie	category_name
《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情
《Lie to me》	悬疑
《Lie to me》	警匪
《Lie to me》	动作
《Lie to me》	心理
《Lie to me》	剧情
《战狼 2》	战争
《战狼 2》	动作
《战狼 2》	灾难

注意:这里需要理清行转列和列转行。
行转列:就是将多行的数据转化成列,想到concat函数
列转行:将一列里面的数据炸出多行,想到explode