Welcome to another demonstration on Snowflake Database. This one is going to be pretty straightforward.
We're going to talk a little bit about playing with JSON and XML data in Snowflake.
A lot of databases are going to purport to be able to do this.
I think Snowflake does this the best, so I'm going to give you a little walkthrough of how we do it here.
So I have some some JSON data that I've put up into my staging area.
And just to kind of show you what that looks like, if I look back here, I've got 500-some files.
Each file has a thousand records of JSON records.
So, for example, this is one JSON record that I have in this file here.
They all follow this format of rec date, first name, last name. They've got an array in here that could be of varying values
This one has one, two, three, four, records. Some will have one record in here. So this file has a thousand different JSON records
with varying numbers of arrays in here, and then 500-some files. So a little over a half million records in total here.
So I already staged that up into the Cloud. I did another video on how you get data up into the Cloud .you can take a look at that.
But what I'm going to do here is ... I want to perform analysis on that type of data.
How do you start performing SQL analysis on this type of stuff? Well, I think Snowflake does it the best.
So here I'm going to just create a table called JSON table. It's just a one-column table of the variant data type.
I'm going to run this here ... create the table for me.
A quick description on the table shows me it's just a one-column table with that variant data type.
And of course, when I do a count of everything, I should get 0 because I just made it.
I just like to show people nothing up my sleeve, nothing is pre-cached or anything like that.
I'm going to load that JSON data into the table here. So, I'm going to go out to my user stage where I have all this log data, this JSON log data.
I'm going to load it in here, and this will take about 16 seconds or so.
Again I'm using the smallest cluster that we have, which is just an extra small cluster here. A one-note cluster
that's going to load about a half million records worth of data right now ... there we go.
So 16 seconds to load ... you know listen after compression 94 meg here ... you know... 528 thousand records.
Here's all the data that I ... or all the files that I've loaded here. Let's get a count star from this table again.
528,000 ... so a half million records of JSON.
Now the great thing about Snowflake is that if I want to start writing SQL on it, I certainly can.
I don't have to parse out the elements of that JSON and put it into some structured table.
I can just perform analysis on it as is without creating tables. So for example, here I'm writing a SQL statement.
I'm just going to get the first thousand records or so where I'm going to get the actual
column itself and then a couple of the elements. I want you to see how I actually get the elements out of this table
using a dot and colon here to go get the different elements.
So I'm going to run this here, get a thousand records back real quick ... so this is the actual data.
Again, this is how we're storing JSON data. I can look at this stuff here ... here's address, city, state, zip
The comments field. This array has one value for customer values in it, and some other fields too.
Look at some of these other fields. This one has a varying array of values ...
look at another one here ... this one has one as well.
So again, we look at the fields that I got here ... I also got the IP, got the req date. You'll notice that they've got the quotes on them.
They're just taking the values as is ... and then including the address portion of this.
So again, the address portion here has some stuff inside of it and cust values is an array with varying numbers of values.
So if I look at the address for all of this, I can look at just that section.
I can also look at just the cust value section with the varying number of elements that it might have in it as well.
So very easy to get that data. Now I'm going to do another example here where I do a little bit more to the data
to start extracting out more stuff into a structure.
So here I'm actually using a dot notation with an address to go get the city, the state, the street address, the zip.
I'm also casting these out into types, which will eliminate these quotes, these double quotes that we have around the values.
And I'm aliasing out the the column names as well here.
So I'm even taking the time stamp here for rec date.
So let me just run this, get a thousand records back ... here it is. Again I'm writing this against JSON data
as is how it's being stored natively. It comes back with a nice structured format here. Parse out all the elements.
Now one of the things that happens is when you start looking at JSON data you get those arrays.
They could be of varying length. And what we've done is we've extended the SQL language
to have a flatten function here. And that's what I'm doing. So on those cust values arrays,
I'm actually flattening it out. So instead of a row, if it's got four values, it's four rows. If it's got two values, it would be two rows.
Aliasing that out as list, and then I'm just saying, hey, show me everything.
So this will show me how Snowflake's kind of looking at the data in here.
So if I do that, I can see now this first record has ... if I look at this here ... it just has one record. The three point two.
The next record has one, two, three, four, five elements in it here. So the one point nine,
three point three, one point seven. These should be rows. The next few rows should be this stuff now.
So zero, one, two, three, four. And I see that here ... zero, one, two, three, four ... all the elements that are in there.
So this is how we flatten out those arrays using that flatten function.
So here's where I'm using that flatten function again. But I'm actually just getting the values here so,
give me the customer values, that array as is, but then give me
the first name, the last name, and then just the actual cust value that's in there.
So let me run this ... so here we are. And we flatten things out here. So it looks like one, two, three, four here.
This one should have four elements ... there it is ... one, two, three ... for this person.
And then here's each one of the values in the row, which is really nice.
So it's vertical decomposition. Taking things from rows and from an array and putting it out into rows here.
Now the other nice thing we can do is, instead of having people write that kind of syntax here,
I can create a view which makes things
in the downstream BI applications or reporting applications or what-have-you look at this
like it's a regular table, right. So if I ran this here, create a "replace the view" based on this stuff, that's all great.
Now when I write SQL against that view, I'm writing just good old SQL.
Looks like any other table, right? But you wouldn't know that behind the scenes, it's actually hitting JSON.
So, if I run this, obviously it comes back fast. Again, this is working on a half million records.
I know it's not a lot. I can make this several million records, and it would come back really darn fast.
Again, his is working on the smallest cluster that we have, an extra small cluster.
Really cool stuff. Now JSON is really popular. XML has been around forever.
We count that as semi-structured data as well.
I'm going to do the same thing over here. The notation is just a little different. So yeah, if you have tons of XML data,
you can see how easy it is to work with that in Snowflake as well.
So again, I've got a table that I'm going to create here called XML table, just one column called source -- it's a variant type. I describe it here.
There's my one-column and, of course, there are no records in here.
I'm going to create a little type here called XML to load some data. By the way, how this data looks,
just so you can kind of see here, I just have a real super small file. It's just some basic XML data
with some book information in here. I think I got this off the internet somewhere ... pretty popular small little data set.
Just a couple records, just to prove out what we're doing here.
One high-level catalog with a repeating number of book arrays or book elements in here
with things that are I need there. So that's what I'm loading in here, just so you can see that.
Here's what we are going to do. We're going to again load our table with that data. So I'm going to run this here.
There it is. So we've loaded that, and now I'm going to just get that source column along with the actual root element.
This is in our documentation. So this is the notation -- go get the root element of catalog.
So I can see here, here's all the XML. This is how we're storing it in the field, as is, just like this.
And then it's telling me, hey, go get the root element. There it is.
Now, I can also tell it to go after using the XML "Get" function here. Just the book section. This will automatically go after
just the first element in here ... so here's the first book section that was in that XML.
And then again, using the the feature that we used above, that flatten function, I'm flattening out the text here.
So what this is going to do here is, this is going to give me the value of all of the XML.
It's going to give me the root element. It's going to give me the ID attribute.
I'm taking out the author, the title, the genre, the price. I'm doing a "to date" on the publish date that's in there.
It's coming out as a strams. So I'm putting a "to date" on it, so if I want to do some date math, I can do that,
and a description here. So if i run this ... running SQL as is on XML data.
So here's book one, all of the information, and here it is across here. Here's book five here. One-o-five and all of its information.
And here it is all parsed out for me. So again, I can tie this to some other structured data
that I have in some other table or some other database or what-have-you.
Again, I'm writing good old SQL on top of this stuff here. Here's where I'm doing a good ole "count star group by,"
so the number of books by each author here in this little 12-record set.
And then again, using the notation here, I can go get the third element in the array if I want here.
So just get the third element. That's all I want. Here it is. And here's book one-o-three that's in that XML.
And then I could take it a step further. Here's where I'm doing some other crafty stuff.
This SQL here will get me the elements of the third element in the array--
zero base, so that's zero, one, two--that's the third element, the third record.
It's also going to show the first element of the third record, so the first element of the third record.
The name of that element, of the first record of the third element, and the actual value. That's what I'm seeing here.
So, here's the actual section we're going after. And I'm going to get, what's the first element name, its author,
and what's the first value, etc. ... and here it is.
So again, just want to show you this because a lot of people are starting to say
there's more and more semi-structured data coming into the enterprise.
Can I have a database that natively handles it? And Snowflake absolutely does this better than anyone else out there.
So if you're interested in it, take a look at what Snowflake can do on semi-structured data.
It works very, very fast. We are storing all of this data-- the JSON, the XML.
When we put it into these tables, we're storing it in a columnar compressed way.
We can go after millions of records very, very quickly.
So take a look. I hope you enjoyed this, and we'll see you soon.