模板项目-学生专业课程能力模型分析

by prentiss 2022-09-16

项目简介

本项目通过学生课程学习情况的数据分析，对学生在课程学习中所反映出来的能力进行维度分析，应用简单的数据处理方式对数据进行脱敏、数值转换并聚合分析至宽表，以帮助快速定位学生的能力特征。

本项目演示了从对某高校学生课程数据的采集、数据统计分析、数据转换导出到可视化展示的完整数据工程项目的流程，以示例的方式，帮助用户快速搭建起数据项目，并通过分析结果提供价值洞见。

本示例项目步骤包括：

  • 数据采集：采集学生信息表、采集学生专业能力表、采集学生通用能力表、采集学生综合评估表
  • 数据处理：统计及聚合计算
  • 数据转换：导出数据到目标库
  • 可视化展示：Superset数据看板可视化展示，展示学生能力模型

项目步骤

1. 采集学生通用能力数据表

步骤简介

通过数据库采集步骤，把本示例项目准备的预制MySQL数据表，采集至目标Hive库，以便后续的数据分析与处理。

库表权限说明

  • public_mysql:为示例/向导项目专属MySQL库；用户使用权限：读
  • public_project_data:为向导项目专属Hive库；用户使用权限：读
  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • org_xxx:为机构项目Hive库（用户角色切换至Admin/DevOps可创建该类型项目，xxx为机构名称，可点击界面右上角获取），付费机构可在机构下的多用户间可共享数据；用户使用权限：读写
  • user_xxx:为个人项目Hive库（用户角色切换至个人账号可创建该类型项目），机构内各成员可独享该类型库；用户使用权限：读写

采集数据源配置

  • 数据源：public_mysql
  • 表：demo_student_common_ability
  • 数据范围：全量采集

保持默认，无需修改，点击下一步

采集输出配置

  • Hive数据库名称：下拉框选择org_xxx或者user_xxx
  • Hive表名称：demo_student_common_ability_full01

其他内容保持默认，无需修改，点击【保存】并【运行】

2. 采集学生综合能力数据表

步骤简介

通过数据库采集步骤，把本示例项目准备的预制MySQL数据表，采集至目标Hive库，以便后续的数据分析与处理。

库表权限说明

  • public_mysql:为示例/向导项目专属MySQL库；用户使用权限：读
  • public_project_data:为向导项目专属Hive库；用户使用权限：读
  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • org_xxx:为机构项目Hive库（用户角色切换至Admin/DevOps可创建该类型项目，xxx为机构名称，可点击界面右上角获取），付费机构可在机构下的多用户间可共享数据；用户使用权限：读写
  • user_xxx:为个人项目Hive库（用户角色切换至个人账号可创建该类型项目），机构内各成员可独享该类型库；用户使用权限：读写

采集数据源配置

  • 数据源：public_mysql
  • 表：demo_student_overall_ability
  • 数据范围：全量采集

保持默认，无需修改，点击下一步

采集输出配置

  • Hive数据库名称：下拉框选择org_xxx或者user_xxx
  • Hive表名称：demo_student_overall_ability_full01

其他内容保持默认，无需修改，点击【保存】并【运行】

3. 采集学生专业能力数据表

步骤简介

通过数据库采集步骤，把本示例项目准备的预制MySQL数据表，采集至目标Hive库，以便后续的数据分析与处理。

库表权限说明

  • public_mysql:为示例/向导项目专属MySQL库；用户使用权限：读
  • public_project_data:为向导项目专属Hive库；用户使用权限：读
  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • org_xxx:为机构项目Hive库（用户角色切换至Admin/DevOps可创建该类型项目，xxx为机构名称，可点击界面右上角获取），付费机构可在机构下的多用户间可共享数据；用户使用权限：读写
  • user_xxx:为个人项目Hive库（用户角色切换至个人账号可创建该类型项目），机构内各成员可独享该类型库；用户使用权限：读写

采集数据源配置

  • 数据源：public_mysql
  • 表：demo_student_professional_ability
  • 数据范围：全量采集

保持默认，无需修改，点击下一步

