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

这篇具有很好参考价值的文章主要介绍了【大数据Hive】hive 行列转换使用详解。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

一、前言

二、使用场景介绍

2.1 使用场景1

2.2 使用场景2

三、多行转多列

3.1 case when 函数

语法一

语法二

操作演示

3.2 多行转多列操作演示

四、多行转单列

4.1 concat函数

语法

4.2 concat_ws函数

语法

4.3 collect_list函数

语法

4.4 collect_set函数

语法

4.5 多行转多列操作演示

五、多列转多行

5.1 union

语法

5.2 union all

语法

5.3 多列转多行操作演示

六、单列转多行

6.1 explode函数

语法

6.2 单列转多行操作演示

七、写在文末


一、前言

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

二、使用场景介绍

2.1 使用场景1

如下图所示

 小tips:

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

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

2.2 使用场景2

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

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

三、多行转多列

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

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

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

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

操作演示

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

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

case when 语法1

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

执行结果

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

case when 语法2

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

执行结果

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

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;

执行过程

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

查询数据是否加载成功

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

最终的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】hive 行列转换使用详解,大数据,hive行转列,hive行列转换,hive行转列使用

四、多行转单列

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

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

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

4.1 concat函数

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

语法

concat(element1,element2,element3……)

操作演示

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

4.2 concat_ws函数

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

语法

concat_ws(SplitChar,element1,element2……)

操作演示

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

4.3 collect_list函数

用于将一列中的多行合并为一行,不进行去重(可以保留重复的数据)

语法

collect_list(colName)

操作演示

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

4.4 collect_set函数

用于将一列中的多行合并为一行,并进行去重(注意:这个与collect_list形成了对比)

语法

collect_set(colName)

操作演示

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

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;

执行完成后查询数据

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

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;

执行结果如下

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

五、多列转多行

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

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

多列转多行的实现需要 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';

执行结果

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

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';

 执行结果

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

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;

查询数据是否加载成功

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

最终实现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;

执行结果如下

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

六、单列转多行

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

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

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

6.1 explode函数

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

语法

explode( Map | Array)

操作演示

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

执行结果

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

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;

执行过程

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

查询数据是否加载成功

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

使用explode函数查询一下col3

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

执行结果

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

最终的sql实现

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

执行结果

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

七、写在文末

本文通过操作案例详细介绍了hive中行列转换的使用,在实际工作中,行列转换可以说使用的场景很多,有必要理解和掌握,本篇到此结束,感谢观看。文章来源地址https://www.toymoban.com/news/detail-811033.html

到了这里,关于【大数据Hive】hive 行列转换使用详解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Hive行转列[一行拆分成多行/一列拆分成多列]

    hive有张表armmttxn_tmp,其中有一个字段lot_number,该字段以逗号分隔开多个值,每个值又以冒号来分割料号和数量,如:A3220089:-40,A3220090:-40,A3220091:-40,A3220083:-40,A3220087:-40,A3220086:-40,A3220088:-40,A3220084:-40,A3220081:-40,A3220082:-40,A3220092:-40,A3220093:-40,A3220085:-40,A3220094:-40。 把该字段拆分开来

    2024年02月03日
    浏览(40)
  • Hive和Impala的行列转换

    explode+lateral group by+collect_list 一、列转行 (对某列拆分,形成新列) 使用函数:lateral view explode(split(column, ‘,’)) num eg: 如表:t_row_to_column_tmp 数据如下,对tag列进行拆分 二、行转列 (根据主键,对某列进行合并) 使用函数:concat_ws(‘,’,collect_set(column)) 说明:collect_list 不去重,

    2024年02月12日
    浏览(35)
  • Hive的窗口函数与行列转换函数及JSON解析函数

    查看系统内置函数 :show functions ; 显示内置函数的用法 : desc function lag; – lag为函数名 显示详细的内置函数用法 : desc function extended lag; 1.1 行转列 行转列是指多行数据转换为一个列的字段。 Hive行转列用到的函数 concat(str1,str2,...) 字段或字符串拼接 concat_ws(\\\'分割符\\\',str1,str2,

    2024年02月12日
    浏览(38)
  • MySQL中的面试题——行转列(列转行)手把手详解思想

    目录 1、什么是行转列,列转行 ? 1.1、概念: 1.2、行转列和列转行的思想 1.2.1、行转列的思想? 1.2.2、列转行的思想? 3、实操讲解 3.1、实现行转列  3.2、总结:行转列实现的两种方法    3.3、实现列转行  3.4、总结 :列转行 4、动手练习         在MySQL中,行转列(

    2024年02月03日
    浏览(58)
  • 【大数据Hive】hive 表数据优化使用详解

    目录 一、前言 二、hive 常用数据存储格式 2.1 文件格式-TextFile 2.1.1 操作演示

    2024年02月08日
    浏览(41)
  • 【大数据Hive】hive 加载数据常用方案使用详解

    目录 一、前言 二、load 命令使用 2.1 load 概述 2.1.1 load 语法规则

    2024年02月10日
    浏览(39)
  • 【大数据Hive】hive 事务表使用详解

    目录 一、前言 二、Hive事务背景知识 hive事务实现原理 hive事务原理之 —— delta文

    2024年02月12日
    浏览(32)
  • 【大数据Hive】Hive 窗口函数使用详解

    目录 一、前言 二、hive 窗口函数概述 2.1 聚合函数与窗口函数差别 2.1.1 创建一张表

    2024年02月11日
    浏览(38)
  • 【大数据Hive】hive 运算符使用详解

    目录 一、前言 二、hive 运算符分类 三、hive 运算符操作演示 3.1  数据准备

    2024年02月03日
    浏览(36)
  • 【大数据Hive】hive select 语法使用详解

    目录 一、前言 二、Hive select 完整语法树 三、Hive select 操作演示 3.1 数据准备

    2024年02月08日
    浏览(42)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包