### 案例分享：金蝶云星空数据集成到吉客云 (方案名称：金蝶->吉客云辅助单位同步)
在此次技术案例中，我们将探讨如何利用轻易云数据集成平台，将金蝶云星空的数据无缝地对接到吉客云，以实现两个系统之间的高效、可靠和透明的数据交互。
为了保证集成过程中的数据不漏单，首先需要调用金蝶云星空的executeBillQuery接口来获取指定范围内的业务数据。该接口支持分页查询，有助于处理大量数据，并避免一次性传输时可能遇到的性能瓶颈。同时，为了确保定时可靠地抓取这些业务数据，我们采用了定时任务调度机制，可以灵活设置抓取频率，根据实际需求进行调整。
针对批量写入至吉客云系统的问题，通过优化API调用策略，大幅提升了写入效率。具体来说，我们使用erp.unit.create接口进行批量操作，实现多个记录的一次性插入，从而减少网络通信开销，提高整体处理速度。
当然，在整个对接过程中，不可避免会遇到一些挑战，例如两边系统的数据格式差异以及分页和限流问题。在这方面，我们通过自定义映射规则，解决了字段匹配的问题；同时，利用流水线控制技术，对分页结果逐步拉取并分别处理，以应对限流约束。此外，通过详细设计异常处理与错误重试机制，可以有效地捕捉并修正各种潜在问题，确保最终的数据一致性与完整性。
对于实时监控与日志记录功能，我们充分发挥轻易云平台提供的全生命周期管理能力，全程跟踪每一个步骤的执行情况。这不仅帮助我们快速识别和解决任何异常，还大大提高了整个项目实施过程中的透明度和可预见性。
总之，本次方案着眼于多维度优化，包括但不限于接口调用、分页/限流管理、异常处理及实时监控等各个环节，以实现高效稳定的数据集成。以下章节将进一步深入阐述具体实施细节及相关代码示例。
### 调用金蝶云星空接口executeBillQuery获取并加工数据
在数据集成过程中，调用源系统的API接口是至关重要的一步。本文将详细介绍如何使用轻易云数据集成平台调用金蝶云星空的`executeBillQuery`接口来获取并加工数据。
#### 接口配置与请求参数
首先，我们需要配置调用金蝶云星空的`executeBillQuery`接口。该接口使用POST方法，主要用于查询业务单据。以下是元数据配置中的关键字段：
```json
{
"api": "executeBillQuery",
"method": "POST",
"number": "FNumber",
"id": "FMATERIALID",
"pagination": {
"pageSize": 100
},
"idCheck": true,
"request": [
{"field":"FMATERIALID","label":"实体主键","type":"string","value":"FMATERIALID"},
{"field":"FNumber","label":"编码","type":"string","value":"FNumber"},
{"field":"FName","label":"名称","type":"string","value":"FName"},
{"field":"FSpecification","label":"规格型号","type":"string","value":"FSpecification"},
{"field":"FOldNumber","label":"旧物料编码","type":"string","value":"FOldNumber"},
{"field":"FBARCODE","label":"条码","type":"string","value":"FBARCODE"},
{"field":"FDescription","label":"描述","type":"string","value":"FDescription"},
{"field":"FMaterialGroup_FNumber","label":"物料分组","type":"string","value":"FMaterialGroup.FNumber"},
{"field":"FErpClsID","label":"物料属性","type":"string","value":"FErpClsID"},
{"field":"FDocumentStatus","label":"数据状态","type":"string","value":"FDocumentStatus"},
{"field":"FForbidStatus","label":"禁用状态","type":"string","value":"FForbidStatus"}
],
"otherRequest": [
{"field": "Limit", "label": "最大行数", "type": "string", "describe": "金蝶的查询分页参数", "value": "{PAGINATION_PAGE_SIZE}"},
{"field": "StartRow", "label": "开始行索引", "type": "string", "describe": "金蝶的查询分页参数", "value": "{PAGINATION_START_ROW}"},
{"field": "TopRowCount", "label": "返回总行数", "type": "int", "describe": "金蝶的查询分页参数"},
{"field": "FilterString", "label": "过滤条件", "type": "string",
"describe":
"示例写法 FSupplierId.FNumber = 'VEN00010' and FApproveDate>=",
"value":
"FApproveDate>={{LAST_SYNC_TIME|datetime}} And FIsSale=1"
},
{"field":
"FieldKeys",
"label":
"需查询的字段key集合",
"type":
"array",
"describe":
"金蝶分录主键ID格式：FPOOrderEntry_FEntryId,其它格式 FPurchaseOrgId.FNumber",
"parser":
{"name":
"ArrayToString",
"params":
","
}
},
{
"field":
"FormId",
"label":
"业务对象表单Id",
"type":
"string",
"describe":
"必须填写金蝶的表单ID如:PUR_PurchaseOrder",
"value":
"BD_MATERIAL"
}
]
}
```
#### 请求示例
通过上述配置，我们可以构建一个请求示例：
```json
{
"_api_": "/k3cloud/Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc",
"_method_": "_post_",
"_data_":{
"_FormId_ ": "_BD_MATERIAL_ ",
"_FieldKeys_ ": ["FMATERIALID "," FNumber "," FName "," FSpecification "," FOldNumber "," FBARCODE "," FDescription "," FMaterialGroup.FNumber "," FErpClsID "," FDocumentStatus "," FForbidStatus"],
"_FilterString_ ": "_FSupplierId.FNumber = 'VEN00010' and FApproveDate>={{LAST_SYNC_TIME|datetime}} And FIsSale=1_ ",
"_Limit_ ": _100_,
"_StartRow_ ": _0_
}
}
```
#### 数据处理与清洗
在获取到原始数据后，需要对其进行清洗和转换，以便后续的数据写入操作。以下是一些常见的数据清洗步骤：
1. **字段映射**：将原始数据中的字段映射到目标系统所需的字段。例如，将`FMATERIALID`映射为目标系统中的`material_id`。
2. **数据类型转换**：确保所有字段的数据类型符合目标系统的要求。例如，将字符串类型的日期转换为日期类型。
3. **数据过滤**：根据业务需求过滤掉不必要的数据。例如，只保留状态为“已审核”的记录。
#### 示例代码
以下是一个简单的数据处理示例代码：
```python
import requests
import json
# 定义请求头和URL
headers = {'Content-Type': 'application/json'}
url = 'https://your-k3cloud-url/k3cloud/Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc'
# 构建请求体
payload = {
"_api_": "/k3cloud/Kingdee.BOS.WebApi.ServicesStub.DynamicFormService.ExecuteBillQuery.common.kdsvc",
"_method_": "_post_",
"_data_":{
"_FormId_ ": "_BD_MATERIAL_ ",
"_FieldKeys_ ": ["FMATERIALID ",
," FNumber "
," FName "
," FSpecification "
," FOldNumber "
," FBARCODE "
," FDescription "
," FMaterialGroup.FNumber "
," FErpClsID "
," FDocumentStatus "
," FForbidStatus"],
"_FilterString_ ":
"_FSupplierId.FNumber = 'VEN00010' and
FApproveDate>={{LAST_SYNC_TIME|datetime}} And
FIsSale=1_",
"_Limit_ ": _100_,
"_StartRow_ ": _0_
}
}
# 发起请求并获取响应
response = requests.post(url, headers=headers, data=json.dumps(payload))
data = response.json()
# 数据处理与清洗
processed_data = []
for item in data:
processed_item = {
'material_id': item['FMATERIALID'],
'number': item['FNumber'],
'name': item['FName'],
'specification': item['FSpecification'],
# ...其他字段映射...
}
# 数据过滤示例：只保留状态为“已审核”的记录
if item['FDocumentStatus'] == 'C':
processed_data.append(processed_item)
# 输出处理后的数据
print(json.dumps(processed_data, indent=2))
```
通过上述步骤，我们可以成功地调用金蝶云星空的`executeBillQuery`接口，获取并加工所需的数据，为后续的数据写入做好准备。这一过程不仅确保了数据的一致性和准确性，还极大地提升了业务流程的效率。
### 将源平台数据转换并写入目标平台：吉客云API接口
在数据集成生命周期的第二步中，我们将重点探讨如何将已经从源平台（如金蝶）获取的数据进行ETL转换，并通过吉客云API接口写入目标平台。本文将详细介绍元数据配置和具体实现步骤。
#### 元数据配置解析
在进行ETL转换之前，首先需要理解元数据配置，这是确保数据正确转换并写入目标平台的关键。以下是元数据配置的详细解析：
```json
{
"api": "erp.unit.create",
"effect": "EXECUTE",
"method": "POST",
"idCheck": true,
"request": [
{"field": "outSkuCode", "label": "outSkuCode", "type": "string", "value": "{FNumber}"},
{"field": "skuBarcode", "label": "skuBarcode", "type": "string", "value": "{FBARCODE}"},
{"field": "unitName", "label": "unitName", "type": "string", "value": "{F_kda_Base_Name}"},
{"field": "assistCountRate", "label": "assistCountRate", "type": "string", "value": "{FBoxStandardQty}"},
{"field": "pBaseUnit", "label": "pBaseUnit", "type": "int"},
{"field": "unitWeight", "label": "unitWeight",
