So we talked about permanent tables, temporary tables, and query cache were the last topics that we want to cover. It's actually storing your query as a view. Now a view, all it is, is you can take your query, and you can save it. Almost like a table, but it reruns that same query that you have on the underlying data source. So it's actually follow the example here. So, an example, of when you might want to use a view. So, for permanent tables, if this data doesn't change the underlying IRS data, you just know for a fact that it's updated annually and this is 2015, there can be no new 2015 filers in future years, right? So if this is relatively set in frozen data then you're fine with just storing it as a permanent new table exactly as we did here in our new permanent table. But if you have a table that's changing every hour, or every week, you're going have to rerun this query every single time, because your permanent table here that we just created, again it was created at this certain point in time, and that's a snapshot of what the underlying table that you pulled from was. So your data sources can become immediately out of sync if you have new records that are being added to that original source. So if you instead of running a query and storing those results statically in a permanent table, if you instead wanted to actually rerun the query every time you hit that new table, you can do so in what's called saving that query as a view. So let's take a look at what that looks like. So, it's similar concept to saving as a table. So you see the button instead of save table here is now save as a view. So I'm going to give it a same name, except with a little bit of a different suffix. And now we have a brand new view that's created as a result. Right here. Now you know notice it's a little bit of a different icon, right? Says it's not a permanent table, it's just to let you know in the Vicary web UI that this is in itself a view. And I want you know what the key difference is as I click between the permanent table and the view. So you notice one easy difference. In the permanent table, you can preview the results immediately. Whereas the view, there's no preview of results available. And that's the key difference between a big query view and a big query permanent table. And one of the core concepts of big query views is that they're not what are called materialized views. So the data is not actually stored. They are what are called logical views. So it's not a big query, the query itself is rerun every time when you actually access that particular view. So losing this query here. Let's see what querying that view actually looks like. So you can now query, select star from that view, limit the thousand and run that particular query. So much like you're querying from a table, you've got your project, you've got your data set, you've got the view. And what it's doing behind the scenes, we're selecting all the results from this. But what from this means is actually in and of itself, that query that we have stored inside of that views logic. And if you're really interested to see where that query is actually stored, if you want to edit it or look at the logic behind it, what you can do is go in and click on that view. Click on the details. And unlike the permanent tables where you have the size of the table that store, the view again is not materialized, it's not stored on disks you can't access that size. But the query itself, as we continue to scroll down here, you actually have the query or the logic, or the guts of what actually is powering that view. So now for this view what you can actually do is you can edit that query. It brings it up into here. So say you want to include all paper files not just schools, you could then comment down that last line, and you could save that view in both locations. Here, you'd need to save it as the view. Now this view is fundamentally different. So now that you're familiar with the concept of views and how to create them, and this quick demo with two example use cases where you see view creation online. The first, as we alluded to you earlier, if your underlying data source that you're querying from is constantly changing, and you don't want to go through rerunning your query that creates a permanent table on such a regular frequent basis, you can again set up this behind the scenes queries for this view that will then query that underlying data source. So if this wasn't updated yearly but updated hourly Instead, you could then have your logic sit directly on top of that table by rerunning that queries through the effective of view. So for a quick underlying data changes, views are common, and the other common use case is a little bit more nuanced. But if you want to limit the rows of a particular dataset, like say you had two different groups that are accessing your Google Cloud Platform project, your marketing group, and your analytics group, and you only wanted to reveal for your customers table all records for the Analysis Group then a limited number of records for the marketing group, what you can actually do is, on a per user basis, and you can map users to groups of course. You can actually through view, filter out based on who the known user is that's currently logged in and accessing that query. So you can use something called session user, which will then determine which user is logged in and provide them with a unique view, for lack of a better word, on the underlying data. So instead of having folks query from a static underlying data source like a permanent table, you can actually fire off this more dynamic query behind the scenes, which filter is based on who they're logged in or e-mail address of the user is, which you can also map to a groups state as well. So let's recap those two primary use cases that I've seen are for these authorized views that have rolled up a permission and built into them which is a really nice feature for data access control. And then the second one is for rapidly changing underlying data sources. The way you want to save the query results, but you don't want to keep rerunning your permanent tables every single time. Alright, back to the lecture and a quick wrap up. Now in this session, we covered how you can permanently store the results of your queries into tables. You can then query those tables later. One of the key things to remember is that BigQuery will store the results of all your queries into temporary tables even if you don't specify anything. And these temp tables are around for about 24 hours. And they form the basis of queried cache. Lastly, we covered views, which are saved queries on top of your existing tables. And you can modify the sequel behind the view at any time. Let's get some practice saving queries into tables and views in our next lab.
