Jak przechowywać dane historyczne


162

Razem ze współpracownikami wdaliśmy się w debatę na temat najlepszego sposobu przechowywania danych historycznych. Obecnie w niektórych systemach używam oddzielnej tabeli do przechowywania danych historycznych i zachowuję oryginalną tabelę dla bieżącego, aktywnego rekordu. Powiedzmy, że mam FOO tabeli. W moim systemie wszystkie aktywne rekordy trafią do FOO, a wszystkie historyczne rekordy do FOO_Hist. Użytkownik może aktualizować wiele różnych pól w FOO, więc chcę mieć dokładne konto wszystkich aktualizowanych. FOO_Hist przechowuje dokładnie te same pola, co FOO, z wyjątkiem automatycznego zwiększania wartości HIST_ID. Za każdym razem FOO jest aktualizowana, ja wykonać instrukcji INSERT INTO FOO_Hist podobny do: insert into FOO_HIST select * from FOO where id = @id.

Mój współpracownik mówi, że to zły projekt, ponieważ nie powinienem mieć dokładnej kopii tabeli z powodów historycznych i powinienem po prostu wstawić kolejny rekord do aktywnej tabeli z flagą wskazującą, że jest to do celów historycznych.

Czy istnieje standard postępowania z przechowywaniem danych historycznych? Wydaje mi się, że nie chcę zaśmiecać moich aktywnych rekordów wszystkimi moimi historycznymi rekordami w tej samej tabeli, biorąc pod uwagę, że może to być znacznie ponad milion rekordów (myślę długoterminowo).

Jak Ty lub Twoja firma radzicie sobie z tym?

Używam MS SQL Server 2008, ale chciałbym, aby odpowiedź była ogólna i arbitralna dla każdego DBMS.

Odpowiedzi:


80

Obsługa danych historycznych bezpośrednio w systemie operacyjnym sprawi, że Twoja aplikacja będzie znacznie bardziej złożona niż byłaby w innym przypadku. Generalnie nie polecałbym tego robić, chyba że masz poważne wymagania, aby manipulować historycznymi wersjami rekordu w systemie.

Jeśli przyjrzysz się uważnie, większość wymagań dotyczących danych historycznych należy do jednej z dwóch kategorii:

  • Rejestrowanie audytu: Lepiej jest to zrobić za pomocą tabel audytu. Dość łatwo jest napisać narzędzie, które generuje skrypty do tworzenia tabel dziennika inspekcji i wyzwalaczy, odczytując metadane z systemowego słownika danych. Tego typu narzędzia można używać do modernizacji logowania audytowego w większości systemów. Możesz również użyć tego podsystemu do przechwytywania zmienionych danych, jeśli chcesz wdrożyć hurtownię danych (patrz poniżej).

  • Raportowanie historyczne: raportowanie stanu historycznego, pozycje „na” lub raportowanie analityczne w czasie. Spełnienie prostych wymagań dotyczących raportowania historycznego może być możliwe dzięki zapytaniu o tabele rejestrowania audytu, które opisano powyżej. Jeśli masz bardziej złożone wymagania, bardziej ekonomiczne może być wdrożenie zbiorczej bazy danych na potrzeby raportowania, niż próba zintegrowania historii bezpośrednio z systemem operacyjnym.

    Wolno zmieniające się wymiary są zdecydowanie najprostszym mechanizmem śledzenia i sprawdzania stanu historycznego, a większość śledzenia historii można zautomatyzować. Ogólne programy obsługi nie są takie trudne do napisania. Generalnie raportowanie historyczne nie musi korzystać z aktualnych danych, więc mechanizm odświeżania wsadowego jest zwykle w porządku. Dzięki temu architektura rdzenia i systemu raportowania jest stosunkowo prosta.

Jeśli Twoje wymagania należą do jednej z tych dwóch kategorii, prawdopodobnie lepiej nie przechowywać danych historycznych w systemie operacyjnym. Rozdzielenie historycznej funkcjonalności na inny podsystem prawdopodobnie będzie ogólnie mniejszym wysiłkiem i stworzy bazy danych transakcyjnych i audytowych / raportowych, które działają znacznie lepiej zgodnie z ich przeznaczeniem.


Myślę, że rozumiem, co mówisz. Więc to, co zrobiłem z moją tabelą FOO_Hist, to naprawdę utworzenie tabeli audytu. Zamiast używać wyzwalacza do wstawienia do tabeli audytu podczas aktualizacji, po prostu uruchomiłem instrukcję w programie. Czy to jest poprawne?
Aaron

6
Dość dużo. Jednak tego rodzaju rejestrowanie audytu lepiej jest wykonywać z wyzwalaczami; wyzwalacze zapewniają, że wszelkie zmiany (w tym ręczne poprawki danych) zostaną zapisane w dziennikach kontroli. Jeśli masz więcej niż 10-20 tabel do audytu, prawdopodobnie szybciej jest zbudować narzędzie generatora wyzwalaczy. Jeśli ruch na dysku związany z dziennikami inspekcji stanowi problem, można umieścić tabele dziennika inspekcji na osobnym zestawie dysków.
ConcernedOfTunbridgeWells

Tak, w 100% się z tym zgadzam. Dziękuję Ci.
Aaron

40

Nie sądzę, aby istniał jakiś szczególny standardowy sposób, ale pomyślałem, że dorzucę możliwą metodę. Pracuję w Oracle i naszej wewnętrznej strukturze aplikacji internetowych, która wykorzystuje XML do przechowywania danych aplikacji.

Używamy czegoś, co nazywa się modelem Master - Detail, który w najprostszy sposób składa się z:

Master TableNa przykład nazywana Widgetsczęsto po prostu zawierającą identyfikator. Często zawiera dane, które nie zmieniają się w czasie / nie są historyczne.

Na przykład tabela szczegółów / historii o nazwieWidget_Details zawierająca co najmniej:

  • ID - klucz podstawowy. Szczegóły / historyczny identyfikator
  • MASTER_ID - na przykład w tym przypadku o nazwie 'WIDGET_ID', jest to FK do rekordu głównego
  • START_DATETIME - znacznik czasu wskazujący początek tego wiersza bazy danych
  • END_DATETIME - znacznik czasu wskazujący koniec tego wiersza bazy danych
  • STATUS_CONTROL - kolumna jednoznakowa wskazuje stan wiersza. „C” wskazuje na aktualny stan, NULL lub „A” byłby historyczny / zarchiwizowany. Używamy tego tylko dlatego, że nie możemy indeksować w dniu END_DATETIME z wartością NULL
  • CREATED_BY_WUA_ID - przechowuje identyfikator konta, które spowodowało utworzenie wiersza
  • XMLDATA - przechowuje aktualne dane

Zasadniczo więc encja zaczyna się od jednego wiersza we wzorcu i jednego wiersza w szczegółach. Szczegół mający NULL datę końcową i STATUS_CONTROL „C”. Kiedy następuje aktualizacja, bieżący wiersz jest aktualizowany tak, aby miał END_DATETIME aktualnego czasu, a status_control jest ustawiony na NULL (lub „A”, jeśli jest to preferowane). W tabeli szczegółów tworzony jest nowy wiersz, wciąż powiązany z tym samym wzorcem, ze status_control „C”, identyfikatorem osoby dokonującej aktualizacji i nowymi danymi przechowywanymi w kolumnie XMLDATA.

To jest podstawa naszego modelu historycznego. Logika tworzenia / aktualizacji jest obsługiwana w pakiecie Oracle PL / SQL, więc po prostu przekazujesz funkcji bieżący identyfikator, swój identyfikator użytkownika i nowe dane XML, a wewnętrznie wykonuje wszystkie aktualizacje / wstawienia wierszy, aby przedstawić to w modelu historycznym . Czasy rozpoczęcia i zakończenia wskazują, kiedy ten wiersz w tabeli jest aktywny dla.

Przechowywanie jest tanie, zazwyczaj nie USUWAMY danych i wolimy zachować ścieżkę audytu. Dzięki temu możemy zobaczyć, jak w danym momencie wyglądały nasze dane. Indeksując status_control = 'C' lub używając widoku, bałagan nie jest dokładnie problemem. Oczywiście twoje zapytania muszą być brane pod uwagę, zawsze powinieneś używać aktualnej (NULL end_datetime i status_control = 'C') wersji rekordu.


Cześć Chris, jeśli to zrobisz, identyfikator (klucz podstawowy) musi zostać zmieniony, prawda? A co z relacją z inną tabelą, jeśli jest używana przez inną?
projo

