Azure 数据工厂将 2 列映射到一列 [英] Azure Data Factory mapping 2 columns in one column

问题描述

有人可以帮我解决连接两列的错误，即first name 和 last name 从我的文本文件中，并将两列合并为一个名称列在我的 Azure SQL 数据库 中作为 Azure 数据工厂 中的 sink 另一个问题是我想选择列性别的第一个字母是 M 或 F 分别代表 male 和来自源文本文件的女性，并在我的性别列中将其更改为一个字母 M 或 FAzure 数据工厂管道

第 2 步:在复制活动中配置 Sink 部分，如下所示:

第 3 步:在您的数据库中，定义与 sqlWriterTableType 同名的表类型.请注意，表类型的架构应与输入数据返回的架构相同.

CREATE TYPE [dbo].[MarketingType] AS TABLE([名字] [varchar](256) 非空，[姓氏] [varchar](256) 非空，[性别] [varchar](256) 非空)

第 4 步:在您的数据库中，定义与 SqlWriterStoredProcedureName 同名的存储过程.它处理来自您指定源的输入数据，并合并到输出表中.注意存储过程的参数名要和dataset中定义的tableName"一致.

创建程序 spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY作为开始合并 [dbo].[jay] 作为目标使用@Marketing 作为源开启 (1=1)当不匹配时插入(姓名、性别)VALUES (source.FirstName + ' ' + source.LastName, UPPER(left(source.Gender,1)));结尾

输出截图:

希望对你有帮助.

Can somebody help me solving the error am getting in concatenating the two columns i.e first name and last name from my text file and merging the two columns into one name column in my Azure SQL database as a sink in Azure Data Factory and another question is that I want to choose the first letter of the column gender that is M or F for male and female respectively from the source text file and changing it to one letter M or F in my gender column in the Azure data factory pipeline enter image description here?

• Update 1

My table name is [dbo].[Contact] and after applying this procedure am getting this error, and my columns names in the text file has space in between them, like First Name and Last Name, does that create a problem too?

enter image description here

解决方案

Based on the doc: Schema mapping in copy activity, merging columns is supported by schema mapping.

As workaround , I suggest configure sql server stored procedure in your sql server sink. It can merge the data being copied with existing data.

Please follow the steps from this doc:

Step 1: Configure your Output dataset:

Step 2: Configure Sink section in copy activity as follows:

Step 3: In your database, define the table type with the same name as sqlWriterTableType. Notice that the schema of the table type should be same as the schema returned by your input data.

CREATE TYPE [dbo].[MarketingType] AS TABLE(
    [FirstName] [varchar](256) NOT NULL,
    [LastName] [varchar](256) NOT NULL,
    [Gender] [varchar](256) NOT NULL
)

Step 4: In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. It handles input data from your specified source, and merge into the output table. Notice that the parameter name of the stored procedure should be the same as the "tableName" defined in dataset.

Create PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY
AS
BEGIN
  MERGE [dbo].[jay] AS target
  USING @Marketing AS source
  ON (1=1)
  WHEN NOT MATCHED THEN
      INSERT (name, gender)
      VALUES (source.FirstName + ' ' + source.LastName, UPPER(left(source.Gender,1)));
END

Output Screenshot:

Hope it helps you.

这篇关于Azure 数据工厂将 2 列映射到一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！