使用轻易云实现SQLServer到MySQL的数据ETL转换

  • 轻易云集成顾问-贺强

SQL Server数据集成到MySQL：唯品退货单明细表对接案例

在本次技术分享中，我们将聚焦于一个实际运行的系统对接案例，即如何高效、安全地将SQL Server中的唯品退货单明细表数据集成到MySQL目标数据库，方案名称为14--巨益OMS-唯品退货单明细表-->Mysql-唯品退货单明细表-vipreturnorderdetail_z。

对于这种跨库的数据集成场景，关键点在于确保数据处理的高时效性和一致性，而这往往依赖于以下几个核心特性的支持：

  1. 大规模数据快速写入能力：为了满足业务需求，需要实现大量数据从SQL Server快速被写入到MySQL，这需要平台提供支持高吞吐量的数据写入能力。

  2. 实时监控与异常处理机制：集中监控和告警系统可以有效追踪每个数据集成任务的状态与性能，并且在出现问题时能够及时发现并进行异常检测与处理，保障了整体流程的顺利进行。

  3. 自定义转换逻辑与格式匹配：由于源端和目标端数据库结构存在差异，自定义的数据转换逻辑至关重要。同时，还需要应对不同存储引擎之间的数据格式差异，以确保迁移后的可用性和一致性。

具体实施过程中，将首先通过调用SQL Server接口select获取待迁移的数据，再利用MySQL接口batchexecute完成批量插入操作。此外，在整个处理中还需特别注意分页抓取、限流控制等问题，以应对大批量数据传输带来的潜在风险。以下是详细步骤... 电商OMS与WMS系统接口开发配置

调用SQL Server接口获取并加工数据的技术实现

在数据集成生命周期的第一步，我们需要从源系统SQL Server中调用接口select获取数据，并进行初步加工。本文将详细探讨如何通过轻易云数据集成平台配置元数据，实现这一过程。

元数据配置解析

首先，我们来看一下元数据配置的具体内容：

{
  "api": "select",
  "effect": "QUERY",
  "method": "SQL",
  "number": "Id",
  "id": "Id",
  "request": [
    {
      "field": "main_params",
      "label": "主参数",
      "type": "object",
      "describe": "111",
      "children": [
        {"field": "offset", "label": "offset", "type": "int"},
        {"field": "fetch", "label": "fetch", "type": "int", "value":"5000"},
        {"field": "CreateDateBegin", 
         "label":"创建日期（开始时间）", 
         "type":"string", 
         "value":"{{LAST_SYNC_TIME|datetime}}"
        },
        {"field":"CreateDateEnd", 
         "label":"创建日期（结束时间）", 
         "type":"string", 
         "value":"{{CURRENT_TIME|datetime}}"
        }
      ]
    }
  ],
  ...
}

该配置定义了一个API接口select，用于从SQL Server中查询数据。主要参数包括分页控制的offset和fetch，以及时间范围控制的CreateDateBegin和CreateDateEnd。

主查询语句

主查询语句如下：

select Id, CreateDate, PoCode, BoxNo, ReturnOrderId, ProductId, ProductCode, ProductName, SkuId, SkuCode, SkuName, VipSkuCode, ReturnQty, InQty, ScanDate, ScanUser, NoticeQty, SupplyPrice, DetailRemark, UniqueCode, DefectiveQty, TradeId, ReceiptQuantity, DefectiveReceiptQuantity 
from vipReturnOrderDetail 
where CreateDate >= :CreateDateBegin and CreateDate <= :CreateDateEnd 
order by Id offset :offset rows fetch next :fetch rows only

这条SQL语句从表vipReturnOrderDetail中选择多个字段，并根据创建日期范围过滤记录，同时使用分页机制控制每次查询的数据量。

参数化查询

为了提高查询效率和安全性，我们采用了参数化查询。参数化查询不仅能防止SQL注入，还能通过预编译提高执行速度。在上述SQL语句中，:CreateDateBegin, :CreateDateEnd, :offset, 和 :fetch都是动态参数，通过元数据中的配置进行传递。

数据请求与清洗

在实际操作中，调用该接口时会传递以下参数：

  • offset: 分页起始位置
  • fetch: 每次获取的数据条数，默认值为5000
  • CreateDateBegin: 创建日期的开始时间，通常为上次同步时间
  • CreateDateEnd: 创建日期的结束时间，通常为当前时间

这些参数确保了每次调用都能准确获取到所需的数据，并且避免了重复或遗漏。

数据转换与写入

