LoginSignup
12
3

More than 1 year has passed since last update.

@takuya_0301(Takuya Niita)in日本オラクル株式会社

Oracle Database 23cの注目機能、JSON Relational Dualityを触ってみる

Last updated at Posted at 2023-04-14

はじめに

先日Oracle Database 23cのFree-Developer Releaseがリリースされました。

23cの注目機能の一つにJSON Relational Duality（ブログなどでは、日本語で「JSONとリレーショナルの二面性」と記載されていますが、全然ピンとこないのでこの記事ではそのまま英語で記載します）があります。

この機能は、一言で言えば、「データをJSON形式でスキーマレスに扱えるドキュメント指向データベースの良さと、ACID特性を持ちデータをリレーショナルに扱うRDBMSの双方の特徴を融合したもの」です。

この後紹介していきますが、具体的には通常のリレーショナルなテーブルと、JSON Relational Duality Viewというビュー(このビューがドキュメント指向データベース(のようなもの) 1になっています)を作成し、そのいずれからもデータの取得、更新などが実施可能な仕組みです。一方を更新すると、もう一方のデータも自動的に更新されるというものです。　
データ更新の際には、もちろんデータの整合性が保証されます。(データ整合性の仕組みについてはこの記事で詳細は解説できませんが、軽めに触れます)

つまり、この機能を利用すると、従来のドキュメント指向データベースとRDBMSのトレードオフを考慮しなくても良いということになります。

百聞は一見にしかずなので、早速触っていきましょう。
この記事では、JSON Relational Dualityの機能や仕組み全てを網羅することはできないので、なんとなくイメージを掴んでいただけるように紹介していきます。

環境構築

Oracle Database 23cのインストール

すでに様々な賢者の方々が環境構築手順を公開されているので、皆様の環境に合わせて構築してください。
この記事ではOracle Linux8にインストールした環境を使っていきます。

ユーザ（スキーマ）の作成

ここでは、検証用のデータベースユーザ（スキーマ）を作成していきます。

oracleユーザで以下のコマンドを実行します。
この記事では、PDB(pluggable database)を利用します。

[oracle@database23c ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Apr 14 02:16:00 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered.

SQL>

以下のDDLを実行します。
この記事では、JSON Relational Dualityの略称でJRDというスキーマを作成します。
パスワードは任意です。

SQL> CREATE USER JRD IDENTIFIED BY "<パスワード>" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

作成したユーザを管理者ユーザにします。

SQL> GRANT DBA TO JRD;

SQL> GRANT UNLIMITED TABLESPACE TO JRD;

これでユーザの作成は完了です。

ORDSのインストール

JSON Relational Dualityで利用するビューには以下の手段でアクセスが可能です。

  • SQL
  • HTTP/S(REST API)
  • MongoDB API
  • ORDS(Oracle REST Data Services)
  • SODA(Simple Oracle Document Access)

この記事では、SQLとORDSを利用したHTTP/S(REST API)でアクセスしたいと思います。

そこで、ORDSのインストールが必要なので、インストールしていきます。

インストールは以下のドキュメントをベースに実施してください。

インストール時に指定するデータベースサービス名とユーザ名はそれぞれFREEPDB1、JRD(もしくは作成したユーザ名)にしてください。

ora-01031 insufficient privilegesエラー
インストール時にora-01031 insufficient privilegesという権限エラーが発生した場合は、以下のコマンドでORDSパッケージに含まれているDDLを実行してください。
実行時はSYSDBAユーザで実行してください。
DDLは、/opt/oracle/ords/scripts/ords_installer_privileges.sqlに存在します。（デフォルト設定でのインストールの場合)

SQL> @/opt/oracle/ords/scripts/ords_installer_privileges.sql JRD

テーブルとJSON Relational Duality Viewの作成

いよいよ、テーブルとJSON Relational Duality Viewを作成していきます。

今回は以下のDDLを利用します。
スキーマ名(データベースユーザ名)を変更した方は、ORDS.ENABLE_OBJECTプロシージャのスキーマ名を変更してください。

このブログではDDLで作成しているraceテーブルおよびその関連テーブルやトリガーについては利用しません。

drop view if exists team_dv;
drop view if exists race_dv;
drop view if exists driver_dv;
drop table if exists driver_race_map;
drop table if exists race;
drop table if exists driver;
drop table if exists team;

