如何避免Delta维度表中已删除的dim_id被重复生成？

问题根源 #

你当前用row_number() + max(dim_id)生成dim_id的逻辑，仅取了当前表中存在的dim_id最大值。当删除dim_id=1004后，现有数据的max(dim_id)变为1003，新插入数据时row_number()从1开始计算，就会生成1003+1=1004，导致重复复用已删除的ID。

解决方案 #

以下是几种可靠的解决思路：

• 维护独立的ID序列表
  创建一个仅存储全局最大已分配ID的小表，彻底脱离主表的数据变化影响：

  -- 1. 创建序列表
  CREATE TABLE IF NOT EXISTS dim_id_sequence (max_id INT);
  -- 2. 初始化（首次执行，填入当前已用的最大ID）
  INSERT INTO dim_id_sequence VALUES (1004);
  -- 3. 插入新数据时获取并更新ID
  WITH new_id_cte AS (
      SELECT max_id + 1 AS next_id FROM dim_id_sequence
  )
  -- 插入新记录
  INSERT INTO your_dim_table (dim_id, user_id)
  SELECT next_id, 7 FROM new_id_cte;
  -- 更新序列表的最大ID
  UPDATE dim_id_sequence SET max_id = (SELECT next_id FROM new_id_cte);
  

  无论主表如何删除数据，序列表始终记录所有已分配过的ID最大值，确保新ID不会重复。

• 利用Delta CDC记录历史ID
  开启Delta表的变更数据捕获（CDC），保留所有历史变更记录（包括删除操作），计算新ID时取所有历史dim_id的最大值：

  -- 开启CDC（若未开启）
  ALTER TABLE your_dim_table SET TBLPROPERTIES (delta.enableChangeDataCapture = true);
  -- 获取所有历史dim_id的最大值，生成新ID
  SELECT COALESCE(MAX(all_dim_ids), 0) + 1 AS new_dim_id
  FROM (
      -- 取所有版本的dim_id
      SELECT dim_id FROM your_dim_table VERSION AS OF 0
      UNION ALL
      SELECT dim_id FROM your_dim_table CHANGES SINCE VERSION 0
  ) AS all_historical_ids;
  

  注意：若表历史版本过多，需定期清理冗余版本但保留必要的ID记录，避免性能损耗。

• 改用UUID或自增列（业务允许的话）

  • 如果不需要连续整数ID，直接用UUID生成唯一值：

    INSERT INTO your_dim_table (dim_id, user_id)
    SELECT UUID(), 7;
    

  • 若业务允许自增逻辑，创建表时将dim_id定义为自增列（Spark 3.0+支持）：

    CREATE TABLE your_dim_table (
        dim_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1001 INCREMENT BY 1),
        user_id INT
    ) USING DELTA;
    

  自增列会自动分配递增ID，即使删除旧记录，新ID也会继续向后递增，绝不会复用已删除的ID。

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