ADO.NET で Spanner を使ってみる

目標

このチュートリアルでは、Spanner ADO.NET ドライバを使用して以下の手順について説明します。

  • Spanner のインスタンスとデータベースを作成します。
  • データベースのデータに対し、書き込み、読み取り、SQL クエリの実行を行います。
  • データベース スキーマを更新します。
  • 読み取り / 書き込みトランザクションを使用してデータを更新します。
  • セカンダリ インデックスをデータベースに追加します。
  • インデックスを使用して、データの読み込みと SQL クエリの実行を行います。
  • 読み取り専用トランザクションを使用してデータを取得します。

費用

このチュートリアルでは、Google Cloudの課金対象コンポーネントである Spanner を使用します。Spanner の使用料金については、料金についてのページをご覧ください。

始める前に

設定で説明されている手順を終わらせておきます。この手順では、デフォルトの Google Cloud プロジェクトの作成と設定、課金の有効化、Cloud Spanner API の有効化、Cloud Spanner API の使用に必要な認証情報を取得するための OAuth 2.0 の設定を行います。

特に、gcloud auth application-default login を使用したローカル開発環境の認証情報の設定は必ず行ってください。

ローカルの ADO.NET 環境を準備する

  1. まだインストールしていない場合は、開発マシンに .NET をダウンロードしてインストールします。

  2. ローカルマシンにサンプル リポジトリのクローンを作成します。

    git clone https://github.com/googleapis/dotnet-spanner-entity-framework.git

  3. Spanner ADO.NET ドライバのサンプルコードが含まれているディレクトリに移動します。

    cd dotnet-spanner-entity-framework/spanner-ado-net/spanner-ado-net-getting-started-guide

インスタンスの作成

Spanner を最初に使用する際は、インスタンスを作成する必要があります。インスタンスとは、Spanner データベースによって使用されるリソースの割り当てのことです。インスタンスの作成時には、インスタンス構成を選択してデータの格納場所を指定し、さらに使用するノード数も選択してインスタンスの配信リソースおよびストレージ リソースの量を決定します。

次のいずれかの方法で Spanner インスタンスを作成する方法については、インスタンスを作成するをご覧ください。インスタンスに test-instance という名前を付けることで、このドキュメント内の他のトピックで test-instance という名前のインスタンスを参照している箇所と関連付けて使用できます。

  • Google Cloud CLI
  • Google Cloud コンソール
  • クライアント ライブラリ（C++、C#、Go、Java、Node.js、PHP、Python、Ruby）

サンプル ファイルの確認

サンプル リポジトリには、ADO.NET で Spanner を使用する方法を示すサンプルが含まれています。

SampleRunner.cs ファイルを見ると、Spanner の使用方法を確認できます。このファイルのコードでは、新しいデータベースを作成して使用する方法が示されています。データで使用しているサンプル スキーマは、スキーマとデータモデルのページにあります。

データベースを作成する

GoogleSQL

gcloud spanner databases create example-db --instance=test-instance

PostgreSQL

gcloud spanner databases create example-db --instance=test-instance \
  --database-dialect=POSTGRESQL

次のように表示されます。

Creating database...done.

テーブルを作成する

次のコードを実行すると、データベースに 2 つのテーブルが作成されます。

GoogleSQL

public static async Task CreateTables(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Create two tables in one batch on Spanner.
    var batch = connection.CreateBatch();
    batch.BatchCommands.Add("CREATE TABLE Singers (" +
                            "  SingerId   INT64 NOT NULL, " +
                            "  FirstName  STRING(1024), " +
                            "  LastName   STRING(1024), " +
                            "  SingerInfo BYTES(MAX) " +
                            ") PRIMARY KEY (SingerId)");
    batch.BatchCommands.Add("CREATE TABLE Albums ( " +
                            "  SingerId     INT64 NOT NULL, " +
                            "  AlbumId      INT64 NOT NULL, " +
                            "  AlbumTitle   STRING(MAX)" +
                            ") PRIMARY KEY (SingerId, AlbumId), " +
                            "INTERLEAVE IN PARENT Singers ON DELETE CASCADE");
    await batch.ExecuteNonQueryAsync();
    Console.WriteLine("Created Singers & Albums tables");
}

PostgreSQL

