Temporale Tabellen

[BerndB]

Nur als Seitenifo:

MariaDB bietet mit systemversionierten Tabellen eine effektive Möglichkeit, die Historie von Datenänderungen automatisch zu erfassen und zu verwalten. Diese Funktion ermöglicht es, Änderungen an Tabellenzeilen nachzuverfolgen und den Zustand der Daten zu einem bestimmten Zeitpunkt in der Vergangenheit zu rekonstruieren.

Tabelle anlegen

CREATE TABLE beispiel_tabelle (
   id INT PRIMARY KEY,
   wert VARCHAR(255)
) WITH SYSTEM VERSIONING;

Zugriff mit Teitstempel

SELECT * FROM beispiel_tabelle
FOR SYSTEM_TIME AS OF '2025-02-01 12:00:00';

Sonst kann man ganz normal wie gewohnt zugreifen und erhällt immer die aktuellen Daten

Einen Bereich abfragen

SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW();

und DELETE auch

DELETE HISTORY FROM beispiel_tabelle
BEFORE SYSTEM_TIME '2024-01-01 00:00:00';

MariaDB Manual


Also alle vorhanden, ohne das man sich um was kümmern muss.

Gruß

Bernd

 

[dabadepdu]

Oh NOOOOOOOO, kein Workaround !!

Temporäre Tabellen haben schon ihre Vorteile. Wenn z.B. ein User Daten importieren kann (CSV,Excel oder jedes andere Format) und erst nach dem Import festgestellt werden kann ob diese valide sind oder man anzeigen möchte was sich dadurch ändert lohnt sich ein Temp. Table.

Natürlich geht das auch mit einer normalen Import Tabelle, jedoch wenn mehrere oder alle User dies gleichzeitig machen können kommt der Vorteil, denn für jede wird eine eigene Tabelle mit dem gleichen Namen (nach aussen) angelegt, die nur für dich ist und andere Queryies sehen diese auch nicht.

OT
Oh JES!
Für mehrere User oder auch mehrere Importsitzungen des gleichen Users kann man UserID und ID der ImportTransaktion mitschreiben. Damit kann man in einer nicht temporären Tabelle viel mehr erreichen, als temporär. Der gesamte Importvorgang inkl. allem Dreck der reinkommt wird transparent. Der Kunde kann soviel Instanzen, Tabs oder was auch immer gleichzeitig für verschiedene Imports laufen lassen, wie durch die Leitung passt und man kann alles oder nichts erlauben oder Teilimporte, einzelne DB Fehler pro Datensatz protokollieren, Fehler anzeigen sowie absolute oder relative Zahlen zum Fortschritt. Dem Datenlieferanten kann man das dann (ich oder der Kunde) zurückschicken und ihn bitten, sauber zu arbeiten. Je schlimmer die Datenqualität, desto mehr freut man sich. Das geht mit temporären Tabellen so nicht, vor allem nicht so elegant.
In besonderen Fällen kann man diese Daten alle bequem im Frontend nachschlagen.
Und wenn ich nach x Jahren Aufbewahrungsfrist oder aus anderen Gründen nicht mehr will, können die originalen Importdaten bequem gelöscht werden.

 

[BerndB]

OT
Oh JES!
Für mehrere User oder auch mehrere Importsitzungen des gleichen Users kann man UserID und ID der ImportTransaktion mitschreiben. Damit kann man in einer nicht temporären Tabelle viel mehr erreichen, als temporär. Der gesamte Importvorgang inkl. allem Dreck der reinkommt wird transparent. ...

Das mag ja für nen Tante Emma Laden völlig ausreichend sein. Rechne einfach mal eine Verkaufsplattform mit 1.000.00 Produkten, die jeweils von mindesten 5 Lieferanten geliefert werden können und du die Lagerbestände im alle 15 Minuten von jedem Lieferanten bekommst. Keine Ahnung was du da anstellen willst. Alleine das löschen der Altdaten von einem Lieferanten würde ja ewig mit DELETE dauern, da das ganze ja per Transaktion geht, du nur einen Index hast der bei JEDEM WRITE / DELETE gepflegt wird.So kannst du einfach ein TRUNCATE nutzen, was die Tabelle einfach löscht und eine neue erzeugt, an jeglicher Transaktion vorbei. Der Index bleibt schmal und ist somit schnell. Andere müssen nicht warten wenn du Daten änderst. Und wieviele unterschiedliche Versionen soll die DB verwalten wenn nur 50 Leute gleichzeitig schreiben und andere lesen wollen. Der Overhead ist viel zu groß. Probiers einfach mal

 

