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

この記事で記載すること

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

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

前提

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

環境

Object Storage上に置く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" : "[e-mail]"}

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

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にアップロード

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

SQLを選択

Worksheetからスクリプトを実行

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'CRED_JSON_EXT',
    username => '[e-mail]',
    password => 'password' );
END;
/

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

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

外部表を作成

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形式でデータがストアされてることを確認

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

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

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データを二次元表としてアクセスできることを確認

参考

• Document: Autonomous Database
  • Query External Data
  • Using Resource Principal Credential to Access Oracle Cloud Infrastructure Resources