{"id":2666,"date":"2023-11-30T02:00:40","date_gmt":"2023-11-30T10:00:40","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=2666"},"modified":"2023-11-29T14:25:47","modified_gmt":"2023-11-29T22:25:47","slug":"winter-2023-service-update-for-external-rest-endpoint-invocation","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/winter-2023-service-update-for-external-rest-endpoint-invocation\/","title":{"rendered":"Winter 2023 Service Update for External REST Endpoint Invocation"},"content":{"rendered":"

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<\/a>, Azure Key Vault<\/a>, Azure Communication Services<\/a>, and Azure AI Search<\/a>. Let’s look at a few examples of using these new endpoints.<\/p>\n

Bing Search Endpoints<\/h2>\n

Bing Seach has many useful API endpoints other than just search; the endpoints include autosuggest<\/a>, image search<\/a>, news search<\/a>, and spell check<\/a> to name a few.<\/p>\n

Search<\/h3>\n

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”:<\/p>\n

\n
declare @url nvarchar(4000) = N'https:\/\/api.bing.microsoft.com\/v7.0\/search?q=cats';\r\ndeclare @headers nvarchar(102) = N'{\"Ocp-Apim-Subscription-Key\":\"1234567890\"}'\r\ndeclare @ret int, @response nvarchar(max);\r\n\r\nexec @ret = sp_invoke_external_rest_endpoint\r\n@url = @url,\r\n@method = 'GET',\r\n@headers = @headers,\r\n@timeout = 230,\r\n@response = @response output;\r\n\r\nselect @ret as ReturnCode, @response as Response;<\/pre>\n<\/div>\n
with the response coming back JSON formatted looking similar to the following:<\/div>\n
<\/div>\n
\n
\n
\"name\": \"Cat - Wikipedia\",\r\n\"url\": \"https:\\\/\\\/en.wikipedia.org\\\/wiki\\\/Cat\",\r\n\"thumbnailUrl\": \"https:\\\/\\\/www.bing.com\\\/th?id=OIP._f_j2vs5T0Y_rdIRS1d85wHaEh&w=80&h=80&c=1&pid=5.1\",\r\n\"isFamilyFriendly\": true,\r\n\"displayUrl\": \"https:\\\/\\\/en.wikipedia.org\\\/wiki\\\/Cat\",\r\n\"snippet\": \"Learn about the domestic cat, the only domesticated species in the family Felidae, and its origin, \r\nevolution, 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 \r\nand managed by humans.\",\r\n\"dateLastCrawled\": \"2023-11-28T13:00:00.0000000Z\",\r\n\"primaryImageOfPage\": {\r\n\"thumbnailUrl\": \"https:\\\/\\\/www.bing.com\\\/th?id=OIP._f_j2vs5T0Y_rdIRS1d85wHaEh&w=80&h=80&c=1&pid=5.1\",\r\n\"width\": 80,\r\n\"height\": 80,\r\n\"imageId\": \"OIP._f_j2vs5T0Y_rdIRS1d85wHaEh\"\r\n},\r\n\"cachedPageUrl\": \"http:\\\/\\\/cc.bingj.com\\\/cache.aspx?q=cats&d=4521063291041408&mkt=en-US&setlang=en-US&w=fXURDRBhSLcH8V2fBd070ARf3gUpVR0R\",\r\n\"language\": \"en\",\r\n\"isNavigational\": false,<\/pre>\n<\/div>\n<\/div>\n
<\/div>\n
Auto Correct<\/h3>\n
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:<\/span><\/div>\n
<\/div>\n
\n
\n
declare @url nvarchar(4000) = N'https:\/\/api.bing.microsoft.com\/v7.0\/spellcheck?text=when+its+your+turn+turn,+john,+come+runing';\r\ndeclare @headers nvarchar(102) = N'{\"Ocp-Apim-Subscription-Key\":\"1234567890\"}'\r\ndeclare @ret int, @response nvarchar(max);\r\n\r\nexec @ret = sp_invoke_external_rest_endpoint\r\n@url = @url,\r\n@method = 'GET',\r\n@headers = @headers,\r\n@timeout = 230,\r\n@response = @response output;\r\n\r\nselect @ret as ReturnCode, @response as Response;<\/pre>\n<\/div>\n<\/div>\n
with the response highlighting the double word and the spelling mistake:<\/div>\n
<\/div>\n
\n
\n
\"result\": {\r\n   \"_type\": \"SpellCheck\",\r\n   \"flaggedTokens\": [\r\n     {\r\n       \"offset\": 19,\r\n       \"token\": \"turn\",\r\n       \"type\": \"RepeatedToken\",\r\n       \"suggestions\": [\r\n          {\r\n            \"suggestion\": \"\",\r\n            \"score\": 0\r\n          }\r\n        ]\r\n     },\r\n     {\r\n       \"offset\": 36,\r\n       \"token\": \"runing\",\r\n       \"type\": \"UnknownToken\",\r\n       \"suggestions\": [\r\n         {\r\n           \"suggestion\": \"running\",\r\n           \"score\": 0.7640906176011809\r\n         }\r\n       ]\r\n     }\r\n   ]\r\n}<\/pre>\n<\/div>\n<\/div>\n
Auto Suggest<\/h3>\n
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:<\/div>\n
<\/div>\n
\n
\n
declare @url nvarchar(4000) = N'https:\/\/api.bing.microsoft.com\/v7.0\/suggestions?q=who is t';\r\ndeclare @headers nvarchar(102) = N'{\"Ocp-Apim-Subscription-Key\":\"1234567890\"}'\r\ndeclare @ret int, @response nvarchar(max);\r\n\r\nexec @ret = sp_invoke_external_rest_endpoint\r\n@url = @url,\r\n@method = 'GET',\r\n@headers = @headers,\r\n@timeout = 230,\r\n@response = @response output;\r\n\r\nSELECT [suggestion]\r\nFROM OPENJSON(@response,'$.result.suggestionGroups')\r\nWITH([searchSuggestions] NVARCHAR(MAX) AS JSON) A\r\nCROSS APPLY OPENJSON(A.[searchSuggestions])\r\nWITH ([suggestion] NVARCHAR(MAX) '$.displayText');<\/pre>\n<\/div>\n
<\/div>\n
with the results being:<\/div>\n
<\/div>\n<\/div>\n
who is taylor swift dating\r\nwho is the richest man in the world\r\nwho is the oldest person alive\r\nwho is tiresias\r\nwho is the best soccer player\r\nwho is the next bachelor\r\n\r\nYou can see that the top result is not \"who is taylor swift\" because everyone <\/span>already<\/span> knows that thus no need to waste bandwidth.<\/span><\/span><\/span>\r\n<\/pre>\n
Azure AI Search<\/h2>\n
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.<\/div>\n
<\/div>\n
\n
\n
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&';\r\ndeclare @headers nvarchar(102) = N'{\"api-key\":\"1234567890\"}'\r\ndeclare @ret int, @response nvarchar(max);\r\n\r\nexec @ret = sp_invoke_external_rest_endpoint\r\n@url = @url,\r\n@method = 'GET',\r\n@headers = @headers,\r\n@timeout = 230,\r\n@response = @response output;\r\n\r\nselect @ret as ReturnCode, @response as Response;<\/pre>\n<\/div>\n<\/div>\n
with the results similar to the following:<\/div>\n
<\/div>\n
\n
\"result\": {\r\n  \"@odata.count\": 88,\r\n  \"value\": [\r\n  \u00a0 \u00a0{\r\n  \u00a0 \u00a0\"@search.score\": 6.7415657,\r\n  \u00a0 \u00a0\"ProductID\": \"784\",\r\n  \u00a0 \u00a0\"Name\": \"Mountain-200 Black, 46\",\r\n  \u00a0 \u00a0\"ProductNumber\": \"BK-M68B-46\"\r\n  \u00a0 \u00a0},\r\n  \u00a0 \u00a0{\r\n  \u00a0 \u00a0\"@search.score\": 5.185546,\r\n  \u00a0 \u00a0\"ProductID\": \"777\",\r\n  \u00a0 \u00a0\"Name\": \"Mountain-100 Black, 44\",\r\n  \u00a0 \u00a0\"ProductNumber\": \"BK-M82B-44\"\r\n  \u00a0 \u00a0},\r\n  \u00a0 \u00a0{\r\n  \u00a0 \u00a0\"@search.score\": 4.6209354,\r\n  \u00a0 \u00a0\"ProductID\": \"746\",\r\n  \u00a0 \u00a0\"Name\": \"HL Mountain Frame - Black, 46\",\r\n  \u00a0 \u00a0\"ProductNumber\": \"FR-M94B-46\"\r\n  \u00a0 \u00a0},<\/pre>\n<\/div>\n
Summary<\/h2>\n
To sum this up, we have added four new Azure Service Endpoints: Bing Search<\/a>, Azure Key Vault<\/a>, Azure Communication Services<\/a>, and Azure AI Search<\/a>. 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.<\/div>\n
<\/div>\n
Resources and Links<\/h2>\n