2025年7月15日火曜日

Autonomous DatabaseでPythonで記述したファンクションを表関数として実行する

Autonomous DatabaseでOML4PyのSQL APIを呼び出し、Pythonで記述したファンクションを表関数として実行してみます。オンプレミスのOracle Databaseでも同様の作業を実施し、「Oracle DatabaseでPythonで記述したファンクションを表関数として実行する」として記事を公開しています。

Autonomous Databaseでもオンプレミスと同じくSQL APIとしてpyqEval、pyqTableEval、pyqRowEval、pyqGroupEvalが提供されています。Autonomous Databaseではこの他にpyqIndexEvalが追加されています。また、Autonomous Databaseの場合、オンプレミスと比べると以下の違いがあります。
  1. 認証トークンの取得時にスキーマのパスワードが必要です。認証トークンの取得方法が特殊なため、APEXのWeb資格証明にパスワードを保存できません。認証トークンの期限はデフォルトで3600秒で、ユーザーは3600秒ごとに再認証する必要があります。しかし、そもそもAPEXのアプリケーションを使うユーザーは、通常スキーマのパスワードを知りません。
  2. SQL APIの処理に時間がかかるためか、非同期ジョブというオンプレミスでは提供されていない機能が追加されています。PAR_LSTに与えるJSONの属性oml_async_flagにtrueを指定することにより、SELECT文の実行と結果の取得を異なるタイミングで実施できます。
このような違いのためAutonomous Databaseでは、以下の方針でOML4PyのSQL APIを呼び出すことにします。
  1. SQL APIを呼び出す処理は開発者が実施し、表関数の結果を実表にコピーします。
  2. APEXアプリケーションでは実表を扱い、SQL APIを直接呼び出すことはしません。
本記事では、すでに作成済みのAPEXアプリケーションを使ってOML4PyのSQL APIをAutonomous Databaseから呼び出してみます。対象となるPythonスクリプトは、OML4Py User’s Guideの以下のセクションに記載されています。


作成済みのAPEXアプリケーションは以下よりダウンロードできます。

https://github.com/ujnak/apexapps/blob/master/exports/oml4py-sqlapi-admin.zip


事前準備



前提条件ですが、Autonomous DatabaseではAPEXのワークスペースとしてAPEXDEVが作成済みで、スキーマとしてWKSP_APEXDEVがデフォルト・パーシング・スキーマとして割り当てられている状態とします。異なる名前のスキーマを使用する場合は、以下の説明に含まれるWKSP_APEXDEVの部分を置き換えてください。

OML4PyのSQL APIを呼び出すために必要な権限を、スキーマWKSP_APEXDEVに与えます。

SQL Developer Webに管理者ユーザーADMINでサインインし、管理のデータベース・ユーザーを開きます。

スキーマWKSP_APEXDEVの編集画面を開き、OMLをオンにします。また、認証トークンの取得に、スキーマのパスワードが必要です。APEXワークスペースと同時に作成されたスキーマにはパスワードが設定されていないので、未設定であればパスワードも設定します。


スキーマWKSP_APEXDEVにネットワークACLを追加します。

SQLワークシートを開きます。

13.7.1 Access and Authorization Procedures and Functionsの手順に沿って作業を進めます。

対象となるホスト（ドメイン）として、adb.<リージョン名>.oraclecloudapps.comを与えます。現在アクセスしているURLから、リージョン名を見つけることができます。


pyqAppendHostAceを呼び出して、スキーマWKSP_APEXDEVにネットワークACLを追加します。
begin
    pyqAppendHostAce('WKSP_APEXDEV','adb.us-ashburn-1.oraclecloudapps.com');
end;

pyqGetHostAceを呼び出して設定を確認します。
DECLARE 
    hostname VARCHAR2(4000);
BEGIN
    hostname := pyqGetHostACE('WKSP_APEXDEV'); 
    DBMS_OUTPUT.put_line ('hostname: ' || hostname);
END;
pyqAppendHostAceにはドメイン名を渡していますが、pyqGetHostAceではAutonomous Databaseの接続先であるホスト名が返されます。


このホスト名は、アクセス・トークンを取得するURLのホスト名になります。

