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;