Views - Part I

1. Views - Part I

Now we get to talk about views. And no, I don’t mean political views, or views of a cityscape. I’m talking about the very best kind of view: Views in the land of data. Anyway, learning about Snowflake views is important because, one, they’re useful, and two, they pop up all the time. So even if you’re like: “look, I’m a simple human with simple tastes; I don’t need views in my life – basic tables suffice,” you’re still going to need to learn about them because you’ll run into them everywhere. In fact, if you’ve been looking closely, you might have already noticed them a few times in this course. There are two kinds of views – standard, non-materialized views and materialized views. Going forward, when we’re talking about a materialized view, we’ll always specify that, so if we just say “view,” that means we’re talking about a non-materialized view. And don’t worry – we’ll cover both. Okay, so let’s first look at an example of a standard view. Let’s open a SQL Worksheet called “Views,” and take a look at the three views we created in our “Stages and Basic Ingestion” video – At the time, we just ran this part of the code without worrying about what it was doing, but now we’re coming back and processing what we did. The only difference between the code we’re seeing here and the code we ran earlier is I switched “CREATE OR REPLACE VIEW” to just “CREATE VIEW,” since we’ve already made this view and don’t want to run this code and accidentally redo work we’ve already done, though it wouldn’t be a big deal if we did, in this particular case. So what is this code doing? It’s creating an object called a view, and in the first example, it’s naming this view “orders_v” and putting it in the “harmonized” schema inside the “frostbyte_tasty_bytes” database. Then you can see that it’s populating the view with a SELECT statement – so it’s going CREATE VIEW [view name] AS SELECT and then it has the rest of a query, including a bunch of joins. This thing we’ve created, this view, acts like a table in some ways. For example, you can query it: SELECT COUNT(*) FROM frostbyte_tasty_bytes.harmonized.orders_v; But a view isn’t a table. It’s basically just a *saved query*, so when we SELECT COUNT(*) FROM the view, it has to actually run the query represented by the view before it can give us the result. And this brings us to the key thing to know about *materialized* views. If when we created this view, instead of running CREATE VIEW we had run CREATE MATERIALIZED VIEW, it would have saved the results, almost like a new table. Except in this case, and this is getting in the weeds, we couldn’t have used a materialized view because you can’t create a materialized [view](https://docs.snowflake.com/en/user-guide/views-materialized#limitations-on-creating-materialized-views) with joins in it. Anyway, querying that materialized view going forward would be much faster than querying the same thing as a plain view. But this materialization comes with costs, because materialized views automatically update every time the underlying data updates, so whether you want a *view* or a *materialized view* depends on a bunch of factors. That was a lot, so just to recap – A standard view saves the query itself, making it easy to run again and again. A materialized view saves the results of running a query, and automatically updates the results when the table or tables that view is based on update. Before we dig further into why you might want to use a view or a materialized view instead of a normal table, let’s talk about what commands you can run with views. In this video, we won’t cover the UI versions of these commands, because creating a view through the UI is almost exactly the same as creating a table through the UI, which we did in the last video. Okay, so here are the commands you can run with views. You can: One, **create** a view, as we’ve discussed. Two, you can **drop** a view – but be careful, unlike with databases, schemas, and tables, you can’t *undrop* a view, You’d need to recreate it. Three, you can **show** views. Four, you can **describe** views. And five, you can “**alter**” a view, but we haven’t covered alter commands much in this course outside the section on virtual warehouses, and we won’t get into that here. All of these commands work with both regular views and materialized views. Let’s create a new view and show the views, and then drop our view and show the views again: CREATE VIEW frostbyte_tasty_bytes.harmonized.brand_names AS SELECT truck_brand_name FROM frostbyte_tasty_bytes.raw_pos.menu; We’ll call this “brand_names” and we’ll pull from the “menu” table. Then we run: SHOW VIEWS; And we can see BRAND_NAMES listed in the harmonized_schema. The “show” command, as with “show databases,” “show schemas,” and “show tables,” will list all of the relevant objects across the databases we have access to – in this case, views – and give us some metadata on those. Then if we run the DROP VIEW command to drop our new view, we should see that it successfully dropped. DROP VIEW frostbyte_tasty_bytes.harmonized.brand_names; And we can confirm by running the “SHOW VIEWS” command again. SHOW VIEWS; And sure enough, the harmonized schema now only has two views – brand_names is gone. The last command we wanted to cover is DESCRIBE VIEW, which gives you information about whatever view you specify. So if we want to know more about our orders_v table, we can run: DESCRIBE VIEW frostbyte_tasty_bytes.harmonized.orders_v; And we can see each column name, column type, whether there are primary keys, etc. In this video, we created a standard view using the CREATE VIEW command, we dropped a view using the DROP VIEW command, we listed all views we have access to with the SHOW VIEWS command, and we saw metadata about a view with the DESCRIBE VIEWS command. In the next video,” we’ll learn about materialized views, and how they differ from standard views.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.