Ora-lytics

SelectAI – Doing something useful

Posted on March 22, 2024 Updated on April 19, 2024

In a previous post, I introduced Select AI and gave examples of how to do some simple things. These included asking it using some natural language questions, to query some data in the Database. That post used both Cohere and OpenAI to process the requests. There were mixed results and some gave a different, somewhat disappointing, outcome. But with using OpenAI the overall outcome was a bit more positive. To build upon the previous post, this post will explore some of the additional features of Select AI, which can give more options for incorporating Select AI into your applications/solutions.

Select AI has five parameters, as shown in the table below. In the previous post, the examples focused on using the first parameter. Although those examples didn’t include the parameter name ‘runsql‘. It is the default parameter and can be excluded from the Select AI statement. Although there were mixed results from using this default parameter ‘runsql’, it is the other parameters that make things a little bit more interesting and gives you opportunities to include these in your applications. In particular, the ‘narrate‘ and ‘explainsql‘ parameters and to a lesser extent the ‘chat‘ parameter. Although for the ‘chat’ parameter there are perhaps slightly easier and more efficient ways of doing this.

Let’s start by looking at the ‘chat‘ parameter. This allows you to ‘chat’ LLM just like you would with ChatGPT and other similar. A useful parameter to set in the CREATE_PROFILE is to set the conversation to TRUE, as that can give more useful results as the conversation develops.

BEGIN
  DBMS_CLOUD_AI.drop_profile(profile_name => 'OPEN_AI');
  DBMS_CLOUD_AI.create_profile(
      profile_name => 'OPEN_AI',
      attributes => '{"provider": "openai",
                      "credential_name": "OPENAI_CRED",
                      "object_list": [{"owner": "SH", "name": "customers"},  
                                      {"owner": "SH", "name": "sales"},
                                      {"owner": "SH", "name": "products"},
                                      {"owner": "SH", "name": "countries"},
                                      {"owner": "SH", "name": "channels"},
                                      {"owner": "SH", "name": "promotions"},
                                      {"owner": "SH", "name": "times"}],
        "conversation": "true"
       }');
END;

There are a few statements I’ve used.

select AI chat who is the president of ireland;
select AI chat what role does NAMA have in ireland;
select AI chat what are the annual revenues of Oracle;
select AI chat who is the largest cloud computing provider;
select AI chat can you rank the cloud providers by income over the last 5 years;
select AI chat what are the benefits of using Oracle Cloud;

As you’d expect the results can be ‘kind of correct’, with varying levels of information given. I’ve tried these using Cohere and OpenAI, and their responses illustrate the need for careful testing and evaluation of the various LLMs to see which one suits your needs.

In my previous post, I gave some examples of using Select AI to query data in the Database based on a natural language request. Select AI takes that request and sends it, along with details of the objects listed in the create_profile, to the LLM. The LLM then sends back the SQL statement, which is then executed in the Database and the results are displayed. But what if you want to see the SQL generated by the LLM. To see the SQL you can use the ‘showsql‘ parameter. Here are a couple of examples:

SQL> select AI showsql how many customers in San Francisco are married;

RESPONSE                                                                                                                                    
_____________________________________________________________________________SELECT COUNT(*) AS total_married_customers
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'    

SQL> select AI what customer is the largest by sales; 

   CUST_ID CUST_FIRST_NAME    CUST_LAST_NAME       TOTAL_SALES 
__________ __________________ _________________ ______________ 
     11407 Dora               Rice                   103412.66 


SQL> select AI showsql what customer is the largest by sales; 

RESPONSE                                                                                                                                                                                                                                                          
_____________________________________________________________________________SELECT C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME, SUM(S.AMOUNT_SOLD) AS TOTAL_SALES
FROM SH.CUSTOMERS C
JOIN SH.SALES S ON C.CUST_ID = S.CUST_ID
GROUP BY C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME
ORDER BY TOTAL_SALES DESC
FETCH FIRST 1 ROW ONLY    

The examples above that illustrate the ‘showsql‘ is kind of interesting. Careful consideration of how and where to use this is needed.

Where things get a little bit more interesting with the ‘narrate‘ parameter, which attempts to narrate or explain the output from the query. There are many use cases where this can be used to supplement existing dashboards, etc. The following are examples of using ‘narrate‘ for the same two queries used above.

SQL> select AI narrate how many customers in San Francisco are married;

RESPONSE                                                         
________________________________________________________________ 
The total number of married customers in San Francisco is 18.    


SQL> select AI narrate what customer is the largest by sales; 

RESPONSE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
_____________________________________________________________________________To find the customer with the largest sales, you can use the following SQL query:

```sql
SELECT c.CUST_FIRST_NAME || ' ' || c.CUST_LAST_NAME AS CUSTOMER_NAME, SUM(s.AMOUNT_SOLD) AS TOTAL_SALES
FROM "SH"."CUSTOMERS" c
JOIN "SH"."SALES" s ON c.CUST_ID = s.CUST_ID
GROUP BY c.CUST_FIRST_NAME, c.CUST_LAST_NAME
ORDER BY TOTAL_SALES DESC
FETCH FIRST 1 ROW ONLY;
```

This query joins the "CUSTOMERS" and "SALES" tables on the customer ID and calculates the total sales for each customer. It then sorts the results in descending order of total sales and fetches only the first row, which represents the customer with the largest sales.

The result will be in the following format:

| CUSTOMER_NAME | TOTAL_SALES |
|---------------|-------------|
| Tess Drumm    | 161882.79   |

In this example, the customer with the largest sales is "Tess Drumm" with a total sales amount of 161,882.79.    

Looking at the outputs from using the ‘narrate‘ parameter, we can see the level of detail given and see the potential of including this kind of information in our applications, dashboards, etc. But to use this output some additional filtering and subsetting will need to be performed.

The final parameter is called ‘explainsql‘. While it may not be as useful as the ‘narrate‘ parameter, it does have some potential uses. The ‘explainsql‘ attempts to explain in natural language what a SQL query is attempting to do, based on a natural language statement. Again using the same two examples, we get the following.

SQL> select AI explainsql what customer is the largest by sales; 

RESPONSE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
_____________________________________________________________________________SELECT C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME, SUM(S.AMOUNT_SOLD) AS TOTAL_SALES
FROM SH.CUSTOMERS C
JOIN SH.SALES S ON C.CUST_ID = S.CUST_ID
GROUP BY C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME
ORDER BY TOTAL_SALES DESC
FETCH FIRST 1 ROW ONLY;

Explanation:
- The query selects the customer ID, first name, last name, and the sum of the amount sold from the CUSTOMERS and SALES tables.
- The CUSTOMERS and SALES tables are joined on the CUST_ID column.
- The results are grouped by customer ID, first name, and last name.
- The results are then ordered in descending order by the total sales.
- The FETCH FIRST 1 ROW ONLY clause is used to retrieve only the top row with the highest total sales.    

SQL> select AI explainsql how many customers in San Francisco are married;

RESPONSE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
_____________________________________________________________________________
SELECT COUNT(*) AS total_married_customers
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married';

This query selects the count of customers who are married and live in San Francisco. The table alias "c" is used for the CUSTOMERS table. The condition "c.CUST_CITY = 'San Francisco'" filters the customers who live in San Francisco, and the condition "c.CUST_MARITAL_STATUS = 'Married'" filters the customers who are married. The result is the total number of married customers in San Francisco. 

Check out the other posts about Select AI.

• SelectAI – the beginning of a journey
• SelectAI – Doing something useful
• SelectAI – Can metadata help
• SelectAI – the APEX version