public static async Task CreateTables(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Create two tables in one batch on Spanner.
    var batch = connection.CreateBatch();
    batch.BatchCommands.Add("create table singers (" +
                            "  singer_id   bigint not null primary key, " +
                            "  first_name  varchar(1024), " +
                            "  last_name   varchar(1024), " +
                            "  singer_info bytea" +
                            ")");
    batch.BatchCommands.Add("create table albums (" +
                            "  singer_id     bigint not null, " +
                            "  album_id      bigint not null, " +
                            "  album_title   varchar, " +
                            "  primary key (singer_id, album_id)" +
                            ") interleave in parent singers on delete cascade");
    await batch.ExecuteNonQueryAsync();
    Console.WriteLine("Created Singers & Albums tables");
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run createtables projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run createtablespg projects/PROJECT_ID/instances/test-instance/databases/example-db

次のステップでは、データベースにデータを書き込みます。

接続を作成する

読み取りまたは書き込みを行うには、Spanner とやり取りするための接続を作成する必要があります。データベース名とその他の接続プロパティは、ADO.NET 接続文字列で指定されます。

GoogleSQL

/// <summary>
/// Create an ADO.NET connection to a Spanner database.
/// </summary>
/// <param name="connectionString">
/// A connection string in the format
/// 'Data Source=projects/my-project/instances/my-instance/databases/my-database'.
/// </param>
public static async Task CreateConnection(string connectionString)
{
    // Use a SpannerConnectionStringBuilder to construct a connection string.
    // The SpannerConnectionStringBuilder contains properties for the most
    // used connection string variables.
    var builder = new SpannerConnectionStringBuilder(connectionString)
    {
        // Sets the default isolation level that should be used for all
        // read/write transactions on this connection.
        DefaultIsolationLevel = IsolationLevel.RepeatableRead,

        // The Options property can be used to set any connection property
        // as a key-value pair.
        Options = "statement_cache_size=2000"
    };

    await using var connection = new SpannerConnection(builder.ConnectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT 'Hello World' as Message";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"Greeting from Spanner: {reader.GetString(0)}");
    }
}

PostgreSQL

/// <summary>
/// Create an ADO.NET connection to a Spanner PostgreSQL database.
/// </summary>
/// <param name="connectionString">
/// A connection string in the format
/// 'Data Source=projects/my-project/instances/my-instance/databases/my-database'.
/// </param>
public static async Task CreateConnection(string connectionString)
{
    // Use a SpannerConnectionStringBuilder to construct a connection string.
    // The SpannerConnectionStringBuilder contains properties for the most
    // used connection string variables.
    var builder = new SpannerConnectionStringBuilder(connectionString)
    {
        // Sets the default isolation level that should be used for all
        // read/write transactions on this connection.
        DefaultIsolationLevel = IsolationLevel.RepeatableRead,

        // The Options property can be used to set any connection property
        // as a key-value pair.
        Options = "statement_cache_size=2000"
    };

    await using var connection = new SpannerConnection(builder.ConnectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT 'Hello World' as Message";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"Greeting from Spanner: {reader.GetString(0)}");
    }
}

DML を使用してデータを書き込む

読み取り / 書き込みトランザクションでデータ操作言語（DML）を使用してデータを挿入できます。

DbCommand#ExecuteNonQuery メソッドを使用して DML ステートメントを実行します。

GoogleSQL

public static async Task WriteDataWithDml(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Add 4 rows in one statement.
    // The ADO.NET driver supports positional query parameters.
    await using var command = connection.CreateCommand();
    command.CommandText = "INSERT INTO Singers (SingerId, FirstName, LastName) " +
                          "VALUES (?, ?, ?), (?, ?, ?), " +
                          "       (?, ?, ?), (?, ?, ?)";
    command.Parameters.Add(12);
    command.Parameters.Add("Melissa");
    command.Parameters.Add("Garcia");

    command.Parameters.Add(13);
    command.Parameters.Add("Russel");
    command.Parameters.Add("Morales");

    command.Parameters.Add(14);
    command.Parameters.Add("Jacqueline");
    command.Parameters.Add("Long");

    command.Parameters.Add(15);
    command.Parameters.Add("Dylan");
    command.Parameters.Add("Shaw");

    var affected = await command.ExecuteNonQueryAsync();
    Console.WriteLine($"{affected} record(s) inserted.");
}

PostgreSQL

public static async Task WriteDataWithDml(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Add 4 rows in one statement.
    // The ADO.NET driver supports positional query parameters.
    await using var command = connection.CreateCommand();
    command.CommandText = "insert into singers (singer_id, first_name, last_name) " +
                          "VALUES (?, ?, ?), (?, ?, ?), " +
                          "       (?, ?, ?), (?, ?, ?)";
    command.Parameters.Add(12);
    command.Parameters.Add("Melissa");
    command.Parameters.Add("Garcia");

    command.Parameters.Add(13);
    command.Parameters.Add("Russel");
    command.Parameters.Add("Morales");

    command.Parameters.Add(14);
    command.Parameters.Add("Jacqueline");
    command.Parameters.Add("Long");

    command.Parameters.Add(15);
    command.Parameters.Add("Dylan");
    command.Parameters.Add("Shaw");

    var affected = await command.ExecuteNonQueryAsync();
    Console.WriteLine($"{affected} record(s) inserted.");
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run dmlwrite projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run dmlwritepg projects/PROJECT_ID/instances/test-instance/databases/example-db

結果は次のようになります。

4 records inserted.

ミューテーションを使用してデータを書き込む

ミューテーションを使ってデータを挿入することもできます。

データを挿入するには、batch.CreateInsertCommand() メソッドを使用します。このメソッドでは、行をテーブルに挿入するための新しい SpannerBatchCommand を作成します。SpannerBatchCommand.ExecuteNonQueryAsync() メソッドを使用すると、テーブルに新しい行を追加できます。

次のコードは、ミューテーションを使用してデータを書き込む方法を示しています。

GoogleSQL

struct Singer
{
    internal long SingerId;
    internal string FirstName;
    internal string LastName;
}

struct Album
{
    internal long SingerId;
    internal long AlbumId;
    internal string Title;
}

public static async Task WriteDataWithMutations(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    Singer[] singers =
    [
        new() {SingerId=1, FirstName = "Marc", LastName = "Richards"},
        new() {SingerId=2, FirstName = "Catalina", LastName = "Smith"},
        new() {SingerId=3, FirstName = "Alice", LastName = "Trentor"},
        new() {SingerId=4, FirstName = "Lea", LastName = "Martin"},
        new() {SingerId=5, FirstName = "David", LastName = "Lomond"},
    ];
    Album[] albums =
    [
        new() {SingerId = 1, AlbumId = 1, Title = "Total Junk"},
        new() {SingerId = 1, AlbumId = 2, Title = "Go, Go, Go"},
        new() {SingerId = 2, AlbumId = 1, Title = "Green"},
        new() {SingerId = 2, AlbumId = 2, Title = "Forever Hold Your Peace"},
        new() {SingerId = 2, AlbumId = 3, Title = "Terrified"},
    ];
    var batch = connection.CreateBatch();
    foreach (var singer in singers)
    {
        // The name of a parameter must correspond with a column name.
        var command = batch.CreateInsertCommand("Singers");
        command.AddParameter("SingerId", singer.SingerId);
        command.AddParameter("FirstName", singer.FirstName);
        command.AddParameter("LastName", singer.LastName);
        batch.BatchCommands.Add(command);
    }
    foreach (var album in albums)
    {
        // The name of a parameter must correspond with a column name.
        var command = batch.CreateInsertCommand("Albums");
        command.AddParameter("SingerId", album.SingerId);
        command.AddParameter("AlbumId", album.AlbumId);
        command.AddParameter("AlbumTitle", album.Title);
        batch.BatchCommands.Add(command);
    }
    var affected = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Inserted {affected} rows.");
}

PostgreSQL

struct Singer
{
    internal long SingerId;
    internal string FirstName;
    internal string LastName;
}

struct Album
{
    internal long SingerId;
    internal long AlbumId;
    internal string Title;
}

public static async Task WriteDataWithMutations(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    Singer[] singers =
    [
        new() {SingerId=1, FirstName = "Marc", LastName = "Richards"},
        new() {SingerId=2, FirstName = "Catalina", LastName = "Smith"},
        new() {SingerId=3, FirstName = "Alice", LastName = "Trentor"},
        new() {SingerId=4, FirstName = "Lea", LastName = "Martin"},
        new() {SingerId=5, FirstName = "David", LastName = "Lomond"},
    ];
    Album[] albums =
    [
        new() {SingerId = 1, AlbumId = 1, Title = "Total Junk"},
        new() {SingerId = 1, AlbumId = 2, Title = "Go, Go, Go"},
        new() {SingerId = 2, AlbumId = 1, Title = "Green"},
        new() {SingerId = 2, AlbumId = 2, Title = "Forever Hold Your Peace"},
        new() {SingerId = 2, AlbumId = 3, Title = "Terrified"},
    ];
    var batch = connection.CreateBatch();
    foreach (var singer in singers)
    {
        // The name of a parameter must correspond with a column name.
        var command = batch.CreateInsertCommand("singers");
        command.AddParameter("singer_id", singer.SingerId);
        command.AddParameter("first_name", singer.FirstName);
        command.AddParameter("last_name", singer.LastName);
        batch.BatchCommands.Add(command);
    }
    foreach (var album in albums)
    {
        // The name of a parameter must correspond with a column name.
        var command = batch.CreateInsertCommand("albums");
        command.AddParameter("singer_id", album.SingerId);
        command.AddParameter("album_id", album.AlbumId);
        command.AddParameter("album_title", album.Title);
        batch.BatchCommands.Add(command);
    }
    var affected = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Inserted {affected} rows.");
}

write 引数を使用して次の例を実行します。

GoogleSQL

dotnet run write projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run writepg projects/PROJECT_ID/instances/test-instance/databases/example-db

SQL を使用したデータのクエリ

Spanner では、データの読み取り用に SQL インターフェースがサポートされています。このインターフェースにアクセスするには、コマンドラインで Google Cloud CLI を使用するか、プログラムで Spanner ADO.NET ドライバを使用します。

コマンドラインから

Albums テーブルのすべての列から値を読み取るには、次の SQL ステートメントを実行します。

GoogleSQL

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

PostgreSQL

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT singer_id, album_id, album_title FROM albums'

結果は次のようになります。

SingerId AlbumId AlbumTitle
1        1       Total Junk
1        2       Go, Go, Go
2        1       Green
2        2       Forever Hold Your Peace
2        3       Terrified

Spanner ADO.NET ドライバを使用する

コマンドラインで SQL ステートメントを実行するだけでなく、Spanner ADO.NET ドライバを使用して、同じ SQL ステートメントをプログラマティックに実行できます。

SQL クエリの実行には次のメソッドが使用されます。

  • DbCommand クラスの ExecuteReader メソッド: クエリや THEN RETURN 句を含む DML ステートメントなど、行を返す SQL ステートメントを実行します。
  • DbDataReader クラス: SQL ステートメントから返されたデータにアクセスするために使用します。

次の例では、ExecuteReaderAsync メソッドを使用します。

GoogleSQL

public static async Task QueryData(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY SingerId, AlbumId";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
    }
}

PostgreSQL

public static async Task QueryData(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT singer_id, album_id, album_title " +
                          "FROM albums " +
                          "ORDER BY singer_id, album_id";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["singer_id"]} {reader["album_id"]} {reader["album_title"]}");
    }
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run query projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run querypg projects/PROJECT_ID/instances/test-instance/databases/example-db

