update table in foreign database

Contributor

Hallo,

hier nochmal ein Frage zu der ich nichts in der Doku gefunden habe.

Ist es möglich über eine Connection (via OCI oder JDBC) in der verbundenen Datenbank ein "update" oder "delete" auszuführen. Bisher fand ich nur Infos zum Import und Export.

Hintergrund: Wir bauen eine permanente Synchronisation einer Oracle-Datenbank in die Exasol-DB auf, in dem wir per Trigger alle geänderten Tabellensätze mit ihren Primary Keys zunächst in einer Synchronisationstabelle protokollieren. Aus dieser Protokoll-Tabelle soll Exasol dann die geänderten Daten importieren. Jetzt müssen wir aber der Quell-Datenbank per konkretem Update oder Delete auf diese Sync-Tabelle mitteilen, welche Daten bereits übernommen wurden.

Zur Info: Die Sync-Tabelle in der Quell-DB sieht so aus:

Create Table CSB_PL_Sync_Queue
(
id NUMBER GENERATED ALWAYS AS IDENTITY,
sync_status int ,
sync_command varchar(20) ,
sync_table_name varchar(100) ,
sync_where_clause varchar(400) ,
datetime_created date default sysdate ,
datetime_updated date,
user_created char(20) default user
)

Konkret wollen wir also das Feld Sync-Status updaten von 0 auf 1, alternativ den Datensatz einfach löschen, wenn der Import der entsprechenden Zeile in Exasol erfolgreich war.

Vielen Dank für Ihre Antwort.

1 ACCEPTED SOLUTION

Xpert

Hallo,

um die Frage noch etwas direkter zu beantworten: meines Wissens geht es zumindest nicht direkt ( ein exa-* möge mich korrigieren falls ich hier die Unwahrheit verbreite ),
da Connection-Objekte im Context IMPORT und EXPORT "beheimatet" sind.

Wenn man es umbedingt auf der Ebene eines SQL-Statements aus EXA heraus tun muss, wäre das folgende möglich, wenn auch nicht unbedingt zu empfehlen:

export (select <your-table-conform-colum-list> from dual where 0=1)
INTO ORA AT <your-connection-here> TABLE <your-table-here> CREATED BY 'update CSB_PL_Sync_Queue set Sync-Status=1';

Es lohnt sich zu wissen, dass sowas funktioniert aber für einen ständigen Anwendungsfall wie hier beschrieben würde ich den Vorschlag von littlekoi
definitiv bevorzugen.

View solution in original post

9 REPLIES 9

Team Exasol
Team Exasol

Ich empfehle in so einem Fall auch eher eine externe Instanz die Synchronisation kontrollieren zu lassen.

Das entscheidende Architekturprinzip dahinter ist Separation of Concerns. Wenn eine externe Instanz die Synchronisation kontrolliert und veranläßt, dann sind Exasol und Oracle nicht starr gekoppelt. Im Gegenteil, sie müssen im besten Fall von einander nichts wissen. Das macht die Wartung einfacher und erlaubt sogar, Produkte auszutauschen, sollte das mal notwendig werden.

Außerdem ist es besser für die Verfügbarkeit. Jede Datenbank kann sich auf ihre eigene Konsistenz konzentrieren. Die Konsistenz zwischen den synchronisierten Datenbanken verantwortet die externe Instanz. Fällt die aus, ist immerhin noch jede Datenbank in sich selbst konsistent.

Wenn man Synchronisation aufsetzt, muss man immer auch den Desaster-Recovery-Fall mitdenken. Rein inkrementelle Mechanismen sind nicht gut aufgestellt für Desaster-Recovery. Erstens dauert das bei großen Datenmengen sehr lange, zweitens hat man immer die Gefahr von Folgefehlern.

Wenn's schnell gehen soll, kann man auch externe Sync-Kontrolle mit IMPORT-Kommandos verbinden.

Contributor

Hallo SebastianB,

leider verstehe ich den Ansatz mit den ETL-Jobs für die kontinuierliche Synchronisation noch nicht. Bei Nutzung der Import-Funktion von Exasol (was ja wohl die Exasol ETL-Vorgehensweise ist), basierend auf dem Script zur Übernahme von Oracle-Datenbanken (https://github.com/exasol/database-migration/blob/master/oracle_to_exasol.sql) entsteht doch noch meinem Verständnis kein konsistenter Gesamt-Snapshot der Oracle-DB, da ja die Daten sequentiell ausgelesen werden und nicht aus einem Transaktionslog. Zudem soll in unserem Projekt ja erreicht werden, dass die Datenbank-Kopie in Exasol zeitlich möglichst nah am Original ist und nicht z.B. nächtlich komplett übernommen werden soll. Dazu müssen also auch in der Oracle-DB gelöschte Datensätze auch in Exasol gelöscht werden. Das kann ich mir aktuell nur so vorstellen, dass die Deletes über Trigger protokolliert werden um sie dann in Exasol nachzuziehen. Wie kann das sonst über Exasol-ETL-Logik gesteuert werden?

Aktuell haben wir die Lösung soweit stehen, dass wir initial und automatisch komplett über Exasol-Imports (siehe Github-Script) importieren, ab dann alle Änderungen (Inserts, Updates und Deletes) über Trigger in einer Sync-Queue-Tabelle protokollieren lassen und über eine Exasol-Sync-Script (selbst geschrieben, incl. des Rück-Updates in die Sync-Queue, was übernommen wurde) diese Änderungen in der Exasol-DB nachziehen.

Gibt es besser Lösungansätze? Was genau ist gemeint mit "eine externe Instanz soll die Synchronisation kontrollieren"?

Wenn Oracle selbst eine Hot-Standby-Datenbank aktuell halten muss, verwenden die dazu meines Wissens die Oracle-Transaktionslogs und zieht diese (also die darin chronologisch festgehalten transaktionssicheren Änderungen) in der DB-Spiegelung nach. Das ist aber eine Lösung die halt stark datenbankgebunden ist. Wir wollen aber auf eine Lösung setzen, die im Wesentlichen DB-neutral ist. Und Trigger und SQL-Statements zur Synchronisation absetzen, kann vermutlich nahezu jede DB. Wir brauchen auch keine großen Transformationen von Daten oder viele andere Datenquellen, da unsere Produktiv-DB schon alle Daten in guter Struktur enthält. Wir wollen halt für die Analysen nicht die Produktiv-DB belasten und zudem die beste DB für Analyse-Aufgaben auswählen.

Dennoch sind wir offen für andere Ansätze und prüfen diese gerne.

Freue mich auf Feedback.

Team Exasol
Team Exasol

Hi MaMerker,

verstanden, das Ziel ist zeitlich möglichst nahe an der Originaldatenbank dran sein, ohne große Umformungen -- mit Exasol als Beschleuniger.

Mein üblicher Favorit, passt nicht auf in Ihrem Fall mit der Voraussetzung, dass die Verzögerung möglichst kurz sein. Das wäre dort, wo das Propagation Delay höher sein darf, ein externer Prozess, ausprogrammiert und von einem Scheduler getriggert, der an Hand von Kriterien wie Sequenzen, eindeutigen Zeitstempeln oder Hashes Deltas ermittelt und einspielt. Das ist unabhängig von der Datenbanktechnik und die Datenbanken müssen sich nicht kennen - abgesehen vielleicht von einem Connection-Objekt auf einer Seite. Ist das Delta ermittelt, weißt der Prozess die Zieldatenbank an, die letzten Änderungen zu holen (gibt dabei die Statements vor) und zu mergen. Mein Faible für die Lösung ist vermutlich auch der Tatsache geschuldet, dass ich Software-Entwickler bin.

Zeitliche Nähe ist immer eine Extra-Herausforderung. Je enger man in so einem Fall die Intervalle für Updates macht, umso größer wird der Overhead für die Diff-Ermittlung und umso kleiner werden die Bulk-Updates. Beides teuer.

Intra-Oracle gibt es dafür, wie von Ihnen schon erwähnt, ereignisgesteuerte Mechanismen. Ich erinnere mich noch dunkel, dass in einem ähnlich gelagerten Fall zwischen einer Oracle OLTP und einem DWH Oracle Streams zum Einsatz kamen, um die Daten möglichst aktuell im DWH zu halten. Ich war in dem Fall nur Zaungast, aber das funktionierte ganz ordentlich.

Den gleichen Luxus hat man datenbankübergreifend leider in der Tat nicht, zumindest ist mir da leider nichts akut bekannt. Ich habe mir selbst mal aufgeschrieben, dass ich in einer ruhigen Minute recherchiere, ob es datenbankübergreifende offene Standards für ereignisgesteuerte Synchronisation gibt. Das Problem ist ja weit verbreitet, es würde mich also nicht überraschen, wenn schon jemand versucht hätte, das zu standardisieren.

Sie haben erwähnt, dass bereits eine komplette Initialbeladung existiert, die vor dem inkrementellen Teil greift. Die kann man dann auch für das Desasterr-Recovery verweden, sollte das mal notwendig werden. Damit ist eine meiner größten Sorgen in Ihrem Fall schon mal abgehakt. Fällt die Kopie aus, läuft einfach die Initialbeladung nochmal - diesmal eben mit mehr Daten.

Ihre Trigger-Lösung hat den Charme, dass sich damit die Kosten für die Diff-Ermittlung sparen lassen. IMPORT muss dann konsequenterweise in engen Intervallen laufen, damit man den Vorteil kurzer Verzögerung auch mit nimmt. Um den Kompromiss kleinerer Bulks kommt man dann nicht herum.

Aus Neugier: wie zeitlich nah hinter dem Original müssen die Daten in Ihrem Fall eigentlich sein?

Contributor

Hallo SebastianB,

wir haben aktuell einen Exasol-Prozess der alle 2 Sekunden nachschaut, ob die Trigger neue Einträge generiert haben, falls ja, werden diese dann importiert. Das dauert dann eben solange, wie es dauert. Dann wartet der Prozess wieder 2 Sekunden und das Spiel beginnt von Vorne. 

Wir wollen das so zeitnah wie möglich haben, da wir in der Web-Anwendung (ERP-System) mit eingebetteten YellowFin-Analysen arbeiten wollen. Arbeitet man also z.B. im CRM-Modul oder auch nur in der Kundenstammdatenverwaltung, so sollen halt die aktuellen Umsatzzahlen und KPIs mit Drill-Downs zugänglich sein. Diese YellowFin-Analysen sollen dann ausschließlich die Daten aus der Analyse-DB (hier Exasol) verwenden und nicht auf die Produktiv-DB zugreifen.

 

In Kürze zusammengefasst:
Wir generieren automatisch Trigger (insert, update, delete) die jede Datensatzänderung der Produktiv-DB in einer Synchronisationstabelle in der Produktiv-DB notieren.

Die Sync-Tabelle sieht dann gefüllt z.B. so aus:

MaMerker_0-1590561377869.png

Dann haben wir ein Exasol-Script gebaut, das die zugehörigen Daten in Exasol übernimmt und das im Sync-Status notiert (kann bei Bedarf gerne zugesendet werden):

MaMerker_1-1590561585967.png

… und starten dies alle 2 Sekunden aus einer Batch-Datei heraus:

:Loop
"C:\Program Files (x86)\Exasol\EXASolution-6.2\EXAplus\exaplusx64.exe" -c 10.101.22.2:8563 -u sys -p exasol -sql "EXECUTE SCRIPT ORAB3ATSAND.sync_from_synctable ('OCI_ORACLE', 'ORAB3ATSAND') with output;"
ping localhost -n 2 > nul
goto Loop

Soweit so gut. Das funktioniert auch. Und die Exasol-DB passt sich permanent automatisch an die Produktiv-DB an, incl. aller Änderungen, eben auch Deletes.

Tests zeigen nun aber, dass bei z.B. einer gleichzeitigen Änderung von 1000 Daten per Update ja erwartungsgemäß 1000 Einträge in der Sync-Tabelle entstehen.
Das Exasol-Script erkennt diese mittels Resultset und arbeitet sie gesammelt ab. Dabei wird aber für jeden der 1000 Datensätze offenbar die Verbindung zur Oracle-Produktiv-DB aufgebaut (via OCI_ORACLE). Und nochmal 1000 mal für das Update (via OCI_ORACLE) in der Sync-Tabelle (sync-status von 0 auf 1 als Kennzeichnung der Erledigung, könnte man auch löschen).
Da jeder Connect aber ca. 500 ms dauert, sind das bei 1000 Datensätzen und 2000 Connects aber immerhin ca 30 min. Ich denke >90% der Zeit sind den Connections geschuldet, nicht dem Datentransfer.

Habt ihr noch eine Idee, was man verbessern kann?

Konkrete Frage: Kann ich in einem Exasol-Script eine Oracle-Connection öffnen, offen halten und verwenden und erst am Ende wieder schließen?

P.S.: Diese Infos gingen am Dienstag auch an Andreas Scheel, da ich mit ihm letztes Jahr schon mal über eine Trigger-basierte Lösung mich ausgetauscht hatte.

 

Erneut Dank für Feedback ;)

 