@projo ID na twoim stole głównym to PK i koncepcyjnie "PK" dla każdej koncepcji, z którą masz do czynienia. Identyfikator w tabeli szczegółów to PK do identyfikacji historycznej wersji dla wzorca (jest to kolejna kolumna szczegółu). Tworząc relacje, często odwoływałeś się do prawdziwego PK swojej koncepcji (tj. ID w tabeli głównej lub kolumny MASTER_ID w twoim szczególe) i używasz STATUS_CONTROL = 'C', aby upewnić się, że otrzymujesz aktualną wersję. Alternatywnie możesz odwołać się do identyfikatora szczegółu, aby odnieść się do określonego punktu w czasie.
Chris Cameron-Mills

+1 Wdrożyłem ten wzór z wielkim sukcesem w kilku dużych projektach.
Logika trzech wartości

Używamy tego samego podejścia, ale teraz zastanawiam się, czy lepiej przechowywać tylko START_DATETIME i nie przechowywaćEND_DATETIME
bat_ventzi

Kilka odmian mojego doświadczenia. Jeśli Twoja encja jest „zakończona”, tj. Zarchiwizowana lub usunięta, w efekcie możesz nie mieć żadnych szczegółowych rekordów z kontrolą statusu „C”, tj. Brak bieżącego wiersza, chociaż nie wiesz, kiedy to się stało. Alternatywnie możesz ustawić end_datetime w ostatnim wierszu, a obecność „zakończonego” wiersza „C” może wskazywać, że jednostka została teraz usunięta / zarchiwizowana. Na koniec możesz to przedstawić w innej kolumnie STATUS, którą prawdopodobnie już masz.
Chris Cameron-Mills

15

Myślę, że podejście jest prawidłowe. Tabela historyczna powinna być kopią tabeli głównej bez indeksów, upewnij się, że masz również znacznik czasu aktualizacji w tabeli.

Jeśli wkrótce spróbujesz innego podejścia, napotkasz problemy:

  • koszty utrzymania
  • więcej flag w zaznaczeniach
  • spowolnienie zapytań
  • wzrost tabel, indeksów

7

W SQL Server 2016 i nowszych wersjach dostępna jest nowa funkcja zwana tabelami czasowymi, która ma na celu rozwiązanie tego problemu przy minimalnym wysiłku ze strony programisty . Koncepcja tabeli temporalnej jest podobna do zmiany przechwytywania danych (CDC), z tą różnicą, że tabela czasowa wyodrębniła większość rzeczy, które trzeba było wykonać ręcznie, jeśli korzystałeś z CDC.


2

Zmień przechwytywanie danych: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017

Jest obsługiwany w SQL Server 2008 R2, może być obsługiwany w SQL Server 2008.


Należy pamiętać, że zmiana przechwytywania danych jest przeznaczona tylko do krótkotrwałego przechowywania historii danych. Widzieć tabele czasowe programu SQL Server a przechwytywanie danych zmian vs śledzenie zmian .
Edward Brey,


1

Chciałem tylko dodać opcję, której zacząłem używać, ponieważ używam Azure SQL, a funkcja wielu tabel była dla mnie zbyt kłopotliwa. Dodałem wyzwalacz wstawiania / aktualizowania / usuwania w mojej tabeli, a następnie przekonwertowałem zmianę przed / po na json przy użyciu funkcji „FOR JSON AUTO”.

 SET @beforeJson = (SELECT * FROM DELETED FOR JSON AUTO)
SET @afterJson = (SELECT * FROM INSERTED FOR JSON AUTO)

To zwraca reprezentację JSON dla rekordu przed / po zmianie. Następnie przechowuję te wartości w tabeli historii z sygnaturą czasową, kiedy nastąpiła zmiana (przechowuję również identyfikator bieżącego rekordu, którego dotyczy problem). Dzięki procesowi serializacji mogę kontrolować sposób wypełniania danych w przypadku zmian w schemacie.

Dowiedziałem się o tym z tego linku tutaj


0

Możesz po prostu podzielić tabele, nie?