[dabadepdu]

Der Overhead sollte immer so klein wie möglich sein. Da geb ich Dir Recht. Ansonsten kann ich nicht recht nachvollziehen, was du schreibst.
Ein Update der Tabelle A von 100000 Datensätzen aus Tabelle B dauert vielleicht ne Sekunde. Mit Profihardware eher weniger.
Das Löschen der gleichen Menge in einer Importtabelle ohne referential constraints dürfte deutlich unter einer Sekunde liegen, eher 10tel Sekunde. Eine Transaktion ist es sowieso immer. Ein Truncate würde bei einer temporären Tabelle nicht nötig sein, die sich automatisch löscht, je nach Definition. Es gibt nur eine Import-Tabelle (je Zieltabelle oder definiertem Verfahren) für alle Nutzer. Welche Versionen meinst Du?

 

[Dukel]

Evtl. kann man die Diskussion über Temporäre Tabellen hier abschneiden, hier geht es eigentlich um Temporale (Historische) Tabellen.

 

[Tommi]

Hallo Martinha

(auch hallo an alle anderen, die dies lesen - hab mich lange nicht mehr im Forum beteiligt).
Das Thema mit den temporalen Tabellen ist tatsächlich interessant,
Die wurden mit dem SQL Server 2016 eingeführt und ich habe auch tatsächlich schon ein Projekt gemacht, in dem diese umfangreich eingesetzt worden sind. In erster Linie werden diese verwendet, um Datenänderungen in einer Zeile abbilden und nachvollziehen zu können.

Eine temporale Tabelle erzeugt neben der eigentlichen Datenhaltungs-Tabelle eine Historisierungs-Tabelle, in der entsprechende Spalten den Gültigkeits-Zeitraum des Datenstandes angeben.
Mit jedem Update werden die vorher in der Datenhaltungs-Tabelle existierenden Daten vor der Änderung in die parallele Historisierungs-Tabelle übertragen. Diese Datenzeilen erhalten dann einen Gültigkeitszeitraum (von...bis).
Grundsätzlich eine nette Sache, da man solche Nachverfolgungs-Tabellen ansonsten in Applikationen immer selbst programmieren oder sogar Trigger einsetzen musste - jetzt regelt der SQL Server das Vorgehen selbst.

Ich weiß nicht, wie es in neueren SQL Server Versionen ist, ob sich da an der Funktionalität was geändert hat, aber problematisch waren an dem System immer die aus einer Tabelle gelöschten Daten. Wenn Daten gelöscht wurden, sind diese nicht in die temporale Tabelle gewandert. Man konnte so also nicht mehr nachvollziehen, wann mit welchem Account der Datensatz entfernt wurde.

Hier kann man sich nur damit helfen, dass solche Daten nicht wirklich gelöscht, sondern mit einen Delete-Flag versehen werden. Dann wird auch ein Lösch-Vorgang in der Historisierungs-Tabelle sichtbar.

Ich habe auch schon über einen Einsatz im DWH nachgedacht, da aber noch nicht den echten Vorteil des Systems an sich erkennen können.

Die temporalen Tabellen sind etwas umständlich abzufragen - wenn man Daten für einen bestimmten Zeitpunkt abfragen möchte, ist das noch recht einfach, wenn man aber Daten über einen Gültigkeits-Zeitraum abfragen möchte, hat man das gleiche Problem wie bei jeder anderen Art von Historisierung - Welcher Datensatz in der Historie ist der letzte in diesem Zeitraum...

Auch werden die temporalen Tabellen nicht automatisch mit einem Index versehen, weder mit einem PK noch mit einem Columnstore-Index. Das muss dann manuell gemacht werden.

Genauere Informationen findet man natürlich in der MS-Doku:

Temporale Tabellen - SQL Server
Verwendungsszenarien für temporale Tabellen - SQL Server

Viele Grüße,
Tommi

 

[t-sql]

Nur als Seitenifo:

