View materializzate in PostgreSQL

Se lavori molto con SQL (meglio ancora se usi PostgreSQL), o anche a un livello intermedio, per interrogare database, probabilmente avrai scritto un buon numero di query complesse per rispondere a domande cruciali. E capita anche che tu debba usare spesso i risultati di quelle query nel tuo lavoro. Eseguire più volte una query complessa può essere costoso. E se ci fosse un modo per mettere in cache i risultati di una query complessa per migliorare le prestazioni? Ebbene, ci sono le view materializzate.

Potresti pensare che esistano anche le semplici view che possono memorizzare il risultato di una query. Ma una view semplice differisce da una view materializzata sotto diversi aspetti. Ecco cosa dice la documentazione ufficiale di PostgreSQL sulle view semplici:

La view non è materializzata fisicamente. Invece, la query viene eseguita ogni volta che la view viene richiamata in una query.

Ciò significa che le view semplici non sono memorizzate fisicamente nel database. Ogni volta che una view viene usata in una query, viene eseguita la query che ha creato la view. Ed è proprio questo a renderle un po' inefficienti in termini di tempo di accesso.

Le view materializzate, invece, offrono molta flessibilità permettendoti di persistere fisicamente una view nel database. E puoi operare sulle view materializzate esattamente come fai con le view semplici (ma con un tempo di accesso inferiore). Nel resto di questo tutorial, vedrai le view materializzate in PostgreSQL. In uno dei suoi webinar, il Chief Data Scientist di DataCamp David Robinson ha mostrato come usa le view materializzate per archiviare in modo efficace i risultati di query complesse.

Nota: per seguire questo tutorial, dovresti avere familiarità con i contenuti trattati nel corso Unire i dati in SQL.

Configurazione iniziale del database

È meglio configurare un database in PostgreSQL e crearvi una tabella di base, così da poterla interrogare in modo efficace. Questo ti aiuterà anche nelle sezioni successive del tutorial. Puoi ripristinare questo backup del database, che contiene la tabella che userai in questo tutorial. Se vuoi imparare come ripristinare un backup del database in PostgreSQL, puoi seguire la prima sezione di questo tutorial.

Se sei riuscito a ripristinare il backup, dovresti vedere una tabella chiamata international_debt all'interno del database (dovrai prima creare un database, se non ne hai uno). Diamo rapidamente un'occhiata alle prime righe della tabella (una semplice select ti aiuterà a farlo) -

La tabella contiene informazioni sulle statistiche del debito di diversi paesi del mondo per l'anno in corso in varie categorie (vedi le colonne indicator_name, indicator_code). La colonna del debito mostra l'ammontare del debito (in USD) che un determinato paese ha in una specifica categoria. I dati appartengono al dominio dell'economia e sono spesso utilizzati per analizzare le condizioni economiche dei vari paesi. I dati sono stati raccolti dalla Banca Mondiale.

Ora che hai configurato correttamente un database, puoi passare alle sezioni successive di questo tutorial.

Creare view materializzate

Creiamo una view materializzata della seguente query:

select country_name,
sum(debt) as total_debt
from international_debt
group by country_name order by country_name;

La query restituisce i nomi dei paesi con il loro debito totale (in ordine alfabetico). L'output della query dovrebbe essere simile a questo:

Ora la view materializzata:

CREATE MATERIALIZED VIEW country_total_debt
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name;

Dovresti ricevere un messaggio di successo del tipo: Query returned successfully: 124 rows affected, 242 ms execution time.

Aggiornare una view materializzata

Puoi anche istruire PostgreSQL a non caricare alcun dato nella view materializzata mentre la crei specificando l'opzione WITH NO DATA:

CREATE MATERIALIZED VIEW country_total_debt_2
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name
WITH NO DATA;

Otterrai un messaggio che dice: Query returned successfully with no result in 41 ms. Nota la differenza tra i due messaggi di output. Se aggiungi l'opzione WITH NO DATA, viene creata una view materializzata vuota. Non puoi interrogare questa view materializzata. Tuttavia, puoi popolare la view materializzata eseguendo:

REFRESH MATERIALIZED VIEW country_total_debt_2;

Interrogare una view materializzata

A questo punto dovresti avere due view materializzate (country_total_debt, country_total_debt_2) create. Eseguiamo una semplice select usando una delle due:

select * from country_total_debt_2;

La query dovrebbe restituire qualcosa di simile a:

L'output è esattamente lo stesso della query che abbiamo racchiuso in una view materializzata. Puoi eseguire query sulle view materializzate anche con funzioni di aggregazione:

select max(total_debt) from country_total_debt_2;

Dovresti ottenere:

Eliminare una view materializzata

Le view materializzate possono essere eliminate proprio come si eliminano le tabelle in PostgreSQL. Devi solo specificare Materialized View dopo Drop:

Drop Materialized View country_total_debt_2;

Per confrontare MySQL con il suo secondo concorrente più popolare, dai un'occhiata al nostro confronto approfondito PostgreSQL vs. MySQL.

Conclusione

Questo è tutto per il tutorial. Grazie per aver letto fino in fondo. In questo tutorial hai imparato cosa sono le view materializzate in PostgreSQL e come puoi operarci. Per la loro efficacia, le view materializzate sono ampiamente usate nei data warehouse. Se ti interessa imparare query più complesse, dai un'occhiata ai seguenti corsi di DataCamp:

• SQL intermedio
• Reporting in SQL