Welcome To TechBrothersIT: Snowflake SQL Tutorial 83 - HASH, MD5, SHA2, BASE64, HEX

Snowflake SQL Tutorial 83 - HASH, MD5, SHA2, BASE64, HEX - Hashing and Encoding

Snowflake SQL · Episode 83

In this post we are going to cover hashing and encoding in Snowflake: HASH, HASH_AGG, MD5, SHA2, BASE64_ENCODE and HEX_ENCODE.

A hash takes a value and gives you back a short fingerprint of it. The original is gone. You cannot get it back, and that is the point. Encoding does something different. It rewrites the same value in a different alphabet and keeps every bit of it, so anybody can turn it back again.

Those two things get mixed up constantly, so we will do the hashing first, then the encoding, and then put them next to each other.

We will start with Snowflake's own HASH and build it up step by step. On the way we will build a row fingerprint that gives two genuinely different rows the same digest, and find out why one customer quietly gets no digest at all.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 83 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's start with Snowflake's own HASH
Now let's hash more than one column
What does HASH do with a NULL?
Let's use HASH to put rows into buckets
Now let's fingerprint a whole table with HASH_AGG
Let's move on to MD5 and SHA2
Why does it matter that these are standard?
What do MD5 and SHA2 do with a NULL?
Now let's build a row digest properly
Now let's look at encoding, which is not hashing
What are those equals signs on the end?
Let's try HEX instead
One last thing: MD5's output is already hex
Let's put the whole lot on one grid

Let's create some sample data

This post works on the CUSTOMERS table, the same one the rest of the course uses. If you have already run the setup script you have it.

Sample data

-- CUSTOMERS comes from the setup script this course uses throughout.
-- Run it once; every episode works on the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

Here is the table we are going to hash.

Query

SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY, COUNTRY, SEGMENT
FROM CUSTOMERS ORDER BY CUSTOMER_ID;

Result10 rows · 5 columns

CUSTOMER_ID	CUSTOMER_NAME	CITY	COUNTRY	SEGMENT
101	Northwind Traders	Charlotte	USA	Enterprise
102	Blue Ridge Supply	Asheville	USA	SMB
103	Kestrel Analytics	Toronto	Canada	Enterprise
104	Harbour Foods	Vancouver	Canada	SMB
105	Alpine Logistics	Zurich	NULL	Enterprise
106	Delta Print Co	Raleigh	USA	SMB
107	Meridian Health	Boston	USA	Enterprise
108	Copper Kettle Ltd	Manchester	UK	SMB
109	Vantage Media	London	UK	Enterprise
110	Sable Interiors	Austin	USA	SMB

Ten rows and five columns. A customer id, a name, a city, a country and a segment.

Have a look at Alpine Logistics. Its COUNTRY is NULL. That one empty cell matters twice later on this page, so it is worth noticing now.

Let's start with Snowflake's own HASH

HASH is Snowflake's own hash function. Let's run it over the customer names and ask what sort of thing it gives back.

Query

SELECT CUSTOMER_ID, CUSTOMER_NAME,
       HASH(CUSTOMER_NAME)                   AS HASHED,
       TYPEOF(HASH(CUSTOMER_NAME)::VARIANT)  AS ITS_TYPE,
       HASH(CUSTOMER_NAME) < 0               AS CAN_IT_BE_NEGATIVE
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 5 columns

CUSTOMER_ID	CUSTOMER_NAME	HASHED	ITS_TYPE	CAN_IT_BE_NEGATIVE
101	Northwind Traders	3049752262830278508	INTEGER	FALSE
102	Blue Ridge Supply	8226330371505562003	INTEGER	FALSE
103	Kestrel Analytics	2850134512092016820	INTEGER	FALSE
104	Harbour Foods	7590755392900839451	INTEGER	FALSE
105	Alpine Logistics	-5978403927361676976	INTEGER	TRUE
106	Delta Print Co	6622266909762863503	INTEGER	FALSE
107	Meridian Health	7467479887013434436	INTEGER	FALSE
108	Copper Kettle Ltd	-6055431142769025159	INTEGER	TRUE
109	Vantage Media	-3337238996595394563	INTEGER	TRUE
110	Sable Interiors	7212178186552162673	INTEGER	FALSE

