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

Kettle项目实战 比赛信息自动流转方案 1. 项目背景与介绍

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

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

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

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

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

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

2. 准备工作 2.1 构建数据库

数据库型号：TDSQL-C for MySQL MySQL 5.7

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

表1 参赛选手信息表

库名	表名	字段名	数据类型	默认值	是否允许为空	字段说明
ETL	player	player_id	varchar(12)	NULL	否	选手学号
ETL	player	player_name	varchar(10)	NULL	否	选手姓名
ETL	player	player_academy	varchar(15)	NULL	否	选手学院
ETL	player	player_class	varchar(15)	NULL	否	选手班级
ETL	player	player_tele	varchar(11)	NULL	是	选手电话
ETL	player	player_avatar	varchar(255)	NULL	是	选手头像
ETL	player	player_qq	varchar(20)	NULL	是	选手qq
ETL	player	player_email	varchar(50)	NULL	是	选手邮箱

表2 比赛信息表

库名	表名	字段名	数据类型	默认值	是否允许为空	字段说明
ETL	competition	comp_id	varchar(255)	NULL	否	比赛id
ETL	competition	comp_name	varchar(255)	NULL	否	比赛名称
ETL	competition	comp_ddl	datetime	NULL	否	比赛报名截至日期
ETL	competition	comp_start_date	datetime	NULL	否	正式比赛日期
ETL	competition	comp_end_date	datetime	NULL	否	比赛结算日期

表3 场地信息表

库名	表名	字段名	数据类型	默认值	是否允许为空	字段说明
ETL	venue	venue_id	varchar(255)	NULL	否	场地id
ETL	venue	venue_name	varchar(255)	NULL	否	场地名称
ETL	venue	venue_capacity	int(11)	NULL	否	场地容量

表4 报名信息表

库名	表名	字段名	数据类型	默认值	是否允许为空	字段说明
ETL	enroll_info	enroll_id	varchar(255)	NULL	否	报名id
ETL	enroll_info	enroll_comp_id	varchar(255)	NULL	否	报名比赛id
ETL	enroll_info	enroll_player_id	varchar(12)	NULL	否	报名选手学号
ETL	enroll_info	enroll_time	datetime	NULL	否	报名时间

表5 参赛信息表

库名	表名	字段名	数据类型	默认值	是否允许为空	字段说明
ETL	participation	parti_id	varchar(255)	NULL	否	参赛身份码
ETL	participation	parti_comp_id	varchar(255)	NULL	否	参赛比赛id
ETL	participation	parti_player_id	varchar(12)	NULL	否	参赛选手id
ETL	participation	parti_venue_id	varchar(255)	NULL	否	参赛会场id
ETL	participation	parti_time	datetime	NULL	否	参赛时间
ETL	participation	parti_num	int(11)	NULL	是	号码牌

表6 得分信息表

库名	表名	字段名	数据类型	默认值	是否允许为空	字段说明
ETL	score	score_id	varchar(255)	NULL	否	得分id
ETL	score	score_parti_id	varchar(255)	NULL	否	参赛身份码
ETL	score	score_judge_name	varchar(15)	NULL	否	打分评委姓名
ETL	score	score_score	float	NULL	否	得分值
ETL	score	score_time	datetime	NULL	否	打分时间

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) in enumerate(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 in range(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