Practical Tutorial: Syncing Jushuitan Sales Outbound Orders to MySQL
What This Strategy Solves
In retail and e-commerce scenarios, sales outbound orders are the core documents driving data flow. In one real project, a retail company's e-commerce business ran on Jushuitan, while their data warehouse and downstream business systems ran on a self-built MySQL. Operations needed to reconcile inventory by order, calculate gross margin, and push invoices every day — all of which depended on structured outbound details in MySQL.
The problem is that order statuses on Jushuitan change (from paid to shipped to completed), and product codes, SKUs, and warehouse codes are spread across multiple tables. Manual export-then-import is not only slow but often misses orders or skips modifications. We used the Qeasy data integration platform to push Jushuitan Qimen's sales outbound orders into MySQL according to unified rules, achieving consistent records and traceable statuses.
Data Flow and Field Mapping
The data flow is unidirectional: Jushuitan Qimen on side B → Qeasy middle layer → MySQL on side A.
The middle layer performs no business logic. It handles three things: protocol adaptation (Qimen open platform API → internal standard fields), code mapping (Jushuitan product/warehouse codes → the customer's internal codes), and incremental cursor management (filtering by document last-modified timestamp).
Key field mapping example:
| Business Meaning | Jushuitan Qimen (Source) | MySQL (Target) | Notes |
|---|---|---|---|
| Document No. | io_id (outbound order no.) | order_no | Primary key, basis for deduplication |
| Document Status | io_status | order_status | Status dictionary mapping required |
| Product Code | sku_id | sku_code_internal | Routed through code mapping table |
| Warehouse Code | warehouse_id | warehouse_code | Routed through code mapping table |
| Quantity | qty | qty | Direct passthrough |
| Outbound Time | io_date | outbound_time | Unified time format |
| Last Modified | modified | update_ts | Incremental cursor field |
Code mappings should not be hardcoded into scripts. Qeasy's "Centralized Code Mapping" module is built exactly for this scenario — maintain once, and all documents flowing through the same pipeline can reuse it.
How to Configure on Qeasy
Select strategy type SYNC with flow direction B_TO_A. Choose Jushuitan Qimen as the source datasource and the corresponding MySQL datasource as the target, filling in source system as B and target system as A to stay aligned with the solution configuration.
There are four configuration points:
- Source API selection: The sales outbound query interface of Jushuitan Qimen. The main input parameters are start time, end time, and page size. For incremental sync, take the start time from the last successful sync's
update_ts, and the end time is usually the current moment minus 5 minutes (to prevent missing boundary data). - Field mapping: In Qeasy's "Field Mapping" interface, map the fields returned by the Qimen JSON to the columns of the MySQL table. Enumerated values such as status, type, and warehouse should be centrally maintained via mapping tables — do not copy-paste them in every strategy.
- Deduplication and idempotency: The primary key of the MySQL target table is
order_no. Configure "update if exists, insert if not" to avoid duplicate pushes that break downstream reconciliation. - Exception handling: Network jitter, field overflow, and missing code mappings are the three most frequent errors. Configure failure retries (exponential backoff), route failed data to a "pending queue," and auto-truncate with flags when key fields overflow — never let a single dirty record block the whole batch.
Implementation Steps
On the customer site, we generally split implementation into three phases:
Phase 1: Full initialization. For the first launch, run a full sync to bring the last 3 months of historical outbound orders from Jushuitan into MySQL. After the full run, perform a reconciliation to confirm the document count and detail amounts match the source.
Phase 2: Incremental start switchover. The incremental cursor update_ts starts from the moment the full sync ends. Set the schedule to run every 15 minutes. A common pitfall here: do not directly treat the first full sync's timestamp as the start point. Use "the moment the full task ends" as the incremental start, otherwise boundary data will be lost.
Phase 3: Steady-state operation. Once the incremental run is stable, adjust the frequency to every 5–10 minutes. Monitor three metrics: sync latency (time difference between source update_ts and MySQL landing), failure rate, and pending queue length. Alert when latency exceeds the business tolerance threshold. Auto-pause and notify operations if the failure rate exceeds the threshold for 3 consecutive runs.
A safe practice is the "dual-track incremental + full" approach: keep the full sync script undeleted as a fallback for quarterly reconciliation and disaster recovery reruns.
Lessons from the Field
Pitfall 1: Incremental start misalignment. The first full sync ran at 2 AM, but the incremental start was set to 2:00 sharp. The result: orders from 2:00 to 2:05 were not picked up by the full sync, and the incremental skipped that window — data was lost. The safe practice is to use the task end timestamp instead of the planned start timestamp.
Pitfall 2: Status field mapping omissions. Jushuitan's io_status has over a dozen enumerated values, and the customer only mapped 5 of them — the rest were discarded. Later we found that downstream reconciliation depended on the "returned" status, almost causing a batch misjudgment. Mapping tables should cover all values completely; even unused ones should have a mapping entry reserved.
Pitfall 3: Header and detail not phased. Outbound orders are a typical "one header, multiple lines" structure. Early on, we tried to use a single sync pipeline to land both header and detail rows, causing interface timeouts when line items exceeded a thousand. We later split it into two pipelines (header sync and detail sync) linked by order_no, and the problem was solved.
Pitfall 4: Qimen API pagination cap. The Qimen open platform's single-return record limit must be strictly respected with proper pagination parameters. Once on-site, due to incorrect pagination parameters, only the first 10 records were pulled — operations had to stare at it for half an hour before noticing.
Pitfall 5: Code mapping drift. Jushuitan occasionally adjusts product codes, but the code mapping table wasn't updated in sync, resulting in a batch of "non-existent products" downstream. It is recommended to run a code consistency inspection script weekly.
Applicable and Non-Applicable Scenarios
This strategy applies to: scenarios where a retail/e-commerce company's sales outbound orders need to be synced in real-time or near-real-time to a self-built data warehouse or business system, especially businesses with large document volumes and high reconciliation timeliness requirements.
It does not apply to: scenarios requiring bidirectional sync where both sides modify the same field, or scenarios where the source-side document structure is highly unstable with frequently changing fields — in such cases, standardization should come first before discussing sync.