CREATE TABLE IF NOT EXISTS team
(team_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name    VARCHAR2(255) NOT NULL UNIQUE,
   points  INTEGER NOT NULL,
   CONSTRAINT team_pk PRIMARY KEY(team_id));

CREATE TABLE IF NOT EXISTS driver
(driver_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
 name VARCHAR2(255) NOT NULL UNIQUE,
 points INTEGER NOT NULL,
 team_id INTEGER,
CONSTRAINT driver_pk PRIMARY KEY(driver_id),
CONSTRAINT driver_fk FOREIGN KEY(team_id) REFERENCES team(team_id));

CREATE TABLE IF NOT EXISTS race
(race_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name      VARCHAR2(255) NOT NULL UNIQUE,
   laps      INTEGER NOT NULL,
   race_date DATE,
   podium  JSON,
   CONSTRAINT   race_pk PRIMARY KEY(race_id));

CREATE TABLE IF NOT EXISTS driver_race_map
(driver_race_map_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
race_id INTEGER NOT NULL,
driver_id INTEGER NOT NULL,
position INTEGER,
CONSTRAINT driver_race_map_pk PRIMARY KEY(driver_race_map_id),
CONSTRAINT driver_race_map_fk1 FOREIGN KEY(race_id) REFERENCES race(race_id),
CONSTRAINT driver_race_map_fk2 FOREIGN KEY(driver_id) REFERENCES driver(driver_id));

CREATE OR REPLACE TRIGGER driver_race_map_trigger BEFORE INSERT ON driver_race_map
FOR EACH ROW
DECLARE
    v_points  INTEGER;
    v_team_id INTEGER;
BEGIN
SELECT team_id INTO v_team_id FROM driver WHERE driver_id = :NEW.driver_id; 
IF :NEW.position = 1 THEN
    v_points := 25;
ELSIF :NEW.position = 2 THEN
  v_points := 18;
ELSIF :NEW.position = 3 THEN
  v_points := 15;
ELSIF :NEW.position = 4 THEN
  v_points := 12;
ELSIF :NEW.position = 5 THEN
  v_points := 10;
ELSIF :NEW.position = 6 THEN
  v_points := 8;
ELSIF :NEW.position = 7 THEN
  v_points := 6;
ELSIF :NEW.position = 8 THEN
  v_points := 4;
ELSIF :NEW.position = 9 THEN
  v_points := 2;
ELSIF :NEW.position = 10 THEN
  v_points := 1;
ELSE
  v_points := 0;
END IF;
UPDATE driver SET points = points + v_points
 WHERE driver_id = :NEW.driver_id;
UPDATE team SET points = points + v_points
    WHERE team_id = v_team_id;
END;
/

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_dv AS
  SELECT JSON {'raceId' IS r.race_id,
               'name'   IS r.name,
               'laps'   IS r.laps WITH NOUPDATE,
               'date'   IS r.race_date,
               'podium' IS r.podium WITH NOCHECK,
               'result' IS
[ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id, 'position' IS drm.position,
                                UNNEST
                                  (SELECT JSON {'driverId' IS d.driver_id,
                                                  'name'     IS d.name}
                                     FROM driver d WITH NOINSERT UPDATE NODELETE
WHERE d.driver_id = drm.driver_id)} FROM driver_race_map drm WITH INSERT UPDATE DELETE
WHERE drm.race_id = r.race_id ]} FROM race r WITH INSERT UPDATE DELETE;

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {'driverId' IS d.driver_id, 'name' IS d.name,
               'points'   IS d.points,
               UNNEST
                 (SELECT JSON {'teamId' IS t.team_id,
                               'team'   IS t.name WITH NOCHECK}
                    FROM team t WITH NOINSERT NOUPDATE NODELETE
                    WHERE t.team_id = d.team_id),
               'race'     IS
[ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id, UNNEST
                                  (SELECT JSON {'raceId' IS r.race_id,
                                                'name'   IS r.name}
                                     FROM race r WITH NOINSERT NOUPDATE NODELETE
                                  WHERE r.race_id = drm.race_id),
                            'finalPosition'   IS drm.position}
FROM driver_race_map drm WITH INSERT UPDATE NODELETE
                WHERE drm.driver_id = d.driver_id ]}
