- 背景: dbt CloudはCIの意味でも便利
- 課題: Pull Requestを閉じてもデータセットは自動では消えない場合がある
- 解決案: 作成から一定期間立ったデータセットはマクロで定期的に掃除させる
- その他の解決策
- 考慮すべき点
背景: dbt CloudはCIの意味でも便利
仕事でdbt Cloudを利用している。SQLやyamlファイルは手元の環境で書いてGitHubにpushする形で生活しているので、実はdbt CloudのIDEにはお世話になっていないのだが、それでも便利に使っている。dbt CloudとGitHubの連携の設定をすると、特にGitHubでPull Requestを作った際にPull Requestに対応したデータセットやテーブルを作ってくれるのが便利である。
dbt_cloud_pr_1862_917のようなPull Requestのidに沿ったデータセットを自動で作ってくれる。schemaが複数ある場合、以下のように複数のデータセットも作ってくれる。
- dbt_cloud_pr_1862_917_staging
- dbt_cloud_pr_1862_917_dwh
- dbt_cloud_pr_1862_917_mart
- dbt_cloud_pr_1862_917_...
これらのデータセットは特に動作確認やPull Requestのレビューで便利である。複数のPull Requestを同時に進める場合、自分でそれぞれのPull Requestに対応したデータセットを手動で作ったりするのは面倒なため、dbt Cloudが勝手にデータセットを作ってそこにテーブルやビューを配置してくれると、確認がとてもしやすい。「成果物はここに置いてあるから、中身見てください!」とレビューを依頼する際にも役に立つ。
課題: Pull Requestを閉じてもデータセットは自動では消えない場合がある
このようにdbt Cloudが自動でPull Requestに対応したデータセットを作ってくれるのは便利ではあるが、明確に一つ課題がある。generate_schema_nameなどカスタムスキーマを使っている場合、Pull Requestを閉じてもdbt Cloudが自動で作ったデータセットが削除されないという点だ。ドキュメントにも明記してある。
dbt Cloud might not drop the temporary schema from your data warehouse if your project has database or schema customization using the generate_database_name or generate_schema_name macros. For more information, refer to Temp PR schema limitations.
DWHの開発をガシガシ進めていると、Pull Requestはどんどんでき、それに応じてデータセット数も無数(数千とか...)に増殖していってしまう。そうすると
- 開発後は参照されないテーブルが大量にできるため、ストレージコストの無駄が発生
- 「このテーブル / カラムはDWHには含みたくないな」と思って本番環境では消したテーブル / カラムが、昔のデータセットでいつまでも残り続けてしまう
- データセキュリティの文脈でもよくない
という課題が出てくる。定期的に掃除したい。
解決案: 作成から一定期間立ったデータセットはマクロで定期的に掃除させる
解決策は実は公式ドキュメントでも書かれているし、issueでも似たような話題がある。
なので、実はこのエントリは不要と言えば不要なのだが、上記のものはSnowflakeを前提に書かれていてBigQueryだとそのままでは動かなかったり、日本語でこの辺のことを書いているエントリがなかったので書いてみた次第。
結論から言うと、以下のようなマクロ(macros/pr_schema_cleanup.sql)を用意する。このマクロを用意した上で、dbt Cloudのジョブにdbt run-operation pr_schema_cleanup --args "{'gcp_project_to_clean':'my-project', 'age_in_days':'31'}"などと書いておけば、作成から31日以上経ったデータセットを定期的に削除していってくれる。
{% macro pr_schema_cleanup(gcp_project_to_clean, age_in_days=31) %}
{% set find_old_schemas %}
select
'drop schema `{{ gcp_project_to_clean }}`.' || schema_name || ' cascade;'
from
{{ gcp_project_to_clean }}.`region-us`.INFORMATION_SCHEMA.SCHEMATA
where
schema_name LIKE "dbt_cloud_pr_%"
and date_diff(current_date("Asia/Tokyo"), date(last_modified_time, "Asia/Tokyo"), day) > {{ age_in_days }}
{% endset %}
{% if execute %}
{{ log('Schema drop statements:', True) }}
{% set schema_drop_list = run_query(find_old_schemas).columns[0].values() %}
{% for schema_to_drop in schema_drop_list %}
{% do run_query(schema_to_drop) %}
{{ log(schema_to_drop, True) }}
{% endfor %}
{% endif %}
{% endmacro %}
特定のプロジェクト内に存在するデータセットのメタデータは、みんな大好きINFORMATION_SCHEMAで取ってこれる。
対象のデータセットが分かったらそれを削除すればいいのだが、データセット内にテーブルがまだ存在すると普通にdrop schemaするだけでは削除できない。CASCADEオプションを付けておくとforce deleteで問答無用に削除してくれる。
その他の解決策
削除対象を作成日時から決めるのではなく、Pull Requestが閉じたことをフックにデータセットを削除するGitHub Actionsのworkflowを書くのもありだと思う。この場合、以下のような考えるべきポイントがいくつかある。
- mergeされないままGitHubでPull Requestがずっとopenになっている場合、データセットがずっと残ってしまう
- 数は多くないと思うのでストレージ観点ではそこまで問題にならないと思うが、データセキュリティ観点では避けたい場合もある
- dbt Cloud以外のGitHub Actionsのworkflow用のサービスアカウントにも強い権限を付与しなければならない
- データセットの削除の権限は結構強い権限が必要。
roles/bigquery.dataEditorだとダメで、roles/bigquery.dataOwnerやroles/bigquery.adminといった強い権限が必要 - 複数箇所でこういった強い権限を発行するのはなるべく避けられるならば避けたい
- データセットの削除の権限は結構強い権限が必要。
考慮すべき点
Pull Requestがまだ閉じていないのに対象のデータセットを消してしまう場合があって、それはOKなのか?という議論がある。OKかどうかは運用のポリシーによるとは思うけど、個人的には問題ないかなと思う。GitHubのPull Requestのdbt Cloudのチェック欄から再度dbt Cloudのジョブを走らせると、一度削除されたとしても再度データセットを作ってくれるので、レビュー時に必要であればこの運用である程度問題なく回せるかなと思ったので。