The hashes are numbers, and big ones. The ITS_TYPE column says INTEGER on every row, so that is not a guess.

Now read the last column down. Alpine Logistics, Copper Kettle Ltd and Vantage Media come back TRUE. Their hashes are negative.

So HASH returns a signed integer. That matters the moment you try to use it for anything, and we will run straight into it two sections from now.

Next question. Does it give the same answer every time?

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(HASH(CUSTOMER_NAME) = HASH(CUSTOMER_NAME)) AS SAME_TWICE,
       COUNT(DISTINCT HASH(CUSTOMER_NAME))                 AS DISTINCT_HASHES,
       COUNT(DISTINCT CUSTOMER_NAME)                       AS DISTINCT_INPUTS
FROM CUSTOMERS;

Result1 row · 4 columns

ROWS_TOTAL	SAME_TWICE	DISTINCT_HASHES	DISTINCT_INPUTS
10	10	10	10

Ten rows, and the hash equalled itself on all ten. Ten distinct inputs went in and ten distinct hashes came out, so nothing collided here.

What HASH is for

HASH is fast and it is not cryptographic. Use it for bucketing, for join keys and for change detection inside your own system. Do not use it for anything security related, and do not expect any other tool on earth to agree with it. MD5 and SHA2 are the ones for that, and they are further down this page.

Now let's hash more than one column

HASH takes as many arguments as you like. So there are two ways to fingerprint several columns at once. You can pass them as separate arguments, or you can join them into one string first.

They are not the same thing. Let's split the letters abc two different ways and try both.

Query

SELECT HASH('ab', 'c')        AS TWO_ARGS_AB_C,
       HASH('a', 'bc')        AS TWO_ARGS_A_BC,
       HASH('ab' || 'c')      AS CONCATENATED_ABC,
       HASH('a' || 'bc')      AS CONCATENATED_ABC_AGAIN,
       HASH('ab', 'c') = HASH('a', 'bc')     AS DO_THE_TWO_ARG_FORMS_COLLIDE,
       HASH('ab' || 'c') = HASH('a' || 'bc') AS DO_THE_CONCATENATED_FORMS_COLLIDE;

Result1 row · 6 columns · scroll →

TWO_ARGS_AB_C	TWO_ARGS_A_BC	CONCATENATED_ABC	CONCATENATED_ABC_AGAIN	DO_THE_TWO_ARG_FORMS_COLLIDE	DO_THE_CONCATENATED_FORMS_COLLIDE
-3815677121991732371	-6384218741891058204	-6041954911504603153	-6041954911504603153	FALSE	TRUE

The two-argument forms gave two different numbers, and the comparison column says they do not collide.

The concatenated forms gave the same number, and the comparison says TRUE. Which is correct, because 'ab' || 'c' and 'a' || 'bc' are both the string abc. There is nothing left in there to tell them apart.

Why this is worth going slowly over

Now think about that as a row fingerprint. Take a row holding ab in the first column and c in the second, and a row holding a in the first and bc in the second. Two different rows. If you concatenate before you hash, they produce one digest, and a change-detection job compares them and reports no change.
The multi-argument form keeps the boundary between the columns for you, and it costs nothing.

What does HASH do with a NULL?

This one surprised me. Let's hash a NULL and see.

Query

SELECT HASH(NULL)             AS HASH_NULL,
       HASH('')               AS HASH_EMPTY,
       HASH(NULL) IS NULL     AS IS_HASH_NULL_NULL,
       HASH(NULL, 'a')        AS NULL_PLUS_A,
       HASH('a')              AS JUST_A,
       HASH(NULL, 'a') = HASH('a') AS DOES_THE_NULL_ARGUMENT_MATTER;

Result1 row · 6 columns · scroll →

HASH_NULL	HASH_EMPTY	IS_HASH_NULL_NULL	NULL_PLUS_A	JUST_A	DOES_THE_NULL_ARGUMENT_MATTER
8817975702393619368	-2066104525904144493	FALSE	-6965723239371014079	-947125324004678632	FALSE

