Eine Reise in die Vergangenheit, bitte Effiziente Historisierung von SQL-Datensätzen

Um eine retrospektive Sicht auf die in einer SQL-Datenbank gespeicherten Daten zu erhalten, ist etwas Know-how nötig. Wie man bei der Historisierung von Daten vorgehen kann, wollen wir uns hier näher anschauen.

Anbieter zum Thema

PresseBox - unn | UNITED NEWS NETWORK GmbH

adesso SE

Bei der Aktualisierung einer SQL-Datenbank werden Stammdaten im Regelfall schlicht überschrieben. Eine Historie mit allen damit in Verbindung stehenden Features wie Rückverfolgbarkeit und Rollbacks steht damit nicht zur Verfügung. Im Folgenden präsentieren wir zwei grundlegend unterschiedliche Ansätze, mit denen die Historisierung von Daten behandelt werden kann.

Zunächst wird betrachtet, wie eine Historisierung in beliebigen SQL-Datenbanken mit der Nutzung der zur Verfügung stehenden Grundfunktionen erreicht werden kann. Darauf folgt der Lösungsansatz mit konkreter Datenbankunterstützung anhand der im MS SQLSERVER zur Verfügung gestellten temporalen Tabellen (system versioned temporal tables).

History Counter

Ein gängiger Ansatz zu Historisierung von Datensätzen funktioniert über die fachkundige Nutzung eines zusätzlichen Felds, den HistoryCounter. Diese Methode funktioniert grundsätzlich mit jedem Datenbanksystem, völlig unabhängig von der Version des Serversystems oder des implementierten ANSI-SQL-Standards. Einzige Voraussetzung ist, dass Stored Procedures oder Trigger unterstützt werden.

Indem der zu historisierenden Tabelle das neue Feld HistoryCounter dem Primary Key als Teilschlüssel hinzugefügt wird, erhält der Datensatz praktisch eine Versionsnummer. Zusätzlich zu dieser zwingend notwendigen Maßnahme haben sich in der Praxis auch weitere Felder für Zeitstempel bewährt, beispielsweise CreatedTimestamp, ModifiedTimestamp und DeletedTimestamp.

Es empfiehlt sich, die Zeitstempel in UTC (Universal Time Coordinated) anzugeben, um Fehlerquellen bezüglich konkreter Zeitzonen oder Sommerzeit/Winterzeit vorzubeugen. Daraus ergibt sich die folgende Basisstruktur einer Tabelle (hier mit abstrakten Datentypen zur Verdeutlichung):

Viel Aufmerksamkeit sollte dem korrekten Umgang mit den neuen Feldern gegeben werden. Eine naheliegende Lösung wäre es, mit dem HistoryCounter bei 0 bzw. 1 anzufangen, und bei jedem Update um 1 zu inkrementieren. Somit hätte der aktuelle Datensatz immer den höchsten HistoryCounter.

Allerdings bringt diese Lösung deutliche Nachteile mit sich: Bei einem Update müsste der aktuell höchste HistoryCounter für den Datensatz erst einmal ermittelt werden, um dann den nächsten (neuen) HistoryCounter zu generieren. Noch aufwändiger wird es bei JOIN-Operationen, denn auch hier wäre die Ermittlung des aktuellen HistoryCounter stets erforderlich. Als Konsequenz ist dieser ressourcenhungrige Ansatz (Speicher, Laufzeit des SQLs) nicht zu empfehlen.

Eine empfehlenswerte Alternative ist es hingegen, die aktuelle Version der Daten mit dem HistoryCounter 0 zu pflegen. Entsprechend ist dann der erste Schritt jedes Updates, den HistoryCounter bestehender Daten um 1 zu erhöhen:

In einem zweiten Schritt wird das ursprüngliche Update-Kommando in ein Insert umgewandelt:

Somit gilt: Je älter der Datensatz (im Sinne der Änderungshistorie) ist, desto höher ist der HistoryCounter. Damit einher geht die vereinfachte Realisierung von JOIN-Operationen, da der Aufwand für die Bestimmung des korrekten HistoryCounter wegfällt, der aktuelle Datensatz hat immer die 0. Zudem sind sowohl zeit- als auch anzahlbasierte Cleanup-Strategien leicht zu implementieren. Ein triviales Beispiel wäre die Löschung aller Datensätze mit HistoryCounter größer 9, um je insgesamt 10 Datensätze zu erhalten.

Mit den oben genannten zusätzlichen Timestamps sind auch zeitbasierte Cleanup-Strategien umzusetzen, sodass die gewünschte Historie und ihre Eigenschaften en detail kontrolliert werden können. Die Löschung alter Datensätze gestaltet sich unkompliziert, da sich die referentielle Integrität immer nur auf den HistoryCounter 0 bezieht. Zudem ist es möglich, die historischen Datensätze im laufenden Betrieb zu entfernen, ohne die Tabellen ändern zu müssen.

Aktuell (Stand März 2022) unterstützt der MS SQLSERVER keine „INSERT/UPDATE BEFORE“-Trigger. Als Ersatz könnte entweder ein TRIGGER … INSTEAD OF UPDATE oder eine Stored Procedure dienen. Bei beiden Ansätzen ist die Idee aber die gleiche, zunächst wird der HistoryCounter für den zu ändernden Datensatz erhöht.

Temporale Tabellen (system versioned temporal tables)

Eine weitere Methode zur Historisierung von Datensätzen basiert auf der Nutzung sogenannter temporaler Tabellen. Zunächst ist es wichtig, Begrifflichkeiten zu klären: Während die leicht zu verwechselnden temporären Tabellen sich durch begrenzte Lebensdauer auszeichnen, sind temporale Tabellen eine maßgeschneiderte Lösung für die Nachverfolgbarkeit von Datensätzen.

Temporale Tabellen sind im ANSI SQL:2011 (ISO/IEC 9075-11:2011) definiert worden. Dieser Standard wird beispielsweise in MS SQLSERVER seit der 2016er Version bzw. in der Cloud mit SQL Azure, MySQL, MariaDB, DB2 oder Oracle unterstützt.

Schon beim Anlegen der Datenbank wird ein erster Unterschied zwischen Historisierung per HistoryCounter oder temporalen Tabellen offensichtlich: Während die zuvor präsentierte Lösung mit einer Tabelle auskommt, legt der MS SQLSERVER nun zwei Tabellen an. In der Haupttabelle sind alle aktuellen Datensätze hinterlegt, in der History-Tabelle alle historischen Datensätze.

Beiden Tabellen ist gemein, dass sie zwei weitere Spalten mit Start- und Endzeitpunkt der Gültigkeit brauchen. Eine weitere Besonderheit ist, dass die Haupttabelle immer einen Primary Key (PK) benötigt, während die History-Tabelle grundsätzlich keinen PK besitzt. Da grundsätzlich mehrere Versionen eines Datensatzes in der History-Tabelle zulässig sein sollen, wird so eine PK-Verletzung vermieden.

Im Folgenden wird zur Erläuterung ein Beispiel aus MS SQLSERVER verwendet. Über untenstehenden Aufruf kann die temporale Tabelle erzeugt werden.

Wichtig ist dabei, dass die Angaben der Zeitspalten immer am Ende der Tabellendefinition erfolgen müssen, ansonsten generiert der MS SQLSERVER eine Fehlermeldung. Das obige Kommando erzeugt die hier vorangestellten Tabellen.

Wie zu sehen ist, wurde die History-Tabelle unterhalb der Haupttabelle einsortiert und sieht zunächst identisch aus – nur hat die History-Tabelle keinen PK mehr, dafür aber automatisch einen CLUSTERED INDEX für die Kombination aus den Zeitspalten erhalten. Versäumt man die explizite Angabe eines Namens für die History-Tabelle, erzeugt der SQL-Server eine generische Benennung.

Im MS SQLSERVER MANAGEMENT STUDIO gibt es eine Unterstützung zur Erzeugung von temporalen Tabellen, allerdings ist es nicht möglich, für solche Tabellen den Designer aufzurufen.

Änderungen am Schema können nur per T-SQL durchgeführt werden und wirken sich immer auf die Haupttabelle und die untergeordnete History-Tabelle gleichermaßen aus. Die beiden Zeitspalten (im Beispiel ValidStart, ValidEnd) können nicht mehr direkt durch ein INSERT oder UPDATE belegt werden, diese Aufgabe übernimmt nun der SQLSERVER. Der aktuelle Datensatz hat als ValidEnd immer das Jahr 9999 und ist somit als unbegrenzt gültig markiert.

Möchte man historische Daten löschen, muss zuvor die Historisierung ausgeschaltet werden:

