Condensing MD5 Hash to shorter numeric key/ID

Hello!

I'm designing an application to transform excel reports for output to our Azure and Bigquery data warehouses. The app takes an excel file and creates a metadata table and data table linked on two ID constraints: a uniqueID field which will be populated based on the last load (find max, add 1 and keep going up) and a "ProfileID" field which is an MD5 hash of the incoming column name. This ID is currently up to 32 alphanumeric characters long:

My IT team are looking for a way we can turn this ProfileID in to a shorter key to improve indexing and querying.The shorter key cannot duplicate unless the profileID is the same. This would happen in 12 months when a profile is updated with 2020 data (new row, new ProfileUniqueID, same ProfileID). Upwards of 5,000 rows will be added to this table every year.

Any ideas out there?

Datasets

Best Practices

Apps

Preparation

Accepted answers

jrgo

@Dan_Z

This is completely untested, but thinking this approach could do the trick. basically spitting each character of the hash into rows and then converting the character to it's decimal code then concatenating it back up. The "- 48" is to start it from 0 since the character "0" (zero) decimal code is 48.

Either way, let me know if this works and is still able to maintain a unique value!

Regards,

Jimmy
Teknion Data Solutions

Dan_Agenti.yxmd

Dan_Z

@jrgo @danilang

Thank you both for the insights!

I played around a little and came up with a test solution that produced zero duplicates with 105k uniques. It was an adaptation where I took the first 10 characters of the original 32 character hash and used the HexToInt function after splitting in to rows. After concatenating, I trimmed the integer to 19 characters, converted to INT64 with no issues and no duplicates. (workflow attached).

My metadata table will max out somewhere around 5-6k profiles that will need these unique values (they need to repeat year over year as we update the profiles to a new data year), so if it worked for 105k I feel confident it will work for a tenth of that.

Converting hash to unique int64.yxzp

All comments

jrgo

@Dan_Z

You may be able to LEFT() to 10 chars the hash key. I tested on a table with an md5 key was created and based on just over 300k records, the first 10 chars was still unique.

Jimmy
Teknion Data Solutions

Dan_Z

Hey Jimmy!

Thanks for testing this out - it seems the obvious solution I never thought of!

My data architecture guy is still adamant he wants an integer instead of a string - got any tricks for converting those 10 character strings to unique integer values without duplicates?

Dan

jrgo

@Dan_Z

Either way, let me know if this works and is still able to maintain a unique value!

Regards,

Jimmy
Teknion Data Solutions

Dan_Agenti.yxmd

Dan_Z

Hey Jimmy!

Using a list of 5,485 unique records I came up with 5,485 unique integers! The format had to be "double" because when converted to INT64, 1 record out of all of them was too big to fit. Just for giggles I brought the LEFT statement down to 8 characters and that issue was resolved.

Thanks a million!

Dan

jrgo

@Dan_Z

Glad to hear and thanks for the update!

i do want to mention that on the 300k row table I tested, I did get two instances of duplicate keys when used only the first 9 so something that you may need to consider (I didn’t test 8).

Jimmy
Teknion Data Solutions

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280