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?
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:
GRANT
s 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.
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:
psql
Befehlpsql -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
copy
BefehlCOPY (SELECT * from users) To '/tmp/output.csv' With CSV;
>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.
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.
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
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!
Ja, hier sind meine Notizen:
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.
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,,
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!