[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にアクセスします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/0f3abe30-ceb4-4193-91b5-9b6d2dab9ac3.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/a56c6ab9-d443-496c-80f3-4420edf50610.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/d8c919b0-50ce-4b35-83e7-e9ece82bdb15.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/f36c30dd-1323-4f19-8877-d5cd15ff8870.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/6d9d2718-f14c-4eae-bf90-60907de113fd.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/c70bc29e-0112-415d-b002-435cb7a57b77.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/de56dfc4-5f18-42a9-a46f-8922a69498eb.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/8a0d8df6-d0ed-41e7-85c5-701e5443d701.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/e6f5962e-5425-42da-ad92-b873d140363e.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/4c8f36c3-dc23-4f78-aef5-084d8f781a51.png)

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


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/29290e71-7b51-4032-b6eb-2d21acaf25fc.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/9af5a4c5-321e-44b8-a89e-58e2b0086180.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/b115e33e-c4db-4af5-8690-41f9a69b24c5.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/c24846f9-bffe-4ab0-b998-e10a2c4fc2b4.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/27e8c7ac-aea0-42fc-9419-d513a902ac80.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/ccd47dd7-6d61-4162-9075-fce3e6349e27.png)

例えば、コマンドプロンプトを選択してバインド変数値を入力すると以下のようなコマンドになりました。
```
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のファイルがダウンロードされました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/45ad639d-83cf-426e-a796-a7f4f84d73a2.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/11367221-385d-4461-8218-78cae22e38a1.png)


件数を変更してみます。JAPANにすると'JAPAN    0'から'JAPAN    9'までのデータ件数になります。
```
curl --location "https://**********************.adb.ap-tokyo-1.oraclecloudapps.com/ords/demouser1/test/gettable/JAPAN" -o output.csv
```

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/f08ba3f7-e11e-4f77-90c2-11523d940028.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/dedb286b-69a1-4237-80e9-ba9d00630275.png)

実行してみると、以下のようになりました。ファイルサイズが326バイトしかありません。
```
curl --location "https://**********************.adb.ap-tokyo-1.oraclecloudapps.com/ords/demouser1/test/gettableall" -o output.csv
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/e7518ba0-67b5-473a-8390-5c6f3948ae2b.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/3c190621-fa48-4153-9ca6-559938465ed4.png)

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

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/3851cbe9-397c-4335-a32b-0c97d77a1e56.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/9b3f200a-a088-4490-bd05-2fa7e9a1c67a.png)

**接続**を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/1749fdf6-4f2e-4c5a-8f21-67a0a1757bac.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/7c109120-aa84-4b5e-9c76-dafee71cfacb.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/2ccd98a5-3b68-4d1b-91ff-66947b410ba2.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/05f26239-5000-42b3-a897-6fefcc363ed6.png)

次に**SQL**にアクセスし、DEMOUSER1にDBMS_CLOUDパッケージの実行権限を以下のコマンドで付与します。
```sql
GRANT execute ON dbms_cloud TO demouser1;
```

## テンプレート、ハンドラの作成
**DEMOUSER1ユーザー**で**Database Actions**にログインし、**REST**にアクセスします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/0f3abe30-ceb4-4193-91b5-9b6d2dab9ac3.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/6571dd6c-0bc6-4cfc-8639-73c3b4b50983.png)


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

```sql
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;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/1446dc58-bc06-418c-9c1c-b210c56b3423.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/f9b8e06c-ca8b-48b7-97a6-76110e365aac.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/1c9213a7-1dc3-4c79-bfe8-6f5e68911e10.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/7e1ad741-0ae4-4975-9611-54895a1a6bc4.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/6b771392-2765-4865-a7cd-70114062ef1a.png)

正常に完了しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/4f2cd019-0074-4f66-9222-c4cf71e4f16d.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/93f7fbb5-6db3-4bef-bc8b-7c77b331f369.png)

## アクセスしてみる
以下をコマンドプロンプトで入力します。
```
curl -v -X POST -H "Content-Type: application/json" "https://**********************.adb.ap-tokyo-1.oraclecloudapps.com/ords/demouser1/test/gettableall" -d "{\"tabname\" : \"ssb.dwdate\"}"
```
出力はこのようになりました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/340d8c92-6207-45db-b713-1c28607e3f45.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/d84fb2e4-338d-4dee-a8d9-dfaf903200c0.png)

なお、[ドキュメントの使用上のノート](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/serverless/adbsb/dbms-cloud-subprograms.html#GUID-F8A70BE2-6060-48A7-9667-0A6B39198071)にあるように、DBMS_CLOUD.EXPORT_DATAでのテキスト出力は、デフォルトのチャンクサイズが10MBなので、10MB以上のサイズのファイルになる場合は複数のファイルになります。このチャンクサイズはmaxfilesizeオプションで1GBまで調整することができます。
そこで、最小構成の2ECPUのAutonomous Database(ATP)で、1GBを指定してSSB.CUSTOMER表を指定してみましたが、PGAメモリ不足になりました・・・。この場合はエラーにならないようECPU数を上げるまたはチャンクサイズを小さくするということになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/e14690fa-5eb1-4ddf-b014-b8571b3a84ea.png)


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

# 参考情報
+ [ORDS: Build Powerful, Secure, RESTful ORDS APIs for Your Oracle Autonomous Database](https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/view-workshop?wid=815) : LivelabsにあるORDSのワークショップ