Zenn
❄️

Snowflake: OBJECT_DEPENDENCIES ビューを再帰的に探索してビューが使用しているテーブルの一覧を取得する

2023/03/08に公開
SQL
Database
Snowflake
tech

Snowflake には ACCOUNT_USAGE スキーマに OBJECT_DEPENDENCIES というビューがあり、ビューやストリームなどのオブジェクトが参照している他のオブジェクトの一覧を見ることができます。

https://docs.snowflake.com/ja/sql-reference/account-usage/object_dependencies

…が OBJECT_DEPENDECIES はそのオブジェクトが直接参照しているオブジェクトのみが表示されるため、例えば:

のようなネスト構造になっている場合、ビュー V2 の参照先にはビュー V1 が表示されることになり、最終的にアクセスするテーブル T1 の名前は、さらに V1 をたどらないとわからなくなっています。

ということで、Recursive CTE で REFERENCED_OBJECT_DOMAIN が VIEW である限り掘り続けて、できあがった結果を REFERENCED_OBJECT_DOMAIN = 'TABLE' でフィルタすることで、最終的にアクセスされているテーブルだけを表示してみます。

まずはダミーデータとして適当にネストしたビューを作成します。

create or replace database objreftest;
create or replace table t1 (c1 int);
create or replace table t2 (c1 int);
create or replace table t3 (c1 int);
create or replace table t4 (c1 int);
create or replace view v1 as select * from t1;
create or replace view v2 as select * from t2;
create or replace view v3 as select * from t3;
create or replace view v4 as select * from t4;
create or replace view v12 as select 1 c1 from v1, v2;
create or replace view v23 as select 1 c1 from v2, v3;
create or replace view v34 as select 1 c1 from v3, v4;
create or replace view v123 as select 1 c1 from v12, v23;
create or replace view v234 as select 1 c1 from v23, v34;
create or replace view v1234 as select 1 c1 from v123, v234;

そして、このデータベースについて REFERENCED_OBJECT_DOMAIN が VIEW である限り掘り続けます。

with recursive v as (
    select
        referencing_database,
        referencing_schema,
        referencing_object_name,
        referenced_database,
        referenced_schema,
        referenced_object_name,
        referenced_object_domain
    from snowflake.account_usage.object_dependencies
    where referencing_object_domain = 'VIEW'
    and referencing_database = 'OBJREFTEST'
    union all
    select
        v.referencing_database,
        v.referencing_schema,
        v.referencing_object_name,
        o.referenced_database,
        o.referenced_schema,
        o.referenced_object_name,
        o.referenced_object_domain
    from v
    join snowflake.account_usage.object_dependencies o on
        v.referenced_object_domain = 'VIEW'
        and v.referenced_database = o.referencing_database
        and v.referenced_schema = o.referencing_schema
        and v.referenced_object_name = o.referencing_object_name
)
select
    referencing_object_name view,
    listagg(distinct referenced_object_name, ', ') within group (order by referenced_object_name) underlying_tables
from v
where referenced_object_domain != 'VIEW'
group by view
order by view
;

結果は下記の通り、最終的に参照しているテーブルだけがちゃんと表示されています。

VIEW UNDERLYING_TABLES
V1 T1
V12 T1, T2
V123 T1, T2, T3
V1234 T1, T2, T3, T4
V2 T2
V23 T2, T3
V234 T2, T3, T4
V3 T3
V34 T3, T4
V4 T4

Recursive CTE 便利ですね。

Discussion