SnowflakeのAccess Historyでクエリの多いテーブルと列を表示する

内容

SnowflakeのACCESS_HISTORY ビューを利用して、クエリの多いテーブルと列を表示して、テーブルの管理を行う。

下のスライドを参考に3つクエリを紹介する。

• テーブルごとに利用者数を集計してランキング
• 列ごとのアクセス状況
• テーブルの利用者を日ごとに集計

列ごとのアクセス状況をロール単位で集計もがんばったらできそう。

参考にしたクエリ

テーブルごとに利用者数を集計してランキング

select
    obj.value:objectName::string as objName,
    count(*) as uses,
    min(to_date(query_start_time)) as since,
    max(to_date(query_start_time)) as until
from
    snowflake.account_usage.access_history,
    table(flatten(direct_objects_accessed)) as obj
where
    obj.value:objectDomain::string = 'Table'
    and objName not like 'SNOWFLAKE.%'
    and objName not like '%.INFORMATION_SCHEMA.%'
group by
    objName
order by
    uses desc;

Snowsightを利用するとグラフ化もできる。

テーブル数が少ないので、絵が貧相...

列ごとのアクセス状況

select
    obj.value:objectName::string as objName,
    col.value:columnName::string as colName,
    count(*) as uses,
    objName || ':' || colName as fullName,
    min(query_start_time) as since,
    max(query_start_time) as until
from
    snowflake.account_usage.access_history,
    table(flatten(direct_objects_accessed)) obj,
    table(flatten(obj.value:columns)) col
where
    obj.value:objectDomain::string = 'Table'
    and objName not like 'SNOWFLAKE.%'
    and objName not like '%.INFORMATION_SCHEMA.%'
group by
    objName,
    colName
order by
    uses desc;

結果はこちら

Snowsightを利用するとこんな感じ。チャート用にテーブル名と列名を合わせた列名を作成。

テーブルの利用者を日ごとに集計

set object_name = 'AZURE_DATA_FACTORY.PUBLIC.WEATHER';
select
    count(*) as uses,
    to_date(query_start_time) as accessDate
from
    snowflake.account_usage.access_history,
    table(flatten(direct_objects_accessed)) obj
where
    obj.value:objectName::string = $object_name
group by
    accessDate
;

X軸を ACCESSDATE にしてバケット化で日付を選ぶと値のない日も表示してくれて便利。

オブジェクト名が雑だ...