SQLコマンドの使用

SQLでCREATE TABLEコマンドを使用して、NoSQL表を作成できます。

次の項では、CREATE TABLE DDL文を使用して表を作成する際に使用できる様々なオプションについて説明します。

例1: 航空会社システム内で乗客の手荷物情報を保持する航空会社手荷物追跡アプリケーション表を作成します。

CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)

前述のスキーマでは、CREATE TABLE文を使用してBaggageInfo表を定義しています。乗客のチケット番号ticketNoは、表の主キーです。fullName、gender、contactPhoneおよびconfNo (予約番号)フィールドには、固定スキーマの一部である乗客の情報が格納されます。bagInfo列はスキーマレスJSON配列で、乗客の受託手荷物の追跡情報を表します。

航空会社の手荷物追跡アプリケーションの詳細は、「航空会社の手荷物追跡アプリケーション」を参照してください。

例2: サブスクライバの現在のアクティビティを追跡するために、JSONフィールドを含むストリーミング・メディア・サービス表を作成します。

CREATE TABLE stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data JSON, 
PRIMARY KEY(acct_id)
)USING TTL 5 DAYS

前述のスキーマでは、CREATE TABLE文を使用してstream_acct表を作成しています。この表の主キーは、サブスクライバのアカウントIDであるacct_idフィールドです。profile_name、account_expiryフィールドには、固定スキーマの一部である視聴者の詳細が格納されます。acct_data列は、サブスクライバによって視聴された番組の詳細を格納するスキーマレスJSONフィールドです。

ストリーミング・メディア・サービス・アプリケーションの詳細は、「ストリーミング・メディア・サービス - 永続ユーザー・プロファイル・ストア」を参照してください

また、TTL値を指定すると、その期間の経過後、行が自動的に期限切れになり、使用できなくなります。TTL値はHOURSまたはDAYSのいずれかである必要があります。このスキーマでは、表の行は5日後に期限切れになります。

remaining_hours関数を使用すると、行が期限切れになるまでの残り時間を確認できます詳細は、「行の関数」を参照してください。

例3: 様々な固定スキーマ定義を含むストリーミング・メディア・サービス表を作成します。

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data RECORD (
firstName STRING,
lastName STRING,
country STRING,
shows JSON
),
PRIMARY KEY(acct_id)
)

前述のスキーマでは、acct_dataフィールドをRECORDデータ型として含めることで、固定スキーマのバリエーションを定義しています。

レコードは、1つ以上のキーと項目のペアの順序付けられたコレクションです。レコードのキーは文字列である必要があり、関連付けられた項目は異なるデータ型にすることができます。レコードのフィールドは固定スキーマの一部であるため、追加または削除できません。前述の例では、firstName、lastName、countryおよびshowsフィールドがacct_dataレコードのキーです。レコードを定義すると、より大きなデータ・セットの一部としてデータを格納する場合に役立ちます。フィールド・ステップ式を使用すると、レコード内のサブセット全体を挿入/更新/フェッチできます。

レコードを次のようにネストすることもできます:

CREATE TABLE IF NOT EXISTS  stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9), 
acct_data RECORD (
   firstName STRING,
   lastName STRING,
   country STRING,
   shows RECORD (
           showName STRING,
           showId INTEGER,
           type JSON,
           numSeasons INTEGER,
           seriesInfo ARRAY(JSON)
   )
),
PRIMARY KEY(acct_id)
)

showsフィールドは、視聴された番組の詳細を追跡するために使用されるネストされたRECORD型です。

例4: ストリーミング・メディア・サービス表を階層表構造として作成します。

次のスキーマでは、親表としてstream_acct表、および子表としてacct_data表を作成します。

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
PRIMARY KEY(acct_id)) 

CREATE TABLE IF NOT EXISTS stream_acct.acct_data(
profile_id INTEGER,
user_name STRING,
firstName STRING,
lastName  STRING,
country   STRING,
shows     JSON,
PRIMARY KEY(profile_id))

前述の親子表の定義により、ストリーミング・メディア・サービスは単一のサブスクリプションで複数のユーザー・プロファイルをサポートできます。

acct_data表は、ユーザーのプロファイルを識別するために主キーprofile_idを持つ子表として定義します。表の主キーの定義に加えて、acct_data表はその親のstream_acct表の主キー列acct_idを暗黙的に継承します。

同じstream_acct親表の下に複数の子表を定義できます。さらに、acct_data表の子表を定義できます。階層内のすべての表には、同じシャード・キー列があり、これは階層内の最上位の親表のCreate Table文で指定されます。この例では、親表の主キーacct_idは、stream_acct表およびacct_data表のシャード・キーでもあります。

例5: 主キーをIDENTITY列とするストリーミング・メディア・サービス表を作成します。

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 4 INCREMENT BY 1 NO CYCLE),
profile_name STRING,
account_expiry TIMESTAMP(9), 
acct_data JSON, 
PRIMARY KEY(acct_id)
)

この例では、例2で定義したものと同じフィールドを使用してstream_acct表を作成します。ただし、このスキーマでは主キーacct_idをIDENTITY列として定義します。IDENTITY列をGENERATED BY DEFAULT AS IDENTITYとして定義すると、IDENTITY列の値を指定しない場合、IDENTITY列の値が自動的に生成されます。acct_id値は、4から1ずつ増分して生成されます。このため、acct_id列の値は、4、5、6...のようになり、LONGデータ型の最大値まで増分されます。このスキーマではNO CYCLEオプションが定義されているため、最大値の後に例外が発生します。これは、シーケンス・ジェネレータの終わりに達したためです。

