日日是Oracle APEX: Autonomous DatabaseでRで記述したファンクションを表関数として実行する

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

PythonではpyqEval、pyqTableEval、pyqRowEval、pyqGroupEval、pyqIndexEvalとして提供されているSQL APIが、R向けにはrqEval2、rqTableEval2、rqRowEval2、rqGroupEval2、rqIndexEval2として提供されています。SQL APIが呼び出す言語はPythonとRで異なりますが、SQL APIの役割や引数は同じです。

OML4PyのSQL APIの場合、オンプレミスとAutonomous DatabaseでSQL APIのファンクション名は同じだったのですが、OML4RはオンプレミスではrqEval、rqTableEval、rqRowEval、rqGroupEvalとなっています。Autonomous DatabaseのSQL APIはこれらのファンクション名の末尾に2が付いていて、異なるファンクションになっています。今回はAutonomous Databaseで作業を行うので、末尾に２がついたSQL APIのファンクションを呼び出します。

本記事では、すでに作成済みのAPEXアプリケーションを使ってOML4RのSQL APIをAutonomous Databaseから呼び出してみます。対象となるRスクリプトは、OML4R User’s Guideの以下のセクションに記載されています。

9.6 SQL API for Embedded R Execution with Autonomous Database

アプリケーションの実装はOML4Pyで作成したものと同じく、以下の方針でSQL APIを呼び出します。

SQL APIを呼び出す処理は開発者が実施し、表関数の結果を実表にコピーします。
APEXアプリケーションでは実表を扱い、SQL APIを直接呼び出すことはしません。

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

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

APEXからみるとAutonomous DatabaseでのSQL APIは、OML4PyとOML4Rでは決定的に異なる部分があります。

Autonomous DatabaseでのAPEXのSQLコマンドで以下のSELECT文を実行し、セッションのPROXY_USER、SESSIOON_USERおよびCURRENT_USERを確認します。

select
sys_context('userenv','proxy_user')   as proxy_user,
sys_context('userenv','session_user') as session_user,
sys_context('userenv','current_user') as current_user
from dual

Autonomous Databaseでは、PROXY_USERはORDS_PUBLIC_USER、SESSION_USERはORDS_PLSQL_GATEWAY、CURRENT_USERはAPEXワークスペースのパーシング・スキーマになります。

OML4Rが提供しているビューUSER_RQ_SCRIPTSをAPEXアプリケーションから検索するときは、APEXワークスペースのパーシング・スキーマではなくPROXY_USERのORDS_PUBLIC_USERを所有者として認識しています。また、rqScriptCreateやrqScriptDropのファンクションも、ユーザーORDS_PUBLIC_USERで処理されます。結果として作成したスクリプトの所有者はORDS_PUBLIC_USERになります。OML4RのSQL APIの実行はAPEXワークスペースのパーシング・スキーマで実行されます。そのため、Rスクリプトの所有者が異なり、APEXアプリケーションから呼び出されたSQL APIは、指定されたRスクリプトを見つけることができません。

APEXアプリケーションではプライベートなRスクリプトを管理できません。そのため、今回はすべてのRスクリプトをグローバルなスクリプトとして作成しています。所有者を限定したい場合は、OMLノートブックなどからスクリプトの作成や削除を行う必要があります。

これから実施する作業は、OML4Pyで行った作業とほとんど同じですが、スクリプト名を読み替えたりしなくても良いように省略せずに記載します。

事前準備

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

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

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

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

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

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

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

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

rqAppendHostAceを呼び出して、スキーマWKSP_APEXDEVにネットワークACLを追加します。（OML4PyではpyqAppendHostAceです。接頭辞のrqとpyqが異なります）。

begin
    rqAppendHostAce('WKSP_APEXDEV','adb.us-ashburn-1.oraclecloudapps.com');
end;

rqGetHostAceを呼び出して設定を確認します。

DECLARE 
    hostname VARCHAR2(4000);
BEGIN
    hostname := rqGetHostACE('WKSP_APEXDEV'); 
    DBMS_OUTPUT.put_line ('hostname: ' || hostname);
END;

rqAppendHostAceにはドメイン名を渡していますが、rqGetHostAceではAutonomous Databaseの接続先であるホスト名が返されます。

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

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

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

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

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

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

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

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

3.2 Administrative Tasks for Creating and Saving a Conda Environmentに記載されているコマンドを記述します。

１番目の段落に以下を記述します。Rを実行する環境としてmyrenvを作成します。

%conda

create -n myrenv -c conda-forge --override-channels --strict-channel-priority r-base=4.0.5 r-forecast r-ggplot2

２番目の段落に以下を記述します。作成した環境myrenvをオブジェクト・ストレージにアップロードします。

%conda

upload myrenv --overwrite --description 'Install R forecast and ggplot2 packages' -t user 'WKSP_APEXDEV' -t application 'OML4R'

３番目の段落に以下を記述します。保存されている環境を一覧します。

%conda

list-saved-envs

４番目の段落に以下を記述します。環境myrenvの情報を表示します。

%conda

list-saved-envs -e myrenv

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

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

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

OMLノートブックからサインアウトし、ユーザーWKSP_APEXDEV（APEXのワークスペース・スキーマ）でサインインし直します。

例題の実行に使用する表IRISを作成します。OML4Pyでの作業で作成した表IRISとは定義や内容が異なるため、すでに表がある場合は削除します。

ADMINでの作業と同様に、スクラッチパッドを開きます。

１番目の段落に以下を記述します。

%sql
drop table if exists iris

２番目の段落に以下を記述します。

%r
ore.create(iris, table = 'IRIS')

３番目の段落に以下を記述します。

%sql
select count(*) from iris

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

３番目の段落の結果として、表IRISに含まれるデータの件数が返されていることを確認します。

以上で準備は完了です。

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

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

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

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

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

次へ進みます。

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

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

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

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

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

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

OML4R SQL APIの実行

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

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

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

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

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

（REST API for Embedded R ExecutionのAuthenticateのセクションはOML4Pyと違い、リフレッシュ・トークンについての記述自体がありません。）

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

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

Rスクリプトの作成、編集、削除ができます。ただし、OML4PyのPythonスクリプトの所有者はAPEXのワークスペース・スキーマでした。OML4Rではグローバル・スコープで作成され、所有者はRQSYSになります。

OML4Rのドキュメントに記載されているRスクリプトを、Claudeに頼んで読みやすくフォーマットしてもらっています。ロジックに違いはありません。

ボタン作成または変更の適用をクリックしたときに、rqScriptCreateを呼び出してRスクリプトを作成しています。引数v_globalにtrueを与えています。

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

ボタン削除をクリックしたときに、rqScriptDropを呼び出してRスクリプトを削除しています。こちらでも引数v_globalにtrueを渡しています。

begin
    sys.rqScriptDrop(
        v_name   => :P3_NAME,
        v_global => true,
        v_silent => false
    );
end;

ボタン9-32: RandomRedDot2 - JSONをクリックすると、以下のコードが実行されます。SQL APIの結果を保存する表RQ_E01の内容を削除したのち、SQL APIの検索結果を表RQ_E01にコピーしています。SQL APIの引数OUT_FMTの指定に依存して、コピー対象に含まれる列は変わりますが、他の同期呼び出しの処理もほとんど同じです。

begin
    delete from rq_e01;
    /* copy SQL API result into the table */
    insert into rq_e01(name, value)
    select name, value from table(
        rqEval2(
            par_lst => '{"ore_service_level":"LOW"}',
            out_fmt => 'JSON',
            scr_name => 'RandomRedDots2'
        )
    );
end;