指定身分欄

本文說明如何建立及使用身分識別資料欄 (有時稱為自動遞增資料欄)，這類資料欄可用於建立及維護資料表的主鍵。將資料列插入含有身分欄的資料表時，BigQuery 會為該欄產生不重複的整數值。

總覽

身分識別資料欄是 INT64 資料欄，其中填入系統產生的專屬值。

身分欄的主要用途是產生主鍵。您也可以使用 GENERATE_UUID 函式產生不重複的字串，藉此產生主鍵，但一般來說，基於下列原因，建議使用身分識別資料欄：

  • 整數值比字串值所需的儲存空間更少。
  • 使用整數進行表格聯結，比使用字串更有效率。

系統會根據起始值 (定義第一個值) 和遞增值 (定義連續產生值之間的最小差異)，產生身分識別資料欄的值。

產生的 ID 資料欄值具有下列屬性：

  • 不重複。系統自動產生的值在資料表中不得重複。
  • 寬鬆排序。產生的值不一定會嚴格遞增或遞減。
  • 稀疏。系統不保證產生的值會連續。系統可能會略過某些值，但身分識別資料欄中的值一律會相差您指定的增量倍數。

限制

  • 資料表最多只能有一個身分欄。
  • 您可以使用舊版 SQL 讀取含有 ID 欄的資料表，但無法使用舊版 SQL 寫入含有 ID 欄的資料表。
  • 您無法在身分資料欄上使用叢集或分區。

  • 如果來源或目的地資料表有身分欄，系統就不支援下列資料表複製作業：

    • 包含 WRITE_APPEND 或 WRITE_TRUNCATE 寫入配置的資料表副本
    • 複製多個來源資料表

  • 如果資料表含有身分資料欄，則不支援使用 Storage Write API (gRPC) 或 tabledata.insertAll API 方法串流資料。

建立身分欄

您可以使用 CREATE TABLE DDL 陳述式建立新資料表時，建立身分欄。 使用 GENERATED AS IDENTITY 子句將 INT64 資料欄指定為身分識別資料欄。資料表最多只能有一個身分欄。 您可以指定下列其中一種產生模式，決定是否能手動將值插入身分識別資料欄：

  • GENERATED ALWAYS AS IDENTITY：值一律由系統產生。在這一欄中插入或更新資料時，您無法提供自己的值。 如未指定 ALWAYS 或 BY DEFAULT，系統會使用 ALWAYS。

  • GENERATED BY DEFAULT AS IDENTITY：您可以在身分識別資料欄中插入或修改值。BigQuery 不會強制插入或修改的值必須是唯一值。

    如果您在插入資料時省略資料欄或提供 NULL，BigQuery 會自動為您產生值。身分識別資料欄不得包含 NULL 值。如要在 INSERT、MERGE 或 UPDATE 陳述式中使用產生值，可以使用 DEFAULT 或 NULL 關鍵字。

下列範例會建立資料表 mydataset.id_table，其中包含從 0 開始遞增 5 的身分識別資料欄 id：

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

將身分欄屬性新增至資料欄

如要修改現有資料欄以產生身分值，請使用 ALTER TABLE ALTER COLUMN SET GENERATED DDL 陳述式。這項陳述式會將現有的 INT64 資料欄變更為身分識別資料欄。系統不會為身分識別資料欄中的現有資料列補充值。

搭配身分欄使用 DML 陳述式

您可以在身分識別資料欄中使用 INSERT、MERGE 和 UPDATE 等 DML 陳述式。下列各節會使用 mydataset.mytable 資料表，其中包含名為 id 的身分識別資料欄，以及名為 data 的字串資料欄：

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

插入資料

將資料插入含有身分識別資料欄的資料表時，您可以從資料欄清單中省略身分識別資料欄，藉此產生該資料欄的值。下列 INSERT 陳述式省略了 id 資料欄，BigQuery 會為該資料欄產生值：

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

結果大致如下，但系統將產生值指派給資料列的順序可能不同：

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

如果使用 GENERATED BY DEFAULT AS IDENTITY 定義身分識別資料欄，您可以為該資料欄指定自己的值。您也可以使用 DEFAULT 關鍵字或 NULL，讓 BigQuery 生成值。

下列 INSERT 陳述式會提供一列的值，並使用 DEFAULT 或 NULL 為其他兩列產生值：

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    |
+-----+------+

如果身分欄是使用 GENERATED ALWAYS AS IDENTITY 定義，您只能使用 DEFAULT 關鍵字，讓 BigQuery 生成值。您無法提供自己的值或使用 NULL。

合併資料

您可以使用 MERGE 陳述式，將資料合併至含有身分識別資料欄的資料表。如果身分識別資料欄使用 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    |
+-----+------+

如果身分欄使用 GENERATED ALWAYS AS IDENTITY 生成模式，則您無法在任何合併更新子句中加入身分欄。如要使用合併插入子句，您可以從資料欄清單中省略身分識別資料欄，或使用 DEFAULT 關鍵字。

更新資料

您可以使用 UPDATE 陳述式，更新使用 GENERATED BY DEFAULT AS 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    |
+-----+------+

如果身分欄使用 GENERATED ALWAYS AS IDENTITY 生成模式，就無法更新身分欄。

附加到資料表中

您可以搭配使用 bq query 指令和 --append_table 旗標，將查詢結果附加至含有 ID 資料欄的目標資料表。如果查詢省略了 ID 欄，系統會為該欄產生值。

以下範例只會將資料附加至 mydataset.mytable 的 data 資料欄：

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

系統會在 mydataset.mytable 中新增資料列，並產生 id 值。

載入資料

您可以使用 bq load 指令或 LOAD DATA 陳述式，將資料載入含有身分欄的資料表。如果來源資料或結構定義中省略了 ID 欄，系統會為該欄產生值。如果身分識別欄是 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 值。

移除身分欄屬性

您可以使用 ALTER TABLE ALTER COLUMN DROP GENERATED DDL 陳述式，從資料欄中移除身分屬性。

以下範例會從 mydataset.mytable 的資料欄 id 中移除身分資料欄屬性：

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

查看身分欄的相關資訊

如要查看資料欄的身分欄設定，請查詢 INFORMATION_SCHEMA.COLUMNS 檢視區塊。

以下範例顯示 mydataset.mytable 中資料欄的身分識別資料欄資訊：

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 陳述式中的身分識別資料欄定義。

後續步驟