변형을 사용하여 데이터 삽입, 업데이트, 삭제

이 페이지에서는 변형을 사용하여 데이터를 삽입, 업데이트, 삭제하는 방법을 설명합니다. 변형이란 Cloud Spanner가 Cloud Spanner 데이터베이스의 여러 행과 테이블에 원자적으로 적용하는 일련의 삽입, 업데이트, 삭제입니다.

gRPC 또는 REST를 사용하여 변형을 커밋할 수도 있지만 클라이언트 라이브러리를 통해 API에 액세스하는 것이 더 일반적입니다.

이 페이지에서는 기본적인 삽입, 업데이트, 삭제 작업을 보여줍니다. 시작하기 가이드에서 더 많은 예를 찾을 수 있습니다.

테이블에 새 행 삽입

C++

InsertMutationBuilder() 함수를 사용하여 데이터를 작성합니다. Client::Commit()는 테이블에 새 행을 추가합니다. 단일 배치의 모든 삽입은 원자적으로 적용됩니다.

이 코드는 데이터를 쓰는 방법을 보여줍니다.

void InsertData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto insert_singers = spanner::InsertMutationBuilder(
                            "Singers", {"SingerId", "FirstName", "LastName"})
                            .EmplaceRow(1, "Marc", "Richards")
                            .EmplaceRow(2, "Catalina", "Smith")
                            .EmplaceRow(3, "Alice", "Trentor")
                            .EmplaceRow(4, "Lea", "Martin")
                            .EmplaceRow(5, "David", "Lomond")
                            .Build();

  auto insert_albums = spanner::InsertMutationBuilder(
                           "Albums", {"SingerId", "AlbumId", "AlbumTitle"})
                           .EmplaceRow(1, 1, "Total Junk")
                           .EmplaceRow(1, 2, "Go, Go, Go")
                           .EmplaceRow(2, 1, "Green")
                           .EmplaceRow(2, 2, "Forever Hold Your Peace")
                           .EmplaceRow(2, 3, "Terrified")
                           .Build();

  auto commit_result =
      client.Commit(spanner::Mutations{insert_singers, insert_albums});
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Insert was successful [spanner_insert_data]\n";
}

C#

connection.CreateInsertCommand() 메서드를 사용하여 데이터를 삽입할 수 있습니다. 이 메서드는 테이블에 행을 삽입하는 새 SpannerCommand를 만듭니다. SpannerCommand.ExecuteNonQueryAsync() 메서드는 테이블에 새 행을 추가합니다.

이 코드는 데이터를 삽입하는 방법을 보여줍니다.

public class Singer
{
    public int SingerId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Album
{
    public int SingerId { get; set; }
    public int AlbumId { get; set; }
    public string AlbumTitle { get; set; }
}
public static async Task InsertSampleDataAsync(
    string projectId, string instanceId, string databaseId)
{
    const int firstSingerId = 1;
    const int secondSingerId = 2;
    string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}"
    + $"/databases/{databaseId}";
    List<Singer> singers = new List<Singer>
    {
        new Singer { SingerId = firstSingerId, FirstName = "Marc",
            LastName = "Richards" },
        new Singer { SingerId = secondSingerId, FirstName = "Catalina",
            LastName = "Smith" },
        new Singer { SingerId = 3, FirstName = "Alice",
            LastName = "Trentor" },
        new Singer { SingerId = 4, FirstName = "Lea",
            LastName = "Martin" },
        new Singer { SingerId = 5, FirstName = "David",
            LastName = "Lomond" },
    };
    List<Album> albums = new List<Album>
    {
        new Album { SingerId = firstSingerId, AlbumId = 1,
            AlbumTitle = "Total Junk" },
        new Album { SingerId = firstSingerId, AlbumId = 2,
            AlbumTitle = "Go, Go, Go" },
        new Album { SingerId = secondSingerId, AlbumId = 1,
            AlbumTitle = "Green" },
        new Album { SingerId = secondSingerId, AlbumId = 2,
            AlbumTitle = "Forever Hold your Peace" },
        new Album { SingerId = secondSingerId, AlbumId = 3,
            AlbumTitle = "Terrified" },
    };
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Insert rows into the Singers table.
        var cmd = connection.CreateInsertCommand("Singers",
            new SpannerParameterCollection
            {
                { "SingerId", SpannerDbType.Int64 },
                { "FirstName", SpannerDbType.String },
                { "LastName", SpannerDbType.String }
            });
        await Task.WhenAll(singers.Select(singer =>
        {
            cmd.Parameters["SingerId"].Value = singer.SingerId;
            cmd.Parameters["FirstName"].Value = singer.FirstName;
            cmd.Parameters["LastName"].Value = singer.LastName;
            return cmd.ExecuteNonQueryAsync();
        }));

        // Insert rows into the Albums table.
        cmd = connection.CreateInsertCommand("Albums",
            new SpannerParameterCollection
            {
                { "SingerId", SpannerDbType.Int64 },
                { "AlbumId", SpannerDbType.Int64 },
                { "AlbumTitle", SpannerDbType.String }
            });
        await Task.WhenAll(albums.Select(album =>
        {
            cmd.Parameters["SingerId"].Value = album.SingerId;
            cmd.Parameters["AlbumId"].Value = album.AlbumId;
            cmd.Parameters["AlbumTitle"].Value = album.AlbumTitle;
            return cmd.ExecuteNonQueryAsync();
        }));
        Console.WriteLine("Inserted data.");
    }
}

Go

Mutation을 사용하여 데이터를 씁니다. Mutation은 변형 작업의 컨테이너입니다. Mutation은 Cloud Spanner 데이터베이스의 여러 행과 테이블에 원자적으로 적용할 수 있는 일련의 삽입, 업데이트, 삭제 등을 나타냅니다.