采集输出配置

  • Hive数据库名称：下拉框选择org_xxx或者user_xxx
  • Hive表名称：demo_student_professional_ability_full01

其他内容保持默认，无需修改，点击【保存】并【运行】

4. 采集学生基础信息数据表

步骤简介

通过数据库采集步骤，把本示例项目准备的预制MySQL数据表，采集至目标Hive库，以便后续的数据分析与处理。

库表权限说明

  • public_mysql:为示例/向导项目专属MySQL库；用户使用权限：读
  • public_project_data:为向导项目专属Hive库；用户使用权限：读
  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • org_xxx:为机构项目Hive库（用户角色切换至Admin/DevOps可创建该类型项目，xxx为机构名称，可点击界面右上角获取），付费机构可在机构下的多用户间可共享数据；用户使用权限：读写
  • user_xxx:为个人项目Hive库（用户角色切换至个人账号可创建该类型项目），机构内各成员可独享该类型库；用户使用权限：读写

采集数据源配置

  • 数据源：public_mysql
  • 表：demo_user_info
  • 数据范围：全量采集

保持默认，无需修改，点击下一步

采集输出配置

  • Hive数据库名称：下拉框选择org_xxx或者user_xxx
  • Hive表名称：demo_user_info_full01

其他内容保持默认，无需修改，点击【保存】并【运行】

5. 处理学生专业能力数据

步骤简介

通过Hive步骤进行HQL业务逻辑代码编写与运行，对采集至Hive库的数据表进行处理

库表权限说明

  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • org_xxx:为机构项目Hive库（用户角色切换至Admin/DevOps可创建该类型项目，xxx为机构名称，可点击界面右上角获取），付费机构可在机构下的多用户间可共享数据；用户使用权限：读写
  • user_xxx:为个人项目Hive库（用户角色切换至个人账号可创建该类型项目），机构内各成员可独享该类型库；用户使用权限：读写

编写主程序

--用户需要使用实际的Hive库名，替换示例Hive库”user_xxx“
-- 例如：机构项目使用：org_xxx（xxx为机构名），个人项目使用：user_xxx (xxx为用户名)，可在界面右上角获取
use user_xxx;
​
drop table demo_student_professional_ability_full_clean;
​
CREATE TABLE if not exists demo_student_professional_ability_full_clean (
    `user_id_wc` string COMMENT '学号', 
    `ppyyych` int COMMENT '品牌运营与策划能力',
    `wacyyzx` int COMMENT '文案创意与撰写能力',
    `xmttg` int COMMENT '新媒体推广能力',
    `wlggtf` int COMMENT '网络广告投放能力',
    `ssyqyh` int COMMENT '搜索引擎优化能力',
    `ssyqjj` int COMMENT '搜索引擎竞价能力',
    `hdchyzx` int COMMENT '活动策划与执行能力',
    `khfwygl` int COMMENT '客户服务与管理能力',
    `sqyx` int COMMENT '社群营销能力'
)
STORED AS orc
TBLPROPERTIES ("orc.compress"="SNAPPY");
​
INSERT OVERWRITE TABLE demo_student_professional_ability_full_clean
Select user_id_wc
     , cast(round(nvl(ppyyych,0) / 10) as int)  as ppyyych
     , cast(round(nvl(wacyyzx,0) / 10) as int)  as wacyyzx
     , cast(round(nvl(xmttg,0) / 10) as int)  as xmttg
     , cast(round(nvl(wlggtf,0) / 10) as int)  as wlggtf
     , cast(round(nvl(ssyqyh,0) / 10) as int)  as ssyqyh
     , cast(round(nvl(ssyqjj,0) / 10) as int)  as ssyqjj
     , cast(round(nvl(hdchyzx,0) / 10) as int)  as hdchyzx
     , cast(round(nvl(khfwygl,0) / 10) as int)  as khfwygl
     , cast(round(nvl(sqyx,0) / 10) as int)  as sqyx
From demo_student_professional_ability_full01;

6. 处理学生通用能力数据

步骤简介

通过Hive步骤进行HQL业务逻辑代码编写与运行，对采集至Hive库的数据表进行处理

