answer_question.log 在线考试系统的学员答题批改日志数据字段分析

[root@linux01 exam]# hdfs dfs -mkdir -p /app/data/exam2101

[root@linux01 exam]# hdfs dfs -put ./answer_question.log /app/data/exam2101

scala> sc.textFile("/app/data/exam2101")

scala> res1.map(x=>x.split("\\s+")).map(x=>(x(8),x(9))).collect.foreach(println)

scala> res1.map(x=>x.split("\\s+")).map(x=>(x(8),x(9))).map(x=>x._1.split("_").toList).collect.foreach(println)

scala> res1.map(x=>x.split("\\s+")).map(x=>(x(8),x(9))).map(x=>{var y=x._1.split("_");(y(1),y(2),y(3).substring(0,y(3).length-1))}).collect.foreach(println)

//0,resp (0)
scala> res1.map(x=>x.split("\\s+")).map(x=>(x(8),x(9))).map(x=>{var y=x._1.split("_");(y(1),y(2),y(3).substring(0,y(3).length-1),x._2.split(",")(0))}).collect.foreach(println)

res1.map(x=>x.split("_")).map(x=>(x(1),x(2),x(3))).map(x=>{var y=x._3.split("r");(x._1,x._2,y(0),y(1).trim.substring(0,y(1).trim.length-1))}).collect.foreach(println)


scala> res1.map(x=>x.split("\\s+")).map(x=>(x(8),x(9))).map(x=>{var y=x._1.split("_");(y(1),y(2),y(3).substring(0,y(3).length-1),x._2.split(",")(0))}).saveAsTextFile("/app/data/result2101")


scala> res1.map(x=>x.split("\\s+")).map(x=>(x(8),x(9))).map(x=>{var y=x._1.split("_");(y(1),y(2),y(3).substring(0,y(3).length-1),x._2.split(",")(0)).productIterator.mkString("\t")}).collect.foreach(println) 

res1.map(x=>x.split("_")).map(x=>(x(1),x(2),x(3))).map(x=>{var y=x._3.split("r");(x._1,x._2,y(0),y(1).trim.substring(0,y(1).trim.length-1)).productIterator.mkString("\t")}).saveAsTextFile("/app/data/result2101")

hbase(main):005:0> create 'exam:analysis','accuracy','question'

use exam;

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/result2101";

select count(*) from ex_exam_record;

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");

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");

insert into ex_exam_anlysis
select student_id,sum(score) total_score,count(question_id) question_count,
       cast(sum(score)/count(question_id) as decimal(5,2)) accuracy
from ex_exam_record group by student_id;

select * from ex_exam_anlysis;

select * from ex_exam_record;


select student_id,concat_ws(",",collect_set(question_id)) as right
from ex_exam_record where score=1 group by student_id;

insert into ex_exam_question
select student_id,
       concat_ws(",",collect_set(case when score=1 then question_id end )) as right,
        concat_ws(",",collect_set(case when score=0.5 then question_id end )) as half,
       concat_ws(",",collect_set(case when score=0 then question_id end )) as error
from ex_exam_record  group by student_id;

select student_id,score,
       concat_ws(",",collect_set(question_id)) as questions
from ex_exam_record group by student_id,score;

select * from ex_exam_question;