Das ist im laufenden Betrieb nicht immer möglich und bedarf daher einer entsprechenden Planung. Seit MS SQLSERVER 2017 gibt es Retention Policies, also Aufbewahrungsrichtlinien, die ein automatisches Löschen ermöglichen. Dabei ist es nicht möglich, die maximal aufzubewahrende Anzahl der Datensätze anzugeben. Daher ist die Anwendung von solchen Richtlinien je nach Anwendungsfall mit Vorsicht zu genießen. Beispielsweise möchte man die Namensänderung eines Mitarbeiters für einen längeren Zeitraum aufbewahren.

Bei der Änderung eines historischen Datensatzes ist Vorsicht geboten, da in solchen Fällen automatisch ein neuer als aktueller behandelter Datensatz mit den entsprechenden alten Daten angelegt wird. In der Regel ist ein solcher Aufruf daher ein schwerer Programmierfehler. Grundsätzlich sollten historische Daten als unveränderlich betrachtet werden.

Bei einem Update der Daten legt der MS SQLSERVER eine vollumfängliche Kopie des bisher aktuellen Datensatzes an. Damit neigt eine historisierte Datenbank zu rapidem Wachstum, das mit entsprechendem Ressourcenhunger einhergeht. Insbesondere bei großen Feldtypen (varbinary(max), image, …) kann sich das negativ auswirken. Bei solchen BLOB-Daten ist es auch über das Historisierungsproblem hinaus generell ratsam, sie in einer eigenen, über eine Relation verknüpften Tabelle zu verwahren. Damit wird das genannte Skalierungsproblem deutlich entschärft. Dies gilt im Übrigen auch für das oben vorgestellte HistoryCounter-Konzept.

Unterstützung von temporalen Tabellen im Entity Framework Core

Mit dem Erscheinen von EF Core 6.0 ist die Unterstützung temporaler Tabellen auch im Datenbank-Provider des SQL SERVERS enthalten. Andere Datenbankhersteller müssen da ggf. noch ihre Provider aktualisieren.

In LINQ-Abfragen bietet EF Core 6.0 Unterstützung für temporale Tabellen an. Dafür gibt es die neuen Operationen TemporalAsOf(), TemporalAll(), TemporalBetween(), TemporalFromTo() und TemporalContainedIn().

Beim Forward-Engineering kann via Fluent Interface leicht eine temporale Tabelle erzeugt werden.

Da keine weiteren Angaben erfolgt sind, wird die untergeordnete History-Tabelle den Zusatz _HISTORY (also kein kryptischer Name wie beim MANAGEMENT STUDIO) bekommen, die Gültigkeitsspalten heißen PeriodStart und PeriodEnd. Möchte man dies explizit angeben, so lautet die Syntax:

Unabhängig davon, ob die Zeitspalten implizit oder explizit deklariert werden, ist deren Angabe in der Entitätsklasse unzulässig. Grund hierfür ist die Implementierung der Zeitspalten im EF Core als „Shadow Properties“.

Fazit

Sicherlich ist die Unterstützung von temporalen Tabellen im MS SQLSERVER eine nützliche Erweiterung. In vielen Fällen unterstützt sie bei der Historisierung und garantiert konsistente Daten. Dem gegenüber stehen aufwändige Anpassungen, wenn es um das Aufräumen der Daten oder Schemaänderungen geht.

Christian Vogt

Gerade im Hinblick auf letztere Themen bietet die Umsetzung mit einem HistoryCounter eine einfache und in vielen Fällen ausreichende Alternative, die unabhängig vom genutzten SQL-Standard ist. Ein weiterer Vorteil ist die Möglichkeit, Aufräumarbeiten von Altdaten im laufenden Betrieb zu verrichten. Dafür muss die Historisierung auf Datenbank-Ebene mit Triggern oder Stored Procedures realisiert werden.

* Christian Vogt arbeitet bei adesso als Software-Architekt. Auch nach rund 30 Jahren Berufserfahrung begeistern ihn bis heute insbesondere Architekturthemen in der Softwareentwicklung.

Dr. Matthias Dück

Dr. Matthias Dück ist Senior Consultant bei adesso. Mit einem Background in Physik arbeitet er bevorzugt als Product Owner, Requirements Engineer und Business Analyst für High-Tech-Anwendungen.

(ID:48099497)