怎么使用 ADF 的 map() 函数

huangapple go评论156阅读模式
英文:

How to use ADF map() function

问题

  1. map(item ->
  2. {
  3. "supervisor": item.supervisor,
  4. "department": item.department,
  5. "salary": toLong(replace(item.salary, "$", ""))
  6. })
英文:

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:

  1. {
  2. "companyName": "ExampleName",
  3. "departmentManager": [
  4. {
  5. "supervisor": "Jhon",
  6. "department": "Marketing",
  7. "salary": "$135000"
  8. },
  9. {
  10. "supervisor": "Emily",
  11. "department": "Human Resources",
  12. "salary": "$135000"
  13. }
  14. ],
  15. "id": "123456798"
  16. }

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:

  1. {
  2. "companyName": "ExampleName",
  3. "departmentManager": [
  4. {
  5. "supervisor": "Jhon",
  6. "department": "Marketing",
  7. "salary": 135000
  8. },
  9. {
  10. "supervisor": "Emily",
  11. "department": "Human Resources",
  12. "salary": 135000
  13. }
  14. ],
  15. "id": "123456798"
  16. }

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:

  1. map(item ->
  2. {
  3. "supervisor": item.supervisor,
  4. "department": item.department,
  5. "salary": toLong(replace(item.salary, "$", ""))
  6. })

But I am getting this error:

怎么使用 ADF 的 map() 函数

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的配置
怎么使用 ADF 的 map() 函数

Cosmos DB查询

  1. SELECT f.companyName, ARRAY(
  2. select c.supervisor,
  3. c.department,
  4. udf.convertToInt(replace(c.salary,"$","")) as salary FROM c
  5. in f.departmentManager)as departmentManager, f.id from f

udf.convertToInt的代码

  1. function convertToInt(strNum) {return parseInt(strNum);}

结果
怎么使用 ADF 的 map() 函数

方法2 使用数据流程

这是我创建的数据流程
怎么使用 ADF 的 map() 函数

  1. Source1 创建了与Cosmos DB帐户的源连接。

  2. flatten1 使用此活动,我将departmentManager结构展平为表格格式,使用Unroll byUnroll root的配置。

配置
怎么使用 ADF 的 map() 函数

结果/预览
怎么使用 ADF 的 map() 函数

  1. derivedColumn1 添加了一个带表达式的列departmentManager。在这里,我编写了将字符串转换为长整数的表达式。

表达式的代码
@(supervisor=departmentManager.supervisor, department=departmentManager.department, salary=toLong(replace(departmentManager.salary,"$",""))

配置
怎么使用 ADF 的 map() 函数

结果/预览
在这里,您可以看到salary的数据类型显示为long
怎么使用 ADF 的 map() 函数

  1. aggregate1 用于将展平的结构再次转换为Json结构。在Group by中使用了companyName,在Aggregates选项卡下,为departmentManager列使用了collect(departmentManager)表达式。

配置
怎么使用 ADF 的 map() 函数

怎么使用 ADF 的 map() 函数

结果/预览
怎么使用 ADF 的 map() 函数

英文:

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
怎么使用 ADF 的 map() 函数

Cosmos DB Query

  1. SELECT f.companyName,ARRAY(
  2. select c.supervisor,
  3. c.department,
  4. udf.convertToInt(replace(c.salary,"$","")) as salary FROM c
  5. in f.departmentManager)as departmentManager, f.id from f

Code for udf.convertToInt

  1. `function convertToInt(strNum) {return parseInt(strNum);}`;

Result

怎么使用 ADF 的 map() 函数

Approach2 using dataflow
Here is the flow I created
怎么使用 ADF 的 map() 函数

  1. Source1 is creating source connection with Cosmos DB account.
  2. flatten1 using this activity I have flatten the departmentManager structure to tabular format using configuration for Unroll by and Unroll root.

Configuration
怎么使用 ADF 的 map() 函数
Result-/Preview
怎么使用 ADF 的 map() 函数

  1. 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怎么使用 ADF 的 map() 函数

Result-/Preview
Here you can see datatype of salary is showing as long.怎么使用 ADF 的 map() 函数

  1. 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

怎么使用 ADF 的 map() 函数

怎么使用 ADF 的 map() 函数
Result-/Preview
怎么使用 ADF 的 map() 函数

huangapple
  • 本文由 发表于 2023年3月9日 22:59:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/75686328.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定