{"id":2364,"date":"2023-09-05T04:00:40","date_gmt":"2023-09-05T11:00:40","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=2364"},"modified":"2023-09-04T16:03:27","modified_gmt":"2023-09-04T23:03:27","slug":"using-openai-rest-endpoints-with-azure-sql-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/using-openai-rest-endpoints-with-azure-sql-database\/","title":{"rendered":"Using OpenAI REST Endpoints with Azure SQL Database"},"content":{"rendered":"

Everyone loves OpenAI these days as it can do some amazing things. Here, I asked it to create a picture of a cat in a data center:<\/p>\n

\"Image<\/a><\/p>\n

(Go ahead and click the image to see a bigger version of this nightmare fuel)<\/em><\/p>\n

Incredible!!!!<\/p>\n

But how can we use OpenAI in real world applications and scenarios with Azure SQL Database?<\/p>\n

Using OpenAI with the Azure SQL Database<\/h2>\n

Taking inspiration from Buck Woody’s blog “Secure your AI using SQL Server Machine Learning with Microsoft Azure OpenAI Services<\/a>“, what can we do in Azure SQL Database if we don’t have access to SQL Server Machine Learning Services<\/a>? We can use External REST Endpoint Invocation<\/a> of course! So, let’s take a look at how to accomplish this step by step.<\/p>\n

Setups<\/h2>\n

First, we need access to OpenAI in Azure<\/a>. You can request access to this new service with this form here<\/a>.<\/p>\n

https:\/\/aka.ms\/oai\/access<\/p>\n

Once you have access, creating an OpenAI service is very simple in the Azure Portal.<\/p>\n

\"Image<\/a><\/p>\n

Click the Azure OpenAI symbol<\/strong>, provide it with some basic information like resource group, name, and location (use US East if you want to play around with DALL-E) and you are done.<\/p>\n

Once the resource is created, click the Model Deployments optio<\/strong>n on the left side of the page, then click Manage Deployments<\/strong> to open OpenAI Studio in the browser.<\/p>\n

\"Image<\/a><\/p>\n

Now in Azure AI Studio, click on the Deployments tab<\/strong> on the left if not already selected then click Create new deploymen<\/strong>t.<\/p>\n

\"Image<\/a><\/p>\n

In the Deploy Model<\/strong> modal window, choose gpt-35-turbo<\/strong>. The gpt-35-turbo-16k option is for when you need very lengthy responses (perfect for those term papers) but does cost a bit more per request. For this example, we will not need the 16k option.<\/p>\n

\"Image<\/a><\/p>\n

Next, name the model in the Deployment nam<\/strong>e field. Then click the blue Create<\/strong> button.<\/p>\n

\"Image<\/a><\/p>\n

And that’s it, you now have your own personal ChatGPT prompt to talk to endlessly about live, the universe and who the Witches of Dathomir are.<\/p>\n

Ask it a question<\/h2>\n

While in Azure AI Studio, click the Chat<\/strong> tab on the left side of the screen.<\/p>\n

\"Image<\/a><\/p>\n

On the right of the page, we are going to use the Chat Session<\/strong> area to ask a question.<\/p>\n

\"Image<\/a><\/p>\n

Use the text field<\/strong> on the bottom to ask a question, then click the paper airplane icon<\/strong> to submit it.<\/p>\n

\"Image<\/a><\/p>\n

Within a few seconds, we get our answer<\/strong>.<\/p>\n

\"Image<\/a><\/p>\n

While still on this page, on the top of the chat session tile click the View Code<\/strong> button.<\/p>\n

\"Image<\/a><\/p>\n

In the Sample Code<\/strong> modal window, look at the bottom at the Endpoint<\/strong> and Key<\/strong> values. Copy these values for we will be using them later in the example.<\/p>\n

\"Image<\/a><\/p>\n

OpenAI REST Endpoints<\/h2>\n

Using the External REST Endpoint Invocation stored procedure, we can now ask our OpenAI service a question from within an Azure SQL Database using data from a table. For this example, the AdventureWorks sample data has been loaded so that we have something to work with just as Mr. Buck Woody did in his blog post<\/a>.<\/p>\n

To start using the stored procedure, open a new query sheet if in Azure Data Studio, use SSMS, or use sqlcmd; whichever you are most comfortable with. The first example is going to ask a simple question as we did in the chat session page. Let’s build up the procedure from the start as well.<\/p>\n

Starting to build our T-SQL code, we are going to declare and prefill some variables:<\/p>\n

Be sure to change the URL to match your service<\/strong><\/div><\/p>\n
\n
declare @url nvarchar(4000) = N'https:\/\/skynetbeta.openai.azure.com\/openai\/deployments\/chattykathy\/chat\/completions?api-version=2023-07-01-preview';\r\ndeclare @headers nvarchar(102) = N'{\"api-key\":\"1001001sos1001001indistress\"}'\r\ndeclare @payload nvarchar(max) = N'{\"messages\":[{\"role\":\"system\",\"content\":\"Why is the sky blue?\"}]}'\r\ndeclare @ret int, @response nvarchar(max);<\/pre>\n<\/div>\n
First, we start by declaring the URL<\/strong> or the Endpoint we want to use. Remember a bit back where we copied the Endpoint in the Sample Code modal<\/strong>? Use that URL here. Second, are the request headers<\/strong>. For this example, we are going to pass the api-key<\/strong> which allows us access to use the endpoint. Again, use the value you copied from the Sample Code modal key field<\/strong>. The payload variable will contain the chat prompt text we want to use to ask OpenAI a question. The last parameter we are declaring is the response<\/strong> variable. This will contain the answer<\/strong> gpt gives us in a JSON<\/strong> format.<\/p>\n
Putting the declaration section together with the procedure call produces this T-SQL you can run against your Azure SQL Database:<\/p>\n
\n
declare @url nvarchar(4000) = N'https:\/\/skynetbeta.openai.azure.com\/openai\/deployments\/chattykathy\/chat\/completions?api-version=2023-07-01-preview';\r\ndeclare @headers nvarchar(102) = N'{\"api-key\":\"1001001sos1001001indistress\"}'\r\ndeclare @payload nvarchar(max) = N'{\"messages\":[[{\"role\":\"system\",\"content\":\"You are an AI assistant that helps people find information.\"},\r\n                                   {\"role\":\"system\",\"content\":\"Why is the sky blue?\"}]}'\r\ndeclare @ret int, @response nvarchar(max);\r\nexec @ret = sp_invoke_external_rest_endpoint\r\n  \u00a0 @url = @url,\r\n  \u00a0 @method = 'POST',\r\n  \u00a0 @headers = @headers,\r\n  \u00a0 @payload = @payload,\r\n  \u00a0 @timeout = 230,\r\n  \u00a0 @response = @response output;\r\nselect @ret as ReturnCode, @response as Response;<\/pre>\n
With the response containing the answer:<\/div>\n
\n
\"choices\": [\r\n  \u00a0 {\r\n  \u00a0 \u00a0 \u00a0 \"index\": 0,\r\n  \u00a0 \u00a0 \u00a0 \"finish_reason\": \"stop\",\r\n  \u00a0 \u00a0 \u00a0 \"message\": {\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"role\": \"assistant\",\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"content\": \"The sky appears blue because when the sun's light enters Earth's atmosphere,\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 it is scattered by the gases and particles in the air. This scattering causes the shorter\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 blue wavelengths of light to be dispersed more than the other colors in the spectrum.\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 This is known as Rayleigh scattering. As a result, when we look up at the sky during\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 the day, we see it as blue.\"\r\n  \u00a0 \u00a0 \u00a0 },\r\n  \u00a0 \u00a0 \u00a0 \"content_filter_results\": {\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"hate\": {\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"filtered\": false,\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"severity\": \"safe\"\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 },\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"self_harm\": {\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"filtered\": false,\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"severity\": \"safe\"\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 },\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"sexual\": {\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"filtered\": false,\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"severity\": \"safe\"\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 },\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"violence\": {\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"filtered\": false,\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"severity\": \"safe\"\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 }\r\n  \u00a0 \u00a0 \u00a0 }\r\n  \u00a0 }\r\n],<\/pre>\n
Congratulations, you have just called an OpenAI prompt from within an Azure SQL Database.<\/div>\n
<\/div>\n
Using Buck’s data<\/h2>\n
Going back to Buck’s blog post<\/a>, he used the adventure works product data to ask OpenAI to create some copy for a marketing campaign. We can replicate that exact flow by building the prompt we use in the payload parameter dynamically with T-SQL. Here is the query he used (slightly changed for Azure SQL DB’s sample data):<\/div>\n
<\/div>\n
\n
SELECT Name \u00a0+ ': ' + Description\r\n  FROM SalesLT.vProductAndDescription\r\n WHERE ProductID = 980 and Culture = 'en';<\/pre>\n
with the result being:<\/div>\n
<\/div>\n
Mountain-400-W Silver, 38: This bike delivers a high-level of performance on a budget.\r\nIt is responsive and maneuverable, and offers peace-of-mind when you decide to go off-road.<\/pre>\n
and we can create a prompt from this with the following T-SQL:<\/span><\/div>\n
<\/div>\n<\/div>\n
\n
\n
declare @adcopy VARCHAR(500);\r\n\r\nset @adcopy =\r\n(SELECT Name \u00a0+ ': ' + Description\r\nFROM SalesLT.vProductAndDescription\r\nWHERE ProductID = 980 and Culture = 'en');<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
And we would declare the payload with this new prompt as follows:<\/div>\n
\n
\n
declare @payload nvarchar(max) = N'{\"messages\":[{\"role\":\"system\",\"content\":\"You are an AI assistant that helps people find information.\"},\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{\"role\":\"user\",\"content\":\"You are an experienced marketing expert. Generate 2000 letters of ad copy for '+(@adcopy)+'\"}]}'<\/pre>\n<\/div>\n
Putting it all together, we have the following T-SQL:<\/p>\n<\/div>\n
<\/div>\n
\n
\n
declare @adcopy VARCHAR(500);\r\n\r\nset @adcopy =\r\n(SELECT Name \u00a0+ ': ' + Description\r\nFROM SalesLT.vProductAndDescription\r\nWHERE ProductID = 980 and Culture = 'en');\r\n\r\ndeclare @url nvarchar(4000) = N'https:\/\/skynetbeta.openai.azure.com\/openai\/deployments\/chattykathy\/chat\/completions?api-version=2023-07-01-preview';\r\ndeclare @headers nvarchar(102) = N'{\"api-key\":\"1001001sos1001001indistress\"}'\r\ndeclare @payload nvarchar(max) = N'{\"messages\":[{\"role\":\"system\",\"content\":\"You are an AI assistant that helps people find information.\"},\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{\"role\":\"user\",\"content\":\"You are an experienced marketing expert. Generate 2000 letters of ad copy for '+(@adcopy)+'\"}]}'\r\ndeclare @ret int, @response nvarchar(max);\r\nexec @ret = sp_invoke_external_rest_endpoint\r\n  \u00a0 @url = @url,\r\n  \u00a0 @method = 'POST',\r\n  \u00a0 @headers = @headers,\r\n  \u00a0 @payload = @payload,\r\n  \u00a0 @timeout = 230,\r\n  \u00a0 @response = @response output;\r\nselect @ret as ReturnCode, @response as Response;<\/pre>\n<\/div>\n<\/div>\n
With the response being:<\/div>\n
<\/div>\n
\n
\"message\": {\r\n  \u00a0 \"role\": \"assistant\",\r\n  \u00a0 \"content\": \"Dear Outdoor Enthusiasts,\\n\\nAre you searching for a high-performance bike that won't break the bank? \r\n                Look no further than the Mountain-400-W Silver, 38!\\n\\nThis top-of-the-line bike was designed to deliver\r\n                a level of performance that's typically reserved for much pricier models. With responsive handling and\r\n                unbeatable maneuverability, you'll be able to tackle even the toughest trails with ease.\\n\\nBut that's\r\n                not all - the Mountain-400-W Silver, 38 also offers unparalleled peace of mind when you venture off-road.\r\n                Its sturdy construction and reliable components mean that you can trust your bike to hold up to even the\r\n                roughest terrain.\\n\\nSo why wait? Whether you're a seasoned pro or just starting out, the Mountain-400-W Silver,\r\n                38 is the perfect choice for any mountain biker looking to take their skills to the next level. Order yours\r\n                today and experience the ultimate in off-road performance!\"\r\n},\r\n\r\n<\/pre>\n
Pesky Passkeys<\/h2>\n
In all the previous examples, we have passed an api-key<\/strong> in with the header<\/strong> parameter. This is fine but will require a developer to have this key and potentially leak it in a code repository. What we can do to solve this issue is to use Managed Identities. To start, you just add our database to the Cognitive Services OpenAI User role using Access control (IAM)<\/strong> on our OpenAI Azure Portal page:<\/div>\n<\/div>\n
<\/div>\n
\"Image<\/a><\/div>\n
<\/div>\n
Once that is done, we can run the following T-SQL in the database we have been using (be sure to change the credential name to match your service):<\/div>\n
<\/div>\n
\n
\n
create database scoped credential [https:\/\/skynetbeta.openai.azure.com]\r\nwith identity = 'Managed Identity',\r\nsecret = '{\"resourceid\": \"https:\/\/cognitiveservices.azure.com\" }';<\/pre>\n<\/div>\n<\/div>\n
Now, with this created, we can remove the header parameter and replace it with the credential parameter<\/strong> in the stored procedure call:<\/div>\n
<\/div>\n
\n
declare @url nvarchar(4000) = N'https:\/\/skynetbeta.openai.azure.com\/openai\/deployments\/chattykathy\/chat\/completions?api-version=2023-07-01-preview';\r\ndeclare @payload nvarchar(max) = N'{\"messages\":[{\"role\":\"system\",\"content\":\"You are an AI assistant that helps people find information.\"},\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0{\"role\":\"user\",\"content\":\"Why is the sky blue?\"}]}'\r\ndeclare @ret int, @response nvarchar(max);\r\n\r\nexec @ret = sp_invoke_external_rest_endpoint\r\n  \u00a0 @url = @url,\r\n  \u00a0 @method = 'POST',\r\n  \u00a0 @payload = @payload,\r\n  \u00a0 @timeout = 230,\r\n  \u00a0 @credential = [https:\/\/skynetbeta.openai.azure.com],<\/strong>\r\n  \u00a0 @response = @response output;\r\nselect @ret as ReturnCode, @response as Response;<\/pre>\n<\/div>\n
A simple fix that alleviates a lot of potential issues down the road.<\/div>\n
<\/div>\n
Want to know more?<\/h2>\n
Looking for more samples and examples? Here is a list of places to start and as always, reach out to us with any questions or comments or if you would like to see this feature do something it does not today!<\/div>\n
<\/div>\n
Documentation<\/a><\/div>\n
Examples Repository<\/a><\/div>\n","protected":false},"excerpt":{"rendered":"
Everyone loves OpenAI these days as it can do some amazing things. Here, I asked it to create a picture of a cat in a data center: (Go ahead and click the image to see a bigger version of this nightmare fuel) Incredible!!!! But how can we use OpenAI in real world applications and scenarios […]<\/p>\n","protected":false},"author":95874,"featured_media":2377,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2364","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql"],"acf":[],"blog_post_summary":"
Everyone loves OpenAI these days as it can do some amazing things. Here, I asked it to create a picture of a cat in a data center: (Go ahead and click the image to see a bigger version of this nightmare fuel) Incredible!!!! But how can we use OpenAI in real world applications and scenarios […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/2364","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/95874"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=2364"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/2364\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/2377"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=2364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=2364"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=2364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}