Данные в различных разрешениях

Взгляните на RRDTool. Это круглосуточная база данных. Вы определяете метрики, которые вы хотите захватить, но также можете определить разрешение, в котором вы храните ее.

Например, вы можете указать для последнего часа, вы храните информацию каждую секунду; за последние 24 часа - каждую минуту; за прошедшую неделю, каждый час и т.д.

Это широко используется для сбора статистики в таких системах, как Ganglia и Cacti.

Вы можете использовать историческую БД, как PI или историк. Это может быть больше денег, чем вы хотите потратить на этот проект, поэтому вы можете посмотреть одну из альтернатив бесплатных программ, таких как пакет баз данных в реальном времени .

Быстрые и грязные предложения.

[Принятие вас не может изменить базовые таблицы, что те таблицы уже записывают строки времени/даты, были добавлены и что у вас действительно есть разрешение создать объекты в DB].

1. Создают ПРЕДСТАВЛЕНИЕ (или несколько ПРЕДСТАВЛЕНИЙ), который имеет логическое поле на нем, которое генерирует уникальный 'номер слота', нарубив дату в таблицах. Что-то как:

представление CREATE VIEW AS ВЫБРАТЬ a, b, c, SUBSTR (date_field, x, y) slot_number ОТ ТАБЛИЦА;

пример выше упрощен, вы, вероятно, хотите добавить в большем количестве элементов от date+time.

[например, скажите, что дата является '01.01.2010 10:20:23,111', вы могли, возможно, генерировать ключ как '01.01.2010 10:00': таким образом, ваше решение составляет один час].

2. Дополнительно: использовать ПРЕДСТАВЛЕНИЕ для генерации реальной таблицы, как:

   CREATE TABLE frozen_data AS ВЫБРАТЬ * ИЗ ПРЕДСТАВЛЕНИЯ ГДЕ slot_number ='xxx;

, Почему беспокойство с шагом 1? Вы на самом деле не имеете к: просто использование ПРЕДСТАВЛЕНИЯ могло бы сделать вещи немного легче (с точки зрения SQL).

, Почему беспокойство с шагом 2? Просто способ (возможно) уменьшающей нагрузки на уже занятые таблицы: если можно динамично генерировать DDL затем, вы могли бы произвести отдельные таблицы с копиями 'слотов' данных: с которым можно затем работать.

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

Ежедневно необходимо было бы сбросить эти таблицы: если вы не можете генерировать таблицы в своем триггере на вашем DB. [вряд ли я думаю].

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

Подождите! Прежде чем прыгнуть на меня в ужасе, дайте мне закончить.

CouchDB собирает неструктурированные данные (JSON &c); цитируя технический обзор с вебсайта,

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

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

Исходя из ваших требований, я могу сказать, что вам нужно

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

Лично я бы сделал что-нибудь вроде:

• кэшировать собранные данные о клиенте(ах) и сохранять их в очередях на couchdb
• в зависимости от рабочей нагрузки, держать кластер db (опять же, couchdb был создан для этого) в синхронизированном виде между собой
• каждый интервал заставляет сервер генерировать представление о том, что вам нужно (i. e. каждый час и т.д.) в то время как другие(ие) продолжают собирать данные
• сохранять такие (теперь структурированные) представления в соответствующей базе данных для манипулирования и игры с инструментами SQL, или что угодно

Последняя точка - это просто пример. Я понятия не имею, что вы планируете с ней делать.

Когда речь заходит о разрезании и агрегации данных (по времени или чему-то еще), звездная схема (звезда Кимбалла) является довольно простым, но в то же время мощным решением. Предположим, что для каждого щелчка мы сохраняем время (до второго разрешения), информацию о пользователе, ID кнопки и местоположение пользователя. Чтобы облегчить нарезку и обработку, я начну с предварительно загруженных таблиц поиска свойств объектов, которые редко меняются - так называемых таблиц размеров в мире DW.



Таблица dimDate имеет по одной строке для каждого дня, с количеством атрибутов (полей), которые описывают конкретный день. Таблица может быть предварительно загружена на годы вперед, и должна обновляться один раз в день, если она содержит такие поля как DaysAgo, WeeksAgo, MonthsAgo, YearsAgo; в противном случае она может быть "загрузить и забыть". ДимДата dimDate позволяет легко нарезать атрибуты даты типа

WHERE [YEAR] = 2009 AND DayOfWeek = 'Sunday'

Для десяти лет данных таблица имеет только ~3650 строк.

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

WHERE Continent = 'South America'

После загрузки, она редко меняется.

Для каждой кнопки сайта в таблице димБаттон есть одна строка, поэтому в запросе может быть

WHERE PageURL = 'http://…/somepage.php'

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

Для записи щелчков кнопкой мыши я добавлю таблицу factClick.