HASH(NULL) is a number. Not NULL — an ordinary big integer, and the IS_HASH_NULL_NULL column confirms it by coming back FALSE.

HASH('') is a different number again, so an empty string and a NULL are told apart rather than lumped together.

And the last column. HASH(NULL, 'a') is not equal to HASH('a'). So a NULL argument is part of the input. It is not skipped.

Hold on to this. MD5 and SHA2 do the opposite, and we will see that in a few sections.

Let's use HASH to put rows into buckets

Here is a real use for it. Bucketing. We want to spread the customers evenly over four buckets, without keeping a list anywhere.

MOD of the hash by four would do it, except the hash can be negative, which we saw. So we wrap it in ABS first.

Query

SELECT MOD(ABS(HASH(CUSTOMER_NAME)), 4) AS BUCKET, COUNT(*) AS N
FROM CUSTOMERS
GROUP BY 1
ORDER BY BUCKET;

Result3 rows · 2 columns

BUCKET	N
0	4
1	1
3	5

Three rows came back, not four. Bucket 0 got 4 customers, bucket 1 got 1 and bucket 3 got 5. Bucket 2 is not in the grid at all, because nothing landed in it.

I am leaving that in. Hashing spreads well over a lot of rows. It does not promise anything over ten.

The check that does matter is whether every row landed somewhere legal.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(MOD(ABS(HASH(CUSTOMER_NAME)), 4) BETWEEN 0 AND 3) AS IN_RANGE,
       COUNT(DISTINCT MOD(ABS(HASH(CUSTOMER_NAME)), 4))           AS DISTINCT_BUCKETS
FROM CUSTOMERS;

Result1 row · 3 columns

ROWS_TOTAL	IN_RANGE	DISTINCT_BUCKETS
10	10	3

All 10 rows are in range, and they used 3 of the 4 possible buckets. That is the honest summary. Every row landed somewhere valid, and the spread is what it is.

Now let's fingerprint a whole table with HASH_AGG

HASH works a row at a time. HASH_AGG is the aggregate version. It reduces a whole set of rows to one number.

Query

SELECT HASH_AGG(CUSTOMER_ID, CUSTOMER_NAME, CITY, COUNTRY, SEGMENT) AS TABLE_FINGERPRINT,
       COUNT(*) AS ROWS_COVERED
FROM CUSTOMERS;

Result1 row · 2 columns

TABLE_FINGERPRINT	ROWS_COVERED
-2283804989312148465	10

One number, covering all 10 rows and the five columns we handed it.

Now the property that makes this useful. Does the order the rows arrive in change the answer?

Query

SELECT (SELECT HASH_AGG(CUSTOMER_ID, CUSTOMER_NAME) FROM CUSTOMERS)     AS UNORDERED,
       (SELECT HASH_AGG(CUSTOMER_ID, CUSTOMER_NAME)
        FROM (SELECT * FROM CUSTOMERS ORDER BY CUSTOMER_NAME DESC))      AS REORDERED,
       (SELECT HASH_AGG(CUSTOMER_ID, CUSTOMER_NAME) FROM CUSTOMERS)
         = (SELECT HASH_AGG(CUSTOMER_ID, CUSTOMER_NAME)
            FROM (SELECT * FROM CUSTOMERS ORDER BY CUSTOMER_NAME DESC))  AS SAME;

Result1 row · 3 columns

UNORDERED	REORDERED	SAME
-6722719543365271327	-6722719543365271327	TRUE

The same table, sorted differently, gave the identical fingerprint, and the comparison says TRUE.

So you can compare two copies of a table without caring how either one is sorted. That is exactly what you want when you are asking “did anything change?”.

Let's move on to MD5 and SHA2

These two are not Snowflake's own. They are the standard algorithms, and they give back text rather than a number.

Query

SELECT MD5('Snowflake')          AS MD5_,
       LENGTH(MD5('Snowflake'))  AS MD5_LENGTH,
       SHA2('Snowflake')         AS SHA2_DEFAULT,
       LENGTH(SHA2('Snowflake')) AS SHA2_LENGTH;

Result1 row · 4 columns

MD5_	MD5_LENGTH	SHA2_DEFAULT	SHA2_LENGTH
edf1439075a83a447fb8b630ddc9c8de	32	1dbd59f661d68b90724f21084396b865497173e4d2714f4d91cf05fa5fc5e18d	64

MD5 gave 32 characters. SHA2 gave 64. Both of them are hexadecimal, which is why they look like that.

SHA2 also takes a digest size. Let's ask for all four of them and measure the output.

Query

SELECT LENGTH(SHA2('Snowflake', 224)) AS SHA224_HEX_CHARS,
       LENGTH(SHA2('Snowflake', 256)) AS SHA256_HEX_CHARS,
       LENGTH(SHA2('Snowflake', 384)) AS SHA384_HEX_CHARS,
       LENGTH(SHA2('Snowflake', 512)) AS SHA512_HEX_CHARS,
       SHA2('Snowflake') = SHA2('Snowflake', 256) AS IS_256_THE_DEFAULT;

Result1 row · 5 columns

SHA224_HEX_CHARS	SHA256_HEX_CHARS	SHA384_HEX_CHARS	SHA512_HEX_CHARS	IS_256_THE_DEFAULT
56	64	96	128	TRUE

The four sizes give 56, 64, 96 and 128 hex characters. And the last column answers the question everybody asks: SHA2(x) with no size equals SHA2(x, 256), so the default is 256.

Why does it matter that these are standard?

Because you can check them. These four values are the published test vectors for their algorithms.

Query

SELECT MD5('')               AS MD5_OF_EMPTY_STRING,
       MD5('abc')            AS MD5_OF_ABC,
       SHA2('abc', 256)      AS SHA256_OF_ABC,
       SHA1('abc')           AS SHA1_OF_ABC;

Result1 row · 4 columns

MD5_OF_EMPTY_STRING	MD5_OF_ABC	SHA256_OF_ABC	SHA1_OF_ABC
d41d8cd98f00b204e9800998ecf8427e	900150983cd24fb0d6963f7d28e17f72	ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad	a9993e364706816aba3e25717850c26c9cd0d89d

Look up MD5 of the empty string, or MD5 of abc, anywhere on the internet. You will get these, character for character.

That is the whole difference. Snowflake's own HASH will not match anything outside Snowflake. These will match everything.

What do MD5 and SHA2 do with a NULL?

We asked HASH this a few sections ago and got a number back. Let's ask these two the same question.

Query

SELECT MD5(NULL)             AS MD5_NULL,
       MD5('')               AS MD5_EMPTY,
       MD5(NULL) IS NULL     AS IS_IT_NULL,
       SHA2(NULL) IS NULL    AS SHA2_NULL_IS_NULL,
       NVL(MD5(NULL), 'no digest') AS HANDLED;

Result1 row · 5 columns

MD5_NULL	MD5_EMPTY	IS_IT_NULL	SHA2_NULL_IS_NULL	HANDLED
NULL	d41d8cd98f00b204e9800998ecf8427e	TRUE	TRUE	no digest

MD5(NULL) is NULL. SHA2(NULL) is NULL. Both IS_IT_NULL columns say TRUE.

MD5('') is still a digest, because an empty string is a value. And if you want something other than NULL you have to say so yourself, which is what the NVL in the last column does.

The contrast to remember

HASH(NULL) gives you a number.
MD5(NULL) and SHA2(NULL) give you NULL.
Same family of functions, opposite behaviour on the same input. So check which one you are holding before you build anything on top of it.

Now let's build a row digest properly

A row digest is one short value that stands for a whole row, so you can tell whether that row has changed without comparing every column. Let's build one over four columns of CUSTOMERS.

There are two things in this query that are not decoration, and we will take them one at a time. A | between the columns, and an NVL around the one column that can be empty.

Query

SELECT CUSTOMER_ID,
       MD5(CUSTOMER_NAME || '|' || CITY || '|' || NVL(COUNTRY, '') || '|' || SEGMENT)
         AS ROW_DIGEST
FROM CUSTOMERS
ORDER BY CUSTOMER_ID;

Result10 rows · 2 columns

CUSTOMER_ID	ROW_DIGEST
101	eabe53f6a4f58c8daea69effe15f6c39
102	ba484e5c841543d069cf46b384d7b563
103	8e5e33f09d434f6dbfa1c208275b1946
104	1589575f84e1c190b6869a73788d2719
105	69ee77d77da954535751e0ba265c9457
106	8aea9d7badd8ecd63d7628b26ad1b2c2
107	2f725151ee88974ce820471122904b6d
108	bf0046ef8765237ec403003e6d183d96
109	1aa66035b36f91513934f3171f8c0e07
110	48c13ba6405f3d581527a22d25e3bbc3

One digest per customer, and they all look different. Let's not take “look different” for an answer.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(DISTINCT MD5(CUSTOMER_NAME || '|' || CITY || '|' || NVL(COUNTRY, '')
                          || '|' || SEGMENT)) AS DISTINCT_DIGESTS
FROM CUSTOMERS;

Result1 row · 2 columns

ROWS_TOTAL	DISTINCT_DIGESTS
10	10

10 rows and 10 distinct digests. Good.

So why the separator?

For exactly the reason the HASH section showed, except now we can watch it happen to MD5.

Query

SELECT MD5('ab' || 'c')                       AS NO_SEPARATOR_AB_C,
       MD5('a' || 'bc')                       AS NO_SEPARATOR_A_BC,
       MD5('ab' || 'c') = MD5('a' || 'bc')    AS COLLIDES_WITHOUT_SEPARATOR,
       MD5('ab' || '|' || 'c')                AS WITH_SEPARATOR_AB_C,
       MD5('a' || '|' || 'bc')                AS WITH_SEPARATOR_A_BC,
       MD5('ab' || '|' || 'c') = MD5('a' || '|' || 'bc') AS STILL_COLLIDES;

Result1 row · 6 columns · scroll →

NO_SEPARATOR_AB_C	NO_SEPARATOR_A_BC	COLLIDES_WITHOUT_SEPARATOR	WITH_SEPARATOR_AB_C	WITH_SEPARATOR_A_BC	STILL_COLLIDES
900150983cd24fb0d6963f7d28e17f72	900150983cd24fb0d6963f7d28e17f72	TRUE	75edb597120d0e5addfedc4901a5752a	8a7d22c8204c8d23ca1da728c29313ad	FALSE

Without a separator, MD5('ab' || 'c') and MD5('a' || 'bc') are the same digest and the comparison says TRUE. Put a | between the pieces and the two digests are different.

The separator is what remembers where one column ended and the next began.

And why the `NVL`?

Alpine Logistics has no country. Let's count the digests with and without the NVL and see what that one empty cell does.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(MD5(CUSTOMER_NAME || '|' || CITY || '|' || COUNTRY || '|' || SEGMENT))
         AS DIGEST_WITHOUT_NVL,
       COUNT(MD5(CUSTOMER_NAME || '|' || CITY || '|' || NVL(COUNTRY, '') || '|' || SEGMENT))
         AS DIGEST_WITH_NVL
FROM CUSTOMERS;

Result1 row · 3 columns

ROWS_TOTAL	DIGEST_WITHOUT_NVL	DIGEST_WITH_NVL
10	9	10

10 rows in the table. Without the NVL, 9 digests. With it, 10.

That is the NULL we noticed in the first section. Concatenating anything to a NULL gives a NULL, MD5 of a NULL is a NULL, and so that customer has no digest.

This is the one that bites

It did not error. It did not warn. It quietly produced nothing for one row, and a change-detection job built on that column would never look at that customer again. Wrap every nullable column in your digest.

Now let's look at encoding, which is not hashing

Different job entirely, and the names sit next to each other in the documentation, so this is worth being clear about.

Let's encode some text as Base64 and measure both ends of it.

Query

SELECT 'TechBrothersIT'                          AS PLAIN,
       BASE64_ENCODE('TechBrothersIT')            AS ENCODED,
       LENGTH('TechBrothersIT')                   AS PLAIN_LENGTH,
       LENGTH(BASE64_ENCODE('TechBrothersIT'))    AS ENCODED_LENGTH;

