Spanner に UPSERT(INSERT OR UPDATE) が来ました

概要

RDBMS を使うアプリケーションではよく「このプライマリキーと同じレコードは存在するかもしれないけど、存在した場合は更新して、しなかった場合には追記して欲しい」という処理が発生します。
このような処理は素直に実装する場合には該当のレコードを SELECT で存在確認を行い、その後アプリケーションで SELECT を行って存在確認をしてからその結果で UPDATE か INSERT に処理を分岐させる方法があります。一方で、このような処理に対応するために SQL 文のレベルで対応している RDBMS もあります。このような処理は一般的に UPSERT という名称で呼ばれることが多いです。

具体的な実装例としては SQLite の UPSERT、PostgreSQL の INSERT の ON CONFLICT DO UPDATEや MySQL の INSERT ... ON DUPLICATE UPDATEがその例です。

Spanner は2024年1月23日の機能追加されるまでは DML での UPSERT をサポートしていませんでしたが、UPSERT に相当する INSERT OR UPDATE に対応したことで他の RDBMS からのアプリケーションの移行が容易になりました。

ミューテーションによる insertOrUpdate

Spanner は SQL の DML(INSERT/UPDATE/DELETE)による更新以外に、ミューテーション(Mutation)というより低レイヤーの API により更新などを行う方法があります。
ミューテーションによる更新では今回の変更以前から insertOrUpdate が利用可能でした。この処理は名前の通り、UPSERT 相当の機能を実現できるものです。各言語のクライアントライブラリからも実行が可能です(Goのクライアントでの例)。

このため、UPSERT が必要な場合に、ミューテーションによる更新を行う方法がありました。同一のインスタンスやデータベースに対して DML とミューテーションの併用は利用可能ですが、同一のトランザクション内では混在させることはできません。そのため、ミューテーションによる InsertOrUpdate を利用したい場合は、トランザクション全体でミューテーションを利用する必要がありました。

INSERT OR UPDATE

2024年1月23日のリリースにおいて Google 標準 SQL で INSERT OR UPDATE と INSERT OR IGNORE に対応しました。PostgreSQL 互換 SQL では PostgreSQL と同様に INSERT 文に ON CONFLICT DO UPDATE SET 句をつける構文で同様の機能がサポートされます。

これにより、従来は MySQL や PostgreSQL からアプリケーションを移植する際に UPSERT 部分をアプリケーションロジックの分岐として実装するか、ミューテーションを使う必要がありました。今回のアップデートでよりストレートに実装が可能になりました。

実行例

テーブル定義と事前データは以下のような内容を前提にしています。

spanner> show create table Singers\G
*************************** 1. row ***************************
       Table: Singers
Create Table: CREATE TABLE Singers (
  SingerId INT64 NOT NULL,
  FirstName STRING(1024),
  LastName STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY(SingerId)
1 rows in set (0.49 sec)

spanner> select * from Singers;
+----------+-----------+----------+------------+
| SingerId | FirstName | LastName | SingerInfo |
+----------+-----------+----------+------------+
| 1        | Marc      | Richards | NULL       |
| 2        | Catalina  | Smith    | NULL       |
| 3        | Alice     | Trentor  | NULL       |
+----------+-----------+----------+------------+
3 rows in set (3.78 msecs)

ここに新たにプライマリキーが SingerId=4 のレコードを追記します。続けて、同じプライマリキーを指定して INSERT OR UPDATEを実行してもエラーとならず完了します。

spanner> INSERT OR UPDATE Singers (SingerId, FirstName, LastName) VALUES (4, 'Lea', 'Martin');
Query OK, 1 rows affected (0.08 sec)

spanner> select * from Singers;
+----------+-----------+----------+------------+
| SingerId | FirstName | LastName | SingerInfo |
+----------+-----------+----------+------------+
| 1        | Marc      | Richards | NULL       |
| 2        | Catalina  | Smith    | NULL       |
| 3        | Alice     | Trentor  | NULL       |
| 4        | Lea       | Martin   | NULL       |
+----------+-----------+----------+------------+
4 rows in set (8.46 msecs)

spanner> INSERT OR UPDATE Singers (SingerId, FirstName, LastName) VALUES (4, 'Lea', 'Martin');
Query OK, 1 rows affected (0.03 sec)

spanner> select * from Singers;
+----------+-----------+----------+------------+
| SingerId | FirstName | LastName | SingerInfo |
+----------+-----------+----------+------------+
| 1        | Marc      | Richards | NULL       |
| 2        | Catalina  | Smith    | NULL       |
| 3        | Alice     | Trentor  | NULL       |
| 4        | Lea       | Martin   | NULL       |
+----------+-----------+----------+------------+
4 rows in set (1.39 msecs)

INSERT OR UPDATE なので、プライマリキー以外のカラム(FirstNameとLastName)に実行前と違う内容を指定すると当然更新(UPDATE)されます。

spanner> INSERT OR UPDATE Singers (SingerId, FirstName, LastName) VALUES (4, 'Nick', 'Porter');
Query OK, 1 rows affected (0.09 sec)

spanner> select * from Singers where SingerId=4;
+----------+-----------+----------+------------+
| SingerId | FirstName | LastName | SingerInfo |
+----------+-----------+----------+------------+
| 4        | Nick      | Porter   | NULL       |
+----------+-----------+----------+------------+
1 rows in set (1.38 msecs)

INSERT OR IGNORE

INSERT OR UPDATE に比べると使い所がやや少ないですが、INSERT OR IGNORE という構文もあります。こちらはすでにレコードがある場合には追記も更新もしないという動作となります。

実行例

すでに同じプライマリキーのレコードがある状態で、INSERT OR IGNORE で異なる値を追記しようとするとエラーとはならず、更新もされないという動作となります。

spanner> select * from Singers where SingerId=4;
+----------+-----------+----------+------------+
| SingerId | FirstName | LastName | SingerInfo |
+----------+-----------+----------+------------+
| 4        | Nick      | Porter   | NULL       |
+----------+-----------+----------+------------+
1 rows in set (4.02 msecs)

spanner> INSERT OR IGNORE Singers (SingerId, FirstName, LastName) VALUES (4, 'Nick', 'Porter');
Query OK, 0 rows affected (0.05 sec)

spanner> select * from Singers where SingerId=4;
+----------+-----------+----------+------------+
| SingerId | FirstName | LastName | SingerInfo |
+----------+-----------+----------+------------+
| 4        | Nick      | Porter   | NULL       |
+----------+-----------+----------+------------+
1 rows in set (4.24 msecs)