Hive查询：行转列和列转行使用场景说明

最新推荐文章于 2025-05-19 19:09:54 发布

「已注销」

最新推荐文章于 2025-05-19 19:09:54 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

分类专栏：大数据与人工智能文章标签： hive hadoop lateral view collect_set linux

转载注明出处

本文链接：https://blog.csdn.net/qq_15973399/article/details/89281778

大数据与人工智能专栏收录该内容

50 篇文章

订阅专栏

本文介绍了Hive中行转列和列转行的使用场景，通过具体操作过程展示了如何利用CONCAT, COLLECT_SET等函数进行数据转换。在处理复杂数据时，行转列有助于简化数据结构，而列转行则适用于拆分数组或映射结构。总结中强调了在处理非关系型数据库数据时，列转行操作的重要性。" 112037192,7978238,计算三个整数的和、积与平均值,"['算法', '数学运算', '编程任务']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 引言

对于复杂的数据，存在复杂的数据类型，面对复杂的需求，可以考虑使用行转列，或者列转行的操作

1.1 行转列说明

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

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

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

当字段存在多个数据组合或者经过变化形成类似的样子，可以考虑行转列（一个列存在了太多了信息）

突破group by 限制，将重复的列元素，转成一行显示。

1.2 列转行说明

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

lateral view

用法：lateral view udtf(expression) tableAlias AS columnAlias

如：lateral view explode(col) table_temp as col_name

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

2. 操作过程

2.1 行转列操作

（1）数据准备

name const blood_type

小明   魔羯座   A
大刚   处女座   A
张三   魔羯座   B
李四   魔羯座   A
王五   处女座   A

（2）将数据保存在Hive中

hive (default)> create table person_const(
              > name string,
              > const string,
              > blood_type string)
              > row format delimited fields terminated by '\t';
OK
Time taken: 0.138 seconds
hive (default)> load data local inpath '/home/fanl/person_const.txt' into table person_const;
Loading data to table default.person_const
Table default.person_const stats: [numFiles=1, totalSize=98]
OK
Time taken: 0.275 seconds
hive (default)> select * from person_const;
OK
person_const.name	person_const.const	person_const.blood_type
小明	魔羯座	A
大刚	处女座	A
张三	魔羯座	B
李四	魔羯座	A
王五	处女座	A
Time taken: 0.063 seconds, Fetched: 5 row(s)
hive (default)>

（3）实现需求：将星座和血型一样的名字列在一行，如：

魔羯座,A 小明|李四

首先，使用concat将星座和血型用,合并

hive (default)> select name,concat(const,',',blood_type) base from person_const;
OK
name	base
小明	魔羯座,A
大刚	处女座,A
张三	魔羯座,B
李四	魔羯座,A
王五	处女座,A
Time taken: 0.066 seconds, Fetched: 5 row(s)
hive (default)>

接着，从以上数据中，用collect_set将重复

hive (default)> select base,concat_ws('|',collect_set(name)) as name 
> from person_const_temp group by base;
OK
base	name
处女座,A	大刚|王五
魔羯座,A	小明|李四
魔羯座,B	张三
Time taken: 19.426 seconds, Fetched: 3 row(s)

2.2 列转行

（1）数据准备

hive (default)> create table movie(
              > name string,
              > type array<string>)
              > row format delimited fields terminated by '\t'
              > collection items terminated by ',';
OK
Time taken: 0.039 seconds

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

将以上数据加载到表中

hive (default)> load data local inpath '/home/fanl/movie.txt' into table movie;
Loading data to table default.movie
Table default.movie stats: [numFiles=1, totalSize=136]
OK
Time taken: 0.15 seconds
hive (default)> 

hive (default)> select * from movie;
OK
movie.name	movie.type
《疑犯追踪》	["悬疑","动作","科幻","剧情"]
《Lie to me》	["悬疑","警匪","动作","心理","剧情"]
《战狼2》	["战争","动作","灾难"]

（2）将按type类型将数据按行显示

hive (default)> select name,typename from movie 
> lateral view explode(type) table_temp as typename;
OK
name	typename
《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情
《Lie to me》	悬疑
《Lie to me》	警匪
《Lie to me》	动作
《Lie to me》	心理
《Lie to me》	剧情
《战狼2》	战争
《战狼2》	动作
《战狼2》	灾难
Time taken: 0.044 seconds, Fetched: 12 row(s)
hive (default)>