情報を可視化するにあたっての背景
スタンバイにおいて、データを集計し提供するためのチームというのができたばかりであり、集計したデータに対して組織だって管理、改善するという仕組みがまだない状態でした。そこでまず、チーム内で管理しているデータが、どこからどの程度利用されているかという利用実態を可視化し、行動指針や評価におけるひとつの指標として使えるようにしようと考えました。
目的
利用状況の可視化の主な目的は以下の２つです。
データを管理するにあたり、作業方針の決定や作業の影響度合いを知る指標がチームにおいて存在しませんでした。自分たちの作業を定量的に評価できないことは、運用におけるコミュニケーションが個人の感覚に依存した状態となり、データの利活用状況について正しく理解できないリスクがあります。これに対して、まずはチームで管理しているデータについて CloudTrail によって集められているアクセスログを集計、可視化し、データの利用状況についてチーム内で統一の評価ができる状態にします。
また、CloudTrail のログに対して、いつでもクエリできる状態にすることで、データに対する調査の手段として CloudTrail のログというのが存在する状態にします。
取り出したい数字
| 可視化項目 | 数値をどう使うか |
|---|---|
| 総アクセス数の推移 | 社内のデータ利活用が増えているかをみる判断材料 |
| ユーザ数の推移 | 社内でのデータ利活用が増えているかをみる判断材料 |
| アクセスの多いバケットのランキング | バケットの重要度合いをみる |
| アクセスの多いユーザのランキング | 主となる利用者が誰なのかをみる |
| アクセスの多いテーブルのランキング | テーブルの重要度合いをみる |
| バケット単位のアクセスの多いユーザのランキング | バケット単位で主となる利用者が誰なのかをみる |
| テーブル単位のアクセスの多いユーザのランキング | テーブル単位で主となる利用者が誰なのかをみる |
データドリブンによる開発・改善が会社の方針でもあるため、チームで集計したデータがたくさんの社員にたくさん利用される状態が望ましいと考えています。そのため、データの参照数やユーザの数といった数値を主に集計します。これらの数値が数ヶ月を対象とした期間で見られるダッシュボードを作ることをイメージしています。
可視化するまでの手順
1. CloudTrail のログデータをテーブル化する
私が配属するときには、すでに CloudTrail が有効化されており、特定の S3 バケットにデータが溜まっていた状態だったため、CloudTrail を有効化してログをためる手順につきましては割愛させていただきます。CloudTrail の設定手順につきましては、公式の資料をご参考ください。
公式の資料を参考に、Athena から external テーブルを作成します。このテーブルから数値を集計するわけですが、パーティションの設定がないと、集計のたびに全てのデータを参照しにいってしまい時間とコストがかかるため「パーティション射影を使用した Athena での CloudTrail ログ用テーブルの作成」の内容を参考に CREATE TABLE 句を作成することをお勧めします。
ここで注意ですが、テーブル定義の時にカラム名を変更しないようにしましょう。キャメルケースで書かれたカラム名が input format で指定している com.amazon.emr.cloudtrail.CloudTrailInputFormat のどのタイプであるかを識別するため、カラム名を変更すると正常にデータを参照できなくなります。
また、Date 型でパーティションを切ることを試してみましたが、スラッシュ区切りの文字列を Date型として取り扱うことができなかったため、公式の資料にある通り、external テーブルの段階では String の型でパーティションを切っています。
2. CloudTrail のデータから取りたい数値を日毎に集計する
作成した external テーブルに対して、取りたい数値を得るクエリを直接実行してみても良いのですが、数日を対象としたクエリでも結果を得られるまで5分以上かかる状況だったため、日毎にバケット、テーブル、ユーザについてまとめあげたテーブルを別途用意し、そのテーブルに毎日レコードを追加するようにしました。
テーブル定義
CREATE TABLE cloud_daily_metrics (
user_name String,
bucket_name String,
table_name String,
count Bigint
) PARTITIONED BY (date date)
STORED AS PARQUET
LOCATION "s3://bucket/prefix/"
作成したテーブルにデータを INSERT するクエリ(日付はサンプルになります)
INSERT INTO
"db_name"."cloud_daily_metrics"
SELECT
b.user_name AS user_name,
b.bucket_name AS bucket_name,
b.table_name AS table_name,
count(1) AS count,
b.date_jst AS date
FROM (
SELECT
CASE
WHEN userIdentity.type = 'Root'
AND userIdentity.userName IS NULL THEN 'Root'
WHEN userIdentity.type = 'Root'
AND userIdentity.userName IS NOT NULL THEN userIdentity.userName
WHEN userIdentity.type in ('IAMUser', 'SAMLUser', 'WebIdentityUser') THEN userIdentity.userName
WHEN userIdentity.type = 'AssumedRole' THEN split_part(userIdentity.principalId, ':', 2)
ELSE 'other'
END AS user_name,
cast( json_extract( json_parse( requestParameters ), '$.bucketName' ) AS varchar ) AS bucket_name,
split_part( cast( json_extract(json_parse(requestParameters), '$.key') AS varchar ), '/', 1) AS table_name,
date( date_parse(a.eventtime, '%Y-%m-%dT%H:%i:%sZ') at time zone 'Asia/Tokyo' ) AS date_jst
FROM "db_name"."cloudtrail_external_table_name" AS a
WHERE a.timestamp in ( '2021/12/20', '2021/12/21')
) AS b
WHERE b.bucket_name like 'bucket-prefix%'
AND b.date_jst = date '2021-12-21'
GROUP BY b.user_name, bucket_name, b.table_name, b.date_jst
user_name を取り出している CASE 文については、公式の資料を参考に、ユーザ名として有効と思える要素を取り出しております。
bucket_name, table_name は requestParameters の内容から取り出しておりますが、CloudTrail の設定などによっては他のカラムから取ってくることが適切な場合があるかもしれないため、CloudTrail のログの内容を確認した上で設定しましょう。
CloudTrail のログのタイムゾーンはUTCとなっているため、INSERT するタイミングで JST に直した日付を作成しています。JST に変換するにあたり、2日間のデータをインプットとして集計しています。
WHERE b.bucket_name like 'bucket-prefix%' こちらの条件句は、本集計対象がチームで管理するバケットを対象としていたため、バケット名のプレフィックスで指定しています。
3. ダッシュボードを作成する
チームでは re:dash によるダッシュボード作成が主な可視化手段となっていたため、上記クエリにより日毎に集計されたテーブルから、数値を取り出すためのクエリを用意し、ダッシュボードを作成します。
re:dash において作成したクエリ群(中括弧で囲まれた部分は re:dash のUIより設定できる変数となります)
# 総アクセス数の推移
select date, sum(count) as AccessCount from db_name.cloudtrail_daily_metrics
where date >= date '{{ start_date }}'
and date <= date '{{ end_date }}'
group by date
order by date
# ユニークユーザ数の推移
select date, count(distinct user_name) as UniqUserCount from db_name.cloudtrail_daily_metrics
where date >= date '{{ start_date }}'
and date <= date '{{ end_date }}'
group by date
order by date
# アクセスの多いバケットのランキング
select bucket_name as BucketName, sum(count) as AccessCount from db_name.cloudtrail_daily_metrics
where date >= date '{{ start_date }}'
and date <= date '{{ end_date }}'
group by bucket_name
order by AccessCount desc
# アクセスの多いテーブルのランキング
select table_name, sum(count) as sum_access from db_name.cloudtrail_daily_metrics
where date >= date '{{ start_date }}'
and date <= date '{{ end_date }}'
and table_name is not null
group by table_name
order by sum_access desc
# データの利用者ランキング
select user_name, sum(count) as sum_access from db_name.cloudtrail_daily_metrics
where date >= date '{{ start_date }}'
and date <= date '{{ end_date }}'
group by user_name
order by sum_access desc
# バケット別ユーザランキング
select user_name as UserName, sum(count) as AccessCount from db_name.cloudtrail_daily_metrics
where date >= date '{{ start_date }}'
and date <= date '{{ end_date }}'
and bucket_name = '{{ bucket_name }}'
group by user_name
order by AccessCount desc
# テーブル別ユーザランキング
select user_name as UserName, sum(count) as AccessCount from db_name.cloudtrail_daily_metrics
where date >= date '{{ start_date }}'
and date <= date '{{ end_date }}'
and table_name = '{{ table_name }}'
group by user_name
order by AccessCount desc
作成したクエリをもとに、ダッシュボードを作成します。
ダッシュボードからの気付き
作成したデータがちゃんと利用されているのかという疑問に対して、営業日に20〜30人程度から利用の記録があるので、全く利用されていないということはありませんでした。日毎のアクセス数が2,500万を超えているのは、システム的な集計のアクセスもカウントしていることもありますが、CloudTrail のログがファイルごとに発生するため、1回の集計で参照するファイルの数が多いと数値が高くなりやすいです。アクセス数の数値自体はあまり意味を持ちませんが、長期的な傾向を見ることで、データの利用が増えているかどうかをみる指標としては使えるものと思われます。
ユーザのランキングの中で、個人で複数のアカウントを使用しているようなケースも確認でき、このアカウントは開発においてしかたなく用意したもので、今後整理しなければならないといった潜在的な課題を浮き彫りにさせることができました。
さらに意味のあるダッシュボードにするために
現在は個人アカウントとシステムアカウントが内混ぜの状態で集計しているため、アクセス数の多くはシステムアカウントが占めており、個人アカウントのアクセスについての情報が埋もれてしまっています。そのため、アクセス数については個人アカウントとシステムアカウントを分離してカウントすることで、データ利用者からのアクセスについて確かな情報を得られる可能性があります。
re:dash によるデータ参照のように、社内からデータのアクセスは、特定の可視化ツールを使用しているケースもあり、可視化ツールからの利用は単一のアカウントからの参照とみなされている箇所があります。そのため、より詳細に可視化ツールから誰がアクセスしているのかというのを調べる(ドリルダウン)するためには、可視化ツール側のログか何かを調べる必要が出てきます。
ログに落ちたものをカウントしているため、参照がない(アクセスが0)という情報がこのダッシュボードからはわかりません。参照のないデータは残し続けると余計なコストとなるため削除するといったアクションが求められますが、このダッシュボードからでは「利用が少ない」を読み取ることまでしかできません。既存のテーブル一覧とつき合わすような形で「アクセスのないテーブル」として一覧化することが望ましいです。
おわりに
「この数値は売り上げに直接関係するのか？」と聞かれれば答えはノーです。そのため、こういった内部利用における数値の可視化というのは重要度が上がりにくいものと思います。データのマネジメントを行うようなチームの功績というのは、会社への貢献のしかたが間接的になりやすく、会社の掲げるKPIに直結することは稀でしょう。そのため、チームで追うべき数字というのも明確にしづらく、チームのモチベーションを維持することが困難という課題もよく聞きます。
今回私が担当させていただいた CloudTrail からの数値の可視化は、データのマネジメントを行う上でのひとつの指標にすぎませんが、これがあることで今後の施策における影響度合いの確認や、他のチームへの説明における具体的な評価値として用いることができます。データドリブンな会社として、よりデータの専門部隊がデータドリブンな状態として活動できるよう、客観的に評価できる仕組みを整えていければと考えております。客観的に評価できる仕組みを整えていければと考えております。
スタンバイのプロダクトや組織について詳しく知りたい方は、気軽にご相談ください。
