Snowflakeで派生先を含めたテーブルの累積参照回数を計算する

背景

以下のブログを参考に、Snowflakeでも派生先テーブルの累積参照回数を計算する。

基礎知識

1. ACCESS_HISTORY ビューによるテーブルへの参照回数の求め方
2. 派生先テーブルの情報を求める
   • ACCESS_HISTORY ビューによる派生先テーブルの情報
3. 再帰クエリの書き方

ACCESS_HISTORY ビューによるテーブルへの参照回数の求め方

ACCOUNT_USAGEスキーマの ACCESS_HISTORY ビューにはどのユーザーがどのテーブルを参照したか記録される。

テーブルごとのアクセス記録を集計し、テーブルごとのアクセス回数を求める。
具体的なクエリはこちらを参照

select
    obj.value:objectName::string as objName,
    count(*) as uses,
from
    snowflake.account_usage.access_history,
    -- ビューなどで間接的に参照した回数も含めるため、`base_objects_accessed` カラムを使用。
    table(flatten(base_objects_accessed)) as obj
where
    obj.value:objectDomain::string = 'Table'
    -- SNOWFLAKEデータベースとINFORMATION_SCHEMAの情報は除く。
    and objName not like 'SNOWFLAKE.%'
    and objName not like '%.INFORMATION_SCHEMA.%'
group by
    objName
;

ACCESS_HISTORY ビューによる派生先テーブルの情報

Snowflake内でCTASなどでテーブル情報を更新している場合、ACCESS_HISTORY ビューにテーブルの更新情報が記録される。

詳しくはこちらを参照

上記の記事ではカラムリネージュの情報を拾っているが、今回はテーブルの情報だけ使用する。

select distinct
    om.value:objectName::string as om_object_name,
    bs.value:objectName::string as bs_object_name
from 
    snowflake.account_usage.access_history as ah, 
    lateral flatten(input => ah.objects_modified) as om,
    lateral flatten(input => om.value:columns) as col,
    lateral flatten(input => col.value:baseSources) as bs
where
    om.value:objectDomain = 'Table'
;

再帰クエリの書き方

再帰CTEを利用することで、階層データに対するクエリを実行できる。

書き方が難しいため、シンプルな例で説明する。

親子関係のテーブル

-- 親子関係のテーブル
create or replace table relationship (
    child number,
    parent number
);
insert into
    relationship (child, parent)
values
    (1, null), -- 最上位の親の情報も含める。
    (10, 1),
    (100, 10),
    (101, 10),
    (20, 1),
    (200, 20);

求めたい表

親子関係のテーブルから、以下のように血のつながりがあるParentとChildを列挙したい。
Parent自身も表に含めることで、Childが持つ回数をGroup Byで集計することができる。

CHILD	PARENT
1	1
10	1
20	1
100	1
101	1
200	1
10	10
100	10
101	10
20	20
200	20
100	100
101	101
200	200

再帰クエリ

以下の記事を参考にクエリを作成した。

具体的なクエリは以下の通り

-- 直系の血縁関係を求める。
with recursive rec(child, parent) as (
    --非再帰項(初項)
    select  relationship.child, relationship.child
    from    relationship
    union all
    --再帰項
    select  relationship.child, rec.parent
    from    relationship inner join rec
        on relationship.parent = rec.child
)
    --メインクエリ
    select      child, parent
    from        rec
    order by parent
;

関係性を考慮した累積値の求め方

上記で求めた直系の血縁関係の表と、子が持つ値を集計することで、関係性を考慮した累積値を求める。

子が持つ値

create or replace table child_cnt (
    child number,
    cnt number
);
insert into
    child_cnt (child, cnt)
values
    (1, 15000),
    (10, 12000),
    (100, 13000),
    (101, 6500),
    (20, 7500),
    (200, 9200);

集計クエリ

with recursive rec(child, parent) as (
    --非再帰項(初項)
    select  relationship.child, relationship.child
    from    relationship
    union all
    --再帰項
    select  relationship.child, rec.parent
    from    relationship inner join rec
        on relationship.parent = rec.child
)
    --メインクエリ
    select
        rec.parent,
        sum(child_cnt.cnt)
    from 
        rec left join child_cnt
        on rec.child = child_cnt.child
    group by
        rec.parent
    order by rec.parent
;

テーブルの累積参照回数を求めるクエリ

上記を考慮して、テーブルの累積参照回数を求めるクエリを以下のように作成した。

with recursive child_cnt as (
    select
        obj.value:objectName::string as child,
        count(*) as cnt,
    from
        snowflake.account_usage.access_history,
        table(flatten(base_objects_accessed)) as obj
    where
        obj.value:objectDomain::string = 'Table'
        and child not like 'SNOWFLAKE.%'
        and child not like '%.INFORMATION_SCHEMA.%'
    group by
        child
), rel as (
    select distinct
        om.value:objectName::string as child,
        bs.value:objectName::string as parent
    from 
        snowflake.account_usage.access_history as ah, 
        lateral flatten(input => ah.objects_modified) as om,
        lateral flatten(input => om.value:columns) as col,
        lateral flatten(input => col.value:baseSources) as bs
    where
        om.value:objectDomain = 'Table'
    union all
    -- copy intoなどによる外部からの更新や一年以上前に更新のあったテーブルを含める。
    select
        child,
        null as parent
    from child_cnt
), rec(child, parent) as (
    --非再帰項(初項)
    select  rel.child, rel.child
    from    rel
    union all
    --再帰項
    select  rel.child, rec.parent
    from    rel inner join rec
        on rel.parent = rec.child
)
    --メインクエリ
    select
        rec.parent,
        sum(child_cnt.cnt)
    from 
        rec left join child_cnt
        on rec.child = child_cnt.child
    group by
        rec.parent
    order by rec.parent
;

ACCESS_HISTORY ビューによる派生先テーブルの情報のみでは、以下の情報が不足するため、テーブルへの参照回数の情報からテーブル情報を補足している。

• COPY INTOで外部からデータを挿入されたテーブル(親がいないテーブル)
• 1年以上更新されていないが参照されているテーブル

最後に

再帰クエリは難しいですが、Snowflakeには上記のようなリネージュの情報やビュー・タスクの関係なども記録されているので、積極的に活用していきましょう！