ETL說的大量，破億就算大了，特別是MSSQL SERVER(下簡稱MSSQL)這種非MPP架構的資料庫。
本次的CASE是要從DB2 WH (下簡稱DB2) 將七億筆資料且去識別化後寫入MSSQL，
因其在DB2的整檔方式是delete+insert，想在MSSQL用相同的方式udpate+insert處理。
流程如下:
- 透過openquery + bcp queryout、bcp in將DB2的前一日資料倒到MSQL 的temp table，約百萬筆
- 接著在MSSQL將temp Table用merge語法更新主table的七億筆資料
卻發生第二步執行過久而time out，且還不知道會跑多久的問題
要怎麼解決呢？
經觀察，在MSSQL執行百萬筆對七億筆的資料比對太耗效能，實測可能要跑八小時以上。
解決方案:
- 方案一: 縮小資料範圍，只保留近幾年的資料，但因資料的屬性是不確定會update多以前的資料，所以被user打槍。
- 方案二: MSSQL切PARTITION，有助於查找比對，但反而會影響寫入。
突然冒出個想法，那不如把耗效能的部份交給DB2去處理？
和同事討論，得知DB2有EXTERNAL TABLE的作法，可以直接產生像bcp queryout的文字檔，後接bcp in，不就解決了嗎？
或許可解決MSSQL執行openquery + bcp queryout 破7億筆要跑超過十小時的情況。
語法如下，實測一小時搞定
exec MASTER.dbo.xp_cmdshell 'echo CREATE EXTERNAL TABLE ''D:\FTP\xx.TXT'' USING (REMOTESOURCE ''JDBC'' DELIMITER ''^|'' RECORDDELIM ''\r\n'' NullValue '''' TIMESTAMP_FORMAT ''YYYY-MM-DD HH.MI.SS'' IncludeHeader FALSE escapechar ''\'' CTRLCHARS ''TRUE'') AS SELECT 去識別欄位,* FROM DW.xx; > D:\FTP\xx.sql'
exec MASTER.dbo.xp_cmdshell 'db2cli execsql -dsn [DB2] -inputsql D:\FTP\xx.sql'
exec MASTER.dbo.xp_cmdshell 'bcp db.dbo.xx in D:\FTP\xx.txt -U帳 -P密 -S[MSSQL的IP] -c -C 65001 -m 100 -t "|" -e D:\FTP\xx.log''