ETL转换与写入:使用轻易云将聚水潭数据导入SQLServer

2023-07-12 03:35:51
轻易云集成顾问-陈洁琳

聚水潭·奇门数据集成到SQL Server的技术案例分享

在今天的数据驱动环境中，企业需要高效地对接和处理多源数据，以确保业务流程的流畅运转。本篇文章将聚焦一个具体的系统对接集成案例：如何将聚水潭·奇门（Jushuitan）平台中的售后单数据无缝集成到SQL Server数据库。我们实际运行的方案名为“聚水潭-售后单查询-->SQL-售后单”，此方案不仅能实时抓取和写入大量数据，还解决了分页限流、数据格式差异等技术难点。

首先，我们需要从聚水潭·奇门接口jushuitan.refund.list.query获取售后单数据。这一步需要考虑API调用频率限制以及如何批量处理中大规模分页请求以保证不漏单。例如，采用逐页读取，并通过队列机制管理API请求，从而避免因超出速率限制导致的数据丢失。

其次，将抓取到的数据快速可靠地写入至SQL Server中。针对大量数据高速导向需求，可使用批量插入策略，通过调优插入语句和事务处理来提高性能。同时，对异常进行精细化处理，例如网络波动或服务器响应慢时，需要设计错误重试机制，这样即使出现临时性故障，也不会影响整个流程。

此外，还要重点解决两端之间的数据格式差异问题。通常情况下，第三方API返回的数据结构会与本地数据库表格有所不同。在这一过程中，需要通过定制化映射规则，使之完全匹配目标库表字段，并且支持各种类型转换。一旦定义好这些规则，就可以借助脚本自动执行转换操作，大幅减少人工介入力度。

最后，为确保整个过程透明可控，实现全程监控及日志记录非常关键。不仅能实时跟踪各个环节状态，还可根据历史记录迅速定位并分析可能的问题来源，提高整体效率与稳定性。用友BIP接口开发配置

调用聚水潭·奇门接口jushuitan.refund.list.query获取并加工数据

在数据集成的生命周期中，调用源系统接口是至关重要的一步。本文将详细探讨如何使用轻易云数据集成平台调用聚水潭·奇门接口jushuitan.refund.list.query，并对获取的数据进行初步加工处理。

接口配置与请求参数

首先，我们需要配置API接口的元数据。以下是jushuitan.refund.list.query接口的具体配置：

API名称: jushuitan.refund.list.query
请求方式: POST
请求参数:
- page_index: 页码，默认值为1。
- page_size: 页数，默认值为50。
- start_time: 修改起始时间，默认值为当前时间前一天。
- end_time: 修改结束时间，默认值为当前时间。
- so_ids: 线上单号列表，可选参数。
- date_type: 时间类型，可选参数。
- status: 售后单状态，可选参数。
- good_status: 货物状态，可选参数，例如买家未收到货、买家已收到货等。
- type: 售后类型，可选参数，例如普通退货、拒收退货等。

这些参数通过POST请求发送到聚水潭·奇门接口，以获取相应的售后单数据。

数据请求与清洗

在轻易云数据集成平台上，我们可以通过配置上述元数据来实现对聚水潭·奇门接口的调用。以下是具体步骤：

配置请求参数：根据业务需求，我们可以设置不同的请求参数。例如，可以将page_index设置为1，page_size设置为50，以分页方式获取数据。同时，通过动态函数设置start_time和end_time，确保每次调用获取的是最新的数据。

{
 "field": "start_time",
 "label": "修改起始时间",
 "type": "datetime",
 "describe": "开始时间",
 "value": "_function LEFT( '{{DAYS_AGO_1|datetime}}' , 10)"
},
{
 "field": "end_time",
 "label": "修改结束时间",
 "type": "datetime",
 "describe": "结束时间",
 "value": "_function LEFT( '{{CURRENT_TIME|datetime}}' , 10)"
}

发送请求：配置好请求参数后，通过轻易云平台发送POST请求至聚水潭·奇门接口。平台会自动处理异步请求，并返回售后单列表数据。
数据清洗：获取到的数据可能包含多种信息，需要进行初步清洗和过滤。例如，可以根据售后单状态（status）和货物状态（good_status）进行筛选，只保留特定状态下的记录。

数据转换与写入

在完成数据清洗后，需要将处理后的数据转换并写入目标数据库。以下是一个简单的数据转换示例：

定义转换规则：将售后单中的关键字段提取出来，并映射到目标数据库表中。例如，将售后单ID（as_id）、订单状态（order_status）等字段映射到SQL数据库中的相应字段。
执行写入操作：使用轻易云平台提供的数据写入功能，将转换后的数据批量写入SQL数据库中。平台支持多种数据库类型，可以根据实际需求选择合适的目标数据库。

INSERT INTO refund_orders (as_id, order_status, modified)
VALUES (?, ?, ?)

通过上述步骤，我们实现了从聚水潭·奇门接口获取售后单数据，并对其进行清洗、转换和写入的全过程。这不仅提高了数据处理效率，还确保了数据的一致性和准确性。在实际应用中，可以根据具体业务需求进一步优化和扩展这些操作，以满足更多场景下的数据集成需求。如何开发金蝶云星空API接口

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

