日日是Oracle APEX: Delta Sharingを使ってインスタンスが異なるAPEXワークスペース間でデータを共有する (1)

Autonomous Database上で利用できるDBMS_SHAREパッケージを使って、異なるインスタンスにあるAPEXワークスペース間でデータを共有してみます。片側のインスタンスをDelta SharingのProviderとして構成し、もう一方のインスタンスをConsumerとして構成します。

作業は、Oracle LiveLabsの以下のインストラクションに沿って実施します。

Implement Data Sharing with PL/SQL in Autonomous Database

https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3747

この他に、Jakub Illnerさんの以下の記事も参照しています。オブジェクト・ストレージ上のファイル・サイズ、エクスポートにかかる時間、デバッグ方法などについても考察されています。

Automate Publishing of Data Shares from Autonomous Database

https://jakubillner.github.io/2024/01/19/data-sharing-automation.html#publishing-data-share

PL/SQLを使う要件がなければ、Data Studioを使って作業ができます。
Implement Data Sharing with ADB Data Studio

https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3771

今回はデータを共有するための作業をAPEXのアプリケーションに組み込むため、PL/SQLからDBMS_SHAREのプロシージャを呼び出します。

作業環境は、ProviderとConsumerの双方とも、Always FreeのAutonomous Data Warehouse、Oracle Database 19c上のOracle APEX 24.1です。

これより作業について記載します。

最初にAPEXの作業を行うワークスペースを作成します。

以下では、ワークスペース名はDWAPEX、ワークスペース・ユーザー名（つまり管理者）もDWAPEXとしています。ワークスペース・パスワードに加えて、データベース・パスワードも設定します。

APEXのワークスペースとしてDWAPEX、ワークスペースに紐づくワークスペース・スキーマとしてWKSP_DWAPEXが作成されます。通常、APEXを動作させるにあたってワークスペース・スキーマにパスワードは不要です。今回はSQLclでワークスペース・スキーマに接続して実行するスクリプトがあるため、データベース・パスワードをあらかじめ設定しておきます。

作成したワークスペースDWAPEXに接続し、SQLワークショップのRESTfulサービスを開きます。

ORDSにスキーマを登録をクリックします。これはORDS_ADMIN.ENABLE_SCHEMAの呼び出しと同等の操作になります。

ORDSスキーマ属性のRESTfulアクセスの有効化をオンにします。スキーマ別名はdwapexです。ORDS_ADMIN.ENABLE_SCHEMAの引数p_url_mapping_patternへの値ですが、無指定の場合はスキーマ名がデフォルト、つまりwksp_dwapexになります。APEXとの相互運用に影響するため、ワークスペース名と同じdwapexにします。

サンプル・サービスのインストールはオンにします。サンプルのRESTサービスとともに、表EMPとDEPTがワークスペース・スキーマWKSP_DWAPEXに作成されます。

SQL Developer Webに管理者ADMINで接続し、SQLワークシートを開きます。

ワークスペース・スキーマWKSP_DWAPEXがShare Providerとなるための設定を行います。

スキーマWKSP_DWAPEXにロールCONNECT、RESOURCE、DWROLE、ADPADMINを追加します。

grant connect,resource,dwrole,adpadmin to wksp_dwapex;

ロールADPADMINはLiveLabsの手順には含まれていませんが、Always FreeのAutonomous Database上でDBMS_SHARE.UPDATE_DEFAULT_SHARE_PROPERTYを実行するために必要なロールです。Always Freeでない、また、Consumer側では不要と思われます。

データ共有を有効にします。

begin
    dbms_share.enable_schema(
        schema_name => 'WKSP_DWAPEX'
        ,enabled => true
    );
end;

共有されるデータは、オブジェクト・ストレージのバケットに配置されます。ワークスペース・スキーマWKSP_DWAPEXからオブジェクト・ストレージへのネットワーク接続を許可するACLを追加します。以下ではネットワーク上のあらゆるホストへの接続を許可しています。LiveLabsの手順では、接続先はオブジェクト・ストレージに限定されています。

begin
    dbms_network_acl_admin.append_host_ace(
        host => '*'
        ,ace => xs$ace_type(
            privilege_list => xs$name_list('connect'),
            principal_name => 'WKSP_DWAPEX',
            principal_type => xs_acl.ptype_db
        )
    );
end;

以上で、スキーマWKSP_DWAPEXの設定は完了です。

APEXのワークスペースDWAPEXにサインインし、SQLコマンドより以下のSQLを実行します。

SELECT dbms_share.can_create_share FROM dual;

CAN_CREATE_SHAREとして１が返されれば、データ共有が正しく構成されています。

共有するデータを保存するバケットを、オブジェクト・ストレージに作成します。

Oracle Cloudのコンソールより、オブジェクト・ストレージとアーカイブ・ストレージのバケットを開きます。

バケットを作成するコンパートメントとしてAPEXを選択しています。コンパートメントはあらかじめ作成しておく必要があります。

バケットの作成をクリックします。

バケット名はdata-share-bucketとします。暗号化はデフォルトのOracle管理キーを使用した暗号化を選択します。

作成をクリックします。

コンパートメントAPEXにバケットdata-share-bucketが作成されました。

バケットを操作するユーザーをdata_share_providerとして作成します。

Oracle Cloudのコンソールのアイデンティティとセキュリティよりドメインを開き、Defaultドメインを開きます。

