怎么使用 ADF 的 map() 函数

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

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:

怎么使用 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查询

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);}

结果
怎么使用 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

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

怎么使用 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:

确定