最近SELECT AIを触ったので、備忘がてらアプトプットします。
Autonomous Databaseを日常的に触らないけど、取り敢えずSELECT AIを叩いてみたいそんな方向けです。

はじめに

SELECT AIとは、AIプロバイダーと連携し、LLMを利用して自然言語からSQLを作成し、実行する機能です。
現時点（2024/5）では、AIプロバイダとして、OpenAI、Cohere、Azure OpenAI、OCI GenAIの4つが選択できます。なお今回は、OCI GenAIを使っていきます。

SELECT AIの簡易説明は以下のスライドにもあります。

今回の流れ

ADB構築
ADB接続
サンプルスキーマの設定
SELECT AIの設定
SELECT AIの実行

１．ADB構築

[Autonomous Databaseの作成]

ワークロード・タイプの選択：データ・ウェアハウス（デフォルトの値）

adminユーザーのパスワードを設定します。
（これは後々使うので、なんのパスワードをを入れたか忘れないようにしましょう！！）

アクセスタイプ：すべての場所からセキュア・アクセス（デフォルトの値）
⇒別の値を選択した場合は、この後のADB接続の方法が変わってきます。

[Autonomous Databaseの作成]を押して、使用可能になるのを待つ。

２．ADB接続

ADBへの接続。
今回はCloudShellを使います。

Walletファイルのダウンロード

Walletファイルの配置

Walletファイルをnetwork/adminへ移動

ritsuko_to@cloudshell:~ (us-ashburn-1)$ pwd
/home/ritsuko_to
ritsuko_to@cloudshell:~ (us-ashburn-1)$ mkdir -p network/admin
ritsuko_to@cloudshell:~ (us-ashburn-1)$ mv Wallet_PQJC2BL1BKUIFQJ9.zip network/admin

zipの解凍

ritsuko_to@cloudshell:admin (us-ashburn-1)$ pwd
/home/ritsuko_to/network/admin
ritsuko_to@cloudshell:admin (us-ashburn-1)$ unzip Wallet_PQJC2BL1BKUIFQJ9.zip 
Archive:  Wallet_PQJC2BL1BKUIFQJ9.zip
replace ewallet.pem? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
  inflating: ewallet.pem             
  inflating: README                  
  inflating: cwallet.sso             
  inflating: tnsnames.ora            
  inflating: truststore.jks          
  inflating: ojdbc.properties        
  inflating: sqlnet.ora              
  inflating: ewallet.p12             
  inflating: keystore.jks            
ritsuko_to@cloudshell:admin (us-ashburn-1)$ ls
cwallet.sso  ewallet.p12  ewallet.pem  keystore.jks  ojdbc.properties  README  sqlnet.ora  tnsnames.ora  truststore.jks  Wallet_PDKT3FUO98WNL65H.zip  Wallet_PQJC2BL1BKUIFQJ9.zip

環境変数"ORACLE_HOME"の設定

ritsuko_to@cloudshell:admin (us-ashburn-1)$ export ORACLE_HOME=/home/ritsuko_to
ritsuko_to@cloudshell:admin (us-ashburn-1)$ echo $ORACLE_HOME
/home/ritsuko_to

sqlnet.oraの編集

DIRECTORYの値に$ORACLE_HOMEを追加します。

変更前

ritsuko_to@cloudshell:admin (us-ashburn-1)$ cat sqlnet.ora 
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes

変更後

ritsuko_to@cloudshell:admin (us-ashburn-1)$ cat sqlnet.ora 
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$ORACLE_HOME/network/admin")))
SSL_SERVER_DN_MATCH=yes

adminでsqlplus接続

コマンド：sqlplus <ユーザ名>/<パスワード>@<サービス名>
※パスワード：Autonomous Databaseを構築したときに指定したパスワードになります。
※サービス名：tnsnames.oraやOCIコンソール上に接続文字列から確認できます。

OCIコンソールの該当ADBから[データベース接続]をクリック

TNS名が確認できます。（今回はmediumで接続）

ritsuko_to@cloudshell:~ (us-ashburn-1)$ sqlplus admin/WelCome123#123#@pqjc2bl1bkuifqj9_medium

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 12 14:14:01 2024
Version 21.14.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sun May 12 2024 14:13:52 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0

