HiveQL／SparkSQL で 時系列データにセッション情報を付与する
概要
アクセスログやクリックログを hadoop に流し込んだあと、データを セッション化 して分析したい という場合に、スクリプトなど使わずに HiveQL（またはSparkSQL）でセッション情報を生成して利用する方法 について書きます
アクセスログのような時系列データから、PV (Page Views), UU (Unique Users) では捉えられない動きを理解する方法として、 ユーザの一連の訪問行動 を セッション とする手法が有用です
セッション 定義
GoogleAnalyticsでも使われている定義が汎用的です
Analyticsでのセッション算出方法
要約すると、下記を満たす場合に新セッションを開始します
（前回アクセスが存在しなければ、無条件で新セッションを開始）
前回アクセスから30分以上経過している場合
前の最後のアクセスから30分未満で次のアクセスがある場合、前のセッションが継続しているとみなされます前回アクセスと日付が異なる場合
毎日00:00でセッションは一度リセットされます
23:59に最初のアクセスがあり、次が00:00だったとすると、セッション数は 2 です流入経路が変わった場合
サイト内の回遊でなく、外部からの流入と判断できる場合は新しいセッションとして扱います
セッション情報の生成／付与例
スキーマ 定義
アクセスログのような時系列データ形式に セッションID を格納するHiveテーブル を定義し、ここにデータを流し込みます（例です）
hive>
CREATE EXTERNAL TABLE example.session_data (
session_id string comment 'session ID',
user_id string comment 'unique-user ID',
event_pattern string comment 'view,event,, etc.',
time string comment 'access/action time hh:mm:ss',
url string comment 'url/concent-id',
size string comment 'content-size',
device string comment 'pc,mobile, etc.'
agent string comment 'user-agent',
response_time string comment 'msec'
)
PARTITIONED BY (
dt string COMMENT 'yyyy-mm-dd'
)
STORED AS
ORC
LOCATION
's3://sample-bucket/example/time_series'
;
セッションID 仕様
ユニークなユーザIDとアクセスデバイス、セッション開始時刻などを組み合わせれば一意性は担保されそうで、
そのままIDにするか、これをシードにハッシュ値生成するなどすれば、一意なIDが生成できます
<unique_user_id>_<device_id>_<session_start_datetime>
e.g.
99999999_999_20161231235959
※ この形なら split(session_id,'_')[0,1,2] をとることで 集計時に各情報を切り出せます
unique_user_id
セッション計測対象のユーザ識別子device_id
アクセスデバイスsession_start_datetime
該当セッションの開始日付時刻yyyymmddhhmmss形式
生成・格納 HiveQL
Viewログや Clickログなど、各種データを同一フォーマットに揃えたRawData example.raw_data を用意したうえで、ユーザID／デバイスIDごとに時系列に並べ、定義に従ってセッション情報を付与します
insert overwrite table example.session_data partition(dt='2017-01-01')
select
printf (
'%s_%s_%s',
user_id,
device,
from_unixtime(max(if(is_new_session = 1,ts,0)) over (partition by user_id order by ts),'yyyyMMddHHmmss')
) session_id,
user_id,
time,
event_pattern,
url,
device,
agent,
size,
response_time
from
(
select
case
when
( ts - lag( ts,1,0 ) over ( partition by user_id order by ts )) >= ( 30 * 60 ) -- * check 30 mins
then 1
when
referer is not null and not referer regexp '<example.self.domain>'
then 1
else 0
end is_new_session,
user_id,
event_pattern, -- 'view','click', etc.
ts, -- unix_timestamp bigint
time, -- HH:MM:SS
url,
device,
agent,
size,
response_time
from
example.raw_data
where
log_date = '2017-01-01'
) acc
;
生成結果例
生成したデータを SELECT してデータを確認します
ユーザ／デバイスごとに time 30分未満の連続した行動が、同一セッションに納まっていることが確認できます
hive>
select session_id,user_id,event_pattern,time,url,size,device,agent,response_time
from example.session_data where dt = '2017-01-01' order by session_id asc, time asc limit 10;
000027bbfae3_1_20170101014939 000027bbfae3 view 01:49:39 /index 31611 pc Mozilla/5.0 hoge agent 331632
000027bbfae3_1_20170101014939 000027bbfae3 view 01:49:39 /index 186 pc Mozilla/5.0 hoge agent 5509
000027bbfae3_1_20170101014939 000027bbfae3 click 01:49:58 /login 92 pc Mozilla/5.0 hoge agent 663830
000027bbfae3_1_20170101014939 000027bbfae3 view 01:50:02 /index 44034 pc Mozilla/5.0 hoge agent 1003123
000027bbfae3_1_20170101014939 000027bbfae3 click 01:50:02 /check 206 pc Mozilla/5.0 hoge agent 4289
000027bbfae3_1_20170101014939 000027bbfae3 view 01:50:16 /index 31489 pc Mozilla/5.0 hoge agent 199420
000027bbfae3_1_20170101014939 000027bbfae3 view 01:50:16 /index 186 pc Mozilla/5.0 hoge agent 4234
000027bbfae3_1_20170101014939 000027bbfae3 click 01:50:16 /logout 172 pc Mozilla/5.0 hoge agent 76000
000027bbfae3_2_20170101161441 000027bbfae3 view 16:14:41 /index 186 pc Mozilla/5.0 hoge agent 3966
000027bbfae3_2_20170101161441 000027bbfae3 view 16:14:42 /index 31629 pc Mozilla/5.0 hoge agent 280368
000027bbfae3_2_20170101161441 000027bbfae3 view 16:15:07 /media?content=30211 11507 pc Mozilla/5.0 hoge agent 188534
000027bbfae3_2_20170101161441 000027bbfae3 view 16:15:39 /media?content=29835 13239 pc Mozilla/5.0 hoge agent 265072
000027bbfae3_2_20170101161441 000027bbfae3 view 16:16:25 /communication?content=29835 12957 pc Mozilla/5.0 hoge agent 187089
000027bbfae3_2_20170101161441 000027bbfae3 view 16:18:19 /communication?content=29835 12769 pc Mozilla/5.0 hoge agent 220601
000027bbfae3_2_20170101161441 000027bbfae3 view 16:19:16 /communication?content=29835 12365 pc Mozilla/5.0 hoge agent 161997
集計例
訪問数／セッション数
PV/UU 集計と同じ形で、session_id に対してユニーク数をとれば ユニークな訪問数／セッション数になります
count(distinct session_id) sessions
SELECT
device,
count(distinct user_id) uu,
count(distinct session_id) sessions,
count(1) views
FROM
example.session_data
WHERE
dt = '2017-01-01' and
event_pattern = 'view' and
status regexp '^2'
GROUP BY
device
;
ランディング地点
ランディング =セッション開始地点 =セッションごとに最もtimeが小さいアクセス
サイトに訪問した目的を把握するなどの用途で使えます
min(ts) over (PARTITION BY session_id)
離脱地点
離脱 =セッションの最後 =セッションごとに最もtimeが大きいアクセス
サイトのナビゲーションで問題がないかなど把握する用途で使えます
max(ts) over (PARTITION BY session_id)
滞在時間
滞在時間 =セッション中の次アクセスとのtime差
ユーザごとに最も時間を費やしたコンテンツを把握することができます
離脱間際のアクセスははずれ（30分近い数値）になり数値を乱すので、うまく除外する必要があるかもです
lead(ts) over ( PARTITION BY session_id ORDER BY ts ) - ts
その他
特定のイベントやコンバージョンを発生させたセッションを詳しく見たり、ユーザの訪問行動からパターンを見つけたりする際に、セッション情報が活用できます
まとめ
Hive／SparkSQL 等のSQLを利用して集計する際に、セッション情報を使う方法でした
今回の例では HiveQLでもSparkSQLでも文法は同じため、各環境に合わせて便利なほうを使ってください（一般的にはSparkSQLのほうが速そうです）
集計例で使用している lead() や lag() などの Window 関数は SQL としても一般的ですが、該当データ前後の情報が取れて便利なので、いろいろなケースで使えそうですね
参考文献
Author And Source
この問題について(HiveQL／SparkSQL で 時系列データにセッション情報を付与する), 我々は、より多くの情報をここで見つけました https://qiita.com/yaiwase/items/c0b9724b8b83520bda06著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .