0

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 asset_id and 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.

https://docs.snowflake.com/en/sql-reference/functions/uuid_string

Coldchain9
  • 1,373
  • 11
  • 31

1 Answers1

1

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

NickW
  • 8,430
  • 2
  • 6
  • 19
  • Thank you! I believe I was getting mixed up with the idea of the namespace and a concrete uuid. This makes a lot more sense. – Coldchain9 Mar 13 '23 at 14:48