CREATE TABLE文

表は、ユーザー・データを保持するための基本構造です。create table文を使用して、Oracle NoSQL Databaseで新しい表を作成します。

構文

create_table_statement ::= 
   CREATE TABLE [IF NOT EXISTS] table_name [comment] 
   "(" table_definition ")" [ttl_definition] [json_collection_definition]

table_name ::= [namespace_name ":"] name_path
name_path ::= field_name ("." field_name)*
field_name ::= id | DSTRING

table_definition ::= 
   (column_definition | key_definition) 
   ("," (column_definition | key_definition))*
column_definition ::= 
   id type_definition 
   [default_definition | identity_definition | 
uuid_definition | mr_counter_definition] 
   [comment]
key_definition ::= 
   PRIMARY KEY 
   "(" [shard_key_definition [","]] [id_list_with_size] ")" 
   [ttl_definition]
id_list_with_size ::= id_with_size ("," id_with_size)*
id_with_size ::= id [storage_size]
storage_size ::= "(" INT_CONSTANT ")"
shard_key_definition ::= SHARD "(" id_list_with_size ")"
ttl_definition ::= USING TTL INT_CONSTANT (HOURS | DAYS)
region_definition ::= IN REGIONS region-name-1,region-name-2 [,...]
json_collection_definition ::= AS JSON COLLECTION

セマンティクス

table_name

表名は、オプションのnamespace_nameおよびlocal_nameとして指定されます。ローカル名はname_pathです。これは、子表の場合はドット区切りidのリストで構成されるためです。子表については、表階層の項で説明します。namespace_nameを含むtable_nameは、修飾表名と呼ばれます。SQL文(DDLまたはDML)がローカル名のみで表を参照する場合、ローカル名は特定の名前空間名を持つ修飾名に内部的に解決されます。ネームスペース管理の章を参照してください。

IF NOT EXISTS

これはオプションの句です。この句が指定されており、同じ修飾名の表が存在する(または作成中の)場合、その既存の表の構造が文の構造と同じであれば、エラーは生成されません。それ以外のすべての場合で、同じ修飾名の表が存在する場合、create table文は表が存在することを示すエラーを生成します。

ttl_definition

存続時間(TTL)値は、行の有効期限を計算するために使用されます。期限切れになった行は問合せ結果に含まれず、最終的にOracle NoSQL Databaseによって表から自動的に削除されます。表の作成時にTTL値を指定すると、この表に挿入されるすべての行のデフォルトのTTLとして適用されます。ただし、表挿入APIを使用してTTL値を指定すると、表レベルのTTLをオーバーライドできます。

行の有効期限は、TTL値を現在のタイムスタンプに加えて算出されます。具体的には、N時間/日のTTL値では、有効期限は現在の時刻(UTC)にN時間/日を加えたもので、次の時間/日に切り上げられます。たとえば、現在のタイムスタンプが2020-06-23T10:01:36.096でTTLが4日の場合、有効期限は2020-06-28T00:00:00.000になります。ゼロを特別な値として使用して、行が期限切れにならないことを示すことができます。CREATE TABLE文にTTLの指定がない場合、デフォルトの表TTLはゼロです。

TTL値が定義されているMR表の場合、他のリージョンにレプリケートされた行は、行が書き込まれたときの有効期限を保持します。これは、デフォルトの表レベルTTL値またはアプリケーションで設定された行レベルのオーバーライドのいずれかです。したがって、この行は、レプリケートされた時期に関係なく、すべてのリージョンで同時に期限切れになります。ただし、いずれかのリージョンで行が更新され、いずれかのリモート・リージョンにレプリケートされる前であっても、ローカル・リージョンで期限切れになる場合、この行は、そのリモート・リージョンでレプリケートおよびコミットされるとすぐに期限切れになります。

json_collection_definition

json_collection_definitionは、表をドキュメントのコレクションとして宣言します。JSONコレクション表は、ドキュメントを格納、更新および問合せするための便利な方法です。JSONコレクション表には、表の作成時に主キーを含める必要があります。表をマルチリージョン表にする場合は、MR_COUNTERSを使用してJSONコレクション表を作成できます。JSONコレクション表の詳細は、「JSONコレクション表の使用」を参照してください。

region_definition

これはオプションの句です。作成される表がMR表である場合、このパラメータによって表がまたがるすべてのリージョンがリストされます。表をMR表として作成するには、この句に少なくとも1つのリモート・リージョンを指定する必要があります。MR表の詳細は、MR表のライフ・サイクルを参照してください。

ノート:

MR表の子表の作成中にこの句を指定すると、エラーが発生します。

table_definition

文のtable_definition部分には、少なくとも1つのフィールド定義と、1つの主キー定義が含まれている必要があります(構文では複数のkey_definitionsを使用できますが、問合せプロセッサにより1つのkey_definitionルールが適用されます。構文ではこの用法で、キー定義をフィールド定義のどこにでも表示できます)。

column_definition

列定義の構文は、レコード・タイプのフィールドを定義するfield_definition構文規則に似ています。データ型の定義の項を参照してください。これは、列の名前、データ型、列がNULL値可能かどうか、オプションのデフォルト値、または列がIDENTITY列かどうか、およびオプションのコメントを指定します。表管理の項で説明したように、表はレコードのコンテナであり、table_definitionsは、リストされたcolumn_definitionsによってフィールドが定義されるレコード・タイプ(表スキーマ)の暗黙的な定義として機能します。ただし、DDL文でtype_definition構文規則が使用される場合、使用できるワイルドカードのタイプはJSON型のみです。そのため、たとえば、JSON型の列を持つ表は作成できますが、ANY型の列を持つ表は作成できません。

identity_definition

identity_definitionは、アイデンティティ列の名前を指定します。表ごとに1つのアイデンティティ列のみが許可されます。IDENTITY列の使用の項を参照してください。

uuid_definition

uuid_definitionは、列の型をUUID型として宣言します。UUIDデータ型の使用を参照してください。

mr_counter_definition

mr_counter_definitionパラメータは、列の型をMR_COUNTERデータ型として宣言します。このデータ型は、複数リージョン表でのみ使用できます。「複数リージョン表でのCRDTデータ型の使用」を参照してください

key_definition

主キー仕様の構文(key_definition)は、表の主キー列をフィールド名の順序付きリストとして指定します。列名はfield_definitionsに表示される列名の中に含まれている必要があり、関連付けられている型は、数値型、文字列、列挙またはタイムスタンプのいずれかである必要があります。主キーの通常の定義が適用されます。同じ表の2つの行が、そのすべての主キー列に同じ値を持つことはできません。

shard_key_definition

key_definitionでは、表のシャード・キー列に加えて、最初のN個の主キー列が指定されます。0 < N <= Mで、Mは主キー列の数です。shardキーの指定はオプションです。デフォルトでは、ルート表(親のない表)の場合、シャード・キーは主キー全体です。意味的には、シャード・キーは、Oracle NoSQL Databaseストアを構成する複数のサーバーとプロセスに表の行を分散するために使用されます。簡単に言うと、同じシャード・キーを持つ2つの行、つまり、シャード・キー列の同じ値は、常に同じサーバーに配置され、同じプロセスで管理されます。Oracle NoSQL Databaseでのデータの分散の詳細は、主キーとシャード・キーの設計の項を参照してください。

storage_size

INTEGER型の主キー・フィールドの追加プロパティは、記憶域サイズです。これは1から5の整数として指定されます(構文では任意の整数が許可されますが、問合せプロセッサにより制限が適用されます)。記憶域サイズは、関連付けられた主キー列の値をシリアル化された形式で格納するために使用できる最大バイト数を指定します。値を指定されたバイト数以下にシリアル化できない場合、エラーがスローされます。INTEGER (およびLONG)主キー値を格納するために内部エンコーディングが使用されるため、そのような値は文字列としてソートできます(主キー値は常に主Bツリー索引のキーとして格納されるため)。次の表に、バイト・サイズごとに格納できる正の値の範囲を示します(負の値も範囲は同じです)。ユーザーは、キー値が選択した記憶域サイズに関連付けられた範囲の上限以下になることがわかっている場合、5未満の記憶域サイズを指定して記憶域スペースを節約できます。

comment

コメントは表レベルで含まれ、解釈されないテキストとして表のメタデータの一部になります。コメントは、describe文の出力に表示されます。

例で使用する表

例5-1 ユーザー・データ・アプリケーション表

次のcreate table文は、ユーザーに関する情報を保持するusers表を定義します。

CREATE TABLE users( id INTEGER,
                     firstName STRING,
                     lastName STRING,
                     otherNames ARRAY(RECORD(first STRING, last STRING)),
                     age INTEGER,
                     income INTEGER,
                     address JSON,
                     connections ARRAY(INTEGER),
                     hobbies ARRAY(STRING),
PRIMARY KEY (id) )

上で定義されているusers表の行は、ユーザーに関する情報を表します。このようなユーザーごとに、otherNamesフィールドはレコードの配列であり、配列内の各レコードにはfirstおよびlast名前フィールドが含まれます。connectionsフィールドは、このユーザーが接続している他のユーザーのIDを含む整数の配列です。配列のIDは、接続の強さのなんらかの測定でソートされることを前提としています。hobbiesフィールドは、様々なアクティビティにおけるユーザーの関心を含む文字列の配列です。address列は、スキーマレスJSONフィールドです。

たとえば、addressフィールドの一般的な値には、次の属性を含めることができます:

{
    "street" : "Pacific Ave",
    "number" : 101,
    "city"   : "Santa Cruz",
    "state"  : "CA",
    "zip"    : 95008,
    "phones" : [
            { "area" : 408, "number" : 4538955, "kind" : "work" },
            { "area" : 831, "number" : 7533341, "kind" : "home" }
    ]
}

JSONフィールドに任意の数の属性を追加できます。JSONフィールドには固定データ型がありません。

一部の住所には、追加のフィールドがある場合や、フィールドがない場合、またはフィールドのスペルが異なる場合があります。phonesフィールドがJSONオブジェクトの配列ではなく、単一のそのようなオブジェクトである場合があります。住所全体が1つの文字列、数値またはJNULLである場合があります。

サポートされているデータ型の詳細は、Oracle NoSQL Databaseデータ・モデルを参照してください。

例5-2 航空会社手荷物追跡アプリケーション

次のCREATE TABLE文では、航空会社システムでの乗客の受託手荷物情報を保持する、BaggageInfo表を定義しています。

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

このスキーマを使用すると、フライトで旅行する乗客が、最終目的地までのルートに沿って、チェックインされた持ち込み荷物または預け荷物の位置を追跡できるユースケースを処理できます。この機能は、航空会社のモバイル・アプリケーションの一部として提供できます。乗客がモバイル・アプリケーションにログインすると、現在のフライトのチケット番号または予約コードが画面に表示されます。乗客は、この情報を使用して各自の受託手荷物情報を追跡できます。モバイル・アプリケーションでは、Oracle NoSQL Databaseを使用して、荷物に関連するすべてのデータを格納しています。バックエンドでは、モバイル・アプリケーション・ロジックにより、SQL問合せを使用して必要なデータを取得します。

このスキーマは、乗客情報を保持するためにlongやstringなどのアトミック・データ型を含む列を持つBaggageInfo表を作成します。乗客の受託手荷物情報は、スキーマレスJSONとして格納されます。このスキーマレスJSONフィールドには任意の数の属性を追加できます。一方、チケット番号、フルネーム、性別、連絡先詳細などの乗客情報は、すべて固定スキーマの一部です。

この表を作成するには、TTL値を指定します。その後、行は自動的に期限切れになり、使用できなくなります。TTL値の後にHOURSまたはDAYSが続きます。このスキーマでは、表の行は5日後に期限切れになります。remaining_days組込み関数を使用して、行が期限切れになるまでの残り時間を確認できます。

表の作成時にTTL値を指定することはオプションです。TTL値を指定しない場合、表の行の有効期限は無限になります。

例5-3 ストリーミング・メディア・サービス

次のCREATE TABLE文は、TVストリーミング・アプリケーションを作成するstream_acct表を定義します。

CREATE TABLE stream_acct(
acct_id INTEGER,
profile_id INTEGER,
profile_name STRING,
acct_data JSON, 
PRIMARY KEY(acct_id)
)

ストリーミング・メディア・サービスは、世界中の顧客が視聴する様々な番組をストリーミングしています。すべての番組にはいくつかのシーズンがあり、各シーズンには複数のエピソードがあります。TVストリーミング・アプリケーションを使用して顧客の現在のアクティビティをトラッキングする永続メタデータ・ストアが必要です。このスキーマを使用すると、顧客が観ていたエピソード、エピソードごとの視聴時間、観ていた番組のシーズンの合計など、有用な情報を顧客に提供できます。データはOracle NoSQL Databaseに格納され、アプリケーションはSQL問合せを実行して必要なデータを取得し、ユーザーに提供します。

このスキーマには、ユーザーのアカウントIDを含む主キー列としてacct_idがあります。スキーマには、profile_id、profile_name、スキーマレスJSON列(acct_data)などの他のフィールドも含まれます。スキーマなしのJSONには固定データ型がありません。このJSONフィールドには任意の数の属性を追加できます。

表へのデータのロード

次の文を使用して、データをユーザー・データ(users表)に挿入します。

INSERT INTO users VALUES ( 

   10,   
   "John",
   "Smith",
   [ {"first" : "Johny", "last" : "Good"}, {"first" : "Johny2", "last" : "Brave"},{"first" : "Johny3", "last" : "Kind"},{"first" : "Johny4", "last" : "Humble"} ],
   22,
   45000,
   {
      "street" : "Pacific Ave",
      "number" : 101,
      "city" : "Santa Cruz",
      "state" : "CA",
      "zip" : 95008,
      "phones" : [
         { "area" : 408, "number" : 4538955, "kind" : "work" },
         { "area" : 831, "number" : 7533341, "kind" : "home" },
         { "area" : 831, "number" : 7533382, "kind" : "mobile" }
      ]
   },
   [ 30, 55, 43 ],
   [ "Reading", "Soccer", "Hiking", "Gardening"]
)

INSERT INTO users VALUES ( 

   20,   
   "Jane",
   "Smith",
   [ {"first" : "Jane", "last" : "Charming"} ],
   22,
   55000,
   {
      "street" : "Atlantic Ave",
      "number" : 201,
      "city" : "San Jose",
      "state" : "CA",
      "zip" : 95005,
      "phones" : [
         { "area" : 608, "number" : 6538955, "kind" : "work" },
         { "area" : 931, "number" : 9533341, "kind" : "home" },
         { "area" : 931, "number" : 9533382, "kind" : "mobile" }
      ]
   },
   [ 40, 75, 63 ],
   [ "Knitting", "Hiking", "Baking", "BingeWatching"]
)

INSERT INTO users VALUES ( 

   30,   
   "Adam",
   "Smith",
   [ {"first" : "Adam", "last" : "BeGood"} ],
   45,
   75000,
   {
      "street" : "Indian Ave",
      "number" : 301,
      "city" : "Houston",
      "state" : "TX",
      "zip" : 95075,
      "phones" : [
         { "area" : 618, "number" : 6618955, "kind" : "work" },
         { "area" : 951, "number" : 9613341, "kind" : "home" },
         { "area" : 981, "number" : 9613382, "kind" : "mobile" }
      ]
   },
   [ 60, 45, 73 ],
   [ "Soccer", "Riding", "PianoForte", "RockClimbing", "Sketching" ]
)

INSERT INTO users VALUES ( 

   40,   
   "Joanna",
   "Smith",
   [ {"first" : "Joanna", "last" : "Smart"} ],
   NULL,
   75000,
   {
      "street" : "Tex Ave",
      "number" : 401,
      "city" : "Houston",
      "state" : "TX",
      "zip" : 95085,
      "phones" : [
         { "area" : NULL, "number" : 1618955, "kind" : "work" },
         { "area" : 451, "number" : 4613341, "kind" : "home" },
         { "area" : 481, "number" : 4613382, "kind" : "mobile" }
      ]
   },
   [ 70, 30, 40 ],
   ["Soccer", "Riding", "PianoForte", "RockClimbing", "Sketching"]
)

航空会社手荷物追跡アプリケーションおよびTVストリーミング・アプリケーションのための表(サンプル)を作成するには、スクリプトbaggageschema_loaddata.sqlおよびacctstream_loaddata.sqlをダウンロードします。

KVSTOREまたはKVLiteを起動し、SQL.shellを開きます。

java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

loadコマンドを使用して、必要なスクリプトを実行します。

load -file baggageschema_loaddata.sql
load -file acctstream_loaddata.sql

これにより、例で使用する表が作成され、その表にデータがロードされます。

航空会社手荷物追跡アプリケーションからの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"
  } ]

ストリーミング・メディア・サービスの1つのサンプル行(stream_acct)を次に示します。

"acct_id" : 1,
"profile_id" : 101,
"profile_name" : "Adams",
[ {
   "firstName" : "Adam",
   "lastName" : "Phillips",
   "country" : "USA",
   "contentStreamed":  [
      {
        "showName" : "At the Ranch",
        "showId" : 26,
        "showtype" : "tvseries",
        "genres" : ["action", "crime", "spanish"], 
        "numSeasons" : 4,
        "seriesInfo": [
          {
            "seasonNum" : 1,
            "numEpisodes" : 2,
            "episodes": [
              {
                "episodeID": 20,
                "lengthMin": 85,
                "minWatched": 85,
                "date" : "2022-04-18"
              },
              {
                "episodeID": 30,
                "lengthMin": 60,
                "minWatched": 60,
                "date" : "2022-04-18"
              }
            ]
          },
          {
            "seasonNum": 2,
            "numEpisodes" : 4,
            "episodes": [
              {
                "episodeID": 40,
                "lengthMin": 50,
                "minWatched": 50,
                "date" : "2022-04-25"
              },
              {
                "episodeID": 50,
                "lengthMin": 45,
                "minWatched": 30,
                "date" : "2022-04-27"
              }
            ]
          }
        ]
      },
      {
        "showName": "Bienvenu",
        "showId": 15,
        "showtype": "tvseries",
        "genres" : ["comedy", "french"], 
        "numSeasons" : 2,
        "seriesInfo": [
          {
            "seasonNum" : 1,
            "numEpisodes" : 2,
            "episodes": [
              {
                "episodeID": 20,
                "lengthMin": 45,
                "minWatched": 45,
                "date" : "2022-03-07"
              },
              {
                "episodeID": 30,
                "lengthMin": 42,
                "minWatched": 42,
                "date" : "2022-03-08"
              }
            ]
          }
        ]
      }
    ]
  } ]