众所周知,数据库中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功能有直接诉求,后面有机会再叙述。