赞
踩
主要介绍explode和posexplode函数的使用。类似于flatmap把一个数组打散
lateral view 侧视图。原表的每一行和explode_lateral_view进行笛卡尔积关联,也可以多重使用
和transArray的作用一致
- 命令格式:
- trans_array (num_keys, separator, key1,key2,…,col1, col2,col3) as (key1,key2,…,col1, col2)
- 用途:
- 用于将一行数据转为多行的UDTF,将列中存储的以固定分隔符格式分隔的数组转为多行。
- 参数说明:
- ● num_keys: bigint类型常量,必须>=0。在转为多行时作为转置key的列的个数。
- Key是指在将一行转为多行时,在多行中重复的列。
- ● separator:string类型常量,用于将字符串拆分成多个元素的分隔符。为空时报异常。
- ● keys:转置时作为key的列, 个数由num_keys指定。如果num_keys指定所有的列都作为key(即num_keys等于所有列的个数),则只返回一行。
- ● cols: 要转为行的数组,keys之后的所有列视为要转置的数组,必须为string类型,存储的内容是字符串格式的数组,如“Hangzhou;Beijing;shanghai”,是以”;”分隔的数组。
- 返回:
- 转置后的行,新的列名由as指定。作为key的列类型保持不变,其余所有的列是string类型。拆分成的行数以个数多的数组为准,不足的补NULL。
- 注:
- UDTF使用上有一些限制
- ● 所有作为key的列必须处在前面,而要转置的列必须放在后面。
- ● 在一个select中只能有一个udtf,不可以再出现其它的列
- ● 不可以与group by/cluster by/distribute by/sort by一起使用。
- 例,表中的数据如
- Login_id LOGIN_IP LOGIN_TIME
- wangwangA 192.168.0.1,192.168.0.2 20120101010000,20120102010000
-
- 则trans_array(1, “,”, login_id, login_ip, login_time) as (login_id,login_ip,login_time)
- 产生的数据是
- Login_id Login_ip Login_time
- wangwangA 192.168.0.1 20120101010000
- wangwangA 192.168.0.2 20120102010000
- 则对数组中不足的数据补NULL
- Login_id Login_ip Login_time
- wangwangA 192.168.0.1 20120101010000
- wangwangA 192.168.0.2 NULL
先看下我们的数据,主要包括三列,分别是班级、姓名以及成绩,数据表名是default.classinfo。
首先来看下最基本的,我们如何把student这一列中的数据由一行变成多行。这里需要使用split和explode,并结合lateral view实现。代码如下:
- select
- class,student_name
- from
- default.classinfo
- lateral view explode(split(student,',')) t as student_name
接下来,我们想要给每个同学来一个编号,假设编号就按姓名的顺序,此时我们要用到另一个hive函数,叫做posexplode,代码如下:
- select
- class,student_index + 1 as student_index,student_name
- from
- default.classinfo
- lateral view posexplode(split(student,',')) t as student_index,student_name
这里select时对编号+1主要是因为编号是从0开始的,结果如下:
好了,我们继续前进。这次我们想基于两列explode,同时能够使学生和其成绩能够匹配,即期望的效果如下:
显然我们要对两列进行explode,先试试行不行:
- select
- class,student_name,student_score
- from
- default.classinfo
- lateral view explode(split(student,',')) sn as student_name
- lateral view explode(split(score,',')) sc as student_score
好像是不太行,如果我们分别对两列进行explode的话,假设每列都有三个值,那么最终会变成3 * 3 = 9行。但我们想要的结果只有三行。此时我们可以进行两次posexplode,姓名和成绩都保留对应的序号,即使变成了9行,我们通过where条件只保留序号相同的行即可。代码如下:
- select
- class,student_name,student_score
- from
- default.classinfo
- lateral view posexplode(split(student,',')) sn as student_index_sn,student_name
- lateral view posexplode(split(score,',')) sc as student_index_sc,student_score
- where
- student_index_sn = student_index_sc
此时结果就对了:
好了,到这里本应该就结束了,假设我们又想对同学的成绩进行一下排名该怎么做呢?当然是借助rank函数啦(row_number函数对于相同的成绩也会赋予不同的排名,所以我们选择rank函数
- select
- class,
- student_name,
- student_score,
- rank() over(partition by class order by student_score desc) as student_rank
- from
- default.classinfo
- lateral view posexplode(split(student,',')) sn as student_index_sn,student_name
- lateral view posexplode(split(score,',')) sc as student_index_sc,student_score
- where
- student_index_sn = student_index_sc
结果符合我们预期:
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。