アイデンティティ・ドメイン以下のユーザーを選択し、ユーザーの作成をクリックします。

ユーザー名として電子メール・アドレスを使用のチェックを外します。

姓はData Share Provider、ユーザー名はdata_share_providerとします。電子メールも入力します。

以上で作成をクリックします。

ユーザーdata_share_providerが作成されます。

ユーザー機能の編集を開き、このアカウントでできる作業を限定します。

APIキー以外のチェックを外し、変更の保存をクリックします。

リソースからAPIキーを開き、APIキーの追加をクリックします。

APIキーを追加する方法はいくつかありますが、今回は一番手順の簡単なAPIキー・ペアの生成を選択します。

秘密キーのダウンロードをクリックし、秘密キーが書かれたファイルをダウンロードした後に、追加をクリックします。

DBMS_CLOUD.CREATE_CREDENTIALを呼び出してクリデンシャルを作成する際に、引数として与える値が表示されます。コピーして保存しておきます。

閉じるをクリックします。

以上でユーザーdata_share_providerが作成できました。ちなみに、先ほどの構成ファイルの情報は、APIキーの右端にある３点メニューから構成ファイルの表示を実行することで確認できます。

作成したユーザーdata_share_providerがオブジェクト・ストレージのバケットを操作できるように、グループとポリシーを設定します。

アイデンティテイ・ドメインのグループを開き、グループの作成をクリックします。

グループの名前はDataSharingGroupとします。適当な説明を入力し、先ほど作成したユーザーdata_share_providerを、グループに含めるユーザーとしてチェックします。

作成をクリックします。

グループとしてDataSharingGroupが作成されます。

グループDataSharingGroupにオブジェクト・ストレージのバケットを操作する権限を与えるポリシーを作成します。

アイデンティティのポリシーを開き、ポリシーの作成をクリックします。ポリシーを作成するコンパートメントとして、APEXより上位のルート・コンパートメントを選択します。

ポリシーの名前はDataSharingPolicyとします。適当な説明を入力します。コンパートメントはルートを選択します。

ポリシー・ビルダーの手動エディタの表示をオンにし、以下の２行を記述します。コンパートメントAPEXに存在するバケットとオブジェクトに対して、ほぼすべての操作権限を与えています。

Allow group DataSharingGroup to manage buckets in compartment APEX

Allow group DataSharingGroup to manage objects in compartment APEX

ポリシーDataSharingPolicyが作成されました。

以上で、オブジェクト・ストレージの準備は完了です。

今回作成したユーザーdata_share_porviderのAPIキーの情報より、クリデンシャルSHARE_BUCKET_CREDENTIALを作成します。クリデンシャルを作成するために、DBMS_CLOUD.CREATE_CREDENTIALを呼び出します。

begin
    dbms_cloud.create_credential(
        credential_name => 'SHARE_BUCKET_CREDENTIAL'
        ,user_ocid => 'ocid1.userで始まるユーザーOCID - configファイルにuserとして記載'
        ,tenancy_ocid => 'ocid1.tenancyで始まるテナンシーOCID - configファイルにtenancyとして記載'
        ,private_key => 'ダウンロードした秘密キー BEGIN　PRIVATE KEYとEND PRIVATE KEYの行を除く'
    ,fingerprint => 'configファイルにfingerprintとして記載'
    );
end;

ビューUSER_CREDENTIALSを検索し、作成したクリデンシャルを確認します。

select * from user_credentials;

クリデンシャルSHARE_BUCKET_CREDENTIALのENABLEDがTRUEであることを確認します。

この記事ではAPEXのSQLコマンドからDBMS_CLOUD.CREATE_CREDENTIALを呼び出しています。SQLコマンドでは実行したコマンドは秘密キーも含めて、履歴に保存されます。セキュリティ上、あまり良いことではありません。

作成済みのバケットdata-share-bucketを開き、適当なファイルをひとつバケットにアップロードします。アップロードしたファイル（オブジェクト）のオブジェクト詳細の表示を実行して、バケットのパスを確認します。

基本情報のURLパス(URI)として表示されているパスは非推奨となっています。新しいURLをコピーし、末尾のオブジェクト名の部分を削除し/o/で終わる部分までをバケットURLとして扱います。

DBMS_CLOUD.LIST_OBJECTSを呼び出し、作成したクリデンシャルを使ってバケットの内容を一覧できるかどうか、確認します。

select * from dbms_cloud.list_objects(
    credential_name => 'SHARE_BUCKET_CREDENTIAL'
    ,location_uri => 'https://[ネームスペース].objectstorage.[リージョン].oci.customer-oci.com/n/[ネームスペース]/b/data-share-bucket/o/'
)

アップロードしたファイルがリストに表示されれば、クリデンシャルとバケットが正しく作成されていると言えます。

LiveLabsの手順に従って、外部表CUSTSALES_EXTERNALを作成します。作成した外部表CUSTSALES_EXTERNALのコピーとして表CUSTSALESを作成し、この表をデータ共有の対象とします。

まず、外部表CUSTSALES_EXERNALを作成します。

begin
    dbms_cloud.create_external_table(
        table_name => 'custsales_external'
        ,file_uri_list =>'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_landing/o/sales_sample/*.parquet'
        ,format => '{"type":"parquet", "schema": "first"}'
    );
end;