OML4Pyのサンプル・スクリプトを実行する際に使用する環境を作成します。

OMLノートブックにサインインします。

https://<テナントID>-<データベース名>.adb.us-ashburn-1.oraclecloudapps.com/oml

ユーザー名とパスワードとして、管理者ユーザーADMINとそのパスワードを指定します。

サインインをクリックします。


condaを使ってPythonを実行する環境を作成します。

スクラッチパッドを開きます。


ひとつめの段落に以下を記述します。作業実施時点でOML4Pyが採用しているPythonのバージョンを指定します。2025年7月15日現在では3.12.6です。

%conda
create -n seaborn python=3.12.6 seaborn

ふたつめの段落に以下を記述します。

%conda
upload seaborn --overwrite --description 'Python package for seaborn' -t python 3.12.6 -t application OML4PY

以上でスクラッチパッドを実行します。


Pythonの実行環境としてseabornが作成され、その後にオブジェクト・ストレージにアップロードされました。

これで、環境seabornをSQL APIの引数env_nameに指定できるようになりました。SQL APIの引数env_nameにseabornを指定したときは、SQL APIの引数scr_nameで指定されたスクリプト内で、環境seabornにロードされているパッケージを呼び出すことができます。


以上で準備は完了です。


APEXアプリケーションのインポート



APEXのワークスペースにサインインします。

OML4PyのSQL APIを呼び出すAPEXアプリケーションをインポートします。

https://github.com/ujnak/apexapps/blob/master/exports/oml4py-sqlapi-admin.zip


ダウンロードしたファイルoml4py-sqlapi-admin.zipを選択します。

次へ進みます。


アプリケーションのインストールをクリックします。


サポートするオブジェクトのインストールをクリックします。サポートするオブジェクトのインストールとして、SQL APIから呼び出すPythonスクリプトの作成と、SQL APIの実行結果を保存する表の作成が行われます。


APEXアプリケーションがインポートされました。

アプリケーションを実行します。


APEXアプリケーションにサインインすると、以下のホーム・ページが開きます。


以上でAPEXアプリケーションのインポートが完了しました。


OML4Py SQL APIの実行



OML4Pyのサンプル・コードを実行する前に、認証トークンの取得とセットを行います。

スキーマのパスワードを入力し、ボタンGet Token / Set Auth Tokenをクリックします。

ボタンをクリックすると以下のコードが実行され、認証トークンの取得と設定が行われます。

ページが送信されたのち、Is Token SetがTRUEに変われば、正常に認証が完了しています。


認証トークンの有効期限は3600秒なので、Is Token SetがTRUEでも有効期間が過ぎるとSQL APIの呼び出しが失敗するようになります。その場合はパスワードを入力し、再認証します。

（REST API for Embedded Python ExecutionのAuthenticateのセクションにRefresh the Tokenとしてリフレッシュ・トークンを使う方法が記述されていますが、そもそもgrant_type=passwordで認証した際に返されるのがaccessTokenのみでrefreshTokenが含まれていません。それに加えてcurlの呼び出し例もAuthorizationヘッダーに与えるトークンとrefresh_tokenに与えるトークンが同じなので、どこかが間違っているように思います。）

最初にPythonファンクションpyqFun1を、SQL APIのpyqEvalから呼び出してみます。

ナビゲーション・メニューのPython Scriptsを開きます。OML4PyのUser's Guideに記載されているSQL APIから呼び出すPythonスクリプトは、アプリケーションのインポート時に作成しています。


Pythonスクリプトの編集ページにボタンFormatを作成しています。Pythonのコード・フォーマッターはPythonで記述されているものが多いので、ページにPyodideとautopep8をロードして、Pythonのフォーマッターを実行しています。

ページ・プロパティのJavaScriptのファイルURLに以下を記述しています。

https://cdn.jsdelivr.net/pyodide/v0.28.0/full/pyodide.js

ファンクションおよびグローバル変数の宣言で、フォーマッターを記述しています。


ページ・ロード時に実行に以下を記述します。

main();


ボタンFormatをクリックしたときに、動的アクションでJavaScriptファンクションformatCode()を呼び出しています。


ボタン作成または変更の適用をクリックしたときに、pyqScriptCreateを呼び出してPythonスクリプトを作成しています。

declare
    l_script clob;
begin
    l_script := :P3_SCRIPT;
    l_script := replace(:P3_SCRIPT, CHR(13)||CHR(10),CHR(10)); -- remove CR
    sys.pyqScriptCreate(
        v_name      => :P3_NAME,
        v_script    => l_script,
        v_global    => false,
        v_overwrite => ( :REQUEST = 'SAVE' )
    );
end;

ボタンpyqFun1 - JSONをクリックすると、以下のコードが実行されます。SQL APIの結果を保存する表PYQ_E01の内容を削除したのち、SQL APIの検索結果を表PYQ_E01にコピーしています。SQL APIの引数OUT_FMTの指定に依存して、コピー対象に含まれる列は変わりますが、他の同期呼び出しの処理もほとんど同じです。
begin
    delete from pyq_e01;
    /* copy SQL API result into the table */
    insert into pyq_e01(name, value)
    select name, value from table(
        pyqEval(
            par_lst => '{"oml_service_level":"LOW"}',
            out_fmt => 'JSON',
            scr_name => 'pyqFun1'
        )
    );
end;

SQL APIの実行結果を実表にコピーしたのち、ブランチによって、その表をソースとした対話モード・レポートのページへリダイレクトします。


結果としてボタンpyqFun1 - JSONをクリックすると、以下のようにSQL APIの実行結果を表示する対話モード・レポートのページが開きます。


後は上から順番にSQL APIからPythonスクリプトを呼び出していきます。create_iris_tableの実行で作成される表IRISや、myLinearRegressionModelの実行で作成されるモデルlinregr、sample_iris_tableで作成される表SAMPLE_IRISに依存している後続の処理があるため、実行する順番には注意が必要です。

test_seaborn_noinp、test_seaborn_inp、mygroupcountといった処理では、condaで作った環境seabornにロードしているパッケージmatplotlibを使用して、チャートを出力しています。


SQL APIの非同期ジョブの呼び出し方法を説明します。

最初に以下のコードで非同期ジョブをサブミットします。SQL APIの引数PAR_LSTに与えるJSONに"oml_asyc_flag": trueを含めます。SQL APIの結果の列VALUEにJOB IDを含んだURLが返されるので、URLからJOB IDを取り出しています。



非同期ジョブの開始時に得られたJOB IDより、ジョブのステータスを確認します。以下のコードを実行します。

非同期ジョブが実行中の場合は、列VALUEにjob is still runningが返されます。


非同期ジョブが終了したら、以下のコードを実行して結果を実表にコピーします。




最後に実表の対話モード・レポートのページにリダイレクトし、処理結果のレポートを表示します。


OML4Py Release 2-23aiのUser's Guideの13.7.3.4 Asynchronous Job Exampleに記載されているサンプル・コードは以下になっています。
set long 150 
  select * 
  from table(pyqGroupEval(
  inp_nam => 'IRIS',
  par_lst => '{"oml_input_type":"pandas.DataFrame",
  "oml_async_flag":true, "oml_graphics_flag":true, 
               "modelName":"linregr", "datastoreName":"pymodel"}', 
  out_fmt => NULL,
  grp_col => 'Species',
  ord_col => NULL,
  scr_name => 'linregrPredict',
  scr_owner => NULL 
));
スクリプトlinregrPredictはPNG出力をするコードを含んでいません。この部分については、以下のPythonスクリプトをlingrPredict2として作成し、上記のlinregrPredictを置き換えています。

以上でOML4PyのUser's Guideに記載されている、Autonomous Database向けのSQL APIのサンプルはすべて実行できました。

OML4PyのSQL APIを呼び出していると「ORA-20100: Resource usage exceeded for free tier」というエラーが発生しました。


OMLノートブックにもアクセスできなくなります。


Free TierではOMLの操作で利用できるVMのリソースは多くはありません。24時間を基準に利用量を計測しているとのことなので、24時間程度使用せずに待てば利用可能なリソースは回復するみたいです。

今回の記事は以上です。

Oracle APEXのアプリケーションからPythonのスクリプトを呼び出す際の参考になれば幸いです。

完
投稿者 時刻:
ラベル: ,