Tutorial Hive Join & SubQuery cu exemple
David Carter
Intrați în interogări
Interogările de alăturare pot funcționa pe două tabele prezente în Hive. Pentru înțelegerea Join Concepts în mod clar, aici creăm două tabele aici,
- Sample_joins( Legat de detaliile clienților )
- Sample_joins1(Legat de detaliile comenzilor efectuate de Angajați)
Pas 1) Crearea tabelului „sample_joins” cu numele coloanelor ID, Nume, Vârstă, adresa și salariul angajaților
Pas 2) Încărcarea și afișarea datelor
Din captura de ecran de mai sus
- Se încarcă date în sample_joins din Customers.txt
- Se afișează conținutul tabelului sample_joins
Pas 3) Crearea tabelului sample_joins1 și încărcarea, afișarea datelor
Din captura de ecran de mai sus, putem observa următoarele
- Crearea tabelului sample_joins1 cu coloanele Orderid, Date1, Id, Amount
- Se încarcă date în sample_joins1 din orders.txt
- Afișarea înregistrărilor prezente în sample_joins1
Mergând înainte, vom vedea diferite tipuri de îmbinări care pot fi efectuate pe tabelele pe care le-am creat, dar înainte de asta trebuie să luați în considerare următoarele puncte pentru alăturari.
Câteva puncte de observat în Joins:
- În Joinuri sunt permise numai alăturari egale
- Mai mult de două tabele pot fi unite în aceeași interogare
- Există îmbinări LEFT, RIGHT, FULL OUTER pentru a oferi mai mult control asupra clauzei ON pentru care nu există potrivire
- Adunările nu sunt comutative
- Joinurile sunt asociative stânga, indiferent dacă sunt asocieri LEFT sau RIGHT
Diferite tipuri de îmbinări
Imbinarile sunt de 4 tipuri, acestea sunt
- Alăturare interioară
- Left exterior Join
- Alăturați-vă la dreapta
- Alăturare completă exterioară
Alăturare interioară:
Înregistrările comune ambelor tabele vor fi preluate de această unire interioară.
Din captura de ecran de mai sus, putem observa următoarele
- Aici efectuăm interogare de îmbinare folosind cuvântul cheie JOIN între tabelele sample_joins și sample_joins1 cu condiția de potrivire ca (c.Id= o.Id).
- Ieșirea afișează înregistrările comune prezente atât în tabel prin verificarea condiției menționate în interogare
Solicitare:
SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);
Unire exterioară stângă:
- Limbajul de interogare Hive LEFT OUTER JOIN returnează toate rândurile din tabelul din stânga, chiar dacă nu există potriviri în tabelul din dreapta
- Dacă clauza ON corespunde cu zero înregistrări în tabelul din dreapta, îmbinările returnează în continuare o înregistrare în rezultat cu NULL în fiecare coloană din tabelul din dreapta
Din captura de ecran de mai sus, putem observa următoarele
- Aici efectuăm interogare de îmbinare folosind cuvântul cheie „LEFT OUTER JOIN” între tabelele sample_joins și sample_joins1 cu condiția de potrivire ca (c.Id= o.Id).De exemplu aici folosim ID-ul angajatului ca referință, verifică dacă id-ul este comun în tabelul din dreapta și din stânga sau nu. Acționează ca o condiție de potrivire.
- Ieșirea care afișează înregistrări comune prezente în ambele tabel prin verificarea condiției menționate în interogare. Valorile NULL din ieșirea de mai sus sunt coloane fără valori din tabelul din dreapta care este sample_joins1
Solicitare:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Alăturare exterioară dreaptă:
- Limbajul de interogare RIGHT OUTER JOIN returnează toate rândurile din tabelul din dreapta, chiar dacă nu există potriviri în tabelul din stânga
- Dacă clauza ON corespunde cu zero înregistrări în tabelul din stânga, îmbinările returnează în continuare o înregistrare în rezultat cu NULL în fiecare coloană din tabelul din stânga
- Uniunile RIGHT returnează întotdeauna înregistrări dintr-un tabel din dreapta și înregistrări potrivite din tabelul din stânga. Dacă tabelul din stânga nu are valori corespunzătoare coloanei, va returna valori NULL în acel loc.
Din captura de ecran de mai sus, putem observa următoarele
- Aici efectuăm interogare de îmbinare folosind cuvântul cheie „RIGHT OUTER JOIN” între tabelele sample_joins și sample_joins1 cu condiția de potrivire ca (c.Id= o.Id).
- Ieșirea afișează înregistrările comune prezente atât în tabel prin verificarea condiției menționate în interogare
Întrebare:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Îmbinare exterioară completă:
Combină înregistrările ambelor tabele sample_joins și sample_joins1 pe baza condiției JOIN dată în interogare.
Returnează toate înregistrările din ambele tabele și completează valorile NULL pentru coloanele care lipsesc valorile potrivite pe ambele părți.
Din captura de ecran de mai sus putem observa următoarele:
- Aici efectuăm interogare de îmbinare folosind cuvântul cheie „FULL OUTER JOIN” între tabelele sample_joins și sample_joins1 cu condiția de potrivire ca (c.Id= o.Id).
- Ieșirea afișează toate înregistrările prezente în ambele tabel prin verificarea condiției menționate în interogare. Valorile nule din ieșire indică aici valorile lipsă din coloanele ambelor tabele.
Întrebare
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Subinterogări
O interogare prezentă într-o interogare este cunoscută ca subinterogare. Interogarea principală va depinde de valorile returnate de subinterogări.
Subinterogările pot fi clasificate în două tipuri
- Subinterogări în clauza FROM
- Subinterogări în clauza WHERE
Când să utilizați:
- Pentru a obține o anumită valoare combinată din valorile a două coloane din tabele diferite
- Dependența valorilor unui tabel de alte tabele
- Verificarea comparativă a valorilor unei coloane din alte tabele
Sintaxă:
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);
Exemplu:
SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2
Aici t1 și t2 sunt nume de tabel. Cea colorată este Subinterogare efectuată pe tabelul t1. Aici a și b sunt coloane care sunt adăugate într-o subinterogare și atribuite col1. Col1 este valoarea coloanei prezentă în tabelul principal. Această coloană „col1” prezentă în subinterogare este echivalentă cu interogarea tabelului principal din coloana col1.
Încorporarea de scripturi personalizate
Stup oferă fezabilitatea scrierii de scripturi specifice utilizatorului pentru cerințele clientului. Utilizatorii își pot scrie propria hartă și pot reduce scripturile pentru cerințe. Acestea se numesc scripturi personalizate încorporate. Logica de codare este definită în scripturile personalizate și putem folosi acel script în timpul ETL.
Când să alegeți Scripturi încorporate:
- În cerințele specifice clientului, dezvoltatorii trebuie să scrie și să implementeze scripturi în Hive
- Unde funcțiile încorporate Hive nu vor funcționa pentru cerințe specifice domeniului
Pentru aceasta, în Hive, folosește clauza TRANSFORM pentru a încorpora atât scripturi de hărți, cât și de reducere.
În aceste scripturi personalizate încorporate, trebuie să observăm următoarele puncte
- Coloanele vor fi transformate în șir și delimitate de TAB înainte de a le oferi scriptului utilizatorului
- Ieșirea standard a scriptului utilizatorului va fi tratată ca coloane de șir separate de TAB
Exemplu de script încorporat,
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;
Din scriptul de mai sus, putem observa următoarele
Acesta este doar un exemplu de script pentru înțelegere
- pv_users este tabelul utilizatori care are câmpuri precum userid și data, așa cum este menționat în map_script
- Script reductor definit pe data și numărul tabelelor pv_users
