MMS • Steef-Jan Wiggers
Article originally posted on InfoQ. Visit InfoQ
Microsoft recently announced the public preview of Azure SQL Database External REST Endpoint Integration – an ability to call a REST endpoint natively from Azure SQL Database.
The public preview of Azure SQL Database External REST Endpoint Integration is a follow-up from this year’s earlier private preview. With the public preview, the new system stored procedure sp_invoke_external_rest_endpoint is available to everyone.
The integration massively reduces difficulties in integrating Azure services with Azure SQL Database. With only one line of code, according to the company, the Azure SQL database can integrate with a wealth of services such as Azure Functions, Cognitive Services, Event Hubs, and Azure containers.
The full syntax, according to the documentation, is as follows:
EXEC @returnValue = sp_invoke_external_rest_endpoint
@url
[ , [ @payload = ] N'json_payload' ]
[ , [ @headers = ] N'http_headers_as_json_array' ]
[ , [ @method = ] 'GET' | 'POST' | 'PUT' | 'PATCH' | 'DELETE' ]
[ , [ @timeout = ] seconds ]
[ , [ @credential = ] credential ]
[ , @response OUTPUT ]
The stored procedure accepts arguments like url (HTTPS REST endpoint to be called), payload (Unicode string in a JSON format), headers(as flat JSON), method(HTTP method calling the url GET, POST, PUT, PATCH, DELETE), timeout(time in seconds allowed for the HTTPS call to run), credential(inject authentication info), and response(response received from the called endpoint passed into a variable).
Developers can call a REST endpoint, like an Azure Function, for example:
declare @ret as int, @response as nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
@method = 'GET',
@url = 'https://my-function.azurewebsites.net/api/say?message=hello world',
@response = @response output;
select @ret as ReturnCode, @response as Response;
Christopher Webb, a member of the Power BI CAT team at Microsoft, mentions another example on his personal blog:
And guess what, the Power BI REST API is one of the APIs you can call! This means, for example, you can run DAX queries via the Execute Queries endpoint (bearing in mind all the limitations) from TSQL.
Glenn F. Henriksen, a CTO at Justify, tweeted:
Finally, we can move all our app logic into SQL-stored procedures and triggers! This was the final missing piece!
And in addition, Jeff Taylor, a senior data engineer, tweeted:
This looks incredible! This should make obtaining certain datasets faster and simpler and in stored procedures no less! A great addition to an already-known language!
Lastly, a GitHub repository contains more examples, with additional ones coming soon.