2024年8月追記
日本語で質問してSelectできるようになりました
OCI Autonomous Database Select AI検証(日本語で質問)
https://qiita.com/tktk2712/items/f50c0b7903acf1d05db1
========================================================
概要
Oracle Cloud Infrastructure(OCI)のデータベースサービスの一つにAutonomous Database（ADB)があります。このADBにSelect AI機能が付加され、いくつかの検証記事も見かけるようになりました。
このSelect AI機能を使用することによってSQLに詳しくなくても生成AIがいい感じでSQLを生成してくれるので
- 非IT部門でも必要なデータと取り出せる(IT部門にお願いしなくていい!?)
- SQL作成支援してくれる(ひな形から必要なSQLを作成できる)
のようなメリットが感じられるかと思います。
ここでは、ADB作成からサンプルデータを使った検証手順やさらに日本語データでの検証を行います。
参考：
OCI Oracle Database Services新機能アップデート(FY24Q3)
検証1 サンプルスキーマを使用したSelect AI検証
Chicagoリージョン追加
2024年3月現在Generative AIサービスはChicagoリージョンのみ提供されていますので、Chicagoリージョンをサブスクライブします
OCIナビゲーションメニューから現在表示されているリージョンをクリックし、「リージョンの管理」を選択
Chicagoのサブスクライブを押下
もしサブスクライブボタンがグレイアウトされている場合は、サービスリクエスト(SR)を起票して制限緩和してもらう必要があります。
SRの例
- サービス・カテゴリー：Regions
- リソース：Subscribed region count
- テナンシ制限： 希望数3
- リクエストの理由：Chicago regionのGenerative AI利用のため
ADB作成
OCIナビゲーションメニューからOracle Database>>Autonomous Databaseを選択
赤四角部分について設定しAutonomous Databaseの作成を押下します
設定その1
設定その2
設定その3
設定その4
しばらく経つと使用可能になります。
ウォレットをダウンロードするためにデータベース接続を押下します。
パスワードを入力してダウンロードを押下すると操作PCにウォレットがダウンロードされます。
Cloud Shellを使用してADBを操作します。
OCIナビゲーションメニューからリージョン横のアイコンをクリックしてCloud Shellを選択します。
CloudShellが起動するので左のアクションからアーキテクチャを選択
X86_64が選択されていることを確認。ARMの場合はX86_64に変更
右側の歯車マークをクリックしてアップロードを選択
先ほどダウンロードしたウォレットをドラッグドロップしてアップロード
lsコマンドでアップロードされたことを確認
cloudshell: $ ls -lart
-rw-r--r--. 1 oci 21988 Mar 11 11:50 Wallet_genaidemo.zip
格納用のディレクトリを作成し、ウォレットファイルを$HOME/network/admin 配下に移動
格納用のディレクトリを作成
cloudshell: $ mkdir -p ~/network/admin
ウォレットファイルを移動
cloudshell: $ mv Wallet_genaidemo.zip ~/network/admin
作成したディレクトリに移動し、圧縮されたウォレットファイルを展開
作成したディレクトリに移動
cloudshell: $ cd ~/network/admin
cloudshell: $ unzip Wallet_genaidemo.zip
TNS_ADMIN 環境変数にウォレットの保存先を設定
cloudshell: $ export TNS_ADMIN=~/network/admin
sqlnet.ora の下記の行をviで編集し、ウォレットの保存先を置き換え
変更前
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
変更後
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))
環境変数”ORACLE_HOME”を設定
cloudshell: $ export ORACLE_HOME=~
接続用のサービス情報を確認
cloudshell: $ cat tnsnames.ora
genaidemo_high = (description= (retry_count=20)(retry_delay=3)..
genaidemo_low = (description= (retry_count=20)(retry_delay=3)..
genaidemo_medium = (description= (retry_count=20)(retry_delay=3)..
:
SQL*Plusを起動
cloudshell: $ sqlplus admin/【パスワード】@genaidemo_low
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Mar 11 12:01:57 2024
Version 21.13.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
SQL>
この手順は以下を参考にしています
104: クレデンシャル・ウォレットを利用して接続してみよう
サンプルスキーマ作成
以下のサイトのサンプルを利用します
https://github.com/oracle-samples/db-sample-schemas/releases
以下の手順でコマンド実行
cloudshell: $ cd ~
cloudshell: $ mkdir demo
cloudshell: $ cd demo
cloudshell: $ wget https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.2.zip
cloudshell: $ unzip v23.2.zip
cloudshell: $ cd db-sample-schemas-23.2
cloudshell: $ cd human_resources
cloudshell: $ sqlplus admin/【パスワード】@genaidemo_low
SQL> @hr_install
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
SelectAIを行うための設定
パッケージの実行権限を付与
Adminで実行
cloudshell: $ sqlplus admin/【パスワード】@genaidemo_low
SQL> grant execute on DBMS_CLOUD to hr;
SQL> grant execute on DBMS_CLOUD_AI to hr;
SQL> exit
先ほど作成したHRユーザで接続、テーブルなど確認
cloudshell: $ sqlplus hr/【パスワード】@genaidemo_low
SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
:
EMP_DETAILS_VIEW
8 rows selected.
SQL> exit
SelectAIを行うために資格情報ファイルに設定する情報をOCIコンソールから準備します
- User OCID
OCIナビゲーションメニューから右のプロファイルアイコンをクリックしてユーザー設定を選択
ユーザー情報のOCIDをコピー
-
テナンシーOCID
OCIナビゲーションメニューから右のプロファイルアイコンをクリックしてテナンシーを選択
テナンシ情報のOCIDをコピー -
APIキーおよびフィンガープリント
OCIナビゲーションメニューから右のプロファイルアイコンをクリックしてユーザー設定を選択
左下のリソースからAPIキーを選択してAPIキーの追加を押下
秘密キー、公開キーをダウンロード,フィンガープリントをコピー
取得した情報をもとにCloudShellでクレデンシャル作成
cloudshell: $ vi genai_cred.sql
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..',
tenancy_ocid => 'ocid1.tenancy.oc1..',
private_key => '-----BEGIN PRIVATE KEY-----
MIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwaaSiAgEAAoIBAQDhxvD/oKFor6h5
:
SAA6TtWn9AD1zRu5raux/w==
-----END PRIVATE KEY-----',
fingerprint => '49:82:………3s:fb:8e'
);
END;
/
cloudshell: $ sqlplus hr/【パスワード】@genaidemo_low
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Mar 11 12:36:57 2024
Version 21.13.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Mon Mar 11 2024 12:17:09 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0
SQL> @genai_cred
PL/SQL procedure successfully completed.
SQL> exit
AIプロファイルの作成
DBMS_CLOUD_AI.CREATE_PROFILEプロシージャを使用して、AIプロファイルを作成
cloudshell: $ vi aiprofile.sql
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'GENAI',
'{"provider": "oci",
"credential_name": "GENAI_CRED",
"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"}
]
}'
);
END;
/
cloudshell: $ sqlplus hr/【パスワード】@genaidemo_low
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Mar 11 12:39:59 2024
Version 21.13.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Mon Mar 11 2024 12:37:01 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0
SQL> @aiprofile
PL/SQL procedure successfully completed.
SQL> exit
SelectAI実施
環境変数を再設定（ワークシートSelectAI設定から続けて実行する場合は不要)
cloudshell: $ export TNS_ADMIN=~/network/admin
cloudshell: $ export ORACLE_HOME=~
SelectAI実行
cloudshell: $ sqlplus hr/【パスワード】@genaidemo_low
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
問合せ実行
SQL> SELECT AI which country have the most number of employees?;
COUNTRY_NAME
------------------------------------------------------------
COUNT(EMPLOYEES.EMPLOYEE_ID)
----------------------------
United States of America
68
問合せのSQLを表示
SQL> SELECT AI SHOWSQL which country have the most number of employees?;
RESPONSE
--------------------------------------------------------------------------------
SELECT Countries.country_name, COUNT(Employees.employee_id)
FROM Countries
JOIN LOCATIONS ON Countries.country_id = LOCATIONS.country_id
JOIN Departments ON LOCATIONS.location_id = Departments.location_id
JOIN Employees ON Departments.department_id = Employees.department_id
GROUP BY Countries.country_name
ORDER BY COUNT(Employees.employee_id) DESC
FETCH FIRST ROW ONLY
SQL>
サンプルスキーマを使用した例
いくつか実行してみた結果です。
サンプルスキーマを使用した例
■一番従業員が多い国は？
SQL> SELECT AI which country have the most number of employees?;
COUNTRY_NAME
------------------------------------------------------------
COUNT(EMPLOYEES.EMPLOYEE_ID)
----------------------------
United States of America
68
SQL> SELECT AI SHOWSQL which country have the most number of employees?;
RESPONSE
--------------------------------------------------------------------------------
SELECT Countries.country_name, COUNT(Employees.employee_id)
FROM Countries
JOIN LOCATIONS ON Countries.country_id = LOCATIONS.country_id
JOIN Departments ON LOCATIONS.location_id = Departments.location_id
JOIN Employees ON Departments.department_id = Employees.department_id
GROUP BY Countries.country_name
ORDER BY COUNT(Employees.employee_id) DESC
FETCH FIRST ROW ONLY
■従業員が多い国5か国
SQL> SELECT AI what are the top 5 countries with the most number of employees?;
COUNTRY_NAME NUM_EMPLOYEES
------------------------------------------------------------ -------------
United States of America 68
United Kingdom of Great Britain and Northern Ireland 35
Canada 2
Germany 1
SQL> SELECT AI SHOWSQL what are the top 5 countries with the most number of employees?;
RESPONSE
--------------------------------------------------------------------------------
SELECT Countries.country_name, COUNT(Employees.employee_id) AS num_employees
FROM Countries
JOIN Regions ON Countries.region_id = Regions.region_id
JOIN Locations ON Countries.country_id = Locations.country_id
JOIN Departments ON Locations.location_id = Departments.location_id
JOIN Employees ON Departments.department_id = Employees.department_id
GROUP BY Countries.country_name
ORDER BY num_employees DESC
FETCH FIRST 5 ROWS ONLY
■部門ごとのsalary集計
SQL> SELECT AI aggregate salaries for each department in descending order;
DEPARTMENT_NAME TOTAL_SALARY
------------------------------ ------------
Sales 304500
Shipping 156400
Executive 58000
Finance 51608
IT 28800
Purchasing 24900
Accounting 20308
Marketing 19000
Public Relations 10000
Human Resources 6500
Administration 4400
11 rows selected.
SQL> SELECT AI SHOWSQL aggregate salaries for each department in descending order;
RESPONSE
--------------------------------------------------------------------------------
SELECT department_name, SUM(salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY department_name
ORDER BY total_salary DESC
検証2 日本語データを使用したSelect AI検証
サンプルデータ準備
検証のため以下のデータを準備します。
MEMBER_PROFILE:メンバーのプロファイル
MEMBER_PROFILE.csv
MEMBER_ID,LAST_NAME_JP,LAST_NAME,MAIL_ID
A001,佐藤,sato,sato@example.com
A002,鈴木,suzuki,suzuki@example.com
A003,高橋,takahashi,takahashi@example.com
A004,田中,tanaka,tanaka@example.com
A005,伊藤,ito,ito@example.com
A006,渡辺,watanabe,watanabe@example.com
A007,山本,yamamoto,yamamoto@example.com
A008,中村,nakamura,nakamura@example.com
A009,小林,kobayashi,kobayashi@example.com
A010,加藤,kato,kato@example.com
A011,吉田,yoshida,yoshida@example.com
A012,山田,yamada,yamada@example.com
A013,佐々木,sasaki,sasaki@example.com
A014,山口,yamaguchi,yamaguchi@example.com
A015,松本,matsumoto,matsumoto@example.com
A016,井上,inoue,inoue@example.com
A017,木村,kimura,kimura@example.com
A018,林,hayashi,hayashi@example.com
A019,斎藤,saito,saito@example.com
A020,清水,shimizu,shimizu@example.com
MEMBER_USAGE.csv
MEMBER_ID,USAGE
A006,18447
A009,19337
A015,21202
A011,20744
A018,20853
A018,19579
A003,20949
A015,21949
A017,20044
A011,20330
A004,21280
A002,21880
A004,18896
A009,19877
A016,18144
A014,19369
A016,21764
A020,18981
A007,20687
A005,19050
A008,18619
A018,19947
A006,21063
A003,18031
A018,20540
A015,18564
A014,20710
A012,18331
A020,19402
A020,20786
A001,21048
A012,18166
A013,19349
A014,18797
A012,19980
A004,19085
A019,18370
A011,18839
A018,20936
A019,20418
A018,21846
A013,21661
A008,19146
A003,21091
A010,19180
A009,18246
A007,20812
A017,18958
A008,20336
A017,18347
OCIナビゲーションメニューからOracle Database>>Autonomous Databaseを選択し
データベースアクション>>SQLを選択
データのロード>>新規表へのデータのアップロードを選択
MEMBER_PROFILE.csvをドラッグアンドドロップしてアップロードを押下
MEMBER_USAGE.csvも同様の操作を行います
MEMBER_PROFILE.csvはUTF-8で作成してください。SJISだと文字化けします。
SelectAIを行うための設定
日本語が表示できるSQLクライアントからADBに接続します。
クレデンシャル(genai_cred.sql)は先程と同様です。
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..',
tenancy_ocid => 'ocid1.tenancy.oc1..',
private_key => '-----BEGIN PRIVATE KEY-----
MIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwaaSiAgEAAoIBAQDhxvD/oKFor6h5
:
SAA6TtWn9AD1zRu5raux/w==
-----END PRIVATE KEY-----',
fingerprint => '49:82:………3s:fb:8e'
);
END;
/
AIプロファイルは以下のとおりです。
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
'GENAI',
'{"provider": "oci",
"credential_name": "GENAI_CRED",
"comments":"true",
"object_list": [
{"owner": "ADMIN", "name": "MEMBER_PROFILE"},
{"owner": "ADMIN", "name": "MEMBER_USAGE"}
]
}'
);
END;
/
cloudshell: $ sqlplus admin/【パスワード】@genaidemo_low
SQL> @genai_cred
PL/SQLプロシージャが正常に完了しました。
SQL> @aiprofile
PL/SQLプロシージャが正常に完了しました。
検索精度を上げるためにカラムへコメントを追加します。
COMMENT ON TABLE ADMIN.MEMBER_PROFILE IS 'Member id, japanese last name, lastname and mail address';
COMMENT ON COLUMN ADMIN.MEMBER_PROFILE.MEMBER_ID IS 'Member id';
COMMENT ON COLUMN ADMIN.MEMBER_PROFILE.LAST_NAME_JP IS 'Member japanese last name';
COMMENT ON COLUMN ADMIN.MEMBER_PROFILE.LAST_NAME IS 'Member last name';
COMMENT ON COLUMN ADMIN.MEMBER_PROFILE.MAIL_ID IS 'Member Mail address';
COMMENT ON TABLE ADMIN.MEMBER_USAGE IS 'Member id and usage';
COMMENT ON COLUMN ADMIN.MEMBER_USAGE.MEMBER_ID IS 'Member id';
COMMENT ON COLUMN ADMIN.MEMBER_USAGE.USAGE IS 'Member usage';
SelectAI実施
usage合計が50000を超えるユーザー情報を抽出してみました。
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQLプロシージャが正常に完了しました。
SQL> column MEMBER_ID format a5
SQL> column MAIL_ID format a25
SQL> column LAST_NAME format a10
SQL> column LAST_NAME_JP format a10
SQL> SELECT AI show me members name,mail address and usage total whose usage total exceeds 50,000;
MEMBE MAIL_ID LAST_NAME LAST_NAME_ USAGE_TOTAL
----- ------------------------- ---------- ---------- -----------
A015 matsumoto@example.com matsumoto 松本 61715
A009 kobayashi@example.com kobayashi 小林 57460
A017 kimura@example.com kimura 木村 57349
A011 yoshida@example.com yoshida 吉田 59913
A014 yamaguchi@example.com yamaguchi 山口 58876
A012 yamada@example.com yamada 山田 56477
A008 nakamura@example.com nakamura 中村 58101
A018 hayashi@example.com hayashi 林 123701
A020 shimizu@example.com shimizu 清水 59169
A004 tanaka@example.com tanaka 田中 59261
A003 takahashi@example.com takahashi 高橋 60071
11行が選択されました。
SQL> SELECT AI SHOWSQL show me members name,mail address and usage total whose usage total exceeds 50,000;
RESPONSE
--------------------------------------------------------------------------------
SELECT mp.member_id, mp.mail_id, mp.last_name, mp.last_name_jp, SUM(mu.usage) AS
usage_total
FROM ADMIN.MEMBER_PROFILE mp
JOIN ADMIN.MEMBER_USAGE mu ON mp.member_id = mu.member_id
GROUP BY mp.member_id, mp.mail_id, mp.last_name, mp.last_name_jp
HAVING SUM(mu.usage) > 50000
Select AIによって作成されたSQLを微調整することによってさらに必要なデータを取得することが可能です。