Datentransfer von Oracle zu Postgresql

[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?

[einfachTobi]

Nach einer kurzen Zeit kann man das nicht mehr bearbeiten. Ansonsten mit dem kleinen Bleistift-Icon oben rechts an deinem Post. Ich würde überschlägig mal davon ausgehen, dass du <= 1000 Writes/s bekommst. Damit dürfte das Vorhaben bei 12e6 Zeilen mindestens 3 1/3 h dauern.

[Sirius3]

@Ponsel: Du denkst, dass

Code: Alles auswählen

result = connection.execution_options(stream_results=True).execute(query,)

schon das Ergebnis liefert. Das ist aber nur ein Cursor, daher ist die Variable auch falsch benannt.
Erst das fetchall liest wirklich Daten aus der Datenbank, und das ist natürlich bei mehreren Millionen Datensätzen langsam.
Das hat, wie schon öfter geschrieben, nichts mit Pandas zu tun.