Kettle项目实战：比赛信息自动流转方案

Kettle项目实战

比赛信息自动流转方案

1. 项目背景与介绍

众多的比赛都可以为抽象成以下的流程：

传统的报名工作需要赛事组工作人员以表格或是邮件等方式采集选手的信息，并归档处理。选参赛过程中的种种行为，诸如签到、比赛、得分都得不到高效的记录，往往由纸张的表格记录，再录入excel进行整理。在处理比赛结果时，需要重复地完成结算流程，并进行检查，非常消耗人工，且易产生错误。

在此番背景下，一个整合比赛从发起、开启报名、比赛到结算的工作流可以被搭建起来，以更高效地筹办与组织赛事。

简单来看，工作流需要解决的问题包括且不局限于：

• 完成比赛信息录入
• 完成选手信息录入
• 完成报名信息录入
• 完成参赛信息录入
• 完成打分信息录入
• 完成信息间的流转与分析

本文将使用ETL技术，在数据已有的前提下，实现数据的流转与不同维度的分析。

2. 准备工作

2.1 构建数据库

数据库型号：TDSQL-C for MySQL

MySQL 5.7

在拆解完需求后，可以得到以下几个实体类（类之间的关系见3.1 项目架构 ）：

表1 参赛选手信息表
库名表名字段名数据类型默认值是否允许为空字段说明ETLplayerplayer_idvarchar(12)NULL否选手学号ETLplayerplayer_namevarchar(10)NULL否选手姓名ETLplayerplayer_academyvarchar(15)NULL否选手学院ETLplayerplayer_classvarchar(15)NULL否选手班级ETLplayerplayer_televarchar(11)NULL是选手电话ETLplayerplayer_avatarvarchar(255)NULL是选手头像ETLplayerplayer_qqvarchar(20)NULL是选手qqETLplayerplayer_emailvarchar(50)NULL是选手邮箱
表2 比赛信息表
库名表名字段名数据类型默认值是否允许为空字段说明ETLcompetitioncomp_idvarchar(255)NULL否比赛idETLcompetitioncomp_namevarchar(255)NULL否比赛名称ETLcompetitioncomp_ddldatetimeNULL否比赛报名截至日期ETLcompetitioncomp_start_datedatetimeNULL否正式比赛日期ETLcompetitioncomp_end_datedatetimeNULL否比赛结算日期
表3 场地信息表
库名表名字段名数据类型默认值是否允许为空字段说明ETLvenuevenue_idvarchar(255)NULL否场地idETLvenuevenue_namevarchar(255)NULL否场地名称ETLvenuevenue_capacityint(11)NULL否场地容量
表4 报名信息表
库名表名字段名数据类型默认值是否允许为空字段说明ETLenroll_infoenroll_idvarchar(255)NULL否报名idETLenroll_infoenroll_comp_idvarchar(255)NULL否报名比赛idETLenroll_infoenroll_player_idvarchar(12)NULL否报名选手学号ETLenroll_infoenroll_timedatetimeNULL否报名时间
表5 参赛信息表
库名表名字段名数据类型默认值是否允许为空字段说明ETLparticipationparti_idvarchar(255)NULL否参赛身份码ETLparticipationparti_comp_idvarchar(255)NULL否参赛比赛idETLparticipationparti_player_idvarchar(12)NULL否参赛选手idETLparticipationparti_venue_idvarchar(255)NULL否参赛会场idETLparticipationparti_timedatetimeNULL否参赛时间ETLparticipationparti_numint(11)NULL是号码牌
表6 得分信息表
库名表名字段名数据类型默认值是否允许为空字段说明ETLscorescore_idvarchar(255)NULL否得分idETLscorescore_parti_idvarchar(255)NULL否参赛身份码ETLscorescore_judge_namevarchar(15)NULL否打分评委姓名ETLscorescore_scorefloatNULL否得分值ETLscorescore_timedatetimeNULL否打分时间

2.2 构建假数据

向数据库中插入一些模拟数据，以开展后续的实验

