hive的行转列和列转行

已于 2022-04-08 17:57:08 修改
阅读量3.4w 收藏 93
点赞数 25
分类专栏： hive 文章标签： hive
于 2022-04-08 17:56:48 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_55834564/article/details/124045485
版权

目录

一、行转列

1.函数说明

concat

concat_ws

collect_set

2.数据准备

3.需求

4.数据准备

创建本地constellation.txt，导入数据 

创建hive表

向表中导入数据

验证表数据

5.按需求查询数据

第一步

第二步

二.列转行

1.函数说明

split

explode

lateral view

2.数据准备

 3.需求

 4.数据准备

创建本地movie.txt，导入数据

创建hive表

向表中导入数据

验证表数据

5.按需求查询数据

第一步

第二步

6.情况简单下，多用如下方式

一、行转列

1.函数说明

  • concat

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

  • concat_ws

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

  • collect_set

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

2.数据准备

nameconstellationblood_type
孙悟空白羊座A
大海射手座A
宋宋白羊座B
猪八戒白羊座A
凤姐射手座A

3.需求

把星座和血型一样的人归类到一起。结果如下：

 在这里插入图片描述

4.数据准备

  • 创建本地constellation.txt，导入数据 

[root@hadoop dool]# vim constellation.txt
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A

  • 创建hive表

create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";

  • 向表中导入数据

load data local inpath "/usr/word/dool/constellation.txt"
into table person_info;

  • 验证表数据

select * from person_info;

导入成功，如下：

0: jdbc:hive2://192.168.171.151:10000> select * from person_info;
+-------------------+----------------------------+-------------------------+--+
| person_info.name  | person_info.constellation  | person_info.blood_type  |
+-------------------+----------------------------+-------------------------+--+
| 孙悟空               | 白羊座                        | A                       |
| 大海                | 射手座                        | A                       |
| 宋宋                | 白羊座                        | B                       |
| 猪八戒               | 白羊座                        | A                       |
| 凤姐                | 射手座                        | A                       |
|                   | NULL                       | NULL                    |
+-------------------+----------------------------+-------------------------+--+
6 rows selected (1.343 seconds)

5.按需求查询数据

第一步

将星座（constellation）和血型（blood_type）用逗号连接

0: jdbc:hive2://192.168.171.151:10000> select
. . . . . . . . . . . . . . . . . . > name,
. . . . . . . . . . . . . . . . . . > concat(constellation,",",blood_type) base
. . . . . . . . . . . . . . . . . . > from
. . . . . . . . . . . . . . . . . . > person_info;
+-------+--------+--+
| name  |  base  |
+-------+--------+--+
| 孙悟空   | 白羊座,A  |
| 大海    | 射手座,A  |
| 宋宋    | 白羊座,B  |
| 猪八戒   | 白羊座,A  |
| 凤姐    | 射手座,A  |
|       | NULL   |
+-------+--------+--+
6 rows selected (1.072 seconds)

第二步

在第一步的基础上根据星座,血型(base)进行分组，通过collect_set将同组多行数据根据name字段聚合成一个数组，再通过concat_ws对数组进行拆分拼接

0: jdbc:hive2://192.168.171.151:10000> select t1.base,
. . . . . . . . . . . . . . . . . . > concat_ws("|",collect_set(t1.name)) name
. . . . . . . . . . . . . . . . . . > from
. . . . . . . . . . . . . . . . . . > (select
. . . . . . . . . . . . . . . . . . > name,
. . . . . . . . . . . . . . . . . . > concat(constellation,",",blood_type) base
. . . . . . . . . . . . . . . . . . > from
. . . . . . . . . . . . . . . . . . > person_info) t1
. . . . . . . . . . . . . . . . . . > group by t1.base;
+----------+----------+--+
| t1.base  |   name   |
+----------+----------+--+
| NULL     |          |
| 射手座,A    | 大海|凤姐    |
| 白羊座,A    | 孙悟空|猪八戒  |
| 白羊座,B    | 宋宋       |
+----------+----------+--+
4 rows selected (40.1 seconds)

二.列转行

1.函数说明

  • split

Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。

  • explode

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

  • lateral view

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

2.数据准备

原数据表如下图

moviecategory
《疑犯追踪》悬疑,动作,科幻,剧情
《Lie to me》悬疑,警匪,动作,心理,剧情
《战狼2》战争,动作,灾难

 3.需求

将电影分类中的数组数据展开。结果如下：

moviecategory
《疑犯追踪》悬疑
《疑犯追踪》动作
《疑犯追踪》科幻
《疑犯追踪》剧情
《Lie to me》悬疑
《Lie to me》警匪
《Lie to me》动作
《Lie to me》心理
《Lie to me》剧情
《战狼2》战争
《战狼2》动作
《战狼2》灾难

 4.数据准备

  • 创建本地movie.txt，导入数据

