指定身份列

本文档介绍如何创建和使用身份列（有时 称为 自动递增列），这些列用于在表中创建和 维护表中的主键。当您向具有身份列的表中插入行时，BigQuery 会为该列生成唯一的整数值。

概览

身份列是一种 INT64 列，其中填充了由系统生成的唯一值。

身份列的主要用例是生成 主键。您还可以使用 主键，方法是使用 GENERATE_UUID函数 生成唯一字符串， 但通常首选身份列，原因如下：

  • 整数值比字符串值所需的存储空间更少。
  • 使用整数进行表联接比使用字符串更高效。

身份列的值是根据定义第一个值的起始值和定义连续生成的值之间的最小差值的递增值生成的。

生成的身份列值具有以下属性：

  • 唯一 。自动生成的值在表中是唯一的。
  • 松散排序 。生成的数值不保证严格按升序或降序排列。
  • 稀疏 。生成的数值不保证是连续的。某些值可能会被跳过，但身份列中的值始终相差您指定的递增值的倍数。

限制

  • 一个表最多只能有一个身份列。
  • 您可以使用旧版 SQL 从具有身份列的表中读取数据，但无法使用旧版 SQL 将数据写入到具有身份列的表中。
  • 您无法在身份列上使用聚簇或分区。

  • 如果源表或目标表具有身份列，则不支持以下表复制操作：

    • 使用 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，其中包含一个身份 列 id，该列从 0 开始，递增值为 5：

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 语句

您可以将 DML 语句（例如 INSERT、MERGE 和 UPDATE）与身份列搭配使用。以下部分使用表 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生成模式， 那么当您 在MERGE语句中插入或更新数据时，可以使用DEFAULT或NULL关键字生成值。

以下示例将 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 标志结合使用，将查询结果附加到具有身份列的目标表。如果查询省略了身份列，系统会为其生成值。

以下示例仅将 data 列的数据附加到 mydataset.mytable：

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

系统会将具有生成的 id 值的新行添加到 mydataset.mytable。

加载数据

您可以使用 加载数据 到具有身份列的表中，方法是使用 bq load 命令 或 LOAD DATA 语句。 如果源数据或架构中省略了身份列，系统会为其生成值。如果身份列为 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               |
+-------------+-------------+---------------------+----------------+--------------------+

或者，您可以查询 ddl 列的 INFORMATION_SCHEMA.TABLES 视图 以 查看表的 CREATE TABLE DDL 语句中的身份列定义。

后续步骤