Prerequisite
Create Database
Database 種類
- transient (沒有Fail-safe的區間,也就是在Time Travel區間後,資料就會被Snowflake刪除)
- permanent(有Fail-safe的區間,在Time Travel區間後,Snowflake內部會將資料保留24小時,預防緊急狀況發生)
Create 方式
- 直接Create (如果同樣的Database名稱被創立過,會Create失敗)
- Create or Replace (如果有存在的DB,會將原本的Drop掉,但原本DB的資料還存在Time Travel的區間內)
- Clone from other database (option:specific time) or other shared database
注意事項:Database的名字只能以英文開頭,中間如果有空白或是特殊字元要包在雙引號內創建Database的時候可以設定DATA_RETENTION_TIME_IN_DAYS創建完Database後,目前當下的Session會直接使用創建後的DB新建的Database,預設會有兩個Schema分別是PUBLIC和INFORMATION_SCHEMA
範例
#Create一般的Database,可以看到一開始的mytestdb2沒給data_retention_time_in_days所以預設是一天,mytestdb3我們設定10,所以是10天create database mytestdb2;
create database mytestdb3 data_retention_time_in_days = 10;
show databases;
#Create database and disable time travel (means can't restore)
create database mytestdb4 data_retention_time_in_days = 0;
show databases;
DROP DATABASE mytestdb4;
show databases;
undrop database mytestdb4;
#Create transient
create transient database mytransientdb;
show databases;
#Create clone db from specific timestampcreate database mytestdb4 clone mytestdb3 at(timestamp => 'Mon, 13 May 2019 11:00:00 +0800'::timestamptz);
show databases history;
comment="from mytestdb3"
Reference