Detekcja zmian w SQL SERVER przy pomocy funkcji haszującej HASHBYTES

Ładowanie większej ilości danych na potrzeby hurtowni danych lub innego repozytorium danych jest czynnością powszechną i stanowi swego rodzaju standard. Tworząc procesy ładujące zazwyczaj powinniśmy starać się robić to w sposób przyrostowy czyli wybierać ze źródeł tylko te dane, które się zmieniły od ostatniego ładowania. Aby coś takiego osiągnąć pomocny może być szereg dodatkowych mechanizmów jak Change Data Capture, Change Tracking czy też techniczne kolumny utrzymywane przez aplikację zawierające datę modyfikacji, usunięcia czy też wstawienia danego rekordu. Co natomiast w przypadku gdy z systemu źródłowego nie jesteśmy w stanie otrzymać tych właśnie informacji, a nie chcemy przeładowywać całego zestawu danych za każdym razem? Z pomocą może nam przyjść funkcja hashująca, która w SQL Server nosi nazwę HASHBYTES. Jest ona pomocna w dużej ilości przypadków, a jak jej użyć, do czego stosować i dlaczego jest lepsza niż CHECKSUM? O tym postaram się powiedzieć w ramach tego artykułu.

Funkcje haszujące zazwyczaj kojarzą się z kryptografią jednakże ich właściwości powodują to, że często przydają się również do detekcji zmian. Ogólnie rzecz biorąc funkcja ta jest bardzo prosta w użyciu i sprowadza się to do tego, że podajemy mu jakieś wartości (będące typu tekstowego lub binarnego) i zwracany jest hash. To w jaki sposób zostanie zbudowany hash zależy w dużej mierze od użytego algorytmu. Warto zauważyć, że niektóre algorytmy są nieco silniejsze niż inne tzn. hashują na większej ilości bitów itp. Zdarza się, że algorytm hashujący zwróci ten sam hash dla dwóch różnych parametrów wejściowych i taką sytuację nazywamy kolizją (hash collision) – w przypadku porównań danych ryzyko to mimo wszystko powinno być jak najmniejsze więc powinniśmy wybrać takie podejście które będzie stosunkowo mało kolizyjne. W tym miejscu warto powiedzieć dlaczego na wstępie powiedziałem, że CHECKSUM nie jest najlepszym sposobem na detekcję zmian. Ponieważ ryzyko kolizji w tym przypadku jest zdecydowanie większe niż w HASHBYTES. Checksum jak sama nazwa wskazuje liczy sumę kontrolną, która jest liczbą całkowitą (INT), a więc ryzyko kolizji jest naprawdę znaczące.

Spójrzmy na pierwszy z brzegu przykładu oparty o AdventureWorksDW2017 – na początku sprawdzimy sobie czy kombinacja kolumn SalesOrderNumber oraz SalesOrderLineNumber jest unikalna w tabeli:

SELECT 
	  SalesORderNumber
	 ,SalesOrderLineNumber
	,COUNT(*) AS NumberOfRows
FROM dbo.FactInternetSales
GROUP BY 
	  SalesORderNumber
	 ,SalesOrderLineNumber
HAVING COUNT(*)>1
ORDER BY NumberOfRows DESC

Rezultat potwierdzi nasze założenia i zapytanie zwróciło zero wierszy, czyli wniosek z tego taki, że zestaw jest unikalny:

W następnym kroku użyjemy już funkcji CHECKSUM aby policzyć sumę kontrolną. Sprawdzimy czy algorytm wyliczył tą samą sumę kontrolną dla więcej niż jednego wiersza:

SELECT 
	 CHECKSUM(SalesOrderNumber+CAST(SalesOrderLineNumber AS VARCHAR(5)))
	,COUNT(*) AS NumberOfRows
FROM dbo.FactInternetSales
GROUP BY 
	CHECKSUM(SalesORderNumber+CAST(SalesOrderLineNumber AS VARCHAR(5)))
HAVING COUNT(*)>1
ORDER BY NumberOfRows DESC

Zaskakujące prawda? Widzimy, że CHECKSUM zwraca te same sumy kontrolne dla różnych wierszy. Aby mieć pewność, że tak się dzieje sprawdziłem jak to wygląda dla jednego z przykładów:

SELECT 
	SalesOrderNumber
	,SalesOrderLineNumber
	,CHECKSUM(SalesORderNumber+CAST(SalesOrderLineNumber AS VARCHAR(5)))
FROM dbo.FactInternetSales
WHERE CHECKSUM(SalesORderNumber+CAST(SalesOrderLineNumber AS VARCHAR(5)))= 2136268688

