虚拟机weware中ubuntu使用hadoop在hive中完成学生表的关联查询。

1、分析数据：

//创建文件夹 hdfs dfs -mkdir /hadoopclass/studentcount hdfs dfs -mkdir /hadoopclass/studentcount/tbcourse hdfs dfs -mkdir /hadoopclass/studentcount/tbcourseclass hdfs dfs -mkdir /hadoopclass/studentcount/tbelectives hdfs dfs -mkdir /hadoopclass/studentcount/tbmajorclass hdfs dfs -mkdir /hadoopclass/studentcount/tbstudent hdfs dfs -mkdir /hadoopclass/studentcount/tbteacher //将文件导入文件夹 hdfs dfs -copyFromLocal ~/student1/tbcourse.csv /hadoopclass/studentcount/tbcourse

hdfs dfs -copyFromLocal ~/student1/tbcourseclass.csv /hadoopclass/studentcount/tbcourseclass

hdfs dfs -copyFromLocal ~/student1/tbelectives.csv /hadoopclass/studentcount/tbelectives

hdfs dfs -copyFromLocal ~/student1/tbmajorclass.csv /hadoopclass/studentcount/tbmajorclass

hdfs dfs -copyFromLocal ~/student1/tbstudent.csv /hadoopclass/studentcount/tbstudent

hdfs dfs -copyFromLocal ~/student1/tbteacher.csv /hadoopclass/studentcount/tbteacher //删除文件 hadoop fs -rm /hadoopclass/studentcount/tbcourse/tbcourse.csv

a、男女生比例

//创建表 create external table tbstudent ( ID varchar(10), NAME string, GENDER string, BIRTHDATE string, PHONENUMBER varchar(11), MAJORCLASS string ) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' stored as textfile location'/hadoopclass/studentcount/tbstudent';

//男生除以女生 SELECT t1.male/t2.female AS genderpercent from (select count(1) as male from tbstudent where gender ='男' ) t1 , (select count(1) as female from tbstudent where gender ='女' ) t2 WHERE 1=1;

b、学生整体及格率

//创建表 create external table tbelectives ( COURSECLASSCODE varchar(10), STUDENTID varchar(10), SCORE string ) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' stored as textfile location'/hadoopclass/studentcount/tbelectives';

//及格除以总的 SELECT t1.a100/t2.a AS passrate FROM (SELECT COUNT(score) AS a FROM tbelectives where score >= "60") t1 ,(SELECT COUNT() AS a FROM tbelectives) t2 ;

SELECT COUNT(score) FROM tb_electives WHERE score >= 60;

SELECT COUNT(score) FROM tbelectives where score >= "60"; SELECT COUNT(*) AS b FROM tbelectives;

c、每门课程的平均分，要求显示出课程的中文名字

//创建表 create external table tbcourseclass ( CODE varchar(12), SEMESTER varchar(10), TEACHERID varchar(10), COURSECODE varchar(10) ) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' stored as textfile location'/hadoopclass/studentcount/tbcourseclass';

create external table tbcourse ( CODE varchar(12), NAME string, PERIOD varchar(10), CREDIT varchar(2) ) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' stored as textfile location'/hadoopclass/studentcount/tbcourse';

SELECT tbcourse.name, AVG(tbelectives.score) FROM tbcourse, tbcourseclass, tbelectives WHERE tbcourse.code = tbcourseclass.coursecode AND tbcourseclass.code = tbelectives.courseclasscode GROUP BY tbcourse.name;

d、有2门课不及格的学生（重修的学生）

SELECT tbstudent.name,t1.count1 FROM tbstudent,(select studentid,count(*) as count1 from tbelectives where score id) t1 WHERE tbstudent.id = t1.student_id and t1.count1 >= 2;

create temporary table onpass as select studentid,count(*) as count1 from tbelectives where score <= 60 group by studentid; select tbstudent.name,onpass.count1 from tbstudent,onpass where tbstudent.id = onpass.studentid and count1>= 2;

e、课程的及格率，同时要显示老师的名字（绩效考核）

//非严格模式set hive.mapred.mode=nostrict;

create external table tbteacher ( ID varchar(12), NAME string, gender string, title string ) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' stored as textfile location'/hadoopclass/studentcount/tb_teacher';

create temporary table pass1 as select t1.courseclasscode,t2.teacherid as id1,avg(t1.score) as avg1 from tbelectives t1,tbcourseclass t2 where t1.courseclasscode = t2.code group by t1.courseclasscode,t2.teacherid order by t2.teacherid;

select t1.courseclasscode,t1.id1,t2.name,t1.avg1 from pass1 t1,tb_teacher t2 where t1.id1=t2.id;

select t1.code1,tbteacher.name,tbcourse.name,t1.avg1 from tbteacher,tbcourse, (select t1.courseclasscode as code1,avg(t1.score) as avg1 from tbelectives t1 group by t1.courseclasscode having AVG(t1.score) >= 60 ) t1,tbcourseclass t2 where tbteacher.id = t2.teacherid and t2.coursecode=tb_course.code;