Snowflakeの未使用テーブルを見つけ出す

Access Historyは、Snowflake DatabaseのAccount Usageスキーマに用意されているビューで、Enterprise Edition以上のすべてのSnowflakeアカウントで利用できます。Access Historyを使うと、各クエリが直接または間接的にアクセスしたSnowflakeオブジェクト(テーブル、ビュー、カラム)を追跡できます。

Direct ObjectsとBase Objectsの違い

クエリがアクセスしたカラムを把握するには、direct_objects_accessedとbase_objects_accessedという2つのカラムに注目します。両者の最大の違いは、ビューの扱い方です。次のビュー定義を例に考えてみましょう。

create or replace view orders_view as (
	select *
	from orders
	where
		not test
		and success
);

select * from orders_viewというクエリは、orders_viewオブジェクトに直接アクセスし、そのベースとなるordersテーブルには間接的にアクセスしています。そのためaccess_historyでは、direct_objects_accessedカラムにorders_viewが、base_objects_accessedにはordersが記録されます。

テーブルが未使用かを判断する際は、ビュー経由で間接的にアクセスされたクエリも漏らさず拾うために、base_objects_accessedを使うことが重要です。

base_objects_accessedのパース

base_objects_accessedは、クエリ実行中にアクセスされたすべてのベースデータオブジェクトを格納するJSON配列です。公式ドキュメントに掲載されている、このカラムの内容例を以下に示します。

1[\
\
2  {\
\
3    "columns": [\
\
4      {\
\
5        "columnId": 68610,\
\
6        "columnName": "CONTENT"\
\
7      }\
\
8    ],\
\
9    "objectDomain": "Table",\
\
10    "objectId": 66564,\
\
11    "objectName": "GOVERNANCE.TABLES.T1"\
\
12  }\
\
13]

各クエリでアクセスされたオブジェクトの配列は、lateral flattenでオブジェクトごとに1行へ展開し、テーブルオブジェクトのみに絞り込めます。具体的には次のとおりです。

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

コードを展開

先ほどのクエリで得た「フラット化済み」のaccess_historyを使えば、テーブルが最後にアクセスされた正確な日時と、そのクエリを実行したユーザーを特定できます。

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

コードを展開

削除候補の未使用テーブルを洗い出す際は、関連するストレージコストもあわせて見えると判断しやすくなります。table_storage_metricsアカウント使用状況ビューを使い、ストレージ単価を1TBあたり月額23ドルと仮定すれば、各テーブルの年間ストレージコストを次のように計算できます。

select
    id as table_id,
    table_catalog || '.' ||table_schema ||'.' || table_name as fully_qualified_table_name,
    (active_bytes + time_travel_bytes + failsafe_bytes + retained_for_clone_bytes)/power(1024,4) as total_storage_tb,
    -- ストレージ単価は$23/TB/月と仮定
    -- ご利用のSnowflake契約に応じて適切な値に変更してください
    total_storage_tb*12*23 as annualized_storage_cost
from snowflake.account_usage.table_storage_metrics
where
    not deleted

ここまでで、テーブルが最後にアクセスされた日時の調べ方と、各テーブルのストレージコストの算出方法を見てきました。これらを組み合わせれば、過去90日間クエリされていないテーブルをすべて洗い出し、削除した場合に見込まれる年間削減額まで可視化できます。

以下のSQLは、Enterprise Edition以上のSnowflakeでのみ利用可能なaccount_usage.access_historyビューを使用します。

dbtをお使いの場合は、より高速に動作する代替版のSQLもご検討ください。

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

コードを展開

access_historyビューへのクエリやフラット化は、処理対象のデータ量が膨大になるため、どうしても実行に時間がかかります。テーブルアクセス履歴をより高速に扱うには、当社のオープンソースdbtパッケージdbt_snowflake_monitoringを使って、データをインクリメンタルにマテリアライズする方法をおすすめします。パッケージを導入すれば、未使用テーブルを特定するクエリも一気にシンプルになります。先ほどのコードは、次のように書き換えられます。

with
table_access_summary as (
    select
        table_id,
        max(query_start_time) as last_accessed_at,
        max_by(user_name, query_start_time) as last_accessed_by,
        max_by(query_id, query_start_time) as last_query_id
    from query_base_table_access
    group by 1
),
table_storage_metrics as (
	select
      id as table_id,
      table_catalog || '.' ||table_schema ||'.' || table_name as fully_qualified_table_name,
      (active_bytes + time_travel_bytes + failsafe_bytes + retained_for_clone_bytes)/power(1024,4) as total_storage_tb,

コードを展開

テーブルを削除すべきか判断する際には、そのテーブルがDDLまたはDML操作で最後に更新された日時を把握しておくと役立ちます。以下のクエリでは、tablesアカウント使用状況ビューを使い、過去1週間以内に更新されたテーブルをすべて取得する方法を紹介します。

select
    table_id,
    table_catalog||'.'||table_schema||'.'||table_name as fully_qualified_table_name,
    last_altered as last_altered_at
from snowflake.account_usage.tables
where
    last_altered > current_date - 7