在数据集成生命周期的第二步，我们需要将已经从源平台（如聚水潭）提取的数据进行ETL转换，转化为目标平台（如SQL Server）能够接收的格式，并最终写入目标平台。本文将详细探讨如何使用轻易云数据集成平台配置元数据，实现这一过程。

配置元数据

首先，我们需要配置元数据，以确保数据能够正确映射到目标数据库表中。以下是一个示例元数据配置：

{
    "api": "insert",
    "effect": "EXECUTE",
    "method": "POST",
    "idCheck": true,
    "request": [
        {
            "field": "main_params",
            "label": "主表参数",
            "type": "object",
            "describe": "主表参数",
            "children": [
                {"field":"refund_version","label":"售后类型","type":"string","describe":"售后类型（refund_version = 1000 代表送仓售后）","value":"{type}"},
                {"field":"items_name","label":"商品名称","type":"string","describe":"商品名称","value":"{items_name}"},
                {"field":"status","label":"售后单状态","type":"string","describe":"售后单状态：WaitConfirm:待确认,Confirmed:已确认,Cancelled:作废,Merged:被合并","value":"{status}"},
                {"field":"shop_buyer_id","label":"买家账号","type":"string","describe":"买家账号","value":"{shop_buyer_id}"},
                {"field":"as_id","label":"内部售后单号","type":"string","describe":"内部售后单号","value":"{as_id}"},
                {"field":"order_status","label":"原订单状态","type":"string","describe":"原订单状态","value":"{order_status}"},
                {"field":"o_id","label":"内部单号","type":"string","describe":"内部单号","value":"{o_id}"},
                {"field":"shop_type","label":"线上类型","type":"string","describe":"线上类型","value":"{shop_type}"},
                {"field":"refund","label":"退款金额(退货金额+卖家应补)","type":"float","describe":"退款金额(退货金额+卖家应补)","value":"{refund}"},
                {"field":"good_status",...}
            ]
        }
    ],
    "otherRequest": [
        {
            "field": "main_sql",
            "label": "主SQL语句",
            "type": "string",
            "describe": "主SQL语句",
            "value": 
              `INSERT INTO shtk (
                  refund_version,
                  items_name,
                  status,
                  shop_buyer_id,
                  as_id,
                  order_status,
                  o_id,
                  shop_type,
                  refund,
                  good_status,
                  free_amount,
                  items_shop_amount,
                  order_type,
                  outer_as_id,
                  warehouse,
                  items_r_qty,
                  items_receive_date,
                  items_amount,
                  items_sku_type,
                  drp_co_id_from,
                  as_date,
                  items_properties_value,
                  items_outer_oi_id,
                  items_remark,
                  node, 
                   ...
              ) VALUES (
                   :refund_version, 
                   :items_name, 
                   :status, 
                   :shop_buyer_id, 
                   :as_id, 
                   :order_status, 
                   :o_id, 
                   :shop_type, 
                   :refund, 
                   :good_status, 
                   :free_amount, 
                   :items_shop_amount, 
                   :order_type, 
                   :outer_as_id, 
                   :warehouse, 
                    ...
              )`
        }
    ]
}

数据清洗与转换

在ETL过程中，数据清洗和转换是关键步骤。我们需要确保从源系统提取的数据能够准确地映射到目标系统的字段中。通过上述元数据配置，我们可以定义每个字段的映射关系。例如：

refund_version 映射到 SQL Server 中的 refund_version 字段
items_name 映射到 SQL Server 中的 items_name 字段
status 映射到 SQL Server 中的 status 字段

这些映射关系通过 JSON 配置中的 "value" 属性来定义，例如 "value": "{type}" 表示将源系统中的 type 字段值映射到目标系统中的 refund_version 字段。

执行SQL插入操作

在完成数据清洗和转换之后，我们需要将处理后的数据写入目标数据库。通过配置 "main_sql"，我们可以定义插入操作所需的 SQL 语句：

INSERT INTO shtk (
    refund_version,
    items_name,
    status,
    shop_buyer_id,
    as_id,
    order_status,
    o_id,
    shop_type,
    refund,...)
VALUES (
   :refund_version, 
   :items_name, 
   :status,...)

这里使用了占位符（如 :refund_version），这些占位符将在执行时被实际的数据值替换。

API接口调用

最后，通过调用配置好的API接口，将处理后的数据发送到目标平台。根据元数据配置中的 "method" 和 "api" 属性，使用 POST 方法调用插入接口：

POST /insert HTTP/1.1
Host: target-sql-server.com
Content-Type: application/json

{
  "main_params": {
      ...
  }
}

该请求会触发执行预先定义好的 SQL 插入语句，将处理后的数据写入 SQL Server 数据库。

通过上述步骤，我们实现了从聚水潭提取售后单查询数据，经过ETL转换后，成功写入SQL Server数据库。这一过程充分利用了轻易云数据集成平台的强大功能，实现了不同系统间的数据无缝对接和高效处理。用友与CRM系统接口开发配置

版权声明

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