Как использовать hive в веб аналитике. Статистика сессий

Это третий пост про использование hive в веб аналитике. В предыдущих
постах я описал сам hive и задачу веб-аналитики, а так же как считать стастики по логам apache.
Здесь я разберу расчет статистик по сессиям, который требует особого подхода.


Сессия или визит — это событие, что пользователь зашел, походил по сайту, затем ушел. Статистика по сессиям — одна из основных статистик в веб аналитике, вместе со статистикой по пользователям и кликам. Так, например, google analytics считает статистики по пользователям и по сессиям, полностью игнорируя статистики по кликам.
 Формально сессия определяется как "последовательность кликов одного пользователя на сайте, время между которыми не превышает 30 минут".
По сессиям можно посчитать, например:

• сколько сессий было сделано пользователем за день
  
• из под каких операционных систем сессий было больше
  
• средняя длительность сессии
  
• среднее число кликов за сессию
  
• среднее число кликов за сессию в зависимости от браузера
  
• распределение источников, переходя с которых пользователи начинали свои сессии
  

и так далее...
Из апачового лога извлечь информацию о сессиях непосредственно нельзя.
Для того чтобы научиться находить сессии нам потребуется

• перевести все времена из человеческого формата в unixtime (время в секундах), чтобы можно было находить время между кликами пользователя
  
• научиться сортировать логи сначала по id пользователя, потом по времени (тогда все сессии будут идти подряд, друг за другом)
  
• потом обработать их, находя начала и концы сессий как смену пользователя или как большой интервал между кликами
  

Как перевести время в unixtime

Вообще в hive есть специальная функция, которая разбирает дату и время: unix_timestamp. Но она к сожалению не смогла разобрать апачовую дату. Функция спотыкалась на разборе месяца: дело в том, что по умолчанию она использовала текущую локаль, понимая под месяцев «янв», «фев», «мар» и так далее.
Для решения проблемы пришлось написать специальную UDF функцию, которая разбирает дату, принудительно используя американскую локаль.
Выбор необходимых полей лога для статистики по сессиям будет выглядеть так:

ADD JAR /home/.../timestamp_to_unixtime.jar;
CREATE TEMPORARY FUNCTION timestamp_to_unixtime 
  AS 'TimestampToUnixtime';
SELECT CONCAT (ip, user_agent) AS user_id, 
  timestamp_to_unixtime (human_time) AS unixtime 
  FROM apache_logs

(Первые две команды добавляют новую временную функцию, последняя вычитывает и создает новые поля из апачовых логов)

Отсортировать пользователей по id и времени

Это делается при помощи конструкций hiveQL.

SELECT * SORT BY user_id, unixtime 

Но надо учитывать, что в распределенном режиме обработки данных разные строки лога могут попадать на разные редьюсеры и обрабатываться отдельно.
Если мы хотим, чтобы все записи про одного пользователя оказались на одном редьюсере, следует указать, что распределение сток при сортировки между редьюсерами будет происходить по id пользователя:

SELECT * DISTRIBUTE BY user_id SORT BY user_id, unixtime 

Найти сессии

Hive позволяет обрабатывать получившиеся стоки лога, используя любые утилиты командной строки. При этом

• Поля, которые передаются утилите на вход, поступают на ее стандартный вход, разделенные табуляциями.
• Каждая строка, которую утилита выписывает в стандартный вывод, тоже считается строкой данных, поля в которой разделены по табу.

Для находждения сессий я написал питоновский скрипт find_new_session.py. На вход он принимает строки «id пользователя, время клика, реферер клика», поля в которых разделены табуляциями.
Скрипт считает что они отсортированы сначала по id, потом по времени и из этого определяет начала и концы сессий.
В качестве результата скрипт выписывает наружу, id пользователя, длительность сессии, число кликов за сессию и реферрер, также разделенные табами.
Чтобы заюзать скрипт в запросе, следует сначала добавить его в распределенный кеш

ADD FILE /home/.../find_new_session.py;

А затем вызывать, указав входные и выходные поля:

SELECT TRANSFORM (user_id, unixtime, referrer)
USING 'find_new_session.py' 
AS user_id, session_length, session_clicks_count, session_referrer

Таблица сессий

Информацию о каждой из произошедших сессий лучше положить в отдельную таблицу. Тогда, один раз найдя информацию про них, мы сможем сколько угодно раз считать интересующие на статистики.
Объединить три описанных выше шага в один мы можем  при помощи поддержки вложенных запросов в hive.
Полный текст запроса, создающего таблицу сессий будет выглядеть так:

ADD JAR /home/.../timestamp_to_unixtime.jar;
CREATE TEMPORARY FUNCTION timestamp_to_unixtime 
  AS 'TimestampToUnixtime';
ADD FILE /home/.../find_new_session.py;

CREATE TABLE sessions LOCATION '/home/.../sessions_table' AS SELECT *
  -- это конструкция "создать таблицу на основании селекта"
  FROM (  -- hiveQL поддерживает возможность делать вложенные слелекты 
    FROM (
      FROM (
        SELECT  
          CONCAT (ip, user_agent) as user_id, 
          timestamp_to_unixtime (human_time) as unixtime, 
          referrer
        FROM apache_logs
        -- в результате этого селекта мы получили 3 поля, 
        -- с которыми будем работать
      ) step1 
      SELECT * DISTRIBUTE BY user_id SORT BY user_id, unixtime 
      -- этот селект упорядочивает всех пользователей (sort) по времени 
      -- и гарантирует, что на пользователи с одним user_id окажутся 
      -- на одном редьюсере (distribute)
    ) step2 
    SELECT TRANSFORM (user_id, unixtime, referrer)
    USING 'find_new_session.py' 
    AS user_id, session_length, session_clicks_count, session_referrer
    -- здесь мы передаем 3 поля скрипту find_new_session 
    -- (он видит их как строку, разделенную табами)
    -- а все что он выдает на выход - интерпретируем, как 
    -- id пользователя, длину сессии, число кликов и реферрер сессии
  ) step3;

Разные статистики по сессиям

Построив таблицу сессий, мы легко можем узнать

• количество сессий, произошедших за день
  

SELECT COUNT (1) FROM sessions GROUP BY session_referrer;

• подсчет распределения сессий по источникам трафика
  

FROM (
  SELECT session_referrer, COUNT (1) AS amount 
    FROM sessions 
    GROUP BY session_referrer
) step1
SELECT session_referrer, amount ORDER BY amount DESC;

• распределение числа сессий на пользователя
  

FROM (
  SELECT user_id, 
    COUNT (1) AS sessions_count FROM sessions GROUP BY user_id
) step1
SELECT COUNT (1), sessions_count GROUP BY sessions_count;

и любые другие важные статистики сессий