1. 概述编辑
1.1 背景
某公司业务中大量使用简道云表单进行填报,每天都会生成大量的数据,而这些数据希望能够定期同步至指定的数据库中,便于与其他系统的数据进行联合使用。
根据简道云录入表单数据的增删改情况,有以下几种情况。
表单数据每天有新增和删除,同时也会有修改的数据;
子表单数据中没有固定的唯一标识,但是也需要进行定时更新到数据库库中。
1.2 思路
表单数据每天有新增和删除:可以使用比对删除功能,比对并在数据库中标记表单中被删除的数据;
表单数据只会有新增和修改,不会删除:此时可以每天更新前一天的数据,同时通过主键字段对比表单和数据库,若字段数据相同则表单最新数据覆盖目标数据库的数据;
对于子表单数据,如果无法设置联合主键进行数据更新,可以先删除近一段时间(近七天)的数据,在全量同步近一段时间(近七天)的数据。
任务示例如下图所示:
2. 实现方法编辑
2.1 场景说明
简道云表单S订单中存放着订单数据,业务员每天都会录入订单相关信息。
现在希望对这些数据定时同步至目标数据库,方便后续进行数据处理和使用。
我们希望将简道云「订单明细」https://tea3o334wi.jiandaoyun.com/f/631161c880be9d0008e99fdf填报数据中「主表单」和「子表单」分别定时更新至 jdy_order 和 jdy_order_zi 。
其中主表单和子表单都是每天都会有新增数据、删除数据以及修改更新的数据。
2.2 方案说明
对于有唯一主键「订单ID」的主表单数据:
有删除数据:使用比对删除,在数据库中对表单中删除的数据进行标记。注:由于每次都是全量数据对比,如果数据量较大则不建议使用该方式。
有新增和修改的数据:设置参数每天更新前一天的数据,同时如果有修改的数据,则通过对比主键「订单ID」字段,表单最新数据覆盖目标数据库的数据。
对于没有主键的的子表单数据:
先删除近一段时间(近七天)的数据,在全量同步近一段时间(近七天)的数据。
3. 操作步骤编辑
3.1 处理简道云数据
在简道云表单编辑界面,点击「扩展功能>数据推送>设置字段别名」,修改字段名称,如下图所示:
3.2 标记删除的数据
对于主表单中每天会有需要删除的数据,这些需要删除的数据需要同步到目标数据库时有一个标识。
1)新建任务并新增一个「数据转换」节点,进入节点编辑界面,使用简道云输入将表单数据接入 FineDataLink 中,如下图所示:
通过 JSON 解析获取每一条表单填报数据的创建人,如下图所示:
通过 JSON 解析获取每一条表单填报数据的更改人,如下图所示:
字段设置删除一些不需要的字段,如下图所示:
首次进行数据同步时,可以先使用 DB 表输出,将全量数据进行数据同步写入数据库,如下图所示:
然后设置对比删除。
通过对比唯一的主键「orderid」也就是表单中的订单ID字段,如果表单中存在orderid,但是数据库中不存在,此时使用逻辑删除的方式,对于标识字段 deleteTime 标记为${deletetime},如下图所示:
这个${deletetime}我们使用参数设置,将其设置当天,也就是如果表单中存在orderid,但是数据库中不存在,在定时更新的时候目标数据库中的deleteTime字段会写入当前更新时间作为删除标记,如下图所示:
此时运行任务,在目标数据表 jdy_order 中 deleteTime 字段新增更新日期来标记表单中被删除的数据,如下图所示:
3.2 更新新增和修改的数据
对于主表单中每天新增和更改的数据,需要每天更新前一天的数据。我们通过设置参数来实现。
新建一个「数据转换」节点,进入编辑界面,使用「简道云输入」,通过设置表单的创建时间和更新时间作为参数,来控制我们取出的简道云数据是一天前的,如下图所示:
在任务界面的「参数设置」中设置starttime前一天,3.1节已经设置了一个当天的参数,因此就不需要再设置了,如下图所示:
同 3.1 节的设置,获取表单数据创建人、更改人并调整字段,如下图所示:
根据主键orderid,遇到有更新的数据(表单中 orderid 数据和数据库中相同,但是数据内容不同时),表单中的数据会覆盖数据库中的数据,实现更新表单中修改的数据,同时表单中新增的数据也会被同步至数据库,如下图所示:
3.3 更新子表单中的数据
由于子表单没有主键,因此我们先删除近一段时间(近七天)的数据,在全量同步近一段时间(近七天)的数据。
3.3.1 删除近七天的数据
在任务界面新建 SQL 脚本,删除近七天数据,示例使用的是 SQLite 数据库,语句为:select * from jdy_order_s where julianday('now') - julianday(createTime)< 7,如下图所示:
3.3.2 更新近七天的数据
新建「数据转换」节点,进入编辑界面,简道云输入将表单数据接入,如下图所示:
使用 JSON 解析将子表单字段进行解析,如下图所示:
同3.1 节获取表单对应数据创建任务并进行字段设置,如下图所示:
通过 SparkSQL 获取表单中近七天的数据,使用了参数time,如下图所示:
在「参数设置」中设置参数 time 为距离当前时间近七天,如下图所示:
最后将近七天的数据输出至 jdy_order_s 数据表即可。
3.4 设置定时调度任务
在调度配置中设置任务定时执行即可,如下图所示: