이 페이지에서는 DML 문을 사용하여 Spanner 데이터를 삽입, 업데이트, 삭제하는 방법을 설명합니다. 클라이언트 라이브러리, Google Cloud 콘솔, gcloud 명령줄 도구를 사용하여 DML 문을 실행할 수 있습니다. 클라이언트 라이브러리와 gcloud 명령줄 도구를 사용하여 Partitioned DML 문을 실행할 수 있습니다.
전체 DML 구문 참조는 GoogleSQL 언어 데이터베이스의 DML 구문 또는 PostgreSQL 언어 데이터베이스의 PostgreSQL DML을 참조하세요.
DML 사용
DML은 Google Cloud 콘솔, Google Cloud CLI, 클라이언트 라이브러리에서INSERT, UPDATE, DELETE 문을 지원합니다.
잠금
읽기-쓰기 트랜잭션 내에서 DML 문을 실행합니다. Spanner에서 데이터를 읽을 때 읽은 행 범위의 제한된 부분에서 공유 읽기 잠금을 획득합니다. 구체적으로는 액세스하는 열에만 이러한 잠금을 획득합니다. 잠금에는 WHERE 절의 필터 조건을 충족하지 않는 데이터가 포함될 수 있습니다.
Spanner에서 DML 문을 사용하여 데이터를 수정하면 수정 중인 특정 데이터에서 배타적인 잠금을 획득합니다. 또한 데이터를 읽을 때와 같은 방법으로 공유 잠금을 획득합니다. 요청에 큰 행 범위나 전체 테이블이 포함된 경우 공유 잠금으로 인해 다른 트랜잭션을 동시에 진행하지 못할 수 있습니다.
데이터를 최대한 효율적으로 수정하려면 Spanner에서 필요한 행만 읽을 수 있도록 하는 WHERE 절을 사용합니다. 이러한 경우 기본 키 또는 보조 색인 키의 필터를 사용하면 됩니다. WHERE 절은 공유 잠금 범위를 제한하고 Spanner에서 업데이트를 더욱 효율적으로 처리할 수 있게 합니다.
예를 들어, Singers 테이블의 뮤지션 중 한 명이 이름을 변경하는 경우 데이터베이스에서 해당 이름을 업데이트해야 합니다. 다음 DML 문을 실행할 수 있지만 이 문은 Spanner가 전체 테이블을 강제로 스캔하도록 하며 전체 테이블을 포함하는 공유 잠금을 획득합니다. 그 결과 Spanner에서 데이터를 필요 이상으로 읽어야 하며 동시 트랜잭션은 데이터를 동시에 수정할 수 없습니다.
-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN
-- IN THE WHERE CLAUSE
UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards";
업데이트를 더 효율적으로 수행하려면 WHERE 절에 SingerId 열을 포함합니다. SingerId 열은 Singers 테이블의 유일한 기본 키 열입니다.
-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE
UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards"
FirstName 또는 LastName에 색인이 없는 경우 전체 테이블을 스캔하여 대상 가수를 찾아야 합니다. 업데이트를 더 효율적으로 만들기 위해 보조 색인을 추가하지 않으려면 WHERE 절에 SingerId 열을 포함합니다.
SingerId 열은 Singers 테이블의 유일한 기본 키 열입니다. 이를 찾으려면 업데이트 트랜잭션 전에 별도의 읽기 전용 트랜잭션에서 SELECT를 실행합니다.
SELECT SingerId
FROM Singers
WHERE FirstName = "Marc" AND LastName = "Richards"
-- Recommended: Including a seekable filter in the where clause
UPDATE Singers SET FirstName = "Marcel"
WHERE SingerId = 1;
동시 실행
Spanner는 모든 SQL 문(SELECT, INSERT, UPDATE, DELETE)을 한 트랜잭션 내에서 순차적으로 실행합니다. 이들은 동시에 실행되지 않습니다. 유일한 예외는 Spanner에서 SELECT 문 여러 개를 동시에 실행할 수 있다는 점이며 이러한 문이 읽기 전용 작업이기 때문입니다.
트랜잭션 제한
DML 문이 포함된 트랜잭션에는 다른 트랜잭션과 동일한 제한이 있습니다. 대규모 변경 사항이 있는 경우, Partitioned DML 사용을 고려해 보세요.
트랜잭션의 DML 문으로 인해 80,000개 이상의 변형이 발생하는 경우, 트랜잭션이 제한을 초과하도록 하는 DML 문은 너무 많은 변형에 대한 메시지와 함께
BadUsage오류를 반환합니다.트랜잭션의 DML 문으로 인해 100MB보다 큰 트랜잭션이 발생하는 경우, 트랜잭션이 제한을 초과하도록 하는 DML 문은 트랜잭션의 크기 제한 초과에 대한 메시지와 함께
BadUsage오류를 반환합니다.
DML을 사용하여 수행된 변형은 클라이언트에 반환되지 않습니다. 이들은 커밋 요청이 커밋될 때 커밋 요청에 병합되며, 최대 크기 제한에 합산되지 않습니다. 전송하는 커밋 요청의 크기가 작더라도 트랜잭션이 허용 크기 제한을 초과할 수 있습니다.
Google Cloud 콘솔에서 문 실행
Google Cloud 콘솔에서 DML 문을 실행하려면 다음 단계를 수행합니다.
Spanner 인스턴스 페이지로 이동합니다.
툴바의 드롭다운 목록에서 프로젝트를 선택합니다.
데이터베이스가 포함된 인스턴스의 이름을 클릭하여 인스턴스 세부정보 페이지로 이동합니다.
개요 탭에서 데이터베이스의 이름을 클릭합니다. 데이터베이스 세부정보 페이지가 나타납니다.
Spanner 스튜디오를 클릭합니다.
DML 문을 입력합니다. 예를 들어 다음 문은
Singers테이블에 새 행을 추가합니다.INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')쿼리 실행을 클릭합니다. Google Cloud 콘솔에 결과가 표시됩니다.
Google Cloud CLI로 문 실행
DML 문을 실행하려면 gcloud spanner databases execute-sql 명령어를 사용합니다. 다음 예시에서는 Singers 테이블에 새 행을 추가합니다.
gcloud spanner databases execute-sql example-db --instance=test-instance \
--sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"
클라이언트 라이브러리를 사용하여 데이터 수정
클라이언트 라이브러리를 사용하여 DML 문을 실행하려면 다음을 수행합니다.
- 읽기-쓰기 트랜잭션을 만듭니다.
- DML 실행을 위해 클라이언트 라이브러리 메서드를 호출하고 DML 문을 전달합니다.
- DML 실행 메서드의 반환 값을 사용하여 삽입, 업데이트 또는 삭제된 행 수를 가져옵니다.
다음 예시 코드는 Singers 테이블에 새 행을 삽입합니다.
C++
ExecuteDml() 함수를 사용하여 DML 문을 실행합니다.
void DmlStandardInsert(google::cloud::spanner::Client client) {
using ::google::cloud::StatusOr;
namespace spanner = ::google::cloud::spanner;
std::int64_t rows_inserted;
auto commit_result = client.Commit(
[&client, &rows_inserted](
spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
auto insert = client.ExecuteDml(
std::move(txn),
spanner::SqlStatement(
"INSERT INTO Singers (SingerId, FirstName, LastName)"
" VALUES (10, 'Virginia', 'Watson')"));
if (!insert) return std::move(insert).status();
rows_inserted = insert->RowsModified();
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Rows inserted: " << rows_inserted;
std::cout << "Insert was successful [spanner_dml_standard_insert]\n";
}C#
ExecuteNonQueryAsync() 메서드를 사용하여 DML 문을 실행합니다.
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class InsertUsingDmlCoreAsyncSample
{
public async Task<int> InsertUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var cmd = connection.CreateDmlCommand("INSERT Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) inserted...");
return rowCount;
}
}Go
Update() 메서드를 사용하여 DML 문을 실행합니다.
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func insertUsingDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `INSERT Singers (SingerId, FirstName, LastName)
VALUES (10, 'Virginia', 'Watson')`,
}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
return nil
})
return err
}
Java
executeUpdate() 메서드를 사용하여 DML 문을 실행합니다.
static void insertUsingDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
String sql =
"INSERT INTO Singers (SingerId, FirstName, LastName) "
+ " VALUES (10, 'Virginia', 'Watson')";
long rowCount = transaction.executeUpdate(Statement.of(sql));
System.out.printf("%d record inserted.\n", rowCount);
return null;
});
}Node.js
runUpdate() 메서드를 사용하여 DML 문을 실행합니다.
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rowCount] = await transaction.runUpdate({
sql: 'INSERT Singers (SingerId, FirstName, LastName) VALUES (10, @firstName, @lastName)',
params: {
firstName: 'Virginia',
lastName: 'Watson',
},
});
console.log(
`Successfully inserted ${rowCount} record into the Singers table.`
);
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});PHP
executeUpdate() 메서드를 사용하여 DML 문을 실행합니다.
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
* Inserts sample data into the given database with a DML statement.
*
* The database and table must already exist and can be created using
* `create_database`.
* Example:
* ```
* insert_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function insert_data_with_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$rowCount = $t->executeUpdate(
'INSERT Singers (SingerId, FirstName, LastName) '
. " VALUES (10, 'Virginia', 'Watson')");
$t->commit();
printf('Inserted %d row(s).' . PHP_EOL, $rowCount);
});
}Python
execute_update() 메서드를 사용하여 DML 문을 실행합니다.
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def insert_singers(transaction):
row_ct = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) "
" VALUES (10, 'Virginia', 'Watson')"
)
print("{} record(s) inserted.".format(row_ct))
database.run_in_transaction(insert_singers)Ruby
execute_update() 메서드를 사용하여 DML 문을 실행합니다.
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')"
)
end
puts "#{row_count} record inserted."다음 코드 예시에서는 WHERE 절을 기반으로 Albums 테이블의 MarketingBudget 열을 업데이트합니다.
C++
void DmlStandardUpdate(google::cloud::spanner::Client client) {
using ::google::cloud::StatusOr;
namespace spanner = ::google::cloud::spanner;
auto commit_result = client.Commit(
[&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
auto update = client.ExecuteDml(
std::move(txn),
spanner::SqlStatement(
"UPDATE Albums SET MarketingBudget = MarketingBudget * 2"
" WHERE SingerId = 1 AND AlbumId = 1"));
if (!update) return std::move(update).status();
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Update was successful [spanner_dml_standard_update]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class UpdateUsingDmlCoreAsyncSample
{
public async Task<int> UpdateUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func updateUsingDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1`,
}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
return nil
})
return err
}
Java
static void updateUsingDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
String sql =
"UPDATE Albums "
+ "SET MarketingBudget = MarketingBudget * 2 "
+ "WHERE SingerId = 1 and AlbumId = 1";
long rowCount = transaction.executeUpdate(Statement.of(sql));
System.out.printf("%d record updated.\n", rowCount);
return null;
});
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rowCount] = await transaction.runUpdate({
sql: `UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1`,
});
console.log(`Successfully updated ${rowCount} record.`);
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
* Updates sample data in the database with a DML statement.
*
* This requires the `MarketingBudget` column which must be created before
* running this sample. You can add the column by running the `add_column`
* sample or by running this DDL statement against your database:
*
* ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
*
* Example:
* ```
* update_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$rowCount = $t->executeUpdate(
'UPDATE Albums '
. 'SET MarketingBudget = MarketingBudget * 2 '
. 'WHERE SingerId = 1 and AlbumId = 1');
$t->commit();
printf('Updated %d row(s).' . PHP_EOL, $rowCount);
});
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def update_albums(transaction):
row_ct = transaction.execute_update(
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 1"
)
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(update_albums)Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1"
)
end
puts "#{row_count} record updated."다음 코드 예시는 FirstName 열이 Alice인 Singers 테이블의 모든 행을 삭제합니다.
C++
void DmlStandardDelete(google::cloud::spanner::Client client) {
using ::google::cloud::StatusOr;
namespace spanner = ::google::cloud::spanner;
auto commit_result = client.Commit([&client](spanner::Transaction txn)
-> StatusOr<spanner::Mutations> {
auto dele = client.ExecuteDml(
std::move(txn),
spanner::SqlStatement("DELETE FROM Singers WHERE FirstName = 'Alice'"));
if (!dele) return std::move(dele).status();
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Delete was successful [spanner_dml_standard_delete]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class DeleteUsingDmlCoreAsyncSample
{
public async Task<int> DeleteUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Alice'");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted...");
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func deleteUsingDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{SQL: `DELETE FROM Singers WHERE FirstName = 'Alice'`}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) deleted.\n", rowCount)
return nil
})
return err
}
Java
static void deleteUsingDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
String sql = "DELETE FROM Singers WHERE FirstName = 'Alice'";
long rowCount = transaction.executeUpdate(Statement.of(sql));
System.out.printf("%d record deleted.\n", rowCount);
return null;
});
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rowCount] = await transaction.runUpdate({
sql: "DELETE FROM Singers WHERE FirstName = 'Alice'",
});
console.log(`Successfully deleted ${rowCount} record.`);
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
* Deletes sample data in the database with a DML statement.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function delete_data_with_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$rowCount = $t->executeUpdate(
"DELETE FROM Singers WHERE FirstName = 'Alice'");
$t->commit();
printf('Deleted %d row(s).' . PHP_EOL, $rowCount);
});
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def delete_singers(transaction):
row_ct = transaction.execute_update(
"DELETE FROM Singers WHERE FirstName = 'Alice'"
)
print("{} record(s) deleted.".format(row_ct))
database.run_in_transaction(delete_singers)Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"DELETE FROM Singers WHERE FirstName = 'Alice'"
)
end
puts "#{row_count} record deleted."다음 예시에서는 GoogleSQL 언어 데이터베이스에 대해서만 바인딩된 매개변수가 있는 STRUCT를 사용하여 FirstName 및 LastName으로 필터링된 행에서 LastName을 업데이트합니다.
GoogleSQL
C++
void DmlStructs(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
std::int64_t rows_modified = 0;
auto commit_result =
client.Commit([&client, &rows_modified](spanner::Transaction const& txn)
-> google::cloud::StatusOr<spanner::Mutations> {
auto singer_info = std::make_tuple("Marc", "Richards");
auto sql = spanner::SqlStatement(
"UPDATE Singers SET FirstName = 'Keith' WHERE "
"STRUCT<FirstName String, LastName String>(FirstName, LastName) "
"= @name",
{{"name", spanner::Value(std::move(singer_info))}});
auto dml_result = client.ExecuteDml(txn, std::move(sql));
if (!dml_result) return std::move(dml_result).status();
rows_modified = dml_result->RowsModified();
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << rows_modified
<< " update was successful [spanner_dml_structs]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class UpdateUsingDmlWithStructCoreAsyncSample
{
public async Task<int> UpdateUsingDmlWithStructCoreAsync(string projectId, string instanceId, string databaseId)
{
var nameStruct = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Timothy" },
{ "LastName", SpannerDbType.String, "Campbell" }
};
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var cmd = connection.CreateDmlCommand("UPDATE Singers SET LastName = 'Grant' WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name");
cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func updateUsingDMLStruct(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
type name struct {
FirstName string
LastName string
}
var singerInfo = name{"Timothy", "Campbell"}
stmt := spanner.Statement{
SQL: `Update Singers Set LastName = 'Grant'
WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`,
Params: map[string]interface{}{"name": singerInfo},
}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
return nil
})
return err
}
Java
static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
Struct name =
Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
Statement s =
Statement.newBuilder(
"UPDATE Singers SET LastName = 'Grant' "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+ "= @name")
.bind("name")
.to(name)
.build();
dbClient
.readWriteTransaction()
.run(transaction -> {
long rowCount = transaction.executeUpdate(s);
System.out.printf("%d record updated.\n", rowCount);
return null;
});
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
const nameStruct = Spanner.struct({
FirstName: 'Timothy',
LastName: 'Campbell',
});
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rowCount] = await transaction.runUpdate({
sql: `UPDATE Singers SET LastName = 'Grant'
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
params: {
name: nameStruct,
},
});
console.log(`Successfully updated ${rowCount} record.`);
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Transaction;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;
/**
* Update data with a DML statement using Structs.
*
* The database and table must already exist and can be created using
* `create_database`.
* Example:
* ```
* insert_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_dml_structs(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$nameValue = (new StructValue)
->add('FirstName', 'Timothy')
->add('LastName', 'Campbell');
$nameType = (new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING);
$rowCount = $t->executeUpdate(
"UPDATE Singers SET LastName = 'Grant' "
. 'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) '
. '= @name',
[
'parameters' => [
'name' => $nameValue
],
'types' => [
'name' => $nameType
]
]);
$t->commit();
printf('Updated %d row(s).' . PHP_EOL, $rowCount);
});
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
record_type = param_types.Struct(
[
param_types.StructField("FirstName", param_types.STRING),
param_types.StructField("LastName", param_types.STRING),
]
)
record_value = ("Timothy", "Campbell")
def write_with_struct(transaction):
row_ct = transaction.execute_update(
"UPDATE Singers SET LastName = 'Grant' "
"WHERE STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) = @name",
params={"name": record_value},
param_types={"name": record_type},
)
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(write_with_struct)Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }
client.transaction do |transaction|
row_count = transaction.execute_update(
"UPDATE Singers SET LastName = 'Grant'
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
params: { name: name_struct }
)
end
puts "#{row_count} record updated."반환되는 DML 문으로 데이터 수정
THEN RETURN 절(GoogleSQL 언어 데이터베이스) 또는 RETURNING 절(PostgreSQL 언어 데이터베이스)은 수정된 행에서 데이터를 가져오려는 시나리오에 사용됩니다. 이는 DML 문, 기본값 또는 생성된 열에서 지정되지 않은 값을 보려는 경우에 특히 유용합니다.
클라이언트 라이브러리를 사용하여 반환되는 DML 문을 실행하려면 다음을 수행합니다.
- 읽기-쓰기 트랜잭션을 만듭니다.
- 쿼리 실행을 위해 클라이언트 라이브러리 메서드를 호출하고 반환되는 DML 문을 전달하여 결과를 얻습니다.
다음 코드 예시에서는 Singers 테이블에 새 행을 삽입하고 삽입된 레코드의 생성된 열 FullName을 반환합니다.
GoogleSQL
C++
void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
// Insert records into SINGERS table and return the generated column
// FullName of the inserted records using `THEN RETURN FullName`.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (12, 'Melissa', 'Garcia'),
(13, 'Russell', 'Morales'),
(14, 'Jacqueline', 'Long'),
(15, 'Dylan', 'Shaw')
THEN RETURN FullName
)""");
using RowType = std::tuple<std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "FullName: " << std::get<0>(*row) << "\n";
}
std::cout << "Inserted row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class InsertUsingDmlReturningAsyncSample
{
public async Task<List<string>> InsertUsingDmlReturningAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Insert records into the SINGERS table and return the
// generated column FullName of the inserted records using
// 'THEN RETURN FullName'.
// It is also possible to return all columns of all the
// inserted records by using 'THEN RETURN *'.
using var cmd = connection.CreateDmlCommand(
@"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES
(6, 'Melissa', 'Garcia'),
(7, 'Russell', 'Morales'),
(8, 'Jacqueline', 'Long'),
(9, 'Dylan', 'Shaw') THEN RETURN FullName");
var reader = await cmd.ExecuteReaderAsync();
var insertedSingerNames = new List<string>();
while (await reader.ReadAsync())
{
insertedSingerNames.Add(reader.GetFieldValue<string>("FullName"));
}
Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");
return insertedSingerNames;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func insertUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Insert records into the SINGERS table and returns the
// generated column FullName of the inserted records using
// 'THEN RETURN FullName'.
// It is also possible to return all columns of all the
// inserted records by using 'THEN RETURN *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (21, 'Melissa', 'Garcia'),
(22, 'Russell', 'Morales'),
(23, 'Jacqueline', 'Long'),
(24, 'Dylan', 'Shaw')
THEN RETURN FullName`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var fullName string
if err := row.Columns(&fullName); err != nil {
return err
}
fmt.Fprintf(w, "%s\n", fullName)
}
fmt.Fprintf(w, "%d record(s) inserted.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class InsertUsingDmlReturningSample {
static void insertUsingDmlReturning() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
insertUsingDmlReturning(projectId, instanceId, databaseId);
}
static void insertUsingDmlReturning(String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Insert records into the SINGERS table and returns the
// generated column FullName of the inserted records using
// ‘THEN RETURN FullName’.
// It is also possible to return all columns of all the
// inserted records by using ‘THEN RETURN *’.
dbClient
.readWriteTransaction()
.run(
transaction -> {
String sql =
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
+ "(12, 'Melissa', 'Garcia'), "
+ "(13, 'Russell', 'Morales'), "
+ "(14, 'Jacqueline', 'Long'), "
+ "(15, 'Dylan', 'Shaw') THEN RETURN FullName";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
while (resultSet.next()) {
System.out.println(resultSet.getString(0));
}
System.out.printf(
"Inserted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
function insertUsingDmlReturning(instanceId, databaseId) {
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rows, stats] = await transaction.run({
sql: 'INSERT Singers (SingerId, FirstName, LastName) VALUES (@id, @firstName, @lastName) THEN RETURN FullName',
params: {
id: 18,
firstName: 'Virginia',
lastName: 'Watson',
},
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully inserted ${rowCount} record into the Singers table.`
);
rows.forEach(row => {
console.log(row.toJSON().FullName);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
insertUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Inserts sample data into the given database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function insert_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
// Insert records into SINGERS table and returns the generated column
// FullName of the inserted records using ‘THEN RETURN FullName’. It is also
// possible to return all columns of all the inserted records by using
// ‘THEN RETURN *’.
$sql = 'INSERT INTO Singers (SingerId, FirstName, LastName) '
. "VALUES (12, 'Melissa', 'Garcia'), "
. "(13, 'Russell', 'Morales'), "
. "(14, 'Jacqueline', 'Long'), "
. "(15, 'Dylan', 'Shaw') "
. 'THEN RETURN FullName';
$transaction = $database->transaction();
$result = $transaction->execute($sql);
foreach ($result->rows() as $row) {
printf(
'%s inserted.' . PHP_EOL,
$row['FullName'],
);
}
printf(
'Inserted row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# Insert records into the SINGERS table and returns the
# generated column FullName of the inserted records using
# 'THEN RETURN FullName'.
# It is also possible to return all columns of all the
# inserted records by using 'THEN RETURN *'.
def insert_singers(transaction):
results = transaction.execute_sql(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
"(21, 'Luann', 'Chizoba'), "
"(22, 'Denis', 'Patricio'), "
"(23, 'Felxi', 'Ronan'), "
"(24, 'Dominik', 'Martyna') "
"THEN RETURN FullName"
)
for result in results:
print("FullName: {}".format(*result))
print("{} record(s) inserted.".format(results.stats.row_count_exact))
database.run_in_transaction(insert_singers)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with insert
# operation.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_insert_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Insert records into the SINGERS table and returns the generated column
# FullName of the inserted records using ‘THEN RETURN FullName’.
# It is also possible to return all columns of all the inserted records
# by using ‘THEN RETURN *’.
results = transaction.execute_query "INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')
THEN RETURN FullName"
results.rows.each do |row|
puts "Inserted singers with FullName: #{row[:FullName]}"
end
puts "Inserted row(s) count: #{results.row_count}"
end
end
PostgreSQL
C++
void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
// Insert records into SINGERS table and return the generated column
// FullName of the inserted records using `RETURNING FullName`.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (12, 'Melissa', 'Garcia'),
(13, 'Russell', 'Morales'),
(14, 'Jacqueline', 'Long'),
(15, 'Dylan', 'Shaw')
RETURNING FullName
)""");
using RowType = std::tuple<std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "FullName: " << std::get<0>(*row) << "\n";
}
std::cout << "Inserted row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class InsertUsingDmlReturningAsyncPostgresSample
{
public async Task<List<string>> InsertUsingDmlReturningAsyncPostgres(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Insert records into SINGERS table and return the
// generated column FullName of the inserted records
// using 'RETURNING FullName'.
// It is also possible to return all columns of all the
// inserted records by using 'RETURNING *'.
using var cmd = connection.CreateDmlCommand(
@"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES
(6, 'Melissa', 'Garcia'),
(7, 'Russell', 'Morales'),
(8, 'Jacqueline', 'Long'),
(9, 'Dylan', 'Shaw') RETURNING FullName");
var reader = await cmd.ExecuteReaderAsync();
var insertedSingerNames = new List<string>();
while (await reader.ReadAsync())
{
insertedSingerNames.Add(reader.GetFieldValue<string>("fullname"));
}
Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");
return insertedSingerNames;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func pgInsertUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Insert records into the SINGERS table and returns the
// generated column FullName of the inserted records using
// 'RETURNING FullName'.
// It is also possible to return all columns of all the
// inserted records by using 'RETURNING *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (21, 'Melissa', 'Garcia'),
(22, 'Russell', 'Morales'),
(23, 'Jacqueline', 'Long'),
(24, 'Dylan', 'Shaw')
RETURNING FullName`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var fullName string
if err := row.Columns(&fullName); err != nil {
return err
}
fmt.Fprintf(w, "%s\n", fullName)
}
fmt.Fprintf(w, "%d record(s) inserted.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class PgInsertUsingDmlReturningSample {
static void insertUsingDmlReturning() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
insertUsingDmlReturning(projectId, instanceId, databaseId);
}
static void insertUsingDmlReturning(String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Insert records into SINGERS table and returns the
// generated column FullName of the inserted records
// using ‘RETURNING FullName’.
// It is also possible to return all columns of all the
// inserted records by using ‘RETURNING *’.
dbClient
.readWriteTransaction()
.run(
transaction -> {
String sql =
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
+ "(12, 'Melissa', 'Garcia'), "
+ "(13, 'Russell', 'Morales'), "
+ "(14, 'Jacqueline', 'Long'), "
+ "(15, 'Dylan', 'Shaw') RETURNING FullName";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
while (resultSet.next()) {
System.out.println(resultSet.getString(0));
}
System.out.printf(
"Inserted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
function pgInsertUsingDmlReturning(instanceId, databaseId) {
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rows, stats] = await transaction.run({
sql: 'INSERT Into Singers (SingerId, FirstName, LastName) VALUES ($1, $2, $3) RETURNING FullName',
params: {
p1: 18,
p2: 'Virginia',
p3: 'Watson',
},
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully inserted ${rowCount} record into the Singers table.`
);
rows.forEach(row => {
console.log(row.toJSON().fullname);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
pgInsertUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Inserts sample data into the given postgresql database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function pg_insert_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
// Insert records into SINGERS table and returns the generated column
// FullName of the inserted records using ‘RETURNING FullName’. It is also
// possible to return all columns of all the inserted records by using
// ‘RETURNING *’.
$sql = 'INSERT INTO Singers (Singerid, FirstName, LastName) '
. "VALUES (12, 'Melissa', 'Garcia'), "
. "(13, 'Russell', 'Morales'), "
. "(14, 'Jacqueline', 'Long'), "
. "(15, 'Dylan', 'Shaw') "
. 'RETURNING FullName';
$transaction = $database->transaction();
$result = $transaction->execute($sql);
foreach ($result->rows() as $row) {
printf(
'%s inserted.' . PHP_EOL,
$row['fullname'],
);
}
printf(
'Inserted row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# Insert records into the SINGERS table and returns the
# generated column FullName of the inserted records using
# 'RETURNING FullName'.
# It is also possible to return all columns of all the
# inserted records by using 'RETURNING *'.
def insert_singers(transaction):
results = transaction.execute_sql(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
"(21, 'Luann', 'Chizoba'), "
"(22, 'Denis', 'Patricio'), "
"(23, 'Felxi', 'Ronan'), "
"(24, 'Dominik', 'Martyna') "
"RETURNING FullName"
)
for result in results:
print("FullName: {}".format(*result))
print("{} record(s) inserted.".format(results.stats.row_count_exact))
database.run_in_transaction(insert_singers)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with insert
# operation in PostgreSql.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_insert_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Insert records into SINGERS table and returns the generated column
# FullName of the inserted records using ‘RETURNING FullName’.
# It is also possible to return all columns of all the inserted
# records by using ‘RETURNING *’.
results = transaction.execute_query "INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')
RETURNING FullName"
results.rows.each do |row|
puts "Inserted singers with FullName: #{row[:fullname]}"
end
puts "Inserted row(s) count: #{results.row_count}"
end
end
다음 코드 예시에서는 WHERE 절을 기반으로 Albums 테이블의 MarketingBudget 열을 업데이트하고 업데이트된 레코드의 수정된 MarketingBudget 열을 반환합니다.
GoogleSQL
C++
void UpdateUsingDmlReturning(google::cloud::spanner::Client client) {
// Update MarketingBudget column for records satisfying a particular
// condition and return the modified MarketingBudget column of the
// updated records using `THEN RETURN MarketingBudget`.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 AND AlbumId = 1
THEN RETURN MarketingBudget
)""");
using RowType = std::tuple<absl::optional<std::int64_t>>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "MarketingBudget: ";
if (std::get<0>(*row).has_value()) {
std::cout << *std::get<0>(*row);
} else {
std::cout << "NULL";
}
std::cout << "\n";
}
std::cout << "Updated row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class UpdateUsingDmlReturningAsyncSample
{
public async Task<List<long>> UpdateUsingDmlReturningAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Update MarketingBudget column for records satisfying
// a particular condition and return the modified
// MarketingBudget column of the updated records using
// 'THEN RETURN MarketingBudget'.
// It is also possible to return all columns of all the
// updated records by using 'THEN RETURN *'.
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1 THEN RETURN MarketingBudget");
var reader = await cmd.ExecuteReaderAsync();
var updatedMarketingBudgets = new List<long>();
while (await reader.ReadAsync())
{
updatedMarketingBudgets.Add(reader.GetFieldValue<long>("MarketingBudget"));
}
Console.WriteLine($"{updatedMarketingBudgets.Count} row(s) updated...");
return updatedMarketingBudgets;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func updateUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Update MarketingBudget column for records satisfying
// a particular condition and returns the modified
// MarketingBudget column of the updated records using
// 'THEN RETURN MarketingBudget'.
// It is also possible to return all columns of all the
// updated records by using 'THEN RETURN *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1
THEN RETURN MarketingBudget`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var marketingBudget int64
if err := row.Columns(&marketingBudget); err != nil {
return err
}
fmt.Fprintf(w, "%d\n", marketingBudget)
}
fmt.Fprintf(w, "%d record(s) updated.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class UpdateUsingDmlReturningSample {
static void updateUsingDmlReturning() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
updateUsingDmlReturning(projectId, instanceId, databaseId);
}
static void updateUsingDmlReturning(String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Update MarketingBudget column for records satisfying
// a particular condition and returns the modified
// MarketingBudget column of the updated records using
// ‘THEN RETURN MarketingBudget’.
// It is also possible to return all columns of all the
// updated records by using ‘THEN RETURN *’.
dbClient
.readWriteTransaction()
.run(
transaction -> {
String sql =
"UPDATE Albums "
+ "SET MarketingBudget = MarketingBudget * 2 "
+ "WHERE SingerId = 1 and AlbumId = 1 "
+ "THEN RETURN MarketingBudget";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
while (resultSet.next()) {
System.out.printf("%d\n", resultSet.getLong(0));
}
System.out.printf(
"Updated row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
function updateUsingDmlReturning(instanceId, databaseId) {
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rows, stats] = await transaction.run({
sql: 'UPDATE Albums SET MarketingBudget = 2000000 WHERE SingerId = 1 and AlbumId = 1 THEN RETURN MarketingBudget',
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully updated ${rowCount} record into the Albums table.`
);
rows.forEach(row => {
console.log(row.toJSON().MarketingBudget);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
updateUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Update the given database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$transaction = $database->transaction();
// Update MarketingBudget column for records satisfying a particular
// condition and returns the modified MarketingBudget column of the updated
// records using ‘THEN RETURN MarketingBudget’. It is also possible to return
// all columns of all the updated records by using ‘THEN RETURN *’.
$result = $transaction->execute(
'UPDATE Albums '
. 'SET MarketingBudget = MarketingBudget * 2 '
. 'WHERE SingerId = 1 and AlbumId = 1 '
. 'THEN RETURN MarketingBudget'
);
foreach ($result->rows() as $row) {
printf('MarketingBudget: %s' . PHP_EOL, $row['MarketingBudget']);
}
printf(
'Updated row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# Update MarketingBudget column for records satisfying
# a particular condition and returns the modified
# MarketingBudget column of the updated records using
# 'THEN RETURN MarketingBudget'.
# It is also possible to return all columns of all the
# updated records by using 'THEN RETURN *'.
def update_albums(transaction):
results = transaction.execute_sql(
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 1 "
"THEN RETURN MarketingBudget"
)
for result in results:
print("MarketingBudget: {}".format(*result))
print("{} record(s) updated.".format(results.stats.row_count_exact))
database.run_in_transaction(update_albums)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with update
# operation.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_update_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Update MarketingBudget column for records satisfying a particular
# condition and returns the modified MarketingBudget column of the
# updated records using ‘THEN RETURN MarketingBudget’.
#
# It is also possible to return all columns of all the updated records
# by using ‘THEN RETURN *’.
results = transaction.execute_query "UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1
THEN RETURN MarketingBudget"
results.rows.each do |row|
puts "Updated Album with MarketingBudget: #{row[:MarketingBudget]}"
end
puts "Updated row(s) count: #{results.row_count}"
end
end
PostgreSQL
C++
void UpdateUsingDmlReturning(google::cloud::spanner::Client client) {
// Update MarketingBudget column for records satisfying a particular
// condition and return the modified MarketingBudget column of the
// updated records using `RETURNING MarketingBudget`.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 AND AlbumId = 1
RETURNING MarketingBudget
)""");
using RowType = std::tuple<absl::optional<std::int64_t>>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "MarketingBudget: ";
if (std::get<0>(*row).has_value()) {
std::cout << *std::get<0>(*row);
} else {
std::cout << "NULL";
}
std::cout << "\n";
}
std::cout << "Updated row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class UpdateUsingDmlReturningAsyncPostgresSample
{
public async Task<List<long>> UpdateUsingDmlReturningAsyncPostgres(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Update MarketingBudget column for records satisfying
// a particular condition and return the modified
// MarketingBudget column of the updated records using
// 'RETURNING MarketingBudget'.
// It is also possible to return all columns of all the
// updated records by using 'RETURNING *'.
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 14 and AlbumId = 20 RETURNING MarketingBudget");
var reader = await cmd.ExecuteReaderAsync();
var updatedMarketingBudgets = new List<long>();
while (await reader.ReadAsync())
{
updatedMarketingBudgets.Add(reader.GetFieldValue<long>("marketingbudget"));
}
Console.WriteLine($"{updatedMarketingBudgets.Count} row(s) updated...");
return updatedMarketingBudgets;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func pgUpdateUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Update MarketingBudget column for records satisfying
// a particular condition and returns the modified
// MarketingBudget column of the updated records using
// 'RETURNING MarketingBudget'.
// It is also possible to return all columns of all the
// updated records by using 'RETURNING *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1
RETURNING MarketingBudget`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var marketingBudget int64
if err := row.Columns(&marketingBudget); err != nil {
return err
}
fmt.Fprintf(w, "%d\n", marketingBudget)
}
fmt.Fprintf(w, "%d record(s) updated.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class PgUpdateUsingDmlReturningSample {
static void updateUsingDmlReturning() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
updateUsingDmlReturning(projectId, instanceId, databaseId);
}
static void updateUsingDmlReturning(String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Update MarketingBudget column for records satisfying
// a particular condition and returns the modified
// MarketingBudget column of the updated records using
// ‘RETURNING MarketingBudget’.
// It is also possible to return all columns of all the
// updated records by using ‘RETURNING *’.
dbClient
.readWriteTransaction()
.run(
transaction -> {
String sql =
"UPDATE Albums "
+ "SET MarketingBudget = MarketingBudget * 2 "
+ "WHERE SingerId = 1 and AlbumId = 1 "
+ "RETURNING MarketingBudget";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
while (resultSet.next()) {
System.out.printf("%d\n", resultSet.getLong(0));
}
System.out.printf(
"Updated row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
function pgUpdateUsingDmlReturning(instanceId, databaseId) {
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rows, stats] = await transaction.run({
sql: 'UPDATE singers SET FirstName = $1, LastName = $2 WHERE singerid = $3 RETURNING FullName',
params: {
p1: 'Virginia1',
p2: 'Watson1',
p3: 18,
},
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully updated ${rowCount} record into the Singers table.`
);
rows.forEach(row => {
console.log(row.toJSON().fullname);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
pgUpdateUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Update the given postgresql database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function pg_update_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$transaction = $database->transaction();
// Update MarketingBudget column for records satisfying a particular
// condition and returns the modified MarketingBudget column of the updated
// records using ‘RETURNING MarketingBudget’. It is also possible to return
// all columns of all the updated records by using ‘RETURNING *’.
$result = $transaction->execute(
'UPDATE Albums '
. 'SET MarketingBudget = MarketingBudget * 2 '
. 'WHERE SingerId = 1 and AlbumId = 1'
. 'RETURNING MarketingBudget'
);
foreach ($result->rows() as $row) {
printf('MarketingBudget: %s' . PHP_EOL, $row['marketingbudget']);
}
printf(
'Updated row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# Update MarketingBudget column for records satisfying
# a particular condition and returns the modified
# MarketingBudget column of the updated records using
# 'RETURNING MarketingBudget'.
# It is also possible to return all columns of all the
# updated records by using 'RETURNING *'.
def update_albums(transaction):
results = transaction.execute_sql(
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 1 "
"RETURNING MarketingBudget"
)
for result in results:
print("MarketingBudget: {}".format(*result))
print("{} record(s) updated.".format(results.stats.row_count_exact))
database.run_in_transaction(update_albums)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with update
# operation in PostgreSql.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_update_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Update MarketingBudget column for records satisfying a particular
# condition and returns the modified MarketingBudget column of the
# updated records using ‘RETURNING MarketingBudget’.
# It is also possible to return all columns of all the updated records
# by using ‘RETURNING *’.
results = transaction.execute_query "UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1
RETURNING MarketingBudget"
results.rows.each do |row|
puts "Updated Albums with MarketingBudget: #{row[:marketingbudget]}"
end
puts "Updated row(s) count: #{results.row_count}"
end
end
다음 코드 예시는 FirstName 열이 Alice인 Singers 테이블의 모든 행을 삭제하고 삭제된 레코드 SingerId 및 FullName 열을 반환합니다.
GoogleSQL
C++
void DeleteUsingDmlReturning(google::cloud::spanner::Client client) {
// Delete records from SINGERS table satisfying a particular condition
// and return the SingerId and FullName column of the deleted records
// using `THEN RETURN SingerId, FullName'.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
DELETE FROM Singers
WHERE FirstName = 'Alice'
THEN RETURN SingerId, FullName
)""");
using RowType = std::tuple<std::int64_t, std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "SingerId: " << std::get<0>(*row) << " ";
std::cout << "FullName: " << std::get<1>(*row) << "\n";
}
std::cout << "Deleted row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
public class DeleteUsingDmlReturningAsyncSample
{
public async Task<List<string>> DeleteUsingDmlReturningAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Delete records from SINGERS table satisfying a
// particular condition and return the SingerId
// and FullName column of the deleted records using
// 'THEN RETURN SingerId, FullName'.
// It is also possible to return all columns of all the
// deleted records by using 'THEN RETURN *'.
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName");
var reader = await cmd.ExecuteReaderAsync();
var deletedSingerNames = new List<string>();
while (await reader.ReadAsync())
{
deletedSingerNames.Add(reader.GetFieldValue<string>("FullName"));
}
Console.WriteLine($"{deletedSingerNames.Count} row(s) deleted...");
return deletedSingerNames;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func deleteUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Delete records from SINGERS table satisfying a
// particular condition and returns the SingerId
// and FullName column of the deleted records using
// 'THEN RETURN SingerId, FullName'.
// It is also possible to return all columns of all the
// deleted records by using 'THEN RETURN *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `DELETE FROM Singers WHERE FirstName = 'Alice'
THEN RETURN SingerId, FullName`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var (
singerID int64
fullName string
)
if err := row.Columns(&singerID, &fullName); err != nil {
return err
}
fmt.Fprintf(w, "%d %s\n", singerID, fullName)
}
fmt.Fprintf(w, "%d record(s) deleted.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class DeleteUsingDmlReturningSample {
static void deleteUsingDmlReturningSample() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
deleteUsingDmlReturningSample(projectId, instanceId, databaseId);
}
static void deleteUsingDmlReturningSample(
String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Delete records from SINGERS table satisfying a
// particular condition and returns the SingerId
// and FullName column of the deleted records using
// ‘THEN RETURN SingerId, FullName’.
// It is also possible to return all columns of all the
// deleted records by using ‘THEN RETURN *’.
dbClient
.readWriteTransaction()
.run(
transaction -> {
String sql =
"DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
while (resultSet.next()) {
System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
}
System.out.printf(
"Deleted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
function deleteUsingDmlReturning(instanceId, databaseId) {
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rows, stats] = await transaction.run({
sql: 'DELETE FROM Singers WHERE SingerId = 18 THEN RETURN FullName',
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully deleted ${rowCount} record from the Singers table.`
);
rows.forEach(row => {
console.log(row.toJSON().FullName);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
deleteUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Delete data from the given database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function delete_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$transaction = $database->transaction();
// Delete records from SINGERS table satisfying a particular condition and
// returns the SingerId and FullName column of the deleted records using
// 'THEN RETURN SingerId, FullName'. It is also possible to return all columns
// of all the deleted records by using 'THEN RETURN *'.
$result = $transaction->execute(
"DELETE FROM Singers WHERE FirstName = 'Alice' "
. 'THEN RETURN SingerId, FullName',
);
foreach ($result->rows() as $row) {
printf(
'%d %s.' . PHP_EOL,
$row['SingerId'],
$row['FullName']
);
}
printf(
'Deleted row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# Delete records from SINGERS table satisfying a
# particular condition and returns the SingerId
# and FullName column of the deleted records using
# 'THEN RETURN SingerId, FullName'.
# It is also possible to return all columns of all the
# deleted records by using 'THEN RETURN *'.
def delete_singers(transaction):
results = transaction.execute_sql(
"DELETE FROM Singers WHERE FirstName = 'David' "
"THEN RETURN SingerId, FullName"
)
for result in results:
print("SingerId: {}, FullName: {}".format(*result))
print("{} record(s) deleted.".format(results.stats.row_count_exact))
database.run_in_transaction(delete_singers)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with delete
# operation.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_delete_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Delete records from SINGERS table satisfying a particular condition and
# returns the SingerId and FullName column of the deleted records using
# ‘THEN RETURN SingerId, FullName’.
# It is also possible to return all columns of all the deleted records
# by using ‘THEN RETURN *’.
results = transaction.execute_query "DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName"
results.rows.each do |row|
puts "Deleted singer with SingerId: #{row[:SingerId]}, FullName: #{row[:FullName]}"
end
puts "Deleted row(s) count: #{results.row_count}"
end
end
PostgreSQL
C++
void DeleteUsingDmlReturning(google::cloud::spanner::Client client) {
// Delete records from SINGERS table satisfying a particular condition
// and return the SingerId and FullName column of the deleted records
// using `RETURNING SingerId, FullName'.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
DELETE FROM Singers
WHERE FirstName = 'Alice'
RETURNING SingerId, FullName
)""");
using RowType = std::tuple<std::int64_t, std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "SingerId: " << std::get<0>(*row) << " ";
std::cout << "FullName: " << std::get<1>(*row) << "\n";
}
std::cout << "Deleted row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class DeleteUsingDmlReturningAsyncPostgresSample
{
public async Task<List<string>> DeleteUsingDmlReturningAsyncPostgres(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Delete records from SINGERS table satisfying a
// particular condition and return the SingerId
// and FullName column of the deleted records using
// 'RETURNING SingerId, FullName'.
// It is also possible to return all columns of all the
// deleted records by using 'RETURNING *'.
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Lata' RETURNING SingerId, FullName");
var reader = await cmd.ExecuteReaderAsync();
var deletedSingerNames = new List<string>();
while (await reader.ReadAsync())
{
deletedSingerNames.Add(reader.GetFieldValue<string>("fullname"));
}
Console.WriteLine($"{deletedSingerNames.Count} row(s) deleted...");
return deletedSingerNames;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func pgDeleteUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Delete records from SINGERS table satisfying a
// particular condition and returns the SingerId
// and FullName column of the deleted records using
// 'RETURNING SingerId, FullName'.
// It is also possible to return all columns of all the
// deleted records by using 'RETURNING *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `DELETE FROM Singers WHERE FirstName = 'Alice'
RETURNING SingerId, FullName`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var (
singerID int64
fullName string
)
if err := row.Columns(&singerID, &fullName); err != nil {
return err
}
fmt.Fprintf(w, "%d %s\n", singerID, fullName)
}
fmt.Fprintf(w, "%d record(s) deleted.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class PgDeleteUsingDmlReturningSample {
static void deleteUsingDmlReturningSample() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
deleteUsingDmlReturningSample(projectId, instanceId, databaseId);
}
static void deleteUsingDmlReturningSample(
String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Delete records from SINGERS table satisfying a
// particular condition and returns the SingerId
// and FullName column of the deleted records using
// ‘RETURNING SingerId, FullName’.
// It is also possible to return all columns of all the
// deleted records by using ‘RETURNING *’.
dbClient
.readWriteTransaction()
.run(
transaction -> {
String sql =
"DELETE FROM Singers WHERE FirstName = 'Alice' RETURNING SingerId, FullName";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
while (resultSet.next()) {
System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
}
System.out.printf(
"Deleted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
function pgDeleteUsingDmlReturning(instanceId, databaseId) {
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rows, stats] = await transaction.run({
sql: 'DELETE FROM Singers WHERE SingerId = 18 RETURNING FullName',
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully deleted ${rowCount} record from the Singers table.`
);
rows.forEach(row => {
console.log(row.toJSON().fullname);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
pgDeleteUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Delete data from the given postgresql database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function pg_delete_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$transaction = $database->transaction();
// Delete records from SINGERS table satisfying a particular condition and
// returns the SingerId and FullName column of the deleted records using
// ‘RETURNING SingerId, FullName’. It is also possible to return all columns
// of all the deleted records by using ‘RETURNING *’.
$result = $transaction->execute(
"DELETE FROM Singers WHERE FirstName = 'Alice' "
. 'RETURNING SingerId, FullName',
);
foreach ($result->rows() as $row) {
printf(
'%d %s.' . PHP_EOL,
$row['singerid'],
$row['fullname']
);
}
printf(
'Deleted row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# Delete records from SINGERS table satisfying a
# particular condition and returns the SingerId
# and FullName column of the deleted records using
# 'RETURNING SingerId, FullName'.
# It is also possible to return all columns of all the
# deleted records by using 'RETURNING *'.
def delete_singers(transaction):
results = transaction.execute_sql(
"DELETE FROM Singers WHERE FirstName = 'David' "
"RETURNING SingerId, FullName"
)
for result in results:
print("SingerId: {}, FullName: {}".format(*result))
print("{} record(s) deleted.".format(results.stats.row_count_exact))
database.run_in_transaction(delete_singers)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with delete
# operation in PostgreSql.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_delete_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Delete records from SINGERS table satisfying a particular condition and
# returns the SingerId and FullName column of the deleted records using
# ‘RETURNING SingerId, FullName’.
# It is also possible to return all columns of all the deleted records
# by using ‘RETURNING *’.
results = transaction.execute_query "DELETE FROM singers WHERE firstname = 'Alice' RETURNING SingerId, FullName"
results.rows.each do |row|
puts "Deleted singer with SingerId: #{row[:singerid]}, FullName: #{row[:fullname]}"
end
puts "Deleted row(s) count: #{results.row_count}"
end
end
동일한 트랜잭션에 작성된 데이터 읽기
DML 문을 사용하여 변경한 내용은 동일 트랜잭션의 후속 문에서 볼 수 있습니다. 이것은 트랜잭션이 커밋될 때까지 변경 내용이 보이지 않는 변형 사용과 다릅니다.
Spanner는 모든 DML 문 다음에 제약조건을 검사합니다. 이 검사는 Spanner가 커밋 때까지 클라이언트에서 변형을 버퍼링하고 커밋 시점에 제약조건을 검사하는 변형을 사용하는 것과 다릅니다. 각 문 다음에 제약조건을 평가함으로써 Spanner는 DML 문에서 반환하는 데이터가 스키마와 일치한다고 보장할 수 있습니다.
다음 예시에서는 Singers 테이블의 행을 업데이트한 후 SELECT 문을 실행하여 새 값을 인쇄합니다.
C++
void DmlWriteThenRead(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
using ::google::cloud::StatusOr;
auto commit_result = client.Commit(
[&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
auto insert = client.ExecuteDml(
txn, spanner::SqlStatement(
"INSERT INTO Singers (SingerId, FirstName, LastName)"
" VALUES (11, 'Timothy', 'Campbell')"));
if (!insert) return std::move(insert).status();
// Read newly inserted record.
spanner::SqlStatement select(
"SELECT FirstName, LastName FROM Singers where SingerId = 11");
using RowType = std::tuple<std::string, std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(select));
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (auto const& row : spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "FirstName: " << std::get<0>(*row) << "\t";
std::cout << "LastName: " << std::get<1>(*row) << "\n";
}
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Write then read succeeded [spanner_dml_write_then_read]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class WriteAndReadUsingDmlCoreAsyncSample
{
public async Task<int> WriteAndReadUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var createDmlCmd = connection.CreateDmlCommand(@"INSERT Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')");
int rowCount = await createDmlCmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) inserted...");
// Read newly inserted record.
using var createSelectCmd = connection.CreateSelectCommand(@"SELECT FirstName, LastName FROM Singers WHERE SingerId = 11");
using var reader = await createSelectCmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
Console.WriteLine($"{reader.GetFieldValue<string>("FirstName")} {reader.GetFieldValue<string>("LastName")}");
}
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func writeAndReadUsingDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
// Insert Record
stmt := spanner.Statement{
SQL: `INSERT Singers (SingerId, FirstName, LastName)
VALUES (11, 'Timothy', 'Campbell')`,
}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
// Read newly inserted record
stmt = spanner.Statement{SQL: `SELECT FirstName, LastName FROM Singers WHERE SingerId = 11`}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done || err != nil {
break
}
var firstName, lastName string
if err := row.ColumnByName("FirstName", &firstName); err != nil {
return err
}
if err := row.ColumnByName("LastName", &lastName); err != nil {
return err
}
fmt.Fprintf(w, "Found record name with %s, %s", firstName, lastName)
}
return err
})
return err
}
Java
static void writeAndReadUsingDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
// Insert record.
String sql =
"INSERT INTO Singers (SingerId, FirstName, LastName) "
+ " VALUES (11, 'Timothy', 'Campbell')";
long rowCount = transaction.executeUpdate(Statement.of(sql));
System.out.printf("%d record inserted.\n", rowCount);
// Read newly inserted record.
sql = "SELECT FirstName, LastName FROM Singers WHERE SingerId = 11";
// We use a try-with-resource block to automatically release resources held by
// ResultSet.
try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
while (resultSet.next()) {
System.out.printf(
"%s %s\n",
resultSet.getString("FirstName"), resultSet.getString("LastName"));
}
}
return null;
});
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
await transaction.runUpdate({
sql: `INSERT Singers (SingerId, FirstName, LastName)
VALUES (11, 'Timothy', 'Campbell')`,
});
const [rows] = await transaction.run({
sql: 'SELECT FirstName, LastName FROM Singers',
});
rows.forEach(row => {
const json = row.toJSON();
console.log(`${json.FirstName} ${json.LastName}`);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
* Writes then reads data inside a Transaction with a DML statement.
*
* The database and table must already exist and can be created using
* `create_database`.
* Example:
* ```
* insert_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function write_read_with_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$rowCount = $t->executeUpdate(
'INSERT Singers (SingerId, FirstName, LastName) '
. " VALUES (11, 'Timothy', 'Campbell')");
printf('Inserted %d row(s).' . PHP_EOL, $rowCount);
$results = $t->execute('SELECT FirstName, LastName FROM Singers WHERE SingerId = 11');
foreach ($results as $row) {
printf('%s %s' . PHP_EOL, $row['FirstName'], $row['LastName']);
}
$t->commit();
});
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
def write_then_read(transaction):
# Insert record.
row_ct = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) "
" VALUES (11, 'Timothy', 'Campbell')"
)
print("{} record(s) inserted.".format(row_ct))
# Read newly inserted record.
results = transaction.execute_sql(
"SELECT FirstName, LastName FROM Singers WHERE SingerId = 11"
)
for result in results:
print("FirstName: {}, LastName: {}".format(*result))
database.run_in_transaction(write_then_read)Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')"
)
puts "#{row_count} record updated."
transaction.execute("SELECT FirstName, LastName FROM Singers WHERE SingerId = 11").rows.each do |row|
puts "#{row[:FirstName]} #{row[:LastName]}"
end
end쿼리 계획 가져오기
Google Cloud 콘솔, 클라이언트 라이브러리, gcloud 명령줄 도구를 사용하여 쿼리 계획을 검색할 수 있습니다.
Partitioned DML 사용
Partitioned DML은 일괄 업데이트 및 삭제, 특히 정기적인 정리 및 백필을 위해 설계되었습니다.
Google Cloud CLI로 문 실행
Partitioned DML 문을 실행하려면 gcloud spanner databases execute-sql 명령어를 --enable-partitioned-dml 옵션과 함께 사용합니다. 다음 예시는 Albums 테이블의 행을 업데이트합니다.
gcloud spanner databases execute-sql example-db \
--instance=test-instance --enable-partitioned-dml \
--sql='UPDATE Albums SET MarketingBudget = 0 WHERE MarketingBudget IS NULL'
클라이언트 라이브러리를 사용하여 데이터 수정
다음 코드 예시에서는 Albums 테이블의 MarketingBudget 열을 업데이트합니다.
C++
ExecutePartitionedDml() 함수를 사용하여 Partitioned DML 문을 실행합니다.
void DmlPartitionedUpdate(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto result = client.ExecutePartitionedDml(
spanner::SqlStatement("UPDATE Albums SET MarketingBudget = 100000"
" WHERE SingerId > 1"));
if (!result) throw std::move(result).status();
std::cout << "Updated at least " << result->row_count_lower_bound
<< " row(s) [spanner_dml_partitioned_update]\n";
}C#
ExecutePartitionedUpdateAsync() 메서드를 사용하여 Partitioned DML 문을 실행합니다.
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class UpdateUsingPartitionedDmlCoreAsyncSample
{
public async Task<long> UpdateUsingPartitionedDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1");
long rowCount = await cmd.ExecutePartitionedUpdateAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return rowCount;
}
}Go
PartitionedUpdate() 메서드를 사용하여 Partitioned DML 문을 실행합니다.
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func updateUsingPartitionedDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
stmt := spanner.Statement{SQL: "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"}
rowCount, err := client.PartitionedUpdate(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
return nil
}
Java
executePartitionedUpdate() 메서드를 사용하여 Partitioned DML 문을 실행합니다.
static void updateUsingPartitionedDml(DatabaseClient dbClient) {
String sql = "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1";
long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
System.out.printf("%d records updated.\n", rowCount);
}Node.js
runPartitionedUpdate() 메서드를 사용하여 Partitioned DML 문을 실행합니다.
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
try {
const [rowCount] = await database.runPartitionedUpdate({
sql: 'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1',
});
console.log(`Successfully updated ${rowCount} records.`);
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}PHP
executePartitionedUpdate() 메서드를 사용하여 Partitioned DML 문을 실행합니다.
use Google\Cloud\Spanner\SpannerClient;
/**
* Updates sample data in the database by partition with a DML statement.
*
* This updates the `MarketingBudget` column which must be created before
* running this sample. You can add the column by running the `add_column`
* sample or by running this DDL statement against your database:
*
* ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
*
* Example:
* ```
* update_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_partitioned_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$rowCount = $database->executePartitionedUpdate(
'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1'
);
printf('Updated %d row(s).' . PHP_EOL, $rowCount);
}Python
execute_partitioned_dml() 메서드를 사용하여 Partitioned DML 문을 실행합니다.
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
row_ct = database.execute_partitioned_dml(
"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)
print("{} records updated.".format(row_ct))Ruby
execute_partitioned_update() 메서드를 사용하여 Partitioned DML 문을 실행합니다.
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = client.execute_partition_update(
"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)
puts "#{row_count} records updated."다음 코드 예시에서는 SingerId 열을 기준으로 Singers 테이블에서 행을 삭제합니다.
C++
void DmlPartitionedDelete(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto result = client.ExecutePartitionedDml(
spanner::SqlStatement("DELETE FROM Singers WHERE SingerId > 10"));
if (!result) throw std::move(result).status();
std::cout << "Deleted at least " << result->row_count_lower_bound
<< " row(s) [spanner_dml_partitioned_delete]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class DeleteUsingPartitionedDmlCoreAsyncSample
{
public async Task<long> DeleteUsingPartitionedDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId > 10");
long rowCount = await cmd.ExecutePartitionedUpdateAsync();
Console.WriteLine($"{rowCount} row(s) deleted...");
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func deleteUsingPartitionedDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
stmt := spanner.Statement{SQL: "DELETE FROM Singers WHERE SingerId > 10"}
rowCount, err := client.PartitionedUpdate(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) deleted.", rowCount)
return nil
}
Java
static void deleteUsingPartitionedDml(DatabaseClient dbClient) {
String sql = "DELETE FROM Singers WHERE SingerId > 10";
long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
System.out.printf("%d records deleted.\n", rowCount);
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
try {
const [rowCount] = await database.runPartitionedUpdate({
sql: 'DELETE FROM Singers WHERE SingerId > 10',
});
console.log(`Successfully deleted ${rowCount} records.`);
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Delete sample data in the database by partition with a DML statement.
*
* This updates the `MarketingBudget` column which must be created before
* running this sample. You can add the column by running the `add_column`
* sample or by running this DDL statement against your database:
*
* ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
*
* Example:
* ```
* update_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function delete_data_with_partitioned_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$rowCount = $database->executePartitionedUpdate(
'DELETE FROM Singers WHERE SingerId > 10'
);
printf('Deleted %d row(s).' . PHP_EOL, $rowCount);
}Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
row_ct = database.execute_partitioned_dml("DELETE FROM Singers WHERE SingerId > 10")
print("{} record(s) deleted.".format(row_ct))Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = client.execute_partition_update(
"DELETE FROM Singers WHERE SingerId > 10"
)
puts "#{row_count} records deleted."일괄 DML 사용
여러 직렬 요청에서 추가 지연 시간을 피하려면 일괄 DML을 사용하여 단일 트랜잭션의 여러 INSERT , UPDATE 또는 DELETE 문을 보냅니다.
C++
ExecuteBatchDml() 함수를 사용하여 DML 문 목록을 실행합니다.
void DmlBatchUpdate(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto commit_result =
client.Commit([&client](spanner::Transaction const& txn)
-> google::cloud::StatusOr<spanner::Mutations> {
std::vector<spanner::SqlStatement> statements = {
spanner::SqlStatement("INSERT INTO Albums"
" (SingerId, AlbumId, AlbumTitle,"
" MarketingBudget)"
" VALUES (1, 3, 'Test Album Title', 10000)"),
spanner::SqlStatement("UPDATE Albums"
" SET MarketingBudget = MarketingBudget * 2"
" WHERE SingerId = 1 and AlbumId = 3")};
auto result = client.ExecuteBatchDml(txn, statements);
if (!result) return std::move(result).status();
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (std::size_t i = 0; i < result->stats.size(); ++i) {
std::cout << result->stats[i].row_count << " rows affected"
<< " for the statement " << (i + 1) << ".\n";
}
// Batch operations may have partial failures, in which case
// ExecuteBatchDml returns with success, but the application should
// verify that all statements completed successfully
if (!result->status.ok()) return result->status;
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Update was successful [spanner_dml_batch_update]\n";
}C#
connection.CreateBatchDmlCommand() 메서드를 사용하여 일괄 명령어를 만들고 Add 메서드를 사용하여 DML 문을 추가한 후 ExecuteNonQueryAsync() 메서드를 사용하여 문을 실행합니다.
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
public class UpdateUsingBatchDmlCoreAsyncSample
{
public async Task<int> UpdateUsingBatchDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
SpannerBatchCommand cmd = connection.CreateBatchDmlCommand();
cmd.Add("INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget) VALUES (1, 3, 'Test Album Title', 10000)");
cmd.Add("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 3");
IEnumerable<long> affectedRows = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"Executed {affectedRows.Count()} " + "SQL statements using Batch DML.");
return affectedRows.Count();
}
}Go
BatchUpdate() 메서드를 사용하여 DML Statement 객체 배열을 실행합니다.
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func updateUsingBatchDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmts := []spanner.Statement{
{SQL: `INSERT INTO Albums
(SingerId, AlbumId, AlbumTitle, MarketingBudget)
VALUES (1, 3, 'Test Album Title', 10000)`},
{SQL: `UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 3`},
}
rowCounts, err := txn.BatchUpdate(ctx, stmts)
if err != nil {
return err
}
fmt.Fprintf(w, "Executed %d SQL statements using Batch DML.\n", len(rowCounts))
return nil
})
return err
}
Java
transaction.batchUpdate() 메서드를 사용하여 여러 DML Statement 객체의 ArrayList을 실행합니다.
static void updateUsingBatchDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
List<Statement> stmts = new ArrayList<Statement>();
String sql =
"INSERT INTO Albums "
+ "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
+ "VALUES (1, 3, 'Test Album Title', 10000) ";
stmts.add(Statement.of(sql));
sql =
"UPDATE Albums "
+ "SET MarketingBudget = MarketingBudget * 2 "
+ "WHERE SingerId = 1 and AlbumId = 3";
stmts.add(Statement.of(sql));
long[] rowCounts;
try {
rowCounts = transaction.batchUpdate(stmts);
} catch (SpannerBatchUpdateException e) {
rowCounts = e.getUpdateCounts();
}
for (int i = 0; i < rowCounts.length; i++) {
System.out.printf("%d record updated by stmt %d.\n", rowCounts[i], i);
}
return null;
});
}Node.js
transaction.batchUpdate()을 사용하여 DML 문 목록을 실행합니다.
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
const insert = {
sql: `INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget)
VALUES (1, 3, "Test Album Title", 10000)`,
};
const update = {
sql: `UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 3`,
};
const dmlStatements = [insert, update];
try {
await database.runTransactionAsync(async transaction => {
const [rowCounts] = await transaction.batchUpdate(dmlStatements);
await transaction.commit();
console.log(
`Successfully executed ${rowCounts.length} SQL statements using Batch DML.`
);
});
} catch (err) {
console.error('ERROR:', err);
throw err;
} finally {
// Close the database when finished.
database.close();
}PHP
executeUpdateBatch()을 사용하여 DML 문 목록을 생성한 다음 commit()을 사용하여 문을 실행합니다.
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
* Updates sample data in the database with Batch DML.
*
* This requires the `MarketingBudget` column which must be created before
* running this sample. You can add the column by running the `add_column`
* sample or by running this DDL statement against your database:
*
* ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
*
* Example:
* ```
* update_data_with_batch_dml($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_batch_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$batchDmlResult = $database->runTransaction(function (Transaction $t) {
$result = $t->executeUpdateBatch([
[
'sql' => 'INSERT INTO Albums '
. '(SingerId, AlbumId, AlbumTitle, MarketingBudget) '
. "VALUES (1, 3, 'Test Album Title', 10000)"
],
[
'sql' => 'UPDATE Albums '
. 'SET MarketingBudget = MarketingBudget * 2 '
. 'WHERE SingerId = 1 and AlbumId = 3'
],
]);
$t->commit();
$rowCounts = count($result->rowCounts());
printf('Executed %s SQL statements using Batch DML.' . PHP_EOL,
$rowCounts);
});
}Python
transaction.batch_update()을 사용하여 여러 DML 문 문자열을 실행합니다.
from google.rpc.code_pb2 import OK
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
insert_statement = (
"INSERT INTO Albums "
"(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
"VALUES (1, 3, 'Test Album Title', 10000)"
)
update_statement = (
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 3"
)
def update_albums(transaction):
status, row_cts = transaction.batch_update([insert_statement, update_statement])
if status.code != OK:
# Do handling here.
# Note: the exception will still be raised when
# `commit` is called by `run_in_transaction`.
return
print("Executed {} SQL statements using Batch DML.".format(len(row_cts)))
database.run_in_transaction(update_albums)Ruby
transaction.batch_update을 사용하여 여러 DML 문 문자열을 실행합니다.
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_counts = nil
client.transaction do |transaction|
row_counts = transaction.batch_update do |b|
b.batch_update(
"INSERT INTO Albums " \
"(SingerId, AlbumId, AlbumTitle, MarketingBudget) " \
"VALUES (1, 3, 'Test Album Title', 10000)"
)
b.batch_update(
"UPDATE Albums " \
"SET MarketingBudget = MarketingBudget * 2 " \
"WHERE SingerId = 1 and AlbumId = 3"
)
end
end
statement_count = row_counts.count
puts "Executed #{statement_count} SQL statements using Batch DML."