如何重置Databricks Delta表的Identity标识列

问题场景 #

我创建了如下DDL定义的Databricks Delta表：

CREATE  TABLE default.Test (
  ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  StopFromDateTime TIMESTAMP,
  StopToDateTime TIMESTAMP,
  User STRING)
USING delta
TBLPROPERTIES (
  'delta.autoOptimize.autoCompact' = 'true',
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.columnMapping.mode' = 'name',
  'delta.minReaderVersion' = '2',
  'delta.minWriterVersion' = '6')

插入3条数据后，表数据如下：

ID   StartDateTime   StopDateTime  User
1    2020-01-01      2020-01-02    Josh
2    2021-01-01      2021-01-02    Alex
3    2022-01-01      2022-01-02    Ross

该表共有4个版本：版本1（创建）、版本2、3、4（插入）。将表恢复到版本2后，表中仅保留第一条数据：

ID   StartDateTime   StopDateTime  User
1    2020-01-01      2020-01-02    Josh

此时插入新数据，新记录的ID为4而非预期的2，需要重置该表的Identity标识列。

解决方法 #

Delta Lake的Identity列会独立追踪已分配的最大值，即使表被回滚到之前的版本，这个追踪值不会自动同步重置。要调整Identity列的起始值，可采用以下两种方法：

方法1：直接修改Identity列的起始值 #

使用ALTER TABLE语句手动指定Identity列的下一个起始值，只需确保该值大于当前表中已存在的最大ID即可：

ALTER TABLE default.Test
ALTER COLUMN ID RESTART WITH 2;

执行该语句后，后续插入的新记录ID会从2开始按定义的步长（INCREMENT BY 1）递增。

方法2：重建表（彻底重置Identity状态） #

如果需要完全重置Identity列的追踪状态，可通过导出数据、重建表再重新导入的方式实现：

1. 导出当前表的业务数据（排除ID列）到临时视图：

CREATE OR REPLACE TEMP VIEW temp_test_data AS
SELECT StopFromDateTime, StopToDateTime, User FROM default.Test;

2. 删除原表：

DROP TABLE default.Test;

3. 按原DDL重新创建表：

CREATE TABLE default.Test (
  ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  StopFromDateTime TIMESTAMP,
  StopToDateTime TIMESTAMP,
  User STRING)
USING delta
TBLPROPERTIES (
  'delta.autoOptimize.autoCompact' = 'true',
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.columnMapping.mode' = 'name',
  'delta.minReaderVersion' = '2',
  'delta.minWriterVersion' = '6');

4. 将临时视图中的数据导入新表：

INSERT INTO default.Test (StopFromDateTime, StopToDateTime, User)
SELECT * FROM temp_test_data;

这种方式会彻底重置Identity列的状态，后续插入的新记录ID会从当前表中最大ID+1（即2）开始递增。

内容的提问来源于stack exchange，提问作者John Bryan