Hive 初级练习 50 题（完美解答版）

原始数据

 
data 文件中有 student、course、teacher、score 四张表，表中数据如下：
 

student.csv

s_id,s_name,s_birth,s_sex
1,赵雷,1990-01-01,男
2,钱电,1990-12-21,男
3,孙风,1990-05-20,男
4,李云,1990-08-06,男
5,周梅,1991-12-01,
6,吴兰,1992-03-01,女
7,郑竹,1989-07-01,女
8,王菊,1990-01-20,女

course.csv

c_id,c_name,t_id
1,语文,2
2,数学,1
3,英语,4

teacher.csv

t_id,t_name
1,张三
2,李四
3,王五

score.csv

s_id,c_id,s_score
1,1,80
1,2,90
1,3,99
2,1,70
2,2,60
2,3,180
3,1,80
3,2,80
3,3,80
4,1,50
4,2,30
4,3,-20
5,1,76
5,2,87
6,1,31
6,3,34
7,2,89
7,3,98

 

数据清洗

 
要求：

1. 将 data 文件中的数据分别导入到 HIVE 中构建 ODS 层的四张外部表
   ext_student\ext_course\ext_teacher\ext_score

2. 对数据进行清洗生成对应 DWD 层的四张内部表
   (1) student 性别缺失，请将未填充性别的人员设置为女
   (2) course 教员编号错误，请将没有担任课程的教员编号填充到错误的教员编号
   (3) score 请根据课程制作分区表，并在填充数据时检验分数是否合理，对不合理的分数计算该学员其他课程的平均成绩作为不合理成绩的分数

1、上传数据

在 hdfs 上创建数据文件目录

hdfs dfs -mkdir -p /tmp/hive_test/data/student
hdfs dfs -mkdir -p /tmp/hive_test/data/course
hdfs dfs -mkdir -p /tmp/hive_test/data/teacher
hdfs dfs -mkdir -p /tmp/hive_test/data/score

上传 csv 文件到 hdfs 数据文件目录

hdfs dfs -put /opt/data/student.csv /tmp/hive_test/data/student
hdfs dfs -put /opt/data/course.csv /tmp/hive_test/data/course
hdfs dfs -put /opt/data/teacher.csv /tmp/hive_test/data/teacher
hdfs dfs -put /opt/data/score.csv /tmp/hive_test/data/score

 

2、导入数据

创建数据库 hive_test

drop database if exists hive_test cascade;
create database hive_test;
use hive_test;

创建外部表 ext_student

create external table if not exists ext_student (
	s_id int,
	s_name string,
	s_birth date,
	s_sex string
)
row format delimited fields terminated by ','
stored as textfile
location '/tmp/hive_test/data/student'
tblproperties("skip.header.line.count"="1")

创建外部表 ext_course

create external table if not exists ext_course (
	c_id int,
	c_name string,
	t_id int
)
row format delimited fields terminated by ','
stored as textfile
location '/tmp/hive_test/data/course'
tblproperties("skip.header.line.count"="1")

创建外部表 ext_teacher

create external table if not exists ext_teacher (
	t_id int,
	t_name string
)
row format delimited fields terminated by ','
stored as textfile
location '/tmp/hive_test/data/teacher'
tblproperties("skip.header.line.count"="1")

创建外部表 ext_score

create external table if not exists ext_score (
	s_id int,
	c_id int,
	s_score int
)
row format delimited fields terminated by ','
stored as textfile
location '/tmp/hive_test/data/score'
tblproperties("skip.header.line.count"="1")

 

3、清洗数据

创建内部表 student，将未填充性别的人员设置为女

create table if not exists student as
select s_id,s_name,s_birth,if(s_sex = '', '女', s_sex) as s_sex from ext_student;

创建内部表 teacher，数据正常无需清洗，直接导入原始数据

create table if not exists teacher as
select * from ext_teacher;

创建内部表 course，将没有担任课程的教员编号填充到错误的教员编号

