[OCI]SODA with partitioning (2020/02/10)
SODA with partitioning (2020/02/10)
https://blogs.oracle.com/jsondb/soda-with-partitioning-v2
投稿者：Maxim Orgiyan
SODA のコレクションでパーティショニングを使うことの可能性についてよく質問を受けます。
今日では、SODA の "マップされたコレクション" 機能を使用して、少しの初期設定でこれを行うことができます。
通常のコレクションがSODAで作成されるとき、通常のOracleテーブルが作成されます。
このテーブルはコレクションのすべてのデータを保存します。
しかし、SODA コレクションが必要とする id 列と content 列を（少なくとも）持っている限り、
SODA コレクションを既存のテーブルにマッピングすることも可能です。
そのようなテーブルは、1つ以上のパーティショニングされたカラムを持つことができます。
今日のところ、SODA 自体はまだパーティショニングされたカラムを生成する機能を持っていませんが、ロードマップ上にはあります。
幸いなことに、この問題を回避するための良い解決策がいくつかあります。
(1) トリガを使用してパーティション列を作成します。
(2) パーティションカラムを仮想カラムとして設定します。
以下に、両方のオプションを設定する方法を示します。
トリガーを使ったパーティショニング
まず、SODAコレクションをバックアップするカスタムテーブルを作成します。
| create table MYCOL ("ID" VARCHAR2(255) not null, "CREATED_ON" timestamp default sys_extract_utc(SYSTIMESTAMP) not null, "LAST_MODIFIED" timestamp default sys_extract_utc(SYSTIMESTAMP) not null, "VERSION" varchar2(255) not null, "JSON_DOCUMENT" BLOB, "ORDER_TIMESTAMP" timestamp not null, primary key ("ID"), check ("JSON_DOCUMENT" is json format json strict)) LOB("JSON_DOCUMENT") STORE AS (CACHE) partition by range (ORDER_TIMESTAMP) (partition p0 values less than (timestamp '2019-01-01 00:00:00'), partition p1 values less than (timestamp '2020-01-01 00:00:00'), partition p2 values less than (timestamp '2021-01-01 00:00:00')) |
このカスタムテーブルはSODAによって自動的にメンテナンスされる5つのカラムを持っています（ID、CREATE_ON、LAST_MODIFIED、VERSION、JSON_DOCUMENT）。
上記のSQLは、追加のORDER_TIMESTAMPカラムを持っていることを除いて、SODAがデフォルトのコレクションのために生成する正確な'create table'ステートメントに対応しています。
この列はタイムスタンプ列であり、パーティション句で指定されたパーティショニングに使用されます。
この例では、範囲パーティショニングを使用していますが、他のパーティショニングスキームを使用することもできます。
次に、ORDER_TIMESTAMP パーティション列を入力するトリガーを設定してみましょう。
| CREATE OR REPLACE TRIGGER MY_TEST_TRG BEFORE INSERT or UPDATE on "MYCOL" FOR EACH ROW begin :new.ORDER_TIMESTAMP := Json_object_T.parse(:NEW.JSON_DOCUMENT).get('timestamp').to_timestamp; end; |
この例では、（Java、node.js、python などの SODA ドライバを使用して）SODA コレクションに書き込まれたドキュメントが "タイムスタンプ" フィールドを含むと仮定します。
私たちが作成したトリガーは、JSON PLSQL オブジェクトタイプパッケージを使用して、
SODA で発生するすべてのドキュメントの挿入や更新操作で、タイムスタンプフィールドを ORDER_TIMESTAMP パーティション列に抽出します。
もちろん、JSONの "timestamp "フィールドに別の名前を選ぶこともできます。そして、それに応じてトリガーの get('timestamp') 関数を調整します。
オプションとして、行の移動を許可したい場合があります（例えば、SODA の更新の結果として、行を別のパーティションに移動させたい場合など）。
| alter table mycol enable row movement; |
次に、このトリガーアプローチの代わりに仮想カラムを使用する方法を説明します。
その後、作成したテーブルの上にマップされたコレクションを作成する方法を示し、全体が正しく動作することを検証します。
仮想カラムを使ったパーティショニング
あるいは、仮想カラムをパーティショニングカラムとして使用することもできます。テーブルの定義は以下のようになります。
| create table MYCOL ("ID" VARCHAR2(255) not null, "CREATED_ON" timestamp default sys_extract_utc(SYSTIMESTAMP) not null, "LAST_MODIFIED" timestamp default sys_extract_utc(SYSTIMESTAMP) not null, "VERSION" varchar2(255) not null, "JSON_DOCUMENT" BLOB, "ORDER_TIMESTAMP" timestamp generated always as (json_value("JSON_DOCUMENT", '$.timestamp' returning timestamp)) not null, primary key ("ID"), check ("JSON_DOCUMENT" is json format json strict)) LOB("JSON_DOCUMENT") STORE AS (CACHE) partition by range(ORDER_TIMESTAMP) (partition p0 values less than (timestamp '2019-01-01 00:00:00'), partition p1 values less than (timestamp '2020-01-01 00:00:00'), partition p2 values less than (timestamp '2021-01-01 00:00:00')) |
| json_value("JSON_DOCUMENT", '$.timestamp' returning timestamp)) not null |
この式は、SODAを介して書き込まれたJSONドキュメント内のタイムスタンプフィールドの値を抽出します。
このテーブルで使用される範囲分割スキームは、上記のトリガーベースのアプローチで使用したものと同じです。
もちろん、ここでも他のパーティショニング・スキームを使用することができます。
マップされたコレクションの作成と動作確認
トリガーによって、または仮想カラムとしてのパーティションカラムを持つカスタムコレクションテーブルを作成したので、
このテーブルの上にマッピングされたSODAコレクションを作成します。
これは、どのSODAインプリメンテーションからでも行うことができます。ここでは、PLSQL から行う方法を説明します。
| declare METADATA varchar2(8000); COL SODA_COLLECTION_T; begin METADATA := '{"tableName" : "MYCOL", "keyColumn":{"name":"ID", "sqlType":"VARCHAR2", "maxLength":255, "assignmentMethod":"UUID"}, "contentColumn":{"name":"JSON_DOCUMENT", "sqlType":"BLOB", "compress":"NONE", "cache":true, "encrypt":"NONE", "validation":"STANDARD"}, "versionColumn":{"name":"VERSION", "method":"SHA256"}, "lastModifiedColumn":{"name":"LAST_MODIFIED"}, "creationTimeColumn":{"name":"CREATED_ON"}, "readOnly":false}'; -- Create a collection using "map" mode, based on the table we've created above and specified in -- the custom metadata under "tableName" field. COL := dbms_soda.create_collection('MYCOL', METADATA, DBMS_SODA.CREATE_MODE_MAP); end; |
ここでは、5 つの列を持つ基礎となるテーブルを記述するカスタム・メタデータを create_collection メソッドに提供しています。
テーブル名は、メタデータの "tableName" フィールドの下で指定されていることに注意してください。
これで、タイムスタンプフィールドを持つドキュメント、
例えば {"order" : "coffee", "timestamp" : "2020-01-01T01:01:01"} を SODA の挿入または置換操作に供給することができ、
パーティショニングカラムは基礎となるトリガーによって入力されます。
例えば、このようなドキュメントを挿入する SODA Java プログラムのコードスニペットを以下に示します。
| OracleRDBMSClient cl = new OracleRDBMSClient(); // JDBC connection Connection conn = ...; OracleDatabase db = cl.getDatabase(conn); // Open the collection we've created above OracleCollection col = db.openCollection("MYCOL"); // Insert a document with a timestamp field (note: value must be in ISO8601 format! OracleDocument d = db.createDocumentFromString("{\"order\" : \"coffee\", \"timestamp\" : \"2017-01-01T00:00:00\"}"); col.insertAndGet(d); |
JSON のタイムスタンプ値は ISO8601 フォーマットでなければならないことに注意してください。
これは、トリガー アプローチのタイムスタンプ パーティション列にこの値を抽出するために使用される PLSQL オブジェクト タイプと、
仮想列アプローチで使用される json_value によって要求されます。
この Java コードが実行された後、基礎となる mycol テーブルが ORDER_TIMESTAMP カラムに値を持っていることを確認することができます。
SQL> select order_timestamp from mycol;
ORDER_TIMESTAMP
---------------------------------------------------------------------------
01-JAN-20 12.00.00.000000 AM
SODA QBEs (query-by-example)では、パーティションカラムはまだ使用できません。
これは我々のロードマップにあります。しかし、上に示したようなSODAコレクションをバックアップするテーブルでは、
パーティションをドロップするなど、通常のSQLパーティション操作を行うことができます。
もちろん、そのようなテーブル上で SQL クエリ（SQL/JSON 演算子の有無に関わらず）を実行することができますし、
通常のように、そのようなクエリはパーティションカラムを利用することができます。
もう一つ注意すべきことがあります。
通常の、マップされていないコレクションをドロップするときは、決して SQL からテーブルを直接ドロップしてはいけません。
代わりに、SODA API を使用してコレクションをドロップします。例えば、PLSQL から。
| select dbms_soda.drop_collection('myCollectionName') from dual; |
これは、基礎となるテーブルも削除します。
しかし、マッピングされたコレクションをドロップした場合、SODA API からコレクションをドロップした後に、基礎となるテーブルは残ります。
したがって、マッピングされたコレクションについて、基礎となるテーブルを削除したい場合、
SQL を使用して明示的に、SODA API からマッピングされたコレクションを削除した後に *after*、削除しなければなりません。
なぜなら、ユーザーが削除したくないかもしれない既存のテーブルである可能性があるからです
（通常の、マップされていないコレクションの場合、SODA 自体によって作成されたテーブルとは対照的です）。
コメント
コメントを投稿