Redshift中AccountLookup表重复插入问题及避免方法咨询

你的问题核心是并发场景下的竞态条件：当多个相同的插入事务同时执行时，每个事务执行NOT EXISTS检查时，其他事务的插入操作还未提交，因此都会通过检查并插入重复的Account值。Redshift作为分布式数据仓库，这种并发冲突更容易出现。

从源头阻止重复的解决方案 #

1. 给AccountLookup表添加唯一约束（最彻底的方法）

先给Account字段添加唯一约束，从数据库层面强制杜绝重复：

ALTER TABLE AccountLookup ADD CONSTRAINT uq_accountlookup_account UNIQUE (Account);

然后修改插入语句，使用Redshift支持的INSERT ... ON CONFLICT或MERGE语法，确保即使有并发插入，也不会产生重复：

方案A：使用INSERT ... ON CONFLICT

BEGIN;
    INSERT INTO AccountLookup (Account)
    SELECT DISTINCT Account FROM Staging
    ON CONFLICT (Account) DO NOTHING;
END;

方案B：使用MERGE语句

BEGIN;
    MERGE INTO AccountLookup acct
    USING (SELECT DISTINCT Account FROM Staging) db
    ON acct.Account = db.Account
    WHEN NOT MATCHED THEN INSERT (Account) VALUES (db.Account);
END;

两种写法都能确保：只有当Account值不存在时才插入，并发场景下会自动忽略重复的插入请求，不会抛出错误也不会产生重复数据。

2. 清理现有重复数据（如果表中已经存在重复）

如果AccountLookup表已经有重复条目，先清理再添加约束：

DELETE FROM AccountLookup
WHERE Id NOT IN (
    SELECT MIN(Id)
    FROM AccountLookup
    GROUP BY Account
);

执行完清理后，再添加上述唯一约束。

额外优化：避免主表生成重复数据（兜底方案） #

即使AccountLookup存在重复，也可以通过窗口函数确保主表只关联每个Account的唯一ID：

BEGIN;
    INSERT INTO Main (Usage, AccountId, Cost)
    SELECT bd.Usage, acct.Id, bd.Cost
    FROM Staging bd
    LEFT JOIN (
        SELECT Account, Id,
               ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Id) AS rn
        FROM AccountLookup
    ) acct ON bd.Account = acct.Account AND acct.rn = 1;
END;

但这只是兜底，优先解决AccountLookup的重复问题才是根本。

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