ETL: Some hint for Full Table Data Migration between legacy and modern

 前一些時間，我正在進行資料系統遷移到新系統的作業，並對此簡單做一些方法的紀錄。

任務執行規劃

如果需要做到舊系統轉新系統，一開始一定要評估舊系統停機損失，假設如果對方只能承受 3 個小時的損失，那麼就必須盡早開始在工程面思考是否有可能將 Migration 在 1.5 小時內完成 (一半的時間)，因為一但開始做遷移，舊系統理論上不應該再有新的一筆資料出現。

工欲善其事，必先利其器

在這項任務中使用了多項工具來達成這個需求:

• MySQL Workbench (開發 SQL 執行流程、主要工作 SQL IDE，最後被玩到 UI 爛掉)
• DBeaver (研究資料欄位、研究整個系統邏輯)
• SSMS (用於匯出 mdf, log 資料的 Database 到 xls 再轉出到 csv)
• Table Plus (用於整批匯入 csv 進 Table 中，速度絕佳快)

電腦開發端所安裝的 MySQL 一定要和資料庫版本相同，特別是 Mysqldump 工具一定要與資料庫版本相同， 5.7, 8 互相用 mysqldump 是會出問題的!

整理 Legacy Data 的方式

兩個系統本身並不互通，你可能會需要匯出整批資料出來，並且整理這些資料，你可以把資料按照 CSV 格式倒入新系統的資料庫的某個欄位:

LegacyBookstoreBooks

LegacyBookstoreMemberships

...

諸如此類的表，裡面先開好欄位定義，都是粗略開啟即可。

清洗資料、日期、格式

當你準備匯入時，你會開始發現你的資料甚至出了很大的問題，有些欄位是 '\N'，有些欄位是 ''，有些欄位是 NULL，有些欄位多了某個詭異的字元，又或是日期格式錯誤，甚至是整個 CSV 就出了狀況。

理想情況是少數資料就人工修復，如果有一點多，你可能需要寫正規表達式來解決它。

於是，你可能會用盡你所想的: 用 Node.js 寫腳本，處理這些 CSV，替換錯誤資料、非法字元，你可能會針對很多個案去研究到底這個 CSV 是不是正確的。

使用 RegExp 可以用 $1, $2 針對搜尋結果做 regex replace string，是一個還蠻不錯的解決方案。

增加額外必要欄位用來對應

有的時候，假設書店在全台有很多店面，每個店面都是使用獨立資料匯入，因此你甚至不會知道這個 csv 是屬於哪家書店，此時你應該在匯入時幫這些資料在 SQL 欄位中，加上 StoreLocation: TPE, KHH, RMTP....etc 各種店名。

除了這種情況外，你可能也要對那些新系統的表格標記一些 isLegacy 字樣的欄位，甚至是 legacyDataCategory，這些都有助於讓你在匯入這些資料後，特別標記資料以便處理。

匯入方式

匯入 CSV 有幾種方案，一種是透過 Node.js 讀取 csv 一行一行匯入，轉型，這麼做的話，也許你第一個碰到的就是資料庫編碼問題，你可能要試圖更改 Table Collection 或 DB Collection，像是原本是 utf8_general_ci, utf8mb4_general_ci 兩種的替換，尤其是使用 utf8mb4_general_ci 也可能遇到 Table 中 Column 欄位資料大小上限的問題。

再來，你也許會使用 Batch 匯入，這必須考慮到每次匯入的 Batch Size 多少合適，因為一筆一筆匯入，就等於一筆一筆 INSERT，這會讓速度變很慢，所以都會一次匯入很多，此時就必須研究一次匯入多少合適，以及最終也要考慮一批 Transaction 可以接收的大小，你可能也必須分批做 Transaction 匯入。

了解邏輯，盡早發現資料錯誤

匯入這些資料後，就開始研究 Column Name 與其他相關聯欄位的 Colum Name 是什麼，你必須從【舊的系統】開始了解，你可以從【匯入的邊界需求】開始定義塑形，你一定要很清楚你需要匯出哪一些資料，作為整個遷移任務的基礎，否則你將會滿無目的的處理資料。

