AI 助理
备案控制台
开发者社区 大数据 文章 正文

学生选课数据库分析（Hadoop实验）

2022-07-05 188
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 学生选课数据库分析（Hadoop实验）

计算机二级全套资源，点击下载，必过考试的神器！！！！


前面我们介绍了Hadoop环境下，分析淘宝大数据案例的过程及方法，根据分析效果还不错，实验效果也体现了大数据的数据集大的一个特点。本次实验我们假设了一个实际场景：假设现在有一个省份的学生选课数据需要你去分析，那么在数据集复杂和庞大的面前，我们的Hadoop是否还实用，这里所说的数据集复杂是因为有多个数据表需要你去采集，分析也需要进行多表连接。

image.png


对Hadoop里面的hive的认识，我觉得没有MySQL智能和方便，那么为什么我们还要去使用Hadoop里面的hive呢，因为大数据集，后面我们会引入spark这个引擎，会极大地方便我们开始新的旅途！


项目简介

假设一个数据集十分庞大的学生选课数据集，当然我们这里只是模拟了一个数据集，便于我们使用，我们在Python环境下面产生一些虚拟数据集，有四个表分别是学生表，选课表，课程表，课程_班级表，当然现实生活中真的需要你去分析，那么也至少是千万级的数据集。


分析数据：

a、男女生比例

b、及格率

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

d、有2门课不及格的学生

e、在mysql中创建结果表，把结果用sqoop写入到表中，并显示结果。

2020121816221911.png

思路其实之前类似：

20201218162234735.png

一些基本的操作和配置在之前的淘宝案例里面我们都有介绍，这里我就不做过多的赘述了，有需要的小伙伴请移步到上一篇文章


需要注意的是，之前我们发现导入导出进行查询的时候，显示中文是？？？本次我们修改了一些数据参数，现在可以显示正常了操作步骤如下：


在useSSL=false后面添加下面参数即可：

&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF-8

考虑到有的人容易打错，所以只需要将下面的代码全选覆盖之前的也可以

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
   <name>javax.jdo.option.ConnectionURL</name>
   <value>jdbc:mysql://localhost:3306/hive?useSSL=false&amp;allowPublicKeyRetrieval=true&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
</property>
<property>
   <name>javax.jdo.option.ConnectionDriverName</name>
   <value>com.mysql.jdbc.Driver</value>
</property>
<property>
   <name>javax.jdo.option.ConnectionUserName</name>
   <value>root</value>
</property>
<property>
   <name>javax.jdo.option.ConnectionPassword</name>
   <value>root</value>
</property>
<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>
<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
</property>
<property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
</property>
<property>
    <name>hive.compactor.worker.threads</name>
    <value>1</value>
    <!--这里的线程数必须大于0 :理想状态和分桶数一致-->
</property>
<property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
</property>
</configuration>

创建文件夹，便于后续操作（和之前的一样）

mkdir -p /home/hadoop/xsxk/data
mkdir -p /home/hadoop/xsxk/tmp/point

创建四个配置文件，帮助我们导入到hive里面（flume组件配置）

