Oracle 自治数据库 Select AI 初体验

这几天有点时间，准备尝试下oracle Select AI，虽然此功能2023年就已经发布了。

Oracle自治数据库已经集成好了Select AI，本文也是讲的这个。

配置 Select AI

需要以下步骤：

1. 创建ADB
2. 申请Cohere/OpenAI免费账号
3. 设置ADB
4. 测试Select AI

第1步在OCI上创建一个自治数据库即可，可以是ATP或ADW，不再赘述。

第2步在Cohere或OpenAI网站上申请个免费账号，我都做了，也很简单。

以下为Cohere的API Key，后续会用到：

以下为OpenAI的API Key：

本文主要讲第3和第4步 ，整个过程参考官方文档：Use Select AI to Generate SQL from Natural Language Prompts

以下为详细过程，我直接使用的管理员账户ADMIN，你也可以创建用户xxx：

-- 赋权，对于ADMIN用户不用执行
grant execute on DBMS_CLOUD_AI to xxx;
-- 设置ACL，对于ADMIN也需要执行
-- 以下指定的是Cohere大模型，如果是openAI，则host对应改为api.openai.com
-- 用户指定的是ADMIN，如果是用户xxx，则principal_name对应改为xxx
BEGIN  
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
     host => 'api.cohere.ai',
     ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                         principal_name => 'ADMIN',
                         principal_type => xs_acl.ptype_db)
);
END;
/  
-- 创建credential，命名为SELECTAI_CRED，用户名为申请邮箱，密码为Cohere或OpenAI的API key
-- EXEC DBMS_CLOUD.DROP_CREDENTIAL('OPENAI_CRED');
EXEC DBMS_CLOUD.CREATE_CREDENTIAL('SELECTAI_CRED', '申请账户的邮箱', '大模型提供的API Key');
-- 创建AI profile，provider设定为cohere或openai
-- EXEC DBMS_CLOUD_AI.drop_profile(profile_name => 'SELECTAI');
BEGIN
  DBMS_CLOUD_AI.create_profile(
      'SELECTAI',
      '{"provider": "cohere",
        "credential_name": "SELECTAI_CRED",
        "object_list": [{"owner": "SH", "name": "customers"},
                        {"owner": "SH", "name": "sales"},
                        {"owner": "SH", "name": "products"},
                        {"owner": "SH", "name": "countries"}]
       }');
END;
/
-- 设定AI Profile
-- 这是一个session设置，因此每次连接数据库时都必须执行
BEGIN
  DBMS_CLOUD_AI.SET_PROFILE(
     profile_name => 'SELECTAI'
  );
END;
/

测试 Select AI

然后就是测试了，可以用SQL Plus或SQL Developer：

测试OpenAI时，报错，因为超过了速率限制：

ORA-20429: Request failed with status HTTP 429 - bearer://api.openai.com/v1/chat/completions
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD$PDBCS_240425_1", line 2060
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD$PDBCS_240425_1", line 12958
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 3096
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 3650
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 5024
ORA-06512: at line 1

没有关系，前面的设置都是正确的。错误HTTP 429表示API调用超过了速率限制。这是由于OpenAI用了免费账户，免费账户的5美元上限或者已达到，或者免费期限已过。

而用Cohere就没有问题了。

SQL> set echo on
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
         55500
SQL> select ai showsql how many customers exist;
RESPONSE                                           
---------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS
SQL> select ai narrate how many customers exist;
RESPONSE                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To determine how many customers exist, we will use the `customers` table provided above which has the column `cust_id` and a ROWNUMBER `ROW_COUNT`. 
Here is the SQL query to be executed:
```sql
SELECT COUNT(*) AS CUSTOMERS_COUNT
FROM SH.CUSTOMERS;
    ```
This query uses the `COUNT(*)` function to count all the rows in the `CUSTOMERS` table. When we execute this query in the Oracle Database, we will get the number of customers in the "SH" schema.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A possible Oracle SQL query to answer the question is:
```sql
SELECT COUNT(*) customer_count
FROM sh.customers
WHERE cust_city = 'San Francisco'
AND cust_martial_status = 'Married';
    ```
Explanation:
- The query uses the "SH"."CUSTOMERS" table to retrieve data about customers.
- It counts the number of married customers residing in San Francisco.
RESPONSE           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- The WHERE clause filters the rows based on two conditions:
   - ``cust_city = 'San Francisco' '' ensures that only customers with ``San Francisco`` as their city are considered.
   - ``cust_martial_status = 'Married'`` ensures that only married customers are counted.
- The result is a single number that answers the question.

中文不支持：

select ai 存在多少客户;
RESPONSE                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sorry, unfortunately a valid SELECT statement could not be generated for your natural language prompt. Here is some more information to help you further: 
I'm sorry, I cannot understand the question because currently Chinese language is not supported. Could you please make the question in English instead? 
If there's anything else I can assist you with, feel free to ask!

这是由于默认的大模型并不支持中文，DBMS_CLOUD_AI.create_profile创建profile时，需要指定新的Command R+模型：

BEGIN
  DBMS_CLOUD_AI.create_profile(
      'SELECTAI',
      '{"provider": "cohere",
        "credential_name": "SELECTAI_CRED",
        "model" : "command-r-plus",
        "object_list": [{"owner": "SH", "name": "customers"},
                        {"owner": "SH", "name": "sales"},
                        {"owner": "SH", "name": "products"},
                        {"owner": "SH", "name": "countries"}]
       }');
END;
/

现在中文就正常了：

SQL> set echo on
SQL> select ai 存在多少客户;
CUSTOMER_COUNT
--------------
         55500
SQL> select ai narrate 存在多少客户;
"要确定客户表中有多少客户，您可以使用以下SQL查询：
```sql
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS;
    ```
此查询将返回客户表中的客户总数。"
SQL> select ai explainsql 旧金山有多少顾客已婚;
RESPONSE                                    
------------------------------------------------------------------------------------------------------------------------------------------
SELECT count(*) AS married_customers_count
FROM sh.customers c
WHERE c.cust_marital_status = 'Married'
  AND c.cust_city = 'San Francisco'

参考

• Introducing Select AI - Natural Language to SQL Generation on Autonomous Database
• Autonomous Database Select AI: Accelerate innovation with enterprise data, OCI Generative AI, and enhanced security
• Autonomous Database speaks “human”
• Conversations are the next generation in natural language queries
• Natural Language Queries - Oracle Autonomous Database Now Speaks “Human” - Select AI