Object Storage上のJSONファイルをAutonomous Databaseから外部表としてアクセスしたときのメモ


# この記事で記載すること

Object Storage上のJSONファイルをAutonomous Databaseから外部表としてアクセスしたときの手順メモを記載します。
必要な手順としては以下となります。

1. クラウド環境セットアップ（ADB、ObjectStorage）
1. JSONファイルをObjectStorageにアップロード
1. ADBからObujectStorageにアクセスするための準備（クレデンシャル or プリンシパル設定）
1. JSONファイルにアクセスする外部表の作成
1. JSONファイルを二次元表として扱うためのViewの作成

# 前提
以下を前提とします。
- Autonomous Database（ADB）がデプロイされている
- Object StorageのBucketが作成されている

# 環境
Object Storage上に置くJSONファイル

```json
{"name" : "Blake", "job" : "Intern", "salary" : 30000}
{"name" : "Smith", "job" : "Programmer", "salary" : 80000}
{"name" : "Miller", "job" : "Programmer", "salary" : 90000}
{"name" : "Clark", "job" : "Manager", "salary" : 100000}
{"name" : "King", "job" : "President", "salary" : 200000, "email" : "king@example.com"}
```

上記JSONファイルを二次元表としてアクセスするためのテーブル（View）

```sql
CREATE OR REPLACE FORCE EDITIONABLE VIEW "ADMIN"."V_EMP_JSON_EXT" (
    "job",
    "name",
    "email",
    "salary"
) DEFAULT COLLATION "USING_NLS_COMP" AS
    SELECT
        JT."job",
        JT."name",
        JT."email",
        JT."salary"
    FROM
        "ADMIN"."EMP_JSON_EXT" RT,
        JSON_TABLE ( "JSON_DOCUMENT", '$[*]'
                COLUMNS
                    "job" VARCHAR2 ( 16 ) PATH '$.job',
                    "name" VARCHAR2 ( 8 ) PATH '$.name',
                    "email" VARCHAR2 ( 16 ) PATH '$.email',
                    "salary" NUMBER PATH '$.salary'
            )
        JT;
```

# 手順
## JSONファイルをObjectStorageにアップロード

JSON_EXTバケット > Upload > select files > Uploadの順に選択・実行してemp.jsonファイルをObjectStorageにアップロード

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/984d2420-f6b7-0ebb-662a-f659091fb917.png)

## ADBからObujectStorageにアクセスするための準備（クレデンシャル設定）

SQLを選択

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/03f9af9c-37f0-0795-055a-5cf0cbfb38b9.png)

Worksheetからスクリプトを実行

```sql
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'CRED_JSON_EXT',
    username => 'adb_user@example.com',
    password => 'password' );
END;
/
```

※ username/passwordは事前に入手しておく必要がある

## JSONファイルにアクセスする外部表の作成

外部表を作成

```sql
BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
                              TABLE_NAME     => 'EMP_JSON_EXT',
                              credential_name => 'JSON_EXT_CRED'
                              FILE_URI_LIST  => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/JSON_EXT/o/emp.json',
                              COLUMN_LIST    => 'json_document blob',
                              FIELD_LIST     => 'json_document char(10000)',
                              FORMAT         => JSON_OBJECT(
                                                  'type' VALUE 'json',
                                                  'rejectlimit' VALUE '999999999'
                              )
    );
END;
/
```

外部表にアクセス
→ BLOB形式でデータがストアされてることを確認

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/b86d3ca8-36a4-e674-7d79-fb1b48470c1b.png)

## JSONファイルを二次元表として扱うためのViewの作成

BLOBにストアされているデータをViewを作成のうえ参照

```sql
CREATE OR REPLACE FORCE EDITIONABLE VIEW "ADMIN"."V_EMP_JSON_EXT" (
    "job",
    "name",
    "email",
    "salary"
) DEFAULT COLLATION "USING_NLS_COMP" AS
    SELECT
        JT."job",
        JT."name",
        JT."email",
        JT."salary"
    FROM
        "ADMIN"."EMP_JSON_EXT" RT,
        JSON_TABLE ( "JSON_DOCUMENT", '$[*]'
                COLUMNS
                    "job" VARCHAR2 ( 16 ) PATH '$.job',
                    "name" VARCHAR2 ( 8 ) PATH '$.name',
                    "email" VARCHAR2 ( 16 ) PATH '$.email',
                    "salary" NUMBER PATH '$.salary'
            )
        JT;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/54326670-989f-bc21-0f69-996c8458311a.png)


JSONデータを二次元表としてアクセスできることを確認


# 参考
- Document: Autonomous Database 
 - [Query External Data](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/query-external-data.html#GUID-72DC8BB6-5245-4262-A100-C35A2E553F70)
 - [Using Resource Principal Credential to Access Oracle Cloud Infrastructure Resources](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/resource-principal.html#GUID-E283804C-F266-4DFB-A9CF-B098A21E496A)