SQL for Oracle NoSQL Databaseの開始
SQL for Oracle NoSQL Databaseへようこそ。この言語は、Oracle NoSQL Databaseを対象とするSQLに似たインタフェースを提供します。SQL for Oracle NoSQL Databaseデータ・モデルでは、フラット・リレーショナル・データ、階層型の型付けされた(スキーマあり)データおよびスキーマなしのJSONデータがサポートされます。SQL for Oracle NoSQL Databaseは、異なるサブモデル間でインピーダンス・ミスマッチが発生することなく、このようなすべてのデータをシームレスに処理するように設計されています。インピーダンス・ミスマッチとは、データベース・モデルとプログラミング言語モデルの間の差異が原因で発生する問題のことです。
前提条件: Oracle NoSQL Databaseをインストールしておきます。Oracle NoSQL Databaseの簡略化されたバージョンであるKVLiteを使用することもできます。
例で使用するスキーマ
様々なSQLの概念を学習するために、2つの異なるスキーマ(およびリアルタイム・シナリオ)を使用します。これら2つのスキーマには、Oracle NoSQL Databaseで使用できる様々なデータ型が含まれます。
スキーマ1: BaggageInfoスキーマ
このスキーマを使用すると、フライトで旅行する乗客が、最終目的地までのルートに沿って、チェックインされた持ち込み手荷物または預け手荷物の進行を追跡できるユースケースを処理できます。この機能は、航空会社のモバイル・アプリケーションの一部として利用できます。乗客がモバイル・アプリケーションにログインすると、現在のフライトのチケット番号または予約コードが画面に表示されます。乗客は、この情報を使用して手荷物情報を検索できます。モバイル・アプリケーションでは、NoSQL Databaseを使用して、手荷物に関連するすべてのデータを格納しています。バックエンドでは、モバイル・アプリケーション・ロジックにより、SQL問合せを実行して必要なデータを取得します。
スキーマ2: ストリーミング・メディア・サービス - 永続ユーザー・プロファイル・ストア
TVストリーミング・アプリケーションについて検討します。世界中の顧客が視聴する様々な番組をストリーミングしています。すべての番組にはいくつかのシーズンがあり、各シーズンには複数のエピソードがあります。TVストリーミング・アプリケーションを使用して顧客の現在のアクティビティをトラッキングする永続メタデータ・ストアが必要です。このスキーマを使用すると、顧客が視聴したエピソード、エピソードごとの視聴時間、視聴した番組の合計シーズン数などの有用な情報を顧客に提供できます。データはNoSQL Databaseに格納され、アプリケーションはSQL問合せを実行して必要なデータを取得し、ユーザーが使用できるようにします。
SQLシェルの起動
java -jar KVHOME/lib/sql.jar
-helper-hosts <host:port[,host:port]*>
-store <storeName>
[-username <user>]
[-security <security-file-path>]
[-timeout <timeout ms>]
[-consistency <NONE_REQUIRED(default) | ABSOLUTE | NONE_REQUIRED_NO_MASTER>]
[-durability <COMMIT_SYNC(default) | COMMIT_NO_SYNC | COMMIT_WRITE_NO_SYNC>]
[single command and arguments] 説明:
-consistencyは、このセッションで使用される読取り一貫性を構成します。
-durabilityは、このセッションで使用される書込み永続性を構成します。
-helper-hostsは、ホストおよびポートのカンマ区切りリストを指定します。
-storeは、ストアの名前を指定します。
-timeoutは、このセッションで使用されるリクエスト・タイムアウトを構成します。
-usernameは、ログインに使用するユーザー名を指定します。
java -jar KVHOME/lib/sql.jar -helper-hosts node01:5000 -store kvstore
sql->このコマンドでは、ストアkvstoreがポート5000で実行されていることを前提としています。SQLが正常に起動したら、問合せを実行します。
例で使用する表
表は、ユーザー・データを保持するための基本構造です。
スキーマ1: BaggageInfoスキーマ
このスキーマで使用される表はBaggageInfoです。このスキーマには、LONG、STRINGなどの固定データ型の組合せが存在します。また、列の1つとしてスキーマなしのJSON (bagInfo)も存在します。スキーマなしのJSONには固定データ型がありません。乗客の手荷物情報は、スキーマなしのJSONです。一方、チケット番号、フルネーム、性別、連絡先詳細などの乗客情報は、すべて固定スキーマの一部です。この非固定スキーマなしのJSONフィールドに任意の数のフィールドを追加できます。
CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)スキーマ2: ストリーミング・メディア・サービス - 永続ユーザー・プロファイル・ストア
このスキーマで使用される表はstream_acctです。このスキーマには、acct_idおよびuser_idで構成されるコンポジット主キー列があります。スキーマには、スキーマなしのJSON列(acct_data)も含まれます。スキーマなしのJSONには固定データ型がありません。この非固定スキーマなしのJSONフィールドに任意の数のフィールドを追加できます。
CREATE TABLE stream_acct(
acct_id INTEGER,
user_id STRING,
acct_data JSON,
PRIMARY KEY(acct_id, user_id)
)表の説明
(DESCRIBE | DESC) [AS JSON] TABLE table_name [ "(" field_name")"]出力をJSON形式にする場合、AS JSONを指定できます。フィールド名を指定して、任意の表の特定のフィールドに関する情報を取得できます。
DESCRIBE TABLE stream_acct === Information ===
+-------------+-----+-------+----------+--------+----------+---------+---------+-------------+
| name | ttl | owner | sysTable | parent | children | regions | indexes | description |
+-------------+-----+-------+----------+--------+----------+---------+---------+-------------+
| stream_acct | | | N | | | | | |
+-------------+-----+-------+----------+--------+----------+---------+---------+-------------+
=== Fields ===
+----+-----------+---------+----------+---------+----------+------------+----------+
| id | name | type | nullable | default | shardKey | primaryKey | identity |
+----+-----------+---------+----------+---------+----------+------------+----------+
| 1 | acct_id | Integer | N | NULL | Y | Y | |
+----+-----------+---------+----------+---------+----------+------------+----------+
| 2 | user_id | String | N | NULL | Y | Y | |
+----+-----------+---------+----------+---------+----------+------------+----------+
| 3 | acct_data | Json | Y | NULL | | | |
+----+-----------+---------+----------+---------+----------+------------+----------+DESC AS JSON TABLE BaggageInfo{
"json_version" : 1,
"type" : "table",
"name" : "BaggageInfo",
"fields" : [{
"name" : "ticketNo",
"type" : "LONG",
"nullable" : false
}, {
"name" : "fullName",
"type" : "STRING",
"nullable" : true
}, {
"name" : "gender",
"type" : "STRING",
"nullable" : true
}, {
"name" : "contactPhone",
"type" : "STRING",
"nullable" : true
}, {
"name" : "confNo",
"type" : "STRING",
"nullable" : true
}, {
"name" : "bagInfo",
"type" : "JSON",
"nullable" : true
}],
"primaryKey" : ["ticketNo"],
"shardKey" : ["ticketNo"]
}DESCRIBE TABLE BaggageInfo (ticketNo) +----+----------+------+----------+---------+----------+------------+----------+
| id | name | type | nullable | default | shardKey | primaryKey | identity |
+----+----------+------+----------+---------+----------+------------+----------+
| 1 | ticketNo | Long | N | NULL | Y | Y | |
+----+----------+------+----------+---------+----------+------------+----------+
問合せを実行するサンプル・データ
スキーマ1: BaggageInfoスキーマ
"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1064",
"confNo" : "LE6J4Z",
[ {
"id" : "79039899165297",
"tagNum" : "17657806255240",
"routing" : "MIA/LAX/MEL",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"flightLegs" : [ {
"flightNo" : "BM604",
"flightDate" : "2019-02-01T01:00:00",
"fltRouteSrc" : "MIA",
"fltRouteDest" : "LAX",
"estimatedArrival" : "2019-02-01T03:00:00",
"actions" : [ {
"actionAt" : "MIA",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-02-01T01:13:00"
}, {
"actionAt" : "MIA",
"actionCode" : "BagTag Scan at MIA",
"actionTime" : "2019-02-01T00:47:00"
}, {
"actionAt" : "MIA",
"actionCode" : "Checkin at MIA",
"actionTime" : "2019-02-01T23:38:00"
} ]
}, {
"flightNo" : "BM667",
"flightDate" : "2019-01-31T22:13:00",
"fltRouteSrc" : "LAX",
"fltRouteDest" : "MEL",
"estimatedArrival" : "2019-02-02T03:15:00",
"actions" : [ {
"actionAt" : "MEL",
"actionCode" : "Offload to Carousel at MEL",
"actionTime" : "2019-02-02T03:15:00"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MEL",
"actionTime" : "2019-02-01T07:35:00"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-02-01T07:18:00"
} ]
} ],
"lastSeenTimeGmt" : "2019-02-02T03:13:00",
"bagArrivalDate" : "2019.02.02T03:13:00"
} ]java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstoreloadコマンドを使用して、スクリプトを実行します。load -file baggageschema_loaddata.sqlスキーマ2: ストリーミング・メディア・サービス - 永続ユーザー・プロファイル・ストア
1,
"user01",
{
"firstName" : "John",
"lastName" : "Sanders",
"country" : "USA",
"contentStreamed": [
{
"showName": "Call My Agent",
"showId": 12,
"showType": "tvseries",
"numSeasons" : 2,
"seriesInfo": [
{
"seasonNum" : 1,
"numEpisodes" : 2,
"episodes": [
{ "episodeID" : 20, "lengthMin" : 40, "minWatched" : 40 },
{ "episodeID" : 30, "lengthMin" : 42, "minWatched" : 42 }
]
},
{
"seasonNum": 2,
"numEpisodes" : 2,
"episodes": [
{ "episodeID" : 20, "lengthMin" : 50, "minWatched" : 50 },
{ "episodeID" : 30, "lengthMin" : 46, "minWatched" : 46 }
]
}
]
},
{
"showName": "Rita",
"showId": 16,
"showType": "tvseries",
"numSeasons" : 1,
"seriesInfo": [
{
"seasonNum" : 1,
"numEpisodes" : 2,
"episodes": [
{ "episodeID" : 20, "lengthMin" : 65, "minWatched" : 65 },
{ "episodeID" : 30, "lengthMin" : 60, "minWatched" : 60 }
]
}
]
}
]
}java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstoreloadコマンドを使用して、スクリプトを実行します。load -file acctstream_loaddata.sql表階層
Oracle NoSQL Databaseでは、表を親子関係にすることができます。これを表階層といいます。
CREATE TABLE文を使用すると、表を別の表の子として作成でき、その別の表が新しい表の親になります。これは、子表のコンポジット名(name_path)を使用して実行します。コンポジット名は、ドットで区切られたN個(N > 1)の識別子で構成されます。最後の識別子は子表のローカル名であり、最初のN-1個の識別子は親の名前を示します。
- 子表は、親表の主キー列を継承します。
- 階層内のすべての表には同じシャード・キー列があり、これらはルート表のCREATE TABLE文に指定されています。
- 子が削除される前に親表を削除することはできません。
- 参照整合性制約は、親子表では適用されません。
なんらかの形式のデータ正規化が必要な場合、子表の使用を検討する必要があります。子表は、1対Nの関係をモデル化する場合にも適しており、また、親子階層に複数のレコードを記述する際にはACIDトランザクション・セマンティクスを提供します。