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

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

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

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

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

部分覆蓋

業(yè)務(wù)場(chǎng)景

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

應(yīng)用方案

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

--開(kāi)啟事務(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)：使用最常見(jiàn)的使用DELETE和INSERT即可實(shí)現(xiàn)。

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

方案二：使用MERGE INTO功能實(shí)現(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ù)庫(kù)產(chǎn)品支持MERGE INTO功能，當(dāng)前Oracle、GaussDB(DWS)等數(shù)據(jù)庫(kù)已支持此功能，mysql的insert into on duplicate key也類似此功能。

完全覆蓋

業(yè)務(wù)場(chǎng)景

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

應(yīng)用方案

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

--開(kāi)啟事務(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)：簡(jiǎn)單暴力，先清理在插入直接實(shí)現(xiàn)類似覆蓋寫功能。

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

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

--開(kāi)啟事務(wù)
START TRANSACTION;
--創(chuàng)建臨時(shí)表
CREATE TABLE temp(LIKE desc INCLUDING ALL);
--數(shù)據(jù)先導(dǎo)入到臨時(shí)表中
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;
--修改臨時(shí)表名temp->des
ALTER TABLE temp RENAME TO des;
--提交事務(wù)
COMMIT;

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

方案缺點(diǎn)：1、組合步驟較多，不易用；2、DROP TABLE操作會(huì)刪除表的依賴對(duì)象，例如視圖等，后面依賴對(duì)象的還原可能會(huì)比較復(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)：?jiǎn)螚lSQL搞定，執(zhí)行便捷，能夠支持一鍵式切換業(yè)務(wù)查詢的新老數(shù)據(jù)，業(yè)務(wù)不中斷。

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

總結(jié)

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

最新評(píng)論