recurring api call and process json files to add data in dedicated sql pool

Question

Hi ,

I have below requirment for enterprise product . please suggest best design.

1-: Recurring API call need to be made to fetch json data .

2-: These json data should be written as json files to azure data lake storage .

3-: We have developed python code which will process these json files as per requirements .

4-: This python code should be executed on hourly basis to process all json files of one hour.

where we should deploy this code for best performance as one hour data would be around 7-8 GB and should be processed in max 15 minutes .

Can we link all these requirments in azure synapse pipeline or if we use azure logical apps , how python code woule be executed .

Please share best design as per azure expertise .

Regards

Amit

Answer 1

Azure Synapse Analytics and Azure Logic Apps can be used together to create an effective solution. Here's a suggested design:

Recurring API Calls:

• Use Azure Logic Apps to schedule and trigger recurring API calls. Logic Apps provides built-in connectors to interact with various APIs.
• Configure the Logic App to call the API at the desired interval (hourly in your case) and retrieve the JSON data.

Writing JSON data to Azure Data Lake Storage:

• After retrieving the JSON data, use the Azure Blob Storage connector in Logic Apps to write the JSON data as files to Azure Data Lake Storage (ADLS). ADLS provides a scalable and reliable storage solution for big data workloads.

Processing JSON files with Python code:

• Deploy your Python code as an Azure Function or Azure Databricks notebook. Both options support executing Python code in a scalable and managed environment.
• Use the ADLS connector within your Python code to read the JSON files from ADLS and process them as per your requirements.

Executing Python code on an hourly basis:

• Create an Azure Synapse Pipeline to orchestrate the execution of your Python code on an hourly basis.
• Use the "Schedule" trigger in the pipeline to define the recurrence interval (hourly).
• Within the pipeline, add an activity (such as an Azure Databricks Notebook activity or an Azure Function activity) to run your Python code.

Regarding performance considerations:

• Azure Synapse Analytics provides a dedicated SQL pool (formerly SQL Data Warehouse) that can handle large data volumes and provide high-performance querying capabilities. You can consider storing the processed data in the dedicated SQL pool if it aligns with your requirements.
• While processing the JSON files, you can leverage Azure Synapse Spark capabilities for distributed data processing to achieve better performance and scalability.

Overall, this design allows you to schedule API calls, store JSON data in ADLS, process the data using Python code, and execute the Python code on an hourly basis with the help of Azure Logic Apps and Azure Synapse Analytics. It provides a scalable and managed environment to handle large data volumes efficiently.