【大数据Hive】hive 行列转换使用详解

一、前言

在某些场景下，对于mysql表来说，要想完整的呈现出一个主体字段的所有属性，可能需要查询多条数据行，显然从msyql扫描数据行数来说，需要扫描多行才能加载出来，有没有一种办法，可以在一行记录中就呈现出所有的属性值呢？这就是所谓的行转列了。

二、使用场景介绍

2.1 使用场景1

如下图所示

小tips：

SqlServer和Orcle中可以使用pivot行转列函数快速实现，而MySQL中没有，还记得mysql中要实现行列转换是怎么实现的吗？

2.2 使用场景2

比如说，需要统计得到网站的每个小时的UV、PV、IP的个数，需要通过左边的原始数据得到右边的表结构，才能通过图形渲染出UV,PV在不同的时间段内的变化趋势图，同时来说，得到右边结构的数据之后，从查询来看，为了得到某个指标，只需要查询一行数据即可；

三、多行转多列

需求：如下，左边为原始的表数据，为了得到右边的数据，显然可以认为是将现有的多行多列数据转为一个新的多行多列数据，根据col1字段做了一个分组；

实现多行转多列的一个重要解决办法就是使用case when 函数(mysql的实现思路也是如此)；

3.1 case when 函数

功能 ：用于实现对数据的判断，根据条件，不同的情况返回不同的结果，类似于Java中的switch case

语法一

CASE

WHEN 条件1 THEN VALUE1

……

WHEN 条件N THEN VALUEN

ELSE 默认值 END

语法二

CASE 列

WHEN V1 THEN VALUE1

……

WHEN VN THEN VALUEN

ELSE 默认值 END

操作演示

使用之前的一张表做测试，表中数据如下

case when 语法1

select
    id,
    case
        when id < 2 then 'a'
        when id = 2 then 'b'
        else 'c'
        end as caseName
from tb_url;

执行结果

case when 语法2

select
    id,
    case id
        when 1 then 'a'
        when 2 then 'b'
        else 'c'
        end as caseName
from tb_url;

执行结果

3.2 多行转多列操作演示

建表并加载数据

--建表
create table row2col1(
                         col1 string,
                         col2 string,
                         col3 int
) row format delimited fields terminated by '\t';
--加载数据到表中
load data local inpath '/usr/local/soft/selectdata/r2c1.txt' into table row2col1;

执行过程

查询数据是否加载成功

最终的sql实现

select
    col1 as col1,
    max(case col2 when 'c' then col3 else 0 end) as c,
    max(case col2 when 'd' then col3 else 0 end) as d,
    max(case col2 when 'e' then col3 else 0 end) as e
from
    row2col1
group by
    col1;

执行上面的sql观察结果，这样就得到了预期的数据展示；

四、多行转单列

原始需求，如下图，左边为原始表数据，分析来看，该表的数据第一列+第二列进行分组的话，正好可以分在一组中，第三列单独可以列出，这样的话，为了减少查询时数据扫描的行数，就可以转成右图中的数据表；

为了实现这个需求，需要再次了解下面几个hive中关于数据拼接的函数

4.1 concat函数

用于实现字符串拼接，不可指定分隔符，如果其中任意一个元素为null，结果就为null

语法

concat(element1,element2,element3……)

操作演示

4.2 concat_ws函数

用于实现字符串拼接，可以指定分隔符，如果其中任意一个元素不为null，结果就不为null

语法

concat_ws(SplitChar，element1，element2……)

操作演示

4.3 collect_list函数

用于将一列中的多行合并为一行，不进行去重（可以保留重复的数据）

语法

collect_list（colName）

操作演示

4.4 collect_set函数

用于将一列中的多行合并为一行，并进行去重（注意：这个与collect_list形成了对比）

语法

collect_set（colName）

操作演示

4.5 多行转多列操作演示

建表并加载数据

--建表
create table row2col2(
                         col1 string,
                         col2 string,
                         col3 int
)row format delimited fields terminated by '\t';

--加载数据到表中
load data local inpath '/usr/local/soft/selectdata/r2c2.txt' into table row2col2;

执行完成后查询数据

sql实现思路

1、根据col1和col2进行分组；

2、使用collect_list将col3的数据组合在一起；

3、最后使用concat_ws将数据进行拼接；

完整的实现sql如下

select
    col1,
    col2,
    concat_ws(',', collect_list(cast(col3 as string))) as col3
from
    row2col2
group by
    col1, col2;

执行结果如下

五、多列转多行

需求场景，如下图所示，左表为原始数据，a,b有3个属性字段，限现在需要将其转为多行进行展示，即每个属性值单列为一行记录，即上述多行转多列的颠倒；

多列转多行的实现需要 union 关键字进行转换，关于union 下面做简单的说明

5.1 union

将多个select语句结果合并为一个，且结果去重且排序

语法

select_statement
UNION [DISTINCT]
select_statement
UNION [DISTINCT]
select_statement ...

操作演示

select 'b','a','c'
union
select 'a','b','c'
union
select 'a','b','c';

执行结果

5.2 union all

将多个select语句结果合并为一个，且结果不去重不排序

语法

select_statement UNION ALL select_statement UNION ALL select_statement ...

操作演示

select 'b','a','c' 
union all 
select 'a','b','c' 
union all  
select 'a','b','c';

执行结果

5.3 多列转多行操作演示

创建表并加载数据

create table col2row1
(
    col1 string,
    col2 int,
    col3 int,
    col4 int
) row format delimited fields terminated by '\t';

--加载数据
load data local inpath '/usr/local/soft/selectdata/c2r1.txt'  into table col2row1;

查询数据是否加载成功

最终实现sql

select col1, 'c' as col2, col2 as col3 from col2row1
UNION ALL
select col1, 'd' as col2, col3 as col3 from col2row1
UNION ALL
select col1, 'e' as col2, col4 as col3 from col2row1;

执行结果如下

六、单列转多行

需求场景，将左表的数据，根据col3进行拆分为多行，展示成右边的数据表，这个正好和上面的多行转单列反过来；

多列转多行的实现需要用到explode函数，该函数在之前的文章中也谈到过，这里再做一下补充说明；

6.1 explode函数

用于将一个集合或者数组中的每个元素展开，将每个元素变成一行

语法

explode( Map | Array)

操作演示

select explode(split("a,b,c,d",","));

执行结果

6.2 单列转多行操作演示

建表并加载数据

--创建表
create table col2row2(
                         col1 string,
                         col2 string,
                         col3 string
)row format delimited fields terminated by '\t';

--加载数据
load data local inpath '/root/hivedata/c2r2.txt' into table col2row2;

执行过程

查询数据是否加载成功

使用explode函数查询一下col3

select explode(split(col3,',')) from col2row2;

执行结果

最终的sql实现

select
    col1,
    col2,
    lv.col3 as col3
from
    col2row2
        lateral view
            explode(split(col3, ',')) lv as col3;

执行结果

七、写在文末

本文通过操作案例详细介绍了hive中行列转换的使用，在实际工作中，行列转换可以说使用的场景很多，有必要理解和掌握，本篇到此结束，感谢观看。