生成列は、行内の他の列から常に計算される列です。これらの列を使用することで、クエリを簡素化し、クエリ実行時に式を評価する費用を削減できます。また、インデックスを付けたり、外部キーとして使用することもできます。この記事では、データベースでこの列タイプを管理する方法について説明します。
新しいテーブルに生成された列を追加する
次の CREATE TABLE
スニペットでは、ユーザーに関する情報を格納するテーブルを作成します。FirstName
と LastName
の列があり、FirstName
と LastName
を連結した FullName
用に生成される列を定義します。
GoogleSQL
CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);
PostgreSQL
CREATE TABLE users (
id VARCHAR(20) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
age BIGINT NOT NULL,
fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
PRIMARY KEY(id)
);
FullName
の値は、新しい行が挿入されたとき、または既存の行の FirstName
と LastName
が更新されるときに計算されます。計算された値は、テーブルの他の列と一緒に保存されます。かっこ内の SQL は世代式と呼ばれます。
expression
は、列のデータ型に割り当てる有効な SQL 式です。ただし、次の制限があります。式は、同じテーブルの列のみを参照できます。
式にサブクエリを含めることはできません。
式に
PENDING_COMMIT_TIMESTAMP()
、CURRENT_DATE()
、CURRENT_TIMESTAMP()
などの非決定性関数を含めることはできません。生成された列の式は変更できません。
式に続く
STORED
属性は、テーブルの他の列とともに式の結果を格納します。その後、参照されるいずれかの列に対して更新を行うと、Spanner は式を再評価して保存します。STORED
属性を使用しない限り、Spanner は生成された列を許可しません。生成された列に直接書き込むことはできません。
生成された列、または列が参照する列では、列オプション
allow_commit_timestamp
を使用できません。生成された列のデータ型や、生成された列が参照する列は変更できません。
生成された列が参照する列は削除できません。
生成された列は、次の制限がある主キーとして使用できます。
生成された主キーは、生成された他の列を参照できません。
生成された主キーは、最大で 1 つの非キー列を参照できます。
生成された主キーは、
DEFAULT
句を含む非キー列に依存できません。
生成されたキー列を使用する場合、次のルールが適用されます。
- Read API: 生成されたキー列を含め、キー列を完全に指定する必要があります。
- ミューテーション API:
INSERT
、INSERT_OR_UPDATE
、REPLACE
の場合、Spanner では生成されたキー列を指定できません。UPDATE
では、必要に応じて、生成されたキー列を指定できます。DELETE
の場合、生成されたキーを含むキー列を完全に指定する必要があります。 - DML:
INSERT
ステートメントまたはUPDATE
ステートメントで、生成された鍵に明示的に書き込むことはできません。 - クエリ: 一般に、生成されたキー列をクエリのフィルタとして使用することをおすすめします。必要に応じて、生成されたキー列の式が参照として 1 つの列のみを使用している場合、クエリは参照される列に等式(
=
)またはIN
条件を適用できます。詳細と例については、値列から派生した一意のキーを作成するをご覧ください。
生成された列は、他の列と同様にクエリできます。次に例を示します。
GoogleSQL
SELECT Id, FullName
FROM Users;
PostgreSQL
SELECT id, fullname
FROM users;
これは、次のステートメントに相当します。このステートメントは、格納済みの生成された列を使用しません。
GoogleSQL
SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;
PostgreSQL
SELECT id, firstname || ' ' || lastname as fullname
FROM users;
クエリを簡素化し、クエリの実行時に式を評価する費用を削減できるほか、生成された列にもインデックスを作成したり、外部キーとして使用できます。
生成された列にインデックスを作成する
生成された FullName
列の検索に役立つように、セカンダリ インデックスを作成できます。次のスニペットに例を示します。
GoogleSQL
CREATE INDEX UsersByFullName ON Users (FullName);
PostgreSQL
CREATE INDEX UserByFullName ON users (fullname);
既存のテーブルに生成された列を追加する
次の ALTER TABLE
ステートメントを使用して Users
テーブルに生成された列を追加し、ユーザーのイニシャルを生成、保存します。
GoogleSQL
ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;
PostgreSQL
ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(FirstName, 0, 1) || SUBSTR(LastName, 0, 1)) STORED;
格納されている生成列を既存のテーブルに追加するのは、列の値をバックフィルする長時間実行オペレーションです。バックフィル中は、格納されている生成列の読み取りやクエリはできません。バックフィルの状態は INFORMATION_SCHEMA に反映されます。
生成された列を使用した部分的なインデックスの作成
18 歳以上のユーザーに対してのみクエリを実行する場合はどうすればよいでしょうか。テーブルのフルスキャンは非効率的であるため、部分インデックスを使用します。
次のステートメントを使用して、生成された別の列を追加します。この列は、18 歳以上であればユーザーの年齢を返し、そうでなければ
NULL
を返します。GoogleSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 INT64 AS (IF(Age > 18, Age, NULL)) STORED;
PostgreSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) STORED;
この新しい列にインデックスを作成し、GoogleSQL 内の
NULL_FILTERED
キーワード、または PostgreSQL 内のIS NOT NULL
述語を持つNULL
値のインデックスを無効にします。部分インデックスは、18 歳以下のすべてのユーザーを除外するため、通常のインデックスよりも小さくて効率的です。GoogleSQL
CREATE NULL_FILTERED INDEX UsersAbove18ByAge ON Users (AgeAbove18);
PostgreSQL
CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18) WHERE AgeAbove18 IS NOT NULL;
18 歳以上のすべてのユーザーの
Id
とAge
を取得するには、次のクエリを実行します。GoogleSQL
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 IS NOT NULL;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 IS NOT NULL;
たとえば 21 歳以上のすべてのユーザーを取得するなど、違う年齢でフィルタリングするには、生成された列で同じインデックスとフィルタを使用します。次に例を示します。
GoogleSQL
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 > 21;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 > 21;
生成された列を削除する
次の DDL ステートメントは、生成された列を Users
テーブルから削除します。
ALTER TABLE Users
DROP COLUMN Initials;
生成された列の式の変更
生成された列の式は変更できません。代わりに、既存の列を削除して、新しい式で新しい生成された列を作成する必要があります。
生成された列に主キーを作成する
Spanner では、生成された列を主キーで使用できます。
次の例は、ShardId
生成列を含む UserInfoLog
テーブルを作成する DDL ステートメントを示しています。ShardId
列の値は、別の列に依存します。UserId
列の MOD
関数を使用して派生させます。ShardId
は主キーの一部として宣言されています。
GoogleSQL
CREATE TABLE UserInfoLog (
ShardId INT64 NOT NULL
AS (MOD(UserId, 2048)) STORED,
UserId INT64 NOT NULL,
FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);
PostgreSQL
CREATE TABLE UserInfoLog (
ShardId BIGINT GENERATED ALWAYS
AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
UserId BIGINT NOT NULL,
FullName VARCHAR(1024) NOT NULL,
PRIMARY KEY(ShardId, UserId));
通常、特定の行を効率的にアクセスするには、すべてのキー列を指定する必要があります。上記の例では、ShardId
と UserId
の両方が提供されます。ただし、Spanner が他の 1 つの列に依存しており、依存する列の値が完全に決定されている場合、Spanner は生成された主キー列の値を推定する場合があります。これは、生成された主キー列が参照する列が、次のいずれかの条件を満たす場合に当てはまります。
WHERE
句の定数値またはバインドされたパラメータと同じであるWHERE
句のIN
演算子によって設定された値を取得している- 等価結合条件から値を取得している
たとえば、次のクエリの場合:
GoogleSQL
SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;
PostgreSQL
SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;
Spanner は、指定された UserId
から ShardId
の値を推定できます。上記のクエリは、クエリの最適化後に次のクエリと同等です。
GoogleSQL
SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;
PostgreSQL
SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;
次の例は、Students
テーブルを作成し、StudentInfo
JSON 列の id
フィールドを取得して主キーとして使用する式を使用する方法を示しています。
GoogleSQL
CREATE TABLE Students (
StudentId INT64 NOT NULL
AS (CAST(JSON_VALUE(StudentInfo, "$.id") AS INT64)) STORED,
StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);
PostgreSQL
CREATE TABLE Students (
StudentId BIGINT GENERATED ALWAYS
AS (((StudentInfo ->> 'id'::TEXT))::BIGINT) STORED NOT NULL,
StudentInfo JSONB NOT NULL,
PRIMARY KEY(StudentId));
生成された列のプロパティを表示する
Spanner の INFORMATION_SCHEMA
には、データベース上に生成された列に関する情報が格納されています。以下に、情報スキーマをクエリするときに回答できる質問の例を示します。
データベースではどのような列が生成されますか。
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
テーブル Users
で生成された列の現在の状態は?
既存のテーブルに生成された列を追加した場合は、クエリで SPANNER_STATE
を渡して列の現在の状態を確認することをおすすめします。SPANNER_STATE
は次の値を返します。
COMMITTED
: この列はすべて使用できます。WRITE_ONLY
: 列はバックフィルされます。読み取りはできません。
次のクエリを使用して、列の状態を確認します。
GoogleSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;
PostgreSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;
注: 生成された列は、読み取りオペレーションやクエリ オペレーションのパフォーマンスには影響しません。ただし、書き込みオペレーションによって生成された列式で参照される列のいずれかの値が変更される場合、生成された列の列式を評価するオーバーヘッドのため、書き込みオペレーション(「DML」ステートメントと「ミューテーション」)のパフォーマンスに影響する可能性があります。オーバーヘッドはアプリケーションの書き込みワークロード、スキーマ設計、データセットの特性によって異なるため、生成された列を使用する前にアプリケーションのベンチマークを行うことをおすすめします。