如何使用Data Factory将JSON数据从REST API映射到Azure SQL [英] How to Map JSON data from a REST API to Azure SQL using Data Factory

问题描述

我在azure数据工厂中有一条新管道. 我创建了数据集，一个来自其余api(一个公共): https://www.alphavantage.co/query?function= TIME_SERIES_DAILY& symbol = MSFT& apikey = demo

I have a new pipeline in azure data factory. I created the dataset, one from the rest api (a public one): https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=MSFT&apikey=demo

然后我创建了一个azure sql表，其中的屏幕快照中显示了这些列

and then I created an azure sql table with columns shown in the screenshot

问题是，我不知道如何进行映射，因为这是一个复杂的JSON对象，因此我受Mapping Designer的限制:

The problem, is that I dont know how to do the mapping, as this is a complex JSON object, I am limited with the Mapping Designer:

如何映射日期?

推荐答案

我倾向于使用ELT方法，通过Web任务调用REST API，并将JSON存储在SQL表中，然后使用SQL分解JSON.像 OPENJSON .

I tend to use an ELT approach for these, calling the REST API with a Web task and storing the JSON in a SQL table and then shredding the JSON using SQL functions like OPENJSON.

示例管道:

使这种方法起作用的关键是存储过程参数上的表达式.这将从Web任务中获取整个JSON输出，并将其传递给proc.这是一个简单的日志记录过程，它将记录插入到日志表中:

The key to getting this approach to work is the expression on the stored procedure parameter. This takes the whole JSON output from the Web task and passes it in to the proc. This is a simple logging proc which inserts the record into a logging table:

@string(activity('Web1').output)

我登录到表，然后切碎JSON，或者您可以直接在存储的proc参数上使用OPENJSON，例如

I log to a table and then shred the JSON or you could use OPENJSON directly on the stored proc parameter, eg

--INSERT INTO ...
SELECT
    CAST( [key] AS DATE ) AS timeSeriesDate,
    JSON_VALUE ( x.[value], '$."1. open"' ) AS [open],
    JSON_VALUE ( x.[value], '$."2. high"' ) AS [high],
    JSON_VALUE ( x.[value], '$."3. low"' ) AS [low],
    JSON_VALUE ( x.[value], '$."4. close"' ) AS [close],
    JSON_VALUE ( x.[value], '$."5. volume"' ) AS [volume]

FROM dbo.myLog
    CROSS APPLY OPENJSON(logDetails , '$."Time Series (Daily)"' ) x
--WHERE logId = 23333;

我的结果:

这篇关于如何使用Data Factory将JSON数据从REST API映射到Azure SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！