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 |
| SODA for C | Oracle Client 19.6 以降 SODA for C |
| SODA for Node.js | Oracle Client 19.6 以降 node-oracledb |
| SODA for Python | Oracle Client 19.6 以降 cx_Oracle 7 |
| 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のロード
- Object Storageバケットの作成
- JSONファイルのアップロード
- object store credentialsの作成
- DBMS_CLOUD.COPY_COLLECTIONでのロード
1.Object STorageバケットの作成
oci os bucket create -name fruit_bucket -c <compartment id>
2.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を使用する手順
- WebコンソールでATPインスタンスの詳細ページから「サービス・コンソール」をクリック
- 「開発」をクリック
- 「RESTfulサービスおよびSODA」にベースURLが表示され、「URLのコピー」をクリック
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にロードすることができました。