Dwa kompletnie różne wiersze, a wyliczona suma kontrolna ta sama. Czy stanowi to dla nas problem przy detekcji zmian? Oczywiście, że tak ponieważ możliwe, że całkowicie popsujemy nasze dane aktualizując np. jedno zamówienie innym co w ogóle nie powinno się zdarzyć. Kolizje w CHECKSUM są nadzwyczaj częste chociażby porównanie dwóch wartości tekstowych jak poniżej daje nieakceptowalne rezultaty:

SELECT 'LE', checksum('le')
UNION
SELECT 'AAAAAAAAAAAAAAAALE', checksum('AAAAAAAAAAAAAAAALE')

Wniosek z tego taki, że z całą pewnością powinniśmy unikać stosowania funkcji CHECKSUM i nieco lepiej zapoznać się z HASHBYTES. Funkcja ta pozwala nam wyspecyfikować jaki algorytm chcemy użyć, a jest ich kilka:

• MD2
• MD4
• MD5
• SHA
• SHA1
• SHA2_256
• SHA2_512

Dwa algorytmy wspomniane na końcu, które pogrubiłem generują kolejno 32 i 64 bitowe hashe i to one powinny być naszym aktualnym wyborem ponieważ wszystkie pozostałe od SQL Server 2016 nie są rekomendowane (depricated).

Funkcja HASHBYTES poza wskazaniem algorytmu przyjmuje również wartość wejściową dla tego algorytmu. Wartość ta przed SQL Server 2016 przyjmowała wartości do 8000 bajtów. Limit ten został zniesiony we wspomnianej wersji 2016. Dla testu przygotowałem zapytanie analogiczne do przykładu z CHECKSUM:

SELECT 
	 SalesOrderNumber
	,SalesOrderLineNumber
	,HASHBYTES('SHA2_256',SalesORderNumber+'-'+CAST(SalesOrderLineNumber AS VARCHAR(5)))
FROM dbo.FactInternetSales

W rezultacie otrzymałem hashe bez duplikatów:

Chciałbym w tym miejscu zwrócić waszą uwagę na to, że algorytmy takie jak SHA2_256 czy SHA2_512 nie są bezkolizyjne – po prostu ryzyko kolizji w ich przypadku jest bardzo małe lub wręcz znikome. Dodkatkow przy tego typu analize ważne sątypy danych i porównywanie np. dwóch wartości tekstowych które są różnych typów no. teksty typu nvarchar i varchar:

SELECT 
	 HASHBYTES('SHA2_256','This is my car') AS VarcharString
	,HASHBYTES('SHA2_256',N'This is my car') AS NvarcharString

Jak widać na powyższym zrzucie ekranowym wygenerowany hash jest całkowicie inny mimo,  że wartość tekstowa jest taka sama. Zachowanie to może powodować różnego rodzaju błędy więc jako złotą zasadę powinniśmy przyjąć to, że budujemy hashe bazując na tych samych typach danych.

Przejdźmy dalej czyli do wykorzystania HASHBYTES do porównywania zbiorów. Wyobraźmy sobie następującą sytuację w której na jakiegoś rodzaju obszarze przejściowym pojawiają się rekordy, które zostały wstawione, zaaktualizowane (zakładamy, że rekordy nie są usuwane w systemie źródłowym, a jedynie odpowiednio oznaczane jako usunięte). Tabela ta będzie zawierała jedynie te wiersze które się zmieniły od ostatniego ładowania więc będzie za każdym razem czyszczona. Taką tabelę przejściową stworzymy sobie w AdventureWorksDW2017 używając SELECT INTO:

SELECT TOP 0
	*
INTO
	[dbo].[staging_FactInternetSales]
FROM
	[dbo].[FactInternetSales]
GO

Tabelę docelową dla naszego testu stworzymy już bezpośrednio poprzez CREATE TABLE:

