問合せを実行するサンプル・データ
表1: 航空会社手荷物追跡アプリケーション
例のとおりに操作する場合は、スクリプトbaggageschema_loaddata.sqlをダウンロードして、次に示すように実行します。このスクリプトにより、例で使用する表が作成され、表にデータがロードされます。次に、1つのサンプル行を示します。
"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"
} ]KVSTOREまたはKVLiteを起動し、SQLシェルを開きます。
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstorebaggageschema_loaddata.sqlには、次の内容が含まれています:### Begin Script###
load -file baggageInfo.ddl
import -table baggageInfo -file baggageData.json
### End Script ###loadコマンドを使用して、スクリプトを実行します。load -file baggageschema_loaddata.sql表2: ストリーミング・メディア・サービス - 永続ユーザー・プロファイル・ストア
スクリプトacctstream_loaddata.sqlをダウンロードして、次に示すように実行します。このスクリプトにより、例で使用する表が作成され、表にデータがロードされます。次に、1つのサンプル行を示します。
1,
123456789,
"AP",
"2023-10-18",
{
"firstName": "Adam",
"lastName": "Phillips",
"country" : "Germany",
"contentStreamed": [
{
"showName" : "At the Ranch",
"showId" : 26,
"showtype" : "tvseries",
"genres" : ["action", "crime", "spanish"],
"numSeasons" : 4,
"seriesInfo": [
{
"seasonNum" : 1,
"numEpisodes" : 2,
"episodes": [
{
"episodeID": 20,
"episodeName" : "Season 1 episode 1",
"lengthMin": 85,
"minWatched": 85,
"date" : "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName" : "Season 1 episode 2",
"minWatched": 60,
"date" : "2022-04-18"
}
]
},
{
"seasonNum": 2,
"numEpisodes" : 2,
"episodes": [
{
"episodeID": 40,
"episodeName" : "Season 2 episode 1",
"lengthMin": 50,
"minWatched": 50,
"date" : "2022-04-25"
},
{
"episodeID": 50,
"episodeName" : "Season 2 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date" : "2022-04-27"
}
]
}
]
},
{
"seasonNum": 3,
"numEpisodes" : 2,
"episodes": [
{
"episodeID": 60,
"episodeName" : "Season 3 episode 1",
"lengthMin": 50,
"minWatched": 50,
"date" : "2022-04-25"
},
{
"episodeID": 70,
"episodeName" : "Season 3 episode 2",
"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,
"episodeName" : "Bonjour",
"lengthMin": 45,
"minWatched": 45,
"date" : "2022-03-07"
},
{
"episodeID": 30,
"episodeName" : "Merci",
"lengthMin": 42,
"minWatched": 42,
"date" : "2022-03-08"
}
]
}
]
}
]
}
KVSTOREまたはKVLiteを起動し、SQLシェルを開きます。
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstoreacctstream_loaddata.sqlには、次の内容が含まれています:### Begin Script###
load -file acctstream.ddl
import -table stream_acct -file acctstreamData.json
### End Script ###loadコマンドを使用して、スクリプトを実行します。load -file acctstream_loaddata.sql表3: JSONコレクション表 - ショッピング・アプリケーション
次のコードは、ショッピング・アプリケーション表にデータを挿入します。このデータを使用して、トピックで説明されている例に従うことができます。
insert into storeAcct(contactPhone, firstName, lastName, address, cart) values("1817113382", "Adam", "Smith", {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX", "zip" : 95085}, [{"item" : "handbag", "quantity" : 1, "priceperunit" : 350},{"item" : "Lego", "quantity" : 1, "priceperunit" : 5500}]) RETURNING *;
insert into storeAcct(contactPhone, firstName, lastName, gender, address, notify, cart, wishlist) values("1917113999", "Sharon", "Willard", "F", {"street" : "Maine", "number" : 501, "city" : "San Jose", "state" : "San Francisco", "zip" : 95095},"yes", [{"item" : "wallet", "quantity" : 2, "priceperunit" : 950},{"item" : "wall art", "quantity" : 1, "priceperunit" : 9500}], [{"item" : "Tshirt", "priceperunit" : 500},{"item" : "Jenga", "priceperunit" : 850}]) RETURNING *;
insert into storeAcct(contactPhone, firstName, lastName, address, notify, cart, orders) values("1617114988", "Lorenzo", "Phil", {"Dropbox" : "Presidency College", "city" : "Kansas City", "state" : "Alabama", "zip" : 95065},"yes", [{"item" : "A4 sheets", "quantity" : 2, "priceperunit" : 500},{"item" : "Mobile Holder", "quantity" : 1, "priceperunit" : 700}], [{"orderID" : "101200", "item" : "AG Novels 1", "EstDelivery" : "2023-11-15", "priceperunit" : 950, "status" : "Preparing to dispatch"},{"orderID" : "101200", "item" : "Wallpaper", "EstDelivery" : "2023-11-01", "priceperunit" : 950, "status" : "Transit"}]) RETURNING *;
insert into storeAcct(contactPhone, firstName, lastName, address, cart, orders) values("1517113582", "Dierdre", "Amador", {"street" : "Tex Ave", "number" : 651, "city" : "Houston", "state" : "TX", "zip" : 95085}, NULL, [{"orderID" : "201200", "item" : "handbag", "EstDelivery" : "2023-11-01", "priceperunit" : 350},{"orderID" : "201201", "item" : "Lego", "EstDelivery" : "2023-11-01", "priceperunit" : 5500}]) RETURNING *;
insert into storeAcct(contactPhone, firstName, lastName, address, notify, cart, orders) values("1417114488", "Doris", "Martin", {"Dropbox" : "Presidency College", "city" : "Kansas City", "state" : "Alabama", "zip" : 95065},"yes", [{"item" : "Notebooks", "quantity" : 2, "priceperunit" : 50},{"item" : "Pens", "quantity" : 2, "priceperunit" : 50}], [{"orderID" : "301200", "item" : "Laptop Bag", "EstDelivery" : "2023-11-15", "priceperunit" : 1950, "status" : "Preparing to dispatch"},{"orderID" : "301200", "item" : "Mouse", "EstDelivery" : "2023-11-02", "priceperunit" : 950, "status" : "Transit"}]) RETURNING *;