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
Datentransfer von Oracle zu Postgresql
- __blackjack__
- User
- Beiträge: 13079
- Registriert: Samstag 2. Juni 2018, 10:21
- Wohnort: 127.0.0.1
- Kontaktdaten:
Ich würde als erstes mal Pandas da rauslassen. Weiss gar nicht warum man das dazwischen schalten wollen würde.
„All religions are the same: religion is basically guilt, with different holidays.” — Cathy Ladman
-
- User
- Beiträge: 491
- Registriert: Mittwoch 13. November 2019, 08:38
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).
Ansonsten scheint das hier vielversprechend zu sein: https://github.com/darold/ora2pg (wenngleich kein Python).
Sehr guter Vorschlag. Wie genau geht das?__blackjack__ hat geschrieben: ↑Donnerstag 2. September 2021, 14:57 Ich würde als erstes mal Pandas da rauslassen. Weiss gar nicht warum man das dazwischen schalten wollen würde.
Wenn ich das richtig verstehe, werden dort Tabellen / Schemas kopiert. Ich versuche ja das Ergebnis einer Abfrage weiterzuleiten.einfachTobi hat geschrieben: ↑Donnerstag 2. September 2021, 15:12 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).
-
- User
- Beiträge: 491
- Registriert: Mittwoch 13. November 2019, 08:38
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).einfachTobi hat geschrieben: ↑Donnerstag 2. September 2021, 15:38 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.
(...)
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).
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?
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.
-
- User
- Beiträge: 491
- Registriert: Mittwoch 13. November 2019, 08:38
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.
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.
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.
Was das beste Vorgehen ist, hängt auch davon ab, welche Umgebung zur Verfügung steht.
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.
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.
Ich weiß nicht ob es im Rahmen dieser Diskussion sinnvoll ist mehrere Beiträge gleichzeitig in einer Antwort zu bearbeiten ich versuche es trotzdem.
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
ich schrieb:
ich schrieb__deets__ hat geschrieben: ↑Freitag 3. September 2021, 08:00 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.
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.Ponsel hat geschrieben: ↑Donnerstag 2. September 2021, 13:55 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.
Ü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.__deets__ hat geschrieben: ↑Freitag 3. September 2021, 08:00 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.
----------------------------------------------------------------------------------
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?einfachTobi hat geschrieben: ↑Freitag 3. September 2021, 07:57 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
----------------------------------------------------------------------------------
Das wäre natürlich das Sahnehäubchen. Welche Infos brauchst du zur Umgebung um mir helfen zu können?sparrow hat geschrieben: ↑Freitag 3. September 2021, 07:59 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.
Ich weiß nicht ob es im Rahmen dieser Diskussion sinnvoll ist mehrere Beiträge gleichzeitig in einer Antwort zu bearbeiten ich versuche es trotzdem.
#########################################################################
#########################################################################
ich schrieb:
ich schrieb__deets__ hat geschrieben: ↑Freitag 3. September 2021, 08:00 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.
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.Ponsel hat geschrieben: ↑Donnerstag 2. September 2021, 13:55 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.
Ü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.__deets__ hat geschrieben: ↑Freitag 3. September 2021, 08:00 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.
#########################################################################
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?einfachTobi hat geschrieben: ↑Freitag 3. September 2021, 07:57 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
#########################################################################
Das wäre natürlich das Sahnehäubchen. Welche Infos brauchst du zur Umgebung um mir helfen zu können?sparrow hat geschrieben: ↑Freitag 3. September 2021, 07:59 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.
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.
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.
-
- User
- Beiträge: 491
- Registriert: Mittwoch 13. November 2019, 08:38
Naiver, ungetesteter Ansatz:
Ob das nun wirklich performant ist, kann ich nicht beurteilen. Es wäre einen Test mit ein paar tausend Datensätzen wert.
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()
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.einfachTobi hat geschrieben: ↑Freitag 3. September 2021, 09:40 Naiver, ungetesteter Ansatz:Ob das nun wirklich performant ist, kann ich nicht beurteilen. Es wäre einen Test mit ein paar tausend Datensätzen wert.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()
p.S. Sorry für das Doppelposting. Kann man das irgendwie im Nachhinein bearbeiten / löschen?