Tutorial su join Hive e sottoquery con esempi
David Carter
Unisciti alle query
Le query di join possono essere eseguite su due tabelle presenti in Hive. Per comprendere Partecipa Concepts in chiaro qui stiamo creando due tabelle qui,
- Sample_joins (relativo ai dettagli dei clienti)
- Sample_joins1 (relativo ai dettagli degli ordini effettuati dai dipendenti)
Passo 1) Creazione della tabella “sample_joins” con i nomi delle colonne ID, nome, età, indirizzo e stipendio dei dipendenti
Passo 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
Passo 3) Creazione della tabella sample_joins1 e caricamento, visualizzazione dei dati
Dallo screenshot 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
Più avanti vedremo i diversi tipi di join che possono essere eseguiti sulle tabelle che abbiamo creato, ma prima di procedere è necessario considerare i seguenti punti per i join.
Alcuni punti da osservare nei join:
- Nei join sono consentiti solo i join di uguaglianza
- È possibile unire più di due tabelle nella stessa query
- Esistono join LEFT, RIGHT, FULL OUTER per fornire un maggiore controllo sulla clausola ON per la quale non esiste corrispondenza
- I join non sono commutativi
- I join sono associativi a sinistra indipendentemente dal fatto che siano LEFT o RIGHT
Diversi tipi di join
I join sono di 4 tipi, questi sono
- Unione interna
- Join esterno sinistro
- Giunto esterno destro
- Join esterno completo
Unione interna:
I record comuni ad entrambe le tabelle verranno recuperati da questo Inner Join.
Dallo screenshot 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 la condizione corrispondente come (c.Id= o.Id).
- L'output visualizza i record comuni presenti in entrambe le tabelle controllando 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:
- 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 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 la condizione corrispondente come (c.Id= o.Id).Per esempio qui utilizziamo l'ID dipendente come riferimento, controlla se l'ID è comune sia a destra che a sinistra della tabella oppure no. Agisce come condizione di corrispondenza.
- L'output che mostra i record comuni presenti in entrambe le tabelle controllando la condizione menzionata nella query. I valori NULL nell'output precedente sono colonne senza valori dalla tabella destra 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:
- Il linguaggio di query Hive RIGHT OUTER JOIN restituisce tutte le righe dalla tabella di destra anche se non ci sono corrispondenze nella tabella di 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 della tabella destra e i record corrispondenti della tabella sinistra. Se la tabella di sinistra non ha valori corrispondenti alla colonna, restituirà valori NULL in quella posizione.
Dallo screenshot 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 la condizione corrispondente come (c.Id= o.Id).
- L'output visualizza i record comuni presenti in entrambe le tabelle controllando la condizione menzionata nella query
domanda:
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 la condizione corrispondente come (c.Id= o.Id).
- L'output visualizza tutti i record presenti in entrambe le tabelle controllando la condizione menzionata nella query. I valori null nell'output qui indicano i valori mancanti dalle colonne di entrambe le tabelle.
domanda
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Sottoquery
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 clause SELECT <column names 1, 2…n>From (SubQuery) <TableName_Main > Subquery in WHERE clause SELECT <column names 1, 2…n> From<TableName_Main>WHERE 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 a e b sono colonne 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
Alveare fornisce la possibilità di scrivere script specifici dell'utente per i requisiti del cliente. 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 utilizzare tale script nel tempo ETL.
Quando scegliere gli script incorporati:
- In base ai requisiti specifici del cliente, 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 della mappa che quelli del riduttore.
In questi script personalizzati incorporati, dobbiamo osservare i seguenti punti
- Le colonne verranno trasformate in stringhe e delimitate da TAB prima di consegnarle allo script utente
- L'output standard dello script utente verrà trattato come colonne di stringhe separate da TAB
Script incorporato di esempio,
FROM ( FROM pv_users MAP pv_users.userid, pv_users.date USING 'map_script' AS dt, uid CLUSTER BY dt) map_output INSERT OVERWRITE TABLE pv_users_reduced REDUCE map_output.dt, map_output.uid USING 'reduce_script' AS date, count;
Dallo script soprastante, possiamo osservare quanto segue
Questo è solo lo script di esempio per comprendere
- pv_users è la tabella degli utenti che contiene campi come userid e data come menzionato in map_script
- Script riduttore definito in data e conteggio delle tabelle pv_users