Come esportare dati da PostgreSQL in un file CSV

Avete bisogno di inviare rapidamente i dati a un cliente o di condividere un report per ulteriori analisi? Un file CSV è un'ottima opzione di condivisione! Vediamo come utilizzare questo formato per esportare dati da un database PostgreSQL.

In questo articolo esamineremo innanzitutto cos'è il formato CSV e perché è utile quando si esporta un database PostgreSQL. Quindi esporteremo alcuni dati di esempio da un database reale utilizzando psql alla riga di comando e pgAdmin, un'interfaccia gratuita e open-source per i database PostgreSQL.

Prima di iniziare, assicuratevi di aver configurato il vostro database PostgreSQL. Se non avete ancora lavorato con PostgreSQL, date un'occhiata al nostro corso SQL per principianti in PostgreSQL. Include 130 sfide interattive di codifica progettate per farvi iniziare il vostro viaggio con Postgres. Se invece avete bisogno solo di un rapido ripasso, ecco un modo per esercitarvi con PostgreSQL.

Cosa sono i file CSV?

CSV è l'abbreviazione di Comma-Separated Values. Questo formato di file consente di memorizzare i dati in testo semplice, il che lo rende ideale per condividere i dati tra le varie applicazioni.

Un file CSV contiene righe di dati corrispondenti a singoli record; la prima riga è solitamente costituita dai nomi delle colonne. I valori di ogni riga sono separati da una virgola. Sebbene le virgole siano il separatore più comunemente usato (come suggerisce il nome del formato), anche altri caratteri (ad esempio punti e virgola, tabulazioni o spazi) possono separare i valori.

Ecco un esempio di file CSV. Nelle prossime sezioni ne esporteremo uno simile:

store_id,revenue,day
1,100.42,2023-05-01
1,148.89,2023-05-02
2,238.98,2023-05-03

Come si può vedere, la prima colonna contiene l'ID del negozio, la seconda il fatturato e la terza la data. Ogni riga rappresenta un record. L'organizzazione è molto simile a quella dei fogli di calcolo e dei database, ma è tutto in testo!

Perché esportare un database come file CSV?

I file CSV possono essere aperti da quasi tutti i software orientati ai dati. Esportare i dati di PostgreSQL in un file CSV significa poter condividere facilmente le informazioni con i colleghi, anche se utilizzano strumenti diversi.

Il formato CSV è anche nativo di molti strumenti di analisi dei dati e fogli di calcolo. L'esportazione in questo formato consente di trasferire rapidamente e facilmente i dati per analisi, visualizzazioni e report approfonditi.

Esportazione di dati da PostgreSQL a CSV

I dati

Questa sarà la nostra tabella di esempio chiamata sales. Ogni riga rappresenta quanto ha guadagnato ogni negozio in un determinato giorno.

sales

Esportazione dei dati con query SQL

Utilizzeremo due diverse query SQL per testare diversi metodi di esportazione. (È utile avere a portata di mano il nostro PostgreSQL Cheat Sheet per aumentare la produttività quando si scrivono le proprie query). Questa è la prima:

SELECT * FROM sales

Questa prima query è semplice e seleziona tutto ciò che è presente nella tabella. L'idea è di assicurarsi di non perdere i dati durante l'esportazione.

Ecco la seconda query (molto più complessa):

WITH max_revs (store_id, day, revenue, max_rev) AS (
	SELECT
		store_id,
		day,
		revenue, 
		MAX(revenue) OVER(PARTITION BY day) 
	FROM sales 
)
SELECT
	store_id,
	day,
	max_rev
FROM max_revs
WHERE revenue = max_rev;

La seconda query simula un semplice report; per ogni giorno, produce il negozio più importante per fatturato e il fatturato più alto in sé. È quello che si potrebbe scrivere lavorando all'analisi dei dati. Utilizziamo un'espressione di tabella comune per selezionare innanzitutto il fatturato più alto del giorno; la seconda query esterna seleziona i negozi che corrispondono al fatturato restituito dalla CTE.

Se non avete ancora familiarità con le CTE e le funzioni finestra, consultate la traccia SQL Reporting e il nostro corso sulle funzioni finestra in SQL. Ma per ora torniamo a esportare i dati da PostgreSQL a un file CSV. Una volta ottenuti i dati desiderati, dobbiamo avviare il processo di esportazione.

Esportazione del database tramite la riga di comando

Per prima cosa, esaminiamo l'esportazione di un database PostgreSQL tramite la riga di comando. Abbiamo due opzioni: usare il comando \copy o l'istruzione COPY; spiegheremo le differenze tra i due tra poco.

Sebbene l'uso di uno di questi metodi sia meno semplice rispetto all'uso di pgAdmin, probabilmente avete già tutto ciò che vi serve. Quindi, iniziamo.

Connessione al database con psql

Per entrambe le opzioni da riga di comando, utilizzeremo psql. Si tratta di uno strumento a riga di comando per i database PostgreSQL.

Per connettersi al database, inserire il seguente comando:

psql -h <hostname> -p <port> -d <database name> -U <username>

