Как хранить исторические данные
некоторые сотрудники и я вступили в дискуссию о лучшем способе хранения исторических данных. В настоящее время для некоторых систем я использую отдельную таблицу для хранения исторических данных, и я сохраняю исходную таблицу для текущей активной записи. Итак, допустим, у меня есть table FOO. В моей системе, все активные записи будут идти в FOO, и все исторические записи будут идти в FOO_Hist. Многие различные поля в FOO могут быть обновлены пользователем, поэтому я хочу вести точный учет всего обновленного. FOO_Hist содержит те же поля, что и FOO, за исключением автоматически увеличивающегося HIST_ID. Каждый раз, когда FOO обновляется, я выполняю инструкцию insert в FOO_Hist аналогично: insert into FOO_HIST select * from FOO where id = @id.
мой коллега говорит, что это плохой дизайн, потому что я не должен иметь точную копию таблицы по историческим причинам и должен просто вставить другую запись в активную таблицу с флагом, указывающим, что это для исторических целей.
есть ли стандарт для работы с историческими хранение данных? Мне кажется, что я не хочу загромождать свои активные записи всеми моими историческими записями в одной таблице, учитывая, что это может быть более миллиона записей (я думаю, в долгосрочной перспективе).
Как вы или ваша компания справиться с этим?
Я использую MS SQL Server 2008, но я хотел бы сохранить общий и произвольный ответ любой СУБД.
12 ответов:
поддержка исторических данных непосредственно в операционной системе сделает ваше приложение гораздо более сложным, чем это было бы в противном случае. Как правило, я бы не рекомендовал делать это, если у вас нет жесткого требования для управления историческими версиями записи в системе.
Если вы посмотрите внимательно, большинство требований к историческим данным относятся к одной из двух категорий:
ведение журнала аудита: это лучше сделать с таблицы аудита. Это довольно легко написать инструмент, который генерирует сценарии для создания таблиц журнала аудита и триггеров путем чтения метаданных из системного словаря данных. Этот тип инструмента может быть использован для модернизации регистрации аудита на большинстве систем. Вы также можете использовать эту подсистему для изменена ввода данных, если вы хотите реализовать хранилище данных (см. ниже).
статистическая отчетность: отчеты с историческими состояние, как-на позиции или аналитической отчетности со временем. Возможно, можно выполнить простые требования к исторической отчетности, запросив таблицы ведения журнала аудита, описанные выше. Если у вас есть более сложные требования, то может быть более экономичным реализовать витрину данных для отчетности, чем пытаться интегрировать историю непосредственно в операционную систему.
медленно меняющиеся размеры на сегодняшний день являются самым простым механизмом для отслеживания и запроса исторического состояния, и большая часть отслеживания истории может быть автоматизированный. Общие обработчики не так уж трудно написать. Как правило, исторические отчеты не должны использовать сиюминутные данные, поэтому механизм пакетного обновления обычно прекрасен. Это делает вашу основную архитектуру и архитектуру системы отчетности относительно простой.Если ваши требования попадают в одну из этих двух категорий, вы, вероятно, лучше не хранить исторические данные в вашей операционной системы. Разделение исторической функциональности на другую подсистема, вероятно, будет меньше усилий в целом и производить транзакционные и аудиторские/отчетные базы данных, которые работают намного лучше для их целевого назначения.
Я не думаю, что есть определенный стандартный способ сделать это, но я думал, что я бы бросил в возможный метод. Я работаю в Oracle и нашей собственной структуре веб-приложений, которая использует XML для хранения данных приложения.
мы используем так называемую мастер-детальную модель, которая в простейшем случае состоит из:
Master Table например под названием
Widgetsчасто просто содержащий идентификатор. Часто будет содержать данные, которые не будут меняться с течением времени / нет исторический.Подробная Таблица / История например под названием
Widget_Detailsсодержащий по крайней мере:
- ID-первичный ключ. Деталь / исторический идентификатор
- MASTER_ID - например, в этом случае называется "WIDGET_ID" , это FK для основной записи
- START_DATETIME-метка времени, указывающая на начало этой строки базы данных
- END_DATETIME-метка времени, указывающая конец этой строки базы данных
- STATUS_CONTROL - один столбец char указывает состояние строки. 'C' означает текущий, нулевой или 'A' будет историческим/архивным. Мы используем это только потому, что мы не можем индексировать на END_DATETIME значение NULL
- CREATED_BY_WUA_ID-сохраняет идентификатор учетной записи, которая вызвала создание строки
- XMLDATA-сохраняет фактические данные
таким образом, сущность начинается с наличия 1 строки в главном и 1 строки в деталях. Деталь, имеющая нулевую дату окончания и STATUS_CONTROL из 'C'. Когда происходит обновление, текущая строка обновляется, чтобы иметь END_DATETIME текущего времени и status_control имеет значение NULL (или 'A', если предпочтительно). В таблице сведений создается новая строка, все еще связанная с тем же мастером, с status_control 'C', идентификатором лица, производящего обновление, и новыми данными, хранящимися в столбце XMLDATA.
это основа нашей исторической модели. Логика создания / обновления обрабатывается в пакете Oracle PL / SQL, поэтому вы просто передайте функции текущий идентификатор, Ваш идентификатор пользователя и новые XML-данные и внутренне он выполняет все обновление / вставку строк, чтобы представить это в исторической модели. Время начала и окончания указывает, когда эта строка в таблице активна.
хранение дешево, мы обычно не удаляем данные и предпочитаем вести журнал аудита. Это позволяет нам видеть, как выглядели наши данные в любой момент времени. Индексируя status_control = ' C ' или используя представление, загромождение не является точно проблема. Очевидно, что ваши запросы должны учитывать, что вы всегда должны использовать текущую (NULL end_datetime и status_control = 'C') версию записи.
Я думаю, что вы подходите правильно. Историческая таблица должна быть копией основной таблицы без индексов, убедитесь, что у вас есть метка времени обновления в таблице.
Если вы попробуете другой подход достаточно скоро вы столкнетесь с проблемами:
- расходы на обслуживание
- больше флагов в selects
- замедление запросов
- рост таблиц, индексов
этот вопрос довольно старый, но люди все еще работают над этим вопросом. поэтому, если вы используете oracle, вас может заинтересовать oracle flashback:http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm
вы могли бы просто разделить столы нет?
"стратегии секционированных таблиц и индексов с использованием SQL Server 2008 Когда размер таблицы базы данных увеличивается до сотен гигабайт или более, может стать сложнее загружать новые данные, удалять старые данные и поддерживать индексы. Просто сам размер таблицы приводит к тому, что такие операции занимают гораздо больше времени. Даже данные, которые должны быть загружены или удалены, могут быть очень большими, что делает операции вставки и удаления в таблице непрактичными. Этот Программное обеспечение базы данных SQL Server 2008 обеспечивает секционирование таблиц, чтобы сделать такие операции более управляемым."
реальный вопрос заключается в том, нужно ли использовать исторические данные и активные данные вместе для отчетности? Если это так, сохраните их в одной таблице, разделите и создайте представление для активных записей для использования в активных запросах. Если вам нужно только посмотреть на них время от времени (чтобы исследовать проблемы leagal или некоторые из них), то поместите их в отдельную таблицу.
другой вариант-архивировать оперативные данные на основе [ежедневно|ежечасно|независимо]. Большинство движков баз данных поддержка извлечения данных в архив.
в основном, идея состоит в том, чтобы создать запланированное задание Windows или CRON, которое
- определяет текущие таблицы в рабочей базе данных
- выбирает все данные из каждой таблицы в файл CSV или XML
- сжимает данные в zip-файл, предпочтительно с меткой времени генерации в имени файла для более легкого архивирования.
многие ядра баз данных SQL поставляются с инструментом, который может быть использован для этой цели. Например, при использовании MySQL в Linux в задании CRON можно использовать следующую команду для планирования извлечения:
mysqldump --all-databases --xml --lock-tables=false -ppassword | gzip -c | cat > /media/bak/servername-$(date +%Y-%m-%d)-mysql.xml.gz
Я знаю, это старый пост, но просто хотел добавить несколько пунктов. Стандарт для таких проблем - это то, что лучше всего подходит для ситуации. понимание необходимости такого хранения и потенциального использования данных хронологии / аудита / отслеживания изменений очень важно.
аудит (в целях безопасности): используйте общую таблицу для всех ваших проверяемых таблиц. определите структуру для хранения имени столбца, поля перед значением и после значения.
Архив/Исторические: для случаев , таких как отслеживание предыдущего адреса, номера телефона и т. д. создание отдельной таблицы FOO_HIST лучше, если ваша активная схема таблицы транзакций существенно не изменится в будущем(если ваша таблица истории должна иметь ту же структуру). если вы ожидаете нормализации таблицы, изменения типа данных добавление / удаление столбцов, храните свои исторические данные в формате xml . определите таблицу со следующими столбцами (ID, дата, схема Версию, Данных XML). это позволит легко обрабатывать изменения схемы . но вы должны иметь дело с xml, и это может ввести уровень сложности для извлечения данных .
вы можете использовать функцию аудита сервера MSSQL. С версии SQL Server 2012, Вы найдете эту функцию во всех изданиях:
вы можете создать материализованные / индексированные представления в таблице. На основе вашего требования вы можете сделать полное или частичное обновление представлений. Пожалуйста, смотрите это, чтобы создать mview и журнал. Как создать материализованные представления в SQL Server?
просто хотел добавить опцию, которую я начал использовать, потому что я использую Azure SQL, и множественная таблица была слишком громоздкой для меня. Я добавил триггер insert/update/delete в свою таблицу, а затем преобразовал изменение before/after в json с помощью функции "для JSON AUTO".
SET @beforeJson = (SELECT * FROM DELETED FOR JSON AUTO) SET @afterJson = (SELECT * FROM INSERTED FOR JSON AUTO)Это возвращает представление JSON для записи до / после изменения. Затем я сохраняю эти значения в таблице истории с отметкой времени, когда произошло изменение (я также сохраняю идентификатор для текущей записи озабоченности). Используя процесс сериализации, я могу контролировать, как данные заполняются в случае изменений схемы.
я узнал об этом по этой ссылке здесь
изменение сбора данных: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017
он поддерживается в SQL Server 2008 R2, возможно, он поддерживался в SQL Server 2008.
Comments