了解完需求再開始尋找舊系統欄位中的關聯性 (Relations) 可能會是面對龐大舊型系統的一個出發點。

當你了解這個邏輯之後，你需要嘗試做出幾個測試的 SELECT 和 JOIN SQL 腳本來證明這些邏輯是對的，例如分期刷卡清單、會員期間資料，舊需要好好的與主管或客戶進行研判。

總之，越早發現錯誤是越好的!

建立 INDEX

匯入完資料後，你可能嘗試幾種 SELECT 都會出狀況，你會發現你瘋狂引發 Full-Table Scan，使用字串來搜尋的話，通常都是使用 Full-Text Searching 去做，尤其是 LIKE 語法。

最經典的例子就是我需要對應發票號碼所使用的付款紀錄，此時兩張 Table 就完全只有 String - String 的對應關係，那會使得整個 JOIN 變得有夠夭壽慢。

解方就是對這些舊資料加上必要的 INDEX，如果我只有發票要對應，我則只要分別在這兩張表的 ReceiptNo 加上 Index:

CREATE INDEX idx_receipts_recepitno ON Receipts (RecepitNo) USING BTREE KEY_BLOCK_SIZE = 65535;
CREATE INDEX idx_payments_receiptno ON Payments (ReceiptNo) USING BTREE KEY_BLOCK_SIZE = 65535;

Key block size 在這裡可能不能開太小，這些號碼對應通常都是很大量的，尤其如果發票是特別的欄位:

RecepitPrefix, ReceiptNo，像是: "RX", "1020120120" 這種就會更麻煩，因為你每一次查詢時可能會寫成:

SELECT payment.id FROM Payments payment 
LEFT JOIN Receipts receipt ON payment.ReceiptNo = CONCAT(receipt.ReceiptPrefix, receipt.ReceiptNo);

你要是不設定 Index 為:

CREATE INDEX idx_receipts_recepitno_receiptprefix ON Receipts (ReceiptPrefix, RecepitNo) USING BTREE KEY_BLOCK_SIZE = 65535;

你可能真的會直接跑不出結果，那樣的 JOIN 量會蠻大的。

CREATE INDEX idx_ordeNo_location ON Orders (OrderNo, location) USING BTREE KEY_BLOCK_SIZE = 2048; -- better up to 65535
CREATE INDEX idx_ordeNo_location ON Payments (OrderNo, location) USING BTREE KEY_BLOCK_SIZE = 2048; -- better up to 65535

我自己跑過使用 65535 的 b-tree key block size 就會很正常，使用 2048, 4096 都太小了。

交集映射優化

要處理各種 JOIN 出錯的著手點，一定就是要看 Explain Tool， MySQL Workbench 可以直接快速查詢，還有圖形化介面:

沒有 Tool 也沒關係，只要在任何 SQL 語法前面加上 EXPLAIN 就可以看這段查詢的效能、使用預設索引。

從交集取映射時，我幾乎沒有用過 JOIN ON ... OR 這個 OR 的語法，使用 OR 可能隨時都會引發全表掃描 (Full-table scan)，詳情觸發機制其實有蠻多人寫過的，可以上網找找。

基本上 JOIN 篩選率到 100，基本上就是需要調整查詢效能了，否則未來會很痛苦。

建立映射表優化 Mapping Table (Many-To-Many Table)

假設不慎，你處理的資料不管怎麼優化都需要跑很久，甚至要用到 Batch (透過 Offset, Limit 來一批一批找)，此時可以借助 Many-to-many table 的概念，預先建立一張 Mapping Table，來建立你想要得到的資訊表，比方說我想要拿這張表去對應發票付款紀錄:

CREATE TABLE ReceiptsToPayments (
		ReceiptId INT NOT NULL PRIMARY KEY
		PaymentId INT NOT NULL
);

也許可以再加一個 Index，不過此處用途是只篩選出一些 Receipts 與 Payments 找出來的表:

