Call azure maps api from azure sql

Question

i am running a fabric data factory pipeline to call various azure maps api’s and write into azure sql. The pipeline runs fine for scheduled tasks but can’t be called by a sql stored proc. i need to call the azure maps route matrix api from azure sql for a specific task as it hits a temp table and cannot be scheduled.

Is it possible to use the sp_invoke_external_rest_endpoint function to call the azure maps API in azure sql? I did not see azure maps in the supported azure endpoint list for this function.

alternatively should I use an azure function or API Management service to call the azure maps endpoint from azure sql with sp_invoke_external_rest_endpoint? I’m not proficient in python or C# so would prefer a low code or sql solution to this.

thanks

Shaun

Answer 1

There is a bunch of samples of calling REST services from Azure SQL here: https://learn.microsoft.com/en-us/samples/azure-samples/azure-sql-db-invoke-external-rest-endpoints/azure-sql-db-invoke-external-rest-endpoints/ I would follow the example that uses an Azure Function with Azure AD, and just swap out the Azure Function with the Azure Maps REST call. See the third section of this example: https://github.com/azure-samples/azure-sql-db-invoke-external-rest-endpoints/blob/main/azure-functions.ipynb For simplicity you will likely want to use the Azure Maps Route Matrix service in a synchronous way that way you don't need to make multiple calls to the service. https://learn.microsoft.com/en-us/rest/api/maps/route/post-route-matrix-sync?view=rest-maps-2023-06-01&tabs=HTTP Note that synchronous calls to the route matrix service have a limit of 100 "origin-destination pairs / cells". That said, I personally would not do this in SQL as this would put more strain on your database. Instead I would create an Azure function or app service that made the Azure Maps request, processed the data as needed and did a bulk insert into the database. This would put less load on the database and would also reduce the processing time of each connection that is doing this work (calling stored proc and waiting for REST response and inserting into table would hold a connection a lot longer than a bulk insert of data). This approach would make your solution a lot more scalable.