IDENTITY 列を指定する

このドキュメントでは、テーブルの主キーの作成と維持に使用される IDENTITY 列（自動インクリメント列とも呼ばれます）を作成して使用する方法について説明します。IDENTITY 列を持つテーブルに行を挿入すると、BigQuery はその列の一意の整数値を生成します。

概要

IDENTITY 列は、一意のシステム生成値が入力される INT64 列です。

IDENTITY 列の主な用途は、 主キーを生成することです。`GENERATE_UUID`関数を使用して一意の文字列を生成することで主キーを生成することもできますが、一般的に IDENTITY 列が推奨される理由は次のとおりです。GENERATE_UUID

  • 整数値は文字列値よりも必要なストレージ容量が少なくなります。
  • テーブル結合に文字列を使用するよりも整数を使用する方が効率的です。

IDENTITY 列の値は、最初の値を定義する開始値と、連続して生成される値の最小差を定義する増分値に基づいて生成されます。

生成された IDENTITY 列の値には次のプロパティがあります。

  • 一意 。自動生成された値はテーブル内で一意です。
  • 順序が緩い 。生成された値が厳密に昇順または降順になるとは限りません。
  • スパース 。生成された値が連続しているとは限りません。一部の値はスキップされる可能性がありますが、IDENTITY 列の値は常に指定した増分の倍数だけ異なります。

制限事項

  • 1 つのテーブルに設定できる IDENTITY 列は 1 つまでです。
  • レガシー SQL を使用して、IDENTITY 列を持つテーブルから読み取ることはできますが、レガシー SQL を使用して IDENTITY 列を持つテーブルに書き込むことはできません。
  • IDENTITY 列でクラスタリングまたはパーティショニングを使用することはできません。

  • ソーステーブルまたは宛先テーブルに IDENTITY 列がある場合、次のテーブル コピー オペレーションはサポートされません。

    • WRITE_APPEND または WRITE_TRUNCATE 書き込み処理を使用したテーブル コピー
    • 複数ソースのテーブル コピー

  • Storage Write API（gRPC）または tabledata.insertAll API メソッド を使用したデータのストリーミングは、IDENTITY 列を持つテーブルでは対象外です。

IDENTITY 列を作成する

IDENTITY 列は、 CREATE TABLE DDL ステートメントを使用して新しいテーブルを作成するときに作成できます。 GENERATED AS IDENTITY 句を使用して、INT64 列を IDENTITY 列として指定します。1 つのテーブルに設定できる IDENTITY 列は 1 つまでです。 IDENTITY 列に手動で値を挿入できるかどうかを決定する次のいずれかの生成モードを指定できます。

  • GENERATED ALWAYS AS IDENTITY: 値は常にシステムによって生成されます。この列にデータを挿入または更新するときに、独自の値を提供することはできません。 ALWAYS または BY DEFAULT を指定しない場合は、ALWAYS が使用されます。

  • GENERATED BY DEFAULT AS IDENTITY: IDENTITY 列に値を挿入または変更できます。BigQuery は、挿入または変更する値の一意性を強制しません。

    データを挿入するときに列を省略するか、NULL を指定すると、BigQuery は自動的に値を生成します。IDENTITY 列に NULL 値を含めることはできません。INSERT、MERGE、UPDATE ステートメントで生成された値を使用する場合は、DEFAULT または NULL キーワードを使用できます。

次の例では、0 から始まり 5 ずつ増分する IDENTITY 列 id を持つテーブル mydataset.id_table を作成します。

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

IDENTITY 列のプロパティを列に追加する

既存の列を変更して IDENTITY 値を生成するには、 ALTER TABLE ALTER COLUMN SET GENERATED DDL ステートメントを使用します。 このステートメントは、既存の INT64 列を IDENTITY 列に変更します。 IDENTITY 列の既存の行の値はバックフィルされません。

IDENTITY 列で DML ステートメントを使用する

IDENTITY 列では、INSERT、MERGE、UPDATE などの DML ステートメントを使用できます。以降のセクションでは、mydataset.mytable には id という名前の IDENTITY 列と data という名前の文字列列があります。

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

データの挿入