Result1 row · 4 columns

PLAIN	ENCODED	PLAIN_LENGTH	ENCODED_LENGTH
TechBrothersIT	VGVjaEJyb3RoZXJzSVQ=	14	20

14 characters went in and 20 came out. So it is bigger, not smaller. Nothing was compressed and nothing was thrown away.

Now let's decode it again.

Query

SELECT BASE64_ENCODE('TechBrothersIT')                                  AS ENCODED,
       BASE64_DECODE_STRING(BASE64_ENCODE('TechBrothersIT'))             AS DECODED,
       BASE64_DECODE_STRING(BASE64_ENCODE('TechBrothersIT')) = 'TechBrothersIT'
         AS ROUND_TRIPPED;

Result1 row · 3 columns

ENCODED	DECODED	ROUND_TRIPPED
VGVjaEJyb3RoZXJzSVQ=	TechBrothersIT	TRUE

Straight back to the original, and the comparison says TRUE.

Notice what that did not need. No key. No password. No secret of any kind. Anybody holding that string can read it back.

Now let's try to do the same thing to an MD5.

The query that fails

SELECT MD5_DECODE(MD5('TechBrothersIT')) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function MD5_DECODE.

Unknown function. There is no MD5_DECODE, and there cannot be one.

That is the whole difference in one line. A hash throws information away, so there is nothing to decode. Encoding rearranges information and keeps all of it, so of course it reverses.

Let's check the round trip over the real table rather than one literal.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(BASE64_DECODE_STRING(BASE64_ENCODE(CUSTOMER_NAME)) = CUSTOMER_NAME)
         AS ROUND_TRIPPED,
       COUNT_IF(BASE64_ENCODE(CUSTOMER_NAME) = CUSTOMER_NAME) AS UNCHANGED_BY_ENCODING
FROM CUSTOMERS;

Result1 row · 3 columns

ROWS_TOTAL	ROUND_TRIPPED	UNCHANGED_BY_ENCODING
10	10	0

All 10 rows went out and came back unchanged. And the last column is a sanity check on the test itself: 0 rows came out of the encoder looking like what went in, so the encoding definitely did something.

Say this out loud once

Base64 is not security. It is not even obscurity. It is a format — a way of carrying bytes through something that only wants text.

What are those equals signs on the end?

People ask about this every time, so let's just look at it. Base64 works in groups of three bytes. Let's encode one, two, three and four characters and watch the padding.

Query

SELECT BASE64_ENCODE('a')     AS ONE_CHAR,
       BASE64_ENCODE('ab')    AS TWO_CHARS,
       BASE64_ENCODE('abc')   AS THREE_CHARS,
       BASE64_ENCODE('abcd')  AS FOUR_CHARS,
       BASE64_ENCODE('')      AS EMPTY_;

Result1 row · 5 columns

ONE_CHAR	TWO_CHARS	THREE_CHARS	FOUR_CHARS	EMPTY_
YQ==	YWI=	YWJj	YWJjZA==

One character gives two equals signs. Two characters give one. Three characters give none, because three bytes is exactly one full group. Four characters start the cycle over and we are back to two.

And the empty string encodes to nothing at all. The grid draws that last cell as NULL because it has no other way to draw a cell with nothing in it, but what came back is an empty string, not a NULL.

Let's try HEX instead

Hex is the other encoding you will meet. Same idea, different alphabet. Let's run the same text through it and put the two lengths side by side.

Query

SELECT HEX_ENCODE('TechBrothersIT')                              AS ENCODED,
       HEX_DECODE_STRING(HEX_ENCODE('TechBrothersIT'))            AS DECODED,
       HEX_DECODE_STRING(HEX_ENCODE('TechBrothersIT')) = 'TechBrothersIT' AS ROUND_TRIPPED,
       LENGTH(HEX_ENCODE('TechBrothersIT'))                       AS HEX_LENGTH,
       LENGTH(BASE64_ENCODE('TechBrothersIT'))                    AS BASE64_LENGTH;

Result1 row · 5 columns