虽然本文重点在于数据请求与清洗，但简要提及一下后续步骤：一旦数据被成功获取并清洗完毕，将会进入数据转换与写入阶段。在这一阶段，数据会被转换为目标格式并写入到目标系统（如MySQL）。

实际案例应用

假设我们需要从巨益OMS系统中获取唯品退货单明细表的数据，并将其写入到MySQL数据库中的相应表格。通过上述配置，我们可以轻松实现这一需求：

  1. 配置API接口：根据元数据定义API接口，确保能够正确传递参数。
  2. 执行查询：调用API接口执行SQL查询，从源系统中获取符合条件的数据。
  3. 处理分页：利用分页机制逐步获取所有符合条件的数据，避免一次性读取大量数据导致性能问题。
  4. 清洗与转换：对获取的数据进行必要的清洗和转换，以适应目标系统的要求。
  5. 写入目标系统：将处理后的数据写入到MySQL数据库中的相应表格。

通过这种方式，我们可以高效、准确地完成从源系统到目标系统的数据集成任务，实现不同系统间的数据无缝对接。 金蝶与MES系统接口开发配置

使用轻易云数据集成平台进行ETL转换并写入MySQL

在数据集成的生命周期中，ETL（Extract, Transform, Load）过程是关键的一步。本文将深入探讨如何使用轻易云数据集成平台将源平台的数据转换为目标平台MySQL API接口能够接收的格式，并最终写入目标平台。

数据请求与清洗

在ETL过程的初始阶段，我们需要从源系统中提取数据，并进行必要的清洗和预处理。这部分工作确保了数据的准确性和一致性，为后续的转换和加载打下基础。本文重点讨论的是第二阶段：数据转换与写入。

数据转换与写入

为了将数据成功地写入目标平台MySQL，我们需要对数据进行适当的转换，使其符合目标平台API接口的要求。以下是具体的元数据配置及其应用：