MariaDB bietet mit systemversionierten Tabellen eine effektive Möglichkeit, die Historie von Datenänderungen automatisch zu erfassen und zu verwalten. Diese Funktion ermöglicht es, Änderungen an Tabellenzeilen nachzuverfolgen und den Zustand der Daten zu einem bestimmten Zeitpunkt in der Vergangenheit zu rekonstruieren.

Tabelle anlegen

CREATE TABLE beispiel_tabelle (
   id INT PRIMARY KEY,
   wert VARCHAR(255)
) WITH SYSTEM VERSIONING;

Zugriff mit Teitstempel

SELECT * FROM beispiel_tabelle
FOR SYSTEM_TIME AS OF '2025-02-01 12:00:00';

Sonst kann man ganz normal wie gewohnt zugreifen und erhällt immer die aktuellen Daten

Einen Bereich abfragen

SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW();

und DELETE auch

DELETE HISTORY FROM beispiel_tabelle
BEFORE SYSTEM_TIME '2024-01-01 00:00:00';

MariaDB Manual


Also alle vorhanden, ohne das man sich um was kümmern muss.

Gruß

Bernd

Also wie beim SQL Server auch ab SQL Server 2016, also noch vor MariaDB.

 

[BerndB]

Ah, verstehe! !!
Du meinst, SQL Server ist schon ziemlich alt, während MariaDB eine moderne, fortschrittlichere Lösung ist.

 

[BerndB]

Aber ist hier alles "off-topic", und sollten wir an dieser Stelle lassen. Das bringt hier keinen weiter

 

[Martinha]

Hi Tommi,

das mit dem Löschen klappt wunderbar. Wenn ich einen DS lösche, ist er in der aktuellen Version der DB nicht mehr vorhanden, wohl aber in den historischen Tabellen. Ich habe eine einfache Abfrage geschieben: zeige mir alle DS die in den hitorischen Tabellen sind, aber nicht in den aktuellen und schon sieht man die Daten.
Sie aus den hitorischen Daten auch noch zu löschen ist schon etwas komplexer, dazu muss man die Historienführung erst ausschalten, dann löschen und ann wieder anschalten.
Diese Daten abzufragen ist kein Problem, es stehen dazu genügend Werkzeuge zur Verfügung. Ich habe zB die Erfahrung gemacht, das man in einer between d1 and d2 Abfrage von den Werten jeweils eine Sekunde addieren , bzw. abziehen sollte, dann ist man auf der sicheren Seite.
Diese Funktionalität selbst zu entwickeln ist eine Aufgabe für einen, der Mutter und Vater eschlagen hat, wi wir im Ruhrgebiet sagten. Vor allem bei verknüpften Tabellen kann es doch sehr komplex werden.
Ich liebe dieses Feature...man muss allerding bedenken, das u.U. recht viele Daten entstehen, aber es gibt ja Parameter, die die Speicherungsdauer begrenzen.

Falls du noch Fragen hast...jederzeit!

Martin

 

[Martinha]

Hi Ukulele,

so ganz habe ich deine Frage nicht verstanden, aber das System arbeitet so:
Nehmen wir an, die historische Datenhaltung hat am 1.2.2025 begonnen.
Dann haben alle aktuellen DS validfrom: 1.2.2025 validto: 31.12.9999

Ändere ich jetzt einen DS am 15.2.2025 so wird ein DS in de historiscchen Tabelle angelegt mit
validfrom: 1.2.2025 validto: 15.2.2015
In dem aktuellen DS wird gesetzt:
validfrom: 15.2.2025 validto 31.12.9999

Du kannst eine Abfrage absetzen mit "as of (datum)", dann sagt dir das System welche Version zu diesem Zeitpunkz gültig war und es fragt dann die aktuelle und die historische Version quasie als Einheit ab.

Ich hoffe zur Klärung beigetragen zu haben...

MfG

Martin

 

[ukulele]

Meine Frage war im Prinzip kann ich am 16.2.2025 ein UPDATE table SET validto = 15.2.2025 machen, um manuell zu bestimmen, ab wann der Datensatz als geändert gilt.

 

[MDDaniel]

Meine Frage war im Prinzip kann ich am 16.2.2025 ein UPDATE table SET validto = 15.2.2025 machen, um manuell zu bestimmen, ab wann der Datensatz als geändert gilt.

Nope, die Spalten die vom System für die Versionierung verwendet werden, können nicht manuell verändert werden, solange der Mechanismus aktiv ist.