🈳

Snowflakeで特定のschemaのテーブルを一気にdropしたい（出来るとは言ってない）

2023/04/20に公開

SQL

Snowflake

tech

前置き

こんにちは。株式会社GENDAのデータエンジニアのこみぃです。

先日、Snowflakeで特定のschemaにあるテーブルを一気にdropする方法を探していたのですが、日本語だとそれっぽい記事がなかったので記事にしてみました。

何がしたかったか？

Snowflakeとdbtを組み合わせてデータ基盤を構築している方は結構いらっしゃると思います。

その場合、ローカルの開発用のDatabaseを別で作ってテストしつつ、本番用のDatabaseは他にある、という構成が多いと思います。
以下のような構成ですね。

ただ、この構成だと過去にテストで作ったけど特に残っている意味はないみたいなテーブルがテスト用のDatabaseに残り続けてしまいますよね。
Snowflakeのストレージは従量課金なので、そんなデータでも料金が継続で発生します。
ストレージの課金はそこまで大きくはないですが、残しておく意味もないので定期的にお掃除したいものです。

DROP TABLEなどで直接テーブルを複数個一気に消すのは不可能（2023年4月時点）

さて、日本語の記事はなかったのですが、フォーラムに以下のような記事がありました。

簡単に和訳しますと、

Q. 複数テーブルを一気にDROPする方法はあるかい？
A. ないけど、SQL2回に分けていいならこうやるといいぜ！！（以下説明）

そういうわけで、結論としては 複数テーブルを一気にDROPする方法は存在しない んですが、さくっとやる方法はありますという感じです。

INFORMATION_SCHEMAを使う

Snowflakeの各DatabaseにはINFORMATION_SCHEMAというものがあり、ここには色々なメタデータが入っています。

今回使うのは INFORMATION_SCHEMA.TABLES です。ここには名前の通りテーブルの情報が入っています。

具体的なやり方

Step 1 SELECTで無理やりSQLを作る

以下のSQLを打ちます。TABLE_SCHEMAというのがスキーマ名が入っているカラムになりますので、適宜書き換えましょう

select_to_create_drop_sql

SELECT 'DROP TABLE ' || table_name || ';' 
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_TYPE = 'BASE TABLE';

Step 2

多分以下のようなDROP分が出てくると思いますので、出てきたDROP TABLEを貼り付けて順番に打ちましょう。

drop_table

+-----------------------------------------+
| 'DROP TABLE ' || TABLE_NAME || ';'      |
|-----------------------------------------|
| DROP TABLE TABLE_TO_DROP_0000000000001; |
| DROP TABLE TABLE_TO_DROP_0000000000002; |
| DROP TABLE TABLE_TO_DROP_0000000000003; |
| DROP TABLE TABLE_TO_DROP_0000000000004; |
| DROP TABLE TABLE_TO_DROP_0000000000005; |
| DROP TABLE TABLE_TO_DROP_0000000000006; |
+-----------------------------------------+

やらかしたらUNDROPでリカバー

DROP TABLE自体がまあまあ危険な操作なのですが、SnowflakeにはUNDROPという必殺技もありますので、他のサービスよりは気軽に打てるかなといったところです。

undrop_table

UNDROP TABLE TABLE_TO_DROP_0000000000001;

本日のまとめ

ちなみに、この「SELECTで無理やりSQLを作る」というテクニックはまあまあ使いますので、覚えておくとちょっと効率が上がることがあります。

そういうわけで、本日のまとめは非常に簡単ですね。

dbtの開発用のDatabaseは適宜お掃除しましょう

結びの言葉

皆様机の上とか自宅の居間とかは綺麗にする方でしょうか？
私はミニマリストを目指しているのもありますし、雑然としているのが苦手なので定期的にお掃除しています。
ミニマリストなら開発環境も、というそんなお話でございました。

最後に一つ宣伝を。
私が所属する株式会社GENDAでは一緒に働く仲間をすごくすごい真剣に求めています。
興味がありましたらぜひお気軽にお声おかけください。
https://genda.jp/

本日はこのあたりで。
それじゃあ、バイバイ！

前置き