Team Exasol
Team Exasol

Sorry, dass ich mich jetzt erst wieder melde. Im Moment gibt's reichlich zu tun - aber wem geht das anders?

2 Sekunden-Polling ist allerdings sportlich. Ich erinnere mich noch gut daran, das bei einem großen Automobilhesteller die IT den Fachbereich fast gelyncht hätte, weil die im 3-Minuten-Takt gepollt hatten. :)

Instinktiv würde ich auf der Sync-Tabelle keine Updates machen, sondern nur anhängen (also INSERT) und dann über einen Index auf den Zeitstempel den letzten Status berücksichtigen. Das hat zum einen den Vorteil, dass einem dann die Informationen zum Debuggen erhalten beleiben und zum anderen ist das evtl. auf Oracle Lock-freundlicher. Käme auf einen Versuch an.

Bei der Gelegentheit würde ich die Tabelle auch gleich noch nach Datum partitionieren, damit das Aufräumen ggf. schneller geht. Einfach alte Einträge partitionsweise löschen. In alle Queries muss dann natürlich immer das Partitionskriterium mit rein.

Das wäre mal die Oracle-Seite.

@mwellbro hat Recht, was das Thema Bulkverarbeitung angeht. 1000 Einträge im Skript zusammen zu fassen ist billig. Das lohnt sich auf jeden Fall. Größere Bulks sind in aller Regel besser.

