Announcing Select AI with Azure OpenAI Service on Oracle Autonomous Database

November 14, 2023 | 6 minute read
Mark Hornick
Senior Director, Machine Learning and AI Product Management
This is a syndicated post, view the original post
Text Size 100%:

With the constantly evolving AI landscape, how are you keeping pace with advances in AI?

Addressing that problem was one of the key requirements when we designed Select AI for Autonomous Database. As introduced during Oracle CloudWorld ’23, Select AI makes it easy to query your data using natural language. Check out the blog Autonomous Database speaks "human" to see how it's done. 

Select AI with Azure OpenAI Service

We're excited to announce that you can now use Select AI with Azure OpenAI Service. Not only do you get access to the latest OpenAI models hosted on Azure, but this new integration is a great step toward improving data governance. Through our Microsoft partnership with Oracle Interconnect for Azure, customers now have a first-of-its-kind, interconnected cloud experience – enabling low-latency, high-throughput, private connection between two leading cloud providers, allowing you to use the best of Oracle Cloud Infrastructure (OCI) and Microsoft Azure with seamless interoperability. With Azure Open AI Service, you can also easily deploy your own models

Agility and the magic of Select AI profiles

Select AI makes it easy to switch between LLMs supporting natural language-to-SQL generation. This not only gives you a choice among AI service providers, but it allows quickly comparing LLM results – how good the generated queries and results are – as well as performance. If you're using GPT-3.5 today, compare results with GPT-4 simply by changing profiles but using the same natural language prompt in your Select AI query.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
EXEC DBMS_CLOUD_AI.set_profile('AZUREAI-gpt35');
select ai showsql how many movies do we have;

EXEC DBMS_CLOUD_AI.set_profile('AZUREAI-gpt4');
select ai showsql how many movies do we have;

Or, you can use the GENERATE function as follows:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SELECT DBMS_CLOUD_AI.GENERATE('how many customers do we have','AZUREAI-gpt35','showsql') FROM dual;
SELECT DBMS_CLOUD_AI.GENERATE('how many customers do we have','AZUREAI-gpt4', 'showsql') FROM dual;

If you're satisfied with the new model's results, update your application's Select AI profile and your application just got smarter!

Getting started

To use Azure OpenAI Service, you'll need your API key (a.k.a. token) found through the Azure portal. On the Resource page shown below, the resource name is at the top. Click "Keys and Endpoint" and copy either key as your token. Then, click "Go to Azure OpenAI Studio" to access your deployment name...

...as shown here.

You’ll use the resource name and deployment name of your deployed model to create your Azure OpenAI Service Select AI profile.

Example using Select AI with Azure OpenAI Service

First, let’s create a credential object for AI integration and grant network ACL access for the AI endpoint. Specify the Azure resource name identified above. 

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
EXEC DBMS_CLOUD.create_credential('AZURE_CRED', 'AZUREAI', '<your api token>');
BEGIN 
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => '<azure_resource_name>.openai.azure.com',
         ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'ADMIN',
                             principal_type => xs_acl.ptype_db)
   );
END;

Now, create your AI profile. Specify "azure" as the provider along with the resource and deployment names identified above. Note that we use the credential created above and specify the list of schema(s) and optionally the tables or views we want Select AI to consider when generating SQL queries. 

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
BEGIN                                                                         
  DBMS_CLOUD_AI.create_profile(                                               
      'AZUREAI-gpt4',                                                              
      '{"provider": "azure", 
        "azure_resource_name": "<azure_resource_name>",
        "azure_deployment_name": "<azure_deployment_name>"                                                                     
        "credential_name": "AZURE_CRED",                                      
        "object_list": [{"owner": "MOVIESTREAM", "name": "movies"},                 
                        {"owner": "MOVIESTREAM", "name": "genres"},                 
                        {"owner": "MOVIESTREAM", "name": "movie_streams"}]
               
       }');                                                                   
END;

Now, set your AI profile in your database session. 

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
EXEC DBMS_CLOUD_AI.set_profile('AZUREAI');

And we’re ready to use Select AI!

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> select ai narrate how many movies do we have;  
RESPONSE
----------------------------------------------------
The total number of movies we have is 3800.

SQL> select ai showsql how many movies do we have;
RESPONSE
----------------------------------------------------
SELECT COUNT(*) AS total_movies
FROM MOVIESTREAM.MOVIES

Resources

Mark Hornick

Senior Director, Machine Learning and AI Product Management

Mark Hornick is Senior Director, Machine Learning and AI Product Management. Mark has more than 20 years of experience integrating and leveraging machine learning with Oracle software as well as working with internal and external customers to apply Oracle’s machine learning technologies. Mark is Oracle’s representative to the R Consortium and is an Oracle Adviser and founding member of the Analytics and Data Oracle User Community. He has been issued seven US patents. Mark holds a bachelor’s degree from Rutgers University and a master’s degree from Brown University, both in computer science. Follow him on Twitter and connect on LinkedIn.

Show more
Everything you need to know about data warehousing with the world's leading cloud solution provider