千锋大数据学习提升系统离线数仓
1 打入业务数据
将 qf_exam20181010.sql 文件导入 Mysql
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rQDg4SH7-1607325243327)(C:\Users\Acer\Desktop\2.png)]
2 将业务数据导入hive表
2.1 init_qianfeng_ods.hql
Create Database `qianfeng_ods`;
use qianfeng_ods;
//创建答卷表
CREATE TABLE if not exists `qianfeng_ods`.`answer_paper` (
`id` int,
`exam_id` int,
`paper_id` int,
`examinee_id` int,
`examinee_name` string,
`examinee_num` string,
`class_id` int,
`class_name` string,
`start_date` string,
`exam_time` string,
`submit_time` string,
`objective_mark` int,
`subject_mark` int,
`subject_smart_mark` int,
`check_state` int,
`teacher_id` int,
`objective_answer_json` string,
`subject_answer_json` string,
`subject_check_json` string,
`objective_check_json` string,
`evaluation_opinions` string
)row format delimited
fields terminated by '\001';
//创建类表
CREATE TABLE `qianfeng_ods`.`category` (
`id` int,
`name` string ,
`outline_json` string ,
`subject_id` int ,
`subject_name` string ,
`remark` string ,
`del` int ,
`modifier_id` int ,
`modifier_name` string ,
`modify_time` string ,
`creator_id` int ,
`creator_name` string ,
`create_time` string
)row format delimited
fields terminated by '\001';
//创建考试表
CREATE TABLE `qianfeng_ods`.`exam` (
`id` int ,
`name` string ,
`paper_template_id` int ,
`subject_id` int ,
`subject_name` string ,
`limit_minute` int ,
`pass_score` int ,
`full_mark` int ,
`volume_num` int ,
`remark` string ,
`state` int ,
`del` int ,
`start_time` string ,
`modifier_id` int ,
`modifier_name` string ,
`modify_time` string ,
`creator_id` int ,
`creator_name` string ,
`create_time` string ,
`publish` int
)row format delimited
fields terminated by '\001';
//创建考试与班级关联表
CREATE TABLE `qianfeng_ods`.`exam_class_ref` (
`id` int ,
`exam_id` int ,
`class_id` int ,
`class_name` string
)row format delimited
fields terminated by '\001';
//创建试卷表
CREATE TABLE `qianfeng_ods`.`paper` (
`id` int ,
`exam_id` int ,
`is_objective` int ,
`is_subjective` int ,
`creator_id` int ,
`creator_name` string ,
`create_time` string
)row format delimited
fields terminated by '\001';
//创建试卷试题表
CREATE TABLE if not exists `qianfeng_ods`.`paper_question` (
`id` int,
`paper_id` int,
`question_id` int
)row format delimited
fields terminated by '\001';
//创建试卷模板表
CREATE TABLE `qianfeng_ods`.`paper_template` (
`id` int,
`name` string,
`subject_id` int,
`subject_name` string,
`state` int,
`total_mark` int,
`del` int,
`modifier_id` int,
`modifier_name` string,
`modify_time` string,
`creator_id` int,
`creator_name` string,
`create_time` string
)row format delimited
fields terminated by '\001';
//创建试卷模板题型组成各难度题数表
CREATE TABLE if not exists `qianfeng_ods`.`paper_template_category_ref` (
`id` int,
`paper_template_id` int,
`category_id` int
)row format delimited
fields terminated by '\001';
//创建试卷模板题型组成表
CREATE TABLE `qianfeng_ods`.`paper_template_part`
(
`id` int,
`paper_template_id` int,
`question_type_id` int,
`per_question_mark` int,
`sort` int,
`selected` int
)row format delimited
fields terminated by '\001';
//创建试卷模板题型组成各难度题数表
CREATE TABLE `qianfeng_ods`.`paper_template_part_question_number`
(
`id` int,
`paper_template_part_id` int,
`question_difficulty_id` int,
`question_number` int
)row format delimited
fields terminated by '\001';
//创建试题表
CREATE TABLE if not exists `qianfeng_ods`.`question`
(
`id` int,
`category_id` int,
`question_type_id` int,
`question_difficulty_id` int,
`state` int,
`content` string,
`right_answer` string,
`analyse` string,
`del` int,
`modifier_id` int,
`modifier_name` string,
`modify_time` string,
`creator_id` int,
`creator_name` string,
`create_time` string
)row format delimited
fields terminated by '\001';
//创建试题难度表
CREATE TABLE `qianfeng_ods`.`question_difficulty` (
`id` int,
`difficulty` int,
`name` string
)row format delimited
fields terminated by '\001';
//创建试题选项表
CREATE TABLE if not exists `qianfeng_ods`.`question_option` (
`id` int,
`content` string ,
`sort` int,
`question_id` int,
`is_right` int
)row format delimited
fields terminated by '\001';
//创建试题类型表
CREATE TABLE if not exists `qianfeng_ods`.`question_type` (
`id` int,
`type` int,
`name` string,
`is_objective` int
)row format delimited
fields terminated by '\001';
2.2创建脚本
vi init_qianfeng_ods.sh
hive -f /opt/hql/init_ods_sales_order.hql
2.2使用sqoop将业务数据导入hive表中
#!/bin/bash
hive -f /opt/hql/init_ods_qianphone.hql
//将数据导入类表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table category \
--hive-import \
--hive-table qianfeng_ods.category \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试题类型表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table question_type \
--hive-import \
--hive-table qianfeng_ods.question_type \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试题难度表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table question_ difficulty \
--hive-import \
--hive-table qianfeng_ods.question_ difficulty \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试题表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table question \
--hive-import \
--hive-table qianfeng_ods.question \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试题选项表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table question_option \
--hive-import \
--hive-table qianfeng_ods.question_option \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试卷模板题型组成表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table paper_template \
--hive-import \
--hive-table qianfeng_ods.paper_template \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试卷模板
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table paper_template_category_ref \
--hive-import \
--hive-table qianfeng_ods.paper_template_category_ref \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试卷模板与分类关联表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table paper_template_part \
--hive-import \
--hive-table qianfeng_ods.paper_template_part \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试卷模板题型组成各难度题数表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table paper_template_part_question_number \
--hive-import \
--hive-table qianfeng_ods.paper_template_part_question_number \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入考试表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table exam \
--hive-import \
--hive-table qianfeng_ods.exam \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入考试与班级关联表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table exam_class_ref \
--hive-import \
--hive-table qianfeng_ods.exam_class_ref \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试卷表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table paper \
--hive-import \
--hive-table qianfeng_ods.paper \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入试卷试题表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table paper_question \
--hive-import \
--hive-table qianfeng_ods.paper_question \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
//将数据导入答卷表
sqoop import \
--connect jdbc:mysql://192.168.10.131:3306/qianfeng \
--password 123456 \
--username root \
--table answer_paper \
--hive-import \
--hive-table qianfeng_ods.answer_paper \
--delete-target-dir \
--fields-terminated-by '\001' \
--num-mappers 1 \
--as-textfile
2.3 JSON 解析
public static String parseScores(String scores){
if (scores.equals("{}")){
return "";
}
String result="";
Map map = (Map)JSON.parse(scores);
Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
while (it.hasNext()) {
Map.Entry entry = it.next();
Map map1=(Map