webentwicklung-frage-antwort-db.com.de

Wie schreibe ich Daten in Redshift, die das Ergebnis eines in Python erstellten Datenrahmens sind?

Ich habe einen Datenrahmen in Python. Kann ich diese Daten als neue Tabelle in Redshift schreiben? Ich habe erfolgreich eine Db-Verbindung zu Redshift erstellt und kann einfache SQL-Abfragen ausführen. Jetzt muss ich einen Datenrahmen darauf schreiben. 

12
Sahil

Sie können to_sql verwenden, um Daten in eine Redshift-Datenbank zu übertragen. Ich war dazu in der Lage, eine Verbindung zu meiner Datenbank über eine SQLAlchemy-Engine herzustellen. Stellen Sie einfach sicher, dass Sie index = False in Ihrem to_sql-Aufruf einstellen. Die Tabelle wird erstellt, wenn sie nicht vorhanden ist. Sie können angeben, ob Sie die Tabelle ersetzen möchten, an die Tabelle anhängen oder fehlschlagen möchten, wenn die Tabelle bereits vorhanden ist.

from sqlalchemy import create_engine
import pandas as pd

conn = create_engine('postgresql://username:[email protected]:5439/yourdatabase')

df = pd.DataFrame([{'A': 'foo', 'B': 'green', 'C': 11},{'A':'bar', 'B':'blue', 'C': 20}])

df.to_sql('your_table', conn, index=False, if_exists='replace')

Beachten Sie, dass Sie möglicherweise pip install psycopg2 benötigen, um sich über SQLAlchemy mit Redshift zu verbinden.

to_sql Dokumentation

23
Andrew
import pandas_redshift as pr

pr.connect_to_redshift(dbname = <dbname>,
                        Host = <Host>,
                        port = <port>,
                        user = <user>,
                        password = <password>)

pr.connect_to_s3(aws_access_key_id = <aws_access_key_id>,
                aws_secret_access_key = <aws_secret_access_key>,
                bucket = <bucket>,
                subdirectory = <subdirectory>)

# Write the DataFrame to S3 and then to redshift
pr.pandas_to_redshift(data_frame = data_frame,
                        redshift_table_name = 'gawronski.nba_shots_log')

Details: https://github.com/agawronski/pandas_redshift

4
AidanGawronski

Vorausgesetzt, Sie haben Zugriff auf S3, sollte dieser Ansatz funktionieren:

Schritt 1: Schreiben Sie den DataFrame als csv in S3 (ich verwende dazu AWS SDK boto3)
Schritt 2: Sie kennen die Spalten, Datentypen und den Schlüssel/Index für Ihre Redshift-Tabelle von Ihrem DataFrame. Sie sollten daher in der Lage sein, ein create table-Skript zu generieren, und drücken Sie auf Redshift, um eine leere Tabelle zu erstellen
Schritt 3: Senden Sie einen copy-Befehl von Ihrer Python-Umgebung an Redshift, um Daten aus S3 in die in Schritt 2 erstellte leere Tabelle zu kopieren

Funktioniert jedes Mal wie ein Zauber.

Schritt 4: Bevor Ihre Cloud-Speicherleute anfangen zu schreien, löschen Sie die CSV aus S3

Wenn Sie sehen, dass Sie dies mehrmals tun, wird es durch das Einwickeln aller vier Schritte in eine Funktion aufgeräumt.

4
BigPanda

Ich habe versucht, Pandas df.to_sql() zu verwenden, aber es war enorm langsam. Es dauerte gut 10 Minuten, um 50 Reihen einzufügen. Siehe diese offene Ausgabe (zum Schreiben)

Ich habe versucht, odo aus dem Blaze-Ökosystem (gemäß den Empfehlungen in der Themendiskussion) zu verwenden, habe aber eine ProgrammingError gesehen, die ich nicht untersucht habe.

Endlich was funktioniert:

import psycopg2

# Fill in the blanks for the conn object
conn = psycopg2.connect(user = 'user',
                              password = 'password',
                              Host = 'Host',
                              dbname = 'db',
                              port = 666)
cursor = conn.cursor()

