Views

[00:00:00]
>> Brian Holt: Views, I like views. Views are not unique to see light, but they certainly have their own kind of spin on them. So this query right here, this is just joining track to album to artist. So if you run this, where is my database? So I need to get rid of that.

[00:00:25]
Git status I'm just going, no, that's fine, okay.
>> Brian Holt: Data.db and I run this query. You can see I'm just getting id, name, title and name. And I'm doing it with two different joints and I'm getting all of the artists, the album names and the tracks, right? Nothing too interesting here.

[00:00:54]
Now imagine like I'm Spotify, and every time that someone goes anywhere in the Spotify UI, you have to show them all three pieces of information about any music that they're looking at, because they wanna see all of it, right? Can you imagine having to do this join any time that you wanna do that?

[00:01:10]
It's like on every page, right? It would be a pain in the but to do it that way. It would be nice if they're on the same table, but they're not. They can't be because of the reasons we've all just talked about. This is what a view is for.

[00:01:24]
A view is basically saying like, it's like a layer on top. It's like an abstraction away from a table where you can say, I'm going to present something to you that looks like a table, but underneath the hood, it's going to be querying other tables underneath it. It's a view into other tables.

[00:01:39]
So basically you're gonna construct a query that's going to define a view. And then from there, you can query that set of data as if it was a table, which is kind of cool, right? You can call this whatever you want. I'm gonna call mine create view, easy tracks as.

[00:02:00]
So here, you're just gonna write your query t.trackId as id, ar.name as artist, al.title as album and t.name as track. From track t, oops, that might be a problem.
>> Brian Holt: It will be, let's try that again.
>> Brian Holt: So shell.clear, we'll just, we'll start again. Create view easy tracks as and then here you're just gonna write your query as if it was a normal SQLite query, t.TrackIs as id, ar.name as artist, l.title as album t.name as track
>> Brian Holt: From track t, join album al on t.album id equals al.album id.

[00:03:50]
>> Brian Holt: And join artist ar on ar.artist id equals al.artist id.
>> Brian Holt: Okay, pretty simple, it's just create view as and then you just give it the query that defines the view. Okay, now I just say select star from easy tracks, limit 10, and now that is a way easier query to wrangle, right?

[00:04:34]
>> Brian Holt: Pretty cool. I think it's pretty cool, it can make your job a lot easier. Lots of really cool reasons to use views. One of them is just simplicity like this. A lot of times what I'll see people use views for, we did this at snowflake, I remember quite frequently.

[00:04:51]
Let's say I'm writing a piece of software that exposes some subset of the large amount of data that I have to you. Let's say I have a media database that you can use and it's a shared view in snowflake which is something they support. And I have TVs, movies, video games, all that kind of stuff but you only bought the music database.

[00:05:19]
I can say, here's my view, which is a subset of my data. And you can query this table as a limited use user, right? But you can't query anything else. That's all my private data that you don't get. You can do a view from that perspective, you can only expose just the view.

[00:05:38]
You could write code that would do that and SQ, nothing is built into escalate would allow you to do some data sharing like that, but it would be a, that's how you would probably implement it, right?
>> Brian Holt: What's problematic about this is that, let's say I use this and then I do a bunch of joins and, some other stuff like that.

[00:06:07]
Every time that I run a query against easy tracks, it's running this query underneath the hood. That could be good, that could be bad. In this case, I imagine it's bad. That's a pretty huge result set, and I don't think it's particularly optimized. And imagine you're Spotify, and you have a hundred million tracks now.

[00:06:30]
Imagine running that query against their table every single time someone opened a webpage, right, it's wild. You just would never wanna do that. So that's the downside of use. It's like kind of hiding the complexity from you. And if you don't know that you're using a view underneath the hood and what that view is actually doing.

[00:06:45]
Yeah, you can end up with some like performance problems, because it's hiding it from you.
>> Brian Holt: One thing that escalate does not support that other databases will do for you something called a materialized view. And so again, referencing my other course here about Postgres, it actually makes that problem go away where you basically run the query and then you just store the results set.

[00:07:12]
And so you actually do get to query a real table as opposed to this virtual abstract table. And then anytime that you want to update it, you just say, hey, materialize this If you rerun yourself and repopulate your data based on the underlying query. But then actually in this lesson, I think I show you down here at the bottom, we optimize the query by about 40,000 using a materialized view, which is wild, right?

[00:07:35]
You go from this astronomically large query to running it in 0.43, 0.75, which is, that's like measuring how many CPU shares or something like that. Fun, crazy stuff. Okay, so all that to say, here's a shiny toy that you don't get to play with because SQLite does not have that feature built in.

[00:07:58]
>> Brian Holt: Another thing that SQLite does not support is you cannot insert into a view. So imagine you just inserted into easy tracks and you gave it the track name, title, name, all that stuff. It wouldn't work because it doesn't have genre and all that stuff underneath it. But if it did have everything in there and you were storing everything in there in Postgres, you can insert into the view and it'll distribute it amongst the three tables that you joined from.

[00:08:24]
SQLite does not support that. I'm kind of okay with that. Not a feature I was using frequently.