Azure数据工厂将JSON复制到表中的行 [英] Azure Data Factory Copy JSON to a row in a table

问题描述

我有一个有效的ADF工作流，它使用For Each循环从GET API调用中复制数据，每次都基于查找JSON文件更改查询字符串，并将单独的文件另存为JSON文件到BLOB存储中.我有一个问题-是否可以使用(id，timestamp，filename，json)结构将该数据加载到SQL表中，这意味着将每个API调用结果存储在该表中的新的单独行中?我无法将字段映射到SQL最终表时遇到问题，因为我无法使用简单的item().File或获取现在存储在容器中的JSON文件的内容.

I have a working ADF workflow copying the data from GET API call using For Each loop changing the query string each time based on the lookup JSON file and saving separate files to a BLOB storage as JSON files. I have a question - is it possible to load this data into SQL table with a structure of (id, timestamp, filename, json) which means storing each API call result in this table in a new sperate row? I have a problem with mapping the fields to the SQL final table as I can't use simple item().File or get contents of the JSON file that is now stored in container.

推荐答案

您可以使用存储过程"活动将json对象沉入一列.

You can use Stored Procedure activity to sink the json object into one column.

我在这里做了一个简单的测试:

I made a simple test here:

1.我使用Lookup活动从rest api获取一个json数组.

1.I use Lookup activity to get a json array from a rest api.

1. 我在Azure SQL中创建sql表和存储过程:

create table dbo.Logs (
    _id bigint primary key IDENTITY(1,1),
    log nvarchar(max)
);

--Strored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spUpsertLogs]

@logs nvarchar(max)

AS

BEGIN

INSERT INTO dbo.Logs (log) values(@logs)

END

3.然后我设置Stroed过程活动，指定Stroed过程的名称和导入参数，使用表达式 @string(activity('Lookup1').output.value)转换json数组转换为String类型.

3.Then I set the Stroed procedure activity, specify the name and import parameters of the Stroed procedure, use expression @string(activity('Lookup1').output.value) to convert the json array to String type.

4.运行debug，将json数组复制到sql表的一列中.结果显示:

4.Run debug, the json array will be copied into one column in the sql table. The result shows:

希望我的回答对您有所帮助.将JSON文档存储在SQL Server中，可以引用

Hope my answer is helpful for you. Store JSON documents in SQL Server, you can reference here.

这篇关于Azure数据工厂将JSON复制到表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！