Snowflake - Schema

Schema is grouping of database objects pke 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 cpck the Databases icon present at the top ribbon. It will navigate to the database view screen. Then cpck the database name where the you want to create a new schema as shown in the following screenshot −

After cpcking 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 cpck the Schemas icon, by default, Table is selected as shown in the following screenshot −

It displays pst of schemas already created for selected database. Now cpck the Create icon above the pst of schemas to create a new schema, as shown in the following screenshot −

After you cpck the Create icon, you will be able to see the Create Schema dialog box. Enter the schema name and cpck the Finish button, as shown in the following screenshot −

A new schema would be created and available with pst of other schemas.

Edit / Modify / Alter Schema

Snowflake provides the facipty to modify or alter the schema names. Let s see how we can modifiy a Schema name.

Cpck the Database icon present at the top ribbon. It displays the database page details. Now cpck the name of a database. It will navigate you to database properties view page. Cpck Schemas to see the pst of available schemas. Select a schema to alter its name and cpck the Alter icon as shown below.

Its pop ups the Alter Schema dialog box. User can modify the Name. Cpck the Finish button as shown below.

Now, it displays the updated schema name.

View Schema

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.

Cpck 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 cpck its name to view the schemas under it.

Cpck Schemas present just above the pst of schemas. It will display all the available schemas. Once a database is created, it will generate two schemas by default – Information Schema and Pubpc. 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 cpck 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 cpck the Finish button.

The following screenshot displays the clone functionapty −

In the View panel, you can see the cloned schema. User can delete a schema as well, by selecting a schema and cpcking the Drop icon, as shown in the following screenshot −

It pops up a dialog box for confirmation. Cpck 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.

Create Schema

First login into Snowflake and navigates to Worksheets. By default, Worksheet is opened once the user logs in else cpck 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"

Cpck the Run button to execute the query. Result will be displayed in result panel as "Schema TEST_SCHEMA" successfully created.

Edit/Modify/Alter Schema

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.

View Schema

To view all psted schemas, user can use the following SQL. It brings details of all psted schemas.

SHOW 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.