[root@hadoop dool]# vim movie.txt
《疑犯追踪》    悬疑,动作,科幻,剧情
《Lie to me》    悬疑,警匪,动作,心理,剧情
《战狼2》    战争,动作,灾难

  • 创建hive表

create table movie_info(
    movie string, 
    category string) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

  • 向表中导入数据

load data local inpath "/usr/word/dool/movie.txt"
into table movie_info;

验证表数据

0: jdbc:hive2://192.168.171.151:10000> select * from movie_info;
+-------------------+-----------------------------+--+
| movie_info.movie  |     movie_info.category     |
+-------------------+-----------------------------+--+
| 《疑犯追踪》            | ["悬疑","动作","科幻","剧情"]       |
| 《Lie to me》       | ["悬疑","警匪","动作","心理","剧情"]  |
| 《战狼2》             | ["战争","动作","灾难"]            |
|                   | NULL                        |
+-------------------+-----------------------------+--+
4 rows selected (0.204 seconds)

5.按需求查询数据

第一步

用split将category切分成数组，用explode函数将category数组炸开

0: jdbc:hive2://192.168.171.151:10000> select 
. . . . . . . . . . . . . . . . . . > explode(split(category,','))
. . . . . . . . . . . . . . . . . . > from movie_info;
+------+--+
| col  |
+------+--+
| 悬疑   |
| 动作   |
| 科幻   |
| 剧情   |
| 悬疑   |
| 警匪   |
| 动作   |
| 心理   |
| 剧情   |
| 战争   |
| 动作   |
| 灾难   |
+------+--+
12 rows selected (0.158 seconds)

第二步

用lateral view函数对原表的movie字段对炸开表进行侧写

0: jdbc:hive2://192.168.171.151:10000> select
. . . . . . . . . . . . . . . . . . > movie,
. . . . . . . . . . . . . . . . . . > category_name
. . . . . . . . . . . . . . . . . . > from movie_info
. . . . . . . . . . . . . . . . . . > lateral view 
. . . . . . . . . . . . . . . . . . > explode(split(category,',')) movie_tmp AS category_name;
+--------------+----------------+--+
|    movie     | category_name  |
+--------------+----------------+--+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+--+
12 rows selected (0.166 seconds)

6.情况简单下，多用如下方式

  • 创建hive表
create table movie_info2(
movie string,
category (split<string>))
row format delimited fields terminated by "\t"
collection items terminated by ",";
  • 查询数据
0: jdbc:hive2://192.168.171.151:10000> select
. . . . . . . . . . . . . . . . . . > movie,
. . . . . . . . . . . . . . . . . . > category_name
. . . . . . . . . . . . . . . . . . > from movie_info
. . . . . . . . . . . . . . . . . . > lateral view 
. . . . . . . . . . . . . . . . . . > explode (category) movie_tmp AS category_name;
+--------------+----------------+--+
|    movie     | category_name  |
+--------------+----------------+--+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+--+
12 rows selected (0.13 seconds)

