创建测试表:
create table stu1(
name string comment '姓名',
chinese string comment '语文',
math string comment '数学',
english string comment '英语'
)comment '学生成绩表1' row format delimited fields terminated by ',';
create table stu2(
name string comment '姓名',
subject string comment '科目',
performance string comment '成绩'
)comment '学生成绩表2' row format delimited fields terminated by ',';
加载数据:
load data local inpath '/home/Devin/stu2.txt' into table stu2;
hive (pingan)> select * from stu1;
OK
stu1.name stu1.chinese stu1.math stu1.english
小王 75 73 86
小李 98 87 89
小刘 76 87 65
Time taken: 0.355 seconds, Fetched: 3 row(s)
hive (pingan)> select * from stu2;
OK
stu2.name stu2.suject stu2.performance
小王 chinese 75
小李 chinese 98
小刘 chinese 76
小王 math 73
小李 math 87
小刘 math 87
小王 english 86
小李 english 89
小刘 english 65
Time taken: 0.078 seconds, Fetched: 9 row(s)
行转列:stu1数据格式转为stu2数据格式
select name,
t.subject,
t.performance
from stu1
lateral view explode(
str_to_map(
concat('chinese=',chinese,'&math=',math,'&english=',english)
,'&','=')
) t as subject,performance;
name t.subject t.performance
小王 math 73
小王 english 86
小王 chinese 75
小李 math 87
小李 english 89
小李 chinese 98
小刘 math 87
小刘 english 65
小刘 chinese 76
Time taken: 89.781 seconds, Fetched: 9 row(s)
列转行:stu2数据格式转为stu1数据格式
select name,
sum(case when subject = 'chinese' then performance end) as chinese,
sum(case when subject = 'math' then performance end) as math,
sum(case when subject = 'english' then performance end) as english
from stu2
group by name;
name chinese math english
小刘 76.0 87.0 65.0
小李 98.0 87.0 89.0
小王 75.0 73.0 86.0
Time taken: 88.087 seconds, Fetched: 3 row(s)
Lateral View用法与Hive UDTF explode:https://blog.csdn.net/oopsoom/article/details/26001307
hive中UDF、UDAF和UDTF详解:https://blog.csdn.net/zhaoli081223/article/details/46637517