args_str = b','.join(cursor.mogrify("(%s,%s,...)", x) for x in Tuple(map(Tuple,np_data)))
cursor.execute("insert into table (a,b,...) VALUES "+args_str.decode("utf-8"))

cursor.close()
flexprobi_conn.commit()
flexprobi_conn.close()

Ja, einfach alter psycopg2. Dies ist für ein numpy-Array, aber die Konvertierung von einer df in eine ndarray sollte nicht zu schwierig sein. Das gab mir rund 3k Reihen/Minute.

Gemäß den Empfehlungen anderer Teamkollegen ist es jedoch die schnellste Lösung, den Befehl COPY zu verwenden, nachdem der Datenrahmen als TSV/CSV in einen S3-Cluster übertragen und anschließend kopiert wurde. Sie sollten dies untersuchen, wenn Sie wirklich riesige Datensätze kopieren. (Ich werde hier aktualisieren, wenn und wenn ich es probiere)

4
Gaurav

Früher habe ich mich auf die Funktion to_sql() der Pandas verlassen, aber sie ist einfach zu langsam. Ich habe vor kurzem folgendes getan:

import pandas as pd
import s3fs # great module which allows you to read/write to s3 easily
import sqlalchemy

df = pd.DataFrame([{'A': 'foo', 'B': 'green', 'C': 11},{'A':'bar', 'B':'blue', 'C': 20}])

s3 = s3fs.S3FileSystem(anon=False)
filename = 'my_s3_bucket_name/file.csv'
with s3.open(filename, 'w') as f:
    df.to_csv(f, index=False, header=False)

con = sqlalchemy.create_engine('postgresql://username:[email protected]:5439/yourdatabase')
# make sure the schema for mytable exists

# if you need to delete the table but not the schema leave DELETE mytable
# if you want to only append, I think just removing the DELETE mytable would work

con.execute("""
    DELETE mytable;
    COPY mytable
    from 's3://%s'
    iam_role 'arn:aws:iam::xxxx:role/role_name'
    csv;""" % filename)

die Rolle muss Rotverschiebungszugriff auf S3 ermöglichen, siehe hier für weitere Details

Ich habe festgestellt, dass dies für eine 300-KB-Datei (12000x2-Datenrahmen) 4 Sekunden dauert, verglichen mit den 8 Minuten, die ich mit der pandas to_sql()-Funktion erhielt

0
erncyp

Für diesen Zweck haben Postgres = RedShift Sie haben zwei Möglichkeiten:

Option 1:

Von Pandas: http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql

Das pandas.io.sql-Modul bietet eine Sammlung von Abfrage-Wrappern, um sowohl das Abrufen von Daten zu erleichtern als auch die Abhängigkeit von der DB-spezifischen API zu reduzieren. Die Datenbankabstraktion wird von SQLAlchemy bereitgestellt, sofern installiert. Zusätzlich benötigen Sie eine Treiberbibliothek für Ihre Datenbank. Beispiele für solche Treiber sind psycopg2 für PostgreSQL oder pymysql für MySQL.

DataFrames schreiben

Angenommen, die folgenden Daten befinden sich in DataFrame-Daten. Mit to_sql () können wir sie in die Datenbank einfügen.

id  Date    Col_1   Col_2   Col_3
26  2012-10-18  X   25.7    True
42  2012-10-19  Y   -12.4   False
63  2012-10-20  Z   5.73    True

In [437]: data.to_sql('data', engine)

Bei einigen Datenbanken kann das Schreiben großer DataFrames zu Fehlern führen, da die Paketgrößenbeschränkungen überschritten werden. Dies kann vermieden werden, indem beim Aufruf von to_sql der Parameter chunksize gesetzt wird. Zum Beispiel schreibt Folgendes die Daten in Stapeln von jeweils 1000 Zeilen in die Datenbank:

In [438]: data.to_sql('data_chunked', engine, chunksize=1000)

Option 2

Oder Sie können einfach selbst ein Wenn Sie ein Datenframe haben, das als Daten bezeichnet wird, ziehen Sie es einfach mit iterows:

for row in data.iterrows():

fügen Sie dann jede Zeile zu Ihrer Datenbank hinzu. Ich würde Kopieren statt Einfügen für jede Zeile verwenden, da es viel schneller sein wird.

http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from

0