Operatori e funzioni di built-in

Durante le operazioni di query, si possono richiedere velocemente calcoli ed elaborazioni sfruttando gli operatori e le funzioni built-in ovvero
quelli già inclusi nella piattaforma Hive. In questa lezione, li esploriamo nelle varie categorie accompagnandoci con esempi pratici.

Operatori built-in

Iniziamo con gli operatori built-in, tematica che non dovrebbe comportare grosse difficoltà in quanto si tratta di elementi presenti in tutti i linguaggi di programmazione.
Ciò che Hive include in questo ambito sono:

• operatori aritmetici: includono tutto ciò che serve per svolgere le tipiche operazioni aritmetiche. In primis, ci sono i simboli
  +, -, * e / che, rispettivamente, rappresentano le operazioni di somma, sottrazione, moltiplicazione e divisione. A questi si aggiungono l'operatore DIV che restituisce la parte
  intera del risultato di una divisione (17 DIV 3 restituisce 5), e % che fornisce il resto di una divisione (17 % 3 risulta 2);
• operatori bitwise: una categoria di operatori aritmetici che permettono di svolgere le operazioni bit a bit. Troviamo
  & per l'operazione di AND (solo se due bit corrispondenti sono entrambi pari a 1, il bit risultato sarà 1 altrimenti sempre zero: 12 & 9 = 8). L'operatore
  OR è rappresentato con | e determina che un solo bit a 1 tra i due è sufficiente per ottenere 1 come risultato (12 | 9 = 13), mentre lo XOR si rappresenta con ^ e
  fornisce 1 come risultato solo se gli operandi sono una coppia di bit di valore diverso (12 ^ 9 = 5). Con il simbolo ~ si ottiene l'operatore NOT che inverte
  il valore dei bit in un dato (~8 = -9);
• operatori di confronto e operatori logici sono già stati incontrati in una lezione precedente a proposito della clausola WHERE;
• operatore di concatenazione per le stringhe: il simbolo || può essere utilizzato a tale scopo, in alternativa alla funzione CONCAT che vedremo nel
  seguito della lezione.

Funzioni built-in

Le funzioni built-in possono essere utilizzate negli ambiti più disparati. Per gli esempi che seguono supporremo di avere una tabella di questo tipo:

Rossi Andrea,M,78,890,1995-05-21
Bianchi Silvia,F,99,670,1999-01-14
Neri Eleonora,F,83,920,1996-08-03
Gialli Paolo,M,92,1020,1992-06-24
Bianchi Vittorio,M,98,750,1993-10-18

che rappresenta i dati relativi ad allievi che hanno superato un corso professionale. Per ognuno sono stati riportati nominativo, sesso (indicato
con le lettere M o F a seconda che si tratti di maschi o femmine), voto finale in centesimi, importo pagato per il corso (li supporremo diversificati in virtù di agevolazioni, convenzioni e sconti
vari) e data di nascita nel formato YYYY-MM-GG (anno su quattro cifre, mese su due e giorno su due, suddivisi da trattini). Questa la struttura
della tabella su cui saranno importati:

CREATE TABLE corso
(allievo STRING,
genere STRING,
voto_finale INT,
importo FLOAT,
nascita DATE)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

Tra le funzioni più comuni sfruttate nelle query ci sono le funzioni di aggregazione che forniscono risultati a partire dai dati presenti nei campi di una tabella.

Con la funzione COUNT possiamo chiedere il numero di record che compongono il risultato di una query. La seguente interrogazione restituirà il numero
di allievi iscritti al corso:

SELECT COUNT(*) FROM corso;

Con le funzioni MIN, MAX, AVG e SUM si possono ottenere, rispettivamente, il minimo, il massimo, la media e la
somma di un insieme di valori. La seguente query ispeziona i dati nella query restituendo ampiezza dell'intervallo tra il voto massimo e quello minimo,
la media delle valutazioni e la somma degli importi pagati dagli allievi:

SELECT MAX(voto_finale)-MIN(voto_finale), AVG(voto_finale), SUM(importo) FROM corso;

con cui otterremo i seguenti valori:

21      90.0    4250.0

Esistono diverse altre funzioni di aggregazione, per lo più a carattere statistico come: variance, var_pop e var_samp per la varianza,
stdev_pop e std_samp dedicate alla deviazione standard, percentile per i percentili, corr per la correlazione nonché
regr_intercept, regr_slope e regr_r2 per quanto riguarda la regressione lineare.

Altro settore molto utile riguarda la manipolazione ed elaborazione di informazioni data/ora. Il seguente esempio mostra tre di queste,
YEAR, MONTH e DAY che recuperano da un campo di tipo DATE i valori dell'anno, del mese e del giorno.
Eccone un esempio:

SELECT nascita, YEAR(nascita), MONTH(nascita), DAY(nascita) FROM corso;

Eseguendo la query otteniamo le seguenti righe per gli allievi che hanno sostenuto il corso:

1995-05-21      1995    5       21
1999-01-14      1999    1       14
1996-08-03      1996    8       3
1992-06-24      1992    6       24
1993-10-18      1993    10      18

Anche di questa categoria di funzioni ne esistono molte altre come hour, minute e second per ore, minuti e secondi, datediff e
date_add per l'aritmetica tra date oppure current_date e current_timestamp per ottenere data attuale e timestamp attuale.

Anche il gruppo delle funzioni per le stringhe è particolarmente ricco. Oltre all'operatore di concatenazione visto in precedenza, si può usare
la funzione CONCAT. Se volessimo mostrare il voto di ogni allievo riportando l'annotazione in centesimi potremmo procedere così:

SELECT allievo, CONCAT(voto_finale,'/100') FROM corso;

ottenendo:

Rossi Andrea       78/100
Bianchi Silvia     99/100
Neri Eleonora      83/100
Gialli Paolo       92/100
Bianchi Vittorio   98/100

La query che segue mostra alcune funzioni per le stringhe impiegate per la generazione (semplificata a scopo di esempio) di username e password per ogni allievo.
Lo username sarà costituito dai primi tre caratteri del nominativo seguiti dall'anno di nascita mentre la password proverrà da un segmento del codice
MD5 calcolato a partire dalla concatenazione di nome e data di nascita, con la funzione substr otterremo una sottostringa, selezionata in base al numero di caratteri,
mentre con lower otterremo la versione minuscola di una stringa (esiste anche upper per il maiuscolo):

SELECT allievo, substr(lower(allievo),0,3)||YEAR(nascita), substr(md5(allievo||nascita),10,12) FROM corso;

Questi i dati generati:

Rossi Andrea        ros1995   e9154fe7f0bf
Bianchi Silvia      bia1999   c0e8200bf4ca
Neri Eleonora       ner1996   2ffd698f334d
Gialli Paolo        gia1992   25efdae1f94c
Bianchi Vittorio    bia1993   bea9ccd9667e

Come ultima categoria analizziamo le funzioni condizionali. Con IF possiamo valutare una condizione e definire quale valore sarà
restituito in caso questa sia TRUE e quale in caso contrario. Con:

SELECT allievo,voto_finale, IF(voto_finale>=85, 'IDONEO', 'NON IDONEO') FROM corso;

assegniamo ad ogni allievo l'attributo di IDONEO o NON IDONEO in base al raggiungimento o meno di una valutazione minima di 85 centesimi. Questo il set dei risultati:

Rossi Andrea        78      NON IDONEO
Bianchi Silvia      99      IDONEO
Neri Eleonora       83      NON IDONEO
Gialli Paolo        92      IDONEO
Bianchi Vittorio    98      IDONEO

Per valori specifici ma più variegati si può chiamare in causa il costrutto CASE...WHEN...THEN...END. Con CASE specifichiamo il nome di un
campo e con i vari blocchi WHEN...THEN (possono essere ripetuti più volte) indichiamo quale valore deve essere restituito in corrispondenza di un determinato
dato presente nel campo. Al termine di tutto riportiamo un END per chiudere il blocco. Qualora esistesse una possibilità di default, la si può introdurre
con la parola chiave ELSE al termine della sequenza di WHEN...THEN.

Come ultimo esempio richiediamo la stampa della stringa MASCHIO in corrispondenza di allievi maschi e FEMMINA in corrispondenza delle femmine:

SELECT allievo, CASE genere WHEN 'M' THEN 'MASCHIO' WHEN 'F' THEN 'FEMMINA' END FROM corso;

Ecco cosa si riceve:

Rossi Andrea        MASCHIO
Bianchi Silvia      FEMMINA
Neri Eleonora       FEMMINA
Gialli Paolo        MASCHIO
Bianchi Vittorio    MASCHIO