I have a table in Snowflake generated like:
create or replace temporary table example (asset_id int, assignment_id int); insert into example (asset_id, assignment_id) values (5000, 1), (5000, 1), (5000, 1), (6000, 1), (6000, 2); select asset_id, assignment_id, uuid_string(uuid_string(), md5(concat(asset_id, assignment_id))) as uuid_assignment from example;
I’d like to generate a
uuid_string() for the combination of both
assignment_id using an md5 hash. After reading the documentation on
uuid_string() I am a little confused on how to do what I am asking. I know uuid_string() is generating a random uuid, but I want to be able to assign the random generated uuid to a deterministic md5 hash.
For the version of uuid_string that takes parameters, the original use case is for avoiding clashes by including a namespace (so that the same value in different namespaces produces different UUIDs) – so you would have a fixed UUID for each of your namespaces and pass the appropriate value into this function.
The way you have used it, the namespace value will be a random UUID each time you run it and therefore for the same input you will get a different output each time. If that’s not the behaviour you want then you need to provided a fixed string UUID as the first parameter e.g.
uuid_string('8e884ace-bee4-11e4-8dfc-aa07a5b093db', md5(concat(asset_id, assignment_id))) as uuid_assignment
There are some good explanations of UUID generation here