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
Related Articles
- Snowflake – CREATE TABLE LIKE
- Snowflake – CREATE TABLE as SELECT
- Java- Create Snowflake table programmatically
- How to Load JSON file into Snowflake table
- Load file from Amazon S3 into Snowflake table
- How to Load Parquet file into Snowflake table
- Load CSV file into Snowflake Database table
- SnowSQL – Unload Snowflake Table to CSV file