Datentransfer von Oracle zu Postgresql

[Ponsel]

Hallo,

ich versuche die Daten aus einer SQL-Abfrage auf eine Oracle-Datenbank mit Hilfe von Python in eine Postgresql-Datenbank zu transferieren. Da ich noch ein ziemlicher Neueinsteiger bin, habe ich es mit Hilfe eines Pandas-Dataframes und sqlalchemy versucht. Für kleinere Datenmengen funktioniert das auch recht gut.

Mein Datensatz beinhaltet 12 Mio Zeilen.
Die reine Abfrage kommt in der selben Zeit zurück die sie auch auf der Datenbank benötigen würde:
result = connection.execution_options(stream_results=True).execute(query,)

Wenn ich dann aber das Ergebnis in ein Dataframe schreibe, dann dauert das so lange, dass ich bisher immer nach 30 Minuten abgebrochen habe.
df = pd.DataFrame(result.fetchall())

Offensichtlich hat Pandas ein Problem wenn die Anzahl der Zeilen größer wird.

Meine Frage in die Runde. Gibt es eine (performante) Möglichkeit Daten aus einer Oracle-Sql-Abfrage in eine Postgres-Tabelle zu schreiben?
Über eine Antwort und ein kleines Codebeispiel würde ich mich sehr freuen.

Viele Grüße
Ponsel

[__blackjack__]

Ich würde als erstes mal Pandas da rauslassen. Weiss gar nicht warum man das dazwischen schalten wollen würde.

[einfachTobi]

Warum der Zwischenschritt über ein DataFrame? Schreibe die gelesenen Daten doch direkt wieder in die neue Datenbank.
Ansonsten scheint das hier vielversprechend zu sein: https://github.com/darold/ora2pg (wenngleich kein Python).

[Ponsel]

Ich würde als erstes mal Pandas da rauslassen. Weiss gar nicht warum man das dazwischen schalten wollen würde.

Sehr guter Vorschlag. Wie genau geht das?

[Ponsel]

Warum der Zwischenschritt über ein DataFrame? Schreibe die gelesenen Daten doch direkt wieder in die neue Datenbank.
Ansonsten scheint das hier vielversprechend zu sein: https://github.com/darold/ora2pg (wenngleich kein Python).

Wenn ich das richtig verstehe, werden dort Tabellen / Schemas kopiert. Ich versuche ja das Ergebnis einer Abfrage weiterzuleiten.

[einfachTobi]

Aus der Readme:

Features included:

- Export full database schema (tables, views, sequences, indexes), with
unique, primary, foreign key and check constraints.
(...)
- Export full data or following a WHERE clause.
(...)

[Ponsel]

Aus der Readme:

Features included:

- Export full database schema (tables, views, sequences, indexes), with
unique, primary, foreign key and check constraints.
(...)
- Export full data or following a WHERE clause.
(...)

Es tut mir leid, aber das hilft mir nicht weiter. Was du da vorschlägst scheint eine Extension zu sein die man in Postgresql installieren muss. Das setzt voraus, das ich einen administrativen Zugang zum DB-Server selber (um die Extension selbst, sowie einen Oracle-Client zu installieren) und Zugang zur Datenbank (für die Aktivierung der Extension) habe. Ich bin hier froh, wenn ich innerhalb eines mir zugewiesenen Schemas Tabellen und Views anlegen darf (schon bei Sequenzen hört es auf).

Ich muss das Ganze also irgendwie durch Python leiten (und damit stümper ich seit ca. 4 Wochen herum).
Ein Link zu einer guten Anleitung würde mir reichen (aber ich habe noch nicht die richtigen Schlagworte gefunden um das zu googeln).

[__deets__]

Deine Wahrnehmung ist falsch. Das ist keine Erweiterung. Das arbeitet ganz normal mit einer DB Verbindung. So wie Python auch. Mit dem psql client werden die erzeugten INSERT-Klauseln einfach reingepumpt.

[Sirius3]

Ich würde auch sagen, dass es am einfachsten wäre aus Oracle einen SQL-Dump rauszuziehen und die mit psql nach Postgreq einspielen.
Und Schreiben ist sehr viel langsamer als Lesen.

[Ponsel]

Ich würde auch sagen, dass es am einfachsten wäre aus Oracle einen SQL-Dump rauszuziehen und die mit psql nach Postgreq einspielen.
Und Schreiben ist sehr viel langsamer als Lesen.

Und wie mache ich das mit Hilfe von Python?
Den Dump muss ich ja irgendwie zwischenspeichern (und das in einem Pandas Dataframe zu machen scheint ... ähm ... ungünstig zu sein).

Oder kann man die Daten aus der Abfrage irgendwie direkt nach Oracle umleiten?

[__deets__]

Wieso Python? Das von einfachTobi benannte Tool ist in Perl geschrieben, aber das ist doch egal.

[Ponsel]

Wieso Python? Das von einfachTobi benannte Tool ist in Perl geschrieben, aber das ist doch egal.

Ich habe hier kein Pearl und keine administrativen Rechte um es mir zu installieren (geschweige denn auf dem Scheduling-Server auf dem das am Ende laufen soll). Und ich habe bisher noch keine Erfahrung mit Pearl um das zu reverse engeneeren. Auch in Python habe ich bestenfalls Anfängerskills. Ich komme ursprünglich aus der java / c# -Ecke. Und nein ich darf das nicht umschreiben, denn die Anpassung die ich durchführen möchte, ist Teil eines größeren Python-Projektes.

[einfachTobi]

Ok. Ich dachte es ging allein um die Migration, statt darum es zwingend in Python umzusetzen. Wobei ich nicht ganz verstehe, warum es möglich ist Python Programme auszuführen, aber nicht möglich sein soll Perl Programme auszuführen.
Da würde ich vorschlagen:
- Verbindung zu Oracle aufbauen
- Daten abfragen
- Verbindung schließen
- Verbindung zu Postgres aufbauen
- Abgefragte Daten hinein schreiben
- Verbindung schließen

Dazu kannst du das Iterable, was dir die Oracle liefert, vermutlich direkt als Parametersatz für die Postgres-Verbindung nutzen. psycopg2 bietet für schnelles Ausführen extra execute_batch.

[sparrow]

Alternativ kann es auch sinnvoll sein, die Verbindungen offen zu halten und die Daten mit einer zu ermittelnden Batchsize zu lesen und dann wegzuschreiben, bevor man sich über die selben Verbindung die nächsten Daten holt.
Was das beste Vorgehen ist, hängt auch davon ab, welche Umgebung zur Verfügung steht.

[__deets__]

Immer diese endlosen Randbedingungen, die man so peu a peu erfährt…

Ich würde erstmal deine Hypothese in Frage stellen. Nur weil du Pandas benutzt, und bei großen Daten alles langsam wurde, ist das nicht Pandas Schuld. Sondern im Zweifel unvermeidbar.

Um dem auf die Spur zu kommen, muss man Experimente machen. Zb einen DataFrame mit n Zeilen (sollten schon so viele sein, dass es eine Weile dauert) als CSV Datei abspeichern. Und diese Datei dann mal direkt mit psycopg2 oder welchen Adapter auch immer du hast “von Hand” in die Datenbank ein pflegen. Wenn das signifikant schneller ist, dann lohnt es sich, das zu generalisieren. Wenn nicht, dann brauchst du halt einfach mehr Geduld. Bei 12000000 Zeilen und einer halben Stunde Wartezeit reden wir über 6.700 Zeilen pro Sekunde. Das ist unrealistisch, das das so schnell geht.

Du kannst auch einfach mal ein paar tausend INSERTs an Testdaten ein pflegen, und schauen, was die DB schafft. Damit hast du eine untere Grenze dafür, wie lange das dauern sollte. Und so lange (oder besser doppelt so lang) musst du eben warten.

[Ponsel]

Ich weiß nicht ob es im Rahmen dieser Diskussion sinnvoll ist mehrere Beiträge gleichzeitig in einer Antwort zu bearbeiten ich versuche es trotzdem.

Immer diese endlosen Randbedingungen, die man so peu a peu erfährt…

ich schrieb:

ich versuche die Daten aus einer SQL-Abfrage auf eine Oracle-Datenbank mit Hilfe von Python in eine Postgresql-Datenbank zu transferieren.
<snip>
Mein Datensatz beinhaltet 12 Mio Zeilen.

Ich würde erstmal deine Hypothese in Frage stellen. Nur weil du Pandas benutzt, und bei großen Daten alles langsam wurde, ist das nicht Pandas Schuld. Sondern im Zweifel unvermeidbar.
Um dem auf die Spur zu kommen, muss man Experimente machen.

ich schrieb

Die reine Abfrage kommt in der selben Zeit zurück die sie auch auf der Datenbank benötigen würde:
result = connection.execution_options(stream_results=True).execute(query,)

Wenn ich dann aber das Ergebnis in ein Dataframe schreibe, dann dauert das so lange, dass ich bisher immer nach 30 Minuten abgebrochen habe.
df = pd.DataFrame(result.fetchall())

Offensichtlich hat Pandas ein Problem wenn die Anzahl der Zeilen größer wird.

in dem Experiment (falls ich es nicht falsch designt habe) habe ich festgestellt das das Lesen der Abfrageergebnisse in die Variable "result" den zu erwartenden Zeitraum in Anspruch nimmt. Und sich der Flaschenhals beim Transfer dieser Daten in ein Dataframe ergibt.

Du kannst auch einfach mal ein paar tausend INSERTs an Testdaten ein pflegen, und schauen, was die DB schafft. Damit hast du eine untere Grenze dafür, wie lange das dauern sollte. Und so lange (oder besser doppelt so lang) musst du eben warten.

Über die Schreibgeschwindigkeit habe ich bisher noch keine verlässlichen Informationen. Erfahrungsgemäß hast du natürlich vollkommen recht. Schreiben ist langsamer als lesen. Dieses Thema hatte ich bisher nicht angesprochen.

----------------------------------------------------------------------------------

Da würde ich vorschlagen:
- Verbindung zu Oracle aufbauen
- Daten abfragen
- Verbindung schließen
- Verbindung zu Postgres aufbauen
- Abgefragte Daten hinein schreiben
- Verbindung schließen

Könntest du mir eine Idee geben wie ich das machen kann? Du bewegst dich ja hier noch auf einem relativ hohem Abstraktionslevel. Mich würde interessieren wie das in Code aussieht?

----------------------------------------------------------------------------------

Alternativ kann es auch sinnvoll sein, die Verbindungen offen zu halten und die Daten mit einer zu ermittelnden Batchsize zu lesen und dann wegzuschreiben, bevor man sich über die selben Verbindung die nächsten Daten holt.
Was das beste Vorgehen ist, hängt auch davon ab, welche Umgebung zur Verfügung steht.

Das wäre natürlich das Sahnehäubchen. Welche Infos brauchst du zur Umgebung um mir helfen zu können?

[Ponsel]

Ich weiß nicht ob es im Rahmen dieser Diskussion sinnvoll ist mehrere Beiträge gleichzeitig in einer Antwort zu bearbeiten ich versuche es trotzdem.

Immer diese endlosen Randbedingungen, die man so peu a peu erfährt…

ich schrieb:

ich versuche die Daten aus einer SQL-Abfrage auf eine Oracle-Datenbank mit Hilfe von Python in eine Postgresql-Datenbank zu transferieren.
<snip>
Mein Datensatz beinhaltet 12 Mio Zeilen.

Ich würde erstmal deine Hypothese in Frage stellen. Nur weil du Pandas benutzt, und bei großen Daten alles langsam wurde, ist das nicht Pandas Schuld. Sondern im Zweifel unvermeidbar.
Um dem auf die Spur zu kommen, muss man Experimente machen.

ich schrieb

Die reine Abfrage kommt in der selben Zeit zurück die sie auch auf der Datenbank benötigen würde:
result = connection.execution_options(stream_results=True).execute(query,)

Wenn ich dann aber das Ergebnis in ein Dataframe schreibe, dann dauert das so lange, dass ich bisher immer nach 30 Minuten abgebrochen habe.
df = pd.DataFrame(result.fetchall())

