データマネジメントチームの森 達也です。
組織内でデータの民主化を進める上で、誰が、いつ、何のデータ、どの機能を使っているかを知る事は重要です。活用状況や課題を教えてくれるユーザもいますが、それ以外のユーザについてはコミュニケーションの機会も少なく、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の可視化についてご紹介できればと思います。最後までご覧頂きありがとうございました。
