大数据与机器学习：实践方法与行业案例.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文件存放的路径。