Hash Diff calculation with SQL Server (Datavault Series)
Updates
Change log
| Date | Changes |
|---|---|
| 31 January, 2017 | Really good news! The 8k limitation on HASHBYTES has been removed in SQL Server 2016! |
Today I would like to discuss Hash Diff calculation using SQL Server 2014, from my own experience.
My purpose is NOT to be complete and/or to replicate book “Building a scalable data warehouse with Data Vault 2.0”. For full details please read the book.
However I will give a short intro for those who hardly know what I am talking about.
Introduction
Hash differences or Hash diffs
A hash difference is a hash value of all the descriptive data of a row in a satellite, plus the business key column(s).
A hash difference column is used to detect changes in a new version of a satellite’s row for a particular business key, and can be used in a comparison instead of doing a comparison on every individual descriptive attribute.
The main reason to use hash diff columns is to improve performance when loading new satellite rows (of which one or more attributes have changed).
The more columns the satellite has, the higher the performance gain will be.
To support data warehouse automation patterns, hash diffs can also be used for satellites with only a few or even one descriptive attribute.
I think a different name, like RowHash, RecordHash, DataHash or AttributeValuesHash, would better describe the contents of the column, because the value itself is not a difference, but that’s a different (relatively unimportant) discussion.
Hash keys
This article is not about Hash keys, however to be complete on hashing as far as Data Vault 2.0 is concerned, I will give a short introduction on that as well.
Hash keys replace sequence numbers (generated by the database engine) of the Data Vault 1.0 standard. They support geographically distributed data warehouses, as well as integration with big data environments like Hadoop.
A hash key is a hash value of the business key column(s) used in a Hub or Link. The advantage is that it is predictable, which enables parallel loading and a multiplatform / distributed datawarehouse architecture. For instance a customer with code NL123 will have the same hash key on all distributed parts of the data warehouse. Because the hash key always has the same length and is stored as a (fixed length) CHAR column, performance is usually better than when the business key column(s) are directly used as primary key.
How to calculate a hash difference in T-SQL
For full details about Endianness, differences of hash functions between different platforms, how to cope with new columns and other technical details I refer to the book mentioned above. If you want to work with Data Vault 2.0 you will need the book anyway.
As said, a hash difference is a hash value of all the descriptive data of a row in a satellite, plus the business key column(s). To do the hashing, we have to put all the column values together in one nvarchar value, and then apply a hashing function on it. This is the short version and the base to understand it.
Between the different column values you have to use a delimiter, preferably one that does not occur in the column values. Otherwise you have to “escape” the delimiter in column values.
If you want to do a case insensitive compare, you should convert all values to either upper- or lowercase. But this means that if in the source system a customer name was “hans michiels” and is changed to “Hans Michiels”, you will not detect the change and therefore do not store it in the Data Vault. So it depends on the contents of the satellite and your business view on it if this is desired behaviour or not.
The hash diff calculation results in a binary(16) value, when using the hashing algorithm MD5 (which is recommended). To enable better cross-platform- and tools support, this is then converted to a hexadecimal string of 32 characters, stored as a CHAR(32) column.
In all cases you must also convert the hexadecimal hash value to all UPPER- or all lowercase, because not all convert functions give the same output, some lower- and other uppercase. To compare values that were made by different hash functions (read on different systems) making it all uppercase (or lowercase) makes sure the values do not differ in casing only (which could lead to a false negative answer on the “are the rows the same” question).
I understand this is all dry and boring nerd stuff if you do not see an example, so .. a simple example:
SELECT
[bla]
, UPPER(
CONVERT(CHAR(32),
HASHBYTES('MD5',
UPPER(
CONCAT( ISNULL(CONVERT(NVARCHAR, [BusinessKeyOfHub]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), [Column01]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), [Column02]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), [Column03]), N'')
) -- END CONCAT
) -- END UPPER
) -- END HASHBYTES
, 2) -- END CONVERT
) -- END UPPER
FROM [stg].[bla]
So far nothing shocking, but there are a few pitfalls. If you keep them in mind, you will be an excellent hasher soon. 😉
CONVERT(NVARCHAR without specifying the size
Do this only for values of 30 or less characters, otherwise the value will be cut off which could lead to wrong hash diff values, not changing when the attribute value changes. It is for instance okay for integers, decimals (unless the precision is more than 28, mind the decimal separator and possible negative sign), datetime stamps, and bits. When converting (n)varchar safest thing to do is follow the original size, for unique identifiers use 38 or 40.
Beware for CONCAT
Beware for concatenation of (N)VARCHAR(MAX), (N)TEXT and XML columns using the CONCAT function. It appears that only the first 8000 bytes of (N)VARCHAR(MAX) columns are in the concatenated result.
And even worse, HASHBYTES ..
HASHBYTES does only hash a NVARCHAR value with a length of 4000 or less. Above that the error “String or binary data would be truncated” occurs. This is really important to keep in mind.
The 8000 characters (or 4000 unicode characters) limitation on any string hashed with the HASHBYTES function has been removed in SQL Server 2016! Now you can hash larger (N)VARCHAR values!
Cope with the HASHBYTES and CONCAT limitations
4000 characters is quite something, right? Only with very wide satellites (a lot of columns) and/or usage of (N)VARCHAR(MAX), (N)TEXT or XML columns (hereafter called ‘unlimited length columns’) you can get into trouble.
So, what is wise to do? Some measures you can take are:
Make your satellites not too wide
This is a best practice, not only from hashing function perspective. Split the satellites by source system, then by rate of change. Keep in mind that the business key column(s) and all satellite column values are converted to NVARCHAR and then (including all delimiters) may not exceed the 4000 characters if you want to use the HASHBYTES function.
Size unlimited length columns according to the actual maximum length
What I mean is, if for instance a Country name is stored in an unlimited length column in the source system, you know that a size of 100 will be long enough. When you doubt just do some data profiling on the source to see what is the maximum length. Use that size plus a safety margin for the satellite column size. Problem gone.
Handle true unlimited length columns
Put true unlimited length columns (for instance free entry ‘memo’ fields or xml columns in source systems in their own satellite! Otherwise if any other (small) attribute changes, the 4k memo is copied, even when it was not changed. This absorbs storage. But that alone is not enough, as said, with HASHBYTES, above 8000 characters (4000 when unicode) you are still f*c**d. Luckily there is a system function in the master database, which uses the MD5 algorithm and takes a binary value as input. So to compute a MD5 hash value for a text column with unlimited length, you could do something like:
SELECT master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX),
[MyHugeMemoField)) AS [HashDiff]
But don’t make the mistake to use the function [sys].[fn_repl_hash_binary] for all hashing, because the performance of HASHBYTES is much better!
On my machine HASHBYTES is three times faster than [sys].[fn_repl_hash_binary] on the same dataset.
You can check for yourself using this script:
USE [msdb]
GO
DECLARE @start DATETIME2(7)
DECLARE @stop DATETIME2(7)
DECLARE @elapsed1 BIGINT
DECLARE @elapsed2 BIGINT
--\-------------------------------------------------------------------------
---) TEST PERFORMANCE of HASHBYTES function.
--/-------------------------------------------------------------------------
SELECT @start = GETDATE();
SELECT TOP1000000
UPPER(
CONVERT(CHAR(32),
HASHBYTES('MD5',
--UPPER(
CONCAT( ISNULL(CONVERT(NVARCHAR(128), o1.[name]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[principal_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[schema_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[parent_object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[type]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[type_desc]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[create_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[modify_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_ms_shipped]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_schema_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(128), o2.[name]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[principal_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[schema_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[parent_object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[type]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[type_desc]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[create_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[modify_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_ms_shipped]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_schema_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(128), o3.[name]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[principal_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[schema_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[parent_object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[type]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[type_desc]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[create_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[modify_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_ms_shipped]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_schema_published]), N'')
) -- END CONCAT
--) -- END UPPER
) -- END HASHBYTES
, 2) -- END CONVERT
) -- END UPPER
FROM [sys].[objects] o1
CROSS JOIN [sys].[objects] o2
CROSS JOIN [sys].[objects] o3;
SELECT @stop = GETDATE();
SELECT @elapsed1 = DATEDIFF(MICROSECOND,@start, @stop);
--\-------------------------------------------------------------------------
---) TEST PERFORMANCE of [master].[sys].[fn_repl_hash_binary] function.
--/-------------------------------------------------------------------------
SELECT @start = GETDATE();
SELECT TOP 1000000
UPPER(
CONVERT(CHAR(32),
master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX),
--UPPER(
CONCAT( ISNULL(CONVERT(NVARCHAR(128), o1.[name]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[principal_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[schema_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[parent_object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[type]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[type_desc]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[create_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[modify_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_ms_shipped]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_schema_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(128), o2.[name]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[principal_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[schema_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[parent_object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[type]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[type_desc]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[create_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[modify_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_ms_shipped]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_schema_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(128), o3.[name]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[principal_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[schema_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[parent_object_id]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[type]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[type_desc]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[create_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[modify_date], 126), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_ms_shipped]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_published]), N'')
, ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_schema_published]), N'')
) -- END CONCAT
--) -- END UPPER
) -- END HASHBYTES
) -- END CONVERT
, 2) -- END CONVERT
) -- END UPPER
FROM [sys].[objects] o1
CROSS JOIN [sys].[objects] o2
CROSS JOIN [sys].[objects] o3;
SELECT @stop = GETDATE();
SELECT @elapsed2 = DATEDIFF(MICROSECOND,@start, @stop);
SELECT @elapsed1 AS [Elapsed_HashBytes]
, @elapsed2 AS [Elapsed_fn_repl_hash_binary]
, 'HASHBYTES is ' + CONVERT(VARCHAR, CONVERT(decimal(19, 2), @elapsed2 / @elapsed1))
+ ' times faster than [fn_repl_hash_binary]' AS [Result]
That’s about it.
Just a few more things:
- If you use a different hashing algorithm (e.g. SHA1) you cannot use the sys.fn_repl_hash_binary function, in that case you will have to seek your solution in a C# script task or CLR stored procedure, doing the hashing in .NET code.
- Don’t use CONCAT if you have more than one column in the satellite containing [MyHugeMemoField], or you still can run into problems.
Conclusion / Wrap up
I have explained in short why Hash diff columns are used in a Data Vault 2.0 satellite, and how these values can be calculated.
After that, you have read about some limitations of the CONCAT and HASHBYTES function when used for calculating Hash diff values, and how to cope with these limitations.
Credits
My thanks go to Vincent Kuiper for reviewing the initial draft of this article.
(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.
