ETL - Zugriff auf remote Datenbank in einer stored procedure

[Romanticus]

Hallo,

ich bin nunmehr seit etwa 3-4 Stunden auf der Suche nach einer Lösung für mein Problem. Ich habe zwei mySQL Datenbanken (5.1.11). Die eine ist die produktive DB, auf der die Daten dauernd aktualisiert werden. Außerdem ist das DB-Schema etwas suboptimal, um es mal vorsichtig ausdrücken. Viele Daten sind redundant, einige fehlen usw. Die zweite Datenbank soll eine Art DataWarehouse darstellen, die die bereinigte und zum Teil aufbereitete Daten aus der produktiven Datenbank enthalten, auf denen verschiedene Analysen ablaufen werden.
Problem: Die Daten müssen möglichst effizient und performant aus der ersten Datenbank in die zweite übertragen werden.

Lösung 1: Ein Script, das die Daten aus der ersten DB einliest, transformiert und in die zweite schreibt. Vorteil - Die Transformationen können beliebig komplex werden (+für mich weniger Einarbeitungszeit). Nachteil - viele SELECT- und INSERT-/UPDATE-Statements.

Lösung 2 (meine Entscheidung): Verwenden der Möglichkeiten, die mySQL 5 bietet (Views, Trigger, Stored Procedures).

Ich habe nun auf der ersten Datenbank Views erstellt, die schon transformierte Daten zurückgibt. Meine Idee ist folgende - eine Stored Procedure auf der zweiten Datenbank, die ein SELECT auf diese Views macht und die Ergebnisse bei sich in die entsprechende Tabellen schreibt. Leider habe ich bis jetzt nicht rausfinden können, ob das überhaupt möglich ist und, wenn ja, wie ich das anstelle.

Ich hoffe, dass es nicht zu verwirrend war und mir jemand helfen kann. Ein Stichwort sollte reichen .

Gruß,
Romanticus

 

[marbe]

Hallo,

ich arbeite seit mehren Jahren im Bereich Datawarehouse (DWH) ich würde Dir zu ersterer Lösung empfehlen und ein Database Link erstellen, zu einem definieren Zeitpunkt die Daten extrahieren in eine sog. Staging Area und dann ggf. nach Anforderung die Transformationen und Berechnungen durchführen.

Das mag sehr aufwendig klingen, ist es teilweise auch, aber lieber jeden SQL Statement selbst geschrieben als so ein "Vollautomatisches" Zaubertool wo niemand weiss was es wirklich macht.

Bei so ziemlich jedem DWH Projekt was ich mit gemacht habe, passten die Daten nicht, Stichwort Datenqualität einheitliche Vorgehensweise etc.

Ich verfolge bei solchen Umsetzungen daher immer das Unabhängigkeitsprinzip d.h. keine Trigger, Prozeduren (die vom DWH sind) kommen auf das operative System. Das wird alles nach der Extraktion im DWH Server gerechnet.

In der Regel müssen die Daten auch noch Historisiert werden auch da soll es automatische Werkzeuge geben, aber naja bisher hab auch hier ich lieber selbst programmiert.

Wenn Du zu dem Thema noch Fragen hast sprich mich ruhig PN an. Will ja hier niemand langweilen

 

[Romanticus]

Es sind ja keine "Zaubertools" im engeren Sinne. Ich definiere die Fuktionen und Prozeduren ja selber und habe dadurch auch die volle Kontrolle darüber, was da genau passiert. Der Vorteil dieser Lösung ist meiner Meinung, dass weniger Daten übers Netzwerk transporiert werden, da einfache Prüfungen und Transformationen direkt auf der Quell-Datenbank durchgeführt werden. Der Nachteil ist natürlich der, dass zum Zeitpunkt der Datenextraktion die operative Datenbank mehr ausgelastet ist. Es gibt noch einen nicht fachlichen Grund für diese Vorgehensweise - die Arbeit ist meine Bachelor-Thesis und mein prüfender Prof ist ein ziemlicher DB-Freak und die zweite Lösung hat ihm mehr gefallen, als die erste , also wäre es nicht verkehrt dies auch so zu tun.
Inzwischen habe ich einen Mittelweg gefunden - die einfachen Transformationen und Views macht die Datenbank, die Kontrolle über den Ablauf der Extraktion und kompliziertere Transformationen übernimmt ein Java-Consolen-Programm.

Gruß,
Romanticus

P.S. ich denke nicht, dass wir hier jemanden langweilen - ich habe zu diesem Thema nicht sehr viel im Netz gefunden, deswegen ist es sicher nicht verkehrt das mal mit jemandem zu diskutieren, der praktische Erfahrung hat.

 

[marbe]

Hallo,

wenn es dem Prof gefallen muss, dann gelten ehh andere Regeln. Da müssen wir ja alle durch, wenn wir mal ein Diplom bzw Bachelor/Master wollen.

Den Ansatz den dien Prof besser findet also Trigger etc auf dem Quell System laufen lassen
macht nur Sinn wenn man sehr aktuell sein muss (z.B. Handelsdaten der Börse). Das ist in der Praxis ziemlich selten der Fall oft reichen hier 1x am Tag geladene Daten zum auswerten aus.

Sich direkt ins Quellsystem zu hängen hat auch andere Sachen zur Folge z.B. Muss beim Releasewechsel der Software immer daran gedacht werden, das man jemand "drittes" im System hat. Die komplexität der Anwendung steigt und damit die Kosten (das berühmte Schnittstellen Thema).

Oft gibt es auch Revisionen die eine strikte Trennung zwischen Quellsystem und Data Warehouse wollen, Unabhängigkeit. Das Auswertungsystem soll ja auch zur Qualitätssicherung verwendet werden. Andere Frage die gern gestellt wird ist die Ausfallsicherheit. Aber wie bei allem entscheidet der Kunde was er möchte und zahlt auch dafür.

Und wie sagt man immer so schön die beste Lösung ist nicht immer die billigste und die billigste Lösung ist nicht immer die beste

Und bekanntlich führen viele Wege nach Rom

 

[moquai]

hallo,
ich habe ein ähnliches problem und brauche den nötigen schups in die richtige richtung.

also folgendes szenario:
- 3 server (MS SQL Server 2000) sammeln produktionsdaten (ca. 10 MB/Tag)
- diese hängen an einem Intranet
- neu soll jetzt ein zentraler Server angeschafft werden der die Daten der 3 Produktionsservern sammelt und länger speichert und Mittels Webserver webfähig macht
- gefordert wurde das der zentrale Server die Daten in eine Oracle DB speichern soll
(bitte berichtigt mich aber wenn die 3 anderen Server schon mit SQL Server 2000 laufen wär doch der Datenaustausch sicher einfacher wenn auch der zentrale Server mit SQL Server läuft oder? Zumal sich die Vor- und Nachteile relativ aufwiegen, ist mehr ne Geschmackssache oder?)
- so dann wär ich schon beim Datenaustausch. Nachdem ich ein wenig rumgegoogelt hab und mich ganz entfehrnt an eine Datenbankvorlesung erinnert habe bin ich darauf gekommen die Daten durch den zentralen Server mittels einer stored procedure zeitgesteuert (3 mal/Tag) von den 3 Produktionsservern holen zu lassen.
(wär das aus eurer Sicht ein möglicher Weg?)
Da ich allerdings noch nie etwas mit stored procedures gemacht habe, würde ich mich freuen wenn ihr mir etwas dazu posten könntet.
Links zu Tutorials, Büchervorschläge etc.

Ich hoffe ihr könnt mir ein paar Tips geben. Bei auftretenden Fragen einfach nochmal nachffragen
danke schonmal
grüße vom mo

 

[marbe]

Hallo,

das hört sich in der Tat komisch an wenn alle Server MS sind und als zentraler Server ein
Oracle genutzt werden soll.

Grundsätzlich kann man Linked Server einrichten und in den Mitte auch ein MS SQL Server und dann per Job die Daten sammeln. XAMPP als Web Schnittstelle.

Klingt nach einem schönen Projekt

 

[Romanticus]

MS SQL <-> Oracle. Wenn du vor hast die Daten aus den Quell-DBs mittels SQL-Abfragen auszulesen und in die Ziel-DB zu speichern macht es meiner Meinung nach keinen Unterschied ob es sich um die gleichen DBs handelt. Ein möglicher Schwachpunkt wäre vorstellbar, wenn die zu sammelnde Datenmenge stark anwächst, sodass die lesenden Zugriffe beim "ernten" der Daten die produktiven Datenbanken zu stark auslasten. Man darf auch die Konsistenz der Daten nicht außer Acht lassen - optimal wäre es, die produktven Datenbanken wärend des Prozesses des Sammelns offline zu nehmen. Wenn man sich das erlauben kann - dann spielt es keine Rolle ob die Ziel DB eine Oracle oder MSSQL ist. Wenn die Hochverfügbarkeit der DBs aber wichtig ist, und du dir nicht leisten kannst, sie für einige Minuten abzuklemmen, dann wäre eine homogene DB-Landschaft insoweit von Vorteil, dass du die DB-Files auf File-Ebene in die Struktur einer Staging Area (s.o.) kopierst und die Daten daraus sammelst. Wobei mir gerade auffält, dass nur die Staging Area auch eine MSSQL sein sollte, die Ziel-DB kann in diesem Fall auch Oracle sein. Files kopieren geht wesentlich schneller als etwa 100-150k SELECT-Abfragen (10MB - haut das etwa hin?) und die Quell-DBs müssen nur sehr kurz gelockt werden. Was ich nicht sicher weiß ist, wie strikt MSSQL mit low-level Files umgeht, ich hab gehört, dass einige DBs es merken, wenn man ihnen "fremde" DB-Files unterschieben will. MySQL z.B. ist in der Hinsicht sehr tolerant.

Zu Stored Procedures mit Oracle kann ich nur sagen, dass sie sehr mächtig sind. Ich selber habe aber nur im Rahmen einer DB-Vorlesung vor 2 Jahren mit Oracle gearbeitet, kann dir also nicht wirklich helfen.

 

[marbe]

Romanitcus hat absolut Recht Prozeduren sind sehr mächtig sowohl im MS als auch im Ora. Nur das hier zu erklären führt eindeutig zuweit. Finde ich