库表权限说明

  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • org_xxx:为机构项目Hive库（用户角色切换至Admin/DevOps可创建该类型项目，xxx为机构名称，可点击界面右上角获取），付费机构可在机构下的多用户间可共享数据；用户使用权限：读写
  • user_xxx:为个人项目Hive库（用户角色切换至个人账号可创建该类型项目），机构内各成员可独享该类型库；用户使用权限：读写

编写主程序


--用户需要使用实际的Hive库名，替换示例Hive库”user_xxx“
-- 例如：机构项目使用：org_xxx（xxx为机构名），个人项目使用：user_xxx (xxx为用户名)，可在界面右上角获取
use user_xxx;
drop table demo_student_common_ability_full_clean;
​
CREATE TABLE if not exists demo_student_common_ability_full_clean (
    `user_id_wc` string COMMENT '学号', 
    `styxl` int COMMENT '健康的身体及心理状态',
    `wlwgjyyjsy` int COMMENT '互联网工具及硬件使用能力',
    `sjcl` int COMMENT '数据处理能力',
    `xz` int COMMENT '写作能力',
    `mx` int COMMENT '美学能力',
    `xxjs` int COMMENT '信息检索能力'
)
STORED AS orc
TBLPROPERTIES ("orc.compress"="SNAPPY");
​
​
INSERT OVERWRITE TABLE demo_student_common_ability_full_clean
Select user_id_wc
     , cast(round(nvl(styxl,0) / 10) as int)  as styxl
     , cast(round(nvl(wlwgjyyjsy,0) / 10) as int)  as wlwgjyyjsy
     , cast(round(nvl(sjcl,0) / 10) as int)  as sjcl
     , cast(round(nvl(xz,0) / 10) as int)  as xz
     , cast(round(nvl(mx,0) / 10) as int)  as mx
     , cast(round(nvl(xxjs,0) / 10) as int)  as xxjs
From demo_student_common_ability_full01

7. 处理学生综合能力数据

步骤简介

通过Hive步骤进行HQL业务逻辑代码编写与运行，对采集至Hive库的数据表进行处理

库表权限说明

  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • org_xxx:为机构项目Hive库（用户角色切换至Admin/DevOps可创建该类型项目，xxx为机构名称，可点击界面右上角获取），付费机构可在机构下的多用户间可共享数据；用户使用权限：读写
  • user_xxx:为个人项目Hive库（用户角色切换至个人账号可创建该类型项目），机构内各成员可独享该类型库；用户使用权限：读写

编写主程序