結果は次のようになります。

1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 Terrified

SQL パラメータを使用したクエリ

頻繁に実行されるクエリがアプリケーションにある場合は、対象のクエリをパラメータ化してパフォーマンスを改善できます。パラメータ クエリをキャッシュに保存して再利用できます。これにより、コンパイルの費用を削減できます。詳細については、クエリ パラメータを使用して、頻繁に実行するクエリを高速化するをご覧ください。

次の例では、WHERE 句のパラメータを使用して、LastName の特定の値を含むレコードをクエリします。

Spanner ADO.NET ドライバは、位置パラメータと名前付きクエリ パラメータの両方をサポートしています。SQL ステートメントの ? は、位置クエリ パラメータを示します。DbCommand の Parameters にクエリ パラメータの値を指定します。次に例を示します。

GoogleSQL

public static async Task QueryDataWithParameter(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT SingerId, FirstName, LastName " +
                          "FROM Singers " +
                          "WHERE LastName = ?";
    command.Parameters.Add("Garcia");
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["SingerId"]} {reader["FirstName"]} {reader["LastName"]}");
    }
}

PostgreSQL

public static async Task QueryDataWithParameter(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT singer_id, first_name, last_name " +
                          "FROM singers " +
                          "WHERE last_name = ?";
    command.Parameters.Add("Garcia");
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["singer_id"]} {reader["first_name"]} {reader["last_name"]}");
    }
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run querywithparameter projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run querywithparameterpg projects/PROJECT_ID/instances/test-instance/databases/example-db