create table if not exists course as
with
-- t1：正确的课程信息及教员编号：c_id,c_name,t_id
t1 as (select c_id,c_name,t_id from ext_course c where exists(select * from teacher t where t.t_id = c.t_id)),
-- t2：教员编号错误的课程信息：c_id,c_name
t2 as (select c.c_id,c.c_name from ext_course c where not exists(select * from t1 where t1.t_id = c.t_id)),
-- t3：未担任课程的教员编号：t_id
t3 as (select t.t_id from teacher t where not exists(select * from ext_course c where c.t_id = t.t_id)),
-- t4：t2和t3拼接（交叉连接），将未担任课程的教员编号拼到教员编号错误的课程信息后，再与t1合并，即为正确的数据
t4 as (select c_id,c_name,t_id from t1 union all select c_id,c_name,t_id from t2,t3)
-- union后顺序会乱，需要按照课程id排下序
select * from t4 order by c_id

创建内部表 score，按照课程分区

create table if not exists score (
	s_id int,
	s_score int
)
partitioned by (c_id int)
row format delimited fields terminated by ','
stored as textfile

-- 开启动态分区
set hive.exec.dynamic.partition=true
set hive.exec.dynamic.partition.mode=nonstrict

-- 清洗外部表ext_score并将数据导入内部表score
-- 对不合理成绩的分数计算该学员其他课程的平均成绩替换该分数
with
t1 as (select s_id,round(avg(s_score), 1) as avg_score from ext_score where s_score between 0 and 100 group by s_id)
insert overwrite table score partition(c_id)
select sc.s_id,if(sc.s_score < 0 or sc.s_score > 100, t1.avg_score, sc.s_score) as s_score,sc.c_idfrom ext_score sc inner join t1 on sc.s_id = t1.s_id

 

4、验证数据

查看学生表 student

select * from student;

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    1 | 赵雷   | 1990-01-01 | 男    |
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
|    5 | 周梅   | 1991-12-01 | 女    |
|    6 | 吴兰   | 1992-03-01 | 女    |
|    7 | 郑竹   | 1989-07-01 | 女    |
|    8 | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

查看课程表 course

select * from course;

+------+--------+------+
| c_id | c_name | t_id |
+------+--------+------+
|    1 | 语文   |    2 |
|    2 | 数学   |    1 |
|    3 | 英语   |    3 |
+------+--------+------+

查看教师表 teacher

select * from teacher;

+------+--------+
| t_id | t_name |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
+------+--------+

查看成绩表 score

select * from score;

+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
|    1 |    1 |      80 |
|    1 |    2 |      90 |
|    1 |    3 |      99 |
|    2 |    1 |      70 |
|    2 |    2 |      60 |
|    2 |    3 |      65 |
|    3 |    1 |      80 |
|    3 |    2 |      80 |
|    3 |    3 |      80 |
|    4 |    1 |      50 |
|    4 |    2 |      30 |
|    4 |    3 |      40 |
|    5 |    1 |      76 |
|    5 |    2 |      87 |
|    6 |    1 |      31 |
|    6 |    3 |      34 |
|    7 |    2 |      89 |
|    7 |    3 |      98 |
+------+------+---------+

表关系如下

 

练习 50 题

 

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

with
sc1 as (select * from score where c_id = 1),
sc2 as (select * from score where c_id = 2)
select s.*,sc1.s_score as score_01,sc2.s_score as score_02
from student s
inner join sc1 on s.s_id = sc1.s_id
inner join sc2 on s.s_id = sc2.s_id
where sc1.s_score > sc2.s_score;

+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth    | s_sex | score_01 | score_02 |
+------+--------+------------+-------+----------+----------+
|    2 | 钱电   | 1990-12-21 | 男    |       70 |       60 |
|    4 | 李云   | 1990-08-06 | 男    |       50 |       30 |
+------+--------+------------+-------+----------+----------+

 

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

with
sc1 as (select * from score where c_id = 1),
sc2 as (select * from score where c_id = 2)
select s.*,sc1.s_score as score_01,sc2.s_score as score_02
from student s
inner join sc1 on s.s_id = sc1.s_id
inner join sc2 on s.s_id = sc2.s_id
where sc1.s_score < sc2.s_score;

+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth    | s_sex | score_01 | score_02 |
+------+--------+------------+-------+----------+----------+
|    1 | 赵雷   | 1990-01-01 | 男    |       80 |       90 |
|    5 | 周梅   | 1991-12-01 | 女    |       76 |       87 |
+------+--------+------------+-------+----------+----------+

 

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

with
t1 as (select s_id,avg(s_score) as avg_score from score group by s_id having avg_score >= 60)
select s.s_id,s.s_name,round(avg_score, 2) as avg_score
from student s
inner join t1 on s.s_id = t1.s_id;

+------+--------+-----------+
| s_id