APIの使用によるデータのアップサート
問合せリクエストでUPSERT SQLコマンドを使用して、データを更新または挿入できます。
問合せを実行するには、NoSQLHandle.query() APIを使用します。
こちらにあるサンプルの中からフル・コードModifyData.javaをダウンロードします。
/*Upsert data*/
private static void upsertRows(NoSQLHandle handle,String sqlstmt) throws Exception {
try (
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
QueryIterableResult results = handle.queryIterable(queryRequest)){
for (MapValue res : results) {
System.out.println("\t" + res);
}
}
}
String upsert_row = "UPSERT INTO stream_acct VALUES("+
"1,"+
"\"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\": 70,"+
"\"minWatched\": 70,"+
"\"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\": 40,"+
"\"minWatched\": 40,"+
"\"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\""+
"}"+
"]"+
"}"+
"]"+
"}"+
"]}) RETURNING *";
System.out.println("Upsert data ");
upsertRows(handle,upsert_row);問合せを実行するには、borneo.NoSQLHandle.query()メソッドを使用します。
こちらにあるサンプルの中からフル・コードModifyData.pyをダウンロードします。
#upsert data
def upsert_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
result = handle.query(request)
print('Upsert data')
for r in result.get_results():
print('\t' + str(r))upsert_row = '''
UPSERT INTO stream_acct VALUES
(
1,
"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": 75,
"minWatched": 75,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 40,
"date": "2022 - 04 - 18 "
}]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 40,
"minWatched": 30,
"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": 20,
"minWatched": 20,
"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"
}]
}]
}]
}
) RETURNING *
'''
upsert_data(handle,upsert_row) 問合せを実行するには、Client.Query関数を使用します。
こちらにあるサンプルの中からフル・コードModifyData.goをダウンロードします。
//upsert data in the table
func upsertRows(client *nosqldb.Client, err error,
tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Upsert failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
}upsert_data := `UPSERT INTO stream_acct VALUES(
1,
"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": 75,
"minWatched": 75,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 40,
"date": "2022 - 04 - 18 "
}
]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [
{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 40,
"minWatched": 30,
"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": 20,
"minWatched": 20,
"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"
}
]
}
]
}
]
}) RETURNING *`
upsertRows(client, err,tableName,upsert_data)問合せリクエストでUPSERT SQLコマンドを使用して、データを更新または挿入できます。問合せを実行するには、queryメソッドを使用します。
JavaScript: こちらにあるサンプルの中からフル・コードModifyData.jsをダウンロードします。
/*upserts data in the table*/
async function upsertData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}TypeScript: こちらにあるサンプルの中からフル・コードModifyData.tsをダウンロードします。
interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}async function upsertData(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}const upsert_row = `UPSERT INTO stream_acct VALUES
(
1,
"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": 75,
"minWatched": 75,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 40,
"date": "2022 - 04 - 18 "
}]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 40,
"minWatched": 30,
"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": 20,
"minWatched": 20,
"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"
}]
}]
}]
}) RETURNING *`
await upsertData(handle,upsert_row);
console.log("Upsert data into table");問合せリクエストでUPSERT SQLコマンドを使用して、データを更新または挿入できます。問合せを実行するには、QueryAsyncメソッドまたはGetQueryAsyncEnumerableメソッドを使用して、結果となる非同期列挙可能オブジェクトを反復処理します。
こちらにあるサンプルの中からフル・コードModifyData.csをダウンロードします。
private static async Task upsertData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
await DoQuery(queryEnumerable);
}
private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
Console.WriteLine(" Query results:");
await foreach (var result in queryEnumerable) {
foreach (var row in result.Rows)
{
Console.WriteLine();
Console.WriteLine(row.ToJsonString());
}
}
}private const string upsert_row = @"UPSERT INTO stream_acct VALUES
(
1,
""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"": 75,
""minWatched"": 75,
""date"": ""2022-04-18""
},
{
""episodeID"": 30,
""lengthMin"": 60,
""episodeName"": ""Season 1 episode 2"",
""minWatched"": 40,
""date"": ""2022 - 04 - 18""
}]
},
{
""seasonNum"": 2,
""numEpisodes"": 2,
""episodes"": [{
""episodeID"": 40,
""episodeName"": ""Season 2 episode 1"",
""lengthMin"": 40,
""minWatched"": 30,
""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"": 20,
""minWatched"": 20,
""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""
}]
}]
}]
}
) RETURNING *";
await upsertData(client,upsert_row);
Console.WriteLine("Upsert data in table");