[AutonomousDB] Autonomous DatabaseにJSONドキュメントを格納してみた。（ORDS+SODA)

Autonomous DatabaseでJSONドキュメントを扱うことができます。
格納方法は複数ありますが、今回は、Simple Oracle Document Access (SODA) API を使用した方法を試してみました。
格納されたJSONドキュメントはSQLやPL/SQLからも操作可能です。
※ この記事の作業は データベース・バージョン:19c の Autonomous Transaction Processing で実行しています。

# Simple Oracle Document Access（SODA）
Simple Oracle Document Access (SODA) は、SQLやデータベースへの格納方法を知らなくても、JSON ドキュメントを「コレクション」としてAutonomous Databaseに格納・取得・クエリ実行ができるNoSQL スタイルの API のセットです。
データベースに格納されたJSONデータへは、SQL Clientからアクセスが可能で、「コレクション」は、データベースのテーブルとビューによってバックアップ可能です。

SODA ドライバは、以下を含むいくつかの言語とフレームワークで利用可能です。
Java、Node.js、Python、C（Oracle Call Interfaceを使用）、PL/SQL、SODA for REST

| SODA API |  入手方法 |
|:-----------------:|:------------------:|
| SODA for Java | Oracle Database 19c (19.6) JDBC Driver & UCP [SODA for Java 1.1.4](https://github.com/oracle/soda-for-java/releases) |
| SODA for C | Oracle Client 19.6 以降 [SODA for C](https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/c/19/adsdc/soda-c-prerequisites.html#GUID-944DC773-6A9B-4F9E-8404-EC0B9CF8975D) |
| SODA for Node.js | Oracle Client 19.6 以降 [node-oracledb](https://oracle.github.io/node-oracledb/INSTALL.html#quickstart) |
| SODA for Python | Oracle Client 19.6 以降 [cx_Oracle 7](https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html) |
| SODA for PL/SQL | AutonomousDB内に内包 |
| SODA for REST | RESTful Servicesへのアクセス |


# JSONドキュメントのロード

今回はAutonomous DatabaseにコレクションとしてJSONドキュメントをロードする以下の２つの方法を検証しました。

- SODA for PL/SQL (DBMS_CLOUD.COPY_COLLECTION)
- SODA for REST (Oracle REST Data Services)

## DBMS_CLOUD.COPY_COLLECTION でのロード
SQL ClientからローカルSODAコレクションもしくはObject Storageに格納されたJSONドキュメントをAutonomous Databaseにロードします。
Object Storageからロードする場合DBMS_CLOUD.COPY_COLLECTIONを利用する。
DBMS_CLOUD.COPY_COLLECTIONは、以下のロードをサポートしています。

- 行区切りJSONをコレクションにロード
- JSONドキュメントの配列をコレクションにロード

### 行区切りJSONのロード

1. Object Storageバケットの作成
2. JSONファイルのアップロード
3. object store credentialsの作成
4. DBMS_CLOUD.COPY_COLLECTIONでのロード

1.Object STorageバケットの作成

```
oci os bucket create -name fruit_bucket -c <compartment id>
```

2.JSONファイルのアップロード

```fruit.json
{"name" : "apple", "count": 20 }
{"name" : "orange", "count": 42 }
{"name" : "pear", "count": 10 }
```

```
oci os object put --bucket-name fruit_bucket --file "fruit.json"
```

3.object store credentialsの作成

```
SET DEFINE OFF
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'atpc_user@example.com',
    password => 'password'
  );
END;
/
```

4.DBMS_CLOUD.COPY_COLLECTIONでのロード

```
BEGIN  
 DBMS_CLOUD.COPY_COLLECTION(    
    collection_name => 'fruit',
    credential_name => 'DEF_CRED_NAME',
    file_uri_list   => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/fruit_bucket/o/fruit.json',
    format          => JSON_OBJECT('recorddelimiter' value '''\n''')  );
END;
/
```

- collection_name: ターゲット・コレクションの名前
- credential_name: 作成したクレデンシャルの名前
- file_uri_list: 読み込むソースファイル（複数の場合カンマ区切り）
- format: 以下のフォーマットをサポート
    - unpackarrays、recorddelimiter、ignoreblanklines、characterset、rejectlimit、compression

### コレクションはDatabase上の表として作成
作成されたコレクション（fruit）の確認

```
SQL> desc  fruit
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(255)
 CREATED_ON                                NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                             NOT NULL TIMESTAMP(6)
 VERSION                                   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT                                      BLOB
```

JSON形式で"JSON_DOCUMENT"列にロードされます。

```
SQL> SELECT
     f.json_document.name,
     f.json_document.count,
     f.json_document.color
FROM fruit f;


NAME   COUNT COLOR 
------ ----- ----- 
apple 20
orange 42          
perr 10

3行が選択されました。
```


#### COPY_COLLECTIONによるロードの監視とトラブルシューティング
ロード操作は、dba_load_operations と user_load_operations に記録されます。
ディクショナリを検索し、進行中または完了したデータロードに関する情報を確認します。
COPY操作の例

```
SELECT table_name, owner_name, type, status, start_time, update_time, logfile_table, badfile_table FROM user_load_operations WHERE type = 'COPY';
TABLE_NAME  OWNER_NAME   TYPE    STATUS     START_TIME             UPDATE_TIME           LOGFILE_TABLE   BADFILE_TABLE
FRUIT       ADMIN        COPY    COMPLETED  2020-04-23 22:27:37    2020-04-23 22:27:38   ""              ""         
FRUIT       ADMIN        COPY    FAILED     2020-04-23 22:28:36    2020-04-23 22:28:37   COPY$2_LOG      COPY$2_BAD
```

- LOGFILE_TABLE列は、ロード操作のログが格納されている表の名前
- BADFILE_TABLE列は、ロード操作のエラー情報が格納されている表の名前
注:LOGFILE_TABLEテーブルとBADFILE_TABLEテーブルは、ロード操作ごとに2日間保存され、その後自動的に削除されます。

## Oracle REST Data Services (REST) でのロード
Autonomous DatabaseのOracle REST Data Services(ORDS)は、HTTP(S)動詞(GET、POST、PUT、DELETEなど)をデータベース・トランザクションにマップし、JSONを使用してフォーマットした結果を返します。
Autonomous DatabaseではORDSが事前構成済みで、ORDSは、lowのサービスを使用してデータベースに接続します。

### Autonomous DatabaseでのSODA for RESTの使用
SODA for RESTによりAutonous DatabaseにJSONドキュメントを格納することが可能です。
JSONドキュメントは名前付きコレクションに格納され管理され、SODAコレクションに格納されたJSONは、必要に応じてSQLからアクセスが可能です。

#### Oracle REST Data ServicesとSODAを使用する手順

1. WebコンソールでATPインスタンスの詳細ページから「サービス・コンソール」をクリック
![図1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/5c37f9f0-6e6c-be17-2582-52f8b58da03f.jpeg)
2. 「開発」をクリック
![図2.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/f9ca7d16-3f6c-b9c6-b02d-b0fbcfabeb5f.jpeg)
3.  「RESTfulサービスおよびSODA」にベースURLが表示され、「URLのコピー」をクリック![図3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/61cd9c67-e2ae-ffc8-6391-2f3fabc45491.jpeg)


SODA for RESTは、次のURLパターンで使用できます。
/ords/schema/soda/latest/*
schemaは、REST対応データベース・スキーマ(例: "admin")に対応します。
以下は、ADMINユーザでの操作例


#### コレクションの作成

ADMINスキーマに「fruit」という名前の新しいコレクションの作成

```
curl -X PUT -u 'ADMIN:password' "https://rzsf8o3up2w8rzc-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"
```

#### JSONドキュメントの格納
JSONドキュメントをfruitコレクションに挿入

```
> curl -X POST -u 'ADMIN:password' \
 -H "Content-Type: application/json" --data '{"name":"orange", "count":42}' \
 "https://rzsf8o3up2w8rzc-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"
```

#### コレクションから格納されたJSONドキュメントを取得

```
 > curl -X POST -u 'ADMIN:password' \
 -H "Content-Type: application/json" --data '{"name":"orange"}' \
 "https://rzsf8o3up2w8rzc-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit?action=query"

{
  "items": [
    {
      "id":"6F7E5C60197E4C8A83AC7D7654F2E375",
      "etag":"57215643953D7C858A7CB28E14BB48549178BE307D1247860AFAB2A958400E16",
      "lastModified":"2019-07-12T19:00:28.199666Z",
      "created":"2020-05-02T19:00:28.199666Z",
      "value":{"name":"orange", "count":42}
    }
  ],
  "hasMore":false,
  "count":1
}

```

#### コレクションへSQLからアクセス
SQLでfruitコレクションにアクセス

```
SQL> SELECT
     f.json_document.name,
     f.json_document.count,
     f.json_document.color
FROM fruit f;


NAME   COUNT COLOR 
------ ----- ----- 
orange 42          


1行が選択されました。
```

# おわりに
REST を 使って JSON ドキュメントを Autonomous Databaseにロードすることができました。

# 参考情報

- [12 Using JSON Documents with Autonomous Database](https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/document-database.html#GUID-78AA9D61-7370-44D3-BB58-D236FEB19CFB)
- [8 Developing RESTful Services in Autonomous Database](https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/ords-autonomous-database.html#GUID-E2E921FF-2D80-4E32-9660-28506D10BADB)
- [はじめてのAutonomous Databaseへのデータロード（Object Storage経由の場合）](https://qiita.com/mikika/items/5323651cb5826a5b495e)