💡 完整的模拟代码见代码仓库

""" 模拟报名记录 """import sql_connector
import random
import datetime
import uuid
from timmer import get_random_time

players = sql_connector.Sql().get_player()
competitions = sql_connector.Sql().get_compatiton()for player in players:
    competition = random.choice(competitions)
    sql_connector.Sql().insert_into_enroll_info(
        enroll_id=uuid.uuid4(),
        enroll_comp_id=competition[0],
        enroll_player_id=player[0],
        enroll_time=get_random_time(20,28))

""" 模拟参赛记录 """import sql_connector
import random
import timmer

enrolls = sql_connector.Sql().get_enroll_info()
venues = sql_connector.Sql().get_venue()# 随机剔除30个
enrolls = enrolls[:-30]for(index,enroll)inenumerate(enrolls):
    venue = random.choice(venues)
    sql_connector.Sql().insert_into_participation(
        parti_id=enroll[0],
        comp_id=enroll[1],
        player_id=enroll[2],
        venue_id=venue[0],
        parti_time=timmer.get_random_time(30,30),
        parti_num=index
    )

""" 模拟打分记录 """import sql_connector
import random
import timmer
import uuid

participations = sql_connector.Sql().get_participation()
judges =['teacher-'+str(x)for x inrange(1,10)]""" 运行两次 一个人有几个成绩 """for participation in participations:
    sql_connector.Sql().insert_into_score(
        score_id=uuid.uuid4(),
        score_parti_id=participation[0],
        score_judge_name=random.choice(judges),
        score_score=random.randint(60,100),
        score_time=timmer.get_random_time(30,30))

p.s. 构建完

enroll_info

和

participation

数据之后随机删掉几行，以验证报名率，参赛率等指标

2.3 准备邮箱服务

开通邮箱的SMTP服务

具体可参考本文：

Kettle邮件发送

3. 项目实施

3.1 项目架构

拆解了需求后，可以得到四个实体：

选手

、

比赛

、

会场

和

得分情况

，如下图所示。

报名信息

由

选手

和

比赛

关联生成，记录了报名时间等信息。

参赛情况

是报名选手在指定

会场

签到后生成的记录，用于代表一次参赛。

得分情况

和

参赛情况

进行关联，记录选手的参赛得分。

最后，比赛总览会输出一系列比赛的数据汇总与分析。

Kettle中的转换排列如下，为了简化数据流转的操作，我们假设这次数据仅针对单次比赛。

总的来看，流转主要涉及到排序、连表、分组这三个操作。图中每一个黄色标记代表着一个最终结果的输出。

每个转换操作的配置这里不做赘述，详细可见源文件（底部附件中）。

4. 运行测试

Kettle 运行转换操作的输出:

选手得分信息与排名

评委打分信息

学院参赛率

班级参赛率

场地到场率信息

会场得分情况

输出的excel文件结果:

班级参赛率.xls

比赛成绩排名.xls

老师打分情况.xls

学院参赛率.xls

向选手发送的晋级邮件:

5. 总结与展望

本项目着眼于国际学院的“中外演说家”比赛流程，设计了一套解决实际问题的比赛数据管理方案。对数据库中的格式化数据进行了抽取与分析，得到了赛事组所要求的统计分析结果。自动化了数据的统计和消息的推送，有一定的扩展潜力。

本项目作为ETL技术的一个Demo，也有着较大的局限性。首先，只使用了Kettle中几个常用的转换操作，没有扩展到大数据与流式数据的阶段。其次，没有使用真实的业务数据，导致设计可能存在缺陷。第三，没有配套的Web系统与数据库关联，导致目前的流转只停留在可行性研究阶段，无法投入使用。

在未来，本项目计划组建Web开发团队，在现有基础上开发一整套集成比赛报名、比赛管理、数据分析于一体的综合系统，并使之真正发挥效益，便利赛事组的工作与管理。

附录

代码仓库:

gitee

转换源文件:

未完成脱敏，暂未上传

参考文献:

Kettle