データ利用状況の可視化 – Premium Audit Log活用 –

データマネジメントチームの森 達也です。

組織内でデータの民主化を進める上で、誰が、いつ、何のデータ、どの機能を使っているかを知る事は重要です。活用状況や課題を教えてくれるユーザもいますが、それ以外のユーザについてはコミュニケーションの機会も少なく、CDPの活用が進んでいるか分からないというケースも多いのではないでしょうか。

本稿では、Webアクセスログから顧客行動を探る様に、CDPのPremium Audit LogからTreasure Data CDPユーザの利用状況を把握するための、データ抽出方法をご紹介します。
* Premium Audit Logの利用にはオプション契約が必要ですので、詳細はカスタマーサクセスまでお問い合わせください。

Premium Audit Logについて

Treasure Data CDPでは全てのユーザの各機能、データへのアクティビティを記録しておりAudit Logとして提供しています。追加オプションとして契約可能なPremium Audit Logは、ローデータを参照する事ができ、目的に応じてSQLによるデータ抽出、集計、可視化が可能です。

計測可能な操作・機能

ログとして計測できる機能は下記になります。コンソール上の各機能の操作と、Data Connector, Query, Workflow, Toolbelt, 外部APIからのデータ処理JOBが含まれています。

• Treasure Data CDP Console
• APIs
• TD Workflow
• TD processing engines
• Audience Studio

データ定義・形式

どのユーザが、いつ、どの機能から、何のアセットに、どんな条件でアクセスしたかが、項目ごとに格納されています。

• Id: Log内で重複のないID
• Time: 操作のあった時間
• Event_name: 操作したカテゴリ。どの機能を使用したか
• 例：Database, Table,File Upload, Queries, Workflow, Jobs, Audience Studio, Userなど
• User_email: 操作したユーザのEmail
• Resource_name: 操作対象のリソース名
• Query_text: クエリーを発行した際のSQL

詳細はこちらのDocumentsをご参照ください。
Premium Audit Log Reference
Premium Audit Log Events

データ格納先

Audit Logは以下のDB, Tableに格納されており、通常のDB, Tableと同じくクエリーによる操作が可能です。デフォルトではAdministrator(管理者)権限を持つアカウントが参照可能ですが、DB権限を付与すればUserでも利用可能です。

• DB名: td_audit_log
• TABLE名: access

データ利用状況の確認方法

具体的なユースケースとして、誰が、いつ、どの機能で、何のデータを使っているかについて、Audit Logからの抽出方法をご紹介します。

誰が使っているか（Who）

Treasure Data CDPコンソールによくログインしているユーザの上位5名を抽出してみます。ここではevent_name = ‘sign_in’でフィルタして、Emailごとに集計しています。

SELECT
  event_name
  , user_email
  , COUNT(1) AS cnt
FROM
  access
WHERE
  TD_TIME_RANGE(time, '2021-07-01', '2021-08-01', 'JST')
  AND event_name = 'sign_in'
GROUP BY
  1, 2
ORDER BY
  3 DESC
LIMIT 5

いつ使われているか（When）

Jobの発行回数を時間別に集計してみます。深夜からお昼前までは多く実行されていますが、その後は落ち着いている様子が分かります。深夜バッチのJobが多く、日中はアドホックJobが多いためと思われます。

SELECT
  event_name
  , TD_TIME_FORMAT(time, 'HH', 'JST') AS hour
  , COUNT(1) AS cnt
FROM
  access
WHERE
  TD_TIME_RANGE(time, '2021-07-01', '2021-08-01', 'JST')
  AND event_name = 'job_issue'
GROUP BY
  1, 2
ORDER BY
  1, 2

どの機能が使われているか（How）

Event_nameを丸めた状態でUUを集計してみます。tableやjobはほぼ全員、WorkflowやQueryは半数、Connection（Integration Hub）は4分の1のユーザが使っている事が分かります。

SELECT
  SPLIT(event_name, '_')[1] AS event_area
  , COUNT(DISTINCT user_email) AS uu
FROM
  access
WHERE
  TD_TIME_RANGE(time, '2021-07-01', '2021-08-01', 'JST')
GROUP BY
  1
ORDER BY
  2 DESC

何のDB, Tableが参照されているか（What）

SQLからアクセスされたDB, TableごとにUUを集計してみます。こちらはAdhocなQueryだけでなく、Schedule QueryやWorkflowからの参照も含んでいます。結果は、TD社Websiteのアクセスログは多くのユーザ（9人）が参照しているものの、その他Tableは2, 3人と大差ない事が分かります。なお、デフォルトでは参照Tableのカラムは用意されていないので、query_textからDB, Tableを抽出する前処理が必要です。詳細はクエリとコメントをご覧ください。

-- 存在するDB名, テーブル名リストを取得
WITH access AS (
  SELECT
    *
  FROM
    td_audit_log.access
  WHERE
    (event_name = 'job_issue' AND job_type IN ('HiveJob', 'PrestoJob') AND query_text IS NOT NULL)
    AND TD_TIME_RANGE(time, '2021-07-01', '2021-08-01', 'JST')
)
-- query_textからfrom, toテーブル名のリストを抽出（event_name = 'job_issue'）
-- 前処理: 不要なDDL, DML, コメントを削除。ダブルクオーテーションを削除
, query_log_step1 AS (
  SELECT
    id
    , query_text
    , resource_name
    , REGEXP_EXTRACT_ALL(
        REGEXP_REPLACE(REGEXP_REPLACE(LOWER(query_text), '("|`)', ''), '((delete|insert|create|drop)(\s|\t|\n)|--).+(;|\n|$)', '')
        , '(from|join)(\s|\t|\n|\r\n|\r)+([a-zA-Z0-9_.]+)(\s|\t|\n|\r\n|\r|$|\)|;)'
        , 3
      ) AS extracted_from_table_name_list
  FROM
    access
  WHERE
    query_text IS NOT NULL
)
-- テーブル名のリストを縦持ちに変換
-- DB名とテーブル名を分ける。DBの指定がない場合はresource_nameを入れる
, query_log_step2 AS (
  SELECT
    id
    , query_text
    , IF(
        CARDINALITY(SPLIT(extracted_from_table_name, '.')) > 1
        , SPLIT(extracted_from_table_name, '.')[1]
        , IF(extracted_from_table_name IS NOT NULL, SPLIT(resource_name, '.')[1], NULL)
      ) AS from_database_name
    , IF(
        CARDINALITY(SPLIT(extracted_from_table_name, '.')) > 1
        , SPLIT(extracted_from_table_name, '.')[2]
        , extracted_from_table_name
      ) AS from_table_name
  FROM
    query_log_step1 AS a
    CROSS JOIN UNNEST(IF(CARDINALITY(extracted_from_table_name_list) > 0, extracted_from_table_name_list, ARRAY [NULL])) AS t(extracted_from_table_name)
  WHERE
    extracted_from_table_name IS NOT NULL
)
-- AuditログにIDをキーにINNER JOINする
SELECT
  from_database_name
  , from_table_name
  , COUNT(DISTINCT user_email) AS uu
FROM
  access AS a
  INNER JOIN query_log_step2 AS dt USING(id)
GROUP BY
  1, 2
ORDER BY
  3 DESC

おわりに

本稿では、Premium Audit Logを活用したデータ利用状況の可視化方法をご紹介しました。Audit Logというと、何かあった時に必要な証跡である監査ログとしての役割が強いですが、TDの利用状況を把握して、利用促進に役立てるという積極的な使い方も可能です。

今回は簡単なユースケースのご紹介に留めましたが、それぞれを掛け合わせる事でより深く分析する事も可能です。例えば、誰が x いつ x どの機能から xどのテーブルを使っているかや、時系列における利用傾向がどのように変化したか、を見てみるのも面白いかと思います。導入当初はData Connectorの利用が多かったが、利活用段階ではAudience Studioの利用が増えたや、初期はAdhocなQueryが多かったが、習熟が進みWorkflowの利用が増えた等のデータ活用のステージが見えてくるかもしれません。

次回はより応用的な内容として、Premium Audit Logを活用したData Lineageの可視化についてご紹介できればと思います。最後までご覧頂きありがとうございました。