Databricks中为现有Delta表添加IDENTITY列的解决方法

首先明确：Delta Lake 原生不支持通过 ALTER TABLE ADD COLUMN 语法为已存在的Delta表新增IDENTITY列，你执行的语句报错属于预期行为，IDENTITY列目前仅支持在CREATE TABLE建表阶段定义。

可行的实现方案分两类：

• 方案1：重建表（使用原生IDENTITY能力，推荐）
  该方案可以完全使用Delta原生的IDENTITY自增逻辑，适合大多数场景，步骤如下：

  1. 新建结构与原表一致的Delta表，额外加上需要的IDENTITY列，注意保持原表的分区、表属性、字段顺序一致：

  CREATE TABLE tname_new (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    -- 按原表字段顺序依次列出所有原有字段，例如：
    -- user_id STRING,
    -- event_time TIMESTAMP,
    -- behavior STRING
  )
  USING DELTA
  -- 保持和原表一致的分区、配置，例如：
  PARTITIONED BY (event_date)
  TBLPROPERTIES (delta.enableChangeDataFeed = true);
  

  2. 将原表全量数据写入新表，写入时不要指定id字段，Delta会自动为id列生成不重复的自增序列值：

  INSERT INTO tname_new
  -- 这里仅查询原表的所有原有字段，不要查询id列
  SELECT user_id, event_time, behavior, event_date
  FROM tname;
  

  3. 校验新表数据完整性、id生成逻辑符合预期后，完成表替换即可，操作前建议先备份原表避免误操作：

  -- 备份原表
  CREATE TABLE tname_bak AS SELECT * FROM tname;
  -- 替换表名
  ALTER TABLE tname RENAME TO tname_old;
  ALTER TABLE tname_new RENAME TO tname;
  

  验证新表读写、id自增逻辑正常后，可按需删除备份表和旧表。

• 方案2：新增普通列手动维护ID（无需全量重建表，无原生IDENTITY能力）
  如果你的表数据量极大、全量重写成本过高，可以选择新增普通BIGINT列手动维护ID值，步骤如下：

  1. 新增普通BIGINT类型的id列：

  ALTER TABLE tname ADD COLUMN id BIGINT;
  

  2. 为存量数据填充唯一递增ID值，可根据业务场景选择排序字段保证顺序：

  MERGE INTO tname target
  USING (
    SELECT 
      主键字段,
      ROW_NUMBER() OVER (ORDER BY 排序字段例如event_time) as row_num
    FROM tname
    WHERE id IS NULL
  ) source
  ON target.主键字段 = source.主键字段
  WHEN MATCHED THEN SET target.id = source.row_num;
  

  3. 后续写入数据时，需要手动查询当前表的最大id值，为新数据生成递增的id，该方案无法像原生IDENTITY列一样自动维护序列，写入逻辑需要自行保证id不重复。

注意：如果你使用GENERATED BY DEFAULT AS IDENTITY模式，写入数据时可以手动指定id值覆盖自动生成的结果，不会打断后续序列的自动递增逻辑。

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