ENCODED	DECODED	ROUND_TRIPPED	HEX_LENGTH	BASE64_LENGTH
5465636842726F74686572734954	TechBrothersIT	TRUE	28	20

It round-trips the same way, with no key. But look at the last two columns: 28 characters for hex against 20 for Base64.

Hex is bigger because it spends exactly two characters on every byte. Let's prove that rather than assert it.

Query

SELECT HEX_ENCODE('a')   AS ONE_CHAR,
       HEX_ENCODE('ab')  AS TWO_CHARS,
       HEX_ENCODE('abc') AS THREE_CHARS,
       LENGTH(HEX_ENCODE('abc')) = LENGTH('abc') * 2 AS EXACTLY_DOUBLE;

Result1 row · 4 columns

ONE_CHAR	TWO_CHARS	THREE_CHARS	EXACTLY_DOUBLE
61	6162	616263	TRUE

One character in, two out. Two in, four out. Three in, six out, and the last column checks the doubling for us.

So hex costs you more room and gives you something back: you can point at any byte in it and read it. That is why error messages and debuggers use hex and web requests use Base64.

One last thing: MD5's output is already hex

This connects the two halves of the post, and once you see it you will not forget it.

MD5 produces 16 raw bytes. What you normally see is not those bytes. Snowflake also gives you MD5_BINARY, which hands you the raw digest, so we can hex-encode it ourselves and compare.

Query

SELECT MD5('abc')                          AS MD5_HEX,
       LENGTH(MD5('abc'))                  AS THIRTY_TWO_CHARS,
       MD5_BINARY('abc')                   AS MD5_AS_BINARY,
       HEX_ENCODE(MD5_BINARY('abc'))       AS BINARY_RE_HEXED,
       UPPER(MD5('abc')) = HEX_ENCODE(MD5_BINARY('abc')) AS SAME_VALUE;

Result1 row · 5 columns

MD5_HEX	THIRTY_TWO_CHARS	MD5_AS_BINARY	BINARY_RE_HEXED	SAME_VALUE
900150983cd24fb0d6963f7d28e17f72	32	bytearray(b'\x90\x01P\x98<\xd2O\xb0\xd6\x96?}(\xe1\x7fr')	900150983CD24FB0D6963F7D28E17F72	TRUE

Hex-encode the binary digest, upper-case the familiar MD5 string, and the last column says they are the same value.

So the 32-character string you have been reading all along is the binary digest, hex encoded for your benefit. The hashing and the encoding were together the whole time.

Let's put the whole lot on one grid

One query, one customer per row, every function from this post side by side.

Query

SELECT CUSTOMER_ID, CUSTOMER_NAME,
       HASH(CUSTOMER_NAME)                        AS SNOWFLAKE_HASH,
       MOD(ABS(HASH(CUSTOMER_NAME)), 4)           AS BUCKET,
       MD5(CUSTOMER_NAME)                         AS MD5_,
       SHA2(CUSTOMER_NAME, 256)                   AS SHA256_,
       BASE64_ENCODE(CUSTOMER_NAME)               AS BASE64_,
       BASE64_DECODE_STRING(BASE64_ENCODE(CUSTOMER_NAME)) AS AND_BACK_AGAIN
FROM CUSTOMERS
ORDER BY CUSTOMER_ID
LIMIT 5;

Result5 rows · 8 columns · scroll →