INSERT INTO ReceiptsToPayments (ReceiptId, PaymentId)
SELECT r.id, p.id FROM Receipts r
INNER JOIN Payments p ON p.ReceiptNo = r.ReceiptNo;

或是

INSERT INTO ReceiptsToPayments (ReceiptId, PaymentId)
SELECT r.id, p.id FROM Receipts r, Payments p
WHERE r.ReceiptNo = p.ReceiptNo;

假設真的要跑很久，就這麼做吧，當你的 Mapping 表做出來之後，之後很多的對應就會非常方便，不過在這小節一直沒有提到，Mapping 表是根據需求而做的，假設你很頻繁需要用到這種方式從 Receipts 找到 Payments，你就必須要建立，因為它可以省下你很多時間。

那如果你無法 JOIN 出來怎麼辦?

請見下一節尾。

不 JOIN 情況下挑選擇集合元素

很多時候你可能只是想篩選 A 資料，只是需要借助 B 資料來處理，我打造一個情境，我只要沒有經過折讓或作廢的發票的付款紀錄，我的本體是紀錄，不是要發票，有的時候會寫成 INNER JOIN 的形式，也許這不一定有好處，這也可能會引發索引以及 JOIN 效能問題。

SELECT p.id, p.BookstoreLocation FROM Receipts r
LEFT JOIN Payments p ON p.ReceiptNo = r.ReceiptNo AND r.BookstoreLocation = p.BookstoreLocation
WHERE r.isVoid = FALSE AND r.isAllowance = FALSE; -- 沒有折讓以及作廢

此時，你也可以採取不同的方案進行: 不要 JOIN 的挑選元素，而是直接挑元素。

SELECT p.id, p.BookstoreLocation FROM Payments p
WHERE (p.ReceiptNo, p.BookstoreLocation) IN (SELECT r.ReceiptNo, r.BookstoreLocation FROM Receipts r WHERE r.isVoid = FALSE AND r.isAllowance = FALSE); 

使用多個 WHERE IN，位置對齊後直接查出付款紀錄，效果更快。

那麼回到上一節說的，想要做 Many-to-many mapping table 可是透過 JOIN 就是無法跑出結果，怎麼辦?

下面這個例子，就是使用這一節的方法，你可以這麼看 A, B 範例，A 要加入 B 的多項資訊整合的表，但是無法用 JOIN 把資料帶出來，所以做了部分 INSERT，再 UPDATE，首先這是表的格式:

CREATE TABLE A_B_WAIT_TO_JOIN_TO_C(
	A_ID
	A_NAME
	B_ID
	B_RECEIPT_NO
)

因為 join 不出資料，我們可以使用挑選法，先挑出 A 的資料。

INSERT INTO A_B_WAIT_TO_JOIN_TO_C
	SELECT ID, NAME FROM A a WHERE (a.a1, a.a2) IN (SELECT b.a1, b.a2 FROM B b)

此時 INTO A_B_WAIT_TO_JOIN_TO_C 這張表應該就只有 A 的資料，那麼你再透過 Update 的方式帶進去，就完成了:

UPDATE A_B_WAIT_TO_JOIN_TO_C abc
INNER JOIN B b ON abc.A_ID = b.ID
SET abc.B_ID = b.ID, abc.B_RECEIPT_NO = b.RecepitNo;

這樣下來，就用 INSERT 加上 UPDATE 補完整張 Many-To-Many 的 Mapping 表。

JOIN 原則問題

LEFT JOIN 如果在隔壁資料沒有的狀況下，也會帶出一筆空的資料 NULL 一整排，有些功能可能就是想知道隔壁是不是空的，讓我們撇除掉這種必要的狀況，在一般情況下是否需要使用 LEFT JOIN，對我來說原則已經下好，就應該是使用 INNER JOIN，只有很多 JOIN 的情況下，你要去 Debug 尋找哪一個 INNER JOIN 開始找不到人時，可以用 LEFT JOIN 去除錯。

