☃️
dbt / Dataformで管理外のテーブルを削除して、気持ち良く正月を迎えたい

2024/12/19に公開
BigQuery
dataform
dbt
tech
 はじめにこんにちは。ファインディ株式会社でデータエンジニアをやっている田頭啓介(@tagasyksk)です。

この記事は、ファインディ株式会社 Advent Calendar 2024の19日目の記事です。
皆さん、年末といえば何を想像しますか？

そうです、みかん大掃除です。

今回は、dbt/Dataformで管理されていないBigQueryテーブルを棚卸しするスクリプトをご紹介します。

 なぜ棚卸しが必要なのか？データマネジメントにおいて、データのライフサイクル管理は非常に重要です。データライフサイクルは、以下のようなループで成り立っています。(DMBOKより引用)
データ生成/取得とデータ利用が最も重要なのは言うまでもないですが、データ廃棄も意識するべきステップです。

使われなくなったデータや更新されなくなったデータを適切に破棄しないと、次のようなリスクが生じてしまいます。
データ漏洩した時の影響が増大する
ストレージ費用が増大する
更新されていないテーブルを誰かが知らずに参照してしまい、間違った意思決定に繋がる
僕自身、sandboxデータセットにとりあえず作成したテーブルや、データセットを分割した際の旧データセットを消し忘れがちです。(「後で消せばいいや」→「忘れてた」を何回やったことか...)
という訳で、dbt/Dataformで管理されていないBigQueryテーブルを洗い出すスクリプトを作成しました。

なぜdbtとDataformをどっちもやるかというと、ファインディ株式会社では各事業部ごとに技術スタックを少しずつ変えており、ある事業部ではDataformを、別の事業部ではdbtを利用しているためです。

気になる方は、是非カジュアル面談でお話ししましょう！

https://herp.careers/v1/findy/AC7frF7k9fxm

 スクリプト紹介
 dbt作業環境
$ dbt --version                   
Core:
  - installed: 1.8.6
Plugins:
  - bigquery: 1.8.2
スクリプト
#!/bin/bash -eu

# プロジェクトIDを指定
PROJECT_ID="sample_project"

# BigQueryのテーブルを格納する一時ファイル
BQ_TABLE_LIST_FILE_PATH="bq_table_list.txt"
# dbtのテーブルを格納する一時ファイル
DBT_TABLE_LIST_FILE_PATH="dbt_table_list.txt"

# テーブル名を抽出 (データセット名.テーブル名形式)
DATASETS=$(bq ls --project_id $PROJECT_ID --format=json | jq -r '.[].datasetReference.datasetId')

for dataset in $DATASETS; do
    # 対象から除外したいデータセットはここで弾く
    if [[ $dataset == "excluded_sample_dataset" ]]; then
        continue
    fi
    TABLES=$(bq ls --dataset_id "$dataset" --project_id $PROJECT_ID --format=json | jq -r '.[].tableReference.tableId')
    for table in $TABLES; do
        echo "$dataset.$table" >> $BQ_TABLE_LIST_FILE_PATH
    done
done

# dbt lsでテーブルを抽出
{
    dbt ls --quiet --output json --resource-type model | jq -r '.config.schema + "." + .name';
    dbt ls --quiet --output json --resource-type source | jq -r '.source_name + "." + .name';
    dbt ls --quiet --output json --resource-type snapshot | jq -r '.config.target_schema + "." + .name';
} >> $DBT_TABLE_LIST_FILE_PATH


# dbtで管理されていないテーブルを比較して抽出
echo "Comparing tables..."
grep -Fxv -f $DBT_TABLE_LIST_FILE_PATH $BQ_TABLE_LIST_FILE_PATH > unmanaged_tables.txt

# 結果を表示
echo "Unmanaged tables:"
cat unmanaged_tables.txt

# 一時ファイルを削除
rm $BQ_TABLE_LIST_FILE_PATH $DBT_TABLE_LIST_FILE_PATH


 解説bq ls、dbt ls、jqをぶん回して抽出しています。

dbt lsはresource-typeによって若干アウトプットが違うので、dataset_id.table_idの形式で抽出できるようにしています。

 Dataform作業環境
$ dataform --version
2.9.0
スクリプト
#!/bin/bash -eu

# プロジェクトIDを指定
PROJECT_ID="sample_project"

# BigQueryのテーブルを格納する一時ファイル
BQ_TABLE_LIST_FILE_PATH="bq_table_list.txt"
# dataformのテーブルを格納する一時ファイル
DATAFORM_TABLE_LIST_FILE_PATH="dataform_table_list.txt"

# プロジェクトの全テーブルを抽出
DATASETS=$(bq ls --project_id $PROJECT_ID --format=json | jq -r '.[].datasetReference.datasetId')
for dataset in $DATASETS; do
    # 対象から除外したいデータセットはここで弾く
    if [[ $dataset == "excluded_sample_dataset" ]]; then
        continue
    fi
    TABLES=$(bq ls --dataset_id "$dataset" --project_id $PROJECT_ID --format=json | jq -r '.[].tableReference.tableId')
    for table in $TABLES; do
        echo "$dataset.$table" >> $BQ_TABLE_LIST_FILE_PATH
    done
done

# dataformで管理されているテーブルを抽出
dataform compile --json | jq -r '.declarations[], .tables[] | (.canonicalTarget.schema|tostring) + "." +.canonicalTarget.name' >> $DATAFORM_TABLE_LIST_FILE_PATH


# dataformで管理されていないテーブルを比較して抽出
echo "Comparing tables..."
grep -Fxv -f $DATAFORM_TABLE_LIST_FILE_PATH $BQ_TABLE_LIST_FILE_PATH > unmanaged_tables.txt

# 結果を表示
echo "Unmanaged tables:"
cat unmanaged_tables.txt

# 一時ファイルを削除
rm $BQ_TABLE_LIST_FILE_PATH $DATAFORM_TABLE_LIST_FILE_PATH


 解説ほぼdbtと同じです。

Dataform CLIにはdbt lsコマンドが無いので、dataform compileしてdeclarations(dbtでいうsource)とtables(dbtでいうmodel)を抽出する必要がありました。

 最後に抽出したテーブルについてアナリストや事業部の人に問い合わせ、不要なことを確認して削除したら大掃除完了です。

清々しい気持ちで新年を迎えることが出来ますね。

みなさんも、更新されなくなったテーブルはガンガン消していきましょう！
追伸

元々はINFORMATION_SCHEMAを利用して使われていないデータマートを洗い出すスクリプトも盛り込む予定でした。ただ、コネクテッドシートでスプシに丸ごとBigQueryのテーブルを連携した際、ログがINFORMATION_SCHEMAに残らなくなっていたので断念しました。ﾄﾞｳｼﾃ...😢
現在コミュニティで確認中です✔️

https://www.googlecloudcommunity.com/gc/Data-Analytics/Logs-for-Connected-Sheets-are-not-recorded-in-BigQuery/m-p/847775#M8179
はじめに

なぜ棚卸しが必要なのか？

スクリプト紹介

dbt

解説

Dataform

解説

最後に

Discussion