„Strategie tabel i indeksów partycjonowanych przy użyciu programu SQL Server 2008 Gdy rozmiar tabeli w bazie danych rośnie do setek gigabajtów lub więcej, ładowanie nowych danych, usuwanie starych i utrzymywanie indeksów może być trudniejsze. Tylko sam rozmiar tabeli powoduje, że takie operacje trwają znacznie dłużej. Nawet dane, które muszą zostać załadowane lub usunięte, mogą być bardzo duże, przez co operacje INSERT i DELETE na tabeli są niepraktyczne. Oprogramowanie bazy danych Microsoft SQL Server 2008 zapewnia partycjonowanie tabel, aby ułatwić zarządzanie takimi operacjami ”.


Tak, mogę podzielić tabele, ale czy jest to standard w przypadku danych historycznych? Czy dane historyczne powinny znajdować się w tej samej tabeli co dane aktywne? To są pytania, które chciałem omówić. To również nie jest arbitralne, ponieważ dotyczy SQL Server 2008.
Aaron

0

Prawdziwe pytanie brzmi: czy musisz używać danych historycznych i aktywnych danych razem do raportowania? Jeśli tak, trzymaj je w jednej tabeli, podziel na partycje i utwórz widok dla aktywnych rekordów do wykorzystania w aktywnych zapytaniach. Jeśli potrzebujesz spojrzeć na nie tylko od czasu do czasu (aby zbadać kwestie leagalne lub inne), umieść je w osobnej tabeli.


2
Czy trudniej jest JOINdwie tabele w kilku raportach historycznych, czy też trudniej jest zmodyfikować każdą wstawienie / aktualizację / usunięcie pojedynczej tabeli, aby mieć świadomość problemów historycznych? W rzeczywistości dziennik audytu zawierałby nawet aktualne dane w tabeli historii, więc bieżąca tabela nie powinna być nawet potrzebna w raporcie.

0

Inną opcją jest archiwizacja danych operacyjnych [codziennie | co godzinę | cokolwiek]. Większość silników baz danych obsługuje wyodrębnianie danych do archiwum .

Zasadniczo chodzi o to, aby utworzyć zaplanowane zadanie systemu Windows lub CRON

  1. określa bieżące tabele w operacyjnej bazie danych
  2. wybiera wszystkie dane z każdej tabeli do pliku CSV lub XML
  3. kompresuje wyeksportowane dane do pliku ZIP, najlepiej z sygnaturą czasową generacji w nazwie pliku dla łatwiejszej archiwizacji.

Wiele silników baz danych SQL jest dostarczanych z narzędziem, którego można użyć w tym celu. Na przykład podczas korzystania z MySQL w systemie Linux w zadaniu CRON można użyć następującego polecenia, aby zaplanować wyodrębnianie:

mysqldump --all-databases --xml --lock-tables=false -ppassword | gzip -c | cat > /media/bak/servername-$(date +%Y-%m-%d)-mysql.xml.gz

2
Nie jest to w ogóle odpowiednie dla danych historycznych, ponieważ jeśli ktoś zmieni wartość i zmieni ją z powrotem w cyklu archiwizacji, aktualizacje zostaną utracone. Nie ma również łatwego sposobu spojrzenia na zmiany jednej encji w czasie lub częściowego przywrócenia encji.
Sgoettschkes

0

Znam ten stary post, ale chciałem tylko dodać kilka punktów. Standardem takich problemów jest to, co działa najlepiej w danej sytuacji. zrozumienie potrzeby takiego przechowywania i potencjalnego wykorzystania danych historycznych / audytowych / śledzenia zmian jest bardzo ważne.

Audyt (cel bezpieczeństwa) : Użyj wspólnej tabeli dla wszystkich tabel podlegających audytowi. zdefiniuj strukturę do przechowywania nazwy kolumny, przed i po polach wartości.

Archive / Historical : w przypadkach takich jak śledzenie poprzedniego adresu, numeru telefonu itp. Utworzenie oddzielnej tabeli FOO_HIST jest lepsze, jeśli schemat tabeli aktywnych transakcji nie zmienia się znacząco w przyszłości (jeśli tabela historii musi mieć taką samą strukturę). jeśli przewidujesz normalizację tabel, dodanie / usunięcie kolumn w celu zmiany typu danych, przechowuj dane historyczne w formacie xml. zdefiniuj tabelę z następującymi kolumnami (ID, Data, Wersja schematu, XMLData). to z łatwością obsłuży zmiany schematu. ale musisz radzić sobie z xml, a to może spowodować pewien stopień komplikacji przy pobieraniu danych.



Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.