教你兩種資料庫覆蓋式資料匯入方法

lanyu發表於2021-09-11
摘要:本文主要介紹如何在資料庫中完成覆蓋式資料匯入的方法。

前言

眾所周知,資料庫中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)等資料庫均已支援此功能。

總結

隨著大資料的場景越來越多,資料匯入的場景也越來越豐富,除了本文介紹的覆蓋式資料匯入,還有其他諸如忽略衝突的INSERT IGNORE匯入等等其他的匯入方式,這些匯入場景可以以使用基礎的INSERT、UPDATE、DELETE、TRUNCATE來組合實現,但是也同樣會對高階的一鍵SQL功能有直接訴求,後面有機會再繼續介紹。

本文分享自華為雲社群《資料庫覆蓋式資料匯入方法介紹》,原文作者:along_2020 。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/2035/viewspace-2796216/,如需轉載,請註明出處,否則將追究法律責任。

相關文章