このページでは、データ操作言語（DML）ステートメントを使用して Spanner データの挿入、更新、削除を行う方法について説明します。DML ステートメントを実行するには、クライアント ライブラリ、Google Cloud コンソール、gcloud コマンドライン ツールを使用します。パーティション化 DML ステートメントは、クライアント ライブラリ、gcloud コマンドライン ツールを使用して実行できます。
DML 構文の詳細については、GoogleSQL 言語データベースのデータ操作言語の構文または PostgreSQL 言語データベースの PostgreSQL データ操作言語をご覧ください。
DML を使用する
Google Cloud コンソール、Google Cloud CLI、クライアント ライブラリでは、DML の 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";
更新を効率化するには、SingerId 列を WHERE 句に含めます。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）を順次実行します。これらは同時に実行されません。ただし、SELECT ステートメントは、読み取り専用オペレーションのため、Spanner で同時に実行される可能性があります。
トランザクション数の制限
DML ステートメントを含むトランザクションには、他のトランザクションと同じ制限があります。大規模な変更を行う場合は、パーティション化 DML の使用を検討してください。
トランザクションの DML ステートメントでミューテーションの数が 80,000 を超えた場合、上限を超えたトランザクションを push する DML ステートメントが
BadUsageエラーを返し、ミューテーションが多すぎることを通知します。トランザクションの DML ステートメントで 100 MB を超えるトランザクションが発生した場合、上限を超えたトランザクションを push する DML ステートメントが
BadUsageエラーを返し、トランザクション サイズの上限を超えていることを通知します。
DML で実行されたミューテーションはクライアントに戻されません。commit 時に commit リクエストに統合され、最大サイズ制限の対象になります。送信する commit リクエストのサイズが小さい場合でも、トランザクションが最大サイズ上限を超える可能性があります。
Google Cloud コンソールでステートメントを実行する
Google Cloud コンソールで DML ステートメントを実行するには、次の手順を行います。
[Spanner インスタンス] ページに移動します。
ツールバーのプルダウン メニューでプロジェクトを選択します。
データベースが含まれているインスタンスの名前をクリックして、[インスタンスの詳細] ページに移動します。
[概要] タブで、データベースの名前をクリックします。[データベースの詳細] ページが表示されます。
[Spanner Studio] をクリックします。
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."returning DML ステートメントを使用してデータを変更する
THEN RETURN 句（GoogleSQL 言語データベース）または RETURNING 句（PostgreSQL 言語データベース）は、変更された行からデータを取得するようなシナリオに適しています。これは、DML ステートメント、デフォルト値、生成された列で指定されていない値を表示する場合に特に便利です。
クライアント ライブラリを使用して returning DML ステートメントを実行には、次の操作を行います。
- 読み取り / 書き込みトランザクションを作成します。
- クライアント ライブラリのメソッドを呼び出してクエリを実行し、returning 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 ステートメントで行った変更は、同じトランザクション内の後続のステートメントで参照できます。この動作はミューテーションを使用した場合と異なります。ミューテーションでは、トランザクションを commit するまで変更結果を参照できません。
Spanner は、DML ステートメントの処理後に制約をチェックします。ミューテーションを使用した場合、Spanner は commit するまでクライアントのバッファにミューテーションを保存し、commit 時に制約のチェックを行います。各ステートメントの後で制約を評価することで、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 コマンドライン ツールを使用します。
パーティション化 DML を使用する
パーティション化 DML は、一括更新と削除、特に定期的なクリーンアップとバックフィル用に設計されています。
Google Cloud CLI を使用してステートメントを実行する
パーティション化 DML ステートメントを実行するには、--enable-partitioned-dml オプションを指定した gcloud spanner databases execute-sql コマンドを使用します。次の例では、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() 関数を使用して、パーティション化 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() メソッドを使用して、パーティション化 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() メソッドを使用して、パーティション化 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() メソッドを使用して、パーティション化 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() メソッドを使用して、パーティション化 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() メソッドを使用して、パーティション化 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() メソッドを使用して、パーティション化 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() メソッドを使用して、パーティション化 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 ステートメントの list を実行します。
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 オブジェクトの array を実行します。
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 ステートメントの list を実行します。
// 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."