azure - 我们可以通过ADF解析json数组并将其复制到多个sql表中吗

Question

我想使用 azure 数据工厂解析一些 json 数据并将其复制到 azure sql 数据库中。

客户数据，如下所示:

    {
      "customerId": 125488,
      "firstName": "John",
      "lastName": "Smith",
      "age": 25,
      "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
      },
      "phoneNumber": [
        {
          "type": "home",
          "number": "212 555-1234"
        },
        {
          "type": "fax",
          "number": "646 555-4567"
        }
      ]
    }

我的目标是将 customerId、firstName、lastName 和age 放入客户表中，如下所示。

    create table dbo.customer (
        customerId int,
        firstName varchar(255),
        lastName varchar(255),
        age int
    )

这部分我已经使用复制数据(api 到 sql)完成了。我的下一个目标是将电话号码放入电话号码表中，如下所示。

    create table dbo.phonenumber (
        customerId int,
        phoneNumber varchar(255)
        phoneType varchar(255)
    )

我正在管道中使用复制事件将客户数据移至客户表中，但我无法将多个输出写入不同的表。我们可以在一个管道中做到这一点吗？

Answer 1

我认为您可以在复制事件中使用存储过程将数据复制到多个表中。
我创建了一个简单的测试，如下所示:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[uspCustomer] @json NVARCHAR(MAX)
AS  

BEGIN 
    INSERT INTO dbo.customer(customerId,firstName,lastName,age) 
        SELECT customerId,firstName,lastName,age 
        FROM OPENJSON(@json)
          WITH (
            customerId INT '$.customerId',
            firstName VARCHAR(255) '$.firstName',
            lastName VARCHAR(255) '$.lastName',
            age INT '$.age'
          );
    INSERT INTO dbo.phonenumber(customerId,phoneNumber,phoneType)
        SELECT customerId,phoneNumber,phoneType
        FROM OPENJSON(@json)
          WITH (
            customerId INT '$.customerId',
            phoneNumber VARCHAR(255) '$.phoneNumber[0].number',
            phoneType VARCHAR(255) '$.phoneNumber[0].type'
          );
    INSERT INTO dbo.phonenumber(customerId,phoneNumber,phoneType)
        SELECT customerId,phoneNumber,phoneType
        FROM OPENJSON(@json)
            WITH (
            customerId INT '$.customerId',
            phoneNumber VARCHAR(255) '$.phoneNumber[1].number',
            phoneType VARCHAR(255) '$.phoneNumber[1].type'
            );

END

以下是存储过程的测试。

DECLARE @json NVARCHAR(MAX);
SET @json = '{"customerId": 125488,"firstName": "John","lastName": "Smith","age": 25,"address": {"streetAddress": "21 2nd Street","city": "New York","state": "NY","postalCode": "10021"},"phoneNumber":[{"type": "home","number": "212 555-1234"},{"type": "fax","number": "646 555-4567"}]};'

exec [dbo].[uspCustomer] @json

结果如下:

仅此而已。