Complete Strategy Tutorial: Syncing Warehouse Master Data from Jky to MySQL for Reconciliation

What This Strategy Solves

In one of our real projects, a retail enterprise needed to move warehouse master data from its Jky system into a MySQL reconciliation database for downstream stock and document matching. On the surface, syncing "warehouse" data looks trivial—after all, there aren't that many fields. But once the business went live, problems piled up: inconsistent code formats, mixed disabled/deleted statuses, a hard-coded incremental start time that caused data to slip through, and dirty data creeping in because the auto-increment primary key wasn't separated from the business unique key. On site with the customer, we took this on with the Qeasy data integration platform and built it into a dedicated sync strategy: "Reconciliation System — Warehouse Information."

Data Flow and Field Mapping

The data flow is one-way: Jky → Qeasy middle layer → MySQL reconciliation database. The source side pulls warehouses via Jky's RESTful interface erp.warehouse.get, and the target side writes them into the warehouse table through MySQL's execute interface.

The table below is the heart of the strategy. Field names have been simplified; please refer to the desensitized source field names in real projects.

source_Id is the most critical column in the strategy. It is essentially the "cross-system reference field," and all incrementals, reconciliations, and write-backs rely on it.

How to Configure It on Qeasy

On the Qeasy data integration platform, this strategy is split into two pipelines: one source-side read, one target-side write.

Source-side configuration essentials:

• In the metadata, set api to erp.warehouse.get, method=POST, type=QUERY.
• Set number to warehouseCode, id to warehouseId, and make sure idCheck=true. This flag must be turned on—it's what prevents duplicate inserts later.
• Pagination parameters: pageIndex starts from 0, pageSize defaults to 50. If a single response mixes deleted/disabled records, use optional filters like code or name on the source side to narrow the result set.
• The incremental conditions gmtModifiedStart and gmtModifiedEnd define the time window for this pull. The start time is fixed at 2024-03-20 00:00:00, and the end time uses {{CURRENT_TIME|datetime}}.

Target-side configuration essentials:

• In the metadata, select MySQL's execute, with buildModel=true.
• main_sql is a precompiled INSERT INTO ... VALUES (...). Placeholders use the <{field_name: }> format, and the field order must match the mapping table above.
• main_params is the parameter-binding object, with field names corresponding one-to-one to the source response, so the JSON coming from the source can plug straight into the SQL.

Implementation Steps

We break this strategy into three phases, each powered by Qeasy's scheduling capability.

Phase 1: Initial full load. Use a fixed date earlier than the business start date as the incremental start (in this case 2024-03-20 00:00:00), and pour all "non-deleted" warehouses from Jky into the reconciliation database in one shot. The goal here is to get the table populated—don't worry about duplicates yet.

Phase 2: Enable incrementals. In Qeasy, change the schedule to every 20 minutes (*/20 * * * *), and replace gmtModifiedStart with {{LAST_SYNC_TIME|datetime}} to truly pull new and changed records by the modification time window.

Phase 3: Routine verification and backfill. Add an extra low-peak schedule each day that runs again under full-load conditions and compares the result with the incremental output to fill in any dirty data that slipped through. This is a common "incremental + full-load dual-track" safety net among Qeasy users—incrementals for timeliness, full loads for safety.

For code mapping, we did not introduce _findCollection or _mongoQuery; all warehouse type fields are passed through directly from the source. If the reconciliation system later needs to group warehouses by type for business purposes, just add a centrally managed code-mapping collection. This pattern of "centrally managed code mapping" is also common among Qeasy users.

Pitfalls and Lessons Learned

1. Using an auto-increment primary key as the business key: MySQL uses id as auto-increment, but you must not use id as the unique key. The real business unique key is warehouse_code, and the cross-system reference is source_Id.
2. Hard-coding the incremental start as an "early date": If you always use a fixed start date, the API pagination slows down dramatically as data grows, and you can easily hit Jky's pagination limits. A safer approach is to change gmtModifiedStart to {{LAST_SYNC_TIME|datetime}} and record the timestamp of every successful sync.
3. Reversing the status field derivation: When isDelete=1 or isBlockup=1, the value should map to 0 (disabled), not 1. A common mistake is stuffing one of the source fields in directly.
4. idCheck not turned on: idCheck=true is the last gate against duplicate inserts. Once disabled, any network-jitter retry will inject duplicate rows, and cleaning them up later is a nightmare.
5. Syncing disabled/deleted warehouses too: It's best to filter out isDelete=1 records at the source-side filter layer, and decide per business whether to sync isBlockup=1 ones—don't write them all in as enabled.

When to Use and When Not to Use

Suitable for: source-side is Jky (or a similar ERP) and the target is a MySQL reconciliation/reporting system that needs master data like warehouses or suppliers synced at minute-level frequency, without complex field calculations. Not suitable for: real-time inventory or transaction streams that demand higher latency and transactional consistency, or scenarios that require complex business validation and reconciliation rules on the target side.