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ワークスペースのパーシング・スキーマではなくSESSION_USERのORDS_PLSQL_GATEWAYを所有者として認識しています。また、rqScriptCreateやrqScriptDropのファンクションも、ユーザーORDS_PLSQL_GATEWAYで処理されます。結果として作成したスクリプトの所有者はORDS_PLSQL_GATEWAYになります。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ワークシートを開きます。
対象となるホスト（ドメイン）として、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;
このホスト名は、アクセス・トークンを取得する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
%conda
list-saved-envs -e myrenv
以上でスクラッチパッドを実行します。
Rの実行環境としてmyrenvが作成され、その後にオブジェクト・ストレージにアップロードされました。
これで、環境myrenvをSQL APIの引数env_nameに指定できるようになりました。SQL APIの引数env_nameにmyrenvを指定したときは、SQL APIの引数scr_nameで指定されたスクリプト内で、環境myrenvにロードされているパッケージを呼び出すことができます。
例題の実行に使用する表IRISを作成します。OML4Pyでの作業で作成した表IRISとは定義や内容が異なるため、すでに表がある場合は削除します。
ADMINでの作業と同様に、スクラッチパッドを開きます。
１番目の段落に以下を記述します。
%sql
drop table if exists iris
２番目の段落に以下を記述します。
%r
ore.create(iris, table = 'IRIS')
３番目の段落に以下を記述します。
select count(*) from iris
以上でスクラッチパッドを実行します。
以上で準備は完了です。
APEXアプリケーションのインポート
APEXのワークスペースにサインインします。
OML4RのSQL APIを呼び出すAPEXアプリケーションをインポートします。
https://github.com/ujnak/apexapps/blob/master/exports/oml4r-sqlapi-admin.zip
ダウンロードしたファイルoml4r-sqlapi-admin.zipを選択します。
次へ進みます。
アプリケーションのインストールをクリックします。
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スクリプトは、アプリケーションのインポート時に作成しています。
以下の内容を静的アプリケーション・ファイルwebr-styler.jsとして作成します。
ページ・プロパティのJavaScriptのファイルURLに以下を記述しています。
[module]#APP_FILES#webr-styler#MIN#.js
ページ・ロード時に実行に以下を記述します。
initializeWebR();
Rスクリプトの作成、編集、削除ができます。ただし、OML4PyのPythonスクリプトの所有者はAPEXのワークスペース・スキーマでした。OML4Rではグローバル・スコープで作成され、所有者はRQSYSになります。
ボタン作成または変更の適用をクリックしたときに、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;
SQL APIの実行結果を実表にコピーしたのち、ブランチによって、その表をソースとした対話モード・レポートのページへリダイレクトします。
結果としてボタン9-32: RandomRedDot2 - JSONをクリックすると、以下のようにSQL APIの実行結果を表示する対話モード・レポートのページが開きます。
後は上から順番にSQL APIからRスクリプトを呼び出していきます。
RandomRedDot2 - PNG、test_ggplot2_noinp - PNGといった末尾にPNGが付いている処理は、チャートを出力します。
SQL APIの非同期ジョブの呼び出し方法を説明します。
最初に以下のコードで非同期ジョブをサブミットします。SQL APIの引数PAR_LSTに与えるJSONに"ore_asyc_flag": trueを含めます。SQL APIの結果の列VALUEにJOB IDを含んだURLが返されるので、URLからJOB IDを取り出しています。
非同期ジョブの開始時に得られたJOB IDより、ジョブのステータスを確認します。以下のコードを実行します。
非同期ジョブが実行中の場合は、列VALUEにjob is still runningが返されます。
最後に実表の対話モード・レポートのページにリダイレクトし、処理結果のレポートを表示します。
以上でOML4RのUser's Guideに記載されている、Autonomous Database向けのSQL APIのサンプルはすべて実行できました。
今回の記事は以上になります。
Oracle APEXのアプリケーションからRのスクリプトを呼び出す際の参考になれば幸いです。
完