使用 INNER JOIN 對系統的好處是保證有資料，沒有資料就不會進去，而工程師應該做的是確保 INNER JOIN 不到的東西錯誤的原因，要一一排除。

也許使用 LEFT JOIN 手動檢查完之後，改成 INNER JOIN 可能會好一些。

要不要使用 Temporary Table ，還是用一般 Table

有的時候在做這系列 Migrations 時會希望有一些欄位紀錄，像是上面提到的 Mapping Table，是否建立時就直接採用 Temporary Table 就好?

其實是不建議的，因為 Temporary 無法重複 Join Table，而且通常做 Migrations 時很容易 Deadlock 或整個資料庫重開機，那麼你的整個 Temp Table 就會隨之消失，除非你要使用 Procedure 並用於紀錄簡單資料 Array，那或許是可以直接當一種 Array 變數使用。

總之，使用一般 Table 建立出來才是上策。

區分環境的必要性

當整理完目標需要匯入的資料表 A, B, C 之後，我當初正想著先開一個 A_ready, B_ready, C_ready，這三個資料表先做預匯入，最後再一次倒入，但其實這是錯誤的。

在一個強關聯性的 RDBMS，所有的 Foreign Key 都必須在 Insert 當下建完，雖然改 Auto_Increment 也可以做到，但我完全建議直接照著原本資料表匯入就好了。

真實環境就是最好的演練，不要刻意區分前後環境，要善用 Transaction, Savepoint 這兩個好工具，事務處理會幫助你的。

在匯入時使用的事務隔離級別直接使用 Serializable 來處理，直接 ROLLBACK, COMMIT 就可以做。

START TRANSACTION; BEGIN;

UPDATE A_B_WAIT_TO_JOIN_TO_C abc
INNER JOIN B b ON abc.A_ID = b.ID
SET abc.B_ID = b.ID, abc.B_RECEIPT_NO = b.RecepitNo;


SAVEPOINT update_all_list;

DELETE FROM A_B_WAIT_TO_JOIN_TO_C;

ROLLBACK update_all_list; -- 直接退回到剛 UPDATE 的狀態

-- COMMIT; 這段可以直接 COMMIT 資料

如果你是要執行一大堆腳本，最好先調整整個 GLOBAL 的交易方式，你可以先查詢 GLOBAL 的事務處理級別是什麼: 

SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;

通常不會是 Serializable，要手動改它，也可以順便將以下設定一併調整:

set sql_safe_updates=0; -- 可以做全表更新
set @@autocommit = false; -- 不要自動 COMMIT，改手動 COMMIT
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 把全域事務處理級別改 SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 把連線者的事務處理級別改 SERIALIZABLE

使用 Transaction 的好處就是你可以操作 CRUD，操作完可以一直在連線狀態做 SELECT 查詢看資料對不對，但缺點就是只能在連線端看，當你打開應用程式連上這個資料庫，是不會看到這些資料的，只能肉眼檢驗正確性，好處是不會污染資料庫，你可以隨時 ROLLBACK 回去，甚至斷線資料也不會 COMMIT 上去。

不過仍要注意 Transaction 不支援某些語法，只要你一輸入就會被立刻 COMMIT 污染資料庫:

DROP, CREATE ... (CREATE TABLE, CREATE PROCEDURE, CREATE TEMORARY...etc), ALTER ...

這系列語法稱作 DDL，MySQL 是不支援 DDL 的交易處理的。

使用 Procedure, Function

當你的手續一多的時候，將重複的任務包成 Procedure, Function 有助於加速開發，但還是需要注意不要過度使用 Procedure, Function，兩者差異在於 Function 會有回傳值，Procedure 不會有，較適合用來處理一系列的任務。

特別要注意，在 Procedure, Function 內部使用 DDL (Create...) 一樣會造成交易 Transaction 直接被 Commit，而且 Create Procedure, Create Function 時也要在最前面先做，不要在做了一堆 INSERT, UPDATE 之後才開始 Create Procedure，那樣會直接讓前面資料污染，被 Commit。

以下示範 Procedure 執行迴圈讀取:

DROP PROCEDURE IF EXISTS DO_PROCEDURE;
DELIMITER //
CREATE PROCEDURE DO_PROCEDURE()
BEGIN
    -- 迴圈參數
    DECLARE j INT DEFAULT 0;
    DECLARE total_count  INT DEFAULT 0;
    
    -- Cursor 需要的變數事先宣告
    DECLARE id INT;
    DECLARE orderId INT;
    DECLARE orderNo VARCHAR(255);
    
    -- 宣告一個 Cursor
    DECLARE myCursor CURSOR FOR
		SELECT 
			a.id,
			a.orderNo,
			b.orderId
		FROM A a
		LEFT JOIN B b ON b.OrderId = a.id
		ORDER BY a.createdAt ASC;
        
	-- 從這邊之後就不可以再宣告變數了
    
    OPEN ctCursor;
    
	-- 事先知道整個迴圈的大小，幾乎用跟上面 cursor 一樣的語法
    SELECT COUNT(*) AS total_count
		FROM A a
		LEFT JOIN B b ON b.OrderId = a.id
	INTO total_count; -- SELECT 完之後 INTO total_count 這個變數。
	
    -- 秀出來看看
    SELECT total_count;
	
    
    -- 使用倒數的方式來做迴圈
   	SOMELOOP: LOOP-- 這裡可以為 Loop 命名
        -- 如果減到 0 就跳出迴圈
		IF total_count = 0 THEN
			LEAVE SOMELOOP;
		END IF;
		SET total_count = total_count - 1;
        
        
        -- 每次迭代一次 Cursor 下一筆 row，丟到預先 Declare 的變數上。
		FETCH myCursor INTO id, orderNo, orderId;
		
        
        IF id <> IS NULL THEN
			SET j = 1;
            SET temp_order_id = orderId;
		END IF;
		
                
        INSERT INTO C (A, B, C) 
					VALUE (orderId, j, orderNo);
        
        -- 內部計數變數，這是正序新增的
		SET j = j + 1;
        
    END LOOP SOMELOOP;
    
    -- 最後關閉 cursor
    CLOSE myCursor;
END
//
DELIMITER ;

要使用時，直接寫 CALL DO_PROCEDURE() 就可以執行了。

這裡特別注意 //, DELIMITER, 的意思是要分隔每一行，在某些 SQL Editor 要寫 Procedure 要加上這串，如果你的 IDE 不會出錯，則可以自己拿掉。

而 Function 的建立也不會很複雜，範例如下:

DROP FUNCTION IF EXISTS `CHECK_ORDER_EXISTS`;
CREATE FUNCTION `CHECK_ORDER_EXISTS`(given_orderNo VARCHAR(255))
RETURNS BOOL NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN
	DECLARE _isExists BOOLEAN DEFAULT FALSE;

	SELECT o.id IS NOT NULL INTO _isExists FROM Orders o WHERE o.orderNo = given_orderNo;

	RETURN _isExists;
END;

二級索引建立方法與 Procedure 二級索引建立法

想像以下的情境，你的關聯性資料庫中有兩張表，付款紀錄 Payments，付款項目 PaymentItems，付款項目裡面會有商品子 id 順序，也有原來的 Payments 紀錄，於是你將在匯入資料時碰到如何建立二級索引的問題。

你有三個辦法可以解決:

1. 使用查詢內變數建構 (適合用在 MySQL 5.7 的做法)

SELECT IF(@last_payment_id=p.id, @row_idx := @row_idx + 1, @row_idx:=0) AS `index`, /* 這個設定一定要在最後面 */ @last_payment_id:=p.id AS PaymentId, '假商品名稱', tpi.price ...
FROM Payments p
INNER JOIN TestPaymentItem tpi ON tpi.PaymentId = p.id,  -- 逗號就會分隔查詢，接下來宣告內變數
(SELECT @row_idx := 0) AS row_idx, (SELECT @last_payment_id := 0) AS last_payment_id
ORDER BY p.PaymentId DESC; /*一定要 order by*/