結果は次のようになります。

12 Melissa Garcia

データベース スキーマを更新する

MarketingBudget という列を新たに Albums テーブルに追加する必要があるとします。既存のテーブルに新しい列を追加するには、データベース スキーマの更新が必要です。Spanner では、データベースでのトラフィックの処理中にデータベースのスキーマを更新できます。スキーマの更新では、データベースをオフラインにする必要がなく、テーブル全体あるいは列全体をロックすることもありません。スキーマの更新中もデータベースへのデータの書き込みを続けることができます。サポートされるスキーマの更新とスキーマ変更のパフォーマンスの詳細については、スキーマを更新するをご覧ください。

列の追加

列を追加するには、コマンドラインで Google Cloud CLI を使用するか、プログラムで Spanner ADO.NET ドライバを使用します。

コマンドラインから

テーブルに新しい列を追加するには、次の ALTER TABLE コマンドを使用します。

GoogleSQL

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'

PostgreSQL

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='alter table albums add column marketing_budget bigint'

以下のように表示されます。

Schema updating...done.

Spanner ADO.NET ドライバを使用する

スキーマを変更するには、ExecuteNonQueryAsync メソッドを使用します。

GoogleSQL

public static async Task AddColumn(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64";
    await command.ExecuteNonQueryAsync();

    Console.WriteLine("Added MarketingBudget column");
}

PostgreSQL

