- Snowflake Tutorial
- Snowflake - Home
- Snowflake - Introduction
- Snowflake - Data Architecture
- Snowflake - Functional Architecture
- Snowflake - How to Access
- Snowflake - Editions
- Snowflake - Pricing Model
- Snowflake - Objects
- Snowflake - Table and View Types
- Snowflake - Login
- Snowflake - Warehouse
- Snowflake - Database
- Snowflake - Schema
- Snowflake - Table & Columns
- Snowflake - Load Data From Files
- Snowflake - Sample Useful Queries
- Snowflake - Monitor Usage and Storage
- Snowflake - Cache
- Unload Data from Snowflake to Local
- External Data Loading (from AWS S3)
- External Data Unloading (Into AWS S3)
- Snowflake Resources
- Snowflake - Quick Guide
- Snowflake - Useful Resources
- Snowflake - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Snowflake - Schema
Schema is grouping of database objects like tables, views etc. Each schema belongs to a single database. “Database.Schema” is the namespace in Snowflake. While performing any operation, the namespace is required to provide either directly in query or setting up in Snowflake's UI.
In this chapter, we will discuss how to create a database, and view details. Snowflake provides the user two ways to create a database, first one is by using user interface and the second one is by using SQL query.
Working with Schema using Snowflake's UI
Let’s see how to create a Schema using the GUI features.
Creating a schema
Login into Snowflake account using the unique URL. Now click the Databases icon present at the top ribbon. It will navigate to the database view screen. Then click the database name where the you want to create a new schema as shown in the following screenshot −
After clicking the database name, it will navigate you to the database properties page where you can see tables/views/schemas, etc., created inside the database. Now click the Schemas icon, by default, Table is selected as shown in the following screenshot −
It displays list of schemas already created for selected database. Now click the Create icon above the list of schemas to create a new schema, as shown in the following screenshot −
After you click the Create icon, you will be able to see the Create Schema dialog box. Enter the schema name and click the Finish button, as shown in the following screenshot −
A new schema would be created and available with list of other schemas.
Edit / Modify / Alter Schema
Snowflake provides the facility to modify or alter the schema names. Let's see how we can modifiy a Schema name.
Click the Database icon present at the top ribbon. It displays the database page details. Now click the name of a database. It will navigate you to database properties view page. Click Schemas to see the list of available schemas. Select a schema to alter its name and click the Alter icon as shown below.
Its pop ups the Alter Schema dialog box. User can modify the Name. Click the Finish button as shown below.
Now, it displays the updated schema name.
Schemas are present inside a database. To view schemas, we must navigate to Database. Let's see how you can use the UI to view the schemas.
Click the Databases icon present at the top ribbon. It will display the View panel of Database where all the created databases are present. Select a database and click its name to view the schemas under it.
Click Schemas present just above the list of schemas. It will display all the available schemas. Once a database is created, it will generate two schemas by default – Information Schema and Public. Information Schema contains all the metadata of a database.
Use the Create button to create a new schema under the same database. User can create N number of schemas.
Use the Clone button to create another copy of existing schema. To perform this operation, select a schema and click the Clone icon.
The following screenshot demonstrates this feature −
It pops up the Clone Schema dialog box, enter the name of the new schema and click the Finish button.
The following screenshot displays the clone functionality −
In the View panel, you can see the cloned schema. User can delete a schema as well, by selecting a schema and clicking the Drop icon, as shown in the following screenshot −
It pops up a dialog box for confirmation. Click Yes for deletion, else No.
Working on Schemas Using Snowflake's SQL Interface
Let’s see how to create a Schema using the SQL interface features.
First login into Snowflake and navigates to Worksheets. By default, Worksheet is opened once the user logs in else click on Worksheets icon present at top ribbon.
Write a following query to create a schema TEST_SCHEMA under database TSET_DB −
CREATE SCHEMA "TEST_DB"."TEST_SCHEMA"
Click the Run button to execute the query. Result will be displayed in result panel as "Schema TEST_SCHEMA" successfully created.
To alter/modify the schema name, use following query and run it −
ALTER SCHEMA "TEST_DB"."TEST_SCHEMA" RENAME TO "TEST_DB"."TEST_SCHEMA_RENAME"
User can go to view panel and verify the updated name.
To view all listed schemas, user can use the following SQL. It brings details of all listed schemas.
To clone a schema, use following SQL −
CREATE SCHEMA "TEST_DB"."TEST2" CLONE "TEST_DB"."TEST_SCHEMA_RENAME"
To delete a schema, use following SQL −
DROP SCHEMA "TEST_DB"."TEST2"
User can run SHOW SCHEMAS query after each operation to verify whether operation is completed or not.