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

 В веб аналитике статистики считаются 3х разных размерностях:

  - в кликах
  - в сессиях
  - и в пользователях

 Статистика по пользователям очень важна, ведь именно посетители сайтов в конечном счете приносят бизнесу деньги.

В этом посте мы научимся считать такие статистики. Накапливать информацию о пользователях сложнее, чем о кликах или сессиях: 

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

Это последняя часть рассказа про расчет аналитики при помощи hive.

Если вы не читали предыдущие статьи — рекомедную вам посмотреть

• что такое hive и веб аналитика
• статистика по кликам
• статистика по сессиям
• работа с большими объемами данных

Расчет статистик по пользователям

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

Алгоритм пересчета статистик предлагается следующий:

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

Таким образом мы сможем день за днем обновлять информацию о старых пользователях и добавлять новых.

Если мы используем в своих рассуждениях "сегодня", "вчера" и "несколько дней", то разумно в таблицах по сессиям и по пользователям использовать разбиение по дням.
Про статистику по сессиям я уже писал отдельный пост.
Однако с разделами по дням таблицу сессий прийдется задавать явно:

CREATE TABLE sessions (
  user_id STRING,      --  идентификатор пользователя
  length STRING,       --  продолжительность сессии
  clicks_count STRING, --  число кликов, сделанных за сессию
  referrer STRING      --  реферрер, по которому началась сессия
)
PARTITIONED BY (day STRING)
LOCATION '/home/mezentsev/src/hive_web_analytics/sessions_table';

Таблица по пользователям может содержать только то, что мы добавим в нее из сессий. Поэтому наши пользователи будут описываться такой вот таблицей:

CREATE TABLE users (
  user_id STRING,      -- id пользователя
  time_on_site BIGINT, -- время, проведенное пользователем на сайте
  clicks_count  INT,   -- число сделанным пользователем кликов
  sessions_count  INT, -- число сессий на сайте
  referrer STRING      -- реферер, с которого пользователь попал на сайт
)
PARTITIONED BY (day STRING)
LOCATION '/home/mezentsev/src/hive_web_analytics/users_table';

При построении сессий, в отличие от предыдущей статьи, мы читать логи и писать сессии в разделы по дням:

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

INSERT OVERWRITE TABLE sessions PARTITION (day='2012-05-01') 
SELECT *
FROM (
  FROM (
      FROM (
         SELECT CONCAT (ip, user_agent) as user_id, 
                timestamp_to_unixtime (human_time) as unixtime, 
                referrer
         FROM partitioned_apache_logs WHERE day='2012-05-01'
      ) step1 
      SELECT * DISTRIBUTE BY user_id SORT BY user_id, unixtime 
  ) step2 
  SELECT TRANSFORM (user_id, unixtime, referrer)
  USING 'find_new_session.py' 
  AS user_id, length, session_clicks_count, session_referrer
) step3;

Запрос, который создает таблицу по пользователям, немного сложнее.
Разберем его по частям.

Склейка новых сессий со старыми пользователями

Склейка, как можно понять, делается при помощи команды join. Объединяются таблицы sessions за сегодня и users за вчера (в качестве дня для примера выбрано 1е мая. День вчера, соответственно - 30 апреля).

    FROM (
      SELECT * FROM sessions WHERE day='2012-05-01'
       -- вычитываемая таблица сессий
    ) session
    FULL OUTER JOIN (
       -- полный join - когда в результат добавляются поля, 
       -- которые есть либо в одной либо в другой таблице
      SELECT * from users WHERE day='2012-04-30'
       -- таблица пользователей
    ) user
    ON (session.user_id = user.user_id)

В hiveQL различается несколько типов join'ов

• просто джойн (JOIN)
• правый джоин (RIGHT OUTER JOIN)
• левый джойн (LEFT OUTER JOIN)
• полный джойн ( FULL OUTER JOIN)

Различие между ними - что они делают в случае, если запись есть в одной из таблиц и ее нету в другой.
Рассмотрим для примера 2 таблицы

По сессиям:	По пользователям:
user1  session1_info    user2  session2_info ...	user1  user1_info ...  user3  user3_info

 

 В таблице по сессиям есть информация про сесси пользователей 1 и 2, а в таблице по пользователям - про пользователей 1 и 3.
Тогда простой join выдаст только пользователей, которые есть в обоих таблицах:
    user1  session1_info user1_info

Левый join выдаст все записи, которые есть в первой таблице:

    user1  session1_info user1_info
    user2  session2_info NULL

Правый join выдаст все записи из таблицы 2:

    user1  session1_info user1_info
    user3  NULL          user3_info

А полный join создаст записи с user id, который есть хотя бы в одной из таблиц:

    user1  session1_info user1_info
    user2  session2_info NULL 
    user3  NULL          user3_info

Именно последний вариант нам и нужен.

Выбор всех полей и их сортировка

Из полученного джойна нам надо выбрать все поля и отсортировать их, чтобы записи о сессиях одного пользователя поступали в утилиту пересчета пользователей вместе.
Вот как мы это сделаем:

IF (session.user_id IS NOT NULL,
    session.user_id, 
    user.user_id) AS join_user_id,
user.time_on_site,
user.clicks_count AS user_clicks_count,
user.sessions_count,
user.referrer AS user_referrer,
session.length,
session.clicks_count AS session_clicks_count,
session.referrer AS session_referrer
FROM (...) session
FULL OUTER JOIN (...) user
ON (session.user_id = user.user_id)
DISTRIBUTE BY join_user_id
SORT BY join_user_id

Первый if учитывает, что поля user_id могуть быть равны "NULL" либо для сессий (если существующий пользователь не заходил сегодня на сайт) либо для пользователей (если пользователь впервые на сайте).
Переменным clicks_count и referrer приходится давать псевдонимы, чтобы избежать неоднозначностей в именах на следующих этапах обработки.

Преобразование полей в обновленные записи пользователя

Получить информацию о пользователях за новый день на основе сджойненых логов можно при помощи внешнего скрипта.
Для этих целей был написан update_users.py:

  FROM (...) joined_users_and_sessions
  SELECT TRANSFORM (*) USING 'update_user.py' AS
    -- применяем скрипт, который обновляет записи о пользователях
        user_id STRING,
        time_on_site BIGINT,
        clicks_count  INT,
        sessions_count  INT,
        referrer STRING
) updated_users

На выходе скрипт выдает все те поля, который пойдут в лог нового дня.

Запрос целиком

Целиком запрос выглядит вот так:

ADD FILE /home/mezentsev/src/hive_web_analytics/update_user.py;
FROM (
  FROM (
    SELECT
      IF (session.user_id IS NOT NULL, 
          session.user_id, 
          user.user_id) AS join_user_id,
      user.time_on_site,
      user.clicks_count AS user_clicks_count,
      user.sessions_count,
      user.referrer AS user_referrer,
      session.length,
      session.clicks_count AS session_clicks_count,
      session.referrer AS session_referrer
    FROM (
      SELECT * FROM sessions WHERE day='2012-05-01'
       -- вычитываемая таблица сессий
    ) session
    FULL OUTER JOIN (
       -- полный join - когда в результат добавляются поля, 
       -- которые есть либо в одной либо в другой таблице
      SELECT * FROM users WHERE day='2012-04-30'
       -- таблица пользователей
    ) user
    ON (session.user_id = user.user_id)
    DISTRIBUTE BY join_user_id
    SORT BY join_user_id
      -- сортируем и распределяем по редьюсерам поля
      -- так, чтобы все записи про одного пользователя
      -- шли последовательно
  ) joined_users_and_sessions

  SELECT TRANSFORM (*) USING 'update_user.py' AS
    -- применяем скрипт, который обновляет записи о пользователях
        user_id STRING,
        time_on_site BIGINT,
        clicks_count  INT,
        sessions_count  INT,
        referrer STRING
) updated_users
INSERT OVERWRITE TABLE users PARTITION (day='2012-05-01')
  -- сохраняем результат в таблицу за новый день
SELECT *;

Тадам! Статистика за 1е мая готова! Правда у нас получится, что все пользователи новые. Пользователей надо обсчитывать хотя бы несколько недель, чтобы информация про пользователей стала более-менее достоверной.
Считать статистики можно при помощи простогоа bash скрипта.
Недостатком построения информации по пользователям таким способом является зависимость информации пользователей за сегодня от информации, накопленной за вчера.  Пересчитывать таблицу пользователей по дням можно только последовательно, тогда как сессии по разным дням вполне спокойно пересчитываются параллельно.

Вот несколько примеров того, как можно считать статистики по получившимся таблицам пользователей:

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

SELECT referrer, COUNT(1) FROM users 
WHERE day='2012-05-31' GROUP BY referrer; 

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

SELECT sessions_count, COUNT(1) FROM users 
WHERE day='2012-05-31' GROUP BY sessions_count;

• источники трафика посетителей, пробывших на ресурсе больше часа

SELECT referrer,  COUNT (1) FROM users 
WHERE day='2012-05-31' AND time_on_site > 3600 GROUP BY referrer;

На самом деле число полей, которые мы добавляем в таблицу пользователей явно недостаточно для большинства важных статистик.
Но это просто учебный пример. В реальности про пользователей еще хорошо знать:

• когда они первый раз пришли на сайт
• дни, когда пользователь бывал на ресурсе
• регион, откуда пользователь пришел
• его браузер, операционка, разрешение экрана
• и так далее