View e subquery

A questo punto della guida siamo perfettamente in grado di realizzare query che permettano di filtrare e
raggruppare dati, ordinarli ed sintetizzarli, nonché far convogliare più tabelle insieme grazie al join.

Per rendere più agevole il loro utilizzo possiamo basarci su due meccanismi che hanno già trovato una grande
fortuna nel mondo relazionale e che offrono il loro contributo anche in Hive: View e subquery.
Vediamole nel dettaglio basando i nostri esempi sul database presentato a proposito dei
join.

Le View

Con le View creiamo in un database una tabella virtuale ovvero una tabella che non corrisponde ad una struttura
fisica ma al risultato di una query.

Partiamo subito con un esempio. Supponiamo di voler svolgere delle elaborazioni sul risultato di un join
tra la tabella transazioni e la tabella conti. Diciamo che il risultato di questo join ci serve come base per poter
iniziare a lavorare:

SELECT data, importo, codice, nome, cognome, numero
FROM conti as c JOIN transazioni as t ON c.id=t.id_conto;

Tale query genera un risultato di questo tipo:

2018-01-24      1000.0  AS      Antonio Rossi   555100
2018-01-25      -250.0  BA      Antonio Rossi   555100
2018-02-01      -300.0  PC      Anna    Bianchi 555200
2018-02-25      1000.0  AS      Anna    Bianchi 555200
2018-03-25      -250.0  BA      Alessio Verdi   555300
2018-03-02      -50.0   PB      Antonio Rossi   555100
2018-03-28      1000.0  AS      Alessio Verdi   555300
2018-04-11      -300.0  PC      Simona  Neri    555400
2018-04-27      1000.0  AS      Anna    Bianchi 555200
...
...

Qualsiasi elaborazione vorremo svolgere comunque dovrà sempre partire da questa struttura di base per poi aggiungere
tutti i WHERE, GROUP BY ed comandi vari di cui avremo bisogno.

In tali casi risulta comodo creare una View ovvero una tabella virtuale in grado di offrire come suo contenuto il risultato
del join:

CREATE VIEW IF NOT EXISTS conti_view AS
SELECT data, importo, codice, nome, cognome, numero
FROM conti as c JOIN transazioni as t ON c.id=t.id_conto;

Da questo momento il database potrà contare su una nuova tabella di nome conti_view.

Interrogandola con:

SELECT * FROM conti_view;

otteniamo un risultato identico al join con una sintassi molto più veloce. Questo è il primo
grande contributo di una view: permettere di usare una tabella come punto di approccio ad un set di dati molto
più articolato.

Per i database la view risulta alla pari di ogni altra tabella tanto che se gli chiedessimo con SHOW TABLES il
suo contenuto risponderebbe così:

> show tables;
OK
codici
conti
conti_view
transazioni

Il database conteneva già da prima le tabelle codici, conti e transazioni
ma adesso mostra la presenza di un'ulteriore tabella che è proprio stata definita con il comando CREATE VIEW.
Dispone anche di una struttura interna, proprio come una comune tabella:

> describe conti_view;
OK
data                    date
importo                 float
codice                  string
nome                    string
cognome                 string
numero                  string

La differenza rispetto ad una tabella standard è che i dati che offre non sono i suoi ma il risultato dinamico
di una query. In ambito lavorativo, piuttosto che eseguire query direttamente sulle tabelle sarebbe sempre meglio definire delle view
in modo da legare meno il codice alle strutture fisiche ed ottenere con le view una sorta
di strato astratto per isolare l'applicazione dal database.

Osservando il comando con cui abbiamo creato la view notiamo che è suddiviso in tre parti:

• CREATE VIEW IF NOT EXISTS conti_view AS: qui viene dichiarata la view utilizzando la direttiva
  CREATE VIEW seguita dal nome che vogliamo assegnarle. La clausola IF NOT EXISTS non è obbligatoria
  ma serve per specificare che il comando deve essere eseguito solo se la struttura non fa già parte del database.
  Subito dopo il nome della view indichiamo AS e questo pone fine alla parte di dichiarazione;
• SELECT data, importo, codice, nome, cognome, numero
FROM conti as c JOIN transazioni as t ON c.id=t.id_conto;: dopo AS inizia la seconda parte della dichiarazione
  della view ed è costituita da una normalissima query, quella che deve specificare il funzionamento della view ovvero il
  modo in cui questa sarà in grado di reperire i dati richiesti.

Si ricorda che non abbiamo specificato il nome del database in cui la view va creata e ciò perchè abbiamo supposto di
aver usato il comando USE seguito dal nome del database nella sessione di lavoro in corso. In alternativa
si può specificare il nome delle strutture (view o tabelle) nel formato nome_database.nome_struttura.

Una view può essere modificata dopo la sua creazione con ALTER VIEW. Possiamo modificarne il nome
come nel seguente caso in cui rinominiamo conti_view in view_conti_intestatati e poi svolgiamo qualche
operazione per dimostrarne i risultati:

> ALTER VIEW conti_view RENAME TO view_conti_intestatari;
OK
> SHOW TABLES;
OK
codici
conti
transazioni
view_conti_intestatari
> SELECT * FROM conti_view;
FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'conti_view'
hive> SELECT * FROM view_conti_intestatari;
2018-01-24      1000.0  AS      Antonio Rossi   555100
2018-01-25      -250.0  BA      Antonio Rossi   555100
2018-02-01      -300.0  PC      Anna    Bianchi 555200
2018-02-25      1000.0  AS      Anna    Bianchi 555200
2018-03-25      -250.0  BA      Alessio Verdi   555300
2018-03-02      -50.0   PB      Antonio Rossi   555100
...
...

Possiamo anche usare ALTER VIEW per modificare la query che vogliamo assegnare alla view:

ALTER VIEW view_conti_intestatari AS
SELECT data, importo, CONCAT(cognome, " ",nome) AS intestatario, numero
FROM conti as c JOIN transazioni as t ON c.id=t.id_conto;

Da questo momento la view ha una struttura diversa ed in maniera assolutamente automatica restituirà dati
differenti:

> DESCRIBE view_conti_intestatari;
OK
data                    date
importo                 float
intestatario            string
numero                  string
> SELECT * FROM view_conti_intestatari;
2018-01-24      1000.0  Rossi Antonio    555100
2018-01-25      -250.0  Rossi Antonio    555100
2018-02-01      -300.0  Bianchi Anna     555200
2018-02-25      1000.0  Bianchi Anna     555200

Infine, le view possono essere distrutte quando non più necessarie con DROP VIEW:

DROP VIEW IF EXISTS view_conti_intestatari;

> SHOW TABLES;
OK
codici
conti
transazioni

SELECT numero, importo, data FROM
(SELECT * FROM transazioni as t JOIN conti as c ON t.id_conto=c.id) as q;

555100  1000.0  2018-01-24
555100  -250.0  2018-01-25
555200  -300.0  2018-02-01
555200  1000.0  2018-02-25
555300  -250.0  2018-03-25
555100  -50.0   2018-03-02
555300  1000.0  2018-03-28
...
...

SELECT importo FROM transazioni WHERE id_conto = (SELECT id FROM conti WHERE numero='555300')

-250.0
1000.0
-30.0
620.0