Hive综合应用案例

1、第1关：计算每个班的语文总成绩和数学总成绩禁止修改drop database if exists mydb cascade；禁止修改begin 创建mydb数据库create database if not exists mydb；使用mydb数据库use mydb；创建表scorecreate table if not exists score(name string comment 姓名Chinese string comment 语文成绩，maths string comment 数学成绩)row format delimited fields terminated bystored a

2、s textfile；导入数据:/root/data/stepl files/load data local inpath */root/data/stepl files/1 into table score；创建表classcreate table if not exists class(stuname string comment 姓名classname string comment 所在班级)row format delimited fields terminated bystored as textfile；导入数据:/root/data/stepl files/load data l

3、ocal inpath */root/data/stepl_flies/1 into table class；一计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计 算。selectfrom(select classname,sum Chinesefrom class ct score swhere = and =60group by tl,(select classname,sum mathsfrom class c,score swhere = and =60group by t2where =；end第2关：查询选修了3门以上的课程的学生姓名禁止修改drop d

4、atabase if exists mydb cascade；禁止修改begin 创建mydb数据库create database if not exists mydb；使用mydb数据库use mydb；创建表my stucreate table if not exists my stu(id string comment 学生 id,name string comment 学生姓名sex string comment 性别age string comment 年龄col string comment 所选的系)row format delimited fields terminated b

5、y Tstored as textfile；导入数据:/root/data/step2 fi les/load data local inpath */ioot/data/step2_fi1es/1 into table my stu；创建表my scorecreate table if not exists my score(id string comment 学生 id,courseid string comment 课程 id,score string comment 成绩)row format delimited fields terminated by Tstored as text

6、file；导入数据:/root/data/step2 _f i les/load data local inpath */root/data/step2_fi1es/* into table my_score； 创建表my coursecreate table if not exists my course(course id string comment 课程 id,coursename string comment 课程名称i)row format delimited fields terminated bystored as textfile；导入数据：/root/data/step2

7、files/load data local inpath */root/data/step2_fi1es/* into table my course；一-查询选修了 3门以上的课程的学生姓名。select .from(select id,count(courseid) coursenumfrom my scoregroup by id) t.my_stu stuwhere =3 and 二；end第3关：课程选修人数禁止修改drop database if exists mydb cascade；禁止修改 begin 创建mydb数据库create database if not exist

8、s mydb；使用mydb数据库use mydb；创建表my stucreate table if not exists my stu( id string comment 学生 id, name string comment 学生姓名 sex string comment 性别age string comment 年龄col string comment 所选的系)row format delimited fields terminated by stored as textfile；导入数据:/root/data/step2 f i les/load data local inpath *

9、/root/data/step2_fi1es/* into table my stu；创建表my score?create table if not exists my score(id string comment 学生 id,courseid string comment 课程 id,score string comment 成绩)row format delimited fields terminated bystored as textfile；导入数据：/root/data/step2 fi 1 es/load data local inpath */root/data/step2.

10、_fi1es/* into table my_score； 仓U建表 my coursecreate table if not exists my course(courseid string comment 课程 id,coursename string comment 课程名称)row format delimited fields terminated bystored as textfile；导入数据：/root/data/step2 files/load data local inpath */root/data/step2_files/* into table my一course；

11、 一-查询每个课程有多少人选修。select .count(*)from(select name, coursenamefrom(select name, courseidfrom my score scorey_stu stuwhere = t1,my course coursewhere = t2group by ；end第4关：shujuku课程的平均成绩禁止修改drop database if exists mydb cascade；禁止修改begin 创建mydb数据库create database if not exists mydb；使用mydb数据库use mydb；创建表my

12、 stucreate table if not exists my_stu(id string comment 学生 id,name string comment 学生姓名sex string comment 性别age string comment 年龄kcol string comment 所选的系)row format delimited fields terminated bystored as textfile；导入数据：/root/data/step2 files/load data local inpath */root/data/step2_fi1es/* into table

13、 my stu；仓Q建表my scorecreate table if not exists myscore(sid string comment 学生 id,courseid string comment 课程 id,score string comment 成绩)row format delimited fields terminated by Tstored as textfile；导入数据:/root/data/step2 fi les/load data local inpath /root/data/step2_f订es/ into table my score； 创建表my coursecreate table if not exists my course(courseid string comment 课程 id,coursename string comment 课程名称)row format delimited fields terminated bystored as textfile；导入数据:/root/data/step2 f i les/load data local inpath */i