As December approaches, it’s time for the winter 2023 update for External REST Endpoint Invocation. We have added four new Azure Service Endpoints: Bing Search, Azure Key Vault, Azure Communication Services, and Azure AI Search. Let’s look at a few examples of using these new endpoints.
Bing Search Endpoints
Bing Seach has many useful API endpoints other than just search; the endpoints include autosuggest, image search, news search, and spell check to name a few.
Search
Let’s start with a regular search example. In this code example and external REST endpoint Invocation, we are going to call the search endpoint the keyword “cats”:
declare @url nvarchar(4000) = N'https://api.bing.microsoft.com/v7.0/search?q=cats';
declare @headers nvarchar(102) = N'{"Ocp-Apim-Subscription-Key":"1234567890"}'
declare @ret int, @response nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
@url = @url,
@method = 'GET',
@headers = @headers,
@timeout = 230,
@response = @response output;
select @ret as ReturnCode, @response as Response;
with the response coming back JSON formatted looking similar to the following:
"name": "Cat - Wikipedia",
"url": "https:\/\/en.wikipedia.org\/wiki\/Cat",
"thumbnailUrl": "https:\/\/www.bing.com\/th?id=OIP._f_j2vs5T0Y_rdIRS1d85wHaEh&w=80&h=80&c=1&pid=5.1",
"isFamilyFriendly": true,
"displayUrl": "https:\/\/en.wikipedia.org\/wiki\/Cat",
"snippet": "Learn about the domestic cat, the only domesticated species in the family Felidae, and its origin,
evolution, and classification. Find out how the cat is used as a house pet, a farm cat, and a feral cat, and how it is conserved
and managed by humans.",
"dateLastCrawled": "2023-11-28T13:00:00.0000000Z",
"primaryImageOfPage": {
"thumbnailUrl": "https:\/\/www.bing.com\/th?id=OIP._f_j2vs5T0Y_rdIRS1d85wHaEh&w=80&h=80&c=1&pid=5.1",
"width": 80,
"height": 80,
"imageId": "OIP._f_j2vs5T0Y_rdIRS1d85wHaEh"
},
"cachedPageUrl": "http:\/\/cc.bingj.com\/cache.aspx?q=cats&d=4521063291041408&mkt=en-US&setlang=en-US&w=fXURDRBhSLcH8V2fBd070ARf3gUpVR0R",
"language": "en",
"isNavigational": false,
Auto Correct
We can spell/grammar check text using the next endpoint. Here, we are going to check the phrase “when its your turn turn, john, come runing” with the following code:
declare @url nvarchar(4000) = N'https://api.bing.microsoft.com/v7.0/spellcheck?text=when+its+your+turn+turn,+john,+come+runing';
declare @headers nvarchar(102) = N'{"Ocp-Apim-Subscription-Key":"1234567890"}'
declare @ret int, @response nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
@url = @url,
@method = 'GET',
@headers = @headers,
@timeout = 230,
@response = @response output;
select @ret as ReturnCode, @response as Response;
with the response highlighting the double word and the spelling mistake:
"result": {
"_type": "SpellCheck",
"flaggedTokens": [
{
"offset": 19,
"token": "turn",
"type": "RepeatedToken",
"suggestions": [
{
"suggestion": "",
"score": 0
}
]
},
{
"offset": 36,
"token": "runing",
"type": "UnknownToken",
"suggestions": [
{
"suggestion": "running",
"score": 0.7640906176011809
}
]
}
]
}
Auto Suggest
The last one will be auto suggest. All auto suggest algorithms are based off of Taylor Swift so we just need to run the following code to confirm:
declare @url nvarchar(4000) = N'https://api.bing.microsoft.com/v7.0/suggestions?q=who is t';
declare @headers nvarchar(102) = N'{"Ocp-Apim-Subscription-Key":"1234567890"}'
declare @ret int, @response nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
@url = @url,
@method = 'GET',
@headers = @headers,
@timeout = 230,
@response = @response output;
SELECT [suggestion]
FROM OPENJSON(@response,'$.result.suggestionGroups')
WITH([searchSuggestions] NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.[searchSuggestions])
WITH ([suggestion] NVARCHAR(MAX) '$.displayText');
with the results being:
who is taylor swift dating
who is the richest man in the world
who is the oldest person alive
who is tiresias
who is the best soccer player
who is the next bachelor
You can see that the top result is not "who is taylor swift" because everyone already knows that thus no need to waste bandwidth.
Azure AI Search
External REST endpoint Invocation now can use Azure AI Search to integrate AI with the Azure SQL Database. By indexing a data source with Azure AI Search, I can use the REST endpoint to query this data. Maybe I want to augment data in the database with an external data source or even perform a quick data load. Using this endpoint, I can do just that. Using the following code, we can provide the endpoint with “search=Mountain&$select=ProductID,Name,ProductNumber&$count=true” which means search for the word Mountain and return the fields ProductID, Name, and ProductNumber.
declare @url nvarchar(4000) = N'https://aisearchdemo.search.windows.net/indexes/azuresql-index/docs?api-version=2023-07-01-Preview&search=Mountain&$select=ProductID,Name,ProductNumber&$count=true&';
declare @headers nvarchar(102) = N'{"api-key":"1234567890"}'
declare @ret int, @response nvarchar(max);
exec @ret = sp_invoke_external_rest_endpoint
@url = @url,
@method = 'GET',
@headers = @headers,
@timeout = 230,
@response = @response output;
select @ret as ReturnCode, @response as Response;
with the results similar to the following:
"result": {
"@odata.count": 88,
"value": [
{
"@search.score": 6.7415657,
"ProductID": "784",
"Name": "Mountain-200 Black, 46",
"ProductNumber": "BK-M68B-46"
},
{
"@search.score": 5.185546,
"ProductID": "777",
"Name": "Mountain-100 Black, 44",
"ProductNumber": "BK-M82B-44"
},
{
"@search.score": 4.6209354,
"ProductID": "746",
"Name": "HL Mountain Frame - Black, 46",
"ProductNumber": "FR-M94B-46"
},
Summary
To sum this up, we have added four new Azure Service Endpoints: Bing Search, Azure Key Vault, Azure Communication Services, and Azure AI Search. Each link has the endpoint descriptions and what you need to call each service. They are all very simple to use but offer amazing functionality.
0 comments