眾所周知，數(shù)據(jù)庫中INSERT INTO語法是append方式的插入，而最近在處理一些客戶數(shù)據(jù)導(dǎo)入場景時，經(jīng)常遇到需要覆蓋式導(dǎo)入的情況

常見的覆蓋式導(dǎo)入主要有下面兩種：

1、部分覆蓋：新老數(shù)據(jù)根據(jù)關(guān)鍵列值匹配，能匹配上則使用新數(shù)據(jù)覆蓋，匹配不上則直接插入。

2、完全覆蓋：直接刪除所有老數(shù)據(jù)，插入新數(shù)據(jù)。

本文主要介紹如何在數(shù)據(jù)庫中完成覆蓋式數(shù)據(jù)導(dǎo)入的方法。

部分覆蓋

業(yè)務(wù)場景

某業(yè)務(wù)每天給業(yè)務(wù)表中導(dǎo)入大數(shù)據(jù)進(jìn)行分析，業(yè)務(wù)表中某列存在主鍵，當(dāng)插入數(shù)據(jù)和已有數(shù)據(jù)存在主鍵沖突時，希望能夠?qū)υ撔袛?shù)據(jù)使用新數(shù)據(jù)覆蓋或者說更新，而當(dāng)新老數(shù)據(jù)userid不沖突的情況下，直接將新數(shù)據(jù)插入到數(shù)據(jù)庫中。以將表src中的數(shù)據(jù)覆蓋式導(dǎo)入業(yè)務(wù)表des中為例：

應(yīng)用方案

方案一：使用DELETE+INSERT組合實現(xiàn)（UPDATE也可以，請讀者思考）

--開啟事務(wù)
START TRANSACTION;
--去除主鍵沖突數(shù)據(jù)
DELETE FROM des
USING src
WHERE EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);
--導(dǎo)入新數(shù)據(jù)
INSERT INTO des
SELECT *
FROM src
WHERE NOT EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);
--事務(wù)提交
COMMIT;

方案優(yōu)點(diǎn)：使用最常見的使用DELETE和INSERT即可實現(xiàn)。

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

方案二：使用MERGE INTO功能實現(xiàn)

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);

方案優(yōu)點(diǎn)：MERGE INTO單SQL搞定，使用便捷，內(nèi)部去重效率高。

方案缺點(diǎn)：需要數(shù)據(jù)庫產(chǎn)品支持MERGE INTO功能，當(dāng)前Oracle、GaussDB(DWS)等數(shù)據(jù)庫已支持此功能，mysql的insert into on duplicate key也類似此功能。

完全覆蓋

業(yè)務(wù)場景

某業(yè)務(wù)每天給業(yè)務(wù)表中導(dǎo)入一定時間區(qū)間的數(shù)據(jù)進(jìn)行分析，分析只需要導(dǎo)入時間區(qū)間的去除，不需要以往歷史數(shù)據(jù)，這種情況就需要使用到覆蓋式導(dǎo)入。

應(yīng)用方案

方案一：使用TRUNCATE+INSERT組合實現(xiàn)

--開啟事務(wù)
START TRANSACTION;
--清除業(yè)務(wù)表數(shù)據(jù)
TRUNCATE des;
--插入1月份數(shù)據(jù)
INSERT INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';
--提交事務(wù)
COMMIT;

方案優(yōu)點(diǎn)：簡單暴力，先清理在插入直接實現(xiàn)類似覆蓋寫功能。

方案缺點(diǎn)：TRUNCATE清理業(yè)務(wù)表des數(shù)據(jù)時對表加8級鎖直到事務(wù)結(jié)束，在因數(shù)據(jù)量巨大而INSERT時間很長的情況下，des表在很長時間內(nèi)是不可訪問的狀態(tài)，業(yè)務(wù)表des相關(guān)的業(yè)務(wù)處于中斷狀態(tài)。

方案二：使用創(chuàng)建臨時表過渡的方式實現(xiàn)

--開啟事務(wù)
START TRANSACTION;
--創(chuàng)建臨時表
CREATE TABLE temp(LIKE desc INCLUDING ALL);
--數(shù)據(jù)先導(dǎo)入到臨時表中
INSERT INTO temp SELECT * FROM src WHERE TIME > '2020-01-01 00:00:00' AND TIME < '2020-02-01 00:00:00';
--導(dǎo)入完成后刪除業(yè)務(wù)表des
DROP TABLE des;
--修改臨時表名temp->des
ALTER TABLE temp RENAME TO des;
--提交事務(wù)
COMMIT;

方案優(yōu)點(diǎn)：相比方案一，在INSERT期間，業(yè)務(wù)表des可以繼續(xù)被訪問（老數(shù)據(jù)），即事務(wù)提交前分析業(yè)務(wù)可繼續(xù)訪問老數(shù)據(jù)，事務(wù)提交后分析業(yè)務(wù)可以訪問新導(dǎo)入的數(shù)據(jù)。

方案缺點(diǎn)：1、組合步驟較多，不易用；2、DROP TABLE操作會刪除表的依賴對象，例如視圖等，后面依賴對象的還原可能會比較復(fù)雜。

方案三：使用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';

方案優(yōu)點(diǎn)：單條SQL搞定，執(zhí)行便捷，能夠支持一鍵式切換業(yè)務(wù)查詢的新老數(shù)據(jù)，業(yè)務(wù)不中斷。

方案缺點(diǎn)：需要產(chǎn)品支持INSERT OVERWRITE功能，當(dāng)前impala、GaussDB(DWS)等數(shù)據(jù)庫均已支持此功能。

總結(jié)

隨著大數(shù)據(jù)的場景越來越多，數(shù)據(jù)導(dǎo)入的場景也越來越豐富，除了本文介紹的覆蓋式數(shù)據(jù)導(dǎo)入，還有其他諸如忽略沖突的INSERT IGNORE導(dǎo)入等等其他的導(dǎo)入方式，這些導(dǎo)入場景可以以使用基礎(chǔ)的INSERT、UPDATE、DELETE、TRUNCATE來組合實現(xiàn)，但是也同樣會對高級的一鍵SQL功能有直接訴求，后面有機(jī)會再敘述。

最新評論