资讯专栏INFORMATION COLUMN

Bulk Upsert for MySQL & PostgreSQL

BakerJ / 2316人阅读

摘要:什么是简而言之,就是,不存在就插入,存在就更新。当然也有功能,和的类似。然而,命令不支持,这使一些增量的工作非常不方便。五把表清空上面过程确实很麻烦,如果使用的话,只需要简单的相关链接

什么是 Upsert
"UPSERT" is a DBMS feature that allows a DML statement"s author to atomically either insert a row, or on the basis of the row already existing, UPDATE that existing row instead, while safely giving little to no further thought to concurrency. One of those two outcomes must be guaranteed, regardless of concurrent activity, which has been called "the essential property of UPSERT".

简而言之,就是,不存在就插入,存在就更新。

单记录 Upsert

MySQL有INSERT...ON DUPLICATE KEY UPDATE语法,可以实现Upsert:

INSERT INTO customers (id, first_name, last_name, email) VALUES (30797, "hooopo1", "wang", "hoooopo@gmail.com") 
ON DUPLICATE KEY UPDATE 
first_name = VALUES(first_name), last_name = VALUES(last_name);

PostgreSQL 从 9.5 也有了INSERT ... ON CONFLICT UPDATE语法,效果和 MySQL 类似:

INSERT INTO customers (id, first_name, last_name, email) VALUES (30797, "hooopo1", "wang", "hoooopo@gmail.com") 
ON CONFLICT(id) DO  UPDATE 
SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name;
批量 Upsert

之前研究 MySQL 里如何插入最快 ,里面提到 LOAD INFILE 方式批量插入,并且 MySQL 的 bulk insert 是支持 REPLACE 语意的,即批量插入的同时还可以 upsert

LOAD DATA LOCAL INFILE "/Users/hooopo/data/out/product_sales_facts.txt"
REPLACE INTO TABLE product_sale_facts FIELDS TERMINATED BY "," (`id`,`date_id`,`order_id`,`product_id`,`address_id`,`unit_price`,`purchase_price`,`gross_profit`,`quantity`,`channel_id`,`gift`)

当然 PostgreSQL 也有 Copy功能,和 MySQL 的 LOAD INFILE 类似。然而,copy 命令支持 Upsert,这使一些增量 ETL 的工作非常不方便。

不过有一种利用 staging 表的方式实现 bulk upsert,大致步骤如下:

一. 目标表

二. 把增量数据批量插入中间表

CREATE TABLE IF NOT EXISTS staging  LIKE customers INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES;
COPY staging (id, email, first_name, last_name)
        FROM STDIN
          WITH
            DELIMITER ","
            NULL "N"
            CSV;

三. 把目标表中与 staging 表冲突部分删掉

DELETE FROM customers
USING staging
WHERE customers.id = staging.id

四. 把 staging 表批量插入到目标表,因为冲突部分已经删掉,所以这步不会有任何冲突。

INSERT INTO customers (SELECT * FROM staging);

五. 把 staging 表清空

TRUNCATE TABLE staging;

上面过程确实很麻烦,如果使用 kiba-plus 的话,只需要简单的 DSL:

destination Kiba::Plus::Destination::PgBulk2, { :connect_url => DEST_URL,
                                :table_name => "customers",
                                :truncate => false,
                                :columns => [:id, :email, :first_name, :last_name],
                                :incremental => true,
                                :unique_by => :id
                              }

相关链接:

https://wiki.postgresql.org/w...

http://www.silota.com/blog/am...

https://hashrocket.com/blog/p...

http://docs.aws.amazon.com/re...

http://stackoverflow.com/ques...

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/48185.html

相关文章

  • Bulk Upsert for MySQL & PostgreSQL

    摘要:什么是简而言之,就是,不存在就插入,存在就更新。当然也有功能,和的类似。然而,命令不支持,这使一些增量的工作非常不方便。五把表清空上面过程确实很麻烦,如果使用的话,只需要简单的相关链接 什么是 Upsert UPSERT is a DBMS feature that allows a DML statements author to atomically either insert a...

    2450184176 评论0 收藏0
  • 【Trafodion使用技巧篇】Trafodion数据加载介绍

    摘要:用例测试表继续用上述的创建测试文件,内容如下创建文件,内容如下根据文件加载数据检查数据是否转换成功初试主要是针对大数据量的且一般是批量装载的方式。 Trafodion 的数据加载主要包括两种方法,即 Trickle Load(持续加载) 和 Bulk Load(批量加载)。下表介绍了两种加载方法的区别: 类型 描述 方法/工具 Trickle Load 数据量较小,立即插入 ...

    gplane 评论0 收藏0
  • 【Trafodion使用技巧篇】Trafodion数据加载介绍

    摘要:用例测试表继续用上述的创建测试文件,内容如下创建文件,内容如下根据文件加载数据检查数据是否转换成功初试主要是针对大数据量的且一般是批量装载的方式。 Trafodion 的数据加载主要包括两种方法,即 Trickle Load(持续加载) 和 Bulk Load(批量加载)。下表介绍了两种加载方法的区别: 类型 描述 方法/工具 Trickle Load 数据量较小,立即插入 ...

    KunMinX 评论0 收藏0
  • 基于 MySQL Binlog 的 Elasticsearch 数据同步实践

    摘要:经过调研后,我们采用开源项目实现数据同步,并针对马蜂窝技术栈和实际的业务环境进行了一些定制化开发。微服务和配置中心项目使用马蜂窝微服务部署,为新接入业务提供了快速上线支持,并且在业务数据突增时可以方便快速的扩容。 一、背景 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticse...

    summerpxy 评论0 收藏0
  • 基于 MySQL Binlog 的 Elasticsearch 数据同步实践

    摘要:经过调研后,我们采用开源项目实现数据同步,并针对马蜂窝技术栈和实际的业务环境进行了一些定制化开发。微服务和配置中心项目使用马蜂窝微服务部署,为新接入业务提供了快速上线支持,并且在业务数据突增时可以方便快速的扩容。 一、背景 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticse...

    makeFoxPlay 评论0 收藏0

发表评论

0条评论

BakerJ

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<