Join

Il meccanismo del Join è centrale nell'uso di database relazionali: permette di allineare tabelle ed estrinsecarne il valore facendo leva sulle relazioni. Hive,
nonostante sia applicato sui Big Data, ha una mentalità pienamente relazionale pertanto permette di fare uso di Join e mette a disposizione l'apposita parola chiave
JOIN.

Preparazione all'esempio

Approcciamo il concetto direttamente con un esempio. Per farlo abbiamo bisogno di dati da suddividere su più tabelle.
Supponiamo di avere i seguenti tre file che rappresentano transazioni bancarie di conti correnti dove ogni operazione è contraddistinta da un
codice alfabetico di due caratteri. Usiamo tre file:

• conti.csv in cui le righe hanno il formato 1;555100;Antonio;Rossi. Questa riga, ad esempio, indica che il conto 555100 è
  intestato ad Antonio Rossi ed ha un identificativo numerico pari a 1 nel nostro sistema;
• transazioni.csv che include i record relativi alle movimentazioni come questa: 2018-01-24;1000;AS;Stipendio gennaio;1. Notiamo che alla
  fine della riga appare il numero 1 che rappresenta l'identificativo del conto su cui la transazione è stata sviluppata: questo serve proprio a stabilire una prima relazione tra tabelle
  per lo sviluppo dei Join. Gli altri dati sono, rispettivamente, data, importo, codice di due caratteri e descrizione;
• codici.csv in cui viene fornita la descrizione per esteso di ogni singolo codice tipo AS;Accredito stipendio. Anche questa tabella serve a
  sviluppare relazioni infatti il codice di due caratteri potrà essere agganciato a quello presente in transazioni.csv.

Quindi, riassumendo, abbiamo tre file con cui possiamo popolare altrettante tabelle tra le quali sussistono due relazioni da sviluppare con JOIN:
quella relativa a numero progressivo di conto corrente tra transazioni e conti e quella basata su codice operazione tra codici e transazioni.

Abbiamo un file, caricamento_dati.sql che permette di creare un database nuovo (cancellato se preesistente) basato sui tre file:

