这几天有点时间，准备尝试下Oracle Select AI，虽然此功能2023年就已经发布了。
Oracle自治数据库已经集成好了Select AI，本文也是讲的这个。
配置 Select AI
需要以下步骤：
- 创建ADB
- 申请Cohere/OpenAI免费账号
- 设置ADB
- 测试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');
BEGINDBMS_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设置，因此每次连接数据库时都必须执行
BEGINDBMS_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
--------------55500SQL> select ai showsql how many customers exist;RESPONSE
---------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERSSQL> 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+模型：
BEGINDBMS_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
--------------55500SQL> 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
