Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

INSERTS abfangen und umleiten

9. Oktober 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Vorab: Das Ganze ist hier mehr exemplarisch und war eher als „proof of concept“ gedacht. Das Beispiel ist extra einfach gehalten und soll das Verfahren nur verdeutlichen.

 

Problemstellung:

Wir haben eine externe Software, die mit einer proprietärer Datenbank arbeitet, auf die man von Außen nicht zugreifen kann, wir aber nun die Daten benötigen. Es steht nur eine Exportfunktion zur Verfügung, um den Bestand komplett zu exportieren. Neben einem Flatfile-Export kann die Software auch in eine SQL Server Datenbank schreiben, aber

-          festes, denormalisiertes Tabellendesign

-          nur Varchar-Felder / Daten

-          nur INSERT Statements

Also ein ganz „toller“ SQL Export, wurde wohl nur zu Marketing-Zwecken eingebaut, „Tooles Feature, wir können in eine SQL DB schreiben!“ (boah, ey).

 

Nun könnte man sagen: „Lass machen; wenn es fertig ist, bereite ich mir die Daten anderweitig mit SSIS und/oder SQL Scripten auf“; also ein Staging-Bereich im Dataware House zu Verfügung stellen und damit weiter arbeiten. Bei einem umfangreichen Szenario, sprich viel Tabelle und/oder Daten, wäre der Aufwand zur Sicherstellung der Daten-Qualität und Performanz gerechtfertig.

 

Und wie könnte man es ohne großen Aufwand als kleine, prozessunabhängige Lösung umsetzen?

 

Lösungsansatz:

Des Softwares Wille soll sein Himmelreich (oder was immer) sein. Aber eine Tabelle gibt es trotzdem nicht, da bin ich Stur.

Die eigentlichen Tabellen lege ich nach meinen Vorstellungen und natürlich normalisiert an. Hinzu kommt dann ein View, das von den Feldern her dem geforderten Tabellendesign entspricht und die Daten aus meinen Tabellen selektiert.

Dieses View ist aber definitiv nicht aktualisierbar, INSERTs können darauf nicht ausgeführt werden; wie kann jetzt trotzdem die externe Software hier rein schreiben?

Ganz einfach, mittels eines INSTEADOF Trigger, der, wieder Name schon sagt, statt des eigentlichen INSERTs ausgeführt wird und in der virtuellen Tabelle „inserted“ die Daten mit Tabellendesigns der View hat. Im Trigger kann man dann die Business Logic umsetzten, mit der man die Daten in die eigentlichen Tabellen weg schreibt.

 

Hier mein Demo Script dazu:

 

 SET NOCOUNT ON; -- Kein Feedback über Anzahl 
   
 -- Basistabellen anlegen 
 CREATE TABLE dbo.FirstNames 
   (Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, 
   firstname nvarchar(50) NOT NULL UNIQUE); 
   
 CREATE TABLE dbo.LastNames 
   (Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
   lastname nvarchar(50) NOT NULL UNIQUE); 
   
 CREATE TABLE dbo.Names 
   (Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
   firstname_id int NOT NULL, 
   lastname_id int NOT NULL); 
 GO 
 -- Kombination Vor/Nachname Unique 
 CREATE UNIQUE NONCLUSTERED INDEX [UQ_FirstLastName] 
 ON [dbo].[Names] 
   ([firstname_id] ASC, 
   [lastname_id] ASC  ) 
 GO 
   
 -- Sicht mit den Namen auf die Basistabellen 
 CREATE VIEW dbo.vwNames 
 AS 
   SELECT N.Id, F.firstname, L.lastname 
   FROM dbo.Names AS N 
   INNER JOIN dbo.FirstNames AS F 
   ON N.firstname_id = F.Id 
   INNER JOIN dbo.LastNames AS L 
   ON N.lastname_id = L.Id; 
 GO 
 -- Geht natürlich (noch) nicht 
 INSERT INTO dbo.vwNames (firstname, lastname) 
 VALUES ('Olaf', 'Helper');  
 GO 
   
 -- InsteadOf Trigger zum Abfangen der INSERTS 
 CREATE TRIGGER dbo.TRI_vwNames ON dbo.vwNames 
 INSTEAD OF INSERT 
 AS 
 BEGIN 
   -- Fehlende Vornamen eintragen 
   INSERT INTO dbo.FirstNames (firstname) 
   SELECT DISTINCT I.firstname  -- Distinct wegen Unique!! 
   FROM inserted as I 
   LEFT JOIN dbo.FirstNames AS F 
   ON I.firstname = F.firstname 
   WHERE F.firstname IS NULL 
   AND NOT I.firstname IS NULL 
   
   -- Dito Nachnamen 
   INSERT INTO dbo.LastNames (lastname) 
   SELECT DISTINCT I.lastname  -- Distinct wegen Unique!! 
   FROM inserted as I 
   LEFT JOIN dbo.LastNames AS L 
   ON I.lastname = L.lastname 
   WHERE L.lastname IS NULL 
   AND NOT I.lastname IS NULL 
   
   -- Nun fehlende Namenseinträge 
   INSERT INTO dbo.Names (firstname_id, lastname_id) 
   SELECT DISTINCT F.Id, L.Id  -- Distinct wegen Unique!! 
   FROM inserted AS I 
   INNER JOIN dbo.FirstNames AS F 
   ON I.firstname = F.firstname 
   INNER JOIN dbo.LastNames AS L 
   ON I.lastname = L.lastname 
   LEFT JOIN dbo.Names AS N 
   ON N.firstname_id = F.Id 
   AND N.lastname_id = L.Id 
   WHERE N.Id IS NULL 
 END; 
 GO 
   
 -- Nun mehrere Datensätze in einem Rutsch 
 -- direkt ins View schreiben 
 INSERT INTO dbo.vwNames 
   (firstname, lastname) 
 SELECT 'Olaf' AS firstname, 'Helper' AS lastname 
 UNION ALL SELECT 'Peter',  'Mustermann' 
 UNION ALL SELECT 'Anonymus', 'Unbekannt' 
 UNION ALL SELECT 'Peter',  'Unbekannt' -- Vorhandener Vor+Nachname 
 UNION ALL SELECT 'Olaf',  'Helper'  -- Doppelter Name 
 UNION ALL SELECT NULL,  NULL  -- Schrott 
 GO 
   
 -- Angekommen? Jawoll! 
 SELECT * FROM dbo.vwNames 
 GO 
   
 Ergebnis: 
   
 Id  firstname  lastname 
 --- ---------- ------------ 
 1  Anonymus  Unbekannt 
 2  Olaf  Helper 
 3  Peter  Mustermann 
 4  Peter  Unbekannt 

 

Fazit:

Mit der Methode kann man zum einen Fremdappliaktionen einen Schreibzugriff einrichten und zugleich die eigentliche Datenbankstruktur gegenüber Außen völlig transparent halten. Zudem lässt sich so eine kleiner datengetriebener ETL Prozeß realisieren.

 

Zugegeben, nichts neues, nichts wildes, aber wollte ich halt mal ausprobieren.

Diesen Post teilen

Repost 0

Kommentiere diesen Post