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

along_2020 发表于 2021/01/16 19:49:59 2021/01/16
【摘要】 本文主要分享在大数据场景数据覆盖式导入数据库的方法


前言

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

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区),文章链接,文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:cloudbbs@huaweicloud.com进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。