DROP DATABASE IF EXISTS banca CASCADE;
CREATE DATABASE banca;
USE banca;
CREATE TABLE transazioni
(data DATE,
importo FLOAT,
codice STRING,
descrizione STRING,
id_conto INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INPATH 'transazioni.csv' INTO TABLE transazioni;
CREATE TABLE conti
(id INT,
numero STRING,
nome STRING,
cognome STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INPATH 'conti.csv' INTO TABLE conti;
CREATE TABLE codici
(codice STRING,
descrizione STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INPATH 'codici.csv' INTO TABLE codici;

Per attivarlo è sufficiente usare il comando hive:

> hive -f caricamento_dati.sql

JOIN: elaborazioni su più tabelle

Con le operazioni di Join potremo svolgere analisi che coinvolgano più tabelle. Mettiamo a frutto la relazione che lega le tabelle transazioni e codici
stampando per ogni operazione data, importo, codice e descrizione del codice (proveniente dalla tabella codici):

SELECT data, importo, t.codice, c.descrizione FROM transazioni as t JOIN codici as c ON c.codice=t.codice;

Otterremo righe di questo tipo:

2018-01-24      1000.0  AS      Accredito stipendio
2018-01-25      -250.0  BA      Bonifico a
2018-02-01      -300.0  PC      Prelievo bancomat
...
...

Abbiamo usato un Join puro che restituisce solo le righe su cui c'è corrispondenza tra le due tabelle. Oltre alla parola JOIN il punto fondamentale è
ON che introduce la condizione che determina il modo di incrociare i campi tra loro: in questo caso si richiede l'uguaglianza di valori sul campo codice.
Si noti che è stato necessario introdurre degli alias per evitare conflitti tra i campi con medesimo nome tra le due strutture: ciò è stato fatto con la parola chiave
AS con cui abbiamo deciso che chiameremo t la tabella transazioni e c la tabella codici.

Possiamo anche usare più JOIN concatenati per sfruttare tutte le tabelle disponibili:

SELECT nome, cognome, importo, cod.descrizione as descrizione
FROM banca.transazioni as t
JOIN banca.conti as c ON c.id=t.id_conto
JOIN banca.codici as cod ON cod.codice=t.codice

In questo caso otteniamo righe di questo genere:

Antonio Rossi   1000.0  Accredito stipendio
Antonio Rossi   -250.0  Bonifico a
Anna    Bianchi -300.0  Prelievo bancomat
...
...

in cui misceleremo nome e cognome dell'intestatario, prelevati dalla tabella conti, importo preso da transazioni ed infine la descrizione del codice
recuperato dalla tabella codici.

Abbiamo sfruttato lo stesso meccanismo di prima semplicemente scrivendo i Join uno dietro l'altro e, per ognuno di essi, specificando tramite parola chiave
ON quali campi dovevano coincidere tra le tabelle. Anche in questo caso è servito l'uso di alias.

Join asimmetrici

È possibile anche realizzare JOIN asimmetrici dove applicando la parola chiave LEFT al JOIN richiediamo che tutti i record della tabella indicata a sinistra del
JOIN vengano obbligatoriamente espressi, quelli dell'altra tabella verranno collegati solo nel caso in cui si verifichi un'esatta corripondenza:

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

il risultato è quanto si vede:

555100  Antonio Rossi   2018-01-24      1000.0
555100  Antonio Rossi   2018-01-25      -250.0
555100  Antonio Rossi   2018-03-02      -50.0
555100  Antonio Rossi   2018-05-28      1000.0
555100  Antonio Rossi   2018-07-10      -500.0
555100  Antonio Rossi   2018-07-24      1000.0
555200  Anna    Bianchi 2018-02-01      -300.0
555200  Anna    Bianchi 2018-02-25      1000.0
555200  Anna    Bianchi 2018-04-27      1000.0
555200  Anna    Bianchi 2018-05-11      -520.0
555200  Anna    Bianchi 2018-06-01      250.0
555300  Alessio Verdi   2018-03-25      -250.0
555300  Alessio Verdi   2018-03-28      1000.0
555300  Alessio Verdi   2018-05-12      -30.0
555300  Alessio Verdi   2018-06-13      620.0
555400  Simona  Neri    2018-04-11      -300.0
555400  Simona  Neri    2018-06-27      1000.0
555500  Marco   Gialli  NULL    NULL

Si noti che con il Join asimmetrico si è prodotto un record con valori nulli: l'ultimo, 555500 Marco Gialli NULL NULL, dimostra che non sono state
registrate transazioni sul conto numero 555500. Quello di restituire record anche senza corrispondenza è la potenzialità del Join asimmetrico in quanto prende in considerazioni
dati che un Join normale trascurerebbe. Bisogna solo prestare attenzione nel caso in cui si voglia sintetizzare i dati per elaborare valutazioni. Ad esempio, se volessimo sapere
per ogni conto il numero di operazioni che sono state svolte potremmo procedere ad un GROUP BY e un ordinamento applicati sul Join precedente:

SELECT c.numero, c.nome,c.cognome, COUNT(t.data) as operazioni
FROM conti as c LEFT JOIN transazioni as t ON c.id=t.id_conto
GROUP BY c.id, c.numero,c.nome,c.cognome
ORDER BY operazioni DESC;

Questo il risultato:

555100  Antonio Rossi   6
555200  Anna    Bianchi 5
555300  Alessio Verdi   4
555400  Simona  Neri    2
555500  Marco   Gialli  0

Si noti che il conto corrente 555500 non ha visto movimentazioni ma grazie all'uso di un LEFT JOIN è stato elencato comunque nei risultati: qualora
avessimo usato un Join normale, non sarebbe apparsa l'ultima riga:

SELECT c.numero, c.nome,c.cognome, COUNT(t.data) as operazioni
FROM conti as c JOIN transazioni as t ON c.id=t.id_conto
GROUP BY c.id, c.numero,c.nome,c.cognome
ORDER BY operazioni DESC;

Pertanto se lo scopo del Join fosse stato quello di individuare tutti i conti correnti avremmo ottenuto un risultato errato:

555100  Antonio Rossi   6
555200  Anna    Bianchi 5
555300  Alessio Verdi   4
555400  Simona  Neri    2