CREATE TABLE [dbo].[destination_FactInternetSales](
        [HashKey] [NVARCHAR](128) NOT NULL,
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[DueDateKey] [int] NOT NULL,
	[ShipDateKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesTerritoryKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[RevisionNumber] [tinyint] NOT NULL,
	[OrderQuantity] [smallint] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[ExtendedAmount] [money] NOT NULL,
	[UnitPriceDiscountPct] [float] NOT NULL,
	[DiscountAmount] [float] NOT NULL,
	[ProductStandardCost] [money] NOT NULL,
	[TotalProductCost] [money] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[TaxAmt] [money] NOT NULL,
	[Freight] [money] NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL,
	[OrderDate] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[ShipDate] [datetime] NULL
) ON [PRIMARY]
GO

Zwróćcie uwagę na HashKey czyli kolumnę w której będziemy przechowywać nasz wyliczony hash. Zasilmy wybranymi rekordami naszą tabelę staging:

INSERT INTO [dbo].[staging_FactInternetSales] WITH (TABLOCK)
SELECT
	*
FROM
	[dbo].[FactInternetSales]
WHERE
	ProductKey=310

Po wykonaniu powyższego zapytania do tabeli powinno trafić niewiele ponad 300 wierszy. Następnie chcielibyśmy załadować tabelę docelową, tylko w tym wypadku musimy sprawdzić czy dany wiersz się zmienił (czyli wykonać UPDATE na tabeli docelowej) czy został wstawiony (czyli wykonać INSERT na tabeli docelowej). Aby zdobyć tą informację będziemy używać właśnie wyliczonego hasha – w celach testowych użyjemy funkcji MERGE, której wielkim fanem nie jestem ale tutaj powinna sprawdzić się wystarczająco dobrze:

MERGE [dbo].[destination_FactInternetSales] AS target
USING 
(
	SELECT 
	   HASHBYTES('SHA2_256',CAST([SalesOrderNumber] AS NVARCHAR(50))+N'-'+CAST([SalesOrderLineNumber] AS NVARCHAR(50))) AS HashKey
	  ,[ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[CustomerKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM [dbo].[staging_FactInternetSales]
) AS source
ON source.[HashKey] = target.[HashKey]
WHEN MATCHED 
	THEN UPDATE SET 
		 target.[ProductKey]			= source.[ProductKey]
		,target.[OrderDateKey]			= source.[OrderDateKey]
		,target.[DueDateKey]			= source.[DueDateKey]
		,target.[ShipDateKey]			= source.[ShipDateKey]
		,target.[CustomerKey]			= source.[CustomerKey]
		,target.[PromotionKey]			= source.[PromotionKey]
		,target.[CurrencyKey]			= source.[CurrencyKey]
		,target.[SalesTerritoryKey]		= source.[SalesTerritoryKey]
		,target.[RevisionNumber]		= source.[RevisionNumber]
		,target.[OrderQuantity]			= source.[OrderQuantity]
		,target.[UnitPrice]			= source.[UnitPrice]
		,target.[ExtendedAmount]		= source.[ExtendedAmount]
		,target.[UnitPriceDiscountPct]	        = source.[UnitPriceDiscountPct]
		,target.[DiscountAmount]		= source.[DiscountAmount]
		,target.[ProductStandardCost]	        = source.[ProductStandardCost]
		,target.[TotalProductCost]		= source.[TotalProductCost]
		,target.[SalesAmount]			= source.[SalesAmount]
		,target.[TaxAmt]			= source.[TaxAmt]
		,target.[Freight]			= source.[Freight]
		,target.[CarrierTrackingNumber]	        = source.[CarrierTrackingNumber]
		,target.[CustomerPONumber]		= source.[CustomerPONumber]
		,target.[OrderDate]			= source.[OrderDate]
		,target.[DueDate]			= source.[DueDate]
		,target.[ShipDate]			= source.[ShipDate]
WHEN NOT MATCHED BY TARGET
THEN
INSERT
	(
		 [HashKey]
		,[ProductKey]
		,[OrderDateKey]
		,[DueDateKey]
		,[ShipDateKey]
		,[CustomerKey]
		,[PromotionKey]
		,[CurrencyKey]
		,[SalesTerritoryKey]
		,[SalesOrderNumber]
		,[SalesOrderLineNumber]
		,[RevisionNumber]
		,[OrderQuantity]
		,[UnitPrice]
		,[ExtendedAmount]
		,[UnitPriceDiscountPct]
		,[DiscountAmount]
		,[ProductStandardCost]
		,[TotalProductCost]
		,[SalesAmount]
		,[TaxAmt]
		,[Freight]
		,[CarrierTrackingNumber]
		,[CustomerPONumber]
		,[OrderDate]
		,[DueDate]
		,[ShipDate]
	)
VALUES
	(
		 [HashKey]
		,[ProductKey]
		,[OrderDateKey]
		,[DueDateKey]
		,[ShipDateKey]
		,[CustomerKey]
		,[PromotionKey]
		,[CurrencyKey]
		,[SalesTerritoryKey]
		,[SalesOrderNumber]
		,[SalesOrderLineNumber]
		,[RevisionNumber]
		,[OrderQuantity]
		,[UnitPrice]
		,[ExtendedAmount]
		,[UnitPriceDiscountPct]
		,[DiscountAmount]
		,[ProductStandardCost]
		,[TotalProductCost]
		,[SalesAmount]
		,[TaxAmt]
		,[Freight]
		,[CarrierTrackingNumber]
		,[CustomerPONumber]
		,[OrderDate]
		,[DueDate]
		,[ShipDate]
	);

W powyższej składni zwróćcie uwagę na następujące rzeczy:

• przy budowie hasha poszczególne kolumny zostały oddzielone myślnikiem HASHBYTES(‘SHA2_256′,CAST([SalesOrderNumber] AS NVARCHAR(50))+N’-‘+CAST([SalesOrderLineNumber] AS NVARCHAR(50))) – jest to kluczowe bo wyobraźmy sobie sytuację, że kolumna SalesOrderNumber ma wartość “1A”, a kolumna SalesOrderLineNumber ma wartość “1”. Wtedy jakbyśmy nie użyli żadnego separatora to hash będzie budowany z tekstu “1A1”, co natomiast jakby inny wiersz miał SalesOrderNumber równy “1”, a SalesOrderLineNumber A1? Otrzymalibyśmy dokładnie taki sam string czyli “1A1” – aby tego uniknąć wystarczy użyć wspomnianego separatora czyli mamy wtedy tekst “1A-1” oraz “1-A1” przy czym nie musi to być myślnik ale tak naprawdę cokolwiek innego.
• Porównanie ON source.[HashKey] = target.[HashKey] jest bardzo uproszczone i opiera się na jednej kolumnie co znacząco upraszcza wiele rzeczy. Często zdarza się, że powinniśmy porównywać bardzo wiele kolumn między zbiorami tak więc wszelkiego rodzaju złączenia i filtracje są długie i mało czytelne, w przypadku hasha porównujemy tylko HashKey.

Po wykonaniu powyższego polecenia MERGE do tabeli docelowej zostaną wrzucone nowe wiersze lub zaaktualizowane te które mają ten sam hash. Dodatkową zaletą jest to, że aby usprawnić sam proces możemy na tabeli docelowej postawić INDEKS unikalny. Struktura tego typu nie tylko przyspieszy porównanie zbiorów ale jednocześnie będzie stała na straży unikalności bo wiadomo, że w ramach pojedynczej tabeli  nie powinien wystąpić ten sam hash(przynajmniej w klasycznym scenariuszu). Jeśli nie mielibyśmy pojedynczej kolumny do porównania musielibyśmy stworzyć indeks składający się z wielu kolumn w odpowiedniej kolejności co wpływa na czytelność kodu i wydajność.

Szczególnie ma to znaczenie gdy chcemy zbudować HASH bazując na wszystkich kolumnach:

SELECT 
	   HASHBYTES('SHA2_256',
	  			ISNULL(CAST([SalesOrderNumber]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([SalesOrderLineNumber]		AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([ProductKey]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([OrderDateKey]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([DueDateKey]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([ShipDateKey]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([CustomerKey]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([PromotionKey]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([CurrencyKey]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([SalesTerritoryKey]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([SalesOrderNumber]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([SalesOrderLineNumber]		AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([RevisionNumber]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([OrderQuantity]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([UnitPrice]				AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([ExtendedAmount]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([UnitPriceDiscountPct]		AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([DiscountAmount]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([ProductStandardCost]		AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([TotalProductCost]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([SalesAmount]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([TaxAmt]				AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([Freight]				AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([CarrierTrackingNumber]		AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([CustomerPONumber]			AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([OrderDate]				AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([DueDate]				AS NVARCHAR(50)),'') +N'-'+
				ISNULL(CAST([ShipDate]				AS NVARCHAR(50)),'')
			)
  FROM [dbo].[staging_FactInternetSales]

W powyższym przykładzie dodatkowo zastąpiłem NULL pustym tekstem co powinniśmy zrobić zawsze w przypadku gdy nie mamy pewności, że wartość zawsze będzie występować. Jest to wymagane ponieważ konkatenacja ciągów tekstowych w których chociaż jeden z nich jest NULL w razultacie daje NULL co nie jest akceptowalne w naszym przypadku. Ktoś może zapytać po co budować tego typu hash i jest to całkiem zasadne pytanie. Zdarzają się przypadki, że chcemy doładowywać wszystkie wiersze niezależnie od tego czy są całkowicie nowe czy istniejące ale uaktualnione. Może zdarzyć się również, że system źródłowy jedynie wypluwa nowe wiersze, a jedynym unikalnym zbiorem są wszystkie kolumny.

Jak widzicie użycie funkcji hashującej jest naprawdę proste i daje wiele benefitów nie tylko w kryptografii. Polecam potestować HASHBYTES i używać do porównywania danych właśnie ze względu na prostotę i całkowicie zapomnieć o istnieniu funkcji CHECKSUM. Pozdrawiam!