4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

SnowflakeAdvent Calendar 2022

Day 5

【Snowflake】テーブルコピーするときに便利なCLONEとLIKEとCTASの使い分け

Last updated at Posted at 2022-12-18

はじめに

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

4
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?