How to pull Json-format data from Azure Function to Azure SQL table

Anonymous
2021-11-17T22:24:54.993+00:00

I am trying to retrieve the Json-format data from Azure Function to final destination, Azure SQL Server table as a tabular format.

I was trying to use WebHook between Azure Function and Azure SQL because somebody in the community mentioned about it for using Azure Function (Http Trigger), but I am not positive about that.

I am not sure why I am using WebHook, and at this point, it does not seem to work...

150354-image.png

Data format until Azure Function is in Json-format, so I am not sure how it should be connected to SQL.

Do I have to add some type of script/programming (like in Python) between Azure Function/WebHook and SQL?
Or do I extract Json data to Blob storage and copy data from Blob to Azure SQL Server (like a link on this sentence)?

Would appreciated for any idea/feedback.

@MughundhanRaveendran-MSFT - If you have any suggestion, please let me know. Appreciated!

Thanks.

Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
5,404 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,234 questions
0 comments No comments
{count} votes

Accepted answer
  1. MartinJaffer-MSFT 26,226 Reputation points
    2021-11-18T19:05:37.49+00:00

    Hello @KingJava and welcome to Microsoft Q&A and thank you for sharing your research.

    There are multiple ways to go about this, but I think having the Azure Function write to blob, and then returning the blob path to Data Factory for use in Copy activity (Blob -> SQL) is the most solid way to go.

    Webhook would be useful for a process that takes so long the connection to ADF would time out. Personally, I haven't really used Webhook enough to be confident.

    The determining factors on what to use are:

    • Do we need to use Azure Function? Is Azure function transforming data, or just fetching? Some API's don't play well with ADF, and Function is used for fetching.
    • How deep or complex is the JSON? Copy Activity works for simple flattening. Data Flow is better for flattening complex or multiple nests in JSON. Another approach is to move the JSON as-is to the SQL and use SQL commands to digest the JSON into tabular format.
    • How big is the data? If the output of the Function is small, like a record or two, we can pass the output to Stored Proc activity for writing. Not recommended for big data.
    • Do you want to use ADF at all? I imagine it is possible to have your Function connect to your SQL directly. Not something I have done.
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.