Snowflake Create, Clone, Drop Database

  • Post author:
  • Post category:Snowflake
  • Post last modified:July 25, 2020

In this section, you will learn Snowflake DDL database commands which include creating a database, cloning database, dropping database and more. You can run all these commands either from Snowflake web console or from your local machine using SnowSQL CLI.

Create a Snowflake Database

Using CREATE DABATAE you can create a Snowflake database.


CREATE DATABASE EMPLOYEE;
CREATE DATABASE EMPLOYEE DATA_RETENTION_TIME_IN_DAYS 5;

Create or Replace Database

Use OR REPLACE in order to drop the existing Snowflake database and create a new database. You can also do this first by running DROP DATABASE and running CREATE DATABASE


CREATE OR REPLACE DATABASE EMPLOYEE;

Create a Transient database

Use TRASIENT option to create a trasient database. transient databases are not fail-safe and take less storage hence, there is no recovery when data loss.


CREATE TRANSIENT DATABASE EMPLOYEE;

Clone Database

use optional CLONE optional parameter in order to clone the existing database, Cloning database copies all schema, tables and everything from the database.


CREATE DATABASE EMPLOYEE_CLONE CLONE EMPLOYEE ;
+-----------------------------------------------+
| status                                        |
|-----------------------------------------------|
| Database EMPLOYEE_CLONE successfully created. |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.644s

Drop Database

DROP DATABSE is used to drop the existing snowflake database


DROP DATABASE EMPLOYEE;
+--------------------------------+
| status                         |
|--------------------------------|
| EMPLOYEE successfully dropped. |
+--------------------------------+
1 Row(s) produced. Time Elapsed: 0.132s

Show Database

Using SHOW DATABSE, you can see all the tables in the Snowflake account.


SHOW DATABASES;
+-------------------------------+-----------------------+------------+------------+-------------------------+--------------+----------------------------+---------+----------------+
| created_on                    | name                  | is_default | is_current | origin                  | owner        | comment                    | options | retention_time |
|-------------------------------+-----------------------+------------+------------+-------------------------+--------------+----------------------------+---------+----------------|
| 2020-02-23 17:13:40.755 -0800 | DEMO_DB               | N          | N          |                         | SYSADMIN     | demo database              |         | 1              |
| 2020-03-07 19:25:04.549 -0800 | EMPLOYEE_CLONE        | N          | Y          |                         | SYSADMIN     |                            |         | 1              |
| 2020-02-23 17:13:41.635 -0800 | SNOWFLAKE_SAMPLE_DATA | N          | N          | SFC_SAMPLES.SAMPLE_DATA | ACCOUNTADMIN | TPC-H, OpenWeatherMap, etc |         | 1              |
| 2020-02-23 17:13:35.624 -0800 | UTIL_DB               | N          | N          |                         | SYSADMIN     | utility database           |         | 1              |
+-------------------------------+-----------------------+------------+------------+-------------------------+--------------+----------------------------+---------+----------------+
4 Row(s) produced. Time Elapsed: 0.142s

It also shows all default databases that come with account creation.

Set default Database

If you wanted to set the default database.


USE employee_clone

References

NNK

SparkByExamples.com is a Big Data and Spark examples community page, all examples are simple and easy to understand and well tested in our development environment Read more ..

Leave a Reply

You are currently viewing Snowflake Create, Clone, Drop Database