防止在 SQL 数据仓库中插入数十亿行时出现重复项?

Question

我正在尝试确定是否有一种实用的方法可以防止在表已经包含数十亿行(例如 200 亿行)时使用 Azure SQL DW 将重复行插入到表中。

需要这样做的根本原因是数据源是第三方，它发送所谓的唯一数据，但有时会发送没有识别键的副本。不幸的是，我不知道我们是否已经收到了他们发送的数据。

我尝试创建一个包含行哈希列(从其他几列预先计算)的表，并根据该行哈希分布数据。例如：

CREATE TABLE [SomeFact]
(
Row_key BIGINT NOT NULL IDENTITY,
EventDate DATETIME NOT NULL,
EmailAddress NVARCHAR(200) NOT NULL,
-- other rows
RowHash BINARY(16) NOT NULL
)
WITH
(
DISTRIBUTION = HASH(RowHash)
)

插入 SQL 大约为：

INSERT INTO [SomeFact]
(
EmailAddress,
EventDate,
-- Other rows
RowHash
)
SELECT
temp.EmailAddress,
temp.EventDate,
-- Other rows
temp.RowHash
FROM #StagingTable temp
WHERE NOT EXISTS (SELECT 1 FROM [SomeFact] f WHERE f.RowHash = temp.RowHash);

不幸的是，这太慢了。我添加了一些统计信息，甚至在 RowHash 上创建了一个二级索引，任何实际大小(例如 1000 万行)的插入都无法成功运行而不会因事务大小而出错。我也尝试了 50,000 个批次，但这些也太慢了。

Answer 1

我能想到的两件事不会有您在查询中拥有的单例记录，那就是

• 将临时表与事实数据表外部联接，并筛选某些 NULL 值。假设您在事实数据表中使用聚集列存储，这应该比上述便宜得多。
• 执行具有"选择不同于现有事实数据表"和"选择不同于临时表"的 CTAS，并连接在一起。

我的直觉说第一个选项会更快，但您可能需要查看查询计划并测试这两种方法。

Answer 2

您能否按 EventDate 对"主"表进行分区，并且假设新数据具有最近的 EventDate，CTAS 仅输出包含新数据的事件日期的分区，然后将"旧"和"新"数据的 CTAS/UNION 数据"合并"到具有相同分区架构的表中(UNION 将删除重复项)或使用您针对较小的表开发的 INSERT 方法， 然后将分区交换回"主"表。

注意 - 分区交换命令上有一个新选项，允许您一步直接"交换"分区："WITH (TRUNCATE_TARGET = ON)"。