最近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>