public static async Task AddColumn(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "alter table albums add column marketing_budget bigint";
    await command.ExecuteNonQueryAsync();

    Console.WriteLine("Added marketing_budget column");
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run addcolumn projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run addcolumnpg projects/PROJECT_ID/instances/test-instance/databases/example-db

結果は次のようになります。

Added MarketingBudget column.

DDL バッチを実行する

複数のスキーマ変更は 1 つのバッチで実行することをおすすめします。ADO.NET の CreateBatch メソッドを使用してバッチを作成します。次の例では、1 つのバッチで 2 つのテーブルを作成します。

GoogleSQL

public static async Task DdlBatch(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Executing multiple DDL statements as one batch is
    // more efficient than executing each statement individually.
    var batch = connection.CreateBatch();
    batch.BatchCommands.Add(
        "CREATE TABLE Venues (" +
        "  VenueId     INT64 NOT NULL, " +
        "  Name        STRING(1024), " +
        "  Description JSON, " +
        ") PRIMARY KEY (VenueId)");
    batch.BatchCommands.Add(
        "CREATE TABLE Concerts (" +
        "  ConcertId INT64 NOT NULL, " +
        "  VenueId   INT64 NOT NULL, " +
        "  SingerId  INT64 NOT NULL, " +
        "  StartTime TIMESTAMP, " +
        "  EndTime   TIMESTAMP, " +
        "  CONSTRAINT Fk_Concerts_Venues " +
        "    FOREIGN KEY (VenueId) REFERENCES Venues (VenueId), " +
        "  CONSTRAINT Fk_Concerts_Singers " +
        "    FOREIGN KEY (SingerId) REFERENCES Singers (SingerId), " +
        ") PRIMARY KEY (ConcertId)");
    await batch.ExecuteNonQueryAsync();

    Console.WriteLine("Added Venues and Concerts tables");
}

PostgreSQL

public static async Task DdlBatch(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Executing multiple DDL statements as one batch is
    // more efficient than executing each statement individually.
    var batch = connection.CreateBatch();
    batch.BatchCommands.Add(
        "create table venues (" +
        "  venue_id    bigint not null primary key, " +
        "  name        varchar(1024), " +
        "  description jsonb" +
        ")");
    batch.BatchCommands.Add(
        "create table concerts (" +
        "  concert_id bigint not null primary key, " +
        "  venue_id   bigint not null, " +
        "  singer_id  bigint not null, " +
        "  start_time timestamptz, " +
        "  end_time   timestamptz, " +
        "  constraint fk_concerts_venues foreign key " +
        "    (venue_id) references venues (venue_id), " +
        "  constraint fk_concerts_singers foreign key " +
        "    (singer_id) references singers (singer_id)" +
        ")");
    await batch.ExecuteNonQueryAsync();

    Console.WriteLine("Added Venues and Concerts tables");
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run ddlbatch projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run ddlbatchpg projects/PROJECT_ID/instances/test-instance/databases/example-db

結果は次のようになります。

Added Venues and Concerts tables.

新しい列にデータを書き込む

次のコードは、新しい列にデータを書き込みます。MarketingBudget の値を、キーが Albums(1, 1) の行は 100000 に、キーが Albums(2, 2) の行は 500000 に設定します。

GoogleSQL

public static async Task UpdateDataWithMutations(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    (long SingerId, long AlbumId, long MarketingBudget)[] albums = [
        (1L, 1L, 100000L),
        (2L, 2L, 500000L),
    ];
    // Use a batch to update two rows in one round-trip.
    var batch = connection.CreateBatch();
    foreach (var album in albums)
    {
        // This creates a command that will use a mutation to update the row.
        var command = batch.CreateUpdateCommand("Albums");
        command.AddParameter("SingerId", album.SingerId);
        command.AddParameter("AlbumId", album.AlbumId);
        command.AddParameter("MarketingBudget", album.MarketingBudget);
        batch.BatchCommands.Add(command);
    }
    var affected = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Updated {affected} albums.");
}

PostgreSQL

public static async Task UpdateDataWithMutations(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    (long SingerId, long AlbumId, long MarketingBudget)[] albums = [
        (1L, 1L, 100000L),
        (2L, 2L, 500000L),
    ];
    // Use a batch to update two rows in one round-trip.
    var batch = connection.CreateBatch();
    foreach (var album in albums)
    {
        // This creates a command that will use a mutation to update the row.
        var command = batch.CreateUpdateCommand("albums");
        command.AddParameter("singer_id", album.SingerId);
        command.AddParameter("album_id", album.AlbumId);
        command.AddParameter("marketing_budget", album.MarketingBudget);
        batch.BatchCommands.Add(command);
    }
    var affected = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Updated {affected} albums.");
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run update projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run updatepg projects/PROJECT_ID/instances/test-instance/databases/example-db

結果は次のようになります。

Updated 2 albums

さらに、SQL クエリを実行して、書き込んだばかりの値を取得することもできます。

次の例では、ExecuteReaderAsync メソッドを使用してクエリを実行します。

GoogleSQL

public static async Task QueryNewColumn(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT SingerId, AlbumId, MarketingBudget " +
                          "FROM Albums " +
                          "ORDER BY SingerId, AlbumId";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["SingerId"]} {reader["AlbumId"]} {reader["MarketingBudget"]}");
    }
}

PostgreSQL

public static async Task QueryNewColumn(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "select singer_id, album_id, marketing_budget " +
                          "from albums " +
                          "order by singer_id, album_id";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["singer_id"]} {reader["album_id"]} {reader["marketing_budget"]}");
    }
}

このクエリを実行するには、次のコマンドを実行します。

GoogleSQL

dotnet run querymarketingbudget projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run querymarketingbudgetpg projects/PROJECT_ID/instances/test-instance/databases/example-db

次のように表示されます。

1 1 100000
1 2 null
2 1 null
2 2 500000
2 3 null

データの更新

読み取り / 書き込みトランザクションで DML を使用してデータを更新できます。

connection.BeginTransactionAsync() を呼び出して、ADO.NET で読み取り / 書き込みトランザクションを実行します。

GoogleSQL