Sostituire hostname con il nome (o l'indirizzo) del database. La porta viene specificata sul server remoto insieme al nome del database e al nome utente. Successivamente, verrà richiesta una password.

Una volta collegati al database, è possibile utilizzare il comando \copy o il metodo COPY per esportare i dati. Inizieremo con la dimostrazione di \copy.

1. Esportazione dei dati con \copy

Il comando \copy copia direttamente il risultato della query locale in un file locale sul computer. Funziona sul lato client, quindi è un'ottima scelta se si hanno solo i permessi di lettura del database.

Ecco la sintassi:

\copy (query) to ‘filename’ with cvs [header]

È possibile includere l'argomento opzionale header quando si desidera che la tabella generata abbia una riga in più in cima contenente tutti i nomi delle colonne. Per garantire la chiarezza dei risultati, utilizzerò questo argomento in tutti i comandi futuri.

Vediamo un paio di esempi.

Esempio 1: Copiare l'intera tabella

Codice:

\copy (SELECT * FROM sales) to ‘/dbExport/sales_full_table.csv’ with csv header

Spiegazione: Dopo aver eseguito la query SELECT * FROM sales, il computer copia i risultati nel file CSV specificato. Non vengono inviate ulteriori richieste al database.

Esempio 2: Copia dei risultati della query

Codice:

\copy (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) to ‘/dbExport/sales_report_query.csv’ with csv header

Spiegazione: Anche in questo caso vale lo stesso principio. Il database esegue questa query di report e il computer locale copia i risultati in un file.

Il comando \copy è facile da usare e, nella maggior parte dei casi, è veloce quanto una query media. Questo perché il file risultante viene generato localmente invece di essere inviato dal database.

2. Uso dell'istruzione COPY

A differenza del comando \copy, l'istruzione COPY viene eseguita sul lato del database. La chiamiamo istruzione perché COPY è in realtà parte di un comando eseguito dal database. Ciò significa che il file risultante sarà salvato sul server remoto, quindi tenetelo presente quando scegliete il percorso del file. Salvare su un server remoto significa anche avere i privilegi di superutente (root) di PostgreSQL.

La sintassi dell'istruzione COPY è la seguente:

COPY { table | (query) } 'filename' [ DELIMITER 'delimiter' ] [HEADER]

Ho omesso alcuni argomenti per semplicità; se volete vedere la sintassi completa, visitate la documentazione di PostgreSQL.

Esempio 1: Copiare l'intera tabella

Codice:

COPY sales TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Spiegazione: Come ci si aspetterebbe, questa operazione copia l'intera tabella delle vendite nel file sales_full_table.csv. Si noti che abbiamo impostato il delimitatore sulla virgola e abbiamo incluso la riga di intestazione.

Esempio 2: Copia dei risultati della query

Codice:

COPY (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Spiegazione: Come suggerisce il sottotitolo, questa operazione esporta i risultati della query nel file sales_full_table.csv. Ecco un esempio dei dati in forma di tabella:

Come si può vedere, il risultato è lo stesso del comando \copy; l'unica differenza è il luogo in cui viene memorizzato il file risultante.

Esportazione dei dati con pgAdmin

Vediamo ora la seconda opzione, che utilizza un'interfaccia grafica (GUI). Forse conoscete già pgAdmin, un programma gratuito e open-source per la gestione dei database PostgreSQL. Questo è un modo semplice e diretto per esportare i dati da un database Postgres.

Connettersi al database

Aprire pgAdmin e fare clic su Aggiungi nuovo server.

Nella pagina Generale, scegliere un nome per la connessione.

Nella pagina Connessione inserire il nome dell'host ("localhost" se il database è in esecuzione sul proprio computer), il nome del database, il nome utente e la password.

Infine, fare clic su Salva. Se la connessione è stata stabilita correttamente, nella pagina del dashboard verranno visualizzati i grafici dell'attività del database.

Esportare i risultati delle query

Per aprire lo strumento di interrogazione, fare clic sull'icona del database (tre dischi) nella barra degli strumenti superiore oppure utilizzare Alt+Shift+Q. A questo punto è possibile interrogare il database scrivendo le query nella casella di testo ed eseguendole con il pulsante play in alto o con il tasto F5.

Una volta eseguita una query, il suo risultato verrà visualizzato nella finestra inferiore. Per salvare i risultati in un file CSV, premere il pulsante di download appena sopra i dati risultanti.

Come si può vedere, i risultati sono gli stessi di quelli ottenuti con gli strumenti da riga di comando. La differenza principale è che pgAdmin rende molto più semplice il passaggio dalla progettazione delle query al salvataggio dei risultati.

Per saperne di più sull'esportazione di dati con file CSV

Esportare i database in file CSV è un piacere! Ora che sapete come esportare tutti i dati di cui avete bisogno, potreste avere un'altra domanda: Come si importano i dati in un database Postgres? Leggete questo breve articolo su come importare dati in PostgreSQL usando pgAdmin per scoprirlo.

E se volete ampliare le vostre conoscenze su PostgreSQL, date un'occhiata al nostro percorso completo dalla A alla Z con PostgreSQL, che comprende oltre 1.000 esercizi interattivi. Buon apprendimento!