Access Historyは、Snowflakeデータベースで提供されるビューで、Snowflakeアカウントの利用状況を監査・把握するうえで最も役立つデータセットのひとつです。本記事では、Access Historyにどのようなデータが含まれているかを掘り下げたうえで、今すぐご自身のアカウントで試せるさまざまなサンプルクエリを紹介します。
Snowflake Access Historyに含まれるデータ
Access Historyには、アカウント内で実行されたクエリ1件につき1行のレコードが格納されます。各クエリには、アクセスしたオブジェクトや変更したオブジェクトに関する複数のカラムが紐づいています。
まず、対象のクエリを検索するときに便利なカラムが3つあります。
query_id：クエリの一意の識別子user_name：クエリを実行したユーザーquery_start_time：クエリの開始時刻
実行ロールや使用したウェアハウスなど、クエリに関する追加情報が必要な場合は、Access HistoryデータセットをSnowflake Query Historyビューと結合できます。
クエリがアクセスしたオブジェクトについては、次の2つのカラムが用意されています。
direct_objects_accessed：クエリが直接アクセスしたデータオブジェクトのJSON配列base_objects_accessed：クエリが直接または間接的にアクセスしたデータオブジェクト（たとえばビューの元になっているテーブル）のJSON配列
クエリが変更したオブジェクトについては、次の2つのカラムがあります。
objects_modified：クエリによって変更されたオブジェクトを示すJSON配列。INSERT、UPDATE、MERGE、CREATEなど、テーブル内のレコードを更新・挿入・削除するクエリで値が入りますobjects_modified_by_ddl：データベース、スキーマ、テーブル、ビュー、カラムに対するDDL操作に関する情報を保持します。
Direct ObjectsとBase Objectsの違い
direct objects accessedとbase objects accessedの違いを理解するため、user_sales_summaryというビューから2つのカラムを取得する以下のクエリを見てみましょう。
select
user_name,
total_sales
from user_sales_summary
direct_objects_accessedカラムには、user_sales_summaryビューへの直接アクセスとして1件のエントリが入ります。一方、base_objects_accessedカラムには、そのビューを構成する2つの元テーブル（usersとsales）に対応する2件のエントリが含まれます。
Access Historyのデータ保持期間
Snowflake Query Historyなどの他のアカウント使用状況ビューと同様に、Snowflakeは過去365日分のデータを保持します。
Access HistoryはすべてのSnowflakeユーザーが利用できますか？
Access Historyビューは、Snowflake Enterpriseエディション以上を契約しているお客様のみが利用できます。
基本を押さえたところで、よくある疑問に答えるためにご自身のアカウントですぐ試せる実例を見ていきましょう。
1\. 特定ユーザーが過去30日間にアクセスしたテーブルをすべて洗い出す
以下のクエリは、特定のユーザーが過去30日間にアクセスしたテーブルをすべて洗い出す方法を示しています。base_objects_accessedカラムは配列のため、lateral結合とflattenテーブル関数を組み合わせ、配列内の各エントリを1行ずつに展開する必要があります。このパターンは本記事を通して繰り返し登場します。
with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
select
access_history.query_id,
access_history.query_start_time,
access_history.user_name,
objects_accessed.value:objectName::text as object_name,
objects_accessed.value:objectDomain::text as object_domain,
objects_accessed.value:columns as columns_array
from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
where
access_history.query_start_time > current_date - 30
)
コードを展開
object_domain='Table'でフィルタしている点に注目してください。ここを変えれば、次のような関連する疑問にも応用できます。
- ユーザーはどのビューにアクセスしたか？
- どの関数を使用したか？
2\. 特定スキーマでアクセスされたテーブルをすべて洗い出す
特定のスキーマでアクセスされたテーブルをすべて洗い出すには、先ほどのaccess_history_flattened CTEを活用します。Access Historyのobject_nameは常に完全修飾名で、database_name.schema_name.table_nameの形式になっています。そこで、このオブジェクト名をパースしてデータベース名とスキーマ名を取り出し、必要に応じてフィルタリングします。
with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
select
access_history.query_id,
access_history.query_start_time,
access_history.user_name,
objects_accessed.value:objectName::text as object_name,
objects_accessed.value:objectDomain::text as object_domain,
objects_accessed.value:columns as columns_array
from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
access_history_flattened_w_names as (
select
コードを展開
3\. 過去30日間に特定のテーブルへアクセスしたユーザーを一覧化する
テーブル内の機密データにアクセスした可能性のあるユーザーを特定したい場面を考えてみましょう。Access Historyビューを使えば、該当ユーザーの一覧をすばやく把握できます。
with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
select
access_history.query_id,
access_history.query_start_time,
access_history.user_name,
objects_accessed.value:objectName::text as object_name,
objects_accessed.value:objectDomain::text as object_domain,
objects_accessed.value:columns as columns_array
from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
where
access_history.query_start_time > current_date - 30 -- adjust as needed
),
コードを展開
4\. 未使用テーブルを特定する
以前、Access Historyビューを活用した未使用テーブルの特定方法について記事を書きました。詳しい解説はそちらをご覧ください。コードは次のとおりです。
with
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
from snowflake.account_usage.access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
table_access_history as (
select
コードを展開
5\. 未使用ビューを特定する
先ほどのクエリを少し書き換えるだけで、過去30日間に使われていないビューも特定できます。object_domain = 'Table'をobject_domain = 'View'に変更するだけです。
with
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
from snowflake.account_usage.access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
table_access_history as (
select
コードを展開
6\. 特定テーブルで最もアクセスされているカラムを特定する
ここまでの例では、テーブルやスキーマへのアクセスに関する分析だけを扱ってきました。base/direct_objects_accessedフィールドに含まれるcolumns配列を活用すれば、特定テーブルのカラム利用状況までもう一歩踏み込んで分析できます。lateral flattenをもう一段重ねることで、クエリ内でアクセスされたカラム1件につき1行のデータセットが得られます（access_history_flattened_columns CTEを参照）。
with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
select
access_history.query_id,
access_history.query_start_time,
access_history.user_name,
objects_accessed.value:objectName::text as object_name,
objects_accessed.value:objectDomain::text as object_domain,
objects_accessed.value:columns as columns_array
from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
where
access_history.query_start_time > current_date - 30 -- adjust as needed
),
コードを展開
7\. 過去30日間に特定のカラムへアクセスしたユーザーを一覧化する
先ほどの例を応用すれば、特定のカラムにアクセスしたユーザーも簡単に特定できます。
with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
select
access_history.query_id,
access_history.query_start_time,
access_history.user_name,
objects_accessed.value:objectName::text as object_name,
objects_accessed.value:objectDomain::text as object_domain,
objects_accessed.value:columns as columns_array
from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
where
access_history.query_start_time > current_date - 30 -- adjust as needed
),
コードを展開
8\. テーブルを変更したクエリをすべて特定する
あるテーブルがなぜ・どのように変わったのかを調査するとき、そのオブジェクトを変更したクエリやユーザーをすぐに特定できると便利です。テーブルの更新頻度を確認したい場合にも役立ちます。これまでと同じ要領で、objects_modifiedカラムをフラット化すれば、テーブルを変更したクエリをすべて洗い出せます。
with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
select
access_history.query_id,
access_history.query_start_time,
access_history.user_name,
objects_modified.value:objectName::text as object_name,
objects_modified.value:objectDomain::text as object_domain
from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.objects_modified) as objects_modified
where
access_history.query_start_time > current_date - 30 -- adjust as needed
)
select
コードを展開
Ian Whitestone・Co-founder & CEO of SELECT
Ianは、SaaS型のSnowflakeコスト管理・最適化プラットフォームSELECTの共同創業者兼CEOです。SELECTを創業する以前は、ShopifyとCapital Oneでフルスタックのデータサイエンス＆エンジニアリングチームを6年間率いてきました。Shopifyでは、データウェアハウスの最適化とコスト可視化の強化を主導しました。