2種資料庫覆寫式資料導入方法介紹

摘要：本文主要分享在大資料場景資料覆寫式導入資料庫的方法。

本文分享自華為雲社群《​​資料庫覆寫式資料導入方法介紹​​》，作者：along_2020 。

前言

衆所周知，資料庫中INSERT INTO文法是append方式的插入，而最近在處理一些客戶資料導入場景時，經常遇到需要覆寫式導入的情況，常見的覆寫式導入主要有下面兩種：

1、部分覆寫：新老資料根據關鍵列值比對，能比對上則使用新資料覆寫，比對不上則直接插入。

2、完全覆寫：直接删除所有老資料，插入新資料。

本文主要介紹如何在資料庫中完成覆寫式資料導入的方法。

部分覆寫

業務場景

某業務每天給業務表中導入大資料進行分析，業務表中某列存在主鍵，當插入資料和已有資料存在主鍵沖突時，希望能夠對該行資料使用新資料覆寫或者說更新，而當新老資料userid不沖突的情況下，直接将新資料插入到資料庫中。以将表src中的資料覆寫式導入業務表des中為例：

應用方案

方案一：使用DELETE+INSERT組合實作（UPDATE也可以，請讀者思考）

--開啟事務
START TRANSACTION;

--去除主鍵沖突資料
DELETE FROM des
USING src
WHERE EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);

--導入新資料
INSERT INTO des
SELECT *
FROM src
WHERE NOT EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);

--事務送出
COMMIT;      

方案優點：使用最常見的使用DELETE和INSERT即可實作。

方案缺點：1、分了DELETE和INSERT兩個步驟，易用性欠缺；2、借助子查詢識重，DELETE/INSERT性能受查詢性能制約。

方案二：使用MERGE INTO功能實作

MERGE INTO des USING src ON (des.userid = src.userid)
WHEN MATCHED THEN UPDATE SET des.b = src.b
WHEN NOT MATCHED THEN INSERT VALUES (src.userid,src.b);      

方案優點：MERGE INTO單SQL搞定，使用便捷，内部去重效率高。

方案缺點：需要資料庫産品支援MERGE INTO功能，目前Oracle、GaussDB(DWS)等資料庫已支援此功能，mysql的insert into on duplicate key也類似此功能。

完全覆寫

業務場景

某業務每天給業務表中導入一定時間區間的資料進行分析，分析隻需要導入時間區間的去除，不需要以往曆史資料，這種情況就需要使用到覆寫式導入。

應用方案

方案一：使用TRUNCATE+INSERT組合實作

--開啟事務
START TRANSACTION;

--清除業務表資料
TRUNCATE des;

--插入1月份資料
INSERT INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';

--送出事務
COMMIT;      

方案優點：簡單暴力，先清理在插入直接實作類似覆寫寫功能。

方案缺點：TRUNCATE清理業務表des資料時對表加8級鎖直到事務結束，在因資料量巨大而INSERT時間很長的情況下，des表在很長時間内是不可通路的狀态，業務表des相關的業務處于中斷狀态。

方案二：使用建立臨時表過渡的方式實作

--開啟事務
START TRANSACTION;

--建立臨時表
CREATE TABLE temp(LIKE desc INCLUDING ALL);

--資料先導入到臨時表中
INSERT INTO temp SELECT * FROM src WHERE TIME > '2020-01-01 00:00:00' AND TIME < '2020-02-01 00:00:00';

--導入完成後删除業務表des
DROP TABLE des;

--修改臨時表名temp->des
ALTER TABLE temp RENAME TO des;

--送出事務
COMMIT;      

方案優點：相比方案一，在INSERT期間，業務表des可以繼續被通路（老資料），即事務送出前分析業務可繼續通路老資料，事務送出後分析業務可以通路新導入的資料。

方案缺點：1、組合步驟較多，不易用；2、DROP TABLE操作會删除表的依賴對象，例如視圖等，後面依賴對象的還原可能會比較複雜。

方案三：使用INSERT OVERWRITE功能

INSERT OVERWRITE INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';      

方案優點：單條SQL搞定，執行便捷，能夠支援一鍵式切換業務查詢的新老資料，業務不中斷。

方案缺點：需要産品支援INSERT OVERWRITE功能，目前impala、GaussDB(DWS)等資料庫均已支援此功能。

總結