In an effort to get myself in the habit of writing a bit more I going to be publishing what I call "tiny tidbits" which are like teeny micro blogs on things that I've recently discovered that i've found particularly helpful.
Creating a unique identifier from scratch
I found myself in the need for a unique identifier for something I was working on and I came across a new BigQuery function that I had never used before generate_uuid(). It's a super easy way to generate a unique identifier that's not reliant on other fields.
Some things to know about generate_uuid():
It returns a string datatype of 32 characters that follows the format of 8-4-4-4-12 so a unique uuid would look something like 4192bff0-e1e0-43ce-a4db-912808c32493.
The string is returned in lowercase but you can transform the id as you see fit if this structure doesn't suit your needs
For my specific use case I wanted some kind of trail back to the event stream I was creating. Since the grain of my table is a user_id I decided to use the concat() function to append the user_id, a dash for formatting compatibiliy, and then the uuid:
concat(user_id, '-', generate_uuid() as event_uuid That's pretty much it! It was super straight forward and very easy to generate the unique identifiers for my table.