Hive Join & SubQuery Tutorial con esempi

In questo tutorial imparerai-

Unisci le query
Diversi tipi di join
Domande secondarie
Incorporamento di script personalizzati
UDF (funzioni definite dall'utente)

Unisci le query:

Le query di join possono essere eseguite su due tabelle presenti in Hive. Per comprendere i concetti di Join in modo chiaro, qui stiamo creando due tabelle sopra,

Sample_joins (correlato ai dettagli dei clienti)
Sample_joins1 (correlato ai dettagli degli ordini eseguiti dai dipendenti)

Passaggio 1) Creazione della tabella "sample_joins" con ID nomi colonna, nome, età, indirizzo e stipendio dei dipendenti

Passaggio 2) Caricamento e visualizzazione dei dati

Dalla schermata sopra

Caricamento dei dati in sample_joins da Customers.txt
Visualizzazione del contenuto della tabella sample_joins

Passaggio 3) Creazione della tabella sample_joins1 e caricamento, visualizzazione dei dati

Dallo screenshot qui sopra, possiamo osservare quanto segue

Creazione della tabella sample_joins1 con le colonne Orderid, Date1, Id, Amount
Caricamento dei dati in sample_joins1 da orders.txt
Visualizzazione dei record presenti in sample_joins1

Andando avanti vedremo diversi tipi di join che possono essere eseguiti su tabelle che abbiamo creato, ma prima devi considerare i seguenti punti per i join.

Alcuni punti da osservare in Joins:

Solo i join di uguaglianza sono consentiti nei join
È possibile unire più di due tabelle nella stessa query
LEFT, RIGHT, FULL OUTER join esistono per fornire un maggiore controllo sulla clausola ON per la quale non c'è corrispondenza
I join non sono commutativi
I join sono associativi a sinistra indipendentemente dal fatto che siano a SINISTRA o a DESTRA

Diversi tipi di join

I join sono di 4 tipi, questi sono

Unione interna
Join esterno sinistro
Right Outer Join
Join esterno completo

Inner Join:

I record comuni a entrambe le tabelle verranno recuperati da questo Inner Join.

Dallo screenshot qui sopra, possiamo osservare quanto segue

Qui stiamo eseguendo una query di join utilizzando la parola chiave JOIN tra le tabelle sample_joins e sample_joins1 con condizione di corrispondenza come (c.Id = o.Id).
L'output mostra i record comuni presenti in entrambe le tabelle verificando la condizione menzionata nella query

Query:

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

Join esterno sinistro:

Il linguaggio di query Hive LEFT OUTER JOIN restituisce tutte le righe della tabella di sinistra anche se non ci sono corrispondenze nella tabella di destra
Se la clausola ON corrisponde a zero record nella tabella di destra, i join restituiscono comunque un record nel risultato con NULL in ogni colonna della tabella di destra

Dallo screenshot qui sopra, possiamo osservare quanto segue

Qui stiamo eseguendo una query di join utilizzando la parola chiave "LEFT OUTER JOIN" tra le tabelle sample_joins e sample_joins1 con condizione di corrispondenza come (c.Id = o.Id).
Ad esempio, qui stiamo usando l'ID dipendente come riferimento, controlla se l'ID è comune sia a destra che a sinistra della tabella o meno. Agisce come condizione di corrispondenza.
L'output che mostra i record comuni presenti in entrambe le tabelle verificando la condizione menzionata nella query.
I valori NULL nell'output precedente sono colonne senza valori dalla tabella Right che è sample_joins1

Query:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Join esterno destro:

Linguaggio query Hive RIGHT OUTER JOIN restituisce tutte le righe della tabella Right anche se non ci sono corrispondenze nella tabella sinistra
Se la clausola ON corrisponde a zero record nella tabella di sinistra, i join restituiscono comunque un record nel risultato con NULL in ogni colonna della tabella di sinistra
I join RIGHT restituiscono sempre i record da una tabella Right e i record corrispondenti dalla tabella sinistra. Se la tabella di sinistra non ha valori corrispondenti alla colonna, restituirà valori NULL in quella posizione.