例6: 主キーをUUIDデータ型とするストリーミング・メディア・サービス表を作成します。

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id STRING AS UUID GENERATED BY DEFAULT, 
profile_name STRING,
account_expiry TIMESTAMP(9), 
acct_data JSON, 
PRIMARY KEY(acct_id)
)

この例では、例2で定義したものと同じフィールドを使用してstream_acct表を作成します。ただし、このスキーマでは、主キーacct_idをUUIDデータ型のGENERATED BY DEFAULTとして定義します。UUID列を指定しない場合、UUID列の値は自動的に生成されます。主キーをUUIDデータ型として定義する潜在的な利点は、IDENTITY列の一意性はリージョン内のNoSQLデータ・ストア内でのみ保証されますが、UUIDデータ型では複数のリージョンにまたがる表内のレコードに対してグローバルに一意の識別子が生成されることです。

MR_COUNTERデータ型

例7: MR_COUNTERデータ型を使用してストリーミング・メディア・サービス表を作成します。

CREATE TABLE IF NOT EXISTS  stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9), 
firstName STRING,
lastName STRING,
country STRING,
shows JSON (counter as INTEGER MR_COUNTER),
PRIMARY KEY(acct_id)) IN REGIONS DEN,LON

前述のスキーマでは、showsフィールドにMR_COUNTERデータ型を追加してストリーミング・メディア・サービス表を定義しています。MR_COUNTERデータ型は、競合のないレプリケートされたデータ型(CRDT)カウンタです。CRDTを使用すると、ユーザーの介入なしで、複数のリージョンにわたり同時変更をマージできます。Oracle NoSQL Databaseの複数リージョン設定では、同じデータのコピーを複数のリージョンに格納する必要があり、様々なリージョンでデータが同時に変更される場合があります。MR_COUNTERデータ型を使用すると、様々なリージョンで同時にデータ変更が実行されても、データは常に、自動的にマージされて一貫性のある状態になります。

この例では、DENとLONの2つのリージョンに表が作成されます。JSONドキュメントの各カウンタ・フィールドでは、そのリージョンでユーザーがストリーミングしている番組の最新のカウントを追跡します。参加リージョン内のすべての複数リージョン表が同期されると、これらの同時変更が自動的にマージされ、ユーザーの介入なしでcounterの最新の更新が反映されます。

JSONコレクション表

例1: ストリーミング・メディア・サービス表をJSONコレクション表として作成します。

次のCREATE TABLE文では、stream_acct表をJSONコレクション表として作成しています:

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER, 
PRIMARY KEY(acct_id)) AS JSON COLLECTION

このJSONコレクション表には、主キー・フィールドとしてacct_idが含まれています。DDLコマンドでは、主キー・フィールド以外のフィールドを指定する必要はありません。

この表にデータを挿入すると、JSONコレクション表では、acct_idフィールド以外の挿入されたフィールドが自動的にJSONフィールドとみなされます。このJSONコレクション表を使用して、TVストリーミング・データを純粋にドキュメントとして格納および取得できます。

例2: ショッピング・アプリケーション表をJSONコレクション表として作成します。

次のCREATE TABLE文では、ショッピング・アプリケーション用に作成されたJSONコレクション表であるstoreAcct表を定義しています。この表には、型文字列の主キー・フィールドとしてcontactPhoneが含まれます。

CREATE TABLE storeAcct(
contactPhone string, 
primary key(contactPhone)) AS JSON COLLECTION

データを表に挿入するには、データの挿入、変更および削除を参照してください。

表作成時の変更前イメージの有効化

書込みの変更前イメージは、DML操作によって更新または削除される前の表の行です。この機能は、ストリーム・サブスクリプションで特に役立ちます。ストリーミングの変更前イメージを使用して、データ・ストア内の表に対する書込み操作によって行われた変更またはデルタをアプリケーションで計算できます。

ENABLE BEFORE IMAGE句を使用すると、それらのイメージの変更前イメージの生成および永続ストレージが有効になります。サブスクリプション・ストリームでの変更前イメージの使用の詳細は、Streams開発者ガイドを参照してください。

例: ストリーミング・メディア・サービス表の作成時に、変更前イメージを有効にします。

次のCREATE TABLE文では、変更前イメージを有効にしてstream_acct表を作成しています。

CREATE TABLE stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data JSON, 
PRIMARY KEY(acct_id)
)USING TTL 5 DAYS ENABLE BEFORE IMAGE USING TTL 48 HOURS 

前述のCREATE TABLE文では、表のTTLと変更前イメージのTTLの両方を同時に指定しています。両方のTTL値は、相互に独立して機能します。

表TTLは5日に設定され、変更前イメージTTLは48時間に設定されます。stream_acct表で生成された変更前イメージは、ディスクに48時間格納されます。この期間が経過すると、変更前イメージは期限切れになり、ディスク領域が解放され、ストリームには表示されません。

変更前イメージのTTL定義がない場合、生成された変更前イメージは、TTL値で調整されないかぎり、24時間保持されます。

表の作成後に変更前イメージの生成を有効にするには、ALTER TABLE文をENABLE BEFORE IMAGE句とともに使用する必要があります。