Hive中的行列转换很容易记反,希望通过以下案例讲解,能够让你 拨开乌云终见日!
列转行
列转行是指将原始数据表中的列数据转为行数据,效果如下:
1.准备工作:
-- 建库
create database demo1;
-- 切库
use demo1;
--建表
create table if not exists district(
id int comment '省份ID',
province string comment '省份名字',
city string comment '市名字'
)comment '地区表'
row format delimited fields terminated by',';
/*
数据:
1,河南省,郑州市、漯河市、许昌市
2,河北省,保定市、邯郸市、邢台市
3,浙江省,杭州市、宁波市、温州市
4,山东省,青岛市、济南市、烟台市
5,山西省,太原市、长治市、晋城市
*/
2.思路:
知识铺垫:列转行需要用到 explode()函数,爆炸函数,它属于UDTF(表生成函数),即:一进多出,只能处理 数组或者字典
2.1 我们使用爆炸函数将city列炸开
select
explode(city)as city
from district;
报错解释:explode()函数 只能处理数组或字典
2.2 我们先试用split()函数对city进行切割 在使用爆炸函数
select
id,
province,
explode(split(city,'、'))as city
from district;
报错解释:炸裂后的数据, 无法直接和原表查询, 因为对应的关联信息不精准(炸裂后的每一行, 具体对应的是炸裂前的哪一行数据, 对不上)
2.3 我们通过侧视图(lateral view)实现,它是hive提供的一种专门用来记录 炸裂前后,数据对应关系的函数
侧视图格式:lateral view 炸裂函数 侧视图的名字 as 炸裂后数据的列名
select
id,
province,
d2.city
from district
lateral view explode(split(city,'、')) d2 as city
行转列
是指将原始数据表中的行数据转为列数据,效果如下:
1.准备工作:
-- 建表
create table if not exists district2(
id int comment '省份ID',
province string comment '省份名字',
city string comment '市名字'
)comment '地区表'
row format delimited fields terminated by',';
-- 数据
/*
1,河南省,郑州市
1,河南省,漯河市
1,河南省,许昌市
2,河北省,保定市
2,河北省,邯郸市
2,河北省,邢台市
3,浙江省,杭州市
3,浙江省,宁波市
3,浙江省,温州市
4,山东省,青岛市
4,山东省,济南市
4,山东省,烟台市
5,山西省,太原市
5,山西省,长治市
5,山西省,晋城市
*/
2. 思路:
知识铺垫:我们使用采集函数将多行数据采集到一块,然后用拼接函数将其拼接到一起
采集函数:
collect_list() 采集,不去重,有序
collect_set() 采集,去重,无序
2.1 我们先使用采集函数将多列采集到一块
select
collect_list(city)as city
from district2;
2.2我们发现所有数据都到一行中去了,不是我们想要的效果,我们需要进行分组
select
id,
province,
collect_list(city)as city
from district2
group by id,province;
2.3上面的city列变成了数组,也不是我们想要的效果,我们使用concat_ws()函数,按照 、字符进行拼接
select
id,
province,
concat_ws('、',collect_list(city))as city
from district2
group by id,province;
希望以上分享的内容能够帮助到各位小伙伴,解决各位小伙伴疑惑,我们下期再见!