lambda-小张
关注
专栏目录
hive操作（行转列，列转行）
aiduo3346的博客
09-05 4203
一、行转列 1．相关函数说明 CONCAT(stringA/col,stringB/col…)：返回输入字符串连接后的结果，支持任意个输入字符串; CONCAT_WS(separator,str1,str2,...)：它是一个特殊形式的CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL，返回值也将为NULL。这个...
Hive行转列、列转行
王义凯 的博客
12-29 844
hive 行转列，列转行
Hive中的行转列和列转行
zhr2415658919的博客
12-05 2956
在使用Hive的数据开发工作中，为了处理复杂的业务需求，经常要用到行转列或列转行的操作。为了节省以后处理这类工作的时间，提高工作效率，现将Hive行列互转的操作方法总结如下。列转行，顾名思义，将原本某列中一行的数据拆分为多行，该操作会使得数据行数增多。我们采用的方法是使用LATERAL VIEW语法，配合explode函数（或其他UDTF）Hive中是有可以直接将一行输出为多行的函数的（即UDTF），比如explode函数，举一个例子来说明这个函数的用法，数据集如下： 其中score字段为数组ARRAY(B
Hive(十)函数 列转行
最新发布
技术博客
08-13 1084
select * from (select name, x, a from people lateral view explode(xinge) x_tmp as x lateral view explode(aihao) a_tmp as a) tmp where x='活泼' and a='打篮球';例如，如果有一个包含数组的列，可以使用Lateral View拆分数组中的元素为多行数据。列转行是指将表中的列数据转换为行，通常使用LATERAL VIEW EXPLODE语法结合SELECT语句。
Hive--行转列/列转行
热门推荐
qq_46893497的博客
11-22 2万+
一、lateral view 分类：视图 功能：配合UDTF来使用,把某一行数据拆分成多行数据 很多的UDTF不能将结果与源表进行关联，使用lateral view ，可以将UDTF拆分的单个字段数据与原始表数据关联上 使用方式： select …… from tabelA lateral view UDTF(xxx) 视图名 as a,b,c 案例： --准备数据:vim /export/datas/lateral.txt 1 http://facebook.com/path/p1.php?q
hive中的行转列，列转行
mo
06-19 2401
行转列： 1.行转列所用的函数 CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串; CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数作为剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间; COLLECT_SET(col)：函数
hive行转列与列转行
qq_24790473的博客
11-15 6913
** 第一部分：行转列* 1.简单行转列：将一个融合多个信息的字段拆分成多行 步骤：先用split切开按照，进行分隔成数组的形式 ["aaa","bbb","ccc"] 然后再用explode函数炸开，将一行分成多行 （1）将一个融合多个信息的字段拆分为多行 aaa,bbb,ccc ===> aaa bbb ccc （2）实现方法：利用内置UDTF函数explode实现 split('aaa,bbb,
Hive之行转列与列转行
qq_27035897的博客
09-27 397
行转列 原始数据： 需求： 把星座和血型一样的人归类到一起。结果如下： 射手座,A 大海|凤姐 白羊座,A 孙悟空|猪八戒 白羊座,B 宋宋 实现： vi person_info.txt 孙悟空 白羊座 A 大海 射手座 A 宋宋 ...
HIVE行转列和列转行
jiantianming2的博客
01-28 1万+
行转列 select dt_month,valid_num,unvalid_num from test.test_xw_rowtocol 数据如下 转换要求：行转列需保留列名，如下图 方式一：采用union all的形式 select dt_month ,'valid_num' as type ​ ,sum(valid_num) as num
hive行转列、列转行真题解析
静待花开
03-31 2235
本文目录一、行转列1.1、行转列需要用到的函数1.2、真题实战1.2.1、需求1.2.2、创建hive表并导入数据1.2.3、按需求解题二、列转行2.1、列转行需要用到的函数2.2、真题实战2.2.1 、需求2.2.2、创建hive表并导入数据2.2.3、按需求解题2.2.4、详细解析上述sql三、总结 一、行转列 1.1、行转列需要用到的函数 CONCAT(string A/col, str...
【Hive】（十）Hive 行转列、列转行详解_hive 字段变成行
2401_84182318的博客
04-17 1310
(img-a9P0O62O-1713343047398)]把星座和血型一样的人归类到一起。
hive列转行案例
01-07
hive列转行 1．函数说明 EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。 LATERAL VIEW ： 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias 解释：用于和 split, explode 等 ...
hive行转列/列转行
chimchim66的博客
05-20 353
准备建表语句： create table syc_ads.test_transform ( name string comment '姓名', constellation string comment '星座', blood string comment '血型', hobby string comment '爱好' ); 准备测试数据： insert into table syc_ads.test_transform va
hive中行转列，列转行的使用
十色花的博客
08-28 1611
一、行转列的使用 １、问题 hive如何将 a       b       1 a       b       2 a       b       3 c       d       4 c       d       5 c       d       6 变为： a       b       1,2,3 c       d       4,5,6   ２、数据 test....
Hive 行转列&列转行
wzj_wp的博客
04-15 1230
行转列 concat_ws: hive (wzj)> desc function extended concat_ws; OK tab_name concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator. Exa...
Hive中行转列与列转行
qq_44540985的博客
11-12 1652
行专列 CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字 符串; CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参为指定分隔符。 CONCAT_WS(separator, array_name)：数组以separator为分隔符进行拼接。 COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重 汇总，产生 Arr
Hive 行转列、列转行
hello_heheha的博客
12-21 759
hive 行列之间数据相互转换
hive行转列、列转行
09-01
Hive中的行转列和列转行可以通过使用函数EXPLODE和LATERAL VIEW来实现。函数EXPLODE(col)用于将Hive的一列中复杂的数组或者Map结构拆分成多行。LATERAL VIEW是用于和split、explode等函数一起使用的，可以将一列展开成多列或将多列合并成一列。 举个例子来说明，假设我们有一个表person_info，其中包含name、constellation和blood_type三列。要将表中的name列中的值拆分成多行，可以使用LATERAL VIEW和EXPLODE函数，具体的语句如下： SELECT t1.base, concat_ws('|', collect_set(t1.name)) name FROM (SELECT name, concat(constellation, ",", blood_type) base FROM person_info) t1 GROUP BY t1.base; 在上述查询中，通过LATERAL VIEW和EXPLODE函数，将name列拆分成多行，然后使用concat_ws函数将拼接后的结果作为新的name列。最后，通过GROUP BY对base列进行分组。这样就实现了Hive中的行转列操作。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [hive列转行案例](https://download.csdn.net/download/weixin_38581777/14037437)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *2* *3* [hive操作（行转列，列转行）](https://blog.csdn.net/aiduo3346/article/details/102085019)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

lambda-小张

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值