三方B2B商品数据同步背景

目前三方B2B平台上的几家店铺有自己的ERP系统,所以需要把店铺的ERP系统里面商品的“库存、价格、批号”等商品信息自动同步到三方B2B系统来,对于数据同步的要求:

  • 数据同步时效性要高,最好做到实时同步(“库存、价格”一变化就能同步过来)
  • 同步数据要准确不能出错,一旦出错就会直接影响线上业务

目前的数据同步所面临的问题

  • 对方ERP系统存储商品信息的表结构不一样(可以说五花八门),甚至在对方ERP系统里找不到数据的更新时间字段导致无法做增量同步数据
  • 对接系统非常多(预计70多家),加上部分系统无法做增量数据同步只能全量同步,导致系统压力非常大(主要是Mysql和ES)

数据同步的增量方案

经过开会讨论决定使用增量同步的方案来减少接口数据压力,增量同步的设计方案如下:

  1. 客户ERP数据库新建临时表对应需要同步的数据表(临时表的建表语句在文档中)
  2. 同步精灵通过一条sql语句(使用ERP系统的原始数据表关联查询我们创建的临时表)得到每次的增量数据,并发送增量数据到三方B2B系统的数据同步接口
  3. 三方B2B系统同步保存增量数据,并返回保存成功的数据给同步精灵
  4. 同步精灵根据三方B2B系统返回同步成功的数据回写对应的临时表数据(把ERP系统的原始数据回写到临时表中)

上述步骤对应的时序图如下:

客户的ERP数据库同步精灵三方B2B系统同步精灵定时任务触发,同步流程开始1.执行增量数据查询SQL使用ERP系统的原始数据表关联查询我们创建的临时2.返回查询到的增量同步数据3.发送增量同步数据4.更新同步商品信息5.返回更新成功的数据ID根据返回的数据ID生成回写临时表的SQL语句6.生成更新SQL语句7.执行回写临时表的SQL语句8.返回执行结果到此一个同步流程结束客户的ERP数据库同步精灵三方B2B系统

关键步骤说明:
一个同步流程基本为上面8个步骤,现在对其中一下关键步骤进行说明,我们以库存同步为例,假设客户ERP数据库存储商品库存的表结构如下:

create table tb_mock
(
    id              bigint              not null    identity,
    erp_no          varchar(127)        not null,
    storage_number  decimal(15,5)       not null,
    primary key (id)
);

其中storage_number表示库存数量,我们新建如下临时表:

create table tb_storage_tmp
(
    id              bigint              not null    identity,  -- 自动增长的主键
    inner_id        varchar(127)        not null    unique,    -- 客户ERP数据库中需要同步的表中的主键(可以使用多字段联合,保证唯一性即可)
    erp_no          varchar(127)        not null,              -- 客户商品编码
    storage_number  decimal(15,5)       not null,              -- 需要同步的库存数量
    update_at       datetime,                                  -- 数据更新时间(就是最后一次同步时间,由同步精灵回写)
    primary key (id)
);
  • 第1步中的“增量数据查询SQL”这个SQL语句很关键,根据我们的假设这个SQL如下:
    select
      a.*,
      b.id
    from
      tb_mock a left join tb_storage_tmp b on (a.id=b.inner_id)
    where a.storage_number!=b.storage_number
      or b.id is null
  • 第5步返回更新成功的数据ID就是tb_storage_tmp表的id字段,注意返回的是一个ID集合(一次同步多条数据)
  • 第6步和第7步中需要生成回写临时表的SQL语句,类似如下(其中storageNumber取第3步发送的数据的对应值):
    update tb_storage_tmp set storage_number=:storageNumber, update_at=:now where id=:id

实施说明

由于客户ERP数据库类型不同所有创建临时表的脚本也不一样,主要分“mysql、sqlserver、oracle”这三种主流数据库,虽然建表脚本不一致,但是数据库表结构是一致的,其他数据库实时人员可以自己写对应的建表脚本。建表脚本见后续文档“SqlServer建表脚本”“Oracle建表脚本”。

由于客户ERP数据库存储数据的表结构都不一样所以增量数据查询SQL无法确定,还需要实时人员根据上面的库存同步案例自行编写

文档更新时间: 2019-08-22 11:55   作者:lizw