大資料與機器學習：實踐方法與行業案例.3.3 自動加載程式的資料庫設計

<b>3.3　自動加載程式的資料庫設計</b>

<b></b>

根據之前的設計，自動加載程式需要從資料庫配置表中擷取配置資訊，并不斷更新相關的狀态，表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）

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）

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）

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）

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仍未檢測到檔案，則認為檔案不存在

hdfsbasedir varchar(100) hadoop中資料檔案的存放基目錄，當db_type=hive時需指定

target_server表中，localbasedir配置項記錄了儲存檔案的本地基目錄，即檔案現在隻在本地後，将存放在該基目錄中，自動加載程式在下載下傳檔案時，會根據該檔案對應的表名和檔案日期在該基目錄下建立相應的子目錄，并将資料檔案儲存在對應的子目錄中。

paralftpcnt配置項指明了程式可以同時運作的最大下載下傳線程數，paralloadcnt、paralunzipcnt分别指明了并行加載的最大線程個數和并行解壓的最大線程個數。

ftploopinterval配置項用于聲明掃描ftp檔案伺服器的間隔時間，10000表示每10秒掃描一次ftp檔案伺服器。

hdfsbasedir用于hive加載時指明hdfs檔案存放的路徑。