vi tb_course.properties
#定义agent名， source、channel、sink的名称
agent3.sources = source3
agent3.channels = channel3
agent3.sinks = sink3
#具体定义source
agent3.sources.source3.type = spooldir
agent3.sources.source3.spoolDir = /home/hadoop/xsxk/data
agent3.sources.source3.fileHeader=false
#设置channel类型为磁盘
agent3.channels.channel3.type = file
#file channle checkpoint文件的路径
agent3.channels.channel3.checkpointDir=/home/hadoop/xsxk/tmp/point
# file channel data文件的路径
agent3.channels.channel3.dataDirs=/home/hadoop/xsxk/tmp
#具体定义sink
agent3.sinks.sink3.type = hive
agent3.sinks.sink3.hive.metastore = thrift://hadoop:9083
agent3.sinks.sink3.hive.database = xsxk
agent3.sinks.sink3.hive.table = tb_course
agent3.sinks.sink3.serializer = DELIMITED
agent3.sinks.sink3.serializer.delimiter = ","
agent3.sinks.sink3.serializer.serdeSeparator = ','
agent3.sinks.sink3.serializer.fieldnames = code,name,period,credit
agent3.sinks.sink3.batchSize = 90
#组装source、channel、sink
agent3.sources.source3.channels = channel3
agent3.sinks.sink3.channel = channel3
vi tb_course_class.properties
#定义agent名， source、channel、sink的名称
agent3.sources = source3
agent3.channels = channel3
agent3.sinks = sink3
#具体定义source
agent3.sources.source3.type = spooldir
agent3.sources.source3.spoolDir = /home/hadoop/xsxk/data
agent3.sources.source3.fileHeader=false
#设置channel类型为磁盘
agent3.channels.channel3.type = file
#file channle checkpoint文件的路径
agent3.channels.channel3.checkpointDir=/home/hadoop/xsxk/tmp/point
# file channel data文件的路径
agent3.channels.channel3.dataDirs=/home/hadoop/xsxk/tmp
#具体定义sink
agent3.sinks.sink3.type = hive
agent3.sinks.sink3.hive.metastore = thrift://hadoop:9083
agent3.sinks.sink3.hive.database = xsxk
agent3.sinks.sink3.hive.table = tb_course_class
agent3.sinks.sink3.serializer = DELIMITED
agent3.sinks.sink3.serializer.delimiter = ","
agent3.sinks.sink3.serializer.serdeSeparator = ','
agent3.sinks.sink3.serializer.fieldnames = code,semester,teacher_id,course_code
agent3.sinks.sink3.batchSize = 90
#组装source、channel、sink
agent3.sources.source3.channels = channel3
agent3.sinks.sink3.channel = channel3
vi tb_student.properties
#定义agent名， source、channel、sink的名称
agent3.sources = source3
agent3.channels = channel3
agent3.sinks = sink3
#具体定义source
agent3.sources.source3.type = spooldir
agent3.sources.source3.spoolDir = /home/hadoop/xsxk/data
agent3.sources.source3.fileHeader=false
#设置channel类型为磁盘
agent3.channels.channel3.type = file
#file channle checkpoint文件的路径
agent3.channels.channel3.checkpointDir=/home/hadoop/xsxk/tmp/point
# file channel data文件的路径
agent3.channels.channel3.dataDirs=/home/hadoop/xsxk/tmp
#具体定义sink
agent3.sinks.sink3.type = hive
agent3.sinks.sink3.hive.metastore = thrift://hadoop:9083
agent3.sinks.sink3.hive.database = xsxk
agent3.sinks.sink3.hive.table = tb_student
agent3.sinks.sink3.serializer = DELIMITED
agent3.sinks.sink3.serializer.delimiter = ","
agent3.sinks.sink3.serializer.serdeSeparator = ','
agent3.sinks.sink3.serializer.fieldnames = id,name,gender,birthdate,phonenumber,major_class
agent3.sinks.sink3.batchSize = 90
#组装source、channel、sink
agent3.sources.source3.channels = channel3
agent3.sinks.sink3.channel = channel3

20201218163537604.png

image.png注意在vi里面的操作，不知道的请移步上一篇文章。这样我们创建了四个文件，到时候我们只需要启动四个不同监听就可以同时都四个表的数据了

在hive里面创建相应的四个表

启动hive

hive
create database xsxk;
use xsxk;
create table `xsxk`.`tb_course`  (
  `code` varchar(255) ,
  `name` varchar(255) ,
  `period` varchar(255) ,
  `credit` varchar(255) 
) 
clustered by(code) into 3 buckets
row format delimited fields terminated by ','
stored as orc tblproperties('transactional'='true');
create table `xsxk`.`tb_course_class`  (
  `code` varchar(255) ,
  `semester` varchar(255) ,
  `teacher_id` varchar(255) ,
  `course_code` varchar(255) 
) 
clustered by(code) into 3 buckets
row format delimited fields terminated by ','
stored as orc tblproperties('transactional'='true');
create table `xsxk`.`tb_electives`  (
  `course_class_code` varchar(255) ,
  `student_id` varchar(255) ,
  `score` varchar(255)
) 
clustered by(course_class_code) into 3 buckets
row format delimited fields terminated by ','
stored as orc tblproperties('transactional'='true');
create table `xsxk`.`tb_student`  (
  `id` varchar(255) ,
  `name` varchar(255) ,
  `gender` varchar(255) ,
  `birthdate` varchar(255), 
  `phonenumber` varchar(255) , 
  `major_class` varchar(255)
) 
clustered by(id) into 3 buckets
row format delimited fields terminated by ','
stored as orc tblproperties('transactional'='true');

image.png

hive里面创建结果表

create table `xsxk`.`xsxk_result`  (
  `key` varchar(255) ,
  `value` varchar(255)) ;

MySQL里面创建接收表

CREATE DATABASE xsxk;
create table `xsxk`.`xsxk_result`  (
  `key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

导入数据，准备四个flume导入监听代码

首先运行该代码启动

hive --service metastore -p 9083

依次运行下面的代码，每次导入成功都需要，关闭依次终端（或者启动多个终端）:

flume-ng agent --conf conf --conf-file tb_course.properties -name agent3 -Dflume.hadoop.logger=INFO,console
flume-ng agent --conf conf --conf-file tb_course_class.properties -name agent3 -Dflume.hadoop.logger=INFO,console
flume-ng agent --conf conf --conf-file tb_electives.properties -name agent3 -Dflume.hadoop.logger=INFO,console
flume-ng agent --conf conf --conf-file tb_student.properties -name agent3 -Dflume.hadoop.logger=INFO,console

以上就把四个表格全部导入进去了，下面我们在hive里面查询一下这些东西是否真的成功了

select count(*) from tb_course;
select count(*) from tb_course_class;
select count(*) from tb_electives;
select count(*) from tb_student;

20201218170602124.png

20201218170749591.png

20201218170858327.png

20201218171057244.png数据分析

这一步是最重要的，我们之前在MySQL里面测试了的，所以我们直接把这些结果插入到我们的hive结果表里面

注意每次查询的时候最好先执行这个代码，因为这个是hive的特性，在多表查询的时候需要进行等值连接

set hive.mapred.mode=nonstrict;

男女比例

INSERT INTO xsxk_result
(SELECT '男女比例',c.`男女比例` FROM
(SELECT a.`男生人数`/b.`女生人数` as `男女比例` from (SELECT count(*) as `男生人数` from tb_student WHERE gender='男') as a,
(SELECT count(*) as `女生人数` from tb_student WHERE gender='女') as b) as c);

20201218171441174.png

男女结果近似1:1.说明模拟的数据还是比较合理，虽然男生高于女生数量，但是也不是特别的高。

对于男女比例失调的当今社会，我只想说“你有多大的磁场就会有多大的引力，温水煮青蛙，只能坐井观天，你想要的只有自己才能给自己

及格率

INSERT INTO xsxk_result
(SELECT '及格率',c.`及格率` FROM
(SELECT b.`及格人数`/a.`总数` as `及格率` FROM (SELECT count(*) as `总数` from tb_electives) as a,
(SELECT count(*) as `及格人数` from tb_electives WHERE SCORE>='60') as b) as c);

查询一下刚刚插入到结果表里面的信息：

select * from xsxk_result;

20201218172242817.png

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

select q.`NAME`,ff.`课程平均分` from tb_course as q,
(SELECT ee.`课程编号`,AVG(ee.`分数`) as `课程平均分` from 
(SELECT cc.`COURSE_CODE` as `课程编号`,dd.`平均分` as `分数`from tb_course_class as cc,
(select course_class_code as `班号`,AVG(SCORE)as `平均分` from tb_electives group by course_class_code order by `平均分` DESC) as dd WHERE
cc.`CODE`=dd.`班号`) as ee GROUP BY ee.`课程编号`) as ff WHERE q.`CODE`=ff.`课程编号`
INSERT INTO xsxk_result
(SELECT c.`name` as `课程名称`,AVG(SCORE) as `平均分` from tb_course as c,tb_electives as e,tb_course_class as cc WHERE cc.`CODE`=e.COURSE_CLASS_CODE and cc.COURSE_CODE=c.`CODE` 
GROUP BY c.`CODE` ORDER BY `平均分` DESC);

20201219095314772.png

20201219095350840.png

有2门课不及格的学生

INSERT INTO xsxk_result
(
SELECT s.`NAME` as `姓名` ,'挂科两门' from tb_student as s JOIN tb_electives as e 
on s.ID=e.STUDENT_ID WHERE e.SCORE<60 GROUP BY s.`NAME` HAVING COUNT(s.`NAME`)=2
);

把结果导出到MySQL里面

sqoop export --connect jdbc:mysql://localhost:3306/xsxk --username root -P --table xsxk_result --export-dir  /user/hive/

2020121909581836.png

20201219100810694.png

Hive操作——删除表(drop、truncate)

Hive删除操作主要分为几大类：删除数据（保留表）、删除库表、删除分区。

一、仅删除表中数据，保留表结构

hive> truncate table 表名;

truncate操作用于删除指定表中的所有行，相当于delete from table where 1=1.表达的是一个意思。


注意：truncate 不能删除外部表！因为外部表里的数据并不是存放在Hive Meta store中。创建表的时候指定了EXTERNAL，外部表在删除分区后，hdfs中的数据还存在，不会被删除。因此要想删除外部表数据，可以把外部表转成内部表或者删除hdfs文件。


二、删除表

hive> drop table if exists 表名;

drop table if exists table_name;

三、删除库

hive> drop database if exists 库名;

注意如果库里有表会报错

解决这个错误有两种方法：一、就是很简单的将所有表先删除完，再删除库。

另外一种就是使用下述的方法：使用cascade关键字执行强制删库。

drop database if exists 库名 cascade;

四、删除hive分区

alter table table_name drop partition (partition_name=‘分区名’)

文章标签：
云数据库 RDS MySQL 版
Python
数据挖掘
分布式计算
SQL
关系型数据库
MySQL
Hadoop
HIVE
数据库
大数据
关键词：
数据库分析
数据库实验
hadoop分析
选课数据库
数据库hadoop
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
王小王-123
目录
相关文章
郑小健
|
5月前
|
关系型数据库 MySQL 分布式数据库
PolarDB 与传统数据库的性能对比分析
【8月更文第27天】随着云计算技术的发展，越来越多的企业开始将数据管理和存储迁移到云端。阿里云的 PolarDB 作为一款兼容 MySQL 和 PostgreSQL 的关系型数据库服务，提供了高性能、高可用和弹性伸缩的能力。本文将从不同角度对比 PolarDB 与本地部署的传统数据库（如 MySQL、PostgreSQL）在性能上的差异。
郑小健
343 1
全干程序员demo
|
2月前
|
存储 SQL Apache
Apache Doris 开源最顶级基于MPP架构的高性能实时分析数据库
Apache Doris 是一个基于 MPP 架构的高性能实时分析数据库，以其极高的速度和易用性著称。它支持高并发点查询和复杂分析场景，适用于报表分析、即席查询、数据仓库和数据湖查询加速等。最新发布的 2.0.2 版本在性能、稳定性和多租户支持方面有显著提升。社区活跃，已广泛应用于电商、广告、用户行为分析等领域。
全干程序员demo
134 1
Apache Doris 开源最顶级基于MPP架构的高性能实时分析数据库
大侠之运维
|
6月前
|
SQL Linux 数据库
【开源推荐】专门用作分析的一款高性能数据库
【7月更文挑战4天】
大侠之运维
139 4
baker_zhuang
|
3月前
|
SQL 关系型数据库 MySQL
Vanna使用ollama分析本地数据库
这篇文章详细介绍了如何使用Vanna和Ollama框架来分析本地数据库，实现自然语言查询转换为SQL语句并与数据库交互的过程。
baker_zhuang
472 7
Vanna使用ollama分析本地数据库
东方睿赢
|
2月前
|
存储 Java 关系型数据库
在Java开发中，数据库连接是应用与数据交互的关键环节。本文通过案例分析，深入探讨Java连接池的原理与最佳实践
在Java开发中，数据库连接是应用与数据交互的关键环节。本文通过案例分析，深入探讨Java连接池的原理与最佳实践，包括连接创建、分配、复用和释放等操作，并通过电商应用实例展示了如何选择合适的连接池库（如HikariCP）和配置参数，实现高效、稳定的数据库连接管理。
东方睿赢
72 2
java冯坚持
|
3月前
|
关系型数据库 MySQL Java
Django学习二：配置mysql，创建model实例，自动创建数据库表，对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库，创建模型实例，并自动或手动创建数据库表，以及对这些表进行操作的详细教程。
java冯坚持
112 0
Django学习二：配置mysql，创建model实例，自动创建数据库表，对mysql数据库表已经创建好的进行直接操作和实验。
武子康
|
3月前
|
SQL 分布式计算 Hadoop
Hadoop-19 Flume Agent批量采集数据到HDFS集群 监听Hive的日志 操作则把记录写入到HDFS 方便后续分析
Hadoop-19 Flume Agent批量采集数据到HDFS集群 监听Hive的日志 操作则把记录写入到HDFS 方便后续分析
武子康
55 2
baker_zhuang
|
3月前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库，实现自然语言查询功能，包括环境搭建和配置流程。
baker_zhuang
349 0
lsug6eziqmdfk1111
|
3月前
|
存储 分布式计算 数据库
阿里云国际版设置数据库云分析工作负载的 ClickHouse 版
阿里云国际版设置数据库云分析工作负载的 ClickHouse 版
lsug6eziqmdfk1111
61 2
糜终
|
4月前
|
Oracle NoSQL 关系型数据库
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
糜终
727 2

热门文章

最新文章

  • 1
    MySQL数据库下载安装教程（Windows&Linux）
  • 2
    达梦数据库字段类型 varchar 转 text
  • 3
    Python处理数据库：MySQL与SQLite详解 | python小知识
  • 4
    使用 Spring Boot 执行数据库操作：全面指南
  • 5
    PolarDB 分布式版 V2.0，安全可靠的集中分布式一体化数据库管理软件
  • 6
    在云上轻松部署达梦数据库
  • 7
    活动实践 | 自建数据库迁移到云数据库
  • 8
    MySQL导入.sql文件后数据库乱码问题
  • 9
    数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
  • 10
    数据库数据恢复—ORACLE常见故障的数据恢复方案
  • 1
    Hadoop配置复杂性
    53
  • 2
    Hadoop生态系统集成问题
    67
  • 3
    Hadoop权限问题
    133
  • 4
    Hadoop安全性问题
    143
  • 5
    安装hadoop学习笔记
    72
  • 6
    Hadoop的HDFS问题
    90
  • 7
    Hadoop防火墙问题
    111
  • 8
    Hadoop配置
    81
  • 9
    Hadoop网络问题
    168
  • 10
    Hadoop常见问题
    80

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库核心概念
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • 高校精品课-西安交通大学 -数据库理论与技术

    • 相关电子书

    更多
  • DTCC 2022大会集锦《云原生一站式数据库技术与实践》
  • 阿里云瑶池数据库精要2022版
  • 2022 DTCC-阿里云一站式数据库上云最佳实践

    • 相关实验场景

    更多
  • 使用DAS实现数据库SQL优化
  • 自建数据库迁移到云数据库
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • AnalyticDB MySQL游戏行业数据分析实践
  • 快速体验PolarDB开源数据库
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月