SQL专项练习第一天
在大数据处理中，Hive 是一个非常强大的工具。今天，为大家分享五个 Hive 数据处理的实际案例，展示 Hive 在数据清洗、转换和分析方面的强大能力。
先在home文件夹下建一个hivedata文件夹，把我们所需的数据写成txt文件导入到/home/hivedata/文件夹下面。
第一题、合并相同星座和血型的人
我们有一组人员信息，包括名字、星座和血型。我们的目标是将相同星座和血型的人合并在一起。
如下数据：
小狮 水瓶座 A
小猿 射手座 A
小云 水瓶座 B
小锋 水瓶座 A
小琪 射手座 A变为：
指标：将相同星座和血型的人合并在一起
射手座,A 小猿|小琪
水瓶座,A 小狮|小锋
水瓶座,B 小云建表：
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited
fields terminated by " "
stored as textfile;加载数据：
load data local inpath '/home/hivedata/constellation.txt' into table person_info;查询数据是否导入成功
select * from person_info;通过以下 Hive 语句，我们可以实现
select concat(constellation, "," ,blood_type)as cb,concat_ws('|', collect_list(name)) as names
from person_info
group by constellation, blood_type;第二题、电影分类展开
我们有一组电影信息，包括电影名称和分类。我们的目标是将每个电影的分类展开，以便更好地进行分析。
如下数据：
《阿凡达2》 悬疑,动作,科幻,剧情
《满江红》 悬疑,警匪,动作,心理,剧情
《流浪地球》 科幻,动作,灾难想得到如下数据：
《阿凡达2》 悬疑
《阿凡达2》 动作
《阿凡达2》 科幻
《阿凡达2》 剧情
《满江红》 悬疑
《满江红》 警匪
《满江红》 动作
《满江红》 心理
《满江红》 剧情
《流浪地球》 科幻
《流浪地球》 动作
《流浪地球》 灾难建表：
create table movie(movie string,category array<string>
)
row format delimited
fields terminated by ' '
collection items terminated by ','
tblproperties("skip.header.line.count"="1"); -- 跳过第一行数据加载数据：
load data local inpath '/home/hivedata/movies.txt' into table movie ;查询数据是否导入成功
select * from movie;通过以下 Hive 语句，我们可以实现
-- 字段是array类型
select movie, category1 from movie lateral view explode(category) mytable as category1;-- 字段是string类型
select movie, category1 from movie lateral view explode(category) mytable as category1;第三题、查找三个表中互不重复的数据
我们有三个表，分别是 A、B、C。数据如下我们的目标是找出这三个表中互不重复的数据。
提示: 不重复的数据，即在三张表中所有的数据加在一起时，只出现了一次。
A.txt
1
2
3
4
5
6
7
8
9
6
7B.txt
2
3
11
12
14
15
16
18
35
30
40C.txt
1
2
3
11
5
6
7
8
20建表：
-- 新建三个表：
create table a(id int);
create table b(id int);
create table c(id int);加载数据：
load data local inpath '/home/hivedata/zuoye03_a.txt' into table A;
load data local inpath '/home/hivedata/zuoye03_b.txt' into table B;
load data local inpath '/home/hivedata/zuoye03_c.txt' into table C;查询数据是否导入成功
select * from a;
select * from b;
select * from c;通过以下 Hive 语句，我们可以实现
select aa.id from (select id from aunion allselect id from bunion allselect id from c) aa group by aa.id
having count(*) = 1;
第四题、求出场次数最多的前三的英雄
我们有一组游戏数据，包括游戏 ID 和英雄列表。我们的目标是找出出场次数最多的前三的英雄。
如下数据：
1 廉颇,镜,沈梦溪,李元芳,太乙真人
2 关羽,兰陵王,嬴政,虞姬,鲁班大师
3 梦琪,盘古,周瑜,狄仁杰,大乔
4 廉颇,澜,上官婉儿,公孙离,盾山
5 吕布,娜可露露,姜子牙,公孙离,张飞
6 马超,猪八戒,狄仁杰,沈梦溪,太乙真人
7 吕布,盘古,嫦娥,公孙离,张飞
8 廉颇,橘右京,西施,虞姬,大乔
9 关羽,镜,姜子牙,狄仁杰,鲁班大师
10 梦琪,阿古朵,周瑜,后羿,蔡文姬
11 夏侯惇,娜可露露,不知火舞,孙尚香,太乙真人
12 猪八戒,镜,嫦娥,伽罗,孙膑
13 廉颇,镜,上官婉儿,马可波罗,蔡文姬
14 梦琪,裴擒虎,沈梦溪,虞姬,鲁班大师
15 梦琪,盘古,不知火舞,成吉思汗,太乙真人
16 夏侯惇,澜,周瑜,马可波罗,张飞
17 猪八戒,露娜,周瑜,狄仁杰,盾山
18 吕布,橘右京,西施,蒙伢,蔡文姬
19 吕布,赵云,西施,公孙离,张飞
20 廉颇,兰陵王,沈梦溪,虞姬,大乔建表：
create table game(
id int,
heros array<string>
)row format delimited
fields terminated by ' '
collection items terminated by ',' ;加载数据：
load data local inpath '/home/hivedata/heros.txt' into table game;查询数据是否导入成功
select * from game;通过以下 Hive 语句，我们可以实现
with t as(select name, count(1) csfrom game lateral view explode(heros) mytable as namegroup by name
), t2 as (select name, cs, dense_rank() over (order by cs desc ) pm from t
)
select * from t2 where pm <= 3;第五题、行转列展示学生选修课程
我们有一组学生选修课程的数据。我们的目标是将这些数据转换为行转列的形式，以便更好地展示每个学生的选修情况。
有如下数据，表示1、2、3三名学生选修了a、b、c、d、e、f中的若干课程
id course
1 a
1 b
1 c
1 e
2 a
2 c
2 d
2 f
3 a
3 b
3 c
3 e根据如上数据，查询出如下结果，其中1表示选修，0表示未选修
id a b c d e f
1 1 1 1 0 1 0
2 1 0 1 1 0 1
3 1 1 1 0 1 0建表：
create table courses(
id int,
course string
)
row format delimited
fields terminated by ' '
tblproperties("skip.header.line.count"="1"); -- 跳过第一行数据加载数据：
load data local inpath '/home/hivedata/homework0823/course.txt' into table courses;查询数据是否导入成功
select * from courses;通过以下 Hive 语句，我们可以实现
--方法一
select id,max(case when course='a' then 1 else 0 end) a,max(case when course='b' then 1 else 0 end) b,max(case when course='c' then 1 else 0 end) c,max(case when course='d' then 1 else 0 end) d,max(case when course='e' then 1 else 0 end) e,max(case when course='f' then 1 else 0 end) f
from courses group by id;--方法二
select id,sum(case when course='a' then 1 else 0 end) a,sum(case when course='b' then 1 else 0 end) b,sum(case when course='c' then 1 else 0 end) c,sum(case when course='d' then 1 else 0 end) d,sum(case when course='e' then 1 else 0 end) e,sum(case when course='f' then 1 else 0 end) f
from courses group by id;
通过以上五个案例，我们可以看到 Hive 在数据处理方面的强大能力。无论是数据清洗、转换还是分析，Hive 都提供了丰富的函数和工具，帮助我们轻松地处理大规模数据。希望这些案例对大家在 Hive 数据处理方面有所帮助。