GCP Cloud Spannerのgenerated columnを試す
はじめに
Spannerには関数の結果を値として保存する generated column があります。これを使うと例えば名字＋名前＝フルネームのような形でデータを持つことが容易になり便利なので紹介したいと思います。
使用方法と例
以下のスキーマをベースに解説します。
CREATE TABLE Users (
UserID STRING(MAX) NOT NULL,
FirstName STRING(MAX) NOT NULL,
LastName STRING(MAX) NOT NULL,
Age INT64 NOT NULL,
) PRIMARY KEY (UserID);
generated columnはCREATE TABLEl, ALTER TABLEどちらのタイミングでも使用可能で
<column名> <型> AS (<func()>) STORED,
のフォーマットで設定します。
姓名の結合
ユーザーが入力した値を設定するFirstName, LastNameというカラムが存在することを前提としてARRAY_TO_STRINGでスペースと共に結合してやります。
FullName STRING(MAX) AS (ARRAY_TO_STRING([FirstName, LastName], ' ')) STORED,
単に関数で結合しているのだけなので、例えばミドルネームを追加して (ARRAY_TO_STRING([FirstName, MiddleName, LastName] 等のようなアレンジも可能です。
注意点としてはスキーマで文字数の制約を行う場合は生成後の文字列が制約を違反するようなUPDATEは失敗するという点が挙げられます。結合後に最大何文字になるか考慮した上で制約を決定するのがよいでしょう。
下記の例ではFirstName, LastNameにそれぞれ4文字をセットするとFullNameの制約である8文字を超過してしまいます（結合時にスペースが追加され計9文字となる）。
FirstName STRING(4) NOT NULL,
LastName STRING(4) NOT NULL,
FullName STRING(8) AS (ARRAY_TO_STRING([FirstName, LastName], ' ')) STORED,
INSERT INTO Users (UserID, FirstName, LastName, Age) VALUES ("id1", "john", "doe1", 20);
Failed to execute the operation to Cloud Spanner, New value exceeds the maximum size limit for this column in this database: Users.FullName, size: 9, limit: 8.
ShardIDの生成
Spannerではホットスポットの回避を目的としてShardIDを付与しPKの先頭とすることがあります。
FYI: https://cloud.google.com/spanner/docs/schema-design#fix_hash_the_key
従来ではアプリケーション内で乱数等を利用してこの値をセットする必要がありましたが、generated columnに任せられるのでよりシンプルな実装とすることができます。また何らかの理由により大量にデータをINSERTしなければならない場合は別途値を生成する必要があり面倒でしたがそれも回避できます。
以下の例ではUserIDを元にShardIDを決定しています。
...
ShardID INT64 AS (MOD(FARM_FINGERPRINT(UserID), 2048)) STORED,
) PRIMARY KEY (ShardID, UserID);
INSERT INTO Users (UserID, FirstName, LastName, Age) VALUES ("uid1", "john", "doe", 30);
+--------+-----------+----------+-----+---------+
| UserID | FirstName | LastName | Age | ShardID |
+--------+-----------+----------+-----+---------+
| uid1 | john | doe | 30 | 1448 |
+--------+-----------+----------+-----+---------+
PKの補完
通常であればレコードを一意に特定する場合はPKを構成する全ての要素（上記ケースではShardIDとUserID）をWHEREに指定する必要がありますが、generated columnの場合値が推測できる場合は自動的に最適化が行われます。
上記例のようにUserIDをベースにShardIDを一意に生成している場合、PK先頭のShardIDを指定したものとしないものについて実行計画を取得して比較すると等価であり、勿論フルスキャンも発生していないことがわかります。
EXPLAIN SELECT *
FROM Users
WHERE UserID = 'uid1';
+----+------------------------------------------------------------+
| ID | Query_Execution_Plan (EXPERIMENTAL) |
+----+------------------------------------------------------------+
| *0 | Distributed Union |
| 1 | +- Local Distributed Union |
| 2 | +- Serialize Result |
| *3 | +- Filter Scan (seekable_key_size: 2) |
| 4 | +- Table Scan (Table: Users, scan_method: Scalar) |
+----+------------------------------------------------------------+
Predicates(identified by ID):
0: Split Range: (($ShardID = 1448) AND ($UserID = 'uid1'))
3: Seek Condition: IS_NOT_DISTINCT_FROM($ShardID, 1448) AND ($UserID = 'uid1')
EXPLAIN SELECT *
FROM Users
WHERE ShardID = MOD(FARM_FINGERPRINT(UserID), 2048)
AND UserID = 'uid1';
+----+------------------------------------------------------------+
| ID | Query_Execution_Plan (EXPERIMENTAL) |
+----+------------------------------------------------------------+
| *0 | Distributed Union |
| 1 | +- Local Distributed Union |
| 2 | +- Serialize Result |
| *3 | +- Filter Scan (seekable_key_size: 2) |
| 4 | +- Table Scan (Table: Users, scan_method: Scalar) |
+----+------------------------------------------------------------+
Predicates(identified by ID):
0: Split Range: (($UserID = 'uid1') AND ($ShardID = 1448))
3: Seek Condition: IS_NOT_DISTINCT_FROM($ShardID, 1448) AND ($UserID = 'uid1')
成人か否かの判定
BOOLをセットすることも可能なので、例えば年齢から成人か否かを判別するような使い方ができます。
IsAdult BOOL AS (Age >= 18) STORED,
+--------+-----------+----------+----------+-----+---------+---------+
| UserID | FirstName | LastName | FullName | Age | IsAdult | ShardID |
+--------+-----------+----------+----------+-----+---------+---------+
| uid1 | john | doe | john doe | 30 | true | 1448 |
+--------+-----------+----------+----------+-----+---------+---------+
インデックス
generated columnに対しインデックスを張り高速にレコードを特定することも可能です。使用方法としては通常のカラムと特に変わりありません。
CREATE INDEX UsersByFullName ON Users (FullName);
振る舞い
既存テーブルに追加する
スキーマ定義時だけでなくALTER TABLEにより後からgenerated columnを追加することができます。フォーマットは概ね想像の通りで、先述のFullNameを例にすると下記のようになります。
ALTER TABLE Users ADD COLUMN FullName STRING(MAX) AS (ARRAY_TO_STRING([FirstName, LastName], ' ')) STORED;
この場合既存レコードの数に応じた書き込み（値生成）の長時間オペレーションが発生するのでインスタンスの負荷に注意する必要があります。生成が終わるまで値を参照することはできないので不整合は発生しませんが、完了前にカラムが存在することが前提のアプリケーションをデプロイするとエラーになってしまいます。
参照元を変更すると値が再生成される
generated columnを生成する元になっているカラムの値が変更された場合は追随して値が再生性されます。ドキュメントには明示されていませんが、恐らくこの場合同一トランザクション内で変更が反映されるため不整合について気にする必要は無いでしょう（特に注意するよう書かれていない）。またそれが正しい場合40,000のMutatioin数制限にも影響してくると思われます。
パフォーマンス影響
読み取り時は通常のカラムと同様に扱え特段の影響はありません。
書き込み時は関数の実行と値の書き込みが必要になるためオーバーヘッドが発生します。複雑な関数をセットしたり大規模なオペレーションをする場合は事前の負荷試験が推奨されています。
各種制約
生成関数の変更はできない
ALTER TABLE <table_name> ALTER COLUMN ... をすることはできないため、生成内容を変更する場合はDROP, CREATEが必要になります。既にアプリケーションから参照されている場合にダウンタイム無しで更新したいのであればマイグレーションのステップを踏む必要があります。
ERROR: rpc error: code = InvalidArgument desc = Cannot change the expression of a generated column `Users.FullName` because it is stored or has other dependencies.
参照できるのは同じテーブル内のカラムのみ
他のテーブルのカラムを参照することはできません。またクエリーの結果を値として使うこともできずあくまでDDLとして関数で定義できる形のみが利用可能です。
非決定性関数は使用不可
CURRENT_TIMESTAMP() のような実行結果がSpanner側に依存する関数は利用できません。
CreatedAt INT64 AS (UNIX_SECONDS(CURRENT_TIMESTAMP())) STORED,
ERROR: (gcloud.spanner.databases.create) INVALID_ARGUMENT: Error parsing the definition of generated column `Users`.`CreatedAt`: Expression is non-deterministic due to the use of non-determinstic function `CURRENT_TIMESTAMP`. Expression of stored generated columns must yield the same value for the same dependent column values. Non-deterministic functions inside the expressions are not allowed.
また allow_commit_timestamp = true のフィールドは参照元・生成先どちらでも使用不可です。
CreatedAt TIMESTAMP NOT NULL OPTIONS ( allow_commit_timestamp = true ),
CreatedAtUnix INT64 AS (UNIX_SECONDS(CreatedAt)) STORED,
ERROR: (gcloud.spanner.databases.create) FAILED_PRECONDITION: Cannot use commit timestamp column `CreatedAt` as a dependency of a materialized generated column.
参照されているカラムは削除できない
ALTER TABLE <table_name> DROP COLUMN <column_name> をする際、generated columnの参照元になっているカラムを削除することはできません。
FirstName STRING(MAX) NOT NULL,
LastName STRING(MAX) NOT NULL,
FullName STRING(MAX) AS (ARRAY_TO_STRING([FirstName, LastName], ' ')) STORED,
ERROR: rpc error: code = FailedPrecondition desc = Cannot drop column `LastName` from table `Users` because it is referenced by generated column `FullName`.
STOREDは必須
スキーマ末尾の STORED, は必須です。入れないとエラーが出てしまうのでおまじないのようなものと思って入れておきましょう。
"error": {
"code": 501,
"message": "Generated column `ShardID` without the STORED attribute is not supported.",
"status": "UNIMPLEMENTED"
}
おわりに
generated columnを使うとこれまでアプリケーション側の実装に頼っていた箇所を減らしコードを簡素化することができます。一方でカラムの値を決定するロジックが一部データベース側へ漏れ出すことにもなるため、あまり凝ったことやドメインロジックの実装はしない方がよいでしょう。
Discussion