public static async Task WriteDataWithTransaction(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Transfer marketing budget from one album to another. We do it in a
    // transaction to ensure that the transfer is atomic.
    await using var transaction = await connection.BeginTransactionAsync();

    // The Spanner ADO.NET driver supports both positional and named
    // query parameters. This query uses named query parameters.
    const string selectSql =
        "SELECT MarketingBudget " +
        "FROM Albums " +
        "WHERE SingerId = @singerId and AlbumId = @albumId";
    // Get the marketing_budget of singer 2 / album 2.
    await using var command = connection.CreateCommand();
    command.CommandText = selectSql;
    command.Transaction = transaction;
    command.Parameters.AddWithValue("singerId", 2);
    command.Parameters.AddWithValue("albumId", 2);
    var budget2 = (long) (await command.ExecuteScalarAsync() ?? 0L);

    const long transfer = 20000L;
    // The transaction will only be committed if this condition still holds
    // at the time of commit. Otherwise, the transaction will be aborted.
    if (budget2 >= transfer)
    {
        // Get the marketing_budget of singer 1 / album 1.
        command.Parameters["singerId"].Value = 1;
        command.Parameters["albumId"].Value = 1;
        var budget1 = (long) (await command.ExecuteScalarAsync() ?? 0L);

        // Transfer part of the marketing budget of Album 2 to Album 1.
        budget1 += transfer;
        budget2 -= transfer;
        const string updateSql =
            "UPDATE Albums " +
            "SET MarketingBudget = @budget " +
            "WHERE SingerId = @singerId and AlbumId = @albumId";
        // Create a DML batch and execute it as part of the current transaction.
        var batch = connection.CreateBatch();
        batch.Transaction = transaction;

        // Update the marketing budgets of both Album 1 and Album 2 in a batch.
        (long SingerId, long AlbumId, long MarketingBudget)[] budgets = [
            new (1L, 1L, budget1),
            new (2L, 2L, budget2),
        ];
        foreach (var budget in budgets)
        {
            var batchCommand = batch.CreateBatchCommand();
            batchCommand.CommandText = updateSql;
            var singerIdParameter = batchCommand.CreateParameter();
            singerIdParameter.ParameterName = "singerId";
            singerIdParameter.Value = budget.SingerId;
            batchCommand.Parameters.Add(singerIdParameter);
            var albumIdParameter = batchCommand.CreateParameter();
            albumIdParameter.ParameterName = "albumId";
            albumIdParameter.Value = budget.AlbumId;
            batchCommand.Parameters.Add(albumIdParameter);
            var marketingBudgetParameter = batchCommand.CreateParameter();
            marketingBudgetParameter.ParameterName = "budget";
            marketingBudgetParameter.Value = budget.MarketingBudget;
            batchCommand.Parameters.Add(marketingBudgetParameter);
            batch.BatchCommands.Add(batchCommand);
        }
        var affected = await batch.ExecuteNonQueryAsync();
        // The batch should update 2 rows.
        if (affected != 2)
        {
            await transaction.RollbackAsync();
            throw new InvalidOperationException($"Unexpected num affected: {affected}");
        }
    }
    // Commit the transaction.
    await transaction.CommitAsync();
    Console.WriteLine("Transferred marketing budget from Album 2 to Album 1");
}

PostgreSQL

public static async Task WriteDataWithTransaction(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Transfer marketing budget from one album to another. We do it in a
    // transaction to ensure that the transfer is atomic.
    await using var transaction = await connection.BeginTransactionAsync();

    // The Spanner ADO.NET driver supports both positional and named
    // query parameters. This query uses named query parameters.
    const string selectSql =
        "SELECT marketing_budget " +
        "FROM albums " +
        "WHERE singer_id = $1 and album_id = $2";
    // Get the marketing_budget of singer 2 / album 2.
    await using var command = connection.CreateCommand();
    command.CommandText = selectSql;
    command.Transaction = transaction;
    command.Parameters.AddWithValue("p1", 2);
    command.Parameters.AddWithValue("p2", 2);
    var budget2 = (long) (await command.ExecuteScalarAsync() ?? 0L);

    const long transfer = 20000L;
    // The transaction will only be committed if this condition still holds
    // at the time of commit. Otherwise, the transaction will be aborted.
    if (budget2 >= transfer)
    {
        // Get the marketing_budget of singer 1 / album 1.
        command.Parameters["p1"].Value = 1;
        command.Parameters["p2"].Value = 1;
        var budget1 = (long) (await command.ExecuteScalarAsync() ?? 0L);

        // Transfer part of the marketing budget of Album 2 to Album 1.
        budget1 += transfer;
        budget2 -= transfer;
        const string updateSql =
            "UPDATE albums " +
            "SET marketing_budget = $1 " +
            "WHERE singer_id = $2 and album_id = $3";
        // Create a DML batch and execute it as part of the current transaction.
        var batch = connection.CreateBatch();
        batch.Transaction = transaction;

        // Update the marketing budgets of both Album 1 and Album 2 in a batch.
        (long SingerId, long AlbumId, long MarketingBudget)[] budgets = [
            new (1L, 1L, budget1),
            new (2L, 2L, budget2),
        ];
        foreach (var budget in budgets)
        {
            var batchCommand = batch.CreateBatchCommand();
            batchCommand.CommandText = updateSql;
            var marketingBudgetParameter = batchCommand.CreateParameter();
            marketingBudgetParameter.ParameterName = "p1";
            marketingBudgetParameter.Value = budget.MarketingBudget;
            batchCommand.Parameters.Add(marketingBudgetParameter);
            var singerIdParameter = batchCommand.CreateParameter();
            singerIdParameter.ParameterName = "p2";
            singerIdParameter.Value = budget.SingerId;
            batchCommand.Parameters.Add(singerIdParameter);
            var albumIdParameter = batchCommand.CreateParameter();
            albumIdParameter.ParameterName = "p3";
            albumIdParameter.Value = budget.AlbumId;
            batchCommand.Parameters.Add(albumIdParameter);
            batch.BatchCommands.Add(batchCommand);
        }
        var affected = await batch.ExecuteNonQueryAsync();
        // The batch should update 2 rows.
        if (affected != 2)
        {
            await transaction.RollbackAsync();
            throw new InvalidOperationException($"Unexpected num affected: {affected}");
        }
    }
    // Commit the transaction.
    await transaction.CommitAsync();
    Console.WriteLine("Transferred marketing budget from Album 2 to Album 1");
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run writewithtransactionusingdml projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run writewithtransactionusingdmlpg projects/PROJECT_ID/instances/test-instance/databases/example-db

トランザクション タグとリクエストタグ

Spanner のトランザクションとクエリのトラブルシューティングを行うには、トランザクション タグとリクエストタグを使用します。Transaction オブジェクトにタグを設定してトランザクション タグを送信し、DbCommand オブジェクトにタグを設定してリクエストタグを Spanner に送信できます。次に例を示します。

GoogleSQL

public static async Task Tags(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    const long singerId = 1L;
    const long albumId = 1L;

    await using var transaction = await connection.BeginTransactionAsync();
    // Set a tag on the transaction before executing any statements.
    transaction.Tag = "example-tx-tag";

    await using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.Tag = "query-marketing-budget";
    command.CommandText =
        "SELECT MarketingBudget " +
        "FROM Albums " +
        "WHERE SingerId=? and AlbumId=?";
    command.Parameters.Add(singerId);
    command.Parameters.Add(albumId);
    var budget = (long)(await command.ExecuteScalarAsync() ?? 0L);

    // Reduce the marketing budget by 10% if it is more than 1,000.
    if (budget > 1000)
    {
        budget -= budget / 10;
        await using var updateCommand = connection.CreateCommand();
        updateCommand.Transaction = transaction;
        updateCommand.Tag = "reduce-marketing-budget";
        updateCommand.CommandText =
            "UPDATE Albums SET MarketingBudget=@budget WHERE SingerId=@singerId AND AlbumId=@albumId";
        updateCommand.Parameters.AddWithValue("budget", budget);
        updateCommand.Parameters.AddWithValue("singerId", singerId);
        updateCommand.Parameters.AddWithValue("albumId", albumId);
        await updateCommand.ExecuteNonQueryAsync();
    }
    // Commit the transaction.
    await transaction.CommitAsync();
    Console.WriteLine("Reduced marketing budget");
}

PostgreSQL

public static async Task Tags(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    const long singerId = 1L;
    const long albumId = 1L;

    await using var transaction = await connection.BeginTransactionAsync();
    // Set a tag on the transaction before executing any statements.
    transaction.Tag = "example-tx-tag";

    await using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.Tag = "query-marketing-budget";
    command.CommandText =
        "select marketing_budget " +
        "from albums " +
        "where singer_id=? and album_id=?";
    command.Parameters.Add(singerId);
    command.Parameters.Add(albumId);
    var budget = (long)(await command.ExecuteScalarAsync() ?? 0L);

    // Reduce the marketing budget by 10% if it is more than 1,000.
    if (budget > 1000)
    {
        budget -= budget / 10;
        await using var updateCommand = connection.CreateCommand();
        updateCommand.Transaction = transaction;
        updateCommand.Tag = "reduce-marketing-budget";
        updateCommand.CommandText =
            "update albums set marketing_budget=$1 where singer_id=$2 and album_id=$3";
        updateCommand.Parameters.Add(budget);
        updateCommand.Parameters.Add(singerId);
        updateCommand.Parameters.Add(albumId);
        await updateCommand.ExecuteNonQueryAsync();
    }
    // Commit the transaction.
    await transaction.CommitAsync();
    Console.WriteLine("Reduced marketing budget");
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run tags projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run tagspg projects/PROJECT_ID/instances/test-instance/databases/example-db

読み取り専用トランザクションを使用したデータの取得

同じタイムスタンプで複数の読み取りを実行する場合について考えます。読み取り専用トランザクションはトランザクションの commit 履歴の整合性のあるプレフィックスを監視しているので、アプリケーションは常に整合性のあるデータを取得できます。読み取り専用トランザクションを実行するには、connection.BeginReadOnlyTransactionAsync() を呼び出します。

同じ読み取り専用トランザクションでクエリと読み取りを実行する方法を次に示します。

GoogleSQL

public static async Task ReadOnlyTransaction(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Start a read-only transaction on this connection.
    await using var transaction = await connection.BeginReadOnlyTransactionAsync();

    await using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY SingerId, AlbumId";
    await using (var reader = await command.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(
                $"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
        }
    }

    // Execute another query using the same read-only transaction.
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY AlbumTitle";
    await using (var reader = await command.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(
                $"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
        }
    }

    // End the read-only transaction by calling Commit.
    await transaction.CommitAsync();
}

PostgreSQL

public static async Task ReadOnlyTransaction(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Start a read-only transaction on this connection.
    await using var transaction = await connection.BeginReadOnlyTransactionAsync();

    await using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY SingerId, AlbumId";
    await using (var reader = await command.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(
                $"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
        }
    }

    // Execute another query using the same read-only transaction.
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY AlbumTitle";
    await using (var reader = await command.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(
                $"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
        }
    }

    // End the read-only transaction by calling Commit.
    await transaction.CommitAsync();
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run readonlytransaction projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run readonlytransactionpg projects/PROJECT_ID/instances/test-instance/databases/example-db

結果は次のようになります。

    1 1 Total Junk
    1 2 Go, Go, Go
    2 1 Green
    2 2 Forever Hold Your Peace
    2 3 Terrified
    2 2 Forever Hold Your Peace
    1 2 Go, Go, Go
    2 1 Green
    2 3 Terrified
    1 1 Total Junk

パーティション化 DML

パーティション化されたデータ操作言語（DML）は、次のタイプの一括更新と一括削除用に設計されています。

  • 定期的なクリーンアップとガベージ コレクション。
  • デフォルト値での新しい列のバックフィリング。

GoogleSQL

public static async Task PartitionedDml(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Enable Partitioned DML on this connection.
    await using var command = connection.CreateCommand();
    command.CommandText = "SET AUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC'";
    await command.ExecuteNonQueryAsync();

    // Back-fill a default value for the MarketingBudget column.
    command.CommandText = "UPDATE Albums SET MarketingBudget=0 WHERE MarketingBudget IS NULL";
    var affected = await command.ExecuteNonQueryAsync();

    // Partitioned DML returns the minimum number of records that were affected.
    Console.WriteLine($"Updated at least {affected} albums");

    // Reset the value for AUTOCOMMIT_DML_MODE to its default.
    command.CommandText = "RESET AUTOCOMMIT_DML_MODE";
    await command.ExecuteNonQueryAsync();
}

PostgreSQL

public static async Task PartitionedDml(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Enable Partitioned DML on this connection.
    await using var command = connection.CreateCommand();
    command.CommandText = "set autocommit_dml_mode='partitioned_non_atomic'";
    await command.ExecuteNonQueryAsync();

    // Back-fill a default value for the MarketingBudget column.
    command.CommandText = "update albums set marketing_budget=0 where marketing_budget is null";
    var affected = await command.ExecuteNonQueryAsync();

    // Partitioned DML returns the minimum number of records that were affected.
    Console.WriteLine($"Updated at least {affected} albums");

    // Reset the value for autocommit_dml_mode to its default.
    command.CommandText = "reset autocommit_dml_mode";
    await command.ExecuteNonQueryAsync();
}

次のコマンドを使用してサンプルを実行します。

GoogleSQL

dotnet run pdml projects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

dotnet run pdmlpg projects/PROJECT_ID/instances/test-instance/databases/example-db

クリーンアップ

このチュートリアルで使用したリソースについて Cloud 請求先アカウントに課金されないようにするため、作成したデータベースとインスタンスを削除します。

データベースの削除

インスタンスを削除すると、それに含まれるすべてのデータベースが自動的に削除されます。このステップでは、インスタンスを削除しないでデータベースを削除する方法を示します（インスタンスの料金は引き続き発生します）。

コマンドラインから

gcloud spanner databases delete example-db --instance=test-instance

Google Cloud コンソールの使用

  1. Google Cloud コンソールの [Spanner インスタンス] ページに移動します。

    インスタンス ページに移動

  2. インスタンスをクリックします。

  3. 削除するデータベースをクリックします。

  4. [データベースの詳細] ページで [削除] をクリックします。

  5. データベースを削除することを確認し、[削除] をクリックします。

インスタンスの削除

インスタンスを削除すると、そのインスタンスで作成されたすべてのデータベースが自動的に削除されます。

コマンドラインから

gcloud spanner instances delete test-instance

Google Cloud コンソールの使用

  1. Google Cloud コンソールの [Spanner インスタンス] ページに移動します。

    インスタンス ページに移動

  2. インスタンスをクリックします。

  3. [削除] をクリックします。

  4. インスタンスを削除することを確認し、[削除] をクリックします。

次のステップ