行列转换
行转列
概念
把数据表中具有相同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;