Salva l’output PL / pgSQL da PostgreSQL in un file CSV

Qual è il modo più semplice per salvare l’output PL / pgSQL da un database PostgreSQL a un file CSV?

Sto usando PostgreSQL 8.4 con plugin pgAdmin III e PSQL da cui eseguo le query.

Vuoi il file risultante sul server o sul client?

Lato server

Se vuoi riutilizzare o automatizzare qualcosa di semplice, puoi utilizzare il comando COPY incorporato di Postgresql. per esempio

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ','; 

Questo approccio funziona interamente sul server remoto : non può scrivere sul PC locale. Deve anche essere eseguito come un “superutente” Postgres (normalmente chiamato “root”) perché Postgres non può impedirlo di fare cose cattive con il filesystem locale di quella macchina.

Questo in realtà non significa che devi essere connesso come superutente (automatizzando quello sarebbe un rischio per la sicurezza di un tipo diverso), perché puoi usare l’opzione SECURITY DEFINER per CREATE FUNCTION per creare una funzione che funzioni come se fossi un superutente .

La parte cruciale è che la tua funzione è lì per eseguire controlli aggiuntivi, non solo bypassare la sicurezza – in modo da poter scrivere una funzione che esporta i dati esatti di cui hai bisogno, o potresti scrivere qualcosa che può accettare varie opzioni a patto che incontrare una whitelist rigorosa. Devi controllare due cose:

  1. Quali file dovrebbe essere consentito all’utente di leggere / scrivere su disco? Ad esempio, potrebbe trattarsi di una directory particolare e il nome del file potrebbe avere un prefisso o un’estensione appropriati.
  2. Quali tabelle l’utente dovrebbe essere in grado di leggere / scrivere nel database? Questo sarebbe normalmente definito da GRANT nel database, ma la funzione è ora in esecuzione come superutente, quindi le tabelle che normalmente sarebbero “fuori limite” saranno completamente accessibili. Probabilmente non vuoi permettere a qualcuno di invocare la tua funzione e aggiungere righe alla fine della tabella “utenti” …

Ho scritto un post sul blog in espansione su questo approccio , inclusi alcuni esempi di funzioni che esportano (o importano) file e tabelle che soddisfano condizioni rigorose.


Dalla parte del cliente

L’altro approccio consiste nel gestire i file dal lato client , cioè nella tua applicazione o script. Il server Postgres non ha bisogno di sapere a quale file si sta copiando, sputa solo i dati e il client lo mette da qualche parte.

La syntax di base per questo è il comando COPY TO STDOUT e gli strumenti grafici come pgAdmin lo avvolgeranno in una bella finestra di dialogo.

Il client della riga di comando di psql ha uno speciale “meta-comando” chiamato \copy , che prende tutte le stesse opzioni della “vera” COPY , ma viene eseguito all’interno del client:

 \copy (Select * From foo) To '/tmp/test.csv' With CSV 

Si noti che non c’è terminazione ; , perché i meta-comandi sono terminati da newline, a differenza dei comandi SQL.

Dai documenti :

Non confondere COPY con l’istruzione psql \ copy. \ copy richiama COPY FROM STDIN o COPY TO STDOUT, quindi recupera / memorizza i dati in un file accessibile al client psql. Pertanto, l’accessibilità dei file e i diritti di accesso dipendono dal client piuttosto che dal server quando viene utilizzata la copia.

Il linguaggio di programmazione dell’applicazione potrebbe anche supportare il push o il recupero dei dati, ma in genere non è ansible utilizzare COPY FROM STDIN / TO STDOUT all’interno di un’istruzione SQL standard, poiché non esiste alcun modo per colbind il stream di input / output. Il gestore PHP di PostgreSQL ( non PDO) include funzioni pg_copy_from e pg_copy_to molto basilari che copiano da / verso un array PHP, che potrebbe non essere efficiente per set di dati di grandi dimensioni.

Ci sono diverse soluzioni:

1 comando psql

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

Questo ha il grande vantaggio che puoi usarlo tramite SSH, come il ssh [email protected] command – che ti permette di ottenere

2 comando di copy postgres

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql interattivo (o non)

 >psql dbname psql>\f ',' psql>\a psql>\o '/tmp/output.csv' psql>SELECT * from users; psql>\q 

Tutti possono essere usati negli script, ma io preferisco il # 1.

4 pgadmin ma non è scriptable.

Nel terminale (mentre connesso al db) imposta l’output sul file cvs

1) Impostare il separatore di campo su ',' :

 \f ',' 

2) Imposta il formato di output non allineato:

 \a 

3) Mostra solo tuple:

 \t 

4) Imposta uscita:

 \o '/tmp/yourOutputFile.csv' 

5) Esegui la tua query:

 :select * from YOUR_TABLE 

6) Uscita:

 \o 

Sarai quindi in grado di trovare il tuo file csv in questa posizione:

 cd /tmp 

Copialo usando il comando scp o modifica usando nano:

 nano /tmp/yourOutputFile.csv 

Se sei interessato a tutte le colonne di una tabella specifica insieme alle intestazioni, puoi utilizzare

 COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER; 

Questo è un po ‘più semplice di

 COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER; 

che, per quanto ne so, sono equivalenti.

Ho dovuto usare \ COPY perché ho ricevuto il messaggio di errore:

 ERROR: could not open file "/filepath/places.csv" for writing: Permission denied 

Quindi ho usato:

 \Copy (Select address, zip From manjadata) To '/filepath/places.csv' With CSV; 

