データ操作言語の構文

Cloud Spanner Dynamic Manipulation Language(DML)を使用すると、Cloud Spanner テーブルでデータの更新、挿入、削除を行うことができます。

DML ステートメントの使用方法については、データ操作言語を使用したデータの挿入、更新、削除をご覧ください。また、ミューテーションを使用してデータを変更することもできます。

この例に使用されているテーブル

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  BirthDate  DATE,
  LastUpdated TIMESTAMP,
) PRIMARY KEY(SingerId);

CREATE TABLE Albums (
  SingerId        INT64 NOT NULL,
  AlbumId         INT64 NOT NULL,
  AlbumTitle      STRING(MAX),
  MarketingBudget INT64,
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId  INT64 NOT NULL,
  AlbumId   INT64 NOT NULL,
  TrackId   INT64 NOT NULL,
  SongName  STRING(MAX),
  Duration  INT64,
  SongGenre STRING(25),
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE Concerts (
  VenueId      INT64 NOT NULL,
  SingerId     INT64 NOT NULL,
  ConcertDate  DATE NOT NULL,
  BeginTime    TIMESTAMP,
  EndTime      TIMESTAMP,
  TicketPrices ARRAY<INT64>,
) PRIMARY KEY(VenueId, SingerId, ConcertDate);

CREATE TABLE AckworthSingers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  BirthDate  DATE,
) PRIMARY KEY(SingerId);

構文の表記法

  • 角括弧 [ ] はオプション句を示します。
  • 括弧 ( ) は、リテラルの括弧を示します。
  • 縦線 | は論理 OR を表します。
  • 中括弧 { } は一連のオプションを示します。
  • 省略記号に先立つカンマは、前の項目をカンマで区切ったリストに繰り返すことができることを示しています。item [, ...] は 1 つ以上の項目を示し、[item, ...] は 0 個以上の項目を示します。
  • カンマ , は、リテラルなカンマを示します。
  • 山括弧 <> は、リテラルな山括弧を示します。
  • コロン : は定義を示します。
  • INSERT などの大文字の単語はキーワードです。

INSERT ステートメント

INSERT ステートメントは、新しい行をテーブルに追加する場合に使用します。INSERT ステートメントを使用すると、値の式で指定した 1 つ以上の行を挿入できます。また、クエリによって生成された 0 個以上の行を挿入することもできます。次のステートメントは、テーブルに挿入された行数を返します。

INSERT [INTO] target_name
 (column_name_1 [, ..., column_name_n] )
 input

input:
 VALUES (row_1_column_1_expr [, ..., row_1_column_n_expr ] )
        [, ..., (row_k_column_1_expr [, ..., row_k_column_n_expr ] ) ]
| select_query

expr: value_expression | DEFAULT

INSERT ステートメントは、次の規則に従う必要があります。

  • 列名の順序は任意です。
  • 列のリストには、同じ名前を使用することはできません。
  • 列数が値の数と一致している必要があります。
  • Cloud Spanner は、VALUES 句の値または選択クエリの位置を列リストで照合します。
  • それぞれの値の型は、対応する列と互換性がなければなりません。
  • 値は、スキーマの制約(固有のセカンダリ インデックスなど)に従う必要があります。
  • NULL 以外の列はすべて列リストに含まれ、NULL 以外の値が指定されている必要があります。

ステートメントがルールに準拠していない場合、Cloud Spanner はエラーを返し、ステートメント全体が失敗します。

ステートメントが主キーに基づいて重複行を挿入しようとすると、ステートメント全体が失敗します。

数値タイプの互換性

INSERT ステートメントに追加する数値は、ターゲット列のタイプと互換性が必要です。値が次のいずれかの条件を満たす場合、値の型はターゲット列の型と互換性があります。

  • 数値タイプと列タイプがまったく同じ。たとえば、挿入する値のタイプが INT64 で、列のタイプが INT64 と互換性がある場合などです。
  • Cloud Spanner は、ターゲットのタイプに合わせて値を暗黙的に変換できます。

デフォルト値

列のデフォルト値を挿入するには、DEFAULT キーワードを使用します。Cloud Spanner は、列リストに含まれていない列に NULL のデフォルト値を割り当てます。

INSERT の例

リテラル値を使用した INSERT

次の例では、Singers テーブルに 3 つの行を追加します。

INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES(1, 'Marc', 'Richards'),
      (2, 'Catalina', 'Smith'),
      (3, 'Alice', 'Trentor')

テーブルに次の 3 つの行が新たに追加されます。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

SELECT ステートメントを使用した INSERT

次の例は、入力として SELECT ステートメントを使用して、テーブル間でデータをコピーする方法を示しています。

INSERT INTO Singers (SingerId, FirstName, LastName)
SELECT SingerId, FirstName, LastName
FROM AckworthSingers

Singers テーブルに行がなく、AckworthSingers テーブルに 3 行しかない場合、この 3 行が Singers テーブルに追加されます。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

次の例は、UNNEST を使用して、INSERT コマンドの入力としてテーブルを返す方法を示しています。

INSERT INTO Singers (SingerId, FirstName, LastName)
SELECT *
FROM UNNEST ([(4, 'Lea', 'Martin'),
      (5, 'David', 'Lomond'),
      (6, 'Elena', 'Campbell')])

これらの 3 行が前の例のテーブルに追加され、Singers テーブルには合計で 6 行が追加されます。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

サブクエリを使用した INSERT

次の例はテーブルに行を挿入する方法を示しています。サブクエリにより、1 つの数値が計算されています。

INSERT INTO Singers (SingerId, FirstName)
VALUES (
    4,
    (SELECT FirstName FROM AckworthSingers WHERE SingerId = 4))

次の表は、ステートメント実行前のデータを示しています。

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL

AckworthSingers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL

次の表に、ステートメントの実行後のデータを示します。

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
4 Lea NULL NULL

複数の列を含めるには、複数のサブクエリを使用します。

INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (
    4,
    (SELECT FirstName FROM AckworthSingers WHERE SingerId = 4),
    (SELECT LastName  FROM AckworthSingers WHERE SingerId = 4))

DELETE ステートメント

テーブルから行を削除するには、DELETE ステートメントを使用します。

DELETE [FROM] target_name [[AS] alias]
WHERE condition

WHERE 句

WHERE 句は必須です。これにより、条件を満たさない行は削除されません。テーブル内のすべての行を削除するには、conditiontrue に設定します。

DELETE FROM target_name WHERE true

WHERE 句には、別のテーブルを参照するサブクエリなど、有効な SQL 文を指定できます。

エイリアス

WHERE 句では、target_name に暗黙のエイリアスがあります。このエイリアスを使用すると、target_name で修飾せずに、target_name の列を参照できます。たとえば、ステートメントが DELETE FROM Singers で始まっている場合、WHERE 句内の Singers の任意の列にアクセスできます。次の例では、FirstNameSingers テーブルの列です。

DELETE FROM Singers
WHERE FirstName = 'Alice'

オプションの AS キーワードを使用して明示的なエイリアスを作成することもできます。エイリアスの詳細については、クエリ構文をご覧ください。

DELETE の例

DELETE と WHERE 句

次の DELETE ステートメントは、名前が Alice のすべての歌手を削除します。

DELETE FROM Singers
WHERE FirstName = 'Alice'

次の表は、ステートメント実行前のデータを示しています。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

次の表に、ステートメントの実行後のデータを示します。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL

サブクエリを使用した DELETE

次のステートメントは、名前が AckworthSingers 内にない SINGERS 内の歌手を削除します。

DELETE FROM Singers
WHERE FirstName NOT IN (SELECT FirstName from AckworthSingers)

次の表は、ステートメント実行前のデータを示しています。

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

AckworthSingers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

次の表に、ステートメントの実行後のデータを示します。

Singers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

UPDATE ステートメント

UPDATE ステートメントを使用して、テーブル内の既存の行を更新します。

UPDATE target_name [[AS] alias]
SET update_item [, ...]
WHERE condition

update_item: path_expression = expression | path_expression = DEFAULT

ここで

  • target_name は更新するテーブルの名前です。
  • SET 句は、WHERE 条件が true の各行に実行する update_items のリストです。
  • path_expression は列名です。
  • expression は更新式です。式は、リテラル、SQL 式または SQL サブクエリです。

UPDATE ステートメントは、以下の規則に従う必要があります。

  • 列は SET 句に一度だけ使用できます。
  • SET 句の列は、任意の順序で使用できます。
  • それぞれの値の型は、対応する列と互換性がなければなりません。
  • 値は、スキーマの制約(固有のセカンダリ インデックスや Nullable 以外の列など)に従う必要があります。
  • 結合による更新はサポートされていません。
  • 主キー列は更新できません。

ステートメントがルールに準拠していない場合、Cloud Spanner はエラーを返し、ステートメント全体が失敗します。

SET 句に含まれていない列は変更されません。

列の更新は同時に実行されます。たとえば、1 つの SET 句で 2 つの列値を入れ替えることができます。

SET x = y, y = x

数値タイプの互換性

UPDATE ステートメントで更新される値は、ターゲット列のタイプと互換性が必要です。値が次のいずれかの条件を満たす場合、値の型はターゲット列の型と互換性があります。

  • 数値タイプと列タイプがまったく同じ。たとえば、値の型が INT64 で、列の型も INT64 です。
  • Cloud Spanner は、ターゲットのタイプに合わせて値を暗黙的に変換できます。

デフォルト値

DEFAULT キーワードは、列の値を NULL に設定します。

WHERE 句

WHERE 句は必須です。これにより、条件を満たさない行は更新されません。テーブル内のすべての行を更新するには、conditiontrue に設定します。

WHERE 句には、別のテーブルを参照するサブクエリなど、有効な SQL ブール式を指定できます。

エイリアス

WHERE 句では、target_name に暗黙のエイリアスがあります。このエイリアスを使用すると、target_name で修飾せずに、target_name の列を参照できます。たとえば、ステートメントが UPDATE Singers で始まっている場合、WHERE 句内の Singers の任意の列にアクセスできます。この例では、FirstNameLastNameSingers テーブルの列です。

UPDATE Singers
SET BirthDate = '1990-10-10'
WHERE FirstName = 'Marc' AND LastName = 'Richards'

オプションの AS キーワードを使用して明示的なエイリアスを作成することもできます。エイリアスの詳細については、クエリ構文をご覧ください。

UPDATE の例

リテラル値を含む UPDATE

次の例では、いずれかの行の BirthDate 列を更新して Singers テーブルを更新します。

UPDATE Singers
SET BirthDate = '1990-10-10'
WHERE FirstName = 'Marc' AND LastName = 'Richards'

次の表は、ステートメント実行前のデータを示しています。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

次の表に、ステートメントの実行後のデータを示します。

SingerId FirstName LastName BirthDate
1 Marc Richards 1990-10-10
2 Catalina Smith NULL
3 Alice Trentor NULL

UPDATE ARRAY 列

次の例では、ARRAY 列を更新します。

UPDATE Concerts
SET TicketPrices = [25, 50, 100]
WHERE VenueId = 1

次の表は、ステートメント実行前のデータを示しています。

VenueId SingerId ConcertDate BeginTime EndTime TicketPrices
1 1 NULL NULL NULL NULL
1 2 NULL NULL NULL NULL
2 3 NULL NULL NULL NULL

次の表に、ステートメントの実行後のデータを示します。

VenueId SingerId ConcertDate BeginTime EndTime TicketPrices
1 1 2018-01-01 NULL NULL [25, 50, 100]
1 2 2018-01-01 NULL NULL [25, 50, 100]
2 3 2018-01-01 NULL NULL NULL

バインドされた STRUCT パラメータ

DML ステートメントの WHERE 句の中で、バインドされた STRUCT パラメータを使用できます。次のコード例は、FirstNameLastName によってフィルタリングされた行の LastName を更新します。

C#

public static async Task UpdateUsingDmlWithStructCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    var nameStruct = new SpannerStruct
    {
        { "FirstName", SpannerDbType.String, "Timothy" },
        { "LastName", SpannerDbType.String, "Campbell" },
    };
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

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

        SpannerCommand cmd = connection.CreateDmlCommand(
            "UPDATE Singers SET LastName = 'Grant' "
           + "WHERE STRUCT<FirstName STRING, LastName STRING>"
           + "(FirstName, LastName) = @name");
        cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) updated...");
    }
}

Go

func updateUsingDMLStruct(ctx context.Context, w io.Writer, client *spanner.Client) error {
	_, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		type name struct {
			FirstName string
			LastName  string
		}
		var singerInfo = name{"Timothy", "Campbell"}

		stmt := spanner.Statement{
			SQL: `Update Singers Set LastName = 'Grant'
				WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`,
			Params: map[string]interface{}{"name": singerInfo},
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
		return nil
	})
	return err
}

Java

static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
  Struct name =
      Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
  Statement s =
      Statement.newBuilder(
              "UPDATE Singers SET LastName = 'Grant' "
                  + "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
                  + "= @name")
          .bind("name")
          .to(name)
          .build();
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              long rowCount = transaction.executeUpdate(s);
              System.out.printf("%d record updated.\n", rowCount);
              return null;
            }
          });
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

const nameStruct = Spanner.struct({
  FirstName: 'Timothy',
  LastName: 'Campbell',
});

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Singers SET LastName = 'Grant'
      WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
      params: {
        name: nameStruct,
      },
    });

    console.log(`Successfully updated ${rowCount} record.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Transaction;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;

/**
 * Update data with a DML statement using Structs.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_dml_structs($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $nameValue = (new StructValue)
            ->add('FirstName', 'Timothy')
            ->add('LastName', 'Campbell');
        $nameType = (new StructType)
            ->add('FirstName', Database::TYPE_STRING)
            ->add('LastName', Database::TYPE_STRING);

        $rowCount = $t->executeUpdate(
            "UPDATE Singers SET LastName = 'Grant' "
             . "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
             . "= @name",
            [
                'parameters' => [
                    'name' => $nameValue
                ],
                'types' => [
                    'name' => $nameType
                ]
            ]);
        $t->commit();
        printf('Updated %d row(s).' . PHP_EOL, $rowCount);
    });
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

record_type = param_types.Struct([
    param_types.StructField('FirstName', param_types.STRING),
    param_types.StructField('LastName', param_types.STRING)
])
record_value = ('Timothy', 'Campbell')

def write_with_struct(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Grant' "
        "WHERE STRUCT<FirstName STRING, LastName STRING>"
        "(FirstName, LastName) = @name",
        params={'name': record_value},
        param_types={'name': record_type}
    )
    print("{} record(s) updated.".format(row_ct))

database.run_in_transaction(write_with_struct)

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Singers SET LastName = 'Grant'
     WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
    params: { name: name_struct }
  )
end

puts "#{row_count} record updated."

commit タイムスタンプ

commit タイムスタンプを TIMESTAMP 列に書き込むには、PENDING_COMMIT_TIMESTAMP 関数を使用します。列の allow_commit_timestamp オプションを true に設定する必要があります。次の DML ステートメントは、commit タイムスタンプで Singers テーブルの LastUpdated 列を更新します。

UPDATE Singers SET LastUpdated = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1

DML で commit タイムスタンプを使用する方法については、commit タイムスタンプの作成をご覧ください。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

Cloud Spanner のドキュメント