這個做法的詳情可以參考 [1]。

2. 使用查詢內變數建構 (適合用在 MySQL 8 的做法)

直接使用 ROW_NUMBER() OVER PARTITION BY 的語法，詳情可以參考 [1]

3. 使用 Procedure 迭代變數

參考上面的 使用 Procedure, Function 章節，我在變數中有寫到 SET j，你可以使用這個代替索引。

注意接收 VARCHAR 的編碼問題

無論是執行 SQL 匯入，或是用 Node.js 把 CSV 一行一行匯入，你可能都會碰到編碼問題，這個時候你可能需要去變更資料庫的編碼格式:

ALTER SCHEMA your_db_name DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

注意在 Procedure 的接收參數上，你可以這麼改接收參數設定編碼:

CREATE PROCEDURE `DO_SOMETHING`(IN Region CHAR(10) CHARSET utf8mb4)

從這邊可以嘗試去接收 utf8mb4 的字元。

建立還原資料手續的 Shell Scripts

如果這是一連串很複雜的工作，我會建議要建立一個 Shell Scripts 代替你處理各種工作，能自動化的就全部自動化，而且要時常跑自動化看看有沒有問題。

假設你要讓機器重開機在繼續做下去，你可以讓 Shell Scripts 做這幾行:

sshpass -p rootroot ssh [email protected] reboot &> /dev/null

sleep 15

這幾行可以讓機器等待 15 秒後再繼續進行。

你可以新增一個 bash 檔案，填入:

#!/bin/sh

set -e

read -p "準備好了嗎?: (Y/n)" any_variable_here

... 填入各種指令手續

基本上 read 就是在等待使用者的回應，你可以利用 read 來卡住每一個需要人工步驟的行為。

另一個 tip 是，你要在這一系列行為中，最好可以多次備份資料庫，安插這樣的指令下去:

mysqldump --routines -h xxx.xxx.xxx.xxx -u admin -p預先填入的密碼 your_db_name > ./xxx.sql

以及如果你會碰到需要輸入交互式命令的程式，像是 python，你可以讓 shell 自動輸入執行，再跳出:

echo "print("From Python")\nexit()\n" | python

基本上， \n 就等同於按下 Enter 的意思。

死結 Deadlock

在這邊因為我沒有遇到絕對的死結問題，只要 SELECT, UPDATE, 一卡住，通常我就會直接重開機，然後把 SQL 腳本分開，斷在那個會死結的行數那邊，然後先讓前面 COMMIT 後重新開機，再接下去執行，就不會有問題。

執行 SQL 使用的 character set

上面有隱約提到編碼的問題，假設你要使用 mysql < xxx.sql 的方式執行 SQL，請務必注意要把編碼指定好:

cat "migration.sql" | mysql -h localhost -u admin -p your_db_name --default-character-set=utf8

匯入匯出

使用 mysqldump 一定要注意 mysql 版本跟你裝的 mysqldump 版本有沒有一致，不一致絕對會發生不相容的狀況，請務必注意。

這是一個匯出的指令:

mysqldump --routines -h xxx.xxx.xxx.xxx -u admin -p預先填入的密碼 your_db_name > ./xxx.sql

這是一個匯入的指令:

mysql  -h xxx.xxx.xxx.xxx -u admin -p預先填入的密碼 your_db_name < xxx.sql

如果要進行匯入，請小心翼翼的 DROP DATABASE 再 CREATE DATABASE 一次；

以及如果你是從遠端匯出，你可以用 scp 傳回來傳過去。

**額外提醒，請記得如果你的資料庫有 FUNCTION, VIEW, PROCEDURE，請事先備份下來，有的時候 mysqldump 不會備份到這些東西，很有可能就會直接消失。

要等到匯入之後再手動自行加回去。

References:

1. https://stackoverflow.com/questions/54046541/mysql-how-to-generate-row-index-rank-in-select