Speichern Sie die PL / pgSQL-Ausgabe von PostgreSQL in einer CSV-Datei
Was ist der einfachste Weg, um PL/pgSQL-Ausgaben aus einer PostgreSQL-Datenbank in eine CSV-Datei zu speichern?
Ich verwende PostgreSQL 8.4 mit dem pgAdmin III- und PSQL-Plugin, von dem aus ich Abfragen ausführe.
Möchten Sie die resultierende Datei auf dem Server oder auf dem Client?
Serverseite
Wenn Sie etwas einfach wiederverwenden oder automatisieren möchten, können Sie den in COPY eingebauten Postgresql-Befehl verwenden. z.B.
_Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';
_Dieser Ansatz läuft vollständig auf dem Remote-Server - es kann nicht auf Ihren lokalen PC geschrieben werden. Es muss auch als Postgres "Superuser" (normalerweise als "root" bezeichnet) ausgeführt werden, da Postgres nicht verhindern kann, dass es böse Dinge mit dem lokalen Dateisystem dieses Computers tut.
Das bedeutet nicht, dass Sie als Superuser verbunden sein müssen (Automatisierung, die ein anderes Sicherheitsrisiko darstellt), da Sie die Option SECURITY DEFINER_ VERWENDEN KÖNNEN, UM _CREATE FUNCTION , um eine Funktion zu erstellen, die so ausführt, als ob Sie ein Superuser wären .
Der entscheidende Teil ist, dass Ihre Funktion zusätzliche Überprüfungen durchführt und nicht nur die Sicherheit umgeht. Sie können also eine Funktion schreiben, die genau die Daten exportiert, die Sie benötigen, oder Sie können etwas schreiben, das verschiedene Optionen akzeptiert, solange diese vorhanden sind eine strenge Whitelist erfüllen. Sie müssen zwei Dinge überprüfen:
- Welche Dateien soll der Benutzer lesen/schreiben dürfen? Dies kann beispielsweise ein bestimmtes Verzeichnis sein, und der Dateiname muss möglicherweise ein geeignetes Präfix oder eine geeignete Erweiterung haben.
- Welche Tabellen soll der Benutzer in der Datenbank lesen/schreiben können? Dies wird normalerweise durch
GRANTs in der Datenbank definiert, aber die Funktion wird jetzt als Superuser ausgeführt, sodass auf Tabellen, die normalerweise außerhalb der Grenzen liegen, uneingeschränkt zugegriffen werden kann. Sie möchten wahrscheinlich nicht, dass jemand Ihre Funktion aufruft und Zeilen am Ende Ihrer "Benutzer" -Tabelle hinzufügt.
Ich habe einen Blog-Beitrag geschrieben, der diesen Ansatz erweitert und einige Beispiele für Funktionen enthält, mit denen Dateien und Tabellen exportiert (oder importiert) werden, die strengen Bedingungen entsprechen.
Client-Seite
Der andere Ansatz ist erledigt die Dateibehandlung auf der Clientseite , d. H. In Ihrer Anwendung oder Ihrem Skript. Der Postgres-Server muss nicht wissen, in welche Datei Sie kopieren, er spuckt nur die Daten aus und der Client legt sie irgendwo ab.
Die zugrunde liegende Syntax hierfür ist der Befehl _COPY TO STDOUT_, und grafische Tools wie pgAdmin verpacken ihn für Sie in einem netten Dialogfeld.
Der psql Befehlszeilen-Client hat einen speziellen "Meta-Befehl" namens \copy, der Verwendet die gleichen Optionen wie "real" COPY, wird jedoch im Client ausgeführt:
_\copy (Select * From foo) To '/tmp/test.csv' With CSV
_Beachten Sie, dass es kein abschließendes _;_ gibt, da Metabefehle im Gegensatz zu SQL-Befehlen durch Zeilenumbruch beendet werden.
Aus den Dokumenten :
Verwechseln Sie COPY nicht mit der psql-Anweisung\copy.\copy ruft COPY FROM STDIN oder COPY TO STDOUT auf und ruft/speichert die Daten in einer Datei ab, auf die der psql-Client zugreifen kann. Daher hängen die Dateizugriffsmöglichkeiten und Zugriffsrechte vom Client und nicht vom Server ab, wenn\copy verwendet wird.
Ihre Anwendungsprogrammiersprache unterstützt möglicherweise auch das Pushen oder Abrufen von Daten, Sie können jedoch _COPY FROM STDIN_/_TO STDOUT_ in einer Standard-SQL-Anweisung nicht verwenden, da Es gibt keine Möglichkeit, den Eingabe-/Ausgabestream zu verbinden. Der PostgreSQL-Handler von PHP ( not PDO) enthält sehr einfache pg_copy_from und pg_copy_to Funktionen, die in/aus einem PHP-Array kopiert werden, was bei großen Datenmengen möglicherweise nicht effizient ist.
Es gibt verschiedene Lösungen:
1 psql Befehl
psql -d dbname -t -A -F"," -c "select * from users" > output.csv
Dies hat den großen Vorteil, dass Sie es über SSH verwenden können, wie ssh [email protected] command - so dass Sie erhalten
2 postgres copy Befehl
COPY (SELECT * from users) To '/tmp/output.csv' With CSV;
3 psql interaktiv (oder nicht)
>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q
Alle von ihnen können in Skripten verwendet werden, aber ich bevorzuge # 1.
4 pgadmin aber das ist nicht skriptfähig.
Im Terminal (während Sie mit der Datenbank verbunden sind) stellen Sie die Ausgabe auf die CVS-Datei ein
1) Setzen Sie den Feldtrenner auf ',':
\f ','
2) Ausgabeformat nicht ausgerichtet einstellen:
\a
3) Zeige nur Tupel:
\t
4) Ausgang einstellen:
\o '/tmp/yourOutputFile.csv'
5) Führen Sie Ihre Anfrage aus:
:select * from YOUR_TABLE
6) Ausgabe:
\o
Sie finden Ihre CSV-Datei dann an folgendem Speicherort:
cd /tmp
Kopiere es mit dem scp Befehl oder bearbeite es mit nano:
nano /tmp/yourOutputFile.csv
Wenn Sie sich für all die Spalten einer bestimmten Tabelle zusammen mit Überschriften interessieren, können Sie verwenden
COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;
Das ist ein bisschen einfacher als
COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;
die meines Wissens gleichwertig sind.
CSV-Exportvereinigung
Diese Information ist nicht wirklich gut vertreten. Da dies das zweite Mal ist, dass ich dies ableiten muss, werde ich dies hier einfügen, um mich selbst daran zu erinnern, wenn nichts anderes.
Wirklich der beste Weg, dies zu tun (CSV aus postgres herauszuholen), ist die Verwendung des Befehls COPY ... TO STDOUT. Obwohl Sie es nicht so machen wollen, wie es in den Antworten hier gezeigt wird. Die korrekte Verwendung des Befehls lautet:
COPY (select id, name from groups) TO STDOUT WITH CSV HEADER
Merke dir nur einen Befehl!
Es ist großartig für den Einsatz über ssh:
$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv
Es ist großartig für die Verwendung in Docker über ssh:
$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv
Es ist sogar großartig auf dem lokalen Computer:
$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv
Oder in Docker auf dem lokalen Computer ?:
docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv
Oder auf einem Kubernetes-Cluster, im Docker, über HTTPS ??:
kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv
Vielseitig, viele Kommas!
Machst du überhaupt?
Ja, hier sind meine Notizen:
Die KOPIEN
Die Verwendung von /copy führt Dateivorgänge auf jedem System aus, auf dem der Befehl psql ausgeführt wird, als der Benutzer, der ihn ausführt 1 . Wenn Sie eine Verbindung zu einem Remote-Server herstellen, ist es einfach, Datendateien auf dem System, auf dem psql ausgeführt wird, zum/vom Remote-Server zu kopieren.
COPY führt Dateivorgänge auf dem Server als Backend-Prozessbenutzerkonto aus (Standard postgres), Dateipfade und Berechtigungen werden überprüft und entsprechend angewendet. Bei Verwendung von TO STDOUT werden die Dateiberechtigungsprüfungen umgangen.
Beide Optionen erfordern eine nachfolgende Dateiverschiebung, wenn psql nicht auf dem System ausgeführt wird, auf dem sich die resultierende CSV befinden soll. Dies ist meiner Erfahrung nach der wahrscheinlichste Fall, wenn Sie hauptsächlich mit Remoteservern arbeiten.
Es ist komplexer, so etwas wie einen TCP/IP-Tunnel über SSH zu einem fernen System für eine einfache CSV-Ausgabe zu konfigurieren, aber für andere Ausgabeformate (binär) ist es möglicherweise besser, /copy über eine getunnelte Verbindung auszuführen und eine lokale Verbindung auszuführen psql. In ähnlicher Weise ist es bei umfangreichen Importen wahrscheinlich die leistungsstärkste Option, die Quelldatei auf den Server zu verschieben und COPY zu verwenden.
PSQL-Parameter
Mit psql-Parametern können Sie die Ausgabe wie CSV formatieren, aber es gibt Nachteile, bei denen Sie nicht vergessen müssen, den Pager zu deaktivieren und keine Header abzurufen:
$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,
3,Truck,1,2017-10-02,,t,,0,,
4,Truck,2,2017-10-02,,t,,0,,
Andere Werkzeuge
Nein, ich möchte nur CSV von meinem Server entfernen, ohne ein Tool zu kompilieren und/oder zu installieren.
Ich musste die\COPY verwenden, da ich die Fehlermeldung erhalten habe:
ERROR: could not open file "/filepath/places.csv" for writing: Permission denied
Also habe ich benutzt:
\Copy (Select address, Zip From manjadata) To '/filepath/places.csv' With CSV;
und es funktioniert
psql kann dies für Sie tun:
[email protected]:~$ psql -d beancounter -t -A -F"," \
-c "select date, symbol, day_close " \
"from stockprices where symbol like 'I%' " \
"and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
[email protected]:~$
In man psql finden Sie Hilfe zu den hier verwendeten Optionen.
Ich arbeite an AWS Redshift, das die Funktion COPY TO nicht unterstützt.
Mein BI-Tool unterstützt jedoch durch Tabulatoren getrennte CSVs. Daher habe ich Folgendes verwendet:
psql -h dblocation -p port -U user -d dbname -F $'\t' --no-align -c "SELECT * FROM TABLE" > outfile.csv
In pgAdmin III gibt es eine Option zum Exportieren in eine Datei aus dem Abfragefenster. Im Hauptmenü ist es Abfrage -> In Datei ausführen, oder es gibt eine Schaltfläche, die dasselbe tut (es ist ein grünes Dreieck mit einer blauen Diskette im Gegensatz zu dem einfachen grünen Dreieck, das die Abfrage gerade ausführt). Wenn Sie die Abfrage nicht über das Abfragefenster ausführen, mache ich das, was IMSoP vorgeschlagen hat, und verwende den Befehl copy.
Die neue Version - psql 12 - unterstützt --csv.
- csv
Schaltet in den CSV-Ausgabemodus (Comma-Separated Values). Dies entspricht dem Format \pset csv .
csv_fieldsep
Gibt das Feldtrennzeichen an, das im CSV-Ausgabeformat verwendet werden soll. Wenn das Trennzeichen im Wert eines Felds enthalten ist, wird dieses Feld in doppelten Anführungszeichen gemäß den Standard-CSV-Regeln ausgegeben. Der Standardwert ist ein Komma.
Verwendungszweck:
psql -c "SELECT * FROM pg_catalog.pg_tables" --csv postgres
psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^' postgres
psql -c "SELECT * FROM pg_catalog.pg_tables" --csv postgres > output.csv
Ich habe ein kleines Tool namens psql2csv geschrieben, das das Muster _COPY query TO STDOUT_ kapselt, was zu einer korrekten CSV führt. Die Benutzeroberfläche ähnelt psql.
_psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY
_Es wird davon ausgegangen, dass die Abfrage den Inhalt von STDIN (falls vorhanden) oder das letzte Argument enthält. Alle anderen Argumente werden an psql weitergeleitet, mit Ausnahme der folgenden:
_-h, --help show help, then exit
--encoding=ENCODING use a different encoding than UTF8 (Excel likes LATIN1)
--no-header do not output a header
_Ich habe einige Dinge ausprobiert, aber nur wenige konnten mir die gewünschte CSV mit Header-Details geben.
Hier ist, was für mich gearbeitet hat.
psql -d dbame -U username \
-c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
OUTPUT_CSV_FILE.csv
Wenn Sie eine längere Abfrage haben und psql verwenden möchten, fügen Sie Ihre Abfrage in eine Datei ein und verwenden Sie den folgenden Befehl:
psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv
Verwenden Sie diesen Befehl, um eine CSV-Datei mit Spaltennamen als HEADER herunterzuladen:
Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;
JackDB , ein Datenbank-Client in Ihrem Webbrowser, macht dies wirklich einfach. Vor allem, wenn Sie auf Heroku sind.
Sie können eine Verbindung zu entfernten Datenbanken herstellen und SQL-Abfragen auf diesen ausführen.
Quelle jackdb-heroku http://static.jackdb.com/assets/img/blog/jackdb-heroku-oauth-connect.gif
Sobald Ihre Datenbank verbunden ist, können Sie eine Abfrage ausführen und nach CSV oder TXT exportieren (siehe unten rechts).
Hinweis: Ich bin in keiner Weise mit JackDB verbunden. Ich benutze derzeit ihre kostenlosen Dienste und denke, es ist ein großartiges Produkt.
Ich würde empfehlen DataGrip , eine Datenbank IDE von JetBrains. Sie können eine SQL-Abfrage in eine CSV-Datei exportieren und ssh-Tunnel problemlos einrichten. Wenn sich die Dokumentation auf "Ergebnismenge" bezieht, bedeutet dies das Ergebnis, das von einer SQL-Abfrage in der Konsole zurückgegeben wurde.
Ich bin nicht mit DataGrip verbunden, ich liebe das Produkt einfach!