[Autonomous Database]Oracle管理ORDSを使ったREST APIで表データをCSVファイルとしてダウンロードする

はじめに

Autonomous DatabaseのOracle管理のORDSを利用して、REST APIでデータベース内の表のデータをCSVファイルとして取得してみたいと思います。

前提条件

• DEMOUSER1という一般ユーザー(CONNECT、RESOURCE、DWROLEロールを付与済み)で実施
• Database ActionsのRESTを利用、モジュールの認証はなし
• 表はSSBユーザのCUSTOMER表を利用。C_CITY列が'JAPAN 0'(119971件)と'JAPAN%'(1200692件）の条件を付加した場合と全件(30000000件）の場合で確認する

1.REST APIを作成する

Autonomous Database（というかOracle Database）は二通りの方法でREST APIを作成できます。

• AutoRESTによるデータベース・オブジェクトのRESTfulサービス化
• 手動によるRESTfulサービスの作成

データベース・オブジェクトでAutoRESTを有効化するとそのオブジェクトにRESTでのアクセスができるようになります。とても簡単にデータベース・オブジェクトをRESTデータソースとして公開ができるのですが、事前定義された形式になるため、目的にあわない場合はハンドラをSQL、PL/SQLで設定する手動による作成が選べます。
今回はDatabase ActionsのRESTを使って手動で作成していきます。

DEMOUSER1ユーザーでDatabase ActionsにログインしRESTにアクセスします。

モジュールを選択し、モジュールの作成をクリックします。

モジュール名にtest、ベース・パスに/test/、ページ区切りサイズを1000、権限によって保護済を非保護にし、作成をクリックします。

なお、作成前にコードの表示で実行されるコードの確認もできます。

モジュールが作成されるとモジュールの詳細ページが表示されます。次にテンプレートを作成します。テンプレートの作成をクリックします。

テンプレートの作成の画面ではURIテンプレートにgettable/:citynameを指定します。そのほかはデフォルトの設定のままにして作成をクリックします。

以下はコードの表示を選んだときの出力です。

テンプレートが作成されるとテンプレートの詳細ページが表示されます。次にハンドラを作成するためハンドラの作成をクリックします。

ハンドラの作成画面では、メソッドにGET、ソース・タイプにCSV問合せを選択します。ソースには以下のSQLを入力します。SSB.CUSTOMER表からC_CITY列に対して:citynameに入る値でLIKE検索をしています。使用可能なMIMEはメソッドがPUTとPOSTのときのみに有効なのでGETでは設定しません。
入力が完了したら作成をクリックします。

SELECT 
  C_CUSTKEY,
  C_NAME,
  C_ADDRESS,
  C_CITY,
  C_NATION,
  C_REGION,
  C_PHONE,
  C_MKTSEGMENT 
FROM 
  SSB.CUSTOMER WHERE C_CITY LIKE :cityname||'%'

以下はコードの表示を選んだ時の出力です。

ハンドラが作成されました。

右端の縦の三点リーダーをクリックするとcURLコマンドというメニューがあります。

アクセスすると コマンドプロンプト、PowerShell、Bashの3つのターミナルでのcURLコマンドが確認できます。

バインド変数値を入力したケースでの確認もできます。

例えば、コマンドプロンプトを選択してバインド変数値を入力すると以下のようなコマンドになりました。

curl --location ^
 "https://**********************.adb.ap-tokyo-1.oraclecloudapps.com/ords/demouser1/test/gettable/JAPAN    0"

2.cURLでアクセスしてみる

ローカルPCのコマンドプロンプトで実行してみます。
cURLコマンドでの結果をそのままではなく、空白は%20で置き換え、結果をファイルで出力させるため-o 出力ファイル名を追加しました。

curl --location "https://**********************.adb.ap-tokyo-1.oraclecloudapps.com/ords/demouser1/test/gettable/JAPAN%20%20%20%200" -o output.csv

実行すると12.7Mのファイルがダウンロードされました。

出力されたファイルを確認するとカンマ区切りで出力されていることが確認できました。

件数を変更してみます。JAPANにすると'JAPAN 0'から'JAPAN 9'までのデータ件数になります。

curl --location "https://**********************.adb.ap-tokyo-1.oraclecloudapps.com/ords/demouser1/test/gettable/JAPAN" -o output.csv

実行してみると127Mのファイルが作成されました。

次に全件ダウンロードを試してみます。同じモジュールtestの下に新たにテンプレートをテンプレートURIを/gettableallで追加し、条件をつけないで全件検索するハンドラを作成してみました。

実行してみると、以下のようになりました。ファイルサイズが326バイトしかありません。

curl --location "https://**********************.adb.ap-tokyo-1.oraclecloudapps.com/ords/demouser1/test/gettableall" -o output.csv

output.csvを確認してみると以下のようなエラーが出力されています。

Requested array size exceeds VM limit　Javaのヒープサイズを超えたというエラーのようです。Oracle管理のORDSはインフラ関連のパラメータ調整はできないので、大量データのダウンロードは別の方法を考えたほうがよさそうです。

3.Object Storageに出力させる

ハンドラではPL/SQLも記述できます。別の方法としてPL/SQLで指定した表をObject Storageに出力するようにしてみます。

リソース・プリンシパルの有効化と権限の付与

Object Storageのアクセスで使用するクレデンシャルはリソース・プリンシパルを利用することにします。ADMINユーザーでDatabase Actionsにログインします。

Data Studioのデータ・ロードにアクセスします。

接続を選択します。

作成からリソース・プリンシパルの管理を選択します。

リソース・プリンシパルの管理の画面が表示されます。
ADMINユーザーのリソース・プリンシパルの有効化をOnに変更し、使用可能なユーザーからDEMOUSER1を選択して、選択したユーザーに移動します。そして実行をクリックします。

以下が表示され、クレデンシャルとしてリソース・プリンシパルが利用できるようになります。

次にSQLにアクセスし、DEMOUSER1にDBMS_CLOUDパッケージの実行権限を以下のコマンドで付与します。

GRANT execute ON dbms_cloud TO demouser1;

テンプレート、ハンドラの作成

DEMOUSER1ユーザーでDatabase Actionsにログインし、RESTにアクセスします。

テンプレートgettableallにアクセスし、別のハンドラを作成するため、ハンドラの作成をクリックします。

ハンドラの作成画面が表示されます。メソッドにPOSTを選択します。ソース・タイプは自動的にPL/SQLが選択されますので、ソースに以下のPL/SQLコードを入力し、作成をクリックします。:tabnameの値と一致する表のデータをObject Storageに出力するという内容です。

BEGIN  
     DBMS_CLOUD.EXPORT_DATA(
        credential_name=> 'OCI$RESOURCE_PRINCIPAL',
        file_uri_list => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<テナンシ名>/b/<バケット名>/o/output.csv',
        query => 'select * from '||:tabname,
        format => json_object('type' value 'csv', 'header' value true)
     );
END;

コードの表示を選んだ時の出力です。

メソッドがPOSTのハンドラが作成されました。

ソースの部分では実行ボタンをクリックすることで指定したPL/SQLの実行ができるので、コードが正しく動作するかを確認できます。

実行してみるとバインド変数の入力画面が表示されました。件数が少ないDWDATE表で試してみます。

正常に完了しました。

ソースで指定したバケットを参照してみると、出力がされていました。

アクセスしてみる

以下をコマンドプロンプトで入力します。

curl -v -X POST -H "Content-Type: application/json" "https://**********************.adb.ap-tokyo-1.oraclecloudapps.com/ords/demouser1/test/gettableall" -d "{\"tabname\" : \"ssb.dwdate\"}"

出力はこのようになりました。

バケットを参照すると、新たにファイルが出力されていました！

なお、ドキュメントの使用上のノートにあるように、DBMS_CLOUD.EXPORT_DATAでのテキスト出力は、デフォルトのチャンクサイズが10MBなので、10MB以上のサイズのファイルになる場合は複数のファイルになります。このチャンクサイズはmaxfilesizeオプションで1GBまで調整することができます。
そこで、最小構成の2ECPUのAutonomous Database(ATP)で、1GBを指定してSSB.CUSTOMER表を指定してみましたが、PGAメモリ不足になりました・・・。この場合はエラーにならないようECPU数を上げるまたはチャンクサイズを小さくするということになります。

おわりに

REST APIを使って表のデータをCSVファイルとしてダウンロードする方法を試してみました。データのサイズにあわせて適切な方法を選択したほうがよさそうです。あと、Database ActionsのRESTは結構便利です。

参考情報

• ORDS: Build Powerful, Secure, RESTful ORDS APIs for Your Oracle Autonomous Database : LivelabsにあるORDSのワークショップ