Databases and Schemas - Part I
1. Databases and Schemas - Part I
In this video we’re going to learn about databases and schemas. We already did a little bit with databases and schemas in the video where we discussed stages – you might remember that we created one database and four schemas – but we mostly skipped over the details. Here, we’ll go deeper, but still with a focus on practice over theory. Before we get into practicing making new databases and schemas, let’s take a look at the ones we’ve already made. If we scroll over to the side, we’ll see four databases. One of these, the Snowflake database, is automatically placed in every Snowflake account, and if you click on it, you’ll see a lot of different schemas, with names like “Account Usage,” and so on. These are there for observability – You can query this Snowflake database and the associated schemas to learn all sorts of things about how your account is getting used. The “Snowflake Sample Data” database came with this trial account, and if you click on it, you’ll see a bunch of schemas associated with this database. The other two databases are ones we made in previous videos. If we click on the “TASTY_BYTES_SAMPLE_DATA” database, which we made right at the beginning of this course, you can see a few schemas, including the “RAW_POS” schema that we created. If you click on that, you’ll see “Tables” listed, and if we click on that, you’ll see the first table we queried in the course – the Menu table. And finally, if we click on “FROSTBYTE_TASTY_BYTES,” the database we generated in the last video, you can see some schemas that might sound familiar – “RAW_POS,” “RAW_CUSTOMER,” “HARMONIZED,” and “ANALYTICS.” We created all four of these, and then created tables and views inside each of them, and finally loaded our data with a COPY INTO command to actually get these populated. If you click “RAW_POS” and then “tables,” you’ll see “ORDER_DETAIL,” “ORDER_HEADER,” etc. After looking at these four databases, you might start to notice that each database can have one or more schemas in it, and each schema can have one or more tables or views or other objects in it. So one way to think about databases and schemas is almost like a file directory where the database is the parent folder, the schema is a folder within that, and then tables and views and other objects are in the schema. This isn’t a complete perspective of databases and schemas, but it is a useful one. We can see this “file path” perspective of databases and schemas in practice when we find a table through the databases menu, we click on the three dots, and then we select “Place Name in Editor.” You’ll see that the database comes first, followed by a dot, then the schema, followed by a dot, and then the table. Unless you’ve selected a default database or schema to use, either through setting context up above or through a “USE DATABASE” or “USE SCHEMA” command, you’ll always need to specify all three parts of this path to query a particular table. Let’s confirm this by running the query “SELECT * FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU” – We see that that works. But then if we delete the database and run that, we get the error: “Cannot perform SELECT. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.” Without a database specified, the system doesn’t know which schema you’re talking about – two schemas in different databases are allowed to have the same name, and it’s the database portion of their path that makes them uniquely identifiable. One more thing to note – When you create a database, it automatically has two schemas in it: The PUBLIC schema, which is the default schema for that database, and the INFORMATION_SCHEMA, which “[contains](https://docs.snowflake.com/en/sql-reference/sql/create-database#general-usage-notes) views and table functions that can be used for querying metadata about the objects in the database” – So if you’re looking for some metadata about something in the database, like a list of all the tables, you can query the relevant table from the INFORMATION_SCHEMA. For example, let’s click on the “INFORMATION_SCHEMA” in the FROSTBYTE_TASTY_BYTES database, and then lets go to “tables” and click “PLACE NAME IN EDITOR,” and SELECT * from that table of tables. SELECT * FROM FROSTBYTE_TASTY_BYTES.INFORMATION_SCHEMA.TABLES; We see Franchise, Menu, Country – all the tables we created for this database, plus a bunch of ones that were automatically created as part of the INFORMATION_SCHEMA. Okay, that’s it for Part I of “Databases and Schemas.” In this video, we learned how to use the UI to see our existing databases and schemas. We also discussed what databases and schemas are, and we learned how to query the information schema to find important metadata, like a list of all the tables in the associated database. Coming up,, we’ll create a database, drop and undrop a database, and more.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.