ed è funzionante

psql può farlo per te:

 [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]:~$ 

Vedi man psql per aiuto sulle opzioni usate qui.

In pgAdmin III esiste un’opzione per esportare su file dalla finestra di query. Nel menu principale è Query -> Esegui su file o c’è un pulsante che fa la stessa cosa (è un triangolo verde con un dischetto blu anziché il triangolo verde chiaro che esegue solo la query). Se non si sta eseguendo la query dalla finestra della query, farei ciò che IMSoP ha suggerito e utilizzare il comando di copia.

Sto lavorando su AWS Redshift, che non supporta la funzione COPY TO .

Il mio strumento di BI supporta i CSV delimitati da tabulazioni, quindi ho utilizzato quanto segue:

  psql -h dblocation -p port -U user -d dbname -F $'\t' --no-align -c " SELECT * FROM TABLE" > outfile.csv 

Questa informazione non è davvero ben rappresentata. Poiché questa è la seconda volta che ho bisogno di ricavare questo, metterò questo qui per ricordare a me stesso se non altro.

Il modo migliore per farlo (ottenere CSV da postgres) è utilizzare il comando COPY ... TO STDOUT . Sebbene tu non voglia farlo nel modo mostrato nelle risposte qui. Il modo corretto di usare il comando è:

 COPY (select id, name from groups) TO STDOUT WITH CSV HEADER 

Ricorda solo un comando!

È ottimo per l’uso su ssh:

 $ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv 

È ottimo per l’uso nella finestra mobile su ssh:

 $ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv 

È anche fantastico sulla macchina locale:

 $ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv 

O nella finestra mobile sulla macchina locale ?:

 docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv 

O su un cluster di Kubernetes, nella finestra mobile, su HTTPS ??:

 kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv 

Così versatile, molte virgole!

Lo fai anche tu?

Sì, l’ho fatto, ecco i miei appunti:

I COPI

L’uso /copy esegue in modo efficace le operazioni sui file su qualsiasi sistema su cui è in esecuzione il comando psql , come l’utente che lo sta eseguendo 1 . Se ci si connette a un server remoto, è semplice copiare i file di dati sul sistema che esegue psql sul / dal server remoto.

COPY esegue le operazioni sui file sul server come l’account utente del processo di back-end ( postgres predefinito), i percorsi dei file e le autorizzazioni vengono controllati e applicati di conseguenza. Se si utilizza TO STDOUT i controlli delle autorizzazioni dei file vengono ignorati.

Entrambe queste opzioni richiedono il movimento successivo dei file se psql non è in esecuzione sul sistema in cui si desidera che il CSV risultante risieda alla fine. Questo è il caso più probabile, secondo la mia esperienza, quando lavori principalmente con server remoti.

È più complesso configurare qualcosa come un tunnel TCP / IP su ssh su un sistema remoto per un semplice output CSV, ma per altri formati di output (binari) potrebbe essere meglio /copy su una connessione tunneled, eseguendo un psql locale. Analogamente, per le importazioni di grandi dimensioni, lo spostamento del file di origine sul server e l’utilizzo di COPY è probabilmente l’opzione con le prestazioni più elevate.

Parametri PSQL

Con i parametri di psql puoi formattare l’output come CSV ma ci sono aspetti negativi come dover ricordare di disabilitare il cercapersone e non ottenere le intestazioni:

 $ 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,, 

Altri strumenti

No, voglio solo togliere CSV dal mio server senza compilare e / o installare uno strumento.

Ho scritto un piccolo strumento chiamato psql2csv che incapsula il modello di COPY query TO STDOUT , dando come risultato un CSV corretto. La sua interfaccia è simile a psql .

 psql2csv [OPTIONS] < QUERY psql2csv [OPTIONS] QUERY 

Si presuppone che la query sia il contenuto di STDIN, se presente, o l'ultimo argomento. Tutti gli altri argomenti vengono inoltrati a psql ad eccezione di questi:

 -h, --help show help, then exit --encoding=ENCODING use a different encoding than UTF8 (Excel likes LATIN1) --no-header do not output a header 

Se hai una query più lunga e ti piace usare psql, metti la tua query su un file e usa il seguente comando:

 psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv 

Ho provato diverse cose, ma meno di loro è stato in grado di darmi il csv desiderato con i dettagli dell’intestazione.

ECCO COSA HA LAVORATO PER ME.

psql -d dbame -U nome utente -c “COPIA (SELEZIONA * DA TABELLA) A STATOUR CON CSV HEADER”> OUTPUT_CSV_FILE.csv

JackDB , un client di database nel tuo browser web, rende questo veramente facile. Soprattutto se sei su Heroku.

Ti consente di connetterti a database remoti e di eseguire query SQL su di essi.

Fonte jackdb-heroku http://static.jackdb.com/assets/img/blog/jackdb-heroku-oauth-connect.gif


Una volta connesso il DB, è ansible eseguire una query ed esportarla in formato CSV o TXT (vedere in basso a destra).


jackdb-export

Nota: non sono in alcun modo affiliato con JackDB. Attualmente utilizzo i loro servizi gratuiti e penso che sia un ottimo prodotto.

 import json cursor = conn.cursor() qry = """ SELECT details FROM test_csvfile """ cursor.execute(qry) rows = cursor.fetchall() value = json.dumps(rows) with open("/home/asha/Desktop/Income_output.json","w+") as f: f.write(value) print 'Saved to File Successfully'