Hive中的行转列与列转行

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;

希望以上分享的内容能够帮助到各位小伙伴，解决各位小伙伴疑惑，我们下期再见！