Slowly Changing Dimensions Typ 2 mit MySQL

Über das folgende Problem bin ich neulich bei dem Aufbau eines DWH gestolpert. Eine Datenbank-Tabelle wird per Bulkload befüllt, soll aber als Slowly Changing Dimension (SCD) vom Typ 2 historisiert werden. Der Bulkload wird per Perlskript angestoßen, das nicht unter der eigenen Hoheit liegt und somit nicht mit eigener Logik ausgestattet werden kann. Die Lösungsidee ist einfach: nur mit Bordmitteln der Datenbank eine SCD Typ2 emulieren…
Gegeben ist die folgende, historisierte Dimensionstabelle – in unserem Beispiel in MySQL 5.0.51 hinterlegt:

CREATE TABLE IF NOT EXISTS `cds` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`ean` varchar(13) collate latin1_general_ci NOT NULL,
`md5` varchar(32) collate latin1_general_ci NOT NULL,
`titel` varchar(200) collate latin1_general_ci default NULL,
`interpret` varchar(200) collate latin1_general_ci default NULL,
`jahr` bigint(4) default NULL,
`gueltig_ab` datetime NOT NULL,
`gueltig_bis` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

Die Tabelle besitzt zwei unterschiedliche Schlüssel: ID ist ein technischer Schlüssel, der über AUTO_INCREMENT hochgezählt wird. Die Spalte EAN, die für Handelsware gebräuchliche European Article Number, ist der Fachschlüssel eines jeden Datensatzes. Neben diesen beiden Schlüsseln existiert noch die Hilfsspalte MD5, in der die fachlich relevanten Spalten, in diesem Fall der Titel, der Interpret und das Erscheinungsjahr, gehasht werden. Mehr zu dem Sinn der Hashspalte, wenn wir zu den Historisierungsmechanismen kommen.

Erzeugen einer Schattentabelle

Um die SCD2 emulieren zu können, benötigen wir einen Schatten zur Zieltabelle: Wir erzeugen eine zweite Tabelle ‚cds_schatten‘, die strukturell ähnlich zu ihrem Vorbild ist, nur die Historisierungsfelder und die ID entfallen. Die Schattentabelle hat also das Format, das wir von der Bulkload-Datei erwarten.

DROP TABLE IF EXISTS `cds_schatten`;
CREATE TABLE IF NOT EXISTS `cds_schatten` (
`ean` varchar(13) collate latin1_general_ci NOT NULL,
`md5` varchar(32) collate latin1_general_ci NOT NULL,
`titel` varchar(200) collate latin1_general_ci default NULL,
`interpret` varchar(200) collate latin1_general_ci default NULL,
`jahr` int(11) default NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Als Storage Engine wählen wir BLACKHOLE. Datensätze, die wir in diese Tabelle schreiben, wollen wir nämlich nicht permanent behalten. Wir benötigen sie nur, um die eigentliche Zieltabelle ‚cds‘ zu befüllen.

Implementierung der Historisierung

Die Historisierung wird nach dem folgenden Schema vorgenommen: Ein neuer Datensatz wird anhand des fachlichen Schlüssels, der EAN, als Version eines vorhandenen Satzes erkannt. Die Speicherung macht aber nur dann Sinn, wenn sich die Inhalte eines der fachlichen Attribute verändert hat, sprich, wenn sich die Hashes der beiden Versionen unterscheiden. Wir laden den Datensatz also zuerst in die Schattentabelle und schauen dann nach, ob sich gemäß dem oben definierten Schema die Notwendigkeit ergibt, den neuen Datensatz in die eigentliche Zieltabelle zu übertragen. Das realisieren wir über einen einfachen BEFORE INSERT Trigger:

DROP TRIGGER IF EXISTS `test`.`bi_cds_schatten`;
DELIMITER //
CREATE TRIGGER `test`.`bi_cds_schatten` BEFORE INSERT ON `test`.`cds_schatten`
FOR EACH ROW BEGIN
DECLARE hash VARCHAR(32);
SELECT md5 INTO hash FROM cds WHERE ean=NEW.ean AND gueltig_bis>NOW();
IF ((hash IS NULL) OR (hash<>NEW.md5)) THEN
UPDATE cds SET gueltig_bis=NOW() WHERE ean=NEW.ean AND gueltig_bis>NOW();
INSERT INTO cds (ean,md5,titel,interpret,jahr,gueltig_ab,gueltig_bis)
VALUES (NEW.ean,NEW.md5,NEW.titel,NEW.interpret,NEW.jahr,NOW(),21001231);
END IF;
END
//
DELIMITER ;

Für jeden Eintrag in die Schattentabelle wird dann der Trigger aufgerufen und trägt in Folge dessen eventuell Daten in die Zieltabelle ein.
Wir definieren im Trigger zuerst eine Variable hash. Als nächstes prüfen wir per SELECT, ob passend zu der neu eingetragenen EAN ein Gegenstück in der Zieltabelle existiert (ean=NEW.ean), das auch zurzeit gültig ist (gueltig_bis>NOW()). Das Ergebnis dieser Anfrage speichern wir in der Variable hash. Für den Inhalt von hash gibt es dann zwei Möglichkeiten: entweder steht darin ein 32-stelliger, alphanumerischer MD5-Wert, d.h. die Suchanfrage hat ergeben, dass es eine alte, aber gültige Version zu der aktuellen EAN gibt, oder der Wert der Variable ist NULL, d.h. es gab keine Vorgängerversion.
Folglich vergleichen wir den Variablenwert mit dem neu einzutragenen MD5-Wert. Ist der Hashwert ungleich (hash<>NEW.md5) oder von vornherein NULL, bedeutet das, dass wir den neuen Datensatz in die Zieltabelle eintragen müssen, ansonsten ist die alte Version unverändert. Dazu terminieren wir zuerst die Gültigkeit der alten Version per UPDATE-Befehl und tragen die neuen Daten ein – inklusive Historisierungsfelder.
Nach Ausführung des Triggers wird der Datensatz in der Schattentabelle nicht permanent gespeichert, sondern verworfen – ein angenehmer Effekt der BLACKHOLE Storage Engine.

Fazit
Die vorgestellte Lösung besitzt einige charmante Effekte: Zum einen müssen wir uns beim Eintragen der Daten nicht um die Befüllung der Historisierungsfelder und die Terminierung alter Versionen interessieren, dies übernimmt der Trigger. Zum zweiten müssen wir uns dank der Wahl der richtigen Storage Engine nicht um die Entsorgung der Inhalte in der Schattentabelle kümmern und zum dritten müssen wir für diesen Weg keinerlei Änderungen an der Zieltabelle selbst vornehmen.
Die Nachteile, die sich in Bezug auf die Performance ergeben, sind schwer zu bemessen. Sicherlich existieren Reibungsverluste durch die Ausführung des Triggers bei den Einträgen. Dem gegenüber stehen jedoch Gewinne bei der Erstellung klassischer ETL-Jobs, in denen die richtige Befüllung der SCD implementiert ist.
Dieses Konzept sollte sich prinzipiell auch auf andere DBMS übertragen lassen. Nicht überall existiert jedoch ein Konstrukt wie die BLACKHOLE Storage Engine, so dass man die Daten in der Schattentabelle eventuell händisch wieder löschen muss.

Advertisements