Offensichtlich hat Pandas ein Problem wenn die Anzahl der Zeilen größer wird.

in dem Experiment (falls ich es nicht falsch designt habe) habe ich festgestellt das das Lesen der Abfrageergebnisse in die Variable "result" den zu erwartenden Zeitraum in Anspruch nimmt. Und sich der Flaschenhals beim Transfer dieser Daten in ein Dataframe ergibt.

Du kannst auch einfach mal ein paar tausend INSERTs an Testdaten ein pflegen, und schauen, was die DB schafft. Damit hast du eine untere Grenze dafür, wie lange das dauern sollte. Und so lange (oder besser doppelt so lang) musst du eben warten.

Über die Schreibgeschwindigkeit habe ich bisher noch keine verlässlichen Informationen. Erfahrungsgemäß hast du natürlich vollkommen recht. Schreiben ist langsamer als lesen. Dieses Thema hatte ich bisher nicht angesprochen.

#########################################################################

Da würde ich vorschlagen:
- Verbindung zu Oracle aufbauen
- Daten abfragen
- Verbindung schließen
- Verbindung zu Postgres aufbauen
- Abgefragte Daten hinein schreiben
- Verbindung schließen

Könntest du mir eine Idee geben wie ich das machen kann? Du bewegst dich ja hier noch auf einem relativ hohem Abstraktionslevel. Mich würde interessieren wie das in Code aussieht?

#########################################################################

Alternativ kann es auch sinnvoll sein, die Verbindungen offen zu halten und die Daten mit einer zu ermittelnden Batchsize zu lesen und dann wegzuschreiben, bevor man sich über die selben Verbindung die nächsten Daten holt.
Was das beste Vorgehen ist, hängt auch davon ab, welche Umgebung zur Verfügung steht.

Das wäre natürlich das Sahnehäubchen. Welche Infos brauchst du zur Umgebung um mir helfen zu können?

[__deets__]

Mea culpa. Das die Probleme schon beim einlesen auftreten, ist mir entgangen.

Bezüglich des -losen Vorgehens: du hast doch schon ein fetchall. Über das kannst du iterieren, und einfach für jedes, oder jeweils n Zeilen, die Daten mit einer zweiten Verbindung in dienDB schreiben.

[einfachTobi]

Naiver, ungetesteter Ansatz:

Code: Alles auswählen

import cx_Oracle
import psycopg2

oracle_connection = cx_Oracle.connect(user="user", password="swordfish", dsn="localhost/foo")
oracle_cursor = connection.cursor()
with psycopg2.connect("dbname=test user=postgres") as postgres_connection:
    postgres_cursor = postgres_connection.cursor()
    psycopg2.extras.execute_batch(postgres_cursor, "INSERT INTO test (num, data) VALUES (%s, %s)", oracle_cursor.execute("SELECT thing, other_thing FROM foo_bar WHERE thing = 'super'").fetchall())
oracle_connection.close()

Ob das nun wirklich performant ist, kann ich nicht beurteilen. Es wäre einen Test mit ein paar tausend Datensätzen wert.

[Ponsel]

Naiver, ungetesteter Ansatz:

Code: Alles auswählen

import cx_Oracle
import psycopg2

oracle_connection = cx_Oracle.connect(user="user", password="swordfish", dsn="localhost/foo")
oracle_cursor = connection.cursor()
with psycopg2.connect("dbname=test user=postgres") as postgres_connection:
    postgres_cursor = postgres_connection.cursor()
    psycopg2.extras.execute_batch(postgres_cursor, "INSERT INTO test (num, data) VALUES (%s, %s)", oracle_cursor.execute("SELECT thing, other_thing FROM foo_bar WHERE thing = 'super'").fetchall())
oracle_connection.close()

Ob das nun wirklich performant ist, kann ich nicht beurteilen. Es wäre einen Test mit ein paar tausend Datensätzen wert.

Das werde ich testen (vermutlich wird das etwas dauern, weil ich mich erst mal mit der Syntax auseinandersetzen muss) und werde euch dann entsprechend eine Statusmeldung geben.


p.S. Sorry für das Doppelposting. Kann man das irgendwie im Nachhinein bearbeiten / löschen?