Operational Property Graphs Example with SQL/PGQ in 23c
https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3659
この演習のLab 1から4までは演習環境の準備で、実際の演習はLab 5から始まります。演習環境はOracle Cloudに構築することを前提としているため、準備のハードルは低くはありません。
先日、Oracle Database 23cとOracle REST Data Servicesのコンテナ・イメージを使って、Oracle Database 23c FreeでOracle APEXが動く環境を作りました（記事その１とその２）。その環境を使ってProperty GraphのLiveLabsを実施してみました。その手順を紹介します。
LiveLabsの演習ではOracle SQL Developerを使用していますが、本記事ではOracle SQL Developer Extension for VS Codeを代わりに使用します。
コンテナ・イメージから作成したOracle APEXの環境では、以下のURLからORDSのランディング・ページにアクセスします。
コンテナ・イメージから作成したOracle APEXの環境で演習を実施するため、Lab 1から4の作業はすべてスキップします。
Lab 5は演習に使用するデータベース・スキーマの準備、Lab 6が実際にProperty Graphを操作するSQLの実行を行います。Oracle APEXのアプリケーションを使った演習は、Lab 6に続くBonus Labとして提供されています。
今回はBonus Labまでを行なうので、Lab 5、6で使用するスキーマをOracle APEXのワークスペースに紐づけて作成します。
ワークスペースを作成する
Oracle APEXの管理サービスにサインインし、新規にワークスペースを作成します。
管理ユーザー名とパスワードは、Oracle APEXのインストール時にスクリプト@apxchpwdを実行して決めた値になります。
作成済みのワークスペースが存在しない場合、サインイン後にワークスペースの作成を求められます。
次へ進みます。
スキーマは新規に作成するため、既存のスキーマの再利用にいいえを選択します。
今回はスキーマ名はAPEXDEVとしました。このスキーマ名はOracle SQL Developer Extension for VS Code（長いので以降VS Codeとします）からデータベースに接続する際のユーザー名として指定します。Autonomous Database上にワークスペースを作成する場合は、スキーマ名はワークスペース名に接頭辞としてWKSP_が付加された名前がデフォルトになります。Oracle APEXを手作業でインストールしている場合はそのような制限はありませんが、ワークスペース名とそのワークスペースに紐づくデフォルトのスキーマ名は異なっている方が、データベースに直接接続可能なユーザー名を推測しにくくなるため、より安全だと言えます。
スキーマのパスワードを設定します。VS Codeから接続する際に、このパスワードを指定します。領域割当て制限(MB)は10000（10GB）とします。Oracle Database 23c Freeのユーザー・データの制限は12GBなので、ほぼ制限なしの設定になります。
VS CodeからはSQLNet経由でデータベースに接続します。そのため、Autonomous Databaseとは異なり、データベースのユーザーに対してREST対応SQLを有効にする必要はありません。
次へ進みます。
ワークスペースの管理者のユーザー名を指定します。今回の例ではadminとしています。管理者のパスワードと電子メールを指定します。管理者宛の電子メールを受け取るには、送信に使用するメール・サーバーを構成する必要があります。電子メールの構成については割愛します。
次へ進みます。
設定内容を確認し、ワークスペースの作成を実行します。
ワークスペースが作成されました。同時にスキーマも作成されています。
LiveLabsのLab 5とLab 6は、VS Codeから作成したスキーマに接続して実施します。
Lab 5 Configure setup materials and toolsの実施
VS Codeにデータベースへの接続を追加します。
接続名は任意、ロールはデフォルトです。ユーザー名、パスワードにAPEXのワークスペースとともに作成したスキーマ名、パスワードを指定します。パスワードの保存にチェックを入れています。ホスト名はlocalhost、サービス名はfreepdb1です。
テストを実施し設定を確認した上で、接続を保存します。
保存された接続をクリックすると、データベースに接続されます。
以上で後続の作業をVS Codeから実施できるようになりました。
（Task 1 - 3）グラフのセットアップに使用するマテリアル23cfree-property-graph.zipをダウンロードします。マテリアルは以下のURLからダウンロードします。
wgetはインストールされていない場合もあるため、以下ではcurlを使ってダウンロードしています。
% curl -OL https://objectstorage.us-ashburn-1.oraclecloud.com/p/VEKec7t0mGwBkJX92Jn0nMptuXIlEpJ5XJA-A6C9PymRgY2LhKbjWqHeB5rVBbaV/n/c4u04/b/livelabsfiles/o/data-management-library-files/23cfree-property-graph.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 815k 100 815k 0 0 403k 0 0:00:02 0:00:02 --:--:-- 403k
%
（Task 1 - 4）ダウンロードしたマテリアル23cfree-property-graph.zipを解凍します。
unzip -o 23cfree-property-graph.zip
% unzip -o 23cfree-property-graph.zip
Archive: 23cfree-property-graph.zip
inflating: graph/license.txt
inflating: graph/.DS_Store
inflating: graph/CreateKeys.sql
inflating: graph/BANK_ACCOUNTS.csv
inflating: graph/license.txt
inflating: graph/f106.sql
inflating: graph/BANK_TRANSFERS.csv
inflating: graph/BANK_ACCOUNTS.csv
inflating: graph/BANK_TRANSFERS.csv
inflating: graph/CreateKeys.sql
inflating: graph/f106.sql
%
load bank_transfers /home/oracle/examples/graph/BANK_TRANSFERS.csv;
load bank_accounts /home/oracle/examples/graph/BANK_ACCOUNTS.csv;
ZIPファイルの展開先が/home/oracle/examplesとは限りません。CreateKeys.sqlの存在するディレクトリに移動してから実行することを前提として、以下の２行に書き直します。
load bank_transfers ./BANK_TRANSFERS.csv;
load bank_accounts ./BANK_ACCOUNTS.csv;
CSVを表にロードするloadコマンドは、SQLclの組み込みコマンドです。そのため、CreateKeys.sqlはSQLclより実行します。
接続上でコンテキスト・メニューを表示し、SQLclを開きます。
ターミナルでSQLclが開始します。パスワードを入力しデータベースにSQLclで接続します。
（Task 2 - 7）最初にcdコマンドでCreateKeys.sqlがあるディレクトリに移動し、続いてCreateKeys.sqlを実行します。
@CreateKeys.sql
表BANK_TRANSFERに5001行、BANK_ACCOUNTSに1000行のデータがロードされていることを確認します。
以上でスキーマのセットアップは完了です。
この後の作業でSQLclは使用しないので、SQLclはexitで終了します。
Lab 6 Operational Property Graphs Example with SQL/PGQ in 23 の実施
VS CodeでSQLワークシートを開いて、LiveLabsで案内されているSQLをコピペして実行することによりLab 6の演習を進めることができます。
Task 1 - 2：プロパティ・グラフBANK_GRAPHを作成します。表BANK_ACCOUNTSを頂点、BANK_TRANSFERSを辺として使用します。
CREATE PROPERTY GRAPH文を実行します。
Task 1 - 3：プロパティ・グラフBANK_GRAPHを確認します。
ビューUSER_PROPERTY_GRAPHSを検索します。
Task 1 - 4：プロパティ・グラフBANK_GRAPHを作成したDDLを表示します。
DBMS_METADATA.GET_DDLを実行します。
ビューUSER_PG_ELEMENTSを検索します。
Task 1 - 6：プロパティ・グラフBANK_GRAPHが持つプロパティとそのラベルを確認します。
ビューUSER_PG_LABEL_PROPERTIESを検索します。
Task 2 - 1：最も送金を多く受け付けている（金額ではなく回数）上位10行を、受付回数の多い順番でリストします。
Task 2 - 2：送金を中継している回数が多い上位１0行を、中継回数の多い順番でリストします。
Task 2 - 3：今までの問い合わせの両方でアカウントID387が（先頭で）検索されています。アカウントID387からの送金を１から３ホップ内で受け付けた銀行とその送金を検索します。
Task 2 - 4：送金が送金元に３ホップで循環している送金を検索します。送金元の銀行から媒介している銀行へ送金され、媒介している銀行から送金元へ送金されている取引です。
Task 2 - 5：３ホップではなく４ホップのケース（中間に２行を経由する）を検索します。
Task 2 - 6：５ホップのケースを検索します。
Task 2 - 7：３から５回のホップで送金元に戻る送金を行なっている銀行を、１０行リストします。
Task 2 - 8：３から５回のホップで送金元に戻る送金を行なっている銀行の上位10行を、回数の多い順番でリストします。
Task 2 - 9：表BANK_TRANSFERSに６つの送金を追加します。
Task 2 - 10：Task 2 - 1で実行した問い合わせ（送金を受け付けた回数が多い上位10行）を再実行します。アカウントID387、934、135の順番が 135、934、387に変わっています。
結果は０件です。
Task 2 - 12：表BANK_TRANSFERSに３つの送金を追加します。
Task 2 - 13：Task 2 - 11の検索を再実行します。送金元のアカウントIDが39で、送金が４ホップで送金元に循環している件数です。
循環している送金の件数が0回から5回に変わります。
Task 2 - 14：送金元のアカウントIDが39で、４ホップで送金が循環している５つの送金について、送金経路を一覧します。
Task 2 - 15：最後に演習で挿入した９行の送金を表BANK_TRANSFERSより削除します。CSVからデータをロードした直後の状態に戻るため、再度演習を行なうことができます。
Lab 6の演習は以上で終了です。
Bonus Lab Integration with APEXの実施
Bonus Labの実際の作業は、23cfree-property-graph.zipに含まれているAPEXアプリケーションf106.sqlのインポートです。以下に接続し、作成済みのワークスペースにサインインします。
管理者ユーザーによる初回接続時は、パスワードの変更を求められます。
アプリケーション・ビルダーを開きます。
アプリケーションf106.sqlのインポート作業を始めます。
インポートをクリックします。
インポートするファイルとしてf106.sqlを選択します。
次へ進みます。
アプリケーションのインストールを実行します。
サポートするオブジェクトのインストールを実行します。
アプリケーションSQL Property Graph in Oracle Database 23cのインストールが完了しました。
アプリケーションを実行します。
アプリケーションに管理者ユーザーでサインインします。
インポートしたアプリケーションには、Property Graph queries with SQLとUsing the GraphVis Pluginの２枚のページが含まれています。
これまでの作業を実施していれば、Pre-requisitesはすべて満たした状態になっています。
Property Graph queries with SQLのページでは、主にLab 6にて実施した演習の内容が実装されています。
最初の２つのリージョンでは、送金の多い上位10行を検索しています。
追加したデータを削除し表BANK_TRANSFERSを最初の状態に戻す機能は、APEXアプリケーションには実装されていないようです。Task 2 - 15のDELETE文をSQLコマンドから実行することにより、表BANK_TRANSFERSを最初の状態に戻すことができます。
Using Graph Visualization Pluginのページでは、いくつかのSELECT文の結果をGraph Visualization Pluginを使って表示しています。
Lab 6のTask 2 - 2の送金を中継している回数が多い上位10行の取引を表示しています。
送金が４ホップで循環している経路を表示しています。
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