Dallo screenshot qui sopra, possiamo osservare quanto segue

Qui stiamo eseguendo una query di join utilizzando la parola chiave "RIGHT OUTER JOIN" tra le tabelle sample_joins e sample_joins1 con condizione di corrispondenza come (c.Id = o.Id).
L'output mostra i record comuni presenti in entrambe le tabelle verificando la condizione menzionata nella query

Query :

 SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Unione esterna completa:

Combina i record di entrambe le tabelle sample_joins e sample_joins1 in base alla condizione JOIN fornita nella query.

Restituisce tutti i record di entrambe le tabelle e inserisce i valori NULL per le colonne con valori mancanti corrispondenti su entrambi i lati.

Dallo screenshot qui sopra possiamo osservare quanto segue:

Qui stiamo eseguendo una query di join utilizzando la parola chiave "FULL OUTER JOIN" tra le tabelle sample_joins e sample_joins1 con condizione di corrispondenza come (c.Id = o.Id).
L'output che mostra tutti i record presenti in entrambe le tabelle verificando la condizione indicata nella query. I valori nulli nell'output qui indicano i valori mancanti dalle colonne di entrambe le tabelle.

Query

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Domande secondarie:

Una query presente all'interno di una query è nota come sottoquery. La query principale dipenderà dai valori restituiti dalle sottoquery.

Le sottoquery possono essere classificate in due tipi

Sottoquery nella clausola FROM
Sottoquery nella clausola WHERE

Quando usare:

Per ottenere un valore particolare combinato da due valori di colonna di tabelle diverse
Dipendenza dei valori di una tabella da altre tabelle
Controllo comparativo dei valori di una colonna da altre tabelle

Sintassi:

Subquery in FROM clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE col1 IN (SubQuery);

Esempio:

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

Qui t1 e t2 sono nomi di tabelle. Quello colorato è la Subquery eseguita sulla tabella t1. Qui aeb sono colonne che vengono aggiunte in una sottoquery e assegnate a col1. Col1 è il valore della colonna presente nella tabella principale. Questa colonna "col1" presente nella sottoquery è equivalente alla query della tabella principale nella colonna col1.

Incorporamento di script personalizzati:

Hive offre la possibilità di scrivere script specifici dell'utente per i requisiti del client. Gli utenti possono scrivere la propria mappa e ridurre gli script per i requisiti. Questi sono chiamati script personalizzati incorporati. La logica di codifica è definita negli script personalizzati e possiamo usare quello script nel tempo ETL.

Quando scegliere gli script incorporati:

Nei requisiti specifici del client, gli sviluppatori devono scrivere e distribuire script in Hive
Dove le funzioni integrate di Hive non funzioneranno per requisiti di dominio specifici

Per questo in Hive utilizza la clausola TRANSFORM per incorporare sia gli script di mappa che di riduttore.

In questo script personalizzati incorporati, dobbiamo osservare i seguenti punti

Le colonne verranno trasformate in stringa e delimitate da TAB prima di assegnarle allo script dell'utente
L'output standard dello script utente verrà trattato come colonne di stringhe separate da tabulazioni

Script incorporato di esempio,

FROM (FROM pv_usersMAP pv_users.userid, pv_users.dateUSING 'map_script'AS dt, uidCLUSTER BY dt) map_outputINSERT OVERWRITE TABLE pv_users_reducedREDUCE map_output.dt, map_output.uidUSING 'reduce_script'AS date, count;

Dallo script sopra, possiamo osservare quanto segue

Questo è solo lo script di esempio per la comprensione

pv_users è la tabella degli utenti che contiene campi come userid e date come menzionato in map_script
Script riduttore definito in data e conteggio delle tabelle pv_users