Virtual Warehouses Overview
1. Virtual Warehouses Overview
So far, we’ve done a few things. We’ve learned about the fictional Tasty Bytes dataset, hopped into Snowsight, loaded a worksheet, queried some data, and created a new worksheet. And now you’ve actually run a query in Snowflake on your own! Very fun. One thing we went through very quickly – and I said at the time that we’d return to it in a future video – is the USE WAREHOUSE command. Well, I have good news! This is that future video! And we’re going to have a great time learning about virtual warehouses. So what is a virtual warehouse? The Snowflake docs define it as a “[cluster](https://docs.snowflake.com/en/user-guide/warehouses) of compute resources in Snowflake.” I like it when things are concrete, so when I spin up a virtual warehouse, I think about the cluster I’m accessing from the Snowflake pool from AWS, GCP, or Azure. In my mind’s eye, I can see the machine in, say, the AWS Oregon West region, and I’m now in command of some “[CPU](https://docs.snowflake.com/en/user-guide/warehouses), memory, and temporary storage” resources. In this video, we’ll talk about what “USE WAREHOUSE” does. We’ll also learn how to create a virtual warehouse, and how to list your existing warehouses. Warehouses are fun, so let’s get into it! You use a warehouse for a couple of things – [executing](https://docs.snowflake.com/en/user-guide/warehouses) most SQL queries, and also for DML commands (data manipulation language commands) that update the data in some way, like deleting or inserting rows, or copying data into a table. So let’s create a new virtual warehouse and do something real with it! When you’re working in Snowsight – again, that’s Snowflake’s browser-based UI – there are often two ways to do something. You can click buttons in the UI itself, or you can write code inside a SQL or Python worksheet or other Snowsight interfaces that accept code. Throughout this course, we’ll often cover both the UI and the code-based ways of doing things. So let’s first create a warehouse through the UI. If we go to the menu on the left hand side of the screen and click on the Admin tab, and then click on “Warehouses,” we see that we already have a warehouse – compute_wh, the one we used in the last video. This is the default warehouse that comes with the trial account. But it looks so lonely. So let’s give it a friend by clicking the “+ Warehouse” button at the top right of the screen. Let’s name it “warehouse_gilberto,” since one of my fellow developer advocates is named Gilberto, and how often do you get to name a warehouse after a colleague while teaching an online course? Not every day. If we click on “type,” we can see that we have the option of making it snowpark-optimized (which is a warehouse with extra memory), but for now we’ll stick with standard. And if we click on size, we can see that we could opt for higher- or lower-performing warehouses. For now, let’s make this one slightly bigger than compute_wh, so we’ll make it small instead of extra small, and we’ll discuss later what using larger warehouses does for you, and how to switch warehouse sizes even mid-workflow. We’ll ignore the advanced options for the moment, and go ahead and create our virtual warehouse. Although the term warehouse sounds like a place where you store things, in Snowflake, the warehouse is for compute, not storage. And just to define our terms, storage refers to where your data is stored, and compute refers to where your queries are processed and usually requires data to move from storage into the compute nodes. Now that we have a warehouse of our own, you can see that it automatically comes online as “started.” This means there are real compute resources in Snowflake that you’re controlling, and we’re also getting billed for it, which we’ll discuss more later. If we click on it, we’ll see that it’s not doing anything yet. So let’s change that. Let’s hop back to our “Worksheets and a Simple Example” worksheet and scroll down to our sample queries. If we look at the top right of the screen, we can see the warehouse currently associated with this worksheet, compute_wh. So let’s switch that to our new warehouse, warehouse_gilberto, and check out the menu items for the “Freezing Point” brand of food truck. Of these, mango sticky rice sounds tastiest to me, so let’s look at that one a bit more and see how much of a profit “Freezing Point” makes every time it sells a unit of mango sticky rice. Taking the sales price minus the cost of goods sold will tell us that. So we run the query and see a profit of $3.75. Not bad! So the warehouse we’re using now is one that we created through the UI, but we can also create a warehouse using SQL commands. So let’s do that. We just type “CREATE WAREHOUSE” followed by the warehouse name. Let’s call this one warehouse_dash. (Dash is another one of my colleagues.) Notice that when we created that warehouse, it switched the worksheet warehouse to use the new one. So let’s take a look at all our warehouses at once by running the “SHOW WAREHOUSES” command. The two new warehouses we’ve made are listed here. Note that the new underscore Dash warehouse is extra small. That’s because we did not specify the size when we created it, so it defaulted to XS. Before, we switched from compute_wh to warehouse_gilberto using the UI by clicking on the top right and making the switch there, but we can also switch it by writing a command. All we have to do is type “USE WAREHOUSE” followed by the name of the warehouse. So let’s switch from warehouse_dash back to warehouse_gilberto. That was productive! We learned what virtual warehouses are, how to create them, how to see all of them at once, and how to switch between them. Coming up, we’ll learn about scaling virtual warehouses.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.