英文:
How to use ADF map() function
问题
map(item ->
{
"supervisor": item.supervisor,
"department": item.department,
"salary": toLong(replace(item.salary, "$", ""))
})
英文:
I need to perform a data transformation in ADF and I'm having difficulty understanding how to use the map() function in DataFlow.
In the input container, I have items with a format similar to the example below:
{
"companyName": "ExampleName",
"departmentManager": [
{
"supervisor": "Jhon",
"department": "Marketing",
"salary": "$135000"
},
{
"supervisor": "Emily",
"department": "Human Resources",
"salary": "$135000"
}
],
"id": "123456798"
}
I have a container in Cosmos that I retrieve several items and apply derived column and map() to transform the departmentManager column before sending it to another container. The output format will be as follows:
{
"companyName": "ExampleName",
"departmentManager": [
{
"supervisor": "Jhon",
"department": "Marketing",
"salary": 135000
},
{
"supervisor": "Emily",
"department": "Human Resources",
"salary": 135000
}
],
"id": "123456798"
}
As you can see, I am changing the type from string to long. How can I achieve this goal using the map() function?
I was trying to use the following approach:
map(item ->
{
"supervisor": item.supervisor,
"department": item.department,
"salary": toLong(replace(item.salary, "$", ""))
})
But I am getting this error:
I was looking at the documentation, but the example is not very helpful.
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expressions-usage#map
答案1
得分: 1
方法1 使用ADF的Copy Data/Lookup Activity和Cosmos DB查询来尝试将salary值转换为long。
Copy Data/Lookup Activity的配置
Cosmos DB查询
SELECT f.companyName, ARRAY(
select c.supervisor,
c.department,
udf.convertToInt(replace(c.salary,"$","")) as salary FROM c
in f.departmentManager)as departmentManager, f.id from f
udf.convertToInt的代码
function convertToInt(strNum) {return parseInt(strNum);}
结果
方法2 使用数据流程
这是我创建的数据流程
-
Source1 创建了与Cosmos DB帐户的源连接。
-
flatten1 使用此活动,我将departmentManager结构展平为表格格式,使用Unroll by和Unroll root的配置。
配置
结果/预览
- derivedColumn1 添加了一个带表达式的列departmentManager。在这里,我编写了将字符串转换为长整数的表达式。
表达式的代码
@(supervisor=departmentManager.supervisor, department=departmentManager.department, salary=toLong(replace(departmentManager.salary,"$",""))
配置
结果/预览
在这里,您可以看到salary的数据类型显示为long。
- aggregate1 用于将展平的结构再次转换为Json结构。在Group by中使用了companyName,在Aggregates选项卡下,为departmentManager列使用了
collect(departmentManager)
表达式。
配置
结果/预览
英文:
There are two different approaches I have tried converting the salary value to long.
Approach1 using Copy Data/Lookup Activity of ADF and Cosmos DB query.
Configuration for Copy Data/Lookup Activity
Cosmos DB Query
SELECT f.companyName,ARRAY(
select c.supervisor,
c.department,
udf.convertToInt(replace(c.salary,"$","")) as salary FROM c
in f.departmentManager)as departmentManager, f.id from f
Code for udf.convertToInt
`function convertToInt(strNum) {return parseInt(strNum);}`;
Result
Approach2 using dataflow
Here is the flow I created
- Source1 is creating source connection with Cosmos DB account.
- flatten1 using this activity I have flatten the departmentManager structure to tabular format using configuration for Unroll by and Unroll root.
Configuration
Result-/Preview
- derivedColumn1 added a column departmentManager with expression. Here I have written the expression to convert string to long conversion.
Code for expression
@(supervisor=departmentManager.supervisor,
department=departmentManager.department,
salary=toLong(replace(departmentManager.salary,"$","")))
Configuration
Result-/Preview
Here you can see datatype of salary is showing as long.
- aggregate1 used this to convert flatten structure again to Json structure. Used companyName in for Group by and under Aggregates tab used
collect(departmentManager)
expression for departmentManager column.
Configuration
Result-/Preview
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论