项目目标：
1.汇总每个学生每学年总分
2.汇总每学年每学科平均分

一、初始脚本

1.1 MySQL初始脚本

-- 创建数据库student_bigdata
CREATE DATABASE IF NOT EXISTS student_bigdata;

-- 切换数据库至student_bigdata
USE student_bigdata;

-- 创建学生信息表
CREATE TABLE IF NOT EXISTS student (
    student_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    date_of_birth DATE NOT NULL,
    PRIMARY KEY (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建学科信息表
CREATE TABLE IF NOT EXISTS subject (
    subject_id INT NOT NULL AUTO_INCREMENT,
    subject_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建学年信息表
CREATE TABLE IF NOT EXISTS academic_year (
    year_id INT NOT NULL AUTO_INCREMENT,
    year_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (year_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建学生成绩信息表
CREATE TABLE IF NOT EXISTS student_grade (
	grade_id INT NOT NULL AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    year_id INT NOT NULL,
    grade DECIMAL(5,2) NOT NULL,
		PRIMARY KEY (grade_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入学生信息
INSERT INTO student (name, gender, date_of_birth) VALUES
('张三', '男', '2000-01-01'),
('李四', '女', '2001-02-02'),
('王五', '男', '1999-03-03'),
('赵六', '女', '2000-04-04'),
('钱七', '男', '1998-05-05'),
('孙八', '女', '2002-06-06'),
('周九', '男', '1997-07-07'),
('吴十', '女', '2003-08-08'),
('郑十一', '男', '1996-09-09');

-- 插入学科信息
INSERT INTO subject (subject_name) VALUES
('数学'),
('语文'),
('英语'),
('物理'),
('化学'),
('历史'),
('地理'),
('生物'),
('计算机');

-- 插入学年信息
INSERT INTO academic_year (year_name) VALUES
('2021-2022'),
('2022-2023'),
('2023-2024');


-- 插入学生成绩信息
INSERT INTO student_grade (student_id, subject_id, year_id, grade) VALUES
-- 张三
(1, 1, 1, 95.5), (1, 2, 1, 85.0), (1, 3, 1, 90.5), (1, 4, 1, 88.0), (1, 5, 1, 92.5), (1, 6, 1, 89.0), (1, 7, 1, 87.5), (1, 8, 1, 90.0), (1, 9, 1, 94.0),
(1, 1, 2, 96.0), (1, 2, 2, 86.0), (1, 3, 2, 91.0), (1, 4, 2, 89.0), (1, 5, 2, 93.0), (1, 6, 2, 90.0), (1, 7, 2, 88.0), (1, 8, 2, 91.0), (1, 9, 2, 95.0),
(1, 1, 3, 97.0), (1, 2, 3, 87.0), (1, 3, 3, 92.0), (1, 4, 3, 90.0), (1, 5, 3, 94.0), (1, 6, 3, 91.0), (1, 7, 3, 89.0), (1, 8, 3, 92.0), (1, 9, 3, 96.0),

-- 李四
(2, 1, 1, 88.0), (2, 2, 1, 90.5), (2, 3, 1, 87.5), (2, 4, 1, 85.0), (2, 5, 1, 86.5), (2, 6, 1, 84.0), (2, 7, 1, 83.0), (2, 8, 1, 85.5), (2, 9, 1, 88.0),
(2, 1, 2, 89.0), (2, 2, 2, 91.0), (2, 3, 2, 88.0), (2, 4, 2, 86.0), (2, 5, 2, 87.0), (2, 6, 2, 85.0), (2, 7, 2, 84.0), (2, 8, 2, 86.5), (2, 9, 2, 89.0),
(2, 1, 3, 90.0), (2, 2, 3, 92.0), (2, 3, 3, 89.0), (2, 4, 3, 87.0), (2, 5, 3, 88.0), (2, 6, 3, 86.0), (2, 7, 3, 85.0), (2, 8, 3, 87.5), (2, 9, 3, 90.0),

-- 王五
(3, 1, 1, 91.0), (3, 2, 1, 92.0), (3, 3, 1, 93.5), (3, 4, 1, 90.0), (3, 5, 1, 94.0), (3, 6, 1, 92.0), (3, 7, 1, 91.0), (3, 8, 1, 93.0), (3, 9, 1, 95.0),
(3, 1, 2, 92.0), (3, 2, 2, 93.0), (3, 3, 2, 94.0), (3, 4, 2, 91.0), (3, 5, 2, 95.0), (3, 6, 2, 93.0), (3, 7, 2, 92.0), (3, 8, 2, 94.0), (3, 9, 2, 96.0),
(3, 1, 3, 93.0), (3, 2, 3, 94.0), (3, 3, 3, 95.0), (3, 4, 3, 92.0), (3, 5, 3, 96.0), (3, 6, 3, 94.0), (3, 7, 3, 93.0), (3, 8, 3, 95.0), (3, 9, 3, 97.0),

-- 赵六
(4, 1, 1, 85.5), (4, 2, 1, 86.5), (4, 3, 1, 87.0), (4, 4, 1, 82.5), (4, 5, 1, 84.0), (4, 6, 1, 83.0), (4, 7, 1, 81.0), (4, 8, 1, 84.5), (4, 9, 1, 86.0),
(4, 1, 2, 86.0), (4, 2, 2, 87.0), (4, 3, 2, 88.0), (4, 4, 2, 83.0), (4, 5, 2, 85.0), (4, 6, 2, 84.0), (4, 7, 2, 82.0), (4, 8, 2, 85.5), (4, 9, 2, 87.0),
(4, 1, 3, 87.0), (4, 2, 3, 88.0), (4, 3, 3, 89.0), (4, 4, 3, 84.0), (4, 5, 3, 86.0), (4, 6, 3, 85.0), (4, 7, 3, 83.0), (4, 8, 3, 86.5), (4, 9, 3, 88.0),

-- 钱七
(5, 1, 1, 93.5), (5, 2, 1, 94.0), (5, 3, 1, 95.0), (5, 4, 1, 91.5), (5, 5, 1, 92.0), (5, 6, 1, 90.0), (5, 7, 1, 89.0), (5, 8, 1, 91.0), (5, 9, 1, 94.5),
(5, 1, 2, 94.0), (5, 2, 2, 95.0), (5, 3, 2, 96.0), (5, 4, 2, 92.0), (5, 5, 2, 93.0), (5, 6, 2, 91.0), (5, 7, 2, 90.0), (5, 8, 2, 92.0), (5, 9, 2, 95.5),
(5, 1, 3, 95.0), (5, 2, 3, 96.0), (5, 3, 3, 97.0), (5, 4, 3, 93.0), (5, 5, 3, 94.0), (5, 6, 3, 92.0), (5, 7, 3, 91.0), (5, 8, 3, 93.0), (5, 9, 3, 96.0),

-- 孙八
(6, 1, 1, 87.0), (6, 2, 1, 88.5), (6, 3, 1, 89.5), (6, 4, 1, 85.5), (6, 5, 1, 86.0), (6, 6, 1, 84.0), (6, 7, 1, 82.0), (6, 8, 1, 85.0), (6, 9, 1, 88.5),
(6, 1, 2, 88.0), (6, 2, 2, 89.0), (6, 3, 2, 90.0), (6, 4, 2, 86.0), (6, 5, 2, 87.0), (6, 6, 2, 85.0), (6, 7, 2, 83.0), (6, 8, 2, 86.0), (6, 9, 2, 89.5),
(6, 1, 3, 89.0), (6, 2, 3, 90.0), (6, 3, 3, 91.0), (6, 4, 3, 87.0), (6, 5, 3, 88.0), (6, 6, 3, 86.0), (6, 7, 3, 84.0), (6, 8, 3, 87.0), (6, 9, 3, 90.0),

-- 周九
(7, 1, 1, 84.0), (7, 2, 1, 85.0), (7, 3, 1, 83.5), (7, 4, 1, 80.0), (7, 5, 1, 82.0), (7, 6, 1, 81.0), (7, 7, 1, 79.0), (7, 8, 1, 81.5), (7, 9, 1, 84.0),
(7, 1, 2, 85.0), (7, 2, 2, 86.0), (7, 3, 2, 84.5), (7, 4, 2, 81.0), (7, 5, 2, 83.0), (7, 6, 2, 82.0), (7, 7, 2, 80.0), (7, 8, 2, 82.5), (7, 9, 2, 85.0),
(7, 1, 3, 86.0), (7, 2, 3, 87.0), (7, 3, 3, 85.5), (7, 4, 3, 82.0), (7, 5, 3, 84.0), (7, 6, 3, 83.0), (7, 7, 3, 81.0), (7, 8, 3, 83.5), (7, 9, 3, 86.0),

-- 吴十
(8, 1, 1, 90.5), (8, 2, 1, 91.0), (8, 3, 1, 89.0), (8, 4, 1, 86.5), (8, 5, 1, 88.0), (8, 6, 1, 87.0), (8, 7, 1, 85.0), (8, 8, 1, 88.5), (8, 9, 1, 91.0),
(8, 1, 2, 91.5), (8, 2, 2, 92.0), (8, 3, 2, 90.0), (8, 4, 2, 87.0), (8, 5, 2, 89.0), (8, 6, 2, 88.0), (8, 7, 2, 86.0), (8, 8, 2, 89.5), (8, 9, 2, 92.0),
(8, 1, 3, 92.5), (8, 2, 3, 93.0), (8, 3, 3, 91.0), (8, 4, 3, 88.0), (8, 5, 3, 90.0), (8, 6, 3, 89.0), (8, 7, 3, 87.0), (8, 8, 3, 90.5), (8, 9, 3, 93.0),

-- 郑十一
(9, 1, 1, 92.0), (9, 2, 1, 93.0), (9, 3, 1, 91.5), (9, 4, 1, 89.0), (9, 5, 1, 90.0), (9, 6, 1, 88.0), (9, 7, 1, 87.0), (9, 8, 1, 89.5), (9, 9, 1, 92.0),
(9, 1, 2, 93.0), (9, 2, 2, 94.0), (9, 3, 2, 92.5), (9, 4, 2, 90.0), (9, 5, 2, 91.0), (9, 6, 2, 89.0), (9, 7, 2, 88.0), (9, 8, 2, 90.5), (9, 9, 2, 93.0),
(9, 1, 3, 94.0), (9, 2, 3, 95.0), (9, 3, 3, 93.5), (9, 4, 3, 91.0), (9, 5, 3, 92.0), (9, 6, 3, 90.0), (9, 7, 3, 89.0), (9, 8, 3, 91.5), (9, 9, 3, 94.0);

COMMIT;

-- 设置sql_mode
set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

-- 创建数据库score_result，如果不存在则创建
CREATE DATABASE IF NOT EXISTS score_result;

-- 切换数据库至score_result
USE score_result;

-- 创建学生每学年总分汇总表
CREATE TABLE IF NOT EXISTS student_total_scores (
    student_id INT NOT NULL,
    year_name VARCHAR(100) NOT NULL,
    total_score DECIMAL(10,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建每学年每学科平均分汇总表
CREATE TABLE IF NOT EXISTS subject_avg_scores (
    year_name VARCHAR(100) NOT NULL,
    subject_name VARCHAR(100) NOT NULL,
    avg_score DECIMAL(5,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这里插入图片描述

1.2 Hive初始脚本

-- 创建数据库student_bigdata_hive，如果不存在则创建
CREATE DATABASE IF NOT EXISTS student_bigdata_hive;

-- 切换数据库至student_bigdata_hive
USE student_bigdata_hive;

-- 创建学生表
CREATE TABLE IF NOT EXISTS student_bigdata_hive.student (
    student_id INT COMMENT '学生ID',
    name STRING COMMENT '姓名',
    gender STRING COMMENT '性别',
    date_of_birth DATE COMMENT '出生日期'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;


-- 创建学科表
CREATE TABLE IF NOT EXISTS student_bigdata_hive.subject (
    subject_id INT COMMENT '学科ID',
    subject_name STRING COMMENT '学科名称'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;


-- 创建学年表
CREATE TABLE IF NOT EXISTS student_bigdata_hive.academic_year (
    year_id INT COMMENT '学年ID',
    year_name STRING COMMENT '学年名称'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;


-- 创建学生成绩表
CREATE TABLE IF NOT EXISTS student_bigdata_hive.student_grade (
    grade_id INT COMMENT '成绩ID',
    student_id INT COMMENT '学生ID',
    subject_id INT COMMENT '学科ID',
    year_id INT COMMENT '学年ID',
    grade DECIMAL(5,2) COMMENT '成绩'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

在这里插入图片描述

二、数据传输

进入sqoop目录bin下，依次运行sqoop从MySQL中向hive导入数据

cd /opt/softs/sqoop1.4.6/bin

sqoop import \
--connect jdbc:mysql://bigdata04:3306/student_bigdata \
--username root \
--password cxy20030419 \
--table student \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "," \
--hive-overwrite \
--hive-table student_bigdata_hive.ods_student


sqoop import \
--connect jdbc:mysql://bigdata04:3306/student_bigdata \
--username root \
--password cxy20030419 \
--table academic_year \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "," \
--hive-overwrite \
--hive-table student_bigdata_hive.ods_academic_year

sqoop import \
--connect jdbc:mysql://bigdata04:3306/student_bigdata \
--username root \
--password cxy20030419 \
--table student_grade \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "," \
--hive-overwrite \
--hive-table student_bigdata_hive.ods_student_grade

sqoop import \
--connect jdbc:mysql://bigdata04:3306/student_bigdata \
--username root \
--password cxy20030419 \
--table subject \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "," \
--hive-overwrite \
--hive-table student_bigdata_hive.ods_subject

检查数据表数据传输是否成功：
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

三、生成数据汇总表

生成一张dwd层数据表，用来显示（学生学号，学生姓名，课程名，学年，成绩）

create table if not exists student_bigdata_hive.dwd_student_subject_year_grade
as
	select
		t1.student_id,
		name as student_name,
		subject_name,
		year_name,
		grade
	from
	(
		select
			student_id,
			name
		from student_bigdata_hive.ods_student
	) t1
	left join student_bigdata_hive.ods_student_grade t2
	on t1.student_id = t2.student_id
	left join student_bigdata_hive.ods_subject t3
	on t2.subject_id = t3.subject_id
	left join student_bigdata_hive.ods_academic_year t4
	on t2.year_id = t4.year_id;

执行sql文件之后，查看数据表中的数据。

在这里插入图片描述

在这里插入图片描述
计算每个学生每个学年的总成绩

create table if not exists student_bigdata_hive.dws_student_year_score_total
as 
	select
		student_id,
		year_name,
		sum(grade) as year_total_grade
	from student_bigdata_hive.dwd_student_subject_year_grade
	group by student_id,year_name;

执行sql文件之后查看数据表中的数据：

在这里插入图片描述
在这里插入图片描述

计算每个学年每个学科的平均成绩

create table if not exists student_bigdata_hive.dws_year_subject_grade_average
as 
	select
		year_name,
		subject_name,
		avg(grade) as year_subject_average
	from student_bigdata_hive.dwd_student_subject_year_grade
	group by subject_name,year_name;

运行sql之后，查看数据库中的数据。
在这里插入图片描述

四、数据报表传输到MySQL

依次执行以下shell命令

cd /opt/softs/sqoop1.4.6/bin

sqoop export \
--connect jdbc:mysql://bigdata04:3306/score_result \
--username root \
--password cxy20030419 \
--table  student_total_scores \
--num-mappers 1 \
--export-dir /user/hive/warehouse/student_bigdata_hive.db/dws_student_year_score_total \
--input-fields-terminated-by  "\001"


sqoop export \
--connect jdbc:mysql://bigdata04:3306/score_result \
--username root \
--password cxy20030419 \
--table subject_avg_scores \
--num-mappers 1 \
--export-dir /user/hive/warehouse/student_bigdata_hive.db/dws_year_subject_grade_average \
--input-fields-terminated-by  "\001"

在这里插入图片描述
在这里插入图片描述

五、使用FineReport制作数据报表

使用finereport连接MySQL里的数据库
在这里插入图片描述
在这里插入图片描述
新建聚合报表，进行数据报表绘制
在这里插入图片描述

# 数据仓库 # hive # 大数据 # mysql
Logo
乐奇 Rokid 开放社区

更多推荐

AI 日报（2026年9月24日）

2026年9月24日，AI技术与应用呈现多元化演进态势。硬件与端侧方面，Meta在Connect大会发布首款轻量化VR眼镜与无摄像头智能眼镜，全面接入Muse智能体；谷歌上线Gemini 3.8 Flash TTS可定制语音模型，并在DeepMind推动私密计算与服务端内存融合。行业与前沿科研领域，Anthropic湿实验实验室利用Claude发现CRISPR样新型酶系统，并推出一站式Claude

avatar 乐奇 Rokid 开放社区
cover

面向逻辑思维的程序设计方法——人形机器人最佳训练场景

avatar 乐奇 Rokid 开放社区

AI行业日报｜2026-09-25：5个热点事件

本期行业动态聚焦于端侧算力重构、自主智能体工作流、开发工具经济模型、网络隔离架构设计与公共部门合规审查。以下结合一线技术场景进行结构化梳理，供 AI 研究员与软件工程团队参考。 PrismML 正将其轻量级语言模型适配至搭载高通处理器的智能眼镜终端 1 。该平台的核心战略指向开源权重模型的本地化运行，旨在最大化挖掘穿戴设备现有算力的利用效率 1 。对于嵌入式与边缘计算开发者而言，此类微型模型向低功

avatar 乐奇 Rokid 开放社区