３．サンプルスキーマの作成

Autonomous Databasenには、最初から入っているサンプルスキーマがあるためそちらを使ってもいいですが、
最近サンプルスキーマを作成する記事も作成したので、
折角なら復習がてら自身でサンプルスキーマを作成して、活用したいと思います。

以下の記事を参考にサンプルスキーマを作成してください。

unzip後の今回のログを一応置いておきます


ritsuko_to@cloudshell:~ (us-ashburn-1)$ ls
network              db-sample-schemas-23.2                    v23.2.zip

ritsuko_to@cloudshell:~ (us-ashburn-1)$ sqlplus admin/WelCome123#123#@pqjc2bl1bkuifqj9_medium

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 12 23:41:06 2024
Version 21.14.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sun May 12 2024 23:39:02 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0

SQL> @./db-sample-schemas-23.2/human_resources/hr_install.sql

Thank you for installing the Oracle Human Resources Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'hr_install.log' log file.

Enter a password for the user HR: 


Enter a tablespace for HR [DATA]: 
Do you want to overwrite the schema, if it already exists? [YES|no]: YES
******  Creating REGIONS table ....

Table created.


Index created.


Table altered.

******  Creating COUNTRIES table ....

Table created.


Table altered.

******  Creating LOCATIONS table ....

Table created.


Index created.


Table altered.


Sequence created.

******  Creating DEPARTMENTS table ....

Table created.


Index created.


Table altered.


Sequence created.

******  Creating JOBS table ....

Table created.


Index created.


Table altered.

******  Creating EMPLOYEES table ....

Table created.


Index created.


Table altered.


Table altered.


Sequence created.

******  Creating JOB_HISTORY table ....

Table created.


Index created.


Table altered.

******  Creating EMP_DETAILS_VIEW view ...

View created.

******  Creating indexes ...

Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.


Index created.

******  Adding table column comments ...

Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Comment created.


Session altered.

****** Populating REGIONS table ....

PL/SQL procedure successfully completed.

****** Populating COUNTRIES table ....

PL/SQL procedure successfully completed.

****** Populating LOCATIONS table ....

PL/SQL procedure successfully completed.

****** Populating DEPARTMENTS table ....

Table altered.


PL/SQL procedure successfully completed.

****** Populating JOBS table ....

PL/SQL procedure successfully completed.

****** Populating EMPLOYEES table ....

PL/SQL procedure successfully completed.

****** Populating JOB_HISTORY table ....

PL/SQL procedure successfully completed.


Commit complete.


Table altered.


Procedure created.


Trigger created.


Trigger altered.


Procedure created.


Trigger created.


Commit complete.


Installation
-------------
Verification:

Table         provided     actual
----------- ---------- ----------
regions              5          5
countries           25         25
departments         27         27
locations           23         23
employees          107        107
jobs                19         19
job_history         10         10

Thank you!
--------------------------------------------------------
The installation of the sample schema is now finished.
Please check the installation verification output above.

You will now be disconnected from the database.

Thank you for using Oracle Database!

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0

サンプルスキーマを作成した際に、作成したHRユーザーで接続できることを確認

ritsuko_to@cloudshell:~ (us-ashburn-1)$ sqlplus hr/WelCome123#123#@pqjc2bl1bkuifqj9_medium

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 12 23:43:11 2024
Version 21.14.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0

ちなみにサンプルスキーマが持っている表は、以下の7つです。
この7つの表のselectを今回は自然言語で試そうというものです。

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY

7 rows selected.

４．SELECT AIの設定

adminで接続、HRユーザーに権限を付与する


ritsuko_to@cloudshell:~ (us-ashburn-1)$ sqlplus admin/WelCome123#123#@pqjc2bl1bkuifqj9_medium

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 12 23:44:52 2024
Version 21.14.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sun May 12 2024 23:41:10 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0

HRユーザーに権限を付与

SQL> grant execute on DBMS_CLOUD to hr;

Grant succeeded.

SQL> grant execute on DBMS_CLOUD_AI to hr;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0

