În acest tutorial, veți învăța-
- Alăturați-vă interogărilor
- Diferite tipuri de îmbinări
- Subinterogări
- Încorporarea scripturilor personalizate
- UDF-uri (Funcții de definire a utilizatorului)
Alăturați-vă interogărilor:
Interogările de alăturare se pot efectua pe două tabele prezente în Hive Pentru a înțelege clar conceptele Join, creăm aici două tabele,
- Sample_joins (legat de detaliile clienților)
- Sample_joins1 (Legat de detaliile comenzilor efectuate de angajați)
Pasul 1) Crearea tabelului "sample_joins" cu numele coloanei ID, nume, vârstă, adresă și salariu al angajaților
Pasul 2) Încărcarea și afișarea datelor
Din captura de ecran de mai sus
- Se încarcă datele în sample_joins din Customers.txt
- Se afișează conținutul tabelului sample_joins
Pasul 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, Suma
- Se încarcă datele în sample_joins1 de la orders.txt
- Afișarea înregistrărilor prezente în sample_joins1
Mergând mai departe vom vedea diferite tipuri de asocieri care pot fi efectuate pe tabelele pe care le-am creat, dar înainte de aceasta trebuie să luați în considerare următoarele puncte pentru asocieri.
Câteva puncte de observat în Alăturări:
- Doar asocierile de egalitate sunt permise În asocieri
- Mai mult de două tabele pot fi unite în aceeași interogare
- Stânga, dreapta, FULL OUTER există pentru a oferi mai mult control asupra Clauzei ON pentru care nu există nicio potrivire
- Alăturările nu sunt comutative
- Asocierile sunt asociative la stânga, indiferent dacă sunt asocieri STÂNGA sau DREAPTA
Diferite tipuri de îmbinări
Asocierile sunt de 4 tipuri, acestea sunt
- Alăturare interioară
- Unire exterioară stângă
- Alăturați-vă la dreapta
- Alăturare completă exterioară
Alăturare interioară:
Înregistrările comune ambelor tabele vor fi recuperate de acest Inner Join.
Din captura de ecran de mai sus, putem observa următoarele
- Aici efectuăm o interogare de tip join folosind cuvântul cheie JOIN între tabelele sample_joins și sample_joins1 cu condiția de potrivire ca (c.Id = o.Id).
- Ieșirea care afișează înregistrări comune prezente atât în tabel, verificând starea menționată în interogare
Interogare:
SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);
Alăturare 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 se potrivește cu zero înregistrări în tabelul din dreapta, îmbinările returnează încă 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 o interogare de tip join folosind cuvântul cheie „LEFT OUTER JOIN” între tabele sample_joins și sample_joins1 cu condiția de potrivire ca (c.Id = o.Id).
De exemplu, aici folosim codul de angajat ca referință, verifică dacă id-ul este comun în dreapta, la stânga sau nu în tabel. Acționează ca o condiție potrivită.
- Ieșirea care afișează înregistrări comune prezente atât în tabel, verificând starea menționată în interogare.
Valorile NULL din ieșirea de mai sus sunt coloane fără valori din tabelul din dreapta, care este sample_joins1
Interogare:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Îmbinarea exterioară dreaptă:
- Limbajul de interogare Hive RIGHT OUTER JOIN returnează toate rândurile din tabelul din dreapta, chiar dacă nu există potriviri în tabelul din stânga
- Dacă clauza ON se potrivește cu zero înregistrări în tabelul din stânga, îmbinările returnează încă o înregistrare în rezultat cu NULL în fiecare coloană din tabelul din stânga
- Alăturările DREAPTA returnează întotdeauna înregistrările dintr-un tabel din dreapta și înregistrările 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 o interogare de tip join folosind cuvântul cheie „RIGHT OUTER JOIN” între tabele sample_joins și sample_joins1 cu condiția de potrivire ca (c.Id = o.Id).
- Ieșirea care afișează înregistrări comune prezente atât în tabel, verificând starea menționată în interogare
Interogare :
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Îmbinarea exterioară completă:
Acesta combină înregistrările atât ale tabelelor sample_joins, cât ș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 de ambele părți.
Din captura de ecran de mai sus putem observa următoarele:
- Aici efectuăm o interogare de tip join folosind cuvântul cheie „FULL OUTER JOIN” între tabele sample_joins și sample_joins1 cu condiția de potrivire ca (c.Id = o.Id).
- Ieșirea care afișează toate înregistrările prezente atât în tabel, verificând starea menționată în interogare. Valorile nule din ieșire indică valorile lipsă din coloanele ambelor tabele.
Interogare
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ă sub numele de interogare secundară. Interogarea principală va depinde de valorile returnate de subinterogări.
Subcercetările pot fi clasificate în două tipuri
- Caută în clauza FROM
- Subcercări în clauza WHERE
Când utilizați:
- Pentru a obține o anumită valoare combinată din două valori de coloană din tabele diferite
- Dependența valorilor unui tabel pe alte tabele
- Verificarea comparativă a valorilor unei coloane din alte tabele
Sintaxă:
Subquery in FROM clauseSELECTFrom (SubQuery) Subquery in WHERE clauseSELECT From WHERE col1 IN (SubQuery);
Exemplu:
SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2
Aici t1 și t2 sunt nume de tabele. 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 interogare este echivalentă cu interogarea tabelului principal din coloana col1.
Încorporarea scripturilor personalizate:
Hive 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 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 hartă, cât și scripturi de reducere.
În acest script personalizat încorporat, trebuie să respectăm următoarele puncte
- Coloanele vor fi transformate în șir și delimitate de TAB înainte de a le da scriptului utilizatorului
- Ieșirea standard a scriptului de utilizator va fi tratată ca coloane de șiruri separate de TAB
Exemplu de script încorporat,
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;
Din scriptul de mai sus, putem observa următoarele
Acesta este doar exemplul de script pentru înțelegere
- pv_users este tabelul de utilizatori care are câmpuri precum userid și data așa cum se menționează în map_script
- Script reductor definit la data și numărarea tabelelor pv_users