Noch eine kleine Bitte am Rande: Code bitte immer als Text posten. Ich kann trozt Brille den Code nur schwer lesen und manchmal ist es auch hilfreich, wenn man ein Snippet in die IDE der Wahl kippen kann.

Xpert

Hallo MaMerker,

mir wäre zumindest nicht bekannt das man eine Connection offen lassen oder eine Art Pooling betreiben könnte - was man innerhalb eures Scripts eventuell machen könnte wäre die FOR-Schleife zu spalten und sich die erfolgreichen sync_ids lokal im script speichern um sie dann gesammlt in Richtung Oracle zu "quittieren" - das dürfte zumindest einige Roundtrips einsparen.
Könnte ein wenig SQL-Kosmetik erfordern, weil eine IN-clause mit > 1000 Einträgen ist vllt. auch nicht das Tollste, aber wenn eure sync_ids einfach hochgezählte Nummern sind wären Range-Bildungen möglich, unter Ausschluss derer die im oberen sync Schritt fehlschlagen.

 

Xpert

Hallo,

um die Frage noch etwas direkter zu beantworten: meines Wissens geht es zumindest nicht direkt ( ein exa-* möge mich korrigieren falls ich hier die Unwahrheit verbreite ),
da Connection-Objekte im Context IMPORT und EXPORT "beheimatet" sind.

Wenn man es umbedingt auf der Ebene eines SQL-Statements aus EXA heraus tun muss, wäre das folgende möglich, wenn auch nicht unbedingt zu empfehlen:

export (select <your-table-conform-colum-list> from dual where 0=1)
INTO ORA AT <your-connection-here> TABLE <your-table-here> CREATED BY 'update CSB_PL_Sync_Queue set Sync-Status=1';

Es lohnt sich zu wissen, dass sowas funktioniert aber für einen ständigen Anwendungsfall wie hier beschrieben würde ich den Vorschlag von littlekoi
definitiv bevorzugen.

View solution in original post

Contributor

So geht es:

export (select 0 as id from dual where 0=1) INTO ORA AT OCI_ORACLE STATEMENT 'select id from "ORAB3ATSAND"."CSB_PL_SYNC_QUEUE" where id = 4' CREATED BY 'update CSB_PL_Sync_Queue set Sync_Status=1 where id = 4';

 

Vielen Dank.

Xpert

I think it's better to go other way and implement a simple ETL script, which is going to:

1) Check if something needs to be imported / synced.
2) Run the actual IMPORT.
3) Set "sync-status" in external database.

Running a simple query in Oracle is very cheap. Running a simple query in Exasol is quite expensive due to all the multi-node syncronisation, distributed transaction management, etc. And the Exasol ends up doing nothing but connecting to external database, running query and waiting.

It's not a problem if you have 1-5 parallel loads. But it might be a problem if you'll have 20+ parallel imports, and if you'll ever experience any slowdowns / locks / errors in Oracle.