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

简介:
+关注继续查看

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文件存放的路径。

相关实践学习
简单用户画像分析
本场景主要介绍基于海量日志数据进行简单用户画像分析为背景,如何通过使用DataWorks完成数据采集 、加工数据、配置数据质量监控和数据可视化展现等任务。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
相关文章
|
7天前
|
SQL 存储 分布式计算
大数据Hive入门案例
大数据Hive入门案例
7 0
|
1月前
|
弹性计算 负载均衡 并行计算
实战案例分析:ECS在电商和大数据领域的应用
本文通过实际案例分析,深入探讨了云服务器ECS在电子商务和大数据领域的应用。在电子商务网站部署方面,我们介绍了如何使用ECS构建稳定的电商平台,包括弹性伸缩和负载均衡的实践。通过示例代码,读者可以了解如何创建ECS实例、配置负载均衡器,以及设置自动伸缩策略,以应对不同流量情况。
87 1
|
4月前
|
SQL 消息中间件 运维
《Apache Flink 案例集(2022版)》——5.数字化转型——工商银行-工商银行实时大数据平台建设历程及展望(1)
《Apache Flink 案例集(2022版)》——5.数字化转型——工商银行-工商银行实时大数据平台建设历程及展望(1)
102 0
|
4月前
|
消息中间件 SQL 大数据
《Apache Flink 案例集(2022版)》——5.数字化转型——工商银行-工商银行实时大数据平台建设历程及展望(2)
《Apache Flink 案例集(2022版)》——5.数字化转型——工商银行-工商银行实时大数据平台建设历程及展望(2)
100 0
|
4月前
|
SQL 存储 分布式计算
《Apache Flink 案例集(2022版)》——5.数字化转型——工商银行-工商银行实时大数据平台建设历程及展望(3)
《Apache Flink 案例集(2022版)》——5.数字化转型——工商银行-工商银行实时大数据平台建设历程及展望(3)
84 0
|
4月前
|
新零售 存储 供应链
案例酷 | 九阳股份:小家电,大数据,精准营销圈粉新生代
编者按: 在数字经济蓬勃发展的当下,千行百业都在紧抓时代机遇转型升级。在小家电领域,数智化转型成为家电行业的热词,家电业的头部企业也纷纷走上数智化“花路”。其中,作为小家电领导品牌之一,九阳从单品类起家,通过一路披荆斩棘,不断超越自己走到小家电领先地位,秉承健康和创新的核心DNA,九阳积极引领厨房小家电升级换代,创新营销玩法,推动数智化转型,品牌转型取得明显成效。 全文约4812字,建议阅读时间14分钟。
|
5月前
|
分布式计算 大数据 Spark
|
8月前
|
关系型数据库 MySQL 大数据
【大数据系列之MySQL】(二十七):子查询的经典使用案例
【大数据系列之MySQL】(二十七):子查询的经典使用案例
146 0
|
8月前
|
机器学习/深度学习 人工智能 分布式计算
阿里云大数据&AI 2022案例合集
阿里云2022大数据&AI精选产品案例合集,了解产品最新及最佳实践,助力企业实现降本增效。
阿里云大数据&AI 2022案例合集
|
9月前
|
数据采集 数据可视化 架构师
数据分析案例-大数据相关招聘岗位可视化分析
数据分析案例-大数据相关招聘岗位可视化分析
144 0
数据分析案例-大数据相关招聘岗位可视化分析
热门文章
最新文章
推荐文章
更多