HRユーザーで接続

ritsuko_to@cloudshell:~ (us-ashburn-1)$ sqlplus hr/WelCome123#123#@pqjc2bl1bkuifqj9_medium

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 12 23:45:06 2024
Version 21.14.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sun May 12 2024 23:43:14 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0

クレデンシャルを作成する

（OCIDや暗号鍵は省略。）

SQL> BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_GENAI_CRED3',
user_ocid       => 'ユーザーのOCID',
tenancy_ocid    => 'テナントのOCID',
private_key     => '-----BEGIN PRIVATE KEY-----
MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQDsJaHS+9ZL41tb
<省略>
5FI5rYA+wocUJ1+veQKFif02
     -----END PRIVATE KEY-----',
fingerprint     => 'xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:c4:13:b3'
);
END;
/  

PL/SQL procedure successfully completed.

プロファイルの作成

CREATE_PROFILEプロシージャを使います。
object_listで今回使いたい表の所有者と表名を羅列します。
今回はHRユーザが持っている7つ表全てをリストしておきます。
attributesの詳細説明はこちらをご確認下さい。

SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI3',
attributes => '{"provider": "oci",
"credential_name": "OCI_GENAI_CRED3",
"object_list": [
{"owner": "HR", "name": "REGIONS"},
{"owner": "HR", "name": "COUNTRIES"},
{"owner": "HR", "name": "LOCATIONS"},
{"owner": "HR", "name": "DEPARTMENTS"},
{"owner": "HR", "name": "JOBS"},
{"owner": "HR", "name": "EMPLOYEES"},
 {"owner": "HR", "name": "JOB_HISTORY"}]
}',
status => 'ENABLED',
description => 'AI profile to use OCI Generative AI for SQL translation'
);
END;
/ 

PL/SQL procedure successfully completed.

プロファイルのセット

先ほど作成したプロファイルをセットする。
セッションが切れた場合、プロファイルのsetを再実行が必要です。


SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENAI3');

PL/SQL procedure successfully completed.

５．SELECTAIの実行

SELECT AIで実行結果を見て、
SELECT AI SHOWSQLで生成されたSQLを見てみます。

・部門の数を聞いてみる

SQL> SELECT AI How many DEPARTMENTS?;

NUM_DEPARTMENTS
---------------
             27

SQL>  SELECT AI SHOWSQL How many DEPARTMENTS?;

RESPONSE
---------------------------------------------------
SELECT COUNT(*) AS num_departments
FROM departments

・従業員の一番多い部門を聞いてみる

SQL> SELECT AI which DEPARTMENT have the most number of employees?; 

DEPARTMENT_NAME                EMPLOYEE_COUNT
------------------------------ --------------
Shipping                                   45

SQL> SELECT AI SHOWSQL which DEPARTMENT have the most number of employees?; 

RESPONSE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY employee_count DESC
FETCH FIRST ROW ONLY


SQL>

・従業員の多い部門、TOP5を聞いてみる


SQL> SELECT AI what are the top 5 departments with the most number of employees?;

DEPARTMENT_NAME                EMPLOYEE_COUNT
------------------------------ --------------
Shipping                                   45
Sales                                      34
Purchasing                                  6
Finance                                     6
IT                                          5

SQL> SELECT AI SHOWSQL what are the top 5 departments with the most number of employees?;

RESPONSE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY employee_count DESC
FETCH FIRST 5 ROWS ONLY


SQL>

・部門ごとの従業員の数を出す

SQL> SELECT AI How many employees per department?;

DEPARTMENT_NAME                NUM_EMPLOYEES
------------------------------ -------------
Sales                                     34
Marketing                                  2
Administration                             1
Purchasing                                 6
Shipping                                  45
IT                                         5
Executive                                  3
Finance                                    6
Public Relations                           1
Human Resources                            1
Accounting                                 2

11 rows selected.

SQL> SELECT AI SHOWSQL How many employees per department?;

RESPONSE
---------------------------------------------------------------------------------------------------------------------------------------------------
SELECT department_name, COUNT(*) AS num_employees
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY department_name


SQL>