in this video we're going to take a look at how to generate random data within snowflake
now snowflake offers several functions out of the box to allow you to do some of these things
things like random rand string and in this video we'll go through a few of these
one at a time and show you how they work
so first off let's take a look at the random function this is the most basic one it obviously
is called random and as it says here each call returns a pseudo-random 64-bit integer here's an
example it's just going to return these integers some are negative some are not so what we'll do
is a select random so you can see what this looks like and down here we can see this number if we
run it again it's a different one again something new and that's exactly how it's going to work
there may be some use cases for you to use that just by itself like that to get a random integer
but what we'll see here is as we move into some other data generation functions we can actually
use the random function as an argument in some of the other ones it'll make more sense in a bit
next we'll move on to uniform if we take a look at uniform it says it's going to return a uniformly
random number in the inclusive range so what we'll need to do here is within the function provide a
minimum a maximum and then some sort of generator expression and that generator expression is where
we can start to use something like random what this is saying is between the numbers 1 and 10
give us a random number based on this expression so let's try to mimic that here we'll go
select uniform 1 10 minimum maximum and then random see we get 5 9 looking down here 10
so we can see that so to show you how that random function makes a difference let's do this again
but this time let's just put a hard-coded value let's say 10 right or 100 doesn't matter
and we'll see we'll get the number nine here for that one to ten by a hundred if we do it again
nine again this one change to four seven nine so it's going to keep showing nine because that
expression is the same that's why using something like random is helpful because it'll switch it up
so when you're trying to create random data doing it this way will give you those random values
all right next up rand string let's get into some random string generator functions here
and just like it sounds this is going to return a random string of a specified length all we
need to do here is provide a length and then an expression again so again here is where random
is helpful it's going to operate very similar to this one but in this case we'll say
select rand str length let's just say 15 and then we'll do random
and here's the value down here did it again but we just gave a random expression let's just say
whatever it's going to give us the same result every time so we see
this result here same thing it's the same input so the same output whereas over here we're
giving different inputs and getting different outputs based on that expression every time
speaking of random strings another string generating function is the uuid string so
uuid generates a compliant uuid as a formatted string and this is a universally unique identifier
and you'll see this a lot in certain tools and applications that they actually set
their primary keys as uuids i've started to see that more and some of the tools that are used so
it'll look something like this it's a mixture of letters numbers dashes but always in this
format here so if we hop over here and run the same query select uuid string
we get something that looks like this run it again something new something new and in
theory the idea here is that every single time you run this this truly is going to be a unique
strength you should never ever get the same one twice technically with a random
i guess technically it is possible to get the same one twice depending on how
you do it very very unlikely but it shouldn't happen whereas this it should always be unique
next let's take a look at the generator function here
and what this is going to do is allow us to create a number of rows based on the specified number
so here's an example here we can create a table so we can combine this with the table function here
essentially to create n number of rows so in this case 10 rows so let me show you how this works and
this is going to help us start to tie this all together so let's say we want to select random
from table and then here is where we're going to use that generator function so we'll say
generator and then row count pointer and then the number so let's say 10 so we're going to
do select 10 random values and here we go we can see 10 records 10 rows random values do it again
same thing and you can change this to 100 how you know whatever you want say 100 now we have
100 random values and you can start to see how this is going to come together for random data
one last function that i'd like to show you is the sequence and create some increasing integers
it says with wrap around it which looks like something like this you know you see
here we get to 127 it starts going back around to 125 125. i'm not going to get too far into that
basically this is just a way to create some sequences if you want
alternatively you could use row number to accomplish something similar
the issue is really going to come into play where you start to use really big numbers
depending on the sequence that you choose let's say for example we want to do select
sequence i'm going to say eight there's one two four and eight select sequence eight and then from
then we'll do the same table generator so that we can create a
sequence up from to 100 and here we can see 0 1 2 3 4
all the way down to 99 maybe you want the first one to be number one
so again alternatively you could do row number order by let's say sequence eight and we'll get a
similar result but this time it's starting at one whereas this regular sequence is starting at zero
another way you could incorporate this would be maybe you want to
increment dates so you could do a date add you know let's say day sequence eight
and the starting point let's say the first of this year which would be 2021. let's run this
and here we can see it's auto incrementing the day using that sequence input
now let's start to put it all together putting it all together
and i'll just copy and paste a query here that i have that that uses each of these
functions that we've talked about puts it into a cte and then we'll select all from it so you can
think of as an example maybe you want to generate some random data into some table and then use it
for something later and also you know perhaps you want a hard-coded value as well with this
sometimes you need to join to something specific i don't know let's add here a a hard-coded value
just in case you know someone needs a hard-coded value and let's run so now we have a hundred rows
you know this hard-coded value is looking great over here we got random strings decimals
uids sequence random integers if you were to do it again some of these values would change so again
totally up to you on what you want to do here let's say we want a thousand records so you can
very quickly spin up test data that you can use for either your qa purposes to assign different
values to things or whatever you want so hopefully that was helpful and thank you for watching