如何在Databricks Delta表中将列转为IDENTITY列并保留旧值

核心思路 #

使用GENERATED BY DEFAULT AS IDENTITY替代GENERATED ALWAYS AS IDENTITY，该模式允许手动插入旧SK值，同时在不指定SK时自动生成唯一值，完美匹配迁移需求。

步骤1：创建支持手动插入SK的Delta表 #

CREATE TABLE d_something (
  sk BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  bk STRING
)
USING DELTA;

• GENERATED BY DEFAULT：插入语句指定SK值时使用手动输入值；未指定时由系统自动生成。
• 可根据原有数据的最大SK调整START WITH值（比如原有最大SK是12，可设为START WITH 13），避免新生成的SK与旧值冲突。

步骤2：迁移旧数据（手动指定SK） #

直接插入原有数据的SK和业务键(BK)：

INSERT INTO d_something (sk, bk)
VALUES (12, 'ABC');

步骤3：插入新数据（自动生成SK） #

插入新维度值时仅指定BK字段，系统会自动生成唯一SK：

INSERT INTO d_something (bk)
VALUES ('DEF');

执行以上操作后，查询表数据将得到期望结果：

sk = 12, bk = 'ABC'
sk = 13, bk = 'DEF'

针对已存在表的修改方案（将普通BIGINT列改为IDENTITY） #

如果已经创建了包含SK列的Delta表（SK为普通BIGINT类型），可通过ALTER COLUMN语句修改为IDENTITY列，DBR 10.4的正确语法如下：

-- 先确认现有SK的最大值，假设为12，START WITH设为13
ALTER TABLE d_something
ALTER COLUMN sk SET GENERATED BY DEFAULT AS IDENTITY (START WITH 13 INCREMENT BY 1);

• 必须确保START WITH的值大于现有SK的最大值，避免新生成的SK与旧值重复。
• 若之前执行ALTER语句报错，大概率是语法错误，比如遗漏GENERATED BY DEFAULT或参数格式不正确。

注意事项 #

• 迁移前需确保原有SK列的唯一性，否则插入时会触发主键/唯一性约束报错（若已设置约束）。
• IDENTITY列的INCREMENT BY可根据需求调整，无需连续的话，可设为更大的值（如100）进一步降低冲突风险。

内容的提问来源于stack exchange，提问作者Rubens Levi