0
votes

I'm trying to get Azure Data Factory to read my REST API and put it in SQL Server. The source is a REST API and the sink is a SQL Server table.

I tried to do something like:

"translator": {
    "type": "TabularTranslator",
    "schemaMapping": {
        "$": "json"
    },
    "collectionReference": "$.tickets"
}

The source looks like:

{ "tickets": [ {... }, {...} ] }

Because of the poor mapping capabilities I'm choosing this path. I'll then split the data with a query. Preferbly I'd like to store each object inside tickets as a row with JSON of that object.

In short, how can I get the JSON output from the RestSource to a SqlSink single column text/nvarchar(max) column?

1
I just save the entire document into a VARCHAR(MAX) field then use JSON functions inside T-SQL to shred it out. What's the actual problem that you are having? - Nick.McDermaid
How do I get the actual json to a VARCHAR(MAX) field? DF keeps trying to parse it. - ferdyh
Before we get to the VARCHAR(MAX) thing, whata problem are you having? Error or incorrect data? TBH. I've only ever done this via Stream Analytics. I don't have access to ADF to play right now, but one way to do is to use the web Activity task, call the API and use the output to insert into a table...in theory - Nick.McDermaid
Well, we're trying to get the data to SQL server without providing a static schema. This is because the data loading team is different from the team that uses the data. When you leave the schema mapping empty, ADF tries to map it automatically, which works for flat JSON, where you only have keys in the first level. When you get to nested objects or arrays, it ignores those. So my solution would be to put the whole json in one column and query it from there. - ferdyh
In SQL, create a stored proc that takes a VARCHAR(MAX) parameter and inserts that into your table. In ADF, drop on a Web task. Put your REST call into it and test. Now add a Stored Procedure Task and call your stored proc. Import the parameter. In the Value field, set dynamic content and enter this @string(activity('Call REST').output) (where Call REST is the name of your Web task). That will call the API once and insert it. If you need to do it repeatedly you'll need to explain further. If this works you can write it up as an answer (or I will) - Nick.McDermaid

1 Answers

0
votes

I managed to solve the same issue by modifying mapping manually. ADF anyway tries to parse json, but from the Advanced mode you can edit json paths. Ex., this is the original schema parsed automatically by ADF https://imgur.com/Y7QhcDI Once opened in Advanced mode it will show full paths by adding indexes of the elements, something similar to $tickets[0][] etc Try to delete all other columns and keep the only one $tickets (the highest level one), in my case it was $value https://i.stack.imgur.com/WnAzC.jpg. As the result the entire json will be written into the destination column.

If there are pagination rules in place, each page will be written as a single row.