CUSTOMER_ID	CUSTOMER_NAME	SNOWFLAKE_HASH	BUCKET	MD5_	SHA256_	BASE64_	AND_BACK_AGAIN
101	Northwind Traders	3049752262830278508	0	d4d09c0707f64c714a6fbeaa7d6d22bc	0aba52c48d2d37a3b61e2c4cd592711f1c9bfdc28d153c66f56332aba48b5b77	Tm9ydGh3aW5kIFRyYWRlcnM=	Northwind Traders
102	Blue Ridge Supply	8226330371505562003	3	8e5e771acc6aaf8372f6a4754a503fe2	63ed2f05ece03ce5d2b0ea048400eec523842e2243b8d5d4a75fda07e0d6bc1e	Qmx1ZSBSaWRnZSBTdXBwbHk=	Blue Ridge Supply
103	Kestrel Analytics	2850134512092016820	0	5f2dc5210fb19c321881bb46ebcb8de3	d9449d5634c557e80de3e56f35ef279602c7b5bf3fe8ccc346c8d563b1600e30	S2VzdHJlbCBBbmFseXRpY3M=	Kestrel Analytics
104	Harbour Foods	7590755392900839451	3	779c747d67c4c9cd54163a461f856b37	b34ee0500e16c8e6f2ea9bde617b195ab2080738aef2fb55017b6055d590a59a	SGFyYm91ciBGb29kcw==	Harbour Foods
105	Alpine Logistics	-5978403927361676976	0	2232cfa6c8f1ebc84f2dcc54517a55d1	8e4e9478e0c18ce77acbf58c062b74af4020c1c7d99100dc99cd1b6bdf7ad2ac	QWxwaW5lIExvZ2lzdGljcw==	Alpine Logistics

Snowflake's signed integer hash, the bucket it falls in, the standard MD5 and SHA2 digests, the Base64 encoding, and the Base64 decoded back to the name it started as.

The last column is the only one you could ever get the original out of. That is the whole post in one row.

Key points

HASH returns a signed integer, so wrap it in ABS before you MOD it.
HASH is fast and not cryptographic. Bucketing, joins and change detection, never security.
Concatenating columns before you hash throws away the boundary between them, so two different rows can produce one digest. Use the multi-argument form, or put a separator in.
HASH(NULL) is a number. MD5(NULL) and SHA2(NULL) are NULL.
An unwrapped nullable column in a row digest loses the whole row quietly — 9 digests out of 10 rows here, with no error. NVL every nullable column.
HASH_AGG fingerprints a whole set of rows and does not care what order they arrive in.
MD5 is 32 hex characters. SHA2 defaults to 256 and gives 64, and the four sizes give 56, 64, 96 and 128.
Encoding is not hashing. BASE64_DECODE_STRING reverses BASE64_ENCODE with no key at all, and MD5_DECODE does not exist.
Hex spends exactly two characters per byte, which is why it is longer than Base64 and easier to read a single byte out of.

Next in this series

Episode 84 — the bitwise functions. BITAND, BITOR, BITXOR, BITNOT and the two shifts, packing several conditions into one integer column, and a reconciliation check that passes on data that is wrong.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The CUSTOMERS data is invented demo data created by that script. The MD5, SHA2 and SHA1 values shown for the empty string and for abc are the published test vectors for those algorithms, so they can be checked against any other source. Nothing here is timed and no performance or security claims are made anywhere in this series. Function behaviour follows the Snowflake hash function documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 83 - HASH, MD5, SHA2, BASE64, HEX - Hashing and Encoding

Let's create some sample data

Let's start with Snowflake's own HASH

Now let's hash more than one column

What does HASH do with a NULL?

Let's use HASH to put rows into buckets

Now let's fingerprint a whole table with HASH_AGG

Let's move on to MD5 and SHA2

Why does it matter that these are standard?

What do MD5 and SHA2 do with a NULL?

Now let's build a row digest properly

So why the separator?

And why the `NVL`?

Now let's look at encoding, which is not hashing

What are those equals signs on the end?

Let's try HEX instead

One last thing: MD5's output is already hex

Let's put the whole lot on one grid

Key points

No comments:

Post a Comment

Label

Snowflake SQL Tutorial 83 - HASH, MD5, SHA2, BASE64, HEX - Hashing and Encoding

Let's create some sample data

Let's start with Snowflake's own HASH

Now let's hash more than one column

What does HASH do with a NULL?

Let's use HASH to put rows into buckets

Now let's fingerprint a whole table with HASH_AGG

Let's move on to MD5 and SHA2

Why does it matter that these are standard?

What do MD5 and SHA2 do with a NULL?

Now let's build a row digest properly

So why the separator?

And why the NVL?

Now let's look at encoding, which is not hashing

What are those equals signs on the end?

Let's try HEX instead

One last thing: MD5's output is already hex

Let's put the whole lot on one grid

Key points

No comments:

Post a Comment

And why the `NVL`?