在数据工厂或即时数据处理中预复制脚本 [英] Pre-copy script in data factory or on the fly data processing

问题描述

我正在从源，API复制数据，并将其复制到Azure SQL DB.但是在其中一列中，我得到了Json对象.

I am copying data from a source, an API, and copying it into Azure SQL DB. But in one of the column I am getting Json objects.

无论如何，我都可以在管道中使用动态参数(通过预复制脚本或其他方式)以仅从那些json对象中获取特定标签的值，以便我只能在该列中使用该值.唯一的限制是我无法更改接收器.它必须是Azure SQL DB.

Any way i can use dynamic parameters (either through Pre-copy script or something else) in the pipeline to only take value of a particular tag from those json objects so that i can have only that value in the column. Only constraint is that I can't change the sink. It has to be Azure SQL DB.

我得到的Json对象: [{"self":" https://xxxxxxxx.jira.com/rest/api/2/customFieldOption/11903 ，" value:"是，" id:" 11903}]

Json object I am getting: [{"self":"https://xxxxxxxx.jira.com/rest/api/2/customFieldOption/11903","value":"Yes","id":"11903"}]

我只希望'value'标签响应而不是完整的json.

And I want only 'value' tag response not the complete json.

推荐答案

预复制脚本是在复制新数据之前针对数据库运行的脚本，而不是修改要提取的数据.

The pre-copy script is a script that you run against the database before copying new data in, not to modify the data you are ingesting.

如果无法更改接收器，您可以做的是使用json的varchar字段将数据存储在其他表中.然后在管道中添加另一个活动，在其中获取这些数据并将其存储在实际表中.在第二个活动中，您可以使用t-sql语句修改复制活动的sqlQuery，以从中提取所需的值.

What you can do if you cannot change the sink, is store the data in a different table, using a varchar field for the json. Then add another activity in your pipeline, where you take this data and store it in the actual table. In this second activity, you can use t-sql statements to modify the sqlQuery of the copy activity to extract the value you want from it.

这在设计查询时将非常有用:

This will be useful when designing the query: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

希望这对您有所帮助！ :)

Hope this helped! :)

PS:在第二个活动中，使用它来获取值"

PS: in the second activity, use this to get the "value"

select JSON_VALUE(fieldWhereYouStoredTheJson, '$[0].value') as jsonValue from temporaryTable

这篇关于在数据工厂或即时数据处理中预复制脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！