How to Map JSON data from a REST API to Azure SQL using Data Factory

5
votes

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

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

enter image description here

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:

How do I map the date?

f

2
jsonazureazure-data-factory
Can you show us what the data you expected are? Something like how you want store these json data? – Leon Yue
in the first screenshot there is the table columns, id, stock, date and value, thats basically it. – Luis Valencia

2 Answers

2
votes

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.

Example pipeline:

Example ADF pipeline

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)

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;

My results:

My results

1
votes

Does the data have a structure? If so, you can generate a dummy file, place it in sink and do a one time mapping. If not, you can Lookup on the file, iterate over the content in a For Each Loop Container and insert details on to a SQL table.

E.g. 

insert <<your table>> 
select '@item().name', '@item().address.city', @item().value

The important thing to remember is to iterate at the correct array. Let me know if it's not clear. Not in front of a system right now, so can't add screenshots.