[Snowflake] 「アクセス履歴ビュー（Access History）」を使ってみた

大阪オフィスの玉井です。

Snowflakeの新しい機能をおさわりしてみました。

アクセス履歴ビューとは

公式ドキュメントにわかりやすい説明が書いてあるので、わざわざでここで書くのもアレですが、「ユーザーが、いつ、何のテーブルの、どのカラムをクエリしたか」がわかるビューと思っていただければ大体大丈夫かと思います。照会できる期間は過去365日（1年）です。

ちなみに、2021年6月現在、まだプレビュー版（おさわり可）であること、Enterprise以上のEditionではないと使えない、といったところは注意です。

何に役立つのか？という点は後述します。ひとまず実際に使ってみました。

やってみた

ビューの場所

そもそも「このビューはどこにあんねん？」って話ですが、ACCOUNT_USAGEスキーマに存在します。また、デフォルトだと、このビューにアクセスできるのはACCONTADMINロールだけです（権限の設定で他のロールに追加することは可能）。ですので、今回はとりあえず下記の状態でクエリしてみます。

普通にSELECTしてみる

まずは超普通なSELECTをば。

SELECT
  *
FROM
  ACCESS_HISTORY

こんな感じで結果が得られます。

各カラムの詳細はドキュメントに記載があります。

2つのVARIANT型カラムについて

ちょっとわかりにくいのがDIRECT_OBJECTS_ACCESSEDとBASE_OBJECTS_ACCESSEDでしょうか。こちらでもざっくり説明します。

• DIRECT_OBJECTS_ACCESSED
  • そのクエリの対象となったオブジェクト（テーブルやビューなど）の情報
• BASE_OBJECTS_ACCESSED
  • そのクエリの対象となったオブジェクトの基になっているオブジェクトの情報

order_tableを基にして作られたsummary_viewに対してクエリが発行された時、DIRECT_OBJECTS_ACCESSEDにはsummary_viewの情報が格納されますが、BASE_OBJECTS_ACCESSEDにはorder_tableの情報が格納されます。

さらに、この2つのカラムは半構造データとなっています。クエリの対象となったオブジェクトやカラムの情報が、JSON形式で格納されています。

ですので、もう少しわかりやすい形で情報を得ようと思うと、FLATTENを使って、これらのJSONをバラしていく必要があります。

もう少しACCESS_HISTORYをさわってみる

とりあえず、遊び半分に、DIRECT_OBJECTS_ACCESSEDをバラしてみました。各要素を1回バラした後、さらに値だけ特定するために、もう一度バラします。

SELECT
  *
FROM
   ACCOUNT_USAGE.ACCESS_HISTORY H
  ,LATERAL FLATTEN(H.DIRECT_OBJECTS_ACCESSED) D
  ,LATERAL FLATTEN(
     D.VALUE
    // 全要素を再帰的にバラす
    ,RECURSIVE = > TRUE
  ) Dvalue

目的もなく、テキトーにバラしただけなので、実用性は皆無ですが、まあ値がとれたりとれなかったり、想定通りの結果ではありました。

もう少し、具体的なユースケースに沿って、何かクエリを考えないとダメだなあ…と思いながら色々調べていたら、下記の記事で、シンプルな「いつ誰がどのオブジェクトの何のカラムにクエリしていたか」がわかるクエリが紹介されていました。

SELECT
   H.QUERY_START_TIME
  ,H.USER_NAME
  ,B.VALUE : "objectName"::STRING AS Base_Object_Name
  ,ColB.VALUE : "columnName"::STRING AS Base_Column_Read_From
FROM
   ACCOUNT_USAGE.ACCESS_HISTORY H
  ,LATERAL FLATTEN(H.BASE_OBJECTS_ACCESSED) B
  ,LATERAL FLATTEN(
     B.VALUE
    ,RECURSIVE = > TRUE
  ) ColB
WHERE
  NOT(
     B.VALUE :objectName::string LIKE ANY('SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY', '%TABLE_ACCESS_LOGS%')
  )
AND ColB.VALUE : "columnName"::STRING IS NOT NULL
ORDER BY
  H.QUERY_START_TIME DESC

途中までやっていることは（当記事の冒頭のクエリと）同じですが、バラしたデータから、オブジェクト名とカラム名を狙い撃ちでとってきていたり、ACCESS_HISTORY自体に対するクエリはフィルタリングしていたり、「なるほど」を思うクエリでした。

BASE_OBJECTS_ACCESSEDを対象にしているので、表面上のオブジェクトではなく、その裏にある実体のデータがどれだけ使われているか？がわかるようなクエリになっています。

アクセス履歴ビューの使いみち

まず思いつくのは監査目的ですね。誰がどのデータにいつアクセスしたか？がわかるため、然るべき時に、こちらのビューを使った調査が役に立つのでは、と思いました。

もう一つは、使われていないデータの把握です。「誰がどのデータにいつアクセスしたか」がわかるということは、その逆もわかるということです。つまり、「データとして用意してるけど、実際にはほとんど使われとらんなあ」というデータもわかるため、不要なデータの整理に役立てることができます。BIツールでダッシュボードを作って定期的に観測するのもいいかもしれません。

他にも、「このテーブル、もう消そうと思ってるけど、まだ誰か使っとるかな？」という時の確認にも使えるかと思います。

おわりに

Snowflakeの管理を担当している人は、ぜひ活用してほしい機能です。