Hive 其他常用查询函数

空字段赋值

函数说明

为了给字段中为 null 的值赋予一个新值。

nal(字段1, 字段2 | 所赋值) 有两种用法

用法一：只传入一个字段

# 原数据
hive> select * from studentinof;
OK
1    bidataboy    1000
2    Boy            NULL
3    Aa            NULL
4    Boy            200
4    Tab            1000
Time taken: 1.13 seconds, Fetched: 5 row(s)

把为 null 的赋值成0

hive> select name,nvl(money,0) from studentinof;
OK
bidataboy    1000
Boy            0
Aa          0
Boy         200
Tab            1000
Time taken: 0.383 seconds, Fetched: 5 row(s)

用法二：传入两个字段

作用：当字段1 为null时，字段2补充，当字段2的值为null，赋予值补充。

# 原数据
hive> select * from studentinof;
OK
1    bidataboy    1000
2    Boy    NULL
3    Aa    NULL
4    Boy    200
5    Tab    1000
6    NULL    2000
Time taken: 0.333 seconds, Fetched: 6 row(s)

# 传入两个字段：当 money 为 null 时，返回 name 的值。
hive> select name ,nvl(money, name) from studentinof;
OK
bidataboy    1000
Boy            Boy
Aa            Aa
Boy         200
Tab            1000
NULL        2000
Time taken: 1.75 seconds, Fetched: 6 row(s)

case 匹配

这个case 匹配跟MySQL 里的是差不多的。

case 变量或者字段
when 待匹配值 then  值或表达式
when 待匹配值 then  值或表达式
else 未匹配的处理
end

使用

统计不同部门的男女数

部门 | 男 | 女

数据

hive> select * from emp;
OK
小黄    A    女
小蓝    B    女
小粉    A    女
小红    B    男
小黑    A    男
小紫    B    男
Time taken: 1.111 seconds, Fetched: 6 row(s)

HQL 查询语句

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
group by dept_id;

结果

Total MapReduce CPU Time Spent: 11 seconds 90 msec
OK
A    1    2
B    2    1
Time taken: 343.221 seconds, Fetched: 2 row(s)

行转列

相关函数

• concat(col ,col ,...)：字符串的拼接，跟 MySQL 里面的一样。
• concat_ws(separator, str1, str2,...)：这个是特殊的拼接，第一个参数是分隔符，也可以传入数组，返回一个能指定分隔符的字符串。
• COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

使用

原数据

hive> select * from emp;
OK
小黄    A    女
小蓝    B    女
小粉    A    女
小红    B    男
小黑    A    男
小紫    B    男
Time taken: 1.048 seconds, Fetched: 6 row(s)

HQL 语句

select t.ds,concat_ws("|",collect_set(t.n))
from  (select concat_ws("|" ,dept_id ,sex) ds, name n from emp) t
group by t.ds;

结果

Total MapReduce CPU Time Spent: 8 seconds 40 msec
OK
A|女    小黄|小粉
A|男    小黑
B|女    小蓝
B|男    小红|小紫
Time taken: 54.446 seconds, Fetched: 4 row(s)

# 可以把最外层的 select 里的concat_ws() 函数去掉，结果变成如下。
Total MapReduce CPU Time Spent: 7 seconds 500 msec
OK
A|女    ["小黄","小粉"]
A|男    ["小黑"]
B|女    ["小蓝"]
B|男    ["小红","小紫"]
Time taken: 71.575 seconds, Fetched: 4 row(s)

列转行

效果实例

# 转换之前
movie       |    category           |
------------------------------------
《疑犯追踪》 |    悬疑,动作,科幻,剧情  |

# 转换之后
movie       |    category           |
------------------------------------
《疑犯追踪》 |    悬疑  |
《疑犯追踪》 |    动作  |
《疑犯追踪》 |    科幻  |
《疑犯追踪》 |    剧情  |

函数说明：

• explode(col)：将hive一列中复杂的array或者map结构拆分成多行。
• lateral view：
  • 用法：lateral view udtf(expression) tablealias as columnalias
  • 解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

使用

原数据

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

创表

create table movie_info(
    movie string, 
    category array<string>) 
row format delimited 
fields terminated by "\t"
collection items terminated by ",";

查询语句

select movie, category_name
from movie_info 
lateral view explode(category) table_tmp as category_name;

结果

不跑 MR

OK
《Lie to me》    悬疑
《Lie to me》    警匪
《Lie to me》    动作
《Lie to me》    心理
《Lie to me》    剧情
《战狼2》    战争
《战狼2》    动作
《战狼2》    灾难
Time taken: 0.438 seconds, Fetched: 8 row(s)