将Rest API数据复制到Azure SQL DB的问题 [英] Issue with copying Rest API data to Azure SQL DB
问题描述
当我将Rest API数据复制到Azure SQL时，存在一些奇怪的问题。如果我使用ADF V2复制向导生成管道，它可以将3条以下记录插入到我的Azure SQL DB表中。
When I copy Rest API data to Azure SQL, there is weird issue. If I use ADF V2 Copy Wizard to generate the pipeline, it can insert 3 following records into my Azure SQL DB table.
taskId taskName error error_description
1 abc成功成功
2 cde成功成功
>
3 efg成功成功
taskId taskName error error_description
1 abc success success
2 cde success success
3 efg sucess success
如果我做任何微小修改，比如修改此管道上的管道描述之后，如果我运行更新的管道，它只能将1条记录复制到azure sql表中。
If I do any tiny modification like modifying the pipeline description on this pipeline, after that, if I run the updated pipeline, it can only copies following 1 record into the azure sql table.
taskId taskName error error_description
null null成功成功
taskId taskName error error_description
null null success success
注意：
Rest API返回的数据格式如下：
{
"data": [
{
"taskId": 1,
"taskName": "abc"
},
{
"taskId": 2,
"taskName": "efg"
},
{
"taskId": 3,
"taskName": "ggg"
}
],
"error": "success",
"error_Description": "successful"
}
管道json代码如下：
{
"name": "pipeline3",
"properties": {
"activities": [
{
"name": "Copy_v9u",
"type": "Copy",
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [
{
"name": "Source",
"value": "api/getTask"
},
{
"name": "Destination",
"value": "[abc].[table1]"
}
],
"typeProperties": {
"source": {
"type": "RestSource",
"httpRequestTimeout": "00:01:40",
"requestInterval": "00.00:00:00.010"
},
"sink": {
"type": "AzureSqlSink",
"preCopyScript": "truncate table abc.table1"
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"path": "taskId"
},
"sink": {
"name": "taskId",
"type": "Int32"
}
},
{
"source": {
"path": "taskName"
},
"sink": {
"name": "taskName",
"type": "String"
}
},
{
"source": {
"path": "$.error"
},
"sink": {
"name": "error",
"type": "String"
}
},
{
"source": {
"path": "$.error_Description"
},
"sink": {
"name": "error_Description",
"type": "String"
}
}
]
}
},
"inputs": [
{
"referenceName": "SourceDataset_v9u",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "DestinationDataset_v9u",
"type": "DatasetReference"
}
]
}
]
},
"type": "Microsoft.DataFactory/factories/pipelines"
}
在复制活动的映射上，我检查了"收集参考"。因为json结果中的"data"包含数组数据。
On the mapping of the copy activity, I checked the "Collection Reference" since the "data"on json result contains the array data.
我认为这是由于通过当前的ADF不会将collectionReference保存在json文件上，一旦我重新发布它，collectionReference信息就会以某种方式丢失。我之前的项目是工作文件，
我比较了管道json文件，我发现之前的一个使用了"schemaMapping"。而不是"映射"而不是"映射"前一个包含"collectionReference"，但是当前的一个不包含"collectionReference"。请参阅
以下代码3个月前管道复制Rest API到sql db：
I think it is caused by current ADF doesn't save collectionReference on the json file and once I republish it, the collectionReference information is lost somehow. My previous project is working file, I compared the pipeline json file, I found previous one uses "schemaMapping" instead of "mappings" and previous one contains "collectionReference", however current one doesn't contains "collectionReference". Please see following code for 3 months' ago pipeline copying Rest API to sql db:
"translator": {
"type": "TabularTranslator",
"schemaMapping": {
"$.count": "count",
"$.next": "next",
"$.previous": "previous",
"product_id": "product_id",
"product_name": "product_name",
"city": "city",
"country": "country"
},
"collectionReference": "$.results"
}
您能告诉解决方案解决此问题吗？
Can you please advise the solution to resolve this issue?
推荐答案
您能指点我正在使用的向导吗？
Can you please point me to the wizard which you are using ?
我认为一旦更新了ADF，架构映射设置就会丢失。正如你所提到的，JSON有效载荷是
Well I think that once you update the ADF the schemamapping setting is getting lost . As mentioned by you the JSON payload is
{
"data": [
{
"taskId": 1,
"taskName": "abc"
},
{
"taskId": 2,
"taskName": "efg"
},
{
"taskId": 3,
"taskName": "ggg"
}
],
"error": "success",
"error_Description": "successful"
}一旦你更新了adf，这就是输出。
and once you update the adf this is the output .
null null成功成功，
请注意，对象的两个成员（taskId和TaskName）都将丢失。
您可以在此处阅读有关架构映射的更多信息。
You can read more on the schema mapping here .
https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping#alternative-schema-mapping
https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping#alternative-schema-mapping
这篇关于将Rest API数据复制到Azure SQL DB的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
