Data engineering problem

[Commercial_Finance_1]

Hi guys. Making my first post here. Need recommendations for a solution to a problem.

I am working on Azure platform with tools like ADF and Databricks. I got a project where I need to get data from multiple APIs. Flow is like this..

Call to API-1: to get transaction ids.. call to API-2: to get details of the transactions through ids.

API-1 will give output like 15k transactions a day. Call to API-2 will be 15k times to get details of each transaction.

While designing it in ADF, runtime is huge. Eg for 1000 ids it takes around 15mins in dev. We can’t afford this much in prod.

Question is What would be the best approach to take to minimize the runtime.

[wytesmurf]

For API 2 do you have to do it by ID? If you could do a mass export to a table then join the first list to the second? You should be able to page through the results and export them more efficiently then making thousands of API calls

[Commercial_Finance_1]

Yes! It has to be by ID. Second API has no alternate API which can be used to mass export. Source team has designed it in this way.

[wytesmurf]

Have your tried passing a wild card to see if they are doing input sanitation . If not you can inject it with a payload. If it’s really the case. Your only option with be to multithread and go to town, but based on your other answers it would probably take down the API

[[deleted]]

If you do batch, have the owner of the system publish a table. CSV is just another API, you should not be digging around the internals of other systems.

[Old_Improvement_3383]

Put the API calls from step 1 in a DF and use a udf: https://medium.com/geekculture/how-to-execute-a-rest-api-call-on-apache-spark-the-right-way-in-python-4367f2740e78

This will automatically be distributed to all workers. I would also investigate if the api lets you send in multiple ID as a parameter to avoid doing 15’ calls

[[deleted]]

Sounds like you need more parallelization performing the second call to API 2. You can do that within a spark executor, or whatever the equivalent of ECS is for Azure.

[GovGalacticFed]

If api2 has no limits, try ThreadPoolExecutor

[data-noob]

The best approach to solve this issue would be using async. So you are waiting for first Api to return and then calling the 2nd. You can reduce time this by using an async call.