生成された列を作成、管理する

生成列とは、常に行内の他の列から計算される列のことです。こうした列を使用すると、クエリを簡素化し、クエリ実行時に式を評価するコストを削減できます。また、インデックスを付けることや、外部キーとして使用することも可能です。このページでは、GoogleSQL 言語データベースと PostgreSQL 言語データベースのデータベースでこの列タイプを管理する方法について説明します。

新しいテーブルに生成列を追加する

次の CREATE TABLE スニペットでは、ユーザーに関する情報を保存するテーブルを作成します。FirstName と LastName の列があり、FirstName と LastName を連結した FullName という名前の生成列を定義します。かっこ内の SQL は、生成式と呼ばれます。

生成列を STORED としてマークすると、クエリ実行時に式を評価するコストを節約できます。結果として、FullName の値は、新しい行が挿入されたとき、または既存の行に対して FirstName または LastName が更新されたときにのみ計算されます。計算された値は、テーブル内の他の列とともに保存されます。

GoogleSQL

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (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)
);

非保存型の生成列を作成するには、DDL の STORED 属性を省略します。この種類の生成列はクエリ実行時に評価され、クエリを簡素化できます。PostgreSQL では、VIRTUAL 属性を使用して、非保存型の生成列を作成できます。

GoogleSQL

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

• expression は、列のデータ型に割り当てる有効な SQL 式です。ただし、次の制限があります。

  • 式は、同じテーブルの列のみを参照できます。

  • 式にサブクエリを含めることはできません。

  • 非確定的関数（PENDING_COMMIT_TIMESTAMP()、CURRENT_DATE()、CURRENT_TIMESTAMP() など）を含む式は、STORED 生成列やインデックスが設定された生成列にできません。

  • STORED やインデックス付き生成列の式は変更できません。

• 式の後に STORED 属性を指定すると、テーブルの他の列と一緒に式の結果が保存されます。その後、参照されるいずれかの列に対して更新を行うと、Spanner は式を再評価して保存します。

• STORED 以外の生成列を NOT NULL としてマークすることはできません。

• 生成列に直接書き込むことはできません。

• 列オプション allow_commit_timestamp は、生成列または生成列を参照する列には使用できません。

• STORED やインデックスが設定された生成列の場合、列のデータ型や、生成列が参照する列のデータ型は変更できません。

• 生成列で参照される列は削除できません。

• 生成列を主キーとして使用する場合、次の追加の制限があります。

  • 生成された主キーは、他の生成列を参照できません。

  • 生成された主キーは、キー以外の列を 1 つだけ参照できます。

  • 生成された主キーは、DEFAULT 句を含む非キー列に依存できません。

• 生成されたキー列を使用する場合は、次のルールが適用されます。

  • 読み取り API: 生成されたキー列を含むキー列を完全に指定する必要があります。
  • Mutation 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;

Fullname を使用するクエリは、生成された式を含むクエリと同等です。したがって、生成列を使用するとクエリを簡素化できます。

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 歳以上のユーザーに対してのみクエリを実行する場合はどうすればよいでしょうか。テーブルのフルスキャンは非効率的であるため、部分インデックスを使用します。

1. 次のステートメントを使用して、生成された別の列を追加します。この列は、18 歳以上であればユーザーの年齢を返し、そうでなければ NULL を返します。

   GoogleSQL

   ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
   AS (IF(Age > 18, Age, NULL));
   

   PostgreSQL

   ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
   GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;
   

2. この新しい列にインデックスを作成し、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;
   

3. 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;
   

4. たとえば 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;
   

   インデックス付きの生成列を使用すると、クエリ実行時に式を評価する費用を節約できます。また、STORED 生成列と比較して、値をベーステーブルとインデックスに 2 回保存する必要がなくなります。

生成列を削除する

次の DDL ステートメントは、生成列を Users テーブルから削除します:

GoogleSQL

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL

  ALTER TABLE users DROP COLUMN initials;

生成列式を変更する

GoogleSQL

ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));

PostgreSQL

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;

STORED 生成列やインデックス付きの非保存生成列の式を更新することはできません。

生成列に主キーを作成する

Spanner では、プライマリキーに STORED 生成列を使用できます。

次の例では、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 の両方を指定します。ただし、生成された主キー列が他の 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 (INT64(StudentInfo.id)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS ((StudentInfo ->> 'id')::BIGINT) STORED NOT NULL,
  StudentInfo JSONB NOT NULL,
  PRIMARY KEY(StudentId));

生成列のプロパティを表示する

Spanner の INFORMATION_SCHEMA には、データベース上に生成列に関する情報が保存されています。以下に、情報スキーマをクエリすることによって回答できる質問の例を示します。

データベースではどのような列が生成されますか。

GoogleSQL

SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL

SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

IS_STORED は、保存された生成列の場合は YES、保存されていない生成列の場合は NO、生成されていない列の場合は 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;

注: 保存されていない生成列には、SQL クエリを使用してのみアクセスできます。ただし、インデックスに登録されている場合は、読み取り API を使用してインデックスから値にアクセスできます。

パフォーマンス

STORED 生成列は、読み取りオペレーションやクエリ オペレーションのパフォーマンスには影響しません。ただし、クエリで使用される保存されていない生成列は、生成列式を評価するオーバーヘッドが原因でパフォーマンスに影響する可能性があります。

STORED 生成列またはインデックス付きの生成列を使用すると、書き込みオペレーション（DML ステートメントとミューテーション）のパフォーマンスに影響します。このオーバーヘッドは、書き込みオペレーションによって生成列式で参照される列のいずれかが挿入または変更されるときに、生成列式を評価することによるものです。オーバーヘッドは、アプリケーションの書き込みワークロード、スキーマ設計、データセットの特性によって異なるため、生成列を使用する前にアプリケーションをベンチマークすることをおすすめします。

次のステップ

• 詳細については、Spanner の GoogleSQL 言語データベースの情報スキーマと PostgreSQL 言語データベースの情報スキーマをご覧ください。

• 生成列の詳細については、テーブルの作成パラメータの詳細をご覧ください。