Sync Shop Master Data from Jushuitan to MySQL: A Hands-On Tutorial
What This Strategy Solves
For a multi-channel retailer, shop master data lives in multiple shop nodes inside the Jushuitan ERP, while downstream systems in MySQL — membership, orders, marketing, reporting — all need shop-level filtering and authorization. Without a single source of truth, teams fall back on manual imports. We use Qeasy as a middleware layer to run a steady "Jushuitan shop query → MySQL" integration strategy, so downstream consumers always read clean shop records instead of relying on manual upkeep.
Data Flow and Field Mapping
The overall direction is Jushuitan (source) → Qeasy middleware (cleaning/mapping) → MySQL (target), direction B → A. The middleware only reshapes structure; it does not inject business logic.
Key field mapping (typical retail master data view; actual fields depend on what Jushuitan returns):
| Dimension | Source: Jushuitan shop query | Middleware (Qeasy) | Target: MySQL shop master table |
|---|---|---|---|
| Code | shop_id (platform shop code) | No value change, only type conversion | store_code (primary key, VARCHAR) |
| Name | shop_name | Trim whitespace, unify traditional/simplified | store_name |
| Channel | platform_code | Code mapping → channel dictionary | channel_id (foreign key) |
| Status | status (active/inactive) | Enum normalized: enabled/disabled | is_enabled (TINYINT) |
| Contact | contact / phone | Phone number desensitized before storage | contact_phone |
| Time | created / modified | Convert to UTC+8 before storage | gmt_create / gmt_modified |
| Ext attributes | ext_json | Flattened into columns | Custom columns |
For code mapping we typically use a "centralized management" pattern: keep the platform_code → channel_id mapping in a single mapping table inside Qeasy. Adding a new channel only touches that table, not the strategy body.
How to Configure in Qeasy
Inside the Qeasy Data Integration platform, this strategy breaks down into four configuration blocks:
- Source side: Select "Jushuitan · Shop Query". After configuring the app authorization, initialize the query as a "full" pull into a temporary result set.
- Target side: Register the MySQL connection (read/write account, target schema). Pre-create the target shop master table with a unique index on the primary key store_code to prevent duplicate inserts.
- Mapping and transformation: In Qeasy's field mapping canvas, drag source fields to target fields. Attach converters for transformations such as enum normalization, type conversion, and phone desensitization.
- Write strategy: Use upsert on the target side, matching on store_code. New rows go through INSERT, existing rows through UPDATE, and rows are never physically deleted (deactivated shops are flagged via is_enabled instead of deletion).
When connecting a multi-shop group at once, we recommend using "shop" as the partition key so downstream queries can easily scope by shop.
Implementation Steps
We recommend a three-stage rollout to avoid dirty data from day one of scheduling:
- Stage 1: Incremental baseline. Manually run a one-time full pull in Qeasy so all current shops land in MySQL as the baseline. After it finishes, run a uniqueness check on the store_code primary key.
- Stage 2: Full reconciliation trigger. Manually run another full pull before the next workday and diff against the baseline: which are new, which are updated, which are deactivated. This step is not about going live — it is for the customer's business side to "validate the data".
- Stage 3: Steady-state scheduling. After reconciliation passes, switch the strategy from "manual trigger" to scheduled mode in Qeasy. Common cadences: fewer than 100 shops, every 30 minutes; 100+ shops, every hour. Qeasy's incremental mode uses modified time as the cursor by default. If Jushuitan does not return modified time, fall back to a full-replace strategy with a longer scheduling interval.
In real projects we deliver to customers a "header/body phased" pattern: land this shop master table as the header first, stabilize it, then extend to a "shop–warehouse" sub-table. This avoids over-scoping on day one and makes issue localization much easier.
Pitfalls Recap
- Pitfall 1: Plain phone numbers and contacts land in the DB. The source returns plain phone numbers, and storing them directly violates compliance. The safe pattern is to desensitize inside the Qeasy converter, keeping only the last four digits, and storing the full number in a separate encrypted column.
- Pitfall 2: Deactivated shops get physically deleted. A classic mistake is using "delete + insert" full-replace on the target table, which makes "disappeared" shops untraceable in downstream reports. The safe pattern is upsert with is_enabled as soft delete — make sure the Qeasy target write mode is set to upsert rather than replace.
- Pitfall 3: Channel code drift. Jushuitan's platform_code can mean different things under different shop groups. Storing the raw value will mis-bucket downstream channel roll-ups. Centralized mapping is mandatory — we keep it in a readable mapping table inside Qeasy, not hard-coded in a converter.
- Pitfall 4: Time zone misalignment. created / modified are UTC by default. Downstream filtering on local time will silently miss data. Always convert to UTC+8 explicitly in the middleware layer.
- Pitfall 5: First full pull conflicts with the incremental start point. If scheduling is enabled before the first manual full pull, you will get duplicate inserts or primary-key conflicts. Always follow the sequence: manual full pull to set the baseline → reconciliation → enable scheduling.
When to Use and When Not to Use
Use when: multi-platform/multi-shop retail, brand groups unifying master data, downstream systems that need shop-level authorization or reporting. Do not use when: single-shop operations, very small shop counts with strict Jushuitan API limits (manual maintenance is cheaper), or cases that require real-time shop change push (this strategy is near real-time, not real-time).