--用户需要使用实际的Hive库名，替换示例Hive库”user_xxx“
-- 例如：机构项目使用：org_xxx（xxx为机构名），个人项目使用：user_xxx (xxx为用户名)，可在界面右上角获取
use user_xxx;
drop table demo_student_overall_ability_full_clean;
​
CREATE TABLE if not exists demo_student_overall_ability_full_clean (
`user_id_wc` string COMMENT '学号', 
`xxzb` int COMMENT '学习指标',
`ybnlzb` int COMMENT '应变能力指标', 
`cxnlzb` int COMMENT '创新能力指标',
`kynlzb` int COMMENT '抗压能力指标', 
`gtnlzb` int COMMENT '沟通能力指标',
`zzglnlzb` int COMMENT '组织管理能力指标', 
`zyddzb` int COMMENT '职业道德指标',
`jkdsxzb` int COMMENT '健康的身心指标', 
`jsnlzb` int COMMENT '鉴赏能力指标',
`sjfxclnlzb` int COMMENT '数据分析处理能力指标', 
`nrcznlzb` int COMMENT '内容创作能力指标',
`hlwptyynlzb` int COMMENT '互联网平台应用能力指标', 
`xxsjjsnlzb` int COMMENT '信息收集检索能力指标',
`yxchnlzb` int COMMENT '营销策划能力指标', 
`yyzhnlzb` int COMMENT '运营整合能力指标'
)
STORED AS orc
TBLPROPERTIES ("orc.compress"="SNAPPY");
​
INSERT OVERWRITE TABLE demo_student_overall_ability_full_clean
Select user_id_wc
     , cast(round(nvl(xxzb,0) / 10) as int)  as xxzb
     , cast(round(nvl(ybnlzb,0) / 10) as int)  as ybnlzb
     , cast(round(nvl(cxnlzb,0) / 10) as int)  as cxnlzb
     , cast(round(nvl(kynlzb,0) / 10) as int)  as kynlzb
     , cast(round(nvl(gtnlzb,0) / 10) as int)  as gtnlzb
     , cast(round(nvl(zzglnlzb,0) / 10) as int)  as zzglnlzb
     , cast(round(nvl(zyddzb,0) / 10) as int)  as zyddzb
     , cast(round(nvl(jkdsxzb,0) / 10) as int)  as jkdsxzb
     , cast(round(nvl(jsnlzb,0) / 10) as int)  as jsnlzb
     , cast(round(nvl(sjfxclnlzb,0) / 10) as int)  as sjfxclnlzb
     , cast(round(nvl(nrcznlzb,0) / 10) as int)  as nrcznlzb
     , cast(round(nvl(hlwptyynlzb,0) / 10) as int)  as hlwptyynlzb
     , cast(round(nvl(xxsjjsnlzb,0) / 10) as int)  as xxsjjsnlzb
     , cast(round(nvl(yxchnlzb,0) / 10) as int)  as yxchnlzb
     , cast(round(nvl(yyzhnlzb,0) / 10) as int)  as yyzhnlzb
From demo_student_overall_ability_full01;

8. 学生专业课程能力模型

步骤简介

通过Hive步骤进行HQL业务逻辑代码编写与运行，对采集至Hive库的数据表进行处理

库表权限说明

  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • org_xxx:为机构项目Hive库（用户角色切换至Admin/DevOps可创建该类型项目，xxx为机构名称，可点击界面右上角获取），付费机构可在机构下的多用户间可共享数据；用户使用权限：读写
  • user_xxx:为个人项目Hive库（用户角色切换至个人账号可创建该类型项目），机构内各成员可独享该类型库；用户使用权限：读写

编写主程序


--用户需要使用实际的Hive库名，替换示例Hive库”user_xxx“
-- 例如：机构项目使用：org_xxx（xxx为机构名），个人项目使用：user_xxx (xxx为用户名)，可在界面右上角获取
use user_xxx;
drop table demo_student_ability_detail;
​
CREATE TABLE if not exists demo_student_ability_detail (
    `user_id_wc` string COMMENT '学号', 
    `ppyyych` int COMMENT '品牌运营与策划能力',
    `wacyyzx` int COMMENT '文案创意与撰写能力',
    `xmttg` int COMMENT '新媒体推广能力',
    `wlggtf` int COMMENT '网络广告投放能力',
    `ssyqyh` int COMMENT '搜索引擎优化能力',
    `ssyqjj` int COMMENT '搜索引擎竞价能力',
    `hdchyzx` int COMMENT '活动策划与执行能力',
    `khfwygl` int COMMENT '客户服务与管理能力',
    `sqyx` int COMMENT '社群营销能力',
    `styxl` int COMMENT '健康的身体及心理状态',
    `wlwgjyyjsy` int COMMENT '互联网工具及硬件使用能力',
    `sjcl` int COMMENT '数据处理能力',
    `xz` int COMMENT '写作能力',
    `mx` int COMMENT '美学能力',
    `xxjs` int COMMENT '信息检索能力',
    `xxzb` int COMMENT '学习指标',
    `ybnlzb` int COMMENT '应变能力指标', 
    `cxnlzb` int COMMENT '创新能力指标',
    `kynlzb` int COMMENT '抗压能力指标', 
    `gtnlzb` int COMMENT '沟通能力指标',
    `zzglnlzb` int COMMENT '组织管理能力指标', 
    `zyddzb` int COMMENT '职业道德指标',
    `jkdsxzb` int COMMENT '健康的身心指标', 
    `jsnlzb` int COMMENT '鉴赏能力指标',
    `sjfxclnlzb` int COMMENT '数据分析处理能力指标', 
    `nrcznlzb` int COMMENT '内容创作能力指标',
    `hlwptyynlzb` int COMMENT '互联网平台应用能力指标', 
    `xxsjjsnlzb` int COMMENT '信息收集检索能力指标',
    `yxchnlzb` int COMMENT '营销策划能力指标', 
    `yyzhnlzb` int COMMENT '运营整合能力指标',
    `school_name`string COMMENT '学校',
    `colledge_name`string COMMENT '学院',
    `major_name`string COMMENT '专业',
    `user_name_wc`string COMMENT '姓名',
    `class_id`string COMMENT '班级号',
    `enrollment_year`string COMMENT '入学年份'
)
STORED AS orc
TBLPROPERTIES ("orc.compress"="SNAPPY");
​
​
INSERT OVERWRITE TABLE demo_student_ability_detail
SELECT t1.`user_id_wc` 
     , t1.`ppyyych` 
     , t1.`wacyyzx` 
     , t1.`xmttg` 
     , t1.`wlggtf` 
     , t1.`ssyqyh` 
     , t1.`ssyqjj` 
     , t1.`hdchyzx` 
     , t1.`khfwygl` 
     , t1.`sqyx` 
     , t2.`styxl` 
     , t2.`wlwgjyyjsy` 
     , t2.`sjcl` 
     , t2.`xz` 
     , t2.`mx` 
     , t2.`xxjs` 
     , t3.`xxzb` 
     , t3.`ybnlzb` 
     , t3.`cxnlzb` 
     , t3.`kynlzb` 
     , t3.`gtnlzb` 
     , t3.`zzglnlzb` 
     , t3.`zyddzb` 
     , t3.`jkdsxzb` 
     , t3.`jsnlzb` 
     , t3.`sjfxclnlzb` 
     , t3.`nrcznlzb` 
     , t3.`hlwptyynlzb` 
     , t3.`xxsjjsnlzb` 
     , t3.`yxchnlzb` 
     , t3.`yyzhnlzb`
     , t4.`school_name`
     , t4.`colledge_name`
     , t4.`major_name`
     , t4.`name`
     , t4.`class_id`
     , t4.`enrollment_year`
from demo_student_professional_ability_full_clean t1
left join demo_student_common_ability_full_clean t2
on t1.user_id_wc = t2.user_id_wc
left join demo_student_overall_ability_full_clean t3
on t1.user_id_wc = t3.user_id_wc
left join demo_user_info_full t4
on t1.user_id_wc = t4.user_id_wc

9. 导出结果数据表至MySQL

步骤简介

通过ETL步骤，把处理完成的Hive结果表转换至目标MySQL库，以方便下游的看板可视化展示

库表权限说明

  • user_beta:为示例项目专属Hive库；用户使用权限：读
  • FM-ds-<机构名>：机构项目MySQL库，用户角色切换至Admin/DevOps可选择该类型数据源
  • FM-ds<用户名>：个人项目MySQL库，用户角色切换至个人可选择该类型数据源

输入源配置

  • 数据源类型：Hive
  • 数据库：下拉框选择user_xxx或者org_xxx(xxx为用户名或机构名)
  • 表：demo_student_ability_detail

其他内容，保持默认，点击【下一步】

输出源配置

  • 数据源类型：MySQL
  • 数据源：下拉框选择FM-ds-<机构名>或者FM-ds-<用户名>
  • 表：students_skillmodel
  • 数据变更：Update

其他内容，保持默认，点击【下一步】，点击【保存】并【运行】

10. BI看板可视化

步骤简介

通过本步骤可使用Superset集成工具，对ETL的MySQL输出表进行基于业务的BI看板可视化展示

Superset使用指南可参考BDOS Online 官方指南：https://www.linktimecloud.com/posts/6346

留言

评论

${{item['author_name']}} 回复 ${{idToContentMap[item.parent] !== undefined ? idToContentMap[item.parent]['author_name'] : ''}}说 · ${{item.date.slice(0, 10)}} 回复

暂时还没有一条评论.