{
  "api": "batchexecute",
  "effect": "EXECUTE",
  "method": "SQL",
  "idCheck": true,
  "request": [
    {"field": "Id", "label": "Id", "type": "int", "value": "{Id}"},
    {"field": "CreateDate", "label": "CreateDate", "type": "datetime", "value": "{CreateDate}", "default":"1970-01-01 00:00:00"},
    {"field": "PoCode", "label": "PoCode", "type": "string", "value": "{PoCode}"},
    {"field": "BoxNo", "label": "BoxNo", "type": "string", "value": "{BoxNo}"},
    {"field": "ReturnOrderId", "label": "ReturnOrderId", "type":"int","value":"{ReturnOrderId}"},
    {"field":"ProductId","label":"ProductId","type":"string","value":"{ProductId}"},
    {"field":"ProductCode","label":"ProductCode","type":"string","value":"{ProductCode}"},
    {"field":"ProductName","label":"ProductName","type":"string","value":"{ProductName}"},
    {"field":"SkuId","label":"SkuId","type":"string","value":"{SkuId}"},
    {"field":"SkuCode","label":"SkuCode","type":"string","value":"{SkuCode}"},
    {"field":"SkuName","label":"SkuName","type":"string","value":"{SkuName}"},
    {"field":"VipSkuCode","label":"VipSkuCode","type":"string","value":"{VipSkuCode}"},
    {"field":"ReturnQty","label":"ReturnQty","type":"int","value":"{ReturnQty}"},
    {"field":"InQty","label":"InQty","type":"int","value":"{InQty}"},
    {"field":"ScanDate","label":"ScanDate","type":"datetime","value":{"ScanDate"},"default:"1970-01-01 00:00:00"},
    {"field:"ScanUser" ,"label:"ScanUser" ,"type:"string" ,"value:"{ScanUser}" },
    {"field:"NoticeQty" ,"label:"NoticeQty" ,"type:"int" ,"value:"{NoticeQty}" },
    {"field:"SupplyPrice" ,"label:"SupplyPrice" ,"type:"float" ,"value:"{SupplyPrice}" },
    {"field:"DetailRemark" ,"label:"DetailRemark" ,"type:"string" ,"value:"{DetailRemark}" },
    {"field:"UniqueCode" ,"label:"UniqueCode" ,"type:"string" ,"value:"{UniqueCode}" },
    {"field:"DefectiveQty" ,"label:"DefectiveQty" ,"type:"int" ,"value:{DefectiveQty}" },
    {"field:TradeId," label:TradeId," type:string," value:{TradeId}" },
   { field:ReceiptQuantity," label:ReceiptQuantity," type:int," value:{ReceiptQuantity}" },
   { field:DefectiveReceiptQuantity," label:DefectiveReceiptQuantity," type:int," value:{DefectiveReceiptQuantity}" }
  ],
  otherRequest:[
     {
      field:'main_sql',
      label:'主语句',
      type:'string',
      describe:'111',
      value:'REPLACE INTO vipreturnorderdetail_z (Id,CreateDate,PoCode,BoxNo,ReturnOrderId,ProductId,ProductCode,ProductName,SkuId,SkuCode,SkuName,VipSkuCode,ReturnQty,InQty,ScanDate,ScanUser,NoticeQty,SupplyPrice,DetailRemark,UniqueCode,DefectiveQty,TradeId,ReceiptQuantity,DefectiveReceiptQuantity) VALUES'
     },
     {
      field:'limit',
      label:'limit',
      type:'string',
      describe:'111',
      value:'1000'
     }
   ],
   buildModel:true
}

元数据配置详解

  1. API调用类型：batchexecute，表示批量执行SQL语句。
  2. 执行效果：EXECUTE，表示执行操作。
  3. 方法：SQL，表示使用SQL语句进行操作。
  4. ID检查：idCheck设置为true，确保每条记录都有唯一标识。
  5. 字段映射：
    • 每个字段都对应一个标签（如CreateDate），指定了字段类型（如datetime）和默认值（如1970-01-01 00:00:00）。
  6. 主语句：通过main_sql字段定义了插入语句模板： 
    REPLACE INTO vipreturnorderdetail_z (字段列表) VALUES
  7. 限制：通过limit字段设置批量操作的记录数上限为1000。

实际应用

在实际应用中，这些配置将会被用于构建一个完整的数据转换流程。首先，从源系统提取的数据将被映射到上述配置中的字段，然后通过批量执行SQL语句，将这些数据插入到目标MySQL数据库中。

例如，对于一条退货单明细记录，其JSON格式的数据可能如下：

{
  Id: 123,
  CreateDate: '2023-10-01T12:34:56Z',
  PoCode: 'PO123456',
  BoxNo: 'BX789012',
  ReturnOrderId: 456,
  ProductId: 'P12345',
  ProductCode: 'PC67890',
  ProductName: 'Sample Product',
  SkuId: 'SKU12345',
  SkuCode: 'SC67890',
  SkuName: 'Sample SKU Name',
  VipSkuCode: 'VIPSKU12345',
  ReturnQty: 10,
  InQty: 8,
  ScanDate:'2023-10-02T12:34:56Z' ,
 ScanUser :'user1' ,
 NoticeQty :10 ,
 SupplyPrice :100.5 ,
 DetailRemark :'remark' ,
 UniqueCode :'unique123' ,
 DefectiveQty :2 ,
 TradeId :'trade123' ,
 ReceiptQuantity :8 ,
 DefectiveReceiptQuantity :2 
}

这些数据将被映射并插入到目标表中，通过上述配置生成如下SQL语句：

REPLACE INTO vipreturnorderdetail_z (Id, CreateDate, PoCode, BoxNo, ReturnOrderId, ProductId, ProductCode, ProductName, SkuId, SkuCode,SkuName,VipSkuCode ,ReturnQty ,InQty ,ScanDate ,ScanUser ,NoticeQty ,SupplyPrice ,DetailRemark ,Unique Code ,Defective Qty ,Trade Id ,Receipt Quantity ,Defective Receipt Quantity) 
VALUES (123,'2023-10-01T12 :34 :56Z','PO123456','BX789012',456,'P12345','PC67890','Sample Product','SKU12345','SC67890','Sample SKU Name','VIPSKU12345',10 ,8 ,'2023-10-02T12 :34 :56Z','user1',10 ,100.5 ,'remark ','unique123',2 ,'trade123',8 ,2)

通过这种方式，我们实现了从源系统到目标MySQL数据库的数据无缝对接，确保了数据的一致性和完整性。这种高效、透明的数据处理方式极大提升了业务流程的效率。 用友与SCM系统接口开发配置

版权声明

该文章《使用轻易云实现SQLServer到MySQL的数据ETL转换》为原创内容，版权归 广东轻亿云软件科技有限公司 所有。 未经本公司书面授权，严禁转载、引用、复制或建立镜像等行为。 若需转载或引用本文内容，请联系本公司获取授权，并在显著位置注明作者姓名、文章出处（包括原文链接）等信息。

数据集成平台架构
更多系统对接方案