Temporal Tables

Zeitabhängigkeit in Datenbanktabellen

Jörg Brandeis

(C) Brandeis Consulting.

Arten von zeitabhängigen Datenbanktabellen

"SAP HANA History Tables" - Ab HANA 1.0

Vom System vergebene Zeit-Dimension, die Änderungen versioniert. Mit SQL kann man die Daten lesen, wie zum Zeitpunkt X. Die Zeitverwaltung ist für das SQL eine Black-Box!

System-Versioned Tables - Ab HANA 2.0 SPS03

Die Systemversionierten Tabellen entsprechen dem SQL-Standard. Zwei Tabellen, eine für die aktuell gültigen Daten und eine für die Historie. Die Gültigkeitszeiträume sind transparent.

Application-Time Period Tables - Ab HANA 2.0 SPS04

Eine Tabelle mit einer Zeitdimension der Anwendung. Die Zeitintervalle werden vom System generiert.

Bitemporale Tabellen - System- und Anwendungs-Zeitabhängigkeit

Zwei Tabellen

(C) Brandeis Consulting.

"SAP HANA History Tables"

Das System verwaltet die Historie. Wir brauchen keine Zeitdimension selber einbauen. Der Zugriff auf Zeitscheiben erfolgt entweder

  • auf Anweisungsebene: SELECT ... AS OF <Timestamp> oder
  • auf Session Ebene: SET HISTORY SESSION TO UTCTIMESTAMP = <timestamp>

Der Timestamp im Beispiel ist für die einfacherer Testbarkeit....

create history column table my_sap_history_table( my_insert_timestamp timestamp,
                                                  value nvarchar(100));

-- Mehrere Inserts....
insert into "AOK_DEMO"."MY_SAP_HISTORY_TABLE" values( current_utctimestamp, 'Zeile 3');

-- Erster Datensatz:  20.01.2023, 10:20:14.391

select * from "AOK_DEMO"."MY_SAP_HISTORY_TABLE" as of utctimestamp '2023-01-20 10:21:15' ;

(C) Brandeis Consulting.

"SAP HANA History Tables" - Bewertung

Vorteile

  • Einfach zu erstellen
  • Die Frage: Wie sah es am 1.1.2022 aus, lässt sich leicht beantworten.
  • Die Vergangenheit ist nicht manipulierbar.

Nachteil

  • Die Zeitdimension ist nicht sichtbar.
  • Die Frage: Wann hat sich der Datensatz X im Laufe der Zeit verändert ist nicht zu beantworten.
(C) Brandeis Consulting.

System-Versioned Tables

Es werden zwei Tabellen angelegt: Die Versionierte-Tabelle und die zugehörige Historien-Tabelle (Wichtig: das ist keine "SAP HANA History Table"). Beide müssen weitgehend gleich aussehen.
Die Versionierte-Tabelle kann ganz normal bearbeitet werden. Die Historientabelle darf nicht mit UPDATE oder INSERT verändert werden. Ein DELETE ist aber zulässig.

Die Gültigkeitszeiträume sind transparent und können im SQL abgefragt werden.

create column table my_system_versioned_table_history( id int , 
		                                               value nvarchar(100),
		                                               valid_from timestamp ,
		                                               valid_to   timestamp );

create column table my_system_versioned_table( id int primary key, 
                                               value nvarchar(100),
                                               valid_from timestamp not null generated always as row start,
                                               valid_to   timestamp not null generated always as row end,
                                               period for system_time( valid_from, valid_to) )
              with system versioning history table my_system_versioned_table_history ;
(C) Brandeis Consulting.

insert into my_system_versioned_table (id, value) values (2, 'zweiter Datensatz') ;

select * from "AOK_DEMO"."MY_SYSTEM_VERSIONED_TABLE_HISTORY" ;

update my_system_versioned_table set value = value || '-Updated' where id = 1;

Vorteile

  • Vollständiger lesender Zugriff auf die existierenden Intervalle
  • Zeitpunktabfrage mit AS OF <Timestamp> sind möglich

Nachteile

  • Zwei Tabellen notwendig
  • Die Vergangenheit kann durch Löschung verändert werden
(C) Brandeis Consulting.

Application-Time Period Tables

Die Anwendung gibt die Zeitdimensionen vor, das Systemdatum spielt keine Rolle:

  • Beim Anlegen eines Datensatzes muss initial ein Gültigkeitsintervall definiert werden. Im Beispiel verwenden wir Default-Werte 1.1.1 bis 31.12.9999
  • Beim Ändern eines Datensatzes wird
create  column table my_application_time_period_table( id int,
                                                      value nvarchar(100),
                                                      valid_from date not null default '0001-01-01',
                                                      valid_to   date not null default '9999-12-31',
                                                      period for application_time( valid_from, 
                                                                                   valid_to ),
                                                      primary key( id, valid_from) );
(C) Brandeis Consulting.

Bitemporäre Tabellen

create  column table my_bi_temporal_history(  id int,
	                                          value nvarchar(100),
	                                          app_valid_from date not null,
	                                          app_valid_to   date not null,
	                                          sys_valid_from timestamp not null,
	                                          sys_valid_to   timestamp not null ) ;
	                                          
create  column table my_bi_temporal_table(  id int,
	                                          value nvarchar(100),
	                                          app_valid_from date not null,
	                                          app_valid_to   date not null,
	                                          sys_valid_from timestamp not null GENERATED ALWAYS AS ROW START,
	                                          sys_valid_to   timestamp not null GENERATED ALWAYS AS ROW END,
	                                          period for application_time(app_valid_from, 
	                                                                      app_valid_to),
	                                          period for system_time(sys_valid_from, 
	                                                                 sys_valid_to),
	                                          primary key (id,
	                                                       app_valid_from ) )
	                                          with system versioning history table "AOK_DEMO"."MY_BI_TEMPORAL_HISTORY"  
(C) Brandeis Consulting.

Zugriff auf Bi-Temporale Tabellen

Der Zusatz AS OF muss hier genauer spezifiziert werden, um klarzustellen welche Zeitdimension jeweils gemeint ist: :

  • FOR SYSTEM_TIME AS OF '<timestamp>'
  • FOR APPLICATION_TIME AS OF '<timestamp>'
 insert into  my_bi_temporal_table 
          (id, value)
   values (1, 'Erster Datensatz') ;
   
--   20.01.2023, 11:44:18.569159
   select * from my_bi_temporal_table for system_time as of  '2023-01-20 11:44:18';
   
   
   update my_bi_temporal_table
   for portion of application_time from date'2024-01-01' to date'2024-12-31'
   
   set value = value || '-Updated'   where id = 1;
(C) Brandeis Consulting.