Hive Pridružite se & SubQuery Vadnica s primeri

Kazalo:

Anonim

V tej vadnici boste izvedeli-

  • Pridružite se poizvedbam
  • Različni tipi pridružitev
  • Podpoizvedbe
  • Vdelava skriptov po meri
  • UDF (uporabniško določene funkcije)

Pridružite se poizvedbam:

Poizvedbe za združevanje lahko izvedete v dveh tabelah, ki sta prisotni v Hive. Za jasno razumevanje konceptov združevanja tukaj ustvarjamo dve tabeli,

  • Sample_joins (povezane s podrobnostmi o kupcih)
  • Sample_joins1 (V zvezi s podrobnostmi naročil, ki jih opravijo zaposleni)

Korak 1) Ustvarjanje tabele "sample_joins" z imeni stolpcev ID, imenom, starostjo, naslovom in plačo zaposlenih

Korak 2) Nalaganje in prikaz podatkov

Z zgornjega posnetka zaslona

  1. Nalaganje podatkov v sample_joins iz Customers.txt
  2. Prikaz vsebine tabele sample_joins

Korak 3) Ustvarjanje tabele sample_joins1 in nalaganje, prikaz podatkov

Iz zgornjega posnetka zaslona lahko opazimo naslednje

  1. Ustvarjanje tabele sample_joins1 s stolpci Orderid, Date1, Id, Amount
  2. Nalaganje podatkov v sample_joins1 iz orders.txt
  3. Prikaz zapisov, ki so prisotni v sample_joins1

V nadaljevanju bomo videli različne vrste združevanj, ki jih je mogoče izvesti v tabelah, ki smo jih ustvarili, pred tem pa morate upoštevati naslednje točke za združitve.

Nekaj ​​točk, ki jih je treba upoštevati pri pridružitvah:

  • V združitvah so dovoljeni samo združevanja po enakovrednosti
  • V isti poizvedbi je mogoče združiti več kot dve tabeli
  • Obstajajo združitve LEVO, DESNO, FULL OUTER, da se zagotovi večji nadzor nad klavzulo ON, za katero ni ujemanja
  • Pridružitve niso komutativne
  • Pridružitve so levo asociativne, ne glede na to, ali gre za LEVO ali DESNO

Različni tipi pridružitev

Pridružitve so 4 vrste, to so

  • Notranje združevanje
  • Levo zunanje Pridružite se
  • Desno zunanje pridruživanje
  • Polno zunanje pridružitev

Notranje pridružitev:

Zapisi, skupni obema tabelama, bodo pridobljeni s to notranjo povezavo.

Iz zgornjega posnetka zaslona lahko opazimo naslednje

  1. Tu izvajamo poizvedbo združevanja s ključno besedo JOIN med tabelama sample_joins in sample_joins1 s pogojem ujemanja kot (c.Id = o.Id).
  2. Izhod, ki prikazuje skupne zapise, prisotne v obeh tabelah, s preverjanjem stanja, omenjenega v poizvedbi

Poizvedba:

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

Levi zunanji priključek:

  • Jezik poizvedb panj LEFT OUTER JOIN vrne vse vrstice iz leve tabele, čeprav v desni tabeli ni zadetkov
  • Če se klavzula ON ujema z nič zapisov v desni tabeli, združevanja še vedno vrnejo zapis rezultata z NULL v vsakem stolpcu iz desne tabele

Iz zgornjega posnetka zaslona lahko opazimo naslednje

  1. Tu izvajamo poizvedbo o pridružitvi s pomočjo ključne besede "LEFT OUTER JOIN" med tabelama sample_joins in sample_joins1 s pogojem ujemanja kot (c.Id = o.Id).

    Tu na primer kot referenco uporabljamo ID zaposlenega, ki preveri, ali je id pogost v desni in levi tabeli ali ne. Deluje kot ujemajoč se pogoj.

  2. Izhod, ki prikazuje skupne zapise, prisotne v obeh tabelah, s preverjanjem stanja, omenjenega v poizvedbi.

    NULL vrednosti v zgornjem izhodu so stolpci brez vrednosti iz desne tabele, ki je sample_joins1

Poizvedba:

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

Desni zunanji spoj:

  • Hive poizvedbeni jezik RIGHT OUTER JOIN vrne vse vrstice iz desne tabele, čeprav v levi tabeli ni zadetkov
  • Če se klavzula ON ujema z nič zapisov v levi tabeli, združevanja še vedno vrnejo zapis rezultata z NULL v vsakem stolpcu iz leve tabele
  • DESNA združevanja vedno vrnejo zapise iz desne tabele in ujemajoče se zapise iz leve tabele. Če leva tabela nima nobenih vrednosti, ki ustrezajo stolpcu, bo na tem mestu vrnila NULL vrednosti.

Iz zgornjega posnetka zaslona lahko opazimo naslednje

  1. Tu izvajamo poizvedbo za pridružitev s pomočjo ključne besede "RIGHT OUTER JOIN" med tabelama sample_joins in sample_joins1 s pogojem ujemanja kot (c.Id = o.Id).
  2. Izhod, ki prikazuje skupne zapise, prisotne v obeh tabelah, s preverjanjem stanja, omenjenega v poizvedbi

Poizvedba :

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

Popolno zunanje združevanje:

Združuje zapise tabel sample_joins in sample_joins1 na podlagi pogoja JOIN, podanega v poizvedbi.

Vrne vse zapise iz obeh tabel in izpolni NULL vrednosti za stolpce, ki manjkajo vrednosti, ki se ujemajo na obeh straneh.

Iz zgornjega posnetka zaslona lahko opazimo naslednje:

  1. Tu izvajamo poizvedbo združevanja z uporabo ključne besede "FULL OUTER JOIN" med tabelama sample_joins in sample_joins1 s pogojem ujemanja kot (c.Id = o.Id).
  2. Izhod, ki prikazuje vse zapise v obeh tabelah s preverjanjem stanja, omenjenega v poizvedbi. Ničelne vrednosti v izhodnih podatkih tukaj označujejo manjkajoče vrednosti iz stolpcev obeh tabel.

Poizvedba

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

Podpoizvedbe:

Poizvedba v poizvedbi je znana kot podpoizvedba. Glavna poizvedba bo odvisna od vrednosti, ki jih vrnejo podpoizvedbe.

Podpoizvedbe lahko razvrstimo v dve vrsti

  • Podpoizvedbe v stavku FROM
  • Podpoizvedbe v stavku WHERE

Kdaj uporabiti:

  • Da dobite določeno vrednost, kombinirano iz dveh vrednosti stolpcev iz različnih tabel
  • Odvisnost vrednosti ene tabele od drugih tabel
  • Primerjalno preverjanje vrednosti enega stolpca iz drugih tabel

Sintaksa:

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

Primer:

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

Tu sta t1 in t2 imeni tabel. Barvna je podpoizvedba, izvedena na tabeli t1. Tu sta a in b stolpca, ki sta dodana v poizvedbo in dodeljena col1. Col1 je vrednost stolpca v glavni tabeli. Ta stolpec "col1" v podpoizvedbi je enakovreden poizvedbi glavne tabele v stolpcu col1.

Vdelava skriptov po meri:

Hive zagotavlja izvedljivost pisanja uporabnikovih skriptov za potrebe odjemalca. Uporabniki lahko napišejo svoj zemljevid in zmanjšajo skripte za zahteve. Ti se imenujejo vdelani skripti po meri. Logična koda je definirana v skriptih po meri in ta skript lahko uporabimo v času ETL.

Kdaj izbrati vdelane skripte:

  • V posebnih zahtevah odjemalca morajo razvijalci pisati in uvajati skripte v Hive
  • Kjer vgrajene funkcije Hive ne bodo delovale za posebne zahteve domene

Za to v Hiveju uporablja člen TRANSFORM za vdelavo skriptov map in reduktorjev.

V tem vdelanem skriptu po meri moramo upoštevati naslednje točke

  • Stolpci bodo pretvorjeni v niz in razmejeni s TAB, preden jih bodo dali uporabniškemu skriptu
  • Standardni izhod uporabniškega skripta bo obravnavan kot stolpci nizov, ločeni s TAB

Vzorec vdelanega skripta,

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;

Iz zgornjega skripta lahko opazimo naslednje

To je le vzorčni skript za razumevanje

  • pv_users je tabela uporabnikov, ki ima polja, kot sta ID uporabnika in datum, kot je omenjeno v map_script
  • Skript reduktorja je določen na datum in število tabel pv_users