Hive的常用函数(nvl,case when,行转列,列转行,爆炸函数)

15 篇文章 1 订阅
13 篇文章 0 订阅

目录

综述:

 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 emp_sex.txt 
悟空		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/emp_sex.txt' 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]$ cat contellation.txt 
悟空		白羊座	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/datas/contellation.txt' into table person_info;

0: jdbc:hive2://hadoop108:10000> select * from person_info;
OK
+-------------------+---------------------------+-------------------------+--+
| person_info.name  | person_info.contellation  | 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   | 张飞|小乔  |
| 白羊座,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


lateral view:斜写视图,为了解决爆炸函数的局限性,结合UDTF函数(如,explode())一起使用。
将电影分类中的数组数据展开
期望:
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难


[isea@hadoop108 datas]$ cat move_info.txt 
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

创建hive表并导入数据:
create table movie_info(name string,category array<string>)
row format delimited
fields terminated by '\t'
collection items terminated by ',';

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

下面使用测写视图的方式,在求出爆炸函数结果的同时,求出除了爆炸函数字段之外的字段。需要添加爆炸之
后的表名,和爆炸之后的字段名,这个字段名可以放置于select之后,查询爆炸之后的字段值。

select name,category_info
from movie_info
lateral view explode(category) tmp_tbl as category_info;

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


这里需要指出的是:在键表的时候一定不能对于集合数据类型,如果没有添加
collection items terminated by ','表中的数据是这样的:
+------------------+----------------------+--+
| movie_info.name  | movie_info.category  |
+------------------+----------------------+--+
| 《疑犯追踪》           | ["悬疑,动作,科幻,剧情"]      |
| 《Lie to me》      | ["悬疑,警匪,动作,心理,剧情"]   |
| 《战狼2》            | ["战争,动作,灾难"]         |
+------------------+----------------------+--+
category是一个完整的字符串,也即array中只有一个元素
添加了分隔符号之后是这样的:
+------------------+-----------------------------+--+
| movie_info.name  |     movie_info.category     |
+------------------+-----------------------------+--+
| 《疑犯追踪》           | ["悬疑","动作","科幻","剧情"]       |
| 《Lie to me》      | ["悬疑","警匪","动作","心理","剧情"]  |
| 《战狼2》            | ["战争","动作","灾难"]            |
+------------------+-----------------------------+--+

更多精彩内容,欢迎关注公众号:stackoverflow

  • 7
    点赞
  • 74
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
### 回答1: 可以使用Hive中的TRANSPOSE函数对字段进行行转列操作。该函数将行数据转换为列数据,可以方便地进行数据透视和分析。使用方法如下: SELECT TRANSPOSE(col_name) FROM table_name; 其中,col_name为需要进行行转列的字段名,table_name为数据表名。 ### 回答2: Hive是一个基于Hadoop的数据仓库工具,它提供了一种将结构化的数据映射到Hadoop分布式文件系统(HDFS)中的方法。Hive支持使用HiveQL查询语言进行数据分析和数据查询。 在Hive中,要对字段进行行转列函数操作,可以使用HiveQL中的"pivot"操作。Pivot操作是将行的数据转换为列的过程,可以通过对数据进行透视操作来实现。 首先,需要使用HiveQL的"SELECT"语句选择要进行行转列操作的数据。然后,使用HiveQL的"PIVOT"关键字指定要转换为列的字段名称。通过指定"PIVOT"关键字后的字段名,Hive会将这些字段的值作为新的列进行展示。最后,可以使用"GROUP BY"语句对转换后的数据进行聚合操作。 例如,假设我们有一个包含日期、地点和销售额的数据表。我们想要将地点作为列,日期作为行,并以销售额填充单元格。以下是一个示例HiveQL查询语句: ``` SELECT * FROM (SELECT date, location, sales FROM sales_table) src PIVOT (SUM(sales) FOR location IN ('location1', 'location2', 'location3')) as result ``` 上述查询语句中,首先选择了date、location和sales字段。然后,通过"PIVOT"关键字指定了要转换为列的字段"location",并使用"SUM"函数对sales字段进行聚合操作。最后,通过"GROUP BY"语句对转换后的数据进行分组。 通过以上的HiveQL查询,我们可以将原始的行数据转换为列数据,并按照指定的格式显示。这样,我们就可以更方便地进行数据分析、数据查询和报表生成等操作。 ### 回答3: Hive是一种基于Hadoop的数据仓库工具,它提供了一种SQL接口,用于查询和分析大规模的数据。在Hive中,可以使用TRANSPOSE函数对字段进行行转列操作。 Hive中的TRANSPOSE函数用于将一列数据转换成多行数据,适用于将宽表转换为长表的场景。TRANSPOSE函数需要指定需要转置的列以及转置后生成的新列的名称。 假设我们有一个包含用户ID和用户偏好的表,其中用户ID为主键,用户偏好分为三个字段:音乐偏好、电影偏好和运动偏好。现在我们想将这三个字段转换成一列,其中列名为偏好类型,值为用户偏好的具体内容。可以使用TRANSPOSE函数实现这个转换过程。 具体的操作如下: 1. 创建一个新表,包含用户ID和偏好类型两个字段。 2. 使用INSERT INTO SELECT语句将原表中的用户ID和三个偏好字段的值插入到新表中。 3. 使用TRANSPOSE函数对偏好字段进行行转列操作,将其转换成多行数据,其中每一行包含用户ID和一个偏好类型的值。 4. 最后,将转置后的数据插入到新表的偏好类型字段中。 通过以上操作,我们就可以将原表中的偏好字段行转列成一列,方便后续的查询和分析操作。 总而言之,Hive提供了TRANSPOSE函数来对字段进行行转列操作,可以方便地将宽表转换为长表,便于数据的处理和分析。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值