2 种数据库覆盖式数据导入方法介绍

本文分享自华为云社区《数据库覆盖式数据导入方法介绍》，作者：along_2020 。

前言

众所周知，数据库中 INSERT INTO 语法是 append 方式的插入，而最近在处理一些客户数据导入场景时，经常遇到需要覆盖式导入的情况，常见的覆盖式导入主要有下面两种：

1、部分覆盖：新老数据根据关键列值匹配，能匹配上则使用新数据覆盖，匹配不上则直接插入。

2、完全覆盖：直接删除所有老数据，插入新数据。

本文主要介绍如何在数据库中完成覆盖式数据导入的方法。

部分覆盖

业务场景

某业务每天给业务表中导入大数据进行分析，业务表中某列存在主键，当插入数据和已有数据存在主键冲突时，希望能够对该行数据使用新数据覆盖或者说更新，而当新老数据 userid 不冲突的情况下，直接将新数据插入到数据库中。以将表 src 中的数据覆盖式导入业务表 des 中为例：

应用方案

方案一：使用 DELETE+INSERT 组合实现（UPDATE 也可以，请读者思考）

--开启事务START TRANSACTION;
--去除主键冲突数据DELETE FROM desUSING srcWHERE EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);
--导入新数据INSERT INTO desSELECT *FROM srcWHERE 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.bWHEN 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';
--导入完成后删除业务表desDROP TABLE des;
--修改临时表名temp->desALTER 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 功能有直接诉求，后面有机会再继续介绍。

点击关注，第一时间了解华为云新鲜技术~