2021-01-14课堂测试一

一、环境要求

sandbox-hdp 2.6.4 或同等版本自建的 Hadoop+Hive+Spark+HBase 开发环境。

二、提交结果要求

1.必须提交源码或对应分析语句，如不提交则不得分。
2.带有分析结果的功能，请分析结果的截图与代码一同提交。

三、数据描述

这是一份来自于某在线考试系统的学员答题批改日志，日志中记录了日志生成时间,题目
难度系数，题目所属的知识点 ID，做题的学生 ID，题目 ID 以及作答批改结果。日志的
结构如下：

四、功能要求

1.数据准备（10 分）
请在 HDFS 中创建目录/app/data/exam，并将 answer_question.log 传到该目录。

hdfs dfs -mkdir -p /app/data/exam200707

hdfs dfs -put /software/answer_question.log /app/data/exam

2.在 Spark-Shell 中，加载 HDFS 文件系统 answer_question.log 文件，并使用 RDD 完成以下分析，也可使用 Spark 的其他方法完成数据分析。（20 分）
①提取日志中的知识点 ID，学生 ID，题目 ID，作答结果 4 个字段的值

val lines = sc.textFile("hdfs://192.168.247.201:9000/app/data/exam/answer_question.log")

// 按空格拆分方法
val rdd = lines.map(_.split(" "))
    .map(x => (x(9).split("_"),x(10).split(",")))
    .map(x => (x._1(1),x._1(2),x._1(3).substring(0,x._1(3).length-1),x._2(0)))
	
// 正则表达式拆分方法
val regex = """.*se\d_(\d+)_(\d+)_(\d+)r (.*),.*""".r
val tupleRdd = lines.map(x=>x match {
  case regex(str0,str1,str2,str3) => (str0,str1,str2,str3)
})

// 按下划线拆分方法
val tupleRdd = lines.map(_.replace("r ","_").replace(",","_"))
	.map(_.split("_"))
	.map(x=>(x(1),x(2),x(3),x(4)))

②将提取后的知识点 ID，学生 ID，题目 ID，作答结果字段的值以文件的形式保存到 HDFS

val rdd2 = rdd.map(x => x.productIterator.mkString("\t")).saveAsTextFile("hdfs://192.168.247.201:9000/app/data/result")

3.创建 HBase 数据表（10 分）
在 HBase 中创建命名空间（namespace）exam，在该命名空间下创建 analysis 表，使用学生 ID 作为 RowKey，该表下有 2 个列族 accuracy、question。
accuracy 列族用于保存学员答题正确率统计数据（ 总 分 accuracy:total_score ， 答 题 的 试 题 数accuracy:question_count，正确率 accuracy:accuracy）；
question 列族用于分类保存学员正确，错误和半对的题目id （正确 question:right，错误 question:error，半对question:half）

（1）创建命名空间（namespace）exam
create_namespace 'exam'

（2）创建 analysis 表，该表下有 2 个列族 accuracy、question
create 'exam:analysis','accuracy','question'

4、请在Hive中创建数据库exam，在该数据库中创建外部表ex_exam_record指向/app/data/result下Spark处理后的日志数据;
创建外部表 ex_exam_anlysis映射至HBase中的analysis表的 accuracy 列族;创建外部表 ex_exam_question映射至HBase中的analysis表的 question 列族（20 分）

（1）创建数据库exam
create database exam;

（2）创建外部表ex_exam_record
create external table if not exists ex_exam_record(
topic_id String,
student_id String, 
question_id String,
score float
)
row format delimited
fields terminated by '\t'
stored as textfile
location '/app/data/result'

（3）创建外部表 ex_exam_anlysis
create external table if not exists ex_exam_anlysis(
student_id String,
total_score float,
question_count int,
accuracy float
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties('hbase.columns.mapping'=':key,accuracy:total_score,accuracy:question_count,accuracy:accuracy')
tblproperties('hbase.table.name'='exam:analysis');

（4）创建外部表 ex_exam_question
create external table if not exists ex_exam_question(
student_id String,
right String,
half String,
error String
)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties('hbase.columns.mapping'=':key,question:right,question:half,question:error')
tblproperties('hbase.table.name'='exam:analysis');

5.使用 ex_exam_record 表中的数据统计每个学员总分、答题的试题数和正确率，并保存到 ex_exam_anlysis 表中，其中正确率的计算方法如下：
正确率=总分/答题的试题数
（20 分）

insert into table ex_exam_anlysis
select student_id,sum(score) as total_score, 
count(question_id) as question_count, sum(score)/count(1) as accuracy 
from ex_exam_record
group by student_id


-- 查询 ex_exam_anlysis 表中是否有数据
select * from ex_exam_anlysis;


-- 在 hbase 中查询数据(列簇 accuracy )
scan 'exam:analysis',{COLUMNS=>'accuracy'}

6.使用 ex_exam_record 表中的数据统计每个作对，做错，半对的题目列表。
①题目 id 以逗号分割，并保存到 ex_exam_question 表中。（10 分）

with t as 
(select student_id, score, 
concat_ws(",",collect_set(question_id)) as question_id 
from ex_exam_record 
group by student_id,score),
a1 as (select student_id, question_id from t where score=1),
a2 as (select student_id, question_id from t where score=0.5),
a3 as (select student_id, question_id from t where score=0)
insert into ex_exam_question 
select a1.student_id, a1.question_id right, a2.question_id half, a3.question_id error 
from a1 
join a2 on a1.student_id = a2.student_id 
join a3 on a1.student_id = a3.student_id;

---- case when的方法 ----
insert into ex_exam_question 
select
student_id,
concat_ws('',collect_list(t.right)) right,
concat_ws('',collect_list(t.half)) half,
concat_ws('',collect_list(t.error)) error
from 
(
select
student_id,
case when score=1.0 then concat_ws(",",collect_list(question_id)) else null end right,
case when score=0.5 then concat_ws(",",collect_list(question_id)) else null end half,
case when score=0.0 then concat_ws(",",collect_list(question_id)) else null end error
from ex_exam_record
group by student_id,score) t
group by student_id

---- case when 简略版 ----
select student_id,
concat_ws(",",collect_list(case when score=1 then question_id else null end)) right,
concat_ws(",",collect_list(case when score=0.5 then question_id else null end)) half,
concat_ws(",",collect_list(case when score=0 then question_id else null end)) error 
from ex_exam_record group by student_id,score

---- 最简化方法 ----
insert into ex_exam_question 
select 
student_id,
concat_ws(",",collect_set(if(score=1,question_id,NULL))),
concat_ws(",",collect_set(if(score=0.5,question_id,NULL))),
concat_ws(",",collect_set(if(score=0,question_id,NULL)))
from ex_exam_record
group by student_id

②完成统计后，在 HBase Shell 中遍历 exam:analysis 表并只显示 question 列族中的数据，（10 分）

scan 'exam:analysis',{COLUMNS=>'question'}