生成された列の作成と管理

コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

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

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

次の CREATE TABLE スニペットでは、ユーザーに関する情報を格納するテーブルを作成します。FirstName と LastName の列があり、FirstName と LastName を連結した FullName 用に生成される列を定義します。

Google 標準 SQL 

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 式です。ただし、次の制限があります。

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

  • STORED 属性のない生成された列は使用できません。

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

  • 生成された列は、主キーとして、またはその一部として使用できません。ただし、セカンダリ インデックス キーとしては使用できます。

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

  • 生成された列のデータ型や、生成された列によって参照される列は変更できません。

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

生成された列は、他の列と同様にクエリできます。次に例を示します。

Google 標準 SQL 

SELECT Id, FullName
FROM Users;

PostgreSQL

SELECT id, fullname
FROM users;

これは、次のステートメントに相当します。このステートメントは、格納済みの生成された列を使用しません。

Google 標準 SQL 

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

クエリを簡素化し、クエリの実行時に式を評価する費用を削減できるほか、生成された列にもインデックスを作成したり、外部キーとして使用できます。

生成された列にインデックスを作成する

生成された FullName 列の検索に役立つように、セカンダリ インデックスを作成できます。次のスニペットに例を示します。

Google 標準 SQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL

CREATE INDEX UserByFullName ON users (fullname);

既存のテーブルに生成された列を追加する

次の ALTER TABLE ステートメントを使用して Users テーブルに生成された列を追加し、ユーザーのイニシャルを生成、保存します。

Google 標準 SQL 

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 を返します。

    Google 標準 SQL 

    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;

  2. この新しい列にインデックスを作成し、Google 標準 SQL では NULL_FILTERED キーワードを使用し、PostgreSQL では IS NOT NULL 述語を使用して NULL 値のインデックス作成を無効にします。この部分インデックスは、通常のインデックスよりもサイズが小さく効率的で、18 歳以上のユーザーがすべて除外されます。

    Google 標準 SQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. 18 歳以上のすべてのユーザーの Id と Age を取得するには、次のクエリを実行します。

    Google 標準 SQL 

    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 歳以上のすべてのユーザーを取得するなど、違う年齢でフィルタリングするには、生成された列で同じインデックスとフィルタを使用します。次に例を示します。

    Google 標準 SQL 

    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 の 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 が WRITE_ONLY を返す場合、Spanner はまだ列のバックフィル中であり、読み取りはできないことを示しています。

Google 標準 SQL 

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;

次のステップ