Проектирование схемы базы данных для событийной аналитики

Я пытаюсь выяснить, как лучше всего смоделировать схему для этой аналитической системы на основе событий, которую я пишу. Моя главная проблема заключается в том, чтобы писать это так, чтобы запросы были простыми и быстрыми. Я тоже буду использовать MySQL. Я рассмотрю некоторые из требований и представляю схему возможной (но я думаю, бедной) схемы.

Требования

Возможное моделирование

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

Моя первоначальная идея состояла в том, чтобы определить таблицу EVENTS с идентификатором, int count, timestamp, свойством (?) и внешним ключом EVENTTYPE. EVENTTYPE имеет идентификатор, имя и дополнительную информацию, относящуюся к родовому типу событий.

Например, событие "APP_LAUNCH" будет иметь запись в таблице СОБЫТИЙ с уникальным идентификатором, счетчиком, представляющим количество раз, когда произошло событие, метку времени (неуверенность в том, на что это делается печать), а также свойство или список свойств (например, "APP_VERSION", "COUNTRY" и т.д.) и внешний ключ для EVENTTYPE с именем "APP_LAUNCH".

Комментарии и вопросы

Я уверен, что это не очень хороший способ моделировать это по следующим причинам. Это затрудняет выполнение запросов timestamp ranged ( "Число APP_LAUNCHES между временем x и y" ). Таблица EVENTTYPE действительно не служит цели. Наконец, я не уверен, как бы я мог выполнять запросы для разных сегментов. Последний из тех, кого я больше всего беспокоюсь.

Я был бы признателен за любую помощь, помогающую правильно моделировать это или указывая на ресурсы, которые помогут.

Последний вопрос (который, вероятно, немой): Неправильно ли вставлять строку для каждого события? Например, скажем, моя клиентская библиотека выполняет следующий вызов моего API:

Как бы я действительно сохранил это в таблице (это, очевидно, тесно связано с дизайном схемы)? Неправильно ли просто вставлять строку для каждого из этих вызовов, из которых может быть значительная сумма? Моя реакция кишки состоит в том, что меня действительно интересуют главным образом общие агрегированные подсчеты. У меня недостаточно опыта работы с SQL, чтобы знать, как эти запросы выполняют, возможно, сотни тысяч этих записей. Будет ли сводная таблица или кеш в памяти помочь облегчить проблемы, когда я хочу, чтобы клиент фактически получал аналитику?

Я понимаю, что здесь много вопросов, но я бы очень признателен за любую помощь. Благодарю!

Ответы

Ответ 1

Я думаю, что большая часть ваших проблем не нужна. Принимая один из ваших вопросов за другим:

1) Самая большая проблема - это настраиваемые атрибуты, разные для каждого события. Для этого вам нужно использовать EAV (сущность-атрибут-значение). Важный вопрос: какие типы могут иметь эти атрибуты? Если более одного - например, string и integer, то это сложнее. Обычно существует два типа таких конструкций:

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

Итак, таблицы выглядели бы так:

Events             EventId int,  EventTypeId varchar,   TS timestamp
EventAttrValueInt  EventId int,  AttrName varchar,  Value int
EventAttrValueChar EventId int,  AttrName varchar,  Value varchar

2) Что вы подразумеваете под сегментацией? Запрос различных параметров события? В упомянутом выше проекте EAV вы можете сделать следующее:

select * 
from Events 
  join EventAttrValueInt  on Id = EventId and AttrName = 'APPVERSION' and Value > 4
  join EventAttrValueChar on Id = EventId and AttrName = 'APP_NAME' 
                                          and Value like "%Office%"
where EventTypeId = "APP_LAUNCH"

Это приведет к выбору всех событий типа APP_LAUNCH, где APPVERSION > 4, а APP_NAME содержит "Office".

3) Таблица EVENTTYPE может служить цели согласованности, т.е. вы могли:

table EVENTS (.... EVENTTYPE_ID varchar - foreign key to EVENTTYPE ...)
table EVENTTYPE (EVENTTYPE_ID varchar)

Или вы можете использовать идентификатор в качестве номера и иметь имя события в таблице EVENTTYPE - это экономит место и позволяет легко переименовать события, но вам нужно будет присоединиться к этой таблице в каждом запросе (в результате чего возникают более медленные запросы). Зависит от приоритета сохранения пространства для хранения и уменьшения времени запроса/простоты.

4) timestamp ranged запросы на самом деле очень просты в вашем дизайне:

select * 
from EVENTS
where EVENTTYPE_ID = "APP_LAUNCH" and TIMESTAMP > '2013-11-1'

5) "Неправильно ли вставлять строку для каждого события?"

Это полностью зависит от вас! Если вам нужна отметка времени и/или разные параметры каждого такого события, то, вероятно, у вас должна быть строка для каждого события. Если существует огромное количество событий одного и того же типа и параметров, вы, вероятно, можете сделать то, что делают большинство систем ведения журналов: агрегировать события, которые происходят в одной строке. Если у вас такое чувство кишки, то это, вероятно, способ пойти.

6) "У меня недостаточно опыта работы с SQL, чтобы знать, как эти запросы выполняют, возможно, сотни тысяч этих записей"

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

7). Будет ли сводная таблица или кеш в памяти помочь устранить проблемы, когда я хочу, чтобы клиент фактически получал аналитику? "

Конечно, это также решение, если запросы замедляются, и вам нужно быстро реагировать. Но тогда вы должны ввести некоторый механизм для периодического обновления кеша. Это чрезмерно сложнее; возможно, лучше рассмотреть агрегацию событий на входе, см. 5).