FROM driver d WITH INSERT UPDATE DELETE;

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_dv AS
SELECT JSON {'teamId' IS t.team_id, 'name' IS t.name,
               'points'  IS t.points,
               'driver'  IS
[ SELECT JSON {'driverId' IS d.driver_id, 'name' IS d.name,
'points' IS d.points WITH NOCHECK} FROM driver d WITH INSERT UPDATE
WHERE d.team_id = t.team_id ]} FROM team t WITH INSERT UPDATE DELETE;

BEGIN
    ORDS.ENABLE_OBJECT(
        P_ENABLED  => TRUE,
        P_SCHEMA => 'JRD',
        P_OBJECT  =>  'DRIVER_DV',
        P_OBJECT_TYPE => 'VIEW',
        P_OBJECT_ALIAS => 'driver_dv',
        P_AUTO_REST_AUTH => FALSE
    );
    COMMIT; 
END;
/
BEGIN
    ORDS.ENABLE_OBJECT(
        P_ENABLED => TRUE,
        P_SCHEMA => 'JRD',
        P_OBJECT =>  'RACE_DV',
        P_OBJECT_TYPE => 'VIEW',
        P_OBJECT_ALIAS => 'race_dv',
        P_AUTO_REST_AUTH => FALSE
    ); 
    COMMIT;
END; 
/ 
BEGIN
    ORDS.ENABLE_OBJECT(
        P_ENABLED => TRUE,
        P_SCHEMA => 'JRD',
        P_OBJECT =>  'TEAM_DV',
        P_OBJECT_TYPE => 'VIEW',
        P_OBJECT_ALIAS => 'team_dv',
        P_AUTO_REST_AUTH => FALSE
    ); 
    COMMIT;
END;
/

このDDL内の以下の部分に注目します。

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {'driverId' IS d.driver_id, 'name' IS d.name,
               'points'   IS d.points,
               UNNEST
                 (SELECT JSON {'teamId' IS t.team_id,
                               'team'   IS t.name WITH NOCHECK}
                    FROM team t WITH NOINSERT NOUPDATE NODELETE
                    WHERE t.team_id = d.team_id),
               'race'     IS
[ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id, UNNEST
                                  (SELECT JSON {'raceId' IS r.race_id,
                                                'name'   IS r.name}
                                     FROM race r WITH NOINSERT NOUPDATE NODELETE
                                  WHERE r.race_id = drm.race_id),
                            'finalPosition'   IS drm.position}
FROM driver_race_map drm WITH INSERT UPDATE NODELETE
                WHERE drm.driver_id = d.driver_id ]}
FROM driver d WITH INSERT UPDATE DELETE;

ここで作成しているJSON RELATIONAL DUALITY VIEWがいわゆるドキュメント指向データベース(のようなもの)1のビューを作成しているDDLになります。
先ほどのDDL全体では、3つのJSON RELATIONAL DUALITY VIEWを作成しています。

また、このJSON RELATIONAL DUALITY VIEWはGraphQL構文を利用して書くこともできます。
例えば、driver_dvをGraphQL構文で書く場合は以下のようになります。

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
  driver @insert @update @delete
  {
    driverId : driver_id
    name     : name
    points   : points
    team @noInsert @noUpdate @noDelete @unnest
    {
      teamId : team_id
      team   : name @noCheck
    }
    race : driver_race_map @insert @update @noDelete
    [
     {
      driverRaceMapId : driver_race_map_id
      race @noInsert @noUpdate @noDelete @unnest
      {
        raceId : race_id
        name   : name
      }
      finalPosition   : position
     }
    ]
  };

SQLよりもかなりシンプルに書くことできますね。
DDL内にGraphQL構文を書けるというのは面白い試みだなと個人的には思います。

以上で、　テーブルおよびJSON Relational Duality Viewの作成は完了です。

動作確認

ここからは動作確認を行います。

今回はORDSを利用してHTTPでアクセスします。

まずはORDSを起動します。
今回はスタンドアローンモードで起動します。

[oracle@database23c ~]$ ords serve
2023-04-14T03:01:42Z INFO       ORDS has not detected the option '--config' and this will be set up to the default directory.

ORDS: Release 23.1 Production on Fri Apr 14 03:01:44 2023

Copyright (c) 2010, 2023, Oracle.

Configuration:
  /etc/ords/config/

2023-04-14T03:01:45.077Z INFO        HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2023-04-14T03:01:45.103Z INFO        Disabling document root because the specified folder does not exist: /etc/ords/config/global/doc_root
2023-04-14T03:01:51.017Z INFO        Configuration properties for: |default|lo|
db.servicename=freepdb1
=========
java.class.version=61.0

2023-04-14T03:01:51.026Z WARNING     *** jdbc.MaxLimit in configuration |default|lo| is using a value of 20, this setting may not be sized adequately for a production environment ***
2023-04-14T03:01:51.027Z WARNING     *** jdbc.InitialLimit in configuration |default|lo| is using a value of 3, this setting may not be sized adequately for a production environment ***

cURLでteam_dvビューにアクセスします。
エンドポイントはhttp://localhost:8080/ords/<スキーマ名>/<ビュー名>になります。
ORDSインストール時にポート番号などを変更している場合は、適宜変更してください。

[opc@database23c ~]$ curl http://localhost:8080/ords/jrd/team_dv/ | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   796    0   796    0     0   1542      0 --:--:-- --:--:-- --:--:--  1542
{
  "items": [],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 0,
  "links": [
    {
      "rel": "self",
      "href": "http://localhost:8080/ords/jrd/team_dv/"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8080/ords/jrd/metadata-catalog/team_dv/"
    },
    {
      "rel": "first",
      "href": "http://localhost:8080/ords/jrd/team_dv/"
    }
  ]
}

まだ何もデータが入っていないので、itemsは空の状態です。

team_dvビューにデータ登録します。

以下のJSONファイルを作成します。

data.json
{
    "teamId": 2,
    "name": "Mercedes",
    "points": 0,
    "driver": [
        {
            "driverId": 105,
            "name": "George Russell",
            "points": 0
        },
        {
            "driverId": 106,
            "name": "Lewis Hamilton",
            "points": 0
        }
    ]
}

以下のコマンドを実行します。

curl -i -X POST --data-binary ＠data.json -H "Content-Type: application/json" http://localhost:8080/ords/jrd/team_dv/

HTTP/1.1 201 Createdでレスポンスが返って来ればOKです。

次に登録したデータを確認してみます。

[opc@database23c ~]$ curl http://localhost:8080/ords/jrd/team_dv/ | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   796    0   796    0     0   3275      0 --:--:-- --:--:-- --:--:--  3275
{
    "items": [
      {
        "teamId": 2,
        "name": "Mercedes",
        "points": 0,
        "driver": [
          {
            "driverId": 105,
            "name": "George Russell",
            "points": 0
          },
          {
            "driverId": 106,
            "name": "Lewis Hamilton",
            "points": 0
          }
        ],
        "_metadata": {
          "etag": "536001F31A8718819AEEF28EC20D8677",
          "asof": "00000000004FE36F"
        },
        "links": [
          {
            "rel": "self",
            "href": "http://localhost:8080/ords/jrd/team_dv/2"
          }
        ]
      }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 2,
    "links": [
      {
        "rel": "self",
        "href": "http://localhost:8080/ords/jrd/team_dv/"
      },
      {
        "rel": "describedby",
        "href": "http://localhost:8080/ords/jrd/metadata-catalog/team_dv/"
      },
      {
        "rel": "first",
        "href": "http://localhost:8080/ords/jrd/team_dv/"
      }
    ]
}

データが登録されていることが確認できます。

ところで、先ほどのレスポンスにETagが含まれていることが確認できます。
これはOracle Databaseが自動的に生成するものです。

例えば、先ほどのデータには以下のようなETagが含まれています。

      "_metadata": {
        "etag": "536001F31A8718819AEEF28EC20D8677",
        "asof": "00000000004FE36F"
      }

このETagはデータ整合性を担保するために利用できます。

ユーザは、このETagの値をリクエストデータに含めることで、ビューおよびその裏側で同期されているリレーショナルなテーブルで管理されている最新のデータに対して更新がされることを保証できます。

実際に試してみましょう。

ここからはdriver_dvを利用します。

まずは現時点でのデータを取得してみます。

[opc@database23c ~]$ curl http://localhost:8080/ords/jrd/driver_dv/ | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1277    0  1277    0     0   4892      0 --:--:-- --:--:-- --:--:--  4892
{
  "items": [
    {
      "driverId": 105,
      "name": "George Russell",
      "points": 0,
      "teamId": 2,
      "team": "Mercedes",
      "race": [],
      "_metadata": {
        "etag": "A8BB1825F6218EC0D300671173540597",
        "asof": "00000000004FE44A"
      },
      "links": [
        {
          "rel": "self",
          "href": "http://localhost:8080/ords/jrd/driver_dv/105"
        }
      ]
    },
    {
      "driverId": 106,
      "name": "Lewis Hamilton",
      "points": 0,
      "teamId": 2,
      "team": "Mercedes",
      "race": [],
      "_metadata": {
        "etag": "D3FF3213793E306204BB5E5060368E41",
        "asof": "00000000004FE44A"
      },
      "links": [
        {
          "rel": "self",
          "href": "http://localhost:8080/ords/jrd/driver_dv/106"
        }
      ]
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 4,
  "links": [
    {
      "rel": "self",
      "href": "http://localhost:8080/ords/jrd/driver_dv/"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8080/ords/jrd/metadata-catalog/driver_dv/"
    },
    {
      "rel": "first",
      "href": "http://localhost:8080/ords/jrd/driver_dv/"
    }
  ]
}

次に以下のようなデータを作成します。
今回はdriverIdが105のデータを更新します。

その際に先ほど返却されたETagを含めます。

data_2.json
{
    "_metadata": {
        "etag": "A8BB1825F6218EC0D300671173540597"
    },
    "driverId": 105,
    "name": "Max Emilian Verstappen",
    "points": 0,
    "teamId": 2,
    "race": []
}

curl -i -X PUT --data-binary @data_2.json -H "Content-Type: application/json" http://localhost:8080/ords/jrd/driver_dv/105

HTTP/1.1 200 OKでレスポンスが返って来れば問題ありません。
これは先ほどリクエストデータに含めたETagがOracle Databaseが管理しているETagと一致している（最新のデータであることを示す）ため、問題なく更新できます。

あえて、誤ったETagで更新してみましょう

data_3.json
{
    "_metadata": {
        "etag": "xxxxxxxx"
    },
    "driverId": 105,
    "name": "Max Emilian Verstappen",
    "points": 0,
    "teamId": 2,
    "race": []
}

curl -i -X PUT --data-binary @data_3.json -H "Content-Type: application/json" http://localhost:8080/ords/jrd/driver_dv/105

HTTP/1.1 412 Precondition Failedでレスポンスが返ってきます。
エラーになったのは、Oracle Databaseが管理しているETagとリクエストデータに含まれてるETagが一致していないためです。

つまり、データを取得してからデータの更新をリクエストする間に対象データに変更があったことを示します。

このETagにより、従来のドキュメント指向データベースでは難しいと言われるデータ整合性を保証します。

最後にリレーショナルなテーブルがどのようになっているかを確認します。

[oracle@database23c ~]$ sqlplus jrd@パスワード/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Apr 14 02:16:00 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered.

SQL> select * from driver;

 DRIVER_ID
----------
NAME
--------------------------------------------------------------------------------
    POINTS    TEAM_ID
---------- ----------
       105
Max Emilian Verstappen
         0          2

       106
Lewis Hamilton
         0          2

 DRIVER_ID
----------
NAME
--------------------------------------------------------------------------------
    POINTS    TEAM_ID
---------- ----------


SQL> select * from team;

   TEAM_ID
----------
NAME
--------------------------------------------------------------------------------
    POINTS
----------
         2
Mercedes
         0


SQL>

リレーショナルなテーブルの方にもデータが反映されていますね。

まとめ

このようにJSON Relational Dualityを利用すると、ドキュメント指向データベースとリレーショナルデータベースの良いとこどりをすることができます。
このブログでご紹介したのはほんの一部ですので、興味がある方はぜひOracle Database 23cで遊んでみてください。

参考資料

  1. 「のようなもの」と書いたのは、JSON形式で格納できるものの、格納先はRDBMSのビューになるので、事前にデータ構造を決める必要があるためです（厳密には「スキーマレス」ではない） ↩ ↩2

12
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
3