В таблице factClick имеется одна строка для каждого щелчка кнопки от конкретного пользователя в определенный момент времени. Я использовал TimeStamp (второе разрешение), ButtonKey и UserKey в составном основном ключе, чтобы отфильтровывать щелчки быстрее, чем одна секунда от конкретного пользователя. Обратите внимание на поле Hour, оно содержит часовую часть TimeStamp, целое число в диапазоне 0-23, позволяющее легко нарезать в час, как

WHERE [HOUR] BETWEEN 7 AND 9

Итак, теперь мы должны рассмотреть:

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

Независимо от того, хранится ли информация в таблице только день или несколько лет - она должна быть разбита на разделы; ConcernedOfTunbridgeW объяснил разбиение на разделы в своём ответе, поэтому я пропущу это здесь.

Теперь, несколько примеров нарезки и обнуления по разным атрибутам (включая день и час)

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

/* To simplify queries flatten the model */ 
CREATE VIEW vClicks 
AS 
SELECT * 
FROM factClick AS f 
JOIN dimDate AS d ON d.DateKey = f.DateKey 
JOIN dimButton AS b ON b.ButtonKey = f.ButtonKey 
JOIN dimUser AS u ON u.UserKey = f.UserKey 
JOIN dimGeography AS g ON g.GeographyKey = f.GeographyKey

Пример запроса

/* 
Count number of times specific users clicked any button  
today between 7 and 9 AM (7:00 - 9:59)
*/ 
SELECT  [Email] 
       ,COUNT(*) AS [Counter] 
FROM    vClicks 
WHERE   [DaysAgo] = 0 
        AND [Hour] BETWEEN 7 AND 9 
        AND [Email] IN ('dude45@somemail.com', 'bob46@bobmail.com') 
GROUP BY [Email] 
ORDER BY [Email]

Предположим, что меня интересуют данные для User = ALL. Диммер dimUser представляет собой большую таблицу, поэтому я сделаю представление без него, чтобы ускорить запросы.

/* 
Because dimUser can be large table it is good 
to have a view without it, to speed-up queries 
when user info is not required 
*/ 
CREATE VIEW vClicksNoUsr 
AS 
SELECT * 
FROM factClick AS f 
JOIN dimDate AS d ON d.DateKey = f.DateKey 
JOIN dimButton AS b ON b.ButtonKey = f.ButtonKey 
JOIN dimGeography AS g ON g.GeographyKey = f.GeographyKey

Пример запроса

/* 
Count number of times a button was clicked on a specific page 
today and yesterday, for each hour. 
*/ 
SELECT  [FullDate] 
       ,[Hour] 
       ,COUNT(*) AS [Counter] 
FROM    vClicksNoUsr 
WHERE   [DaysAgo] IN ( 0, 1 ) 
        AND PageURL = 'http://...MyPage' 
GROUP BY [FullDate], [Hour] 
ORDER BY [FullDate] DESC, [Hour] DESC

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

Таблица factClickAgg может быть загружена почасово или даже в конце каждого дня - в зависимости от требований к отчетности и аналитике. Например, допустим, что таблица загружается в конце каждого дня (после полуночи), я могу использовать что-то вроде:

/* At the end of each day (after midnight) aggregate data. */ 
INSERT  INTO factClickAgg 
        SELECT  DateKey 
               ,[Hour] 
               ,ButtonKey 
               ,GeographyKey 
               ,COUNT(*) AS [ClickCount] 
        FROM    vClicksNoUsr 
        WHERE   [DaysAgo] = 1 
        GROUP BY DateKey 
               ,[Hour] 
               ,ButtonKey 
               ,GeographyKey

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

/* To simplify queries for aggregated data */ 
CREATE VIEW vClicksAggregate 
AS 
SELECT * 
FROM factClickAgg AS f 
JOIN dimDate AS d ON d.DateKey = f.DateKey 
JOIN dimButton AS b ON b.ButtonKey = f.ButtonKey 
JOIN dimGeography AS g ON g.GeographyKey = f.GeographyKey

Теперь я могу запрашивать агрегированные данные, например, по дням :

/* 
Number of times a specific buttons was clicked 
in year 2009, by day 
*/ 
SELECT  FullDate 
       ,SUM(ClickCount) AS [Counter] 
FROM    vClicksAggregate 
WHERE   ButtonName = 'MyBtn_1' 
        AND [Year] = 2009 
GROUP BY FullDate 
ORDER BY FullDate

Или с помощью еще нескольких опций

/* 
Number of times specific buttons were clicked 
in year 2008, on Saturdays, between 9:00 and 11:59 AM 
by users from Africa 
*/ 

SELECT  SUM(ClickCount) AS [Counter] 
FROM    vClicksAggregate 
WHERE   [Year] = 2008 
        AND [DayOfWeek] = 'Saturday' 
        AND [Hour] BETWEEN 9 AND 11 
        AND Continent = 'Africa' 
        AND ButtonName IN ( 'MyBtn_1', 'MyBtn_2', 'MyBtn_3' )