Bringing incremental data in from REST APIs into SQL azure

Question

My needs are following: - Need to fetch data from a 3rd party API into SQL azure.

• The API's will be queried everyday for incremental data and may require pagination as by default any API response will give only Top N records.

• The API also needs an auth token to work, which is the first call before we start downloading data from endpoints.

Due to last two reasons, I've opted for Function App which will be triggered daily rather than data factory which can query web APIs.

Is there a better way to do this? Also I am thinking of pushing all JSON into Blob store and then parsing data from the JSON into SQL Azure. Any recommendations?

Answer 1

How long does it take to call all of the pages? If it is under ten minutes, then my recommendation would be to build an Azure Function that queries the API and inserts the json data directly into a SQL database.

Azure Function

Azure functions are very cost effective. The first million execution are free. If it takes longer than ten, then have a look at durable functions. For handling pagination, we have plenty of examples. Your exact solution will depend on the API you are calling and the language you are using. Here is an example in C# using HttpClient. Here is one for Python using Requests. For both, the pattern is similar. Get the total number of pages from the API, set a variable to that value, and loop over the pages; Getting and saving your data in each iteration. If the API won't provide the max number of pages, then loop until you get an error. Protip: Make sure specify an upper bound for those loops. Also, if your API is flakey or has intermittent failures, consider using a graceful retry pattern such as exponential backoff.

Azure SQL Json Indexed Calculated Columns

You mentioned storing your data as json files into a storage container. Are you sure you need that? If so, then you could create an external table link between the storage container and the database. That has the advantage of not having the data take up any space in the database. However, if the json will fit in the database, I would highly recommend dropping that json right into the SQL database and leveraging indexed calculated columns to make querying the json extremely quick.

Using this pairing should provide incredible performance per penny value! Let us know what you end up using.

Answer 2

Maybe you can create a time task by SQL server Agent.

SQL server Agent--new job--Steps--new step:

In the Command, put in your Import JSON documents from Azure Blob Storage sql statemanets for example.

Schedules--new schedule: Set Execution time.

But I think Azure function is better for you to do this.Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. You can write just the code you need for the problem at hand, without worrying about a whole application or the infrastructure to run it. Functions can make development even more productive, and you can use your development language of choice, such as C#, F#, Node.js, Java, or PHP.

It is more intuitive and efficient.

Hope this helps.