Предотвратить дублирование при вставке с миллиардами строк в хранилище данных SQL?

Question

Я пытаюсь определить, есть ли практический способ предотвратить вставку дубликатов строк в таблицу с помощью Azure SQL DW, когда таблица уже содержит миллиарды строк (скажем, 20 миллиардов).

Основная причина этого заключается в том, что источником данных является третья сторона, которая отправляет предположительно уникальные данные, но иногда отправляет дубликаты, которые не имеют идентифицирующего ключа. К сожалению, я не знаю, получили ли мы уже данные, которые они отправляют.

Я попытался создать таблицу, которая содержит столбец хеша строки (предварительно рассчитанный из нескольких других столбцов) и распределить данные на основе этого хеша строки. Например:

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, и вставки любого реального размера (например, 10 миллионов строк) не будут выполняться без ошибок из-за размеров транзакций. Я также пробовал партии по 50000 штук, и они тоже слишком медленные.

Answer 1

Я могу подумать о двух вещах, которые не содержали бы в вашем запросе одноэлементные записи:

• Внешнее объедините вашу промежуточную таблицу с таблицей фактов и отфильтруйте некоторые значения NULL. Предполагая, что вы используете Clustered Column Store в своей таблице фактов, это должно быть намного дешевле, чем выше.
• Создайте CTAS с помощью Select Distinct из существующей таблицы фактов и Select Distinct из промежуточной таблицы, объединенной с UNION.

Моя интуиция говорит, что первый вариант будет быстрее, но вы, вероятно, захотите взглянуть на план запроса и протестировать оба подхода.

Answer 2

Можете ли вы разбить "главную" таблицу по EventDate и, если новые данные имеют недавнюю EventDate, CTAS выделяет только те разделы, которые содержат EventDate новых данных, затем "объединяет" данные с CTAS / UNION "старого" и "Новые" данные в таблицу с той же схемой секционирования (UNION удалит дубликаты) или используйте метод INSERT, разработанный вами для таблицы меньшего размера, затем поменяйте местами разделы в "основной" таблице.

Примечание. В команде раздела подкачки появилась новая опция, которая позволяет напрямую "заместить" раздел за один шаг: "WITH (TRUNCATE_TARGET = ON)".