はじめに
Snowflakeにはテーブルをコピーするのに非常に便利だと感じたオプションでCLONEとLIKEがあり、従来のCTAS(CREATE TABLE table_name AS SELECT・・・)と比べても便利なので使い分けを紹介していきます。特にCLONEはDISKが二重持ちにならないメリットが非常に大きいと思います。
各コピー方法の概要
- CLONEでの作成・・・データ含むテーブル構造のコピー
- コメントや項目に設定されたデフォルト値やNOT NULL、primary key(※)などの制約もコピーされる
- データはマイクロパーティション単位で元表と共有され、DISKが二重持ちにならない。
(更新されたパーティション分だけDISKを使用する) - CTASと比べて高速
- Secure Data Sharingで共有された表には使用できない
- LIKEでの作成・・・テーブル構造のみのコピー(0件の表になる)
- コメントや項目に設定されたデフォルト値やNOT NULL、primary key(※)などの制約もコピーされる
- Secure Data Sharingで共有された表には使用できない
- CTASでの作成・・・従来型の手法
- SELECT句でコピーするデータや構造を指定できるため汎用性は高い
※Snowflakeでprimary keyやuniqueは制約としては有効にはならない。desc table等でデータの特性を理解できる情報としてのみ利用できる
使い分け
- CLONE
元表からデータを含むテーブル丸ごとコピーしたい場合はこれ一択。 - LIKE
元表からテーブル構造のみコピーしたい場合。一部(少数)のデータのみが入った表を作りたい場合はLIKEで作成した後、INSERT SELECTするのが効率的。 - CTAS
上記CLONEとLIKEが使えない場合(元表がVIEWやSecure Data Sharingで共有された表)や複数の表をjoinした結果をテーブルとして作成したい場合
構文サンプル
CLONE(テーブルを丸ごとコピー)
CREATE OR REPLACE TABLE TABLE_A_CLONE
CLONE TABLE_A
;
LIKE
CREATE OR REPLACE TABLE TABLE_A_LIKE
LIKE TABLE_A
;
CTAS(SQL結果をテーブルにする)
CREATE OR REPLACE TABLE TABLE_AB_CTAS
AS
SELECT A.COLUMN1,A.COLUMN2,B.COLUMN3
FROM TABLE_A A
INNER JOIN TABLE_B B
ON A.COLUMN1 = B.COLUMN1
;
参考:CTASでテーブル定義のみ作成したい(空の表を作成したい)場合、SELECT句にWHERE 1=0
を指定すると0件の表となります。
おまけ
上記のような元のテーブルからのコピーでCREATE TABLEする際にオプションでCOPY GRANTS
を付けると権限を引き継ぐことができる・・はず、なのですが、筆者の環境では安定せず、こちらはもう少し詳しくわかったら書き足したいと思います。
おわりに
CLONEはイミュータブルなマイクロパーティションと権限のポリシー管理により実現できた機能だと想像しています。
SnowflakeはCLOUDの特性を活かしたデータベースだとつくづく思います。
この記事も誰かのお役に当てたら幸いです。
参考URL:
https://docs.snowflake.com/ja/sql-reference/sql/create-table.html#create-table-like