HiveQL／SparkSQL で 時系列データにセッション情報を付与する


# 概要

アクセスログやクリックログを hadoop に流し込んだあと、データを セッション化 して分析したい という場合に、スクリプトなど使わずに **HiveQL（またはSparkSQL）でセッション情報を生成して利用する方法** について書きます

アクセスログのような時系列データから、PV (Page Views), UU (Unique Users) では捉えられない動きを理解する方法として、 **ユーザの一連の訪問行動** を **セッション** とする手法が有用です

# セッション 定義
GoogleAnalyticsでも使われている定義が汎用的です
[Analyticsでのセッション算出方法](https://support.google.com/analytics/answer/2731565?hl=ja)

要約すると、下記を満たす場合に新セッションを開始します
（前回アクセスが存在しなければ、無条件で新セッションを開始）

- **前回アクセスから30分以上経過している場合**
前の最後のアクセスから30分未満で次のアクセスがある場合、前のセッションが継続しているとみなされます

- **前回アクセスと日付が異なる場合**
毎日00:00でセッションは一度リセットされます
23:59に最初のアクセスがあり、次が00:00だったとすると、セッション数は 2 です

- **流入経路が変わった場合**
サイト内の回遊でなく、外部からの流入と判断できる場合は新しいセッションとして扱います


# セッション情報の生成／付与例

## スキーマ 定義

アクセスログのような時系列データ形式に **セッションID を格納するHiveテーブル** を定義し、ここにデータを流し込みます（例です）

```sql

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が生成できます

```sql

<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ごとに時系列に並べ、定義に従ってセッション情報を付与します


```sql


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分未満の連続した行動が、同一セッションに納まっていることが確認できます


```sql

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 に対してユニーク数をとれば ユニークな訪問数／セッション数になります

```sql

count(distinct session_id) sessions
```

```sql

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が小さいアクセス 
サイトに訪問した目的を把握するなどの用途で使えます

```sql

min(ts) over (PARTITION BY session_id)
```

## 離脱地点

離脱 =セッションの最後 =セッションごとに最もtimeが大きいアクセス
サイトのナビゲーションで問題がないかなど把握する用途で使えます

```
max(ts) over (PARTITION BY session_id)
```


## 滞在時間
滞在時間 =セッション中の次アクセスとのtime差
ユーザごとに最も時間を費やしたコンテンツを把握することができます
離脱間際のアクセスははずれ（30分近い数値）になり数値を乱すので、うまく除外する必要があるかもです

```sql
lead(ts) over ( PARTITION BY session_id ORDER BY ts ) - ts
```

## その他
特定のイベントやコンバージョンを発生させたセッションを詳しく見たり、ユーザの訪問行動からパターンを見つけたりする際に、セッション情報が活用できます



# まとめ

Hive／SparkSQL 等のSQLを利用して集計する際に、セッション情報を使う方法でした

今回の例では HiveQLでもSparkSQLでも文法は同じため、各環境に合わせて便利なほうを使ってください（一般的にはSparkSQLのほうが速そうです）

集計例で使用している `lead()` や `lag()` などの Window 関数は SQL としても一般的ですが、該当データ前後の情報が取れて便利なので、いろいろなケースで使えそうですね


# 参考文献

- https://support.google.com/analytics/answer/2731565?hl=ja
- https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics