大数据与机器学习:实践方法与行业案例.3.3 自动加载程序的数据库设计

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介:

3.3 自动加载程序的数据库设计


根据之前的设计,自动加载程序需要从数据库配置表中获取配置信息,并不断更新相关的状态,表3-2列出了自动加载程序需要的所有配置表。

表3-2 自动加载程序的配置表

表 名 中文名称 用 途

file_settings 数据文件信息表 存储数据文件名称、日期等配置信息

file_status 数据文件状态表 存储数据文件的状态

load_config 加载配置信息表 存储数据库中表的相关信息

ftp_server 数据缓冲区信息表 存储数据缓冲区文件服务器的相关信息

target_server 目标服务器表 存储目标服务器的相关信息

file_targetServer_rel 文件–目标服务器关联表 file_settings与target_server多对多映射中间表

 

本书使用Hibernate进行关系数据库映射,上述配置表除file_targetServer_rel表外,其余表均对应一个实体(entity),图3-9给出了这些实体间的关系图。

 

图3-9 实体关系图

下面分别给出上述5个实体的说明,根据表格中的信息,可以很方便地转化为所需的ddl建表脚本。

3.3.1 数据文件信息表

数据文件信息表的主要内容如表3-3所示。

表3-3 数据文件信息表(file_settings)

字 段 数据类型 注 释

id bigint 自增长id,主键

file_prefix varchar(100) 文件名前缀

file_date_format varchar(20) 文件日期格式,必须是Java的SimpleDateFormat类支持的格式

file_mid varchar(100) 文件名中间部分

file_suffix varchar(10) 文件名后缀

datedif int 文件日期–当前日期(天数)

priority int 文件优先级。数值越大,优先级越低

load_config_id bigint 对应的文件导入配置id、load_config外键

ftp_path varchar(100) 文件在ftp服务器上的目录

ftp_svr_id bigint ftp服务器id,ftp_server外键

 

文件的全名通过字段file_prefix、file_mid、file_suffix以及文件日期拼接而成(参阅3.1.2节)。表中datedif字段的作用是为了让程序自动识别当日的数据文件,比如当datedif=-1时,程序在2015年10月16日运行时,会识别文件日期为20151015的数据文件。priority字段用于提示文件处理的优先级,当有大量文件同时需要处理时,优先级高的数据文件将会优先处理。

该表中的数据需要初始化,比如原始数据文件01-cdt-trx-dtl-yyyyMMdd- 000000.tsv.zip需要每天下载并加载至交易明细表cdt_trx_dtl中,则需要预先往该表中插入一条记录。代码清单3-1中,load_config_id=66、ftp_svr_id=1,我们将在随后的load_config表和ftp_server表中找到对应的记录。

代码清单 3-1

insert into file_settings (file_prefix, file_date_format, file_mid,file_suffix,

datedif, priority, load_config_id, ftp_path, ftp_svr_id)

values ( '01-cdt-trx-dtl-', 'yyyyMMdd', '-000000.tsv', '.zip', -1, 1, 66, '', 1);

3.3.2 数据文件状态表

自动加载程序会根据数据文件信息表中的相关信息以及程序运行的日期,自动插入文件状态记录,并保存在数据文件状态表中,主要内容如表3-4所示。

表3-4 数据文件状态表(file_status)

字 段 数据类型 注 释

id bigint 自增长id,主键

target_server_id bigint 目标服务器id

file_id bigint 文件id,file_settings外键

full_name varchar(120) 文件全名

status varchar(50) 文件状态

log_info varchar(2000) 日志信息

upt_time datetime(0) 更新时间

file_date varchar(20) 文件日期

batch_date varchar(20) 批次日期

file_path varchar(100) 下载的文件路径

unzip_file_path varchar(200) 解压后的文件路径

file_size bigint 文件大小(Bytes)

file_size_check_cnt tinyint 文件已经被检测的次数

priority int 处理优先级

 

例如,当程序在2015年10月16日运行时,程序根据file_settings表中的相关配置信息,会自动往该表中插入一条信息(为方便表述,将程序自动执行的sql脚本列出,如代码清单3-2所示)。

代码清单 3-2

insert into file_status

(target_server_id,file_id,full_name,status,upt_time,file_date,batch_date)

values (1,1,'01-cdt-trx-dtl-20151015-000000.tsv.zip','FILE_NOT_EXISTS',

'2015-10-16 01:50:27',

'20151015','2015-10-16');

上述脚本中,target_server_id =1、file_id=1,分别对应target_server表和file_settings表中的id=1记录。从代码清单3-1中可以看到,文件全名已经自动拼接(full_name字段值),并且status自动初始化为文件不存在(FILE_NOT_EXISTS),脚本中未出现的字段默认为null,这些字段的值以及status字段的值将随着文件的下载、解压、加载做相应的改变。

3.3.3 加载配置信息表

加载配置信息表用于存储数据文件到对应的数据库表的加载方式等相关信息,通过主键id与file_settings表中的load_config_id关联,主要内容如表3-5所示。

表3-5 加载配置信息表(load_config)

字 段 数据类型 注 释

id bigint 自增长id,主键

control_file varchar(100) 控制文件,含路径(关系型数据库适用)

log_file_path varchar(100) load输出的日志文件路径(关系型数据库适用)

bad_file_path varchar(100) load输出的错误文件路径(关系型数据库适用)

max_errors int 允许的最大错误条数(关系型数据库适用)

load_type varchar(10) 加载方式:增量/全量

tab_nam varchar(50) 对应的表名

db_type varchar(20) 数据库类型

pre_exec_sql varchar(100) load前需要预执行的sql脚本文件,含路径

partition_by_col varchar(50) Hive表需指定partition by字段

 

表file_settings中,load_config_id=66,则在该表中有以下记录:

 

以上记录说明file_settings中id=1的文件对应了表adobe.cdt_trx_dtl,并且该表是一张Hive表,其分区字段是LOAD_DAY。

load_config表中的部分字段仅适用于关系型数据库,如control_file、log_file_path、bad_file_path、max_errors,这些字段用于组成关系型数据库批量加载命令。

 

3.3.4 数据缓冲区信息表

数据缓冲区信息表用于存储ftp文件服务器的相关信息,主要内容如表3-6所示。

表3-6 数据缓冲区信息表(ftp_server)

字 段 数据类型 注 释

id bigint 自增长id,主键

ftp_host varchar(100) ftp服务器ip

ftp_user varchar(50) ftp用户名

ftp_pwd varchar(50) ftp密码

ftp_nam varchar(50) ftp名称

 

表file_settings中,ftp_server_id=1的记录在本表中的信息如下(ftp_host字段和ftp_pwd字段进行了屏蔽,生产上应该进行加密存储)。

 

3.3.5 目标服务器表

目标服务器表是file_settings与target_server多对多映射中间表,主要内容如表3-7所示。

表3-7 目标服务器表(target_server)

字 段 数据类型 注 释

id bigint 自增长id,主键

hostname varchar(100) 目标服务器名称

ip varchar(20) 目标服务器ip

localBaseDir varchar(100) 保存下载的数据文件的基目录

paralFtpCnt int 最大并行下载线程数

paralUnZipCnt int 最大并行解压线程数

paralLoadCnt int 最大并行加载线程数

ftpLoopInterval bigint 循环扫描ftp文件的间隔时间(毫秒)

stopScanTime varchar(50) 扫描至T+1的截止时间,即如果截至T+1的10:00:00仍未检测到文件,则认为文件不存在

db_type varchar(20) 数据库类型

hdfsBaseDir varchar(100) Hadoop中数据文件的存放基目录,当db_type=hive时需指定

 

target_server表中,localBaseDir配置项记录了保存文件的本地基目录,即文件现在只在本地后,将存放在该基目录中,自动加载程序在下载文件时,会根据该文件对应的表名和文件日期在该基目录下创建相应的子目录,并将数据文件保存在对应的子目录中。

paralFtpCnt配置项指明了程序可以同时运行的最大下载线程数,paralLoadCnt、paralUnZipCnt分别指明了并行加载的最大线程个数和并行解压的最大线程个数。

ftpLoopInterval配置项用于声明扫描ftp文件服务器的间隔时间,10000表示每10秒扫描一次FTP文件服务器。

hdfsBaseDir用于Hive加载时指明HDFS文件存放的路径。

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
相关文章
|
9天前
|
机器学习/深度学习 分布式计算 算法
联邦学习是保障数据隐私的分布式机器学习方法
【6月更文挑战第13天】联邦学习是保障数据隐私的分布式机器学习方法,它在不暴露数据的情况下,通过在各设备上本地训练并由中心服务器协调,实现全局模型构建。联邦学习的优势在于保护隐私、提高训练效率和增强模型泛化。已应用于医疗、金融和物联网等领域。未来趋势包括更高效的数据隐私保护、提升可解释性和可靠性,以及与其他技术融合,有望在更多场景发挥潜力,推动机器学习发展。
22 4
|
1天前
|
数据采集 数据可视化 大数据
Python在大数据处理中的应用实践
Python在大数据处理中扮演重要角色,借助`requests`和`BeautifulSoup`抓取数据,`pandas`进行清洗预处理,面对大规模数据时,`Dask`提供分布式处理能力,而`matplotlib`和`seaborn`则助力数据可视化。通过这些工具,数据工程师和科学家能高效地管理、分析和展示海量数据。
19 4
|
2天前
|
机器学习/深度学习 人工智能 算法
【机器学习】RLHF:在线方法与离线算法在大模型语言模型校准中的博弈
【机器学习】RLHF:在线方法与离线算法在大模型语言模型校准中的博弈
18 6
|
4天前
|
机器学习/深度学习 算法 搜索推荐
机器学习方法之强化学习
强化学习是一种机器学习方法,旨在通过与环境的交互来学习如何做出决策,以最大化累积的奖励。
18 2
|
4天前
|
机器学习/深度学习 搜索推荐
解决冷启动问题的机器学习方法和一个简化的代码示例
解决冷启动问题的机器学习方法和一个简化的代码示例
|
5天前
|
机器学习/深度学习 数据采集 运维
无监督学习是机器学习的一种重要方法
无监督学习是机器学习的一种重要方法
|
5天前
|
机器学习/深度学习 算法 TensorFlow
强化学习是一种通过与环境交互来学习最优行为策略的机器学习方法。
强化学习是一种通过与环境交互来学习最优行为策略的机器学习方法。
|
7天前
|
机器学习/深度学习 算法
机器学习方法分类
【6月更文挑战第14天】机器学习方法分类。
20 2
|
8天前
|
机器学习/深度学习 人工智能 算法
【机器学习】机器学习与AI大数据的融合:开启智能新时代
【机器学习】机器学习与AI大数据的融合:开启智能新时代
17 1
|
9天前
|
机器学习/深度学习 算法 Python
介绍文本分类的基本概念、常用方法以及如何在Python中使用机器学习库进行文本分类
【6月更文挑战第13天】文本分类是机器学习在数字化时代的关键应用,涉及文本预处理、特征提取和模型训练等步骤。常见方法包括基于规则、关键词和机器学习,其中机器学习(如朴素贝叶斯、SVM、深度学习)是主流。在Python中,可使用scikit-learn进行文本分类,例如通过TF-IDF和朴素贝叶斯对新闻数据集进行处理和预测。随着技术发展,未来将深入探索深度学习和多模态数据在文本分类中的应用。
27 2

热门文章

最新文章