IDENTITY 列を持つテーブルにデータを挿入するときに、列リストから IDENTITY 列を省略して、その値を生成できます。 次の INSERT ステートメントでは id 列が省略されているため、BigQuery がその値を生成します。

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

結果は次のようになります。生成された値が行に割り当てられる順序は異なる場合があります。

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

IDENTITY 列が GENERATED BY DEFAULT AS IDENTITY で定義されている場合は、列に独自の値を使用できます。DEFAULT キーワードまたは NULL を使用して、BigQuery に値を生成させることもできます。

次の INSERT ステートメントでは、1 つの行に値が指定され、DEFAULT または NULL を使用して他の 2 つの行の値を生成します。

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

結果は次のようになります。

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

IDENTITY 列が GENERATED ALWAYS AS IDENTITY で定義されている場合は、DEFAULT キーワードのみを使用して BigQuery に値を生成させることができます。 独自の値を提供したり、NULL を使用したりすることはできません。

データのマージ

MERGE ステートメント を使用して、IDENTITY 列を持つテーブルにデータをマージできます。IDENTITY 列で GENERATED BY DEFAULT AS IDENTITY 生成モードを使用している場合は、DEFAULT または NULL キーワードを使用して、 MERGEステートメントの一部としてデータを挿入または更新するときに値を生成できます。

次の例では、mydataset.source_table を mydataset.mytable にマージします。 data 列に一致するものがない場合は新しい行を挿入し、一致するものがある場合は id 列を新しく生成された値に更新します。

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

結果は次のようになります。

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

IDENTITY 列で GENERATED ALWAYS AS IDENTITY 生成モードを使用している場合は、マージ更新句に IDENTITY 列を含めることはできません。マージ挿入句を使用するには、列リストから IDENTITY 列を省略するか、DEFAULT キーワードを使用します。

データの更新

UPDATE ステートメント を使用して、 GENERATED BY DEFAULT AS IDENTITY 生成モードを使用する IDENTITY 列の値を更新できます。DEFAULT または NULL キーワードを使用して新しい値を生成できます。

次の例では、列 id のすべての値を新しく生成された値に更新します。

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

結果は次のようになります。

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

IDENTITY 列で GENERATED ALWAYS AS IDENTITY 生成モードを使用している場合は、IDENTITY 列を更新できません。

テーブルに追加する

--append_table フラグを指定して bq query コマンドを使用すると、IDENTITY 列を持つ宛先テーブルにクエリの結果を追加できます。クエリで IDENTITY 列が省略されている場合は、その値が生成されます。

次の例では、列 data のデータのみを mydataset.mytableに追加します。

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

生成された id 値を持つ新しい行が mydataset.mytable に追加されます。

データの読み込み

IDENTITY 列を持つテーブルにデータを読み込むには、bq load コマンドまたはLOAD DATA ステートメントを使用します。ソースデータまたはスキーマから IDENTITY 列が省略されている場合は、その値が生成されます。IDENTITY 列が GENERATED ALWAYS AS IDENTITY の場合は、省略する必要があります。

次の例では、CSV ファイル data.csv から mydataset.mytable にデータを読み込みます。このファイルには、data 列のデータのみが含まれています。

"X"
"Y"

次の bq load コマンドは、data.csv を mydataset.mytable に読み込みます。 ヘッダー行は省略され、スキーマには data 列のみが指定されています。

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

読み込みジョブは、新しい行の id 値を生成します。

IDENTITY 列のプロパティを削除する

ALTER TABLE ALTER COLUMN DROP GENERATED DDL ステートメントを使用すると、列から IDENTITY プロパティを削除できます。

次の例では、mydataset.mytable の列 id から IDENTITY 列のプロパティを削除します。

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

IDENTITY 列に関する情報を表示する

列の IDENTITY 列の構成を確認するには、 INFORMATION_SCHEMA.COLUMNS ビューをクエリします。

次の例は、mydataset.mytable の列の IDENTITY 列の情報を示しています。

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

結果は次のようになります。

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

または、INFORMATION_SCHEMA.TABLESビューのddl列をクエリして、テーブルの CREATE TABLE DDL ステートメントで IDENTITY 列の定義を確認することもできます。

次のステップ