アクセスログからセッションを作成する方法 ref: http://qiita.com/itochu0523/items/80fcde77b4b1a8867baf

file0.sql

--hive
SELECT 
  TD_SESSIONIZE(time, 86400, td_ip) as session_id
, time
, td_ip
, td_path
, td_client_id
, td_title
, td_browser
, td_color
, td_os_version
, td_browser_version
, td_referrer 
, td_screen 
, td_os
, td_host 
, td_url
, td_language
FROM 
(
  SELECT time, td_ip, td_path, td_client_id, td_title
        ,td_browser, td_color, td_os_version
        ,td_browser_version, td_referrer, td_screen 
        ,td_os, td_host, td_url, td_language
  from access
  distribute by td_ip
  sort by td_ip,time
) a 

file1.sql

SELECT
   b.session_id as session_id
 , b.td_client_id as td_client_id
 , TD_TIME_FORMAT(b.session_start_time, 'yyyy-MM-dd HH:mm:ss', 'JST') as session_start_time
 , TD_TIME_FORMAT(b.session_end_time, 'yyyy-MM-dd HH:mm:ss', 'JST') as session_end_time
 , (b.session_end_time - b.session_start_time) as session_stay_time
 , b.session_cnt as session_cnt
FROM
(
SELECT
   a.session_id as session_id
 , a.td_client_id as td_client_id
 , min(a.time) as session_start_time
 , max(a.time) as session_end_time
 , count(1) as session_cnt
FROM
(
  SELECT 
    TD_SESSIONIZE(time, 86400, td_ip) as session_id
  , time
  , td_ip
  , td_path
  , td_client_id
  FROM (
    SELECT time, td_ip, td_path, td_client_id
    from access
    distribute by td_ip
    sort by td_ip,time
  ) t
) a
GROUP BY a.session_id,a.td_client_id
ORDER BY a.session_id
) b