REST Endpoint Invocation is now Generally Available

Brian Spendolini

On behalf of the Engineering and Product Management teams, we are happy to announce that External REST Endpoint Invocation is now GA!

What is External REST Endpoint Invocation?

External REST Endpoint Invocation makes it possible for developers to call REST/GraphQL endpoints from other Azure Services from right in the Azure SQL Database. With a quick call to the system stored procedure sp_invoke_external_rest_endpoint, you can have data processed via an Azure Function, update a PowerBI dashboard, or even talk to Cognitive Services. In fact, just before this GA date, we enabled access to OpenAI, Azure Blob Storage, Azure Files, Azure Queue Services and Azure Table Services. This feature also supports header and managed identity authentication, so you can get rid of passing pesky passwords.

Use cases for this feature are many and here are a few to get your creative spark going:

Activate workflows

• Send an email / notification using Azure Logic Apps

Data enrichment

• Determine if a value is an outlier or not using Azure Cognitive services
• Perform reverse geocoding using Azure Functions
• Call a REST/GraphQL service to add external information (eg: weather, reviews, etc) to your data

Cache invalidation / update

• For cache invalidation or update by calling an Azure Function

Start complex processing

• Call a Durable Function to kick of some complex process

Update websites

• Broadcast a SignalR message

Integrate with event-based architectures

• Send data to Event Hubs for further integration options

Create a data stream

• Send data to Stream Analytics for further investigation/fraud detection

Endpoints Everywhere!

Here is the latest list of services that can be used with External REST Endpoint Invocation. We are constantly evaluating new ones and if you have any ideas, please let us know.

Getting Started

Getting started with External REST Endpoint Invocation is easy; it’s just a simple stored procedure you call.

EXEC @returnValue = sp_invoke_external_rest_endpoint [ @url = ] N'url’ 
[ , [ @payload = ] N'json_payload’ ] 
[ , [ @headers = ] N'http_headers_as_json_array’ ] 
[ , [ @method = ] 'GET' | 'POST' | 'PUT' | 'PATCH' | 'DELETE’ | 'HEAD' ] 
[ , [ @timeout = ] seconds ] 
[ , [ @credential = ] credential ] 
[ , @response OUTPUT ]

For example, if I wanted to ask ChatGPT “why is the sky blue”, I could issue the following T-SQL command:

declare @url nvarchar(4000) = 
N'https://openaidemo1.openai.azure.com/openai/deployments/talk-to-me-goose/chat/completions?api-version=2023-03-15-preview';
declare @headers nvarchar(102) = N'{"api-key":"12345,678910,1112"}'
declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"Why is the sky blue?"}]}'
declare int, @response nvarchar(max);

exec = sp_invoke_external_rest_endpoint 
@url = @url,
@method = 'POST',
@headers = @headers,
@payload = @payload,
@timeout = 230,
@response = @response output;

select as ReturnCode, @response as Response;

With the response from ChatGPT being similar to the following:

"message": {
"role": "assistant",
"content": "The sky appears blue due to a phenomenon called Rayleigh scattering.
When sunlight enters Earth's atmosphere, it encounters molecules like nitrogen and oxygen,
which are much smaller than the wavelength of visible light. These molecules scatter
shorter wavelengths of light (blue and violet) more than longer wavelengths (red and
orange). As a result, the blue light gets scattered in all directions, filling the sky
with a blue hue."
}

And once you were done asking ChatGPT about life, the universe and lunch, you could call an Azure Function and maybe actually get some work done:

DECLARE INT, @response NVARCHAR(MAX);

EXEC = sp_invoke_external_rest_endpoint
@url = N'https://myfunction.azurewebsites.net/api/convertCurrencyFromUSD',
@payload = N'{"To":"Yen"}',
@method = N'POST',
@response = @response OUTPUT;

SELECT AS ReturnCode, @response AS Response;

In this example, I can send a JSON payload to an Azure Function and get some currency converted. I could then take the response to this request and say apply it to a table in my Azure SQL Database by updating a column, row or value.

One last example. This one takes from Drew Skwiers-Koballa’s blog post using Azure Cognitive Services. This example is going to pass a string of text to Content Moderator and ask it to identify any personal identifying information and any profanity. The use case from the blog post is to check entries to a forum before they are posted.

The code is as follows:

declare @url nvarchar(4000) = 
N'https://coggsmccoggsface.cognitiveservices.azure.com/contentmoderator/moderate/v1.0/ProcessText/Screen/?language=eng&autocorrect=False&PII=True&listId=&classify=True';
declare @headers nvarchar(102) = N'{"Ocp-Apim-Subscription-Key":"1001001sos1001001","Content-Type" : "text/plain"}'
declare @payload nvarchar(max) = N'{"Is this a crap email abcdef@abcd.com, phone: 6657789887, IP: 255.255.255.255, 1 Microsoft Way, Redmond, WA 98052"}'
declare @ret int, @response nvarchar(max);

exec @ret = sp_invoke_external_rest_endpoint 
    @url = @url,
    @method = 'POST',
    @headers = @headers,
    @payload = @payload,
    @timeout = 230,
    @response = @response output;
    
select @ret as ReturnCode, @response as Response;

With the response indicating that yes, there is PII included in the text as well as a potentially profane word:

        "PII": {
            "Email": [
                {
                    "Detected": "abcdef@abcd.com",
                    "SubType": "Regular",
                    "Text": "abcdef@abcd.com",
                    "Index": 23
                }
            ],
            "IPA": [
                {
                    "SubType": "IPV4",
                    "Text": "255.255.255.255",
                    "Index": 63
                }
            ],
            "Phone": [
                {
                    "CountryCode": "US",
                    "Text": "6657789887",
                    "Index": 47
                }
            ],
            "Address": [
                {
                    "Text": "1 Microsoft Way, Redmond, WA 98052",
                    "Index": 80
                }
            ],
            "SSN": []
        },
        "Classification": {
            "ReviewRecommended": true,
            "Category1": {
                "Score": 0.00040505084325559437
            },
            "Category2": {
                "Score": 0.2234508991241455
            },
            "Category3": {
                "Score": 0.9879999756813049
            }
        },
        "Language": "eng",
        "Terms": [
            {
                "Index": 5,
                "OriginalIndex": 12,
                "ListId": 0,
                "Term": "crap"
            }
        ],

You can find more examples such as using PowerBI and Azure Blob Storage with XML payloads in our GitHub Repository.

Thank You

With your help and feedback, this feature enjoyed a successful private and public preview. The team took your comments seriously and improved External REST Endpoint Invocation to better fit the use cases and needs brought to light. But we are not stopping there. We have many more improvements and features we would like to add and as always are seeking your feedback in the process.

Let us know if you like this feature, how you are using it, and additional functionality you would like to see using the comment section below to give us your feedback and to tell us how you’d like to see this feature evolving, or by using the Azure SQL and SQL Server feedback portal.

Again, thank you!

Engineering and Product Management

Resources

Documentation

Examples Repository

Brian Spendolini Senior Product Manager, Azure SQL Database

Follow