Mutation.InsertOrUpdate()INSERT_OR_UPDATE를 사용하여 새 행을 추가하거나 행이 이미 존재하는 경우에는 열 값을 업데이트하는 변형을 생성합니다. 또는 Mutation.Insert() 메서드를 사용하여 새 행을 추가하는 INSERT 변형을 생성합니다.

Client.Apply()는 데이터베이스에 변형을 원자적으로 적용합니다.

이 코드는 데이터를 쓰는 방법을 보여줍니다.


import (
	"context"
	"io"

	"cloud.google.com/go/spanner"
)

func write(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	singerColumns := []string{"SingerId", "FirstName", "LastName"}
	albumColumns := []string{"SingerId", "AlbumId", "AlbumTitle"}
	m := []*spanner.Mutation{
		spanner.InsertOrUpdate("Singers", singerColumns, []interface{}{1, "Marc", "Richards"}),
		spanner.InsertOrUpdate("Singers", singerColumns, []interface{}{2, "Catalina", "Smith"}),
		spanner.InsertOrUpdate("Singers", singerColumns, []interface{}{3, "Alice", "Trentor"}),
		spanner.InsertOrUpdate("Singers", singerColumns, []interface{}{4, "Lea", "Martin"}),
		spanner.InsertOrUpdate("Singers", singerColumns, []interface{}{5, "David", "Lomond"}),
		spanner.InsertOrUpdate("Albums", albumColumns, []interface{}{1, 1, "Total Junk"}),
		spanner.InsertOrUpdate("Albums", albumColumns, []interface{}{1, 2, "Go, Go, Go"}),
		spanner.InsertOrUpdate("Albums", albumColumns, []interface{}{2, 1, "Green"}),
		spanner.InsertOrUpdate("Albums", albumColumns, []interface{}{2, 2, "Forever Hold Your Peace"}),
		spanner.InsertOrUpdate("Albums", albumColumns, []interface{}{2, 3, "Terrified"}),
	}
	_, err = client.Apply(ctx, m)
	return err
}

자바

Mutation 객체를 사용하여 데이터를 씁니다. Mutation 객체는 변형 작업을 위한 컨테이너입니다. Mutation은 Cloud Spanner가 Cloud Spanner 데이터베이스의 여러 행과 테이블에 원자적으로 적용할 수 있는 일련의 삽입, 업데이트, 삭제를 나타냅니다.

Mutation 클래스의 newInsertBuilder() 메서드는 테이블에 새 행을 삽입하는 INSERT 변형을 생성합니다. 행이 이미 존재할 경우 쓰기가 실패합니다. 또는 newInsertOrUpdateBuilder 메서드를 사용하여 INSERT_OR_UPDATE 변형을 생성할 수도 있습니다. 이 변형은 행이 이미 있으면 열 값을 업데이트합니다.

DatabaseClient 클래스의 write() 메서드는 변형을 씁니다. 단일 배치의 모든 변형은 원자적으로 적용됩니다.

이 코드는 데이터를 쓰는 방법을 보여줍니다.

static final List<Singer> SINGERS =
    Arrays.asList(
        new Singer(1, "Marc", "Richards"),
        new Singer(2, "Catalina", "Smith"),
        new Singer(3, "Alice", "Trentor"),
        new Singer(4, "Lea", "Martin"),
        new Singer(5, "David", "Lomond"));

static final List<Album> ALBUMS =
    Arrays.asList(
        new Album(1, 1, "Total Junk"),
        new Album(1, 2, "Go, Go, Go"),
        new Album(2, 1, "Green"),
        new Album(2, 2, "Forever Hold Your Peace"),
        new Album(2, 3, "Terrified"));
static void writeExampleData(DatabaseClient dbClient) {
  List<Mutation> mutations = new ArrayList<>();
  for (Singer singer : SINGERS) {
    mutations.add(
        Mutation.newInsertBuilder("Singers")
            .set("SingerId")
            .to(singer.singerId)
            .set("FirstName")
            .to(singer.firstName)
            .set("LastName")
            .to(singer.lastName)
            .build());
  }
  for (Album album : ALBUMS) {
    mutations.add(
        Mutation.newInsertBuilder("Albums")
            .set("SingerId")
            .to(album.singerId)
            .set("AlbumId")
            .to(album.albumId)
            .set("AlbumTitle")
            .to(album.albumTitle)
            .build());
  }
  dbClient.write(mutations);
}

Node.js

Table 객체를 사용하여 데이터를 씁니다. Table.insert() 메서드는 테이블에 새 행을 추가합니다. 단일 배치의 모든 삽입은 원자적으로 적용됩니다.

이 코드는 데이터를 쓰는 방법을 보여줍니다.

// 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);

// Instantiate Spanner table objects
const singersTable = database.table('Singers');
const albumsTable = database.table('Albums');

// Inserts rows into the Singers table
// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so
// they must be converted to strings before being inserted as INT64s
try {
  await singersTable.insert([
    {SingerId: '1', FirstName: 'Marc', LastName: 'Richards'},
    {SingerId: '2', FirstName: 'Catalina', LastName: 'Smith'},
    {SingerId: '3', FirstName: 'Alice', LastName: 'Trentor'},
    {SingerId: '4', FirstName: 'Lea', LastName: 'Martin'},
    {SingerId: '5', FirstName: 'David', LastName: 'Lomond'},
  ]);

  await albumsTable.insert([
    {SingerId: '1', AlbumId: '1', AlbumTitle: 'Total Junk'},
    {SingerId: '1', AlbumId: '2', AlbumTitle: 'Go, Go, Go'},
    {SingerId: '2', AlbumId: '1', AlbumTitle: 'Green'},
    {SingerId: '2', AlbumId: '2', AlbumTitle: 'Forever Hold your Peace'},
    {SingerId: '2', AlbumId: '3', AlbumTitle: 'Terrified'},
  ]);

  console.log('Inserted data.');
} catch (err) {
  console.error('ERROR:', err);
} finally {
  await database.close();
}

PHP

Database::insertBatch 메서드를 사용하여 데이터를 씁니다. insertBatch는 테이블에 새 행을 추가합니다. 단일 배치의 모든 삽입은 원자적으로 적용됩니다.

이 코드는 데이터를 쓰는 방법을 보여줍니다.

use Google\Cloud\Spanner\SpannerClient;

/**
 * Inserts sample data into the given database.
 *
 * 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($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $operation = $database->transaction(['singleUse' => true])
        ->insertBatch('Singers', [
            ['SingerId' => 1, 'FirstName' => 'Marc', 'LastName' => 'Richards'],
            ['SingerId' => 2, 'FirstName' => 'Catalina', 'LastName' => 'Smith'],
            ['SingerId' => 3, 'FirstName' => 'Alice', 'LastName' => 'Trentor'],
            ['SingerId' => 4, 'FirstName' => 'Lea', 'LastName' => 'Martin'],
            ['SingerId' => 5, 'FirstName' => 'David', 'LastName' => 'Lomond'],
        ])
        ->insertBatch('Albums', [
            ['SingerId' => 1, 'AlbumId' => 1, 'AlbumTitle' => 'Total Junk'],
            ['SingerId' => 1, 'AlbumId' => 2, 'AlbumTitle' => 'Go, Go, Go'],
            ['SingerId' => 2, 'AlbumId' => 1, 'AlbumTitle' => 'Green'],
            ['SingerId' => 2, 'AlbumId' => 2, 'AlbumTitle' => 'Forever Hold Your Peace'],
            ['SingerId' => 2, 'AlbumId' => 3, 'AlbumTitle' => 'Terrified']
        ])
        ->commit();

    print('Inserted data.' . PHP_EOL);
}

Python

Batch 객체를 사용하여 데이터를 씁니다. Batch 객체는 변형 작업을 위한 컨테이너입니다. 변형은 Cloud Spanner 데이터베이스의 여러 행과 테이블에 원자적으로 적용할 수 있는 일련의 삽입, 업데이트, 삭제 등을 나타냅니다.

Batch 클래스의 insert() 메서드는 하나 이상의 삽입 변형을 배치에 추가하는 데 사용됩니다. 단일 배치의 모든 변형은 원자적으로 적용됩니다.

이 코드는 데이터를 쓰는 방법을 보여줍니다.

def insert_data(instance_id, database_id):
    """Inserts sample data into the given database.

    The database and table must already exist and can be created using
    `create_database`.
    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.batch() as batch:
        batch.insert(
            table="Singers",
            columns=("SingerId", "FirstName", "LastName"),
            values=[
                (1, u"Marc", u"Richards"),
                (2, u"Catalina", u"Smith"),
                (3, u"Alice", u"Trentor"),
                (4, u"Lea", u"Martin"),
                (5, u"David", u"Lomond"),
            ],
        )

        batch.insert(
            table="Albums",
            columns=("SingerId", "AlbumId", "AlbumTitle"),
            values=[
                (1, 1, u"Total Junk"),
                (1, 2, u"Go, Go, Go"),
                (2, 1, u"Green"),
                (2, 2, u"Forever Hold Your Peace"),
                (2, 3, u"Terrified"),
            ],
        )

    print("Inserted data.")

Ruby

Client 객체를 사용하여 데이터를 씁니다. Client#commit 메서드는 데이터베이스의 열, 행, 테이블에 걸쳐 단일 논리적 시점에서 원자적으로 실행되는 쓰기 트랜잭션을 만들고 커밋합니다.

이 코드는 데이터를 쓰는 방법을 보여줍니다.

# 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

client.commit do |c|
  c.insert "Singers", [
    { SingerId: 1, FirstName: "Marc",     LastName: "Richards" },
    { SingerId: 2, FirstName: "Catalina", LastName: "Smith"    },
    { SingerId: 3, FirstName: "Alice",    LastName: "Trentor"  },
    { SingerId: 4, FirstName: "Lea",      LastName: "Martin"   },
    { SingerId: 5, FirstName: "David",    LastName: "Lomond"   }
  ]
  c.insert "Albums", [
    { SingerId: 1, AlbumId: 1, AlbumTitle: "Total Junk"              },
    { SingerId: 1, AlbumId: 2, AlbumTitle: "Go, Go, Go"              },
    { SingerId: 2, AlbumId: 1, AlbumTitle: "Green"                   },
    { SingerId: 2, AlbumId: 2, AlbumTitle: "Forever Hold Your Peace" },
    { SingerId: 2, AlbumId: 3, AlbumTitle: "Terrified"               }
  ]
end

puts "Inserted data"

테이블의 행 업데이트

Albums(1, 1) 판매량이 예상보다 적다고 가정해 보겠습니다. 이에 따라 Albums(2, 2)의 마케팅 예산 중 $200,000를 Albums(1, 1)로 옮기려고 합니다. 단, Albums(2, 2)의 예산에서 이 금액을 사용할 수 있어야 합니다.

새 값을 쓸지 여부를 결정하려면 테이블의 데이터를 읽어야 하므로 읽기-쓰기 트랜잭션을 사용하여 원자적으로 읽기 및 쓰기를 수행해야 합니다.

C++

Transaction() 함수를 사용하여 클라이언트에 대해 트랜잭션을 실행합니다.

다음은 트랜잭션을 실행하는 코드입니다.

void ReadWriteTransaction(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  using ::google::cloud::StatusOr;

  // A helper to read a single album MarketingBudget.
  auto get_current_budget =
      [](spanner::Client client, spanner::Transaction txn,
         std::int64_t singer_id,
         std::int64_t album_id) -> StatusOr<std::int64_t> {
    auto key = spanner::KeySet().AddKey(spanner::MakeKey(singer_id, album_id));
    auto rows = client.Read(std::move(txn), "Albums", std::move(key),
                            {"MarketingBudget"});
    using RowType = std::tuple<std::int64_t>;
    auto row = spanner::GetSingularRow(spanner::StreamOf<RowType>(rows));
    if (!row) return std::move(row).status();
    return std::get<0>(*std::move(row));
  };

  auto commit = client.Commit(
      [&client, &get_current_budget](
          spanner::Transaction const& txn) -> StatusOr<spanner::Mutations> {
        auto b1 = get_current_budget(client, txn, 1, 1);
        if (!b1) return std::move(b1).status();
        auto b2 = get_current_budget(client, txn, 2, 2);
        if (!b2) return std::move(b2).status();
        std::int64_t transfer_amount = 200000;

        return spanner::Mutations{
            spanner::UpdateMutationBuilder(
                "Albums", {"SingerId", "AlbumId", "MarketingBudget"})
                .EmplaceRow(1, 1, *b1 + transfer_amount)
                .EmplaceRow(2, 2, *b2 - transfer_amount)
                .Build()};
      });

  if (!commit) throw std::runtime_error(commit.status().message());
  std::cout << "Transfer was successful [spanner_read_write_transaction]\n";
}

C#

.NET Standard 2.0(또는 .NET 4.5) 이상의 경우, .NET Framework의 TransactionScope()를 사용하여 트랜잭션을 실행할 수 있습니다. 지원되는 모든 .NET 버전에서 SpannerConnection.BeginTransactionAsync의 결과를 SpannerCommandTransaction 속성으로 설정하여 트랜잭션을 생성할 수 있습니다.

이 트랜잭션을 실행하는 방법은 다음 두 가지입니다.

.NET Standard 2.0

public static async Task ReadWriteWithTransactionAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    // This sample transfers 200,000 from the MarketingBudget
    // field of the second Album to the first Album. Make sure to run
    // the addColumn and writeDataToNewColumn samples first,
    // in that order.

    string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}"
    + $"/databases/{databaseId}";

    using (TransactionScope scope = new TransactionScope(
        TransactionScopeAsyncFlowOption.Enabled))
    {
        decimal transferAmount = 200000;
        decimal secondBudget = 0;
        decimal firstBudget = 0;

        // Create connection to Cloud Spanner.
        using (var connection =
            new SpannerConnection(connectionString))
        {
            // Create statement to select the second album's data.
            var cmdLookup = connection.CreateSelectCommand(
            "SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
            // Excecute the select query.
            using (var reader = await cmdLookup.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    // Read the second album's budget.
                    secondBudget =
                      reader.GetFieldValue<decimal>("MarketingBudget");
                    // Confirm second Album's budget is sufficient and
                    // if not raise an exception. Raising an exception
                    // will automatically roll back the transaction.
                    if (secondBudget < transferAmount)
                    {
                        throw new Exception("The second album's "
                            + $"budget {secondBudget} "
                            + "is less than the "
                            + "amount to transfer.");
                    }
                }
            }
            // Read the first album's budget.
            cmdLookup = connection.CreateSelectCommand(
            "SELECT * FROM Albums WHERE SingerId = 1 and AlbumId = 1");
            using (var reader = await cmdLookup.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    firstBudget =
                      reader.GetFieldValue<decimal>("MarketingBudget");
                }
            }

            // Specify update command parameters.
            var cmd = connection.CreateUpdateCommand("Albums",
                new SpannerParameterCollection {
                {"SingerId", SpannerDbType.Int64},
                {"AlbumId", SpannerDbType.Int64},
                {"MarketingBudget", SpannerDbType.Int64},
            });
            // Update second album to remove the transfer amount.
            secondBudget -= transferAmount;
            cmd.Parameters["SingerId"].Value = 2;
            cmd.Parameters["AlbumId"].Value = 2;
            cmd.Parameters["MarketingBudget"].Value = secondBudget;
            await cmd.ExecuteNonQueryAsync();
            // Update first album to add the transfer amount.
            firstBudget += transferAmount;
            cmd.Parameters["SingerId"].Value = 1;
            cmd.Parameters["AlbumId"].Value = 1;
            cmd.Parameters["MarketingBudget"].Value = firstBudget;
            await cmd.ExecuteNonQueryAsync();
            scope.Complete();
            Console.WriteLine("Transaction complete.");
        }
    }
}

.NET Standard 1.5

public static async Task ReadWriteWithTransactionCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    // This sample transfers 200,000 from the MarketingBudget
    // field of the second Album to the first Album. Make sure to run
    // the addColumn and writeDataToNewColumn samples first,
    // in that order.
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    decimal transferAmount = 200000;
    decimal secondBudget = 0;
    decimal firstBudget = 0;

    Console.WriteLine(".NetCore API sample.");

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Create a readwrite transaction that we'll assign
        // to each SpannerCommand.
        using (var transaction =
                await connection.BeginTransactionAsync())
        {
            // Create statement to select the second album's data.
            var cmdLookup = connection.CreateSelectCommand(
             "SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
            cmdLookup.Transaction = transaction;
            // Excecute the select query.
            using (var reader = await cmdLookup.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    // Read the second album's budget.
                    secondBudget =
                       reader.GetFieldValue<decimal>("MarketingBudget");
                    // Confirm second Album's budget is sufficient and
                    // if not raise an exception. Raising an exception
                    // will automatically roll back the transaction.
                    if (secondBudget < transferAmount)
                    {
                        throw new Exception("The second album's "
                                + $"budget {secondBudget} "
                                + "contains less than the "
                                + "amount to transfer.");
                    }
                }
            }
            // Read the first album's budget.
            cmdLookup = connection.CreateSelectCommand(
             "SELECT * FROM Albums WHERE SingerId = 1 and AlbumId = 1");
            cmdLookup.Transaction = transaction;
            using (var reader = await cmdLookup.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    firstBudget =
                      reader.GetFieldValue<decimal>("MarketingBudget");
                }
            }

            // Specify update command parameters.
            var cmd = connection.CreateUpdateCommand("Albums",
                new SpannerParameterCollection
                {
                    {"SingerId", SpannerDbType.Int64},
                    {"AlbumId", SpannerDbType.Int64},
                    {"MarketingBudget", SpannerDbType.Int64},
                });
            cmd.Transaction = transaction;
            // Update second album to remove the transfer amount.
            secondBudget -= transferAmount;
            cmd.Parameters["SingerId"].Value = 2;
            cmd.Parameters["AlbumId"].Value = 2;
            cmd.Parameters["MarketingBudget"].Value = secondBudget;
            await cmd.ExecuteNonQueryAsync();
            // Update first album to add the transfer amount.
            firstBudget += transferAmount;
            cmd.Parameters["SingerId"].Value = 1;
            cmd.Parameters["AlbumId"].Value = 1;
            cmd.Parameters["MarketingBudget"].Value = firstBudget;
            await cmd.ExecuteNonQueryAsync();

            await transaction.CommitAsync();
        }
        Console.WriteLine("Transaction complete.");
    }
}

Go

읽기-쓰기 트랜잭션의 컨텍스트에서 작업을 실행하기 위해 ReadWriteTransaction 유형을 사용합니다. Client.ReadWriteTransaction()ReadWriteTransaction 객체를 반환합니다.

이 샘플은 ReadWriteTransaction.ReadRow()을 사용하여 데이터 행을 검색합니다.

또한 트랜잭션이 커밋될 때 적용될 업데이트 세트에 변형 목록을 추가하는 ReadWriteTransaction.BufferWrite()도 사용합니다.

이 샘플은 Cloud Spanner 테이블 또는 색인에서 row key를 나타내는 Key 유형도 사용합니다.


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
)

func writeWithTransaction(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 {
		getBudget := func(key spanner.Key) (int64, error) {
			row, err := txn.ReadRow(ctx, "Albums", key, []string{"MarketingBudget"})
			if err != nil {
				return 0, err
			}
			var budget int64
			if err := row.Column(0, &budget); err != nil {
				return 0, err
			}
			return budget, nil
		}
		album2Budget, err := getBudget(spanner.Key{2, 2})
		if err != nil {
			return err
		}
		const transferAmt = 200000
		if album2Budget >= transferAmt {
			album1Budget, err := getBudget(spanner.Key{1, 1})
			if err != nil {
				return err
			}
			album1Budget += transferAmt
			album2Budget -= transferAmt
			cols := []string{"SingerId", "AlbumId", "MarketingBudget"}
			txn.BufferWrite([]*spanner.Mutation{
				spanner.Update("Albums", cols, []interface{}{1, 1, album1Budget}),
				spanner.Update("Albums", cols, []interface{}{2, 2, album2Budget}),
			})
			fmt.Fprintf(w, "Moved %d from Album2's MarketingBudget to Album1's.", transferAmt)
		}
		return nil
	})
	return err
}

자바

읽기-쓰기 트랜잭션의 컨텍스트에서 작업을 실행하기 위해 TransactionRunner 인터페이스를 사용합니다. 이 인터페이스는 필요한 경우 재시도를 포함해 읽기-쓰기 트랜잭션을 실행하는 데 사용되는 run() 메서드를 포함하고 있습니다. DatabaseClient 클래스의 readWriteTransaction 메서드는 단일 논리 트랜잭션을 실행하기 위한 TransactionRunner 객체를 반환합니다.

TransactionRunner.TransactionCallable 클래스는 트랜잭션 1회 시도를 수행하기 위한 run() 메서드를 포함합니다. run()은 트랜잭션 컨텍스트인 TransactionContext 객체를 사용합니다.

이 샘플은 readRow() 호출의 결과를 저장하기에 편리한 Struct 클래스를 사용합니다. 이 샘플은 Cloud Spanner 테이블 또는 색인에서 row key를 나타내는 Key 클래스도 사용합니다.

다음은 트랜잭션을 실행하는 코드입니다.

static void writeWithTransaction(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              // Transfer marketing budget from one album to another. We do it in a transaction to
              // ensure that the transfer is atomic.
              Struct row =
                  transaction.readRow("Albums", Key.of(2, 2), Arrays.asList("MarketingBudget"));
              long album2Budget = row.getLong(0);
              // Transaction will only be committed if this condition still holds at the time of
              // commit. Otherwise it will be aborted and the callable will be rerun by the
              // client library.
              long transfer = 200000;
              if (album2Budget >= transfer) {
                long album1Budget =
                    transaction
                        .readRow("Albums", Key.of(1, 1), Arrays.asList("MarketingBudget"))
                        .getLong(0);
                album1Budget += transfer;
                album2Budget -= transfer;
                transaction.buffer(
                    Mutation.newUpdateBuilder("Albums")
                        .set("SingerId")
                        .to(1)
                        .set("AlbumId")
                        .to(1)
                        .set("MarketingBudget")
                        .to(album1Budget)
                        .build());
                transaction.buffer(
                    Mutation.newUpdateBuilder("Albums")
                        .set("SingerId")
                        .to(2)
                        .set("AlbumId")
                        .to(2)
                        .set("MarketingBudget")
                        .to(album2Budget)
                        .build());
              }
              return null;
            }
          });
}

Node.js

Database.runTransaction()을 사용하여 트랜잭션을 실행합니다.

다음은 트랜잭션을 실행하는 코드입니다.

// This sample transfers 200,000 from the MarketingBudget field
// of the second Album to the first Album, as long as the second
// Album has enough money in its budget. Make sure to run the
// addColumn and updateData samples first (in that order).

// 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 transferAmount = 200000;

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  let firstBudget, secondBudget;
  const queryOne = {
    columns: ['MarketingBudget'],
    keys: [[2, 2]], // SingerId: 2, AlbumId: 2
  };

  const queryTwo = {
    columns: ['MarketingBudget'],
    keys: [[1, 1]], // SingerId: 1, AlbumId: 1
  };

  Promise.all([
    // Reads the second album's budget
    transaction.read('Albums', queryOne).then(results => {
      // Gets second album's budget
      const rows = results[0].map(row => row.toJSON());
      secondBudget = rows[0].MarketingBudget;
      console.log(`The second album's marketing budget: ${secondBudget}`);

      // Makes sure the second album's budget is large enough
      if (secondBudget < transferAmount) {
        throw new Error(
          `The second album's budget (${secondBudget}) is less than the transfer amount (${transferAmount}).`
        );
      }
    }),

    // Reads the first album's budget
    transaction.read('Albums', queryTwo).then(results => {
      // Gets first album's budget
      const rows = results[0].map(row => row.toJSON());
      firstBudget = rows[0].MarketingBudget;
      console.log(`The first album's marketing budget: ${firstBudget}`);
    }),
  ])
    .then(() => {
      console.log(firstBudget, secondBudget);
      // Transfers the budgets between the albums
      firstBudget += transferAmount;
      secondBudget -= transferAmount;

      console.log(firstBudget, secondBudget);

      // Updates the database
      // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
      // must be converted (back) to strings before being inserted as INT64s.
      transaction.update('Albums', [
        {
          SingerId: '1',
          AlbumId: '1',
          MarketingBudget: firstBudget.toString(),
        },
        {
          SingerId: '2',
          AlbumId: '2',
          MarketingBudget: secondBudget.toString(),
        },
      ]);
    })
    .then(() => {
      // Commits the transaction and send the changes to the database
      return transaction.commit();
    })
    .then(() => {
      console.log(
        `Successfully executed read-write transaction to transfer ${transferAmount} from Album 2 to Album 1.`
      );
    })
    .catch(err => {
      console.error('ERROR:', err);
    })
    .then(() => {
      transaction.end();
      // Closes the database when finished
      return database.close();
    });
});

PHP

Database::runTransaction을 사용하여 트랜잭션을 실행합니다.

다음은 트랜잭션을 실행하는 코드입니다.

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
use UnexpectedValueException;

/**
 * Performs a read-write transaction to update two sample records in the
 * database.
 *
 * This will transfer 200,000 from the `MarketingBudget` field for the second
 * Album to the first Album. If the `MarketingBudget` for the second Album is
 * too low, it will raise an exception.
 *
 * Before running this sample, you will need to run the `update_data` sample
 * to populate the fields.
 * Example:
 * ```
 * read_write_transaction($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_write_transaction($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $transferAmount = 200000;

        // Read the second album's budget.
        $secondAlbumKey = [2, 2];
        $secondAlbumKeySet = $spanner->keySet(['keys' => [$secondAlbumKey]]);
        $secondAlbumResult = $t->read(
            'Albums',
            $secondAlbumKeySet,
            ['MarketingBudget'],
            ['limit' => 1]
        );

        $firstRow = $secondAlbumResult->rows()->current();
        $secondAlbumBudget = $firstRow['MarketingBudget'];
        if ($secondAlbumBudget < $transferAmount) {
            // Throwing an exception will automatically roll back the transaction.
            throw new UnexpectedValueException(
                'The second album\'s budget is lower than the transfer amount: ' . $transferAmount
            );
        }

        $firstAlbumKey = [1, 1];
        $firstAlbumKeySet = $spanner->keySet(['keys' => [$firstAlbumKey]]);
        $firstAlbumResult = $t->read(
            'Albums',
            $firstAlbumKeySet,
            ['MarketingBudget'],
            ['limit' => 1]
        );

        // Read the first album's budget.
        $firstRow = $firstAlbumResult->rows()->current();
        $firstAlbumBudget = $firstRow['MarketingBudget'];

        // Update the budgets.
        $secondAlbumBudget -= $transferAmount;
        $firstAlbumBudget += $transferAmount;
        printf('Setting first album\'s budget to %s and the second album\'s ' .
            'budget to %s.' . PHP_EOL, $firstAlbumBudget, $secondAlbumBudget);

        // Update the rows.
        $t->updateBatch('Albums', [
            ['SingerId' => 1, 'AlbumId' => 1, 'MarketingBudget' => $firstAlbumBudget],
            ['SingerId' => 2, 'AlbumId' => 2, 'MarketingBudget' => $secondAlbumBudget],
        ]);

        // Commit the transaction!
        $t->commit();

        print('Transaction complete.' . PHP_EOL);
    });
}

Python

Database 클래스의 run_in_transaction() 메서드를 사용하여 트랜잭션을 실행합니다.

다음은 트랜잭션을 실행하는 코드입니다.

def read_write_transaction(instance_id, database_id):
    """Performs a read-write transaction to update two sample records in the
    database.

    This will transfer 200,000 from the `MarketingBudget` field for the second
    Album to the first Album. If the `MarketingBudget` is too low, it will
    raise an exception.

    Before running this sample, you will need to run the `update_data` sample
    to populate the fields.
    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    def update_albums(transaction):
        # Read the second album budget.
        second_album_keyset = spanner.KeySet(keys=[(2, 2)])
        second_album_result = transaction.read(
            table="Albums",
            columns=("MarketingBudget",),
            keyset=second_album_keyset,
            limit=1,
        )
        second_album_row = list(second_album_result)[0]
        second_album_budget = second_album_row[0]

        transfer_amount = 200000

        if second_album_budget < transfer_amount:
            # Raising an exception will automatically roll back the
            # transaction.
            raise ValueError("The second album doesn't have enough funds to transfer")

        # Read the first album's budget.
        first_album_keyset = spanner.KeySet(keys=[(1, 1)])
        first_album_result = transaction.read(
            table="Albums",
            columns=("MarketingBudget",),
            keyset=first_album_keyset,
            limit=1,
        )
        first_album_row = list(first_album_result)[0]
        first_album_budget = first_album_row[0]

        # Update the budgets.
        second_album_budget -= transfer_amount
        first_album_budget += transfer_amount
        print(
            "Setting first album's budget to {} and the second album's "
            "budget to {}.".format(first_album_budget, second_album_budget)
        )

        # Update the rows.
        transaction.update(
            table="Albums",
            columns=("SingerId", "AlbumId", "MarketingBudget"),
            values=[(1, 1, first_album_budget), (2, 2, second_album_budget)],
        )

    database.run_in_transaction(update_albums)

    print("Transaction complete.")

Ruby

Client 클래스의 transaction 메서드를 사용하여 트랜잭션을 실행합니다.

다음은 트랜잭션을 실행하는 코드입니다.

# 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
transfer_amount = 200_000

client.transaction do |transaction|
  first_album  = transaction.read("Albums", [:MarketingBudget], keys: [[1, 1]]).rows.first
  second_album = transaction.read("Albums", [:MarketingBudget], keys: [[2, 2]]).rows.first

  raise "The second album does not have enough funds to transfer" if second_album[:MarketingBudget] < transfer_amount

  new_first_album_budget  = first_album[:MarketingBudget] + transfer_amount
  new_second_album_budget = second_album[:MarketingBudget] - transfer_amount

  transaction.update "Albums", [
    { SingerId: 1, AlbumId: 1, MarketingBudget: new_first_album_budget  },
    { SingerId: 2, AlbumId: 2, MarketingBudget: new_second_album_budget }
  ]
end

puts "Transaction complete"

테이블에서 행 삭제

각 클라이언트 라이브러리는 다음과 같은 여러 가지 행 삭제 방법을 제공합니다.

  • 한 테이블의 모든 행 삭제
  • 행의 키 열 값을 지정하여 단일 행 삭제
  • 키 범위를 만들어 행 그룹 삭제
  • 인터리브 처리된 테이블의 스키마 정의에 ON DELETE CASCADE가 포함되어 있는 경우 상위 행을 삭제하여 인터리브 처리된 테이블의 행 삭제

C++

클라이언트에 대해 DeleteMutationBuilder() 함수를 사용하여 행을 삭제합니다.

이 코드는 데이터 삭제 방법을 보여줍니다.

void DeleteData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  // Delete the albums with key (2,1) and (2,3).
  auto delete_albums = spanner::DeleteMutationBuilder(
                           "Albums", spanner::KeySet()
                                         .AddKey(spanner::MakeKey(2, 1))
                                         .AddKey(spanner::MakeKey(2, 3)))
                           .Build();

  // Delete some singers using the keys in the range [3, 5]
  auto delete_singers_range =
      spanner::DeleteMutationBuilder(
          "Singers", spanner::KeySet().AddRange(spanner::MakeKeyBoundClosed(3),
                                                spanner::MakeKeyBoundOpen(5)))
          .Build();

  // Deletes remaining rows from the Singers table and the Albums table, because
  // the Albums table is defined with ON DELETE CASCADE.
  auto delete_singers_all =
      spanner::MakeDeleteMutation("Singers", spanner::KeySet::All());

  auto commit_result = client.Commit(spanner::Mutations{
      delete_albums, delete_singers_range, delete_singers_all});
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Delete was successful [spanner_delete_data]\n";
}

C#

connection.CreateDeleteCommand() 메서드를 사용하여 행을 삭제합니다. 이 메서드는 새 SpannerCommand를 만들어 행을 삭제합니다. SpannerCommand.ExecuteNonQueryAsync() 메서드는 테이블에서 행을 삭제합니다.

이 예시에서는 Singers 테이블의 행을 개별적으로 삭제합니다. Albums 테이블이 Singers 테이블에 인터리브 처리되어 있고 ON DELETE CASCADE를 사용하여 정의되었기 때문에 Albums 테이블의 행이 삭제됩니다.

public static async Task DeleteIndividualRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    const int singerId = 2;
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    List<Album> albums = new List<Album>
    {
        new Album { SingerId = singerId, AlbumId = 1, AlbumTitle = "Green" },
        new Album { SingerId = singerId, AlbumId = 3, AlbumTitle = "Terrified" },
    };
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete individual rows from the UpcomingAlbums table.
        await Task.WhenAll(albums.Select(album =>
        {
            var cmd = connection.CreateDeleteCommand(
                "UpcomingAlbums",
                new SpannerParameterCollection
                {
                    { "SingerId", SpannerDbType.Int64, album.SingerId },
                    { "AlbumId", SpannerDbType.Int64, album.AlbumId }
                }
            );
            return cmd.ExecuteNonQueryAsync();
        }));

        Console.WriteLine("Deleted individual rows in UpcomingAlbums.");
    }
}

public static async Task DeleteRangeOfRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete a range of rows from the UpcomingSingers table where the column key is >=3 and <5.
        var cmd = connection.CreateDmlCommand(
           "DELETE FROM UpcomingSingers WHERE SingerId >= 3 AND SingerId < 5");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) deleted from UpcomingSingers.");
    }
}

public static async Task DeleteAllRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete remaining UpcomingSingers rows, which will also delete the remaining
        // UpcomingAlbums rows since it was defined with ON DELETE CASCADE.
        var cmd = connection.CreateDmlCommand(
           "DELETE FROM UpcomingSingers WHERE true");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) deleted from UpcomingSingers.");
    }
}

Go

Mutation을 사용하여 행을 삭제합니다. Mutation.Delete() 메서드를 사용하여 행을 삭제하는 DELETE 변형을 생성합니다. Client.Apply() 메서드가 데이터베이스에 원자적으로 변형을 적용합니다.

이 예시에서는 Albums 테이블의 행을 개별적으로 삭제한 후 KeyRange를 사용하여 Singers 테이블의 모든 행을 삭제합니다.


import (
	"context"
	"io"

	"cloud.google.com/go/spanner"
)

func delete(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	m := []*spanner.Mutation{
		// Delete individual rows.
		spanner.Delete("Albums", spanner.Key{2, 1}),
		spanner.Delete("Albums", spanner.Key{2, 3}),
		// Delete a range of rows where the column key is >=3 and <5.
		spanner.Delete("Singers", spanner.KeyRange{Start: spanner.Key{3}, End: spanner.Key{5}, Kind: spanner.ClosedOpen}),
		// Delete remaining Singers rows, which will also delete the remaining
		// Albums rows because Albums was defined with ON DELETE CASCADE.
		spanner.Delete("Singers", spanner.AllKeys()),
	}
	_, err = client.Apply(ctx, m)
	return err
}

자바

Mutation.delete() 메서드를 사용하여 행을 삭제합니다.

이 예시에서는 KeySet.all() 메서드를 사용하여 Albums 테이블의 모든 행을 삭제합니다. 이 예시에서는 Albums 테이블의 행을 삭제한 후 KeySet.singleKey() 메서드로 생성된 키를 사용하여 Singers 테이블의 행을 개별적으로 삭제합니다.

static void deleteExampleData(DatabaseClient dbClient) {
  List<Mutation> mutations = new ArrayList<>();

  // KeySet.Builder can be used to delete a specific set of rows.
  // Delete the Albums with the key values (2,1) and (2,3).
  mutations.add(
      Mutation.delete(
          "Albums", KeySet.newBuilder().addKey(Key.of(2, 1)).addKey(Key.of(2, 3)).build()));

  // KeyRange can be used to delete rows with a key in a specific range.
  // Delete a range of rows where the column key is >=3 and <5
  mutations.add(
      Mutation.delete("Singers", KeySet.range(KeyRange.closedOpen(Key.of(3), Key.of(5)))));

  // KeySet.all() can be used to delete all the rows in a table.
  // Delete remaining Singers rows, which will also delete the remaining Albums rows since it was
  // defined with ON DELETE CASCADE.
  mutations.add(Mutation.delete("Singers", KeySet.all()));

  dbClient.write(mutations);
  System.out.printf("Records deleted.\n");
}

Node.js

table.deleteRows() 메서드를 사용하여 행을 삭제합니다.

이 예시에서는 table.deleteRows() 메서드를 사용하여 Singers 테이블의 모든 행을 삭제합니다. Albums 테이블이 Singers 테이블에 인터리브 처리되어 있고 ON DELETE CASCADE를 사용하여 정의되었기 때문에 Albums 테이블의 행이 삭제됩니다.

// 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);

// Instantiate Spanner table object
const albumsTable = database.table('Albums');

// Deletes individual rows from the Albums table.
try {
  const keys = [
    [2, 1],
    [2, 3],
  ];
  await albumsTable.deleteRows(keys);
  console.log('Deleted individual rows in Albums.');
} catch (err) {
  console.error('ERROR:', err);
}

// Delete a range of rows where the column key is >=3 and <5
database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: 'DELETE FROM Singers WHERE SingerId >= 3 AND SingerId < 5',
    });
    console.log(`${rowCount} records deleted from Singers.`);
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Deletes remaining rows from the Singers table and the Albums table,
  // because Albums table is defined with ON DELETE CASCADE.
  try {
    // The WHERE clause is required for DELETE statements to prevent
    // accidentally deleting all rows in a table.
    // https://cloud.google.com/spanner/docs/dml-syntax#where_clause
    const [rowCount] = await transaction.runUpdate({
      sql: 'DELETE FROM Singers WHERE true',
    });
    console.log(`${rowCount} records deleted from Singers.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    await database.close();
  }
});

PHP

Database::delete() method을 사용하여 행을 삭제합니다. Database::delete() 메서드 페이지에 예시가 나와 있습니다.

Python

Batch.delete() 메서드를 사용하여 행을 삭제합니다.

이 예시에서는 KeySet 객체를 사용하여 AlbumsSingers 테이블의 모든 행을 개별적으로 삭제합니다.

def delete_data(instance_id, database_id):
    """Deletes sample data from the given database.

    The database, table, and data must already exist and can be created using
    `create_database` and `insert_data`.
    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    # Delete individual rows
    albums_to_delete = spanner.KeySet(keys=[[2, 1], [2, 3]])

    # Delete a range of rows where the column key is >=3 and <5
    singers_range = spanner.KeyRange(start_closed=[3], end_open=[5])
    singers_to_delete = spanner.KeySet(ranges=[singers_range])

    # Delete remaining Singers rows, which will also delete the remaining
    # Albums rows because Albums was defined with ON DELETE CASCADE
    remaining_singers = spanner.KeySet(all_=True)

    with database.batch() as batch:
        batch.delete("Albums", albums_to_delete)
        batch.delete("Singers", singers_to_delete)
        batch.delete("Singers", remaining_singers)

    print("Deleted data.")

Ruby

Client#delete 메서드를 사용하여 행을 삭제합니다. Client#delete 페이지에 예시가 나와 있습니다.

# 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

# Delete individual rows
client.delete "Albums", [[2, 1], [2, 3]]

# Delete a range of rows where the column key is >=3 and <5
key_range = client.range 3, 5, exclude_end: true
client.delete "Singers", key_range

# Delete remaining Singers rows, which will also delete the remaining
# Albums rows because Albums was defined with ON DELETE CASCADE
client.delete "Singers"