hive行列转换

行列转换

行转列

概念

​ 把数据表中具有相同key值的多行value数据(左侧)转换为使用一个key值的多列数据(右侧);使每一行数据中,一个key对应多个value。

函数

case···when···then···else···end语句

用法:
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

condition1, condition2, ... 是需要进行判断的条件。
result1, result2, ... 是与相应条件匹配时返回的结果。
default_result 是当所有条件都不匹配时返回的默认结果。

collect_set()、collect_list()

函数作用
collect_set(字段)求出该字段的所有值(不重复,去重)
collect_list(字段)求出该字段的所有值(存在重复)

它们都是将分组中的某列转为一个数组返回,不同的是collect_list不去重而collect_set去重。

用法:
SELECT id, collect_set(value) AS values_set
FROM my_table
GROUP BY id;

案列

  • 需求:

    ​ 有id为1,2,3的学生选修了课程a,b,c,d,e,f中其中几门。

    编写Hive的HQL语句来实现以下结果:表中的Yes表示选修,表中的No表示未选修

  • 元数据. (id course )

    1,a
    1,b
    1,c
    1,e
    2,a
    2,c
    2,d
    2,f
    3,a
    3,b
    3,c
    3,e
    
  • 建表、导入数据

    -- 创建表并指定字段分隔符为逗号(,)
    create table if not exists id_course(id int, course string) row format delimited fields terminated by ",";
    
    -- 准备数据,放置在服务器文件系统或HDFS。此处放在服务器文件系统上(/root/yber/data/id_course_data.txt)
    
    -- 加载数据到表
    load data local inpath "/usr/local/soft/hive-1.2.1/data/id_course_data.txt" into table id_course;
    

结果:

id	a	b	c	d	e	f
1	Yes	Yes	Yes	Yes	No	No
2	Yes	No	Yes	Yes	NO	Yes
3	Yes	Yes	Yes	NO	YES	No
select id
,max(if(course='a','Yes','No')) as a
,max(if(course='b','Yes','No')) as b
,max(if(course='c','Yes','No')) as c
,max(if(course='d','Yes','No')) as d
,max(if(course='e','Yes','No')) as e
,max(if(course='f','Yes','No')) as f
from id_course
group by id;

列转行

概念

​ 把表中同一个key值对应的多个value列,转换为多行数据,使每一行数据中,保证一个key只对应一个value。(将一行某列的数据转到多行上)

字符串函数-split

函数用法含义
split()split(favors,‘-’)将favors按照-分割成多部分。

炸裂函数-explode

函数用法含义读音
explode()explode(array参数)将array的值转到多行上explode /ɪkˈsploʊd/ 爆炸
explode(split(炸裂字段,分隔符))和split组合使用,将String类型转为array,然后转到多行上
用法:
SELECT id, exploded_value
FROM my_table
LATERAL VIEW EXPLODE(values) exploded_table AS exploded_value;

案例

数据

(city,infos)

北京	朝阳区,海淀区,其他
上海	黄浦区,徐汇区,其他
查询语句
-- 创建表并指定字段分隔符为逗号(\t)
create table city_infos(city string,infos string) row format delimited fields terminated by "\t";

-- 准备数据,放置在服务器文件系统或HDFS。此处放在服务器文件系统上(/root/yber/data/lines_data.txt)

-- 加载数据到表
load data local inpath "/usr/local/soft/hive-1.2.1/data/lines_data.txt" into table city_infos;

查询结果
city	t2.info
北京	朝阳区
北京	海淀区
北京	其他
上海	黄浦区
上海	徐汇区
上海	其他
select city
,view.info_explode 
from city_infos lateral view explode(split(infos,',')) view as info_explode;

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值