hive行列互换

于 2024-05-16 08:31:16 发布
阅读量531 收藏 14
点赞数 25
文章标签： hive hadoop 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_77836489/article/details/138496825
版权

hive行列互换

今天你用钱袋里的铜板充满你的内心，明天你的内心就可以把你的钱袋注满黄金。

目录

1.创建表

2.上传数据

3.实现需求

4.行列互换

1）行转列：    

2）列传行：

①如何转换：（案例）

②EXPLODE() 函数：将集合中的一行数据转换为多行

③示例2：增加一列数据

解析：  

1.创建表


CREATE EXTERNAL TABLE IF NOT EXISTS learn3.student20(
id STRING COMMENT "学生ID",
name STRING COMMENT "学生姓名",
age int COMMENT "年龄",
gender STRING COMMENT "性别",
subject STRING COMMENT "学科"
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
STORED AS TEXTFILE;

2.上传数据

load data local inpath "/usr/local/soft/hive-3.1.2/data/student_20.txt" INTO TABLE learn3.student20;

3.实现需求

理科|男  1500100020|杭振凯,1500100019|娄曦之
---需求：将两列数据进行拼接 按照上面的格式存储输出

select
CONCAT(subject,"|",gender) as subject_gender
,CONCAT(name,"|",id) as name_id
from learn3.student20;

然后我们需要进行合并

select r.subject_gender,collect_list(r.name_id)
from
(
select
CONCAT(subject,"|",gender) as subject_gender
,CONCAT(name,"|",id) as name_id
from learn3.student20
) r group by subject_gender;

但是 我们如果想要达到需要的结果 我们还需要做一些其他操作 去掉两边的[ ] 我们对SQL语句做一下改进

select r.subject_gender,CONCAT_WS(",",collect_list(r.name_id))
from
(
select
CONCAT(subject,"|",gender) as subject_gender
,CONCAT(name,"|",id) as name_id
from learn3.student20
) r group by subject_gender;

对以上代码 我们也可以使用WITH AS写法

WITH concat_stu AS(
select
CONCAT(subject,"|",gender) as subject_gender
,CONCAT(name,"|",id) as name_id
from learn3.student20)
select concat_stu .subject_gender,CONCAT_WS(",",collect_list(concat_stu .name_id))
from concat_stu group by subject_gender;

| 文科|女            | ["1500100001|施笑槐","1500100007|尚孤风","1500100016|潘访烟","1500100018|骆怜雪"] |
| 文科|男            | ["1500100002|吕金鹏","1500100013|逯君昊"]                |
| 理科|女            | ["1500100003|单乐蕊","1500100005|宣谷芹","1500100008|符半双","1500100012|梁易槐","1500100015|宦怀绿","1500100017|高芷天"] |
| 理科|男            | ["1500100004|葛德曜","1500100006|边昂雄","1500100009|沈德昌","1500100010|羿彦昌","1500100011|宰运华","1500100014|羿旭炎","1500100019|娄曦之","1500100020|杭振凯"] |


desc function concat_ws;



| concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator. |

我们通过查看 可以看到 concat_ws()中的既可以是array类型的参数 也可以传入一个string类型的参数 所以我们可以使用concat_ws()函数用 , 号将它们拼接在一起 

4.行列互换

+-----------------+-----------------+
| subject_gender  |     id_name     |
+-----------------+-----------------+
| 文科|女            | 1500100001|施笑槐  |
| 文科|男            | 1500100002|吕金鹏  |
| 理科|女            | 1500100003|单乐蕊  |
| 理科|男            | 1500100004|葛德曜  |
| 理科|女            | 1500100005|宣谷芹  |
| 理科|男            | 1500100006|边昂雄  |
| 文科|女            | 1500100007|尚孤风  |
| 理科|女            | 1500100008|符半双  |
| 理科|男            | 1500100009|沈德昌  |
| 理科|男            | 1500100010|羿彦昌  |
| 理科|男            | 1500100011|宰运华  |
| 理科|女            | 1500100012|梁易槐  |
| 文科|男            | 1500100013|逯君昊  |
| 理科|男            | 1500100014|羿旭炎  |
| 理科|女            | 1500100015|宦怀绿  |
| 文科|女            | 1500100016|潘访烟  |
| 理科|女            | 1500100017|高芷天  |
| 文科|女            | 1500100018|骆怜雪  |
| 理科|男            | 1500100019|娄曦之  |
| 理科|男            | 1500100020|杭振凯  |



| 文科|女            | 1500100001|施笑槐,1500100007|尚孤风,1500100016|潘访烟,1500100018|骆怜雪 |
| 文科|男            | 1500100002|吕金鹏,1500100013|逯君昊                      |
| 理科|女            | 1500100003|单乐蕊,1500100005|宣谷芹,1500100008|符半双,1500100012|梁易槐,1500100015|宦怀绿,1500100017|高芷天 |
| 理科|男            | 1500100004|葛德曜,1500100006|边昂雄,1500100009|沈德昌,1500100010|羿彦昌,1500100011|宰运华,1500100014|羿旭炎,1500100019|娄曦之,1500100020|杭振凯 |

1）行转列：
    

将原先多行数据转成一行

转换方式：   

通过COLLECT_SET() 或者 COLLECT_LIST() 和 GROUP BY 进行配合使用     

将GROUP BY 分组的数据进行存放于一个集合当中


2）列传行：

将一行数据转换为多行数据   

①如何转换：（案例）
案例：
| wordcount.word  |
+-----------------+
| hello,word      |
| hello,java      |
| hello,hive      |
| hello,word      |
| hello,java      |
| hive,hello      |

结果：
+----------+
| _u1.col  |
+----------+
| hello    |
| hive     |
| java     |
| word     |
| hello    |
| hive     |
+----------+

如果是这种格式一致的表 我们在进行列转行的操作时 可以使用拼接的方式

WITH split_res AS
(
select split(word,",")[0] as clo1,
split(word,",")[1] as clo2
from learn3.wordcount
)
,c1 AS
(
select clo1 as clo,count(*) as num
from split_res group by clo1
)
,c2 AS
(
select clo2 as clo,count(*) as num
from split_res group by clo2
)
select clo from c1
UNION ALL
select clo from c2;

现在我们的数据参差不齐 刚才的方法就没办法使用了 那么 有没有什么更方便的方法呢？

我们再插入一行数据

INSERT INTO TABLE learn3.wordcount (word) VALUES ("hello,word,hello,java,hello,spark");

②EXPLODE() 函数：将集合中的一行数据转换为多行
select
EXPLODE(split(word,",")) as word
FROM learn3.wordcount;

现在我们再对这个表做wordcount也就非常方便了 直接使用count和group by就可以了

select count(a.word) as num,a.word
from
(
select
EXPLODE(split(word,",")) as word
FROM learn3.wordcount
) a group by a.word;

③示例2：增加一列数据

CREATE TABLE learn3.movie(
movie_name STRING COMMENT "电影名",
types STRING COMMENT "电影类型"
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

load data local inpath "/usr/local/soft/hive-3.1.2/data/moive.txt" INTO TABLE learn3.movie;

需求：

---- 将types列中的电影类型进行分隔，并且与电影名进行对应
由  |肖申克的救赎            | 犯罪/剧情        |  
转换为：
  肖申克的救赎 犯罪
  肖申克的救赎 剧情


如果我们直接使用查询语句 我们会发现报下面的错误 因为我们现在多了一列数据 他怎么实现对应 这是一个问题

select movie_name
,EXPLODE(split(types,"/")) as type
FROM learn3.movie;

lateral view 用法：行转列

我们根据示例修改一下我们的SQL语句

select
movie_name,type
from learn3.movie LATERAL VIEW EXPLODE(split(types,"/")) view as type;



| movie.movie_name  | movie.types  |
+-------------------+--------------+
| 肖申克的救赎            | 犯罪/剧情        |
| 霸王别姬              | 剧情/爱情/同性     |
| 阿甘正传              | 剧情/爱情        |
| 泰坦尼克号             | 剧情/爱情/灾难     |
| 这个杀手不太冷           | 剧情/动作/犯罪     |


| movie_name  | type  |
+-------------+-------+
| 肖申克的救赎      | 犯罪    |
| 肖申克的救赎      | 剧情    |
| 霸王别姬        | 剧情    |
| 霸王别姬        | 爱情    |
| 霸王别姬        | 同性    |
| 阿甘正传        | 剧情    |
| 阿甘正传        | 爱情    |
| 泰坦尼克号       | 剧情    |
| 泰坦尼克号       | 爱情    |
| 泰坦尼克号       | 灾难    |
| 这个杀手不太冷     | 剧情    |
| 这个杀手不太冷     | 动作    |
| 这个杀手不太冷     | 犯罪    |

LATERAL VIEW EXPLODE(split(types,"/")) view as type

解析：  

① 通过split方法将types中的字符串切分为数组

② 通过EXPLODE方法将数组由一行数据转换为多行

③ 通过LATERAL VIEW 将EXPLODE转换的结果包装成一个名为view的一个侧写表，并且列名为type
④ 通过全连接将侧写表中的数据与原表 learn3.movie 中的一行数据进行全连接 

难以触及的高度
关注
  • 25
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Hive行列转换
一亿年开发经验
05-25 7927
1.多行转多列 姓名(name) 学科(subject) 成绩(score) A 语文 70 A 数学 80 A 英语 90 B 语文 75 B 数学 85 B 英语 95 行列转换思路分析及实现 多行转多列 如果需要将上⾯的样例表转换为 姓名 | 语⽂成绩 | 数学成绩 | 英语成绩 这样的格式，就是 多行转多列 思路： 涉及到行转成列，肯定是会按照某⼀列或者某⼏列的值进⾏分组来压缩⾏数，所以会⽤到group by。 分组之后需要⽤到聚合函数，由于多列中的每列只关
Hive函数--sql-行列转换--三
认真学代码的shell
09-28 3190
行专列、列转行语法.concat函数,concat_ws函数，concat_group函数之间的区别
Hive的行列转换（行转多列、多列转行、行转单列、单列转行）
热门推荐
whoami19871226的博客
06-10 1万+
在实际使用Hive的过程中，常常会涉及到行列转换，细分的话，有下面4种类型的行列转换，分别是： 1. 行转多列 2. 多列转行 3. 行转单列 4. 单列转行 下面我们通过样例介绍每种行列转换的实现方法。 样例表 班级成绩表： 姓名(name) 学科(subject) 成绩(score) A 语文 70 A 数学 90 A 英语 80 B 数学 95 B 英语 85 B 语文 75 行列转换思路分析及实现 行转多列 如果需要将上面的样例表转换为 “姓名 | 语文成绩
Hive 行列转换
weixin_30745641的博客
12-21 319
一、列转行 (对某列拆分，一列拆多行) 使用函数：lateral view explode(split(column, ',')) num eg: 如表：t_row_to_column_tmp 数据如下，对tag列进行拆分 SQL代码： select id,tag,tag_new from t_row_to_column_tmp lateral view explode(spl...
hive行列互换习题二
在路上的博客
09-21 169
hive习题行列互换习题二 userid subject score 001 语文 90 001 数学 92 001 英语 80 002 语文 88 002 数学 90 002 英语 75.5 003 语文 70 003 数学 85 003 英语 90 003 政治 72 userid 语文 数学 英语 政治 total 001 90 92 80 0 ...
seatunnel 支持hive jdbc
07-28
driver = "org.apache.hive.jdbc.HiveDriver" user = "hive" password = "hive" table = ods_wjw_jb_gxy_hz_glk query = "select a,b,c from ods_wjw_jb_gxy_hz_glk" fetch_size = 300 } }
hive-jdbc3.1.3
最新发布
08-22
argodb亲测可是使用hive驱动包，国产argodb的没有什么资料，当时使用的hive的一套语法，我还发布了其他版本的jdbc包，可以查看其他资源
Hive DML
01-07
Hive不支持update的操作。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。 load：加载数据到表 load data [local] inpath ‘filepath’ overwrite | into table 表名 [partition ...
hive 参数设置
01-07
命令行模式，或者说目录模式，可以使用hive 执行命令。 选项说明： 　-e : 执行短命令 　-f : 执行文件（适合脚本封装） 　-S : 安静模式，不显示MR的运行过程 　-hivevar : 用于定义hive运行时的变量替换 ，专门...
hive-3.1.1安装包
04-16
Hive是一个基于Hadoop的数据仓库工具，它本身并不存储数据，部署在Hadoop集群上，数据是存储在HDFS上的. Hive所建的表在HDFS上对应的是一个文件夹，表的内容对应的是一个文件。它不仅可以存储大量的数据而且可以对...
hive行列互换习题一
在路上的博客
09-21 183
hive行列互换习题一 1 用户商品行列互换 用户 商品 A P1 B P1 A P2 B P3 请你使用hql变成如下结果: 用 户 P1 P2 P3 A 1 1 0 B 1 0 1 create table t39( uname string, pro string ) row format delimited fields terminated by ‘\t’ ; select u...
hive-----行列互换
qq_42387496的博客
03-05 227
新建一张这样的表： CREATE TABLE users ( name string, game string, score double ); insert into users values('us1','game1',2.0 ) , ('us1','game2',3.0 ) , ('us1','game3',5.0 ) , ('us2','game2',4.0 ) , ('us2','game3',3.0 ) , ('us2','game4',7.0 ) , ('u...
Hive典型应用场景之行列转换
zryowen123的博客
08-30 7233
在使用Hive处理数据时，经常遇到行列转换的场景，本文将对Hive的行列转换操作做详细的说明。
Hive行列互转
weixin_50691399的博客
01-09 1338
本文主要是hive的列转行和行转列相关知识
Hive行列转换使用
Mr.pan felix的专栏
06-17 469
Hive典型应用场景之行列转换 在使用Hive处理数据时，经常遇到行列转换的场景，本文将对Hive的行列转换操作做详细的说明。 行转列 1）多行转多列 假设数据表 row2col： col1 col2 col3 a c 1 a d 2 a e 3 b c 4 b d 5 b e 6 现在要将其转化为： col1 c d e a .
Hive中行列转换
DataFlow范式
07-08 1万+
1、演示多列转为单行 数据文件及内容： student.txt xiaoming|english|92.0 xiaoming|chinese|98.0 xiaoming|math|89.5 huahua|chinese|80.0 huahua|math|89.5 创建表studnet： create table student(name string,subject strin
hive 行列转换案例
后知后觉的it路
01-03 1259
      0  stu表数据:   stu:id             namehello,you zm2008hello,me zm2015     1 实现单词计数:     (列转行)   ---> split切分+explode(炸开)   1.0 数据拆分成数组 select split(id,',') from  stu;    得到数组[hello...
hive行列互换不使用TRANSPOSE
04-03
Hive中没有直接的行列互换函数，但可以通过以下方式实现： 1. 使用CASE WHEN语句将列转换为行 例如，假设有以下表格： ``` id | name | age | gender -----|------|-----|------- 1 | Alice| 25 | F 2 | Bob | 30 | M 3 | Chris| 27 | M ``` 我们想要将它转换为以下形式： ``` field | 1 | 2 | 3 -------|------|------|------ name | Alice| Bob | Chris age | 25 | 30 | 27 gender | F | M | M ``` 可以使用以下查询语句： ``` SELECT 'name' AS field, MAX(CASE WHEN id = 1 THEN name END) AS 1, MAX(CASE WHEN id = 2 THEN name END) AS 2, MAX(CASE WHEN id = 3 THEN name END) AS 3 FROM my_table UNION ALL SELECT 'age' AS field, MAX(CASE WHEN id = 1 THEN age END) AS 1, MAX(CASE WHEN id = 2 THEN age END) AS 2, MAX(CASE WHEN id = 3 THEN age END) AS 3 FROM my_table UNION ALL SELECT 'gender' AS field, MAX(CASE WHEN id = 1 THEN gender END) AS 1, MAX(CASE WHEN id = 2 THEN gender END) AS 2, MAX(CASE WHEN id = 3 THEN gender END) AS 3 FROM my_table; ``` 2. 使用LATERAL VIEW EXPLODE函数将行转换为列 假设有以下表格： ``` id | category | value -----|----------|------ 1 | A | 10 1 | B | 20 1 | C | 30 2 | A | 40 2 | B | 50 2 | C | 60 ``` 我们想要将它转换为以下形式： ``` id | A | B | C -----|---|---|--- 1 | 10| 20| 30 2 | 40| 50| 60 ``` 可以使用以下查询语句： ``` SELECT id, MAX(CASE WHEN category = 'A' THEN value END) AS A, MAX(CASE WHEN category = 'B' THEN value END) AS B, MAX(CASE WHEN category = 'C' THEN value END) AS C FROM my_table LATERAL VIEW EXPLODE(array('A', 'B', 'C')) exploded_table AS category GROUP BY id; ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值