Hive Join & SubQuery Tutorial ja esimerkkejä
Liity kyselyihin
Liittymiskyselyt voidaan suorittaa kahdessa Hiven taulukossa. Ymmärtämisen vuoksi Liity Concepts on selvää, että luomme tänne kaksi taulukkoa,
- Sample_joins (liittyy asiakkaiden tietoihin)
- Sample_joins1 (liittyy työntekijöiden tekemiin tilaustietoihin)
Vaihe 1) Taulukon "sample_joins" luominen sarakkeiden nimillä ID, nimi, ikä, työntekijöiden osoite ja palkka
Vaihe 2) Tietojen lataaminen ja näyttäminen
Yllä olevasta kuvakaappauksesta
- Ladataan tietoja sample_joins -tiedostoon Customers.txt-tiedostosta
- Näytetään sample_joins -taulukon sisältö
Vaihe 3) sample_joins1 -taulukon luominen ja tietojen lataaminen, näyttäminen
Yllä olevasta kuvakaappauksesta voimme havaita seuraavan
- Taulukon sample_joins1 luominen sarakkeilla Orderid, Date1, Id, Amount
- Ladataan tietoja tiedoston sample_joins1 tiedostosta orders.txt
- Näytetään tietueet, jotka ovat kohdassa sample_joins1
Jatkossa tulemme näkemään erilaisia liitoksia, jotka voidaan suorittaa luomissamme taulukoissa, mutta ennen sitä sinun on harkittava seuraavia liitoskohtia.
Muutamia huomioitavia kohtia liitoksissa:
- Vain tasa-arvoliitokset ovat sallittuja In Joinsissa
- Useampi kuin kaksi taulukkoa voidaan yhdistää samaan kyselyyn
- LEFT, RIGHT, FULL ULKO-liitokset ovat olemassa, jotta voidaan hallita paremmin ON-lauseketta, jolle ei ole vastaavaa
- Liitokset eivät ole kommutatiivisia
- Liitokset ovat vasen-assosiatiivisia riippumatta siitä, ovatko ne LEFT- vai RIGHT-liitoksia
Erilaiset liitokset
Liitoksia on 4 tyyppiä, nämä ovat
- Sisäinen liittyminen
- Vasen ulompi liitos
- Oikea ulompi liitos
- Täysi ulkoinen liittyminen
Sisäinen liittyminen:
Molemmille taulukoille yhteiset tietueet haetaan tällä sisäisellä liityksellä.
Yllä olevasta kuvakaappauksesta voimme havaita seuraavan
- Tässä suoritamme liittymiskyselyn käyttämällä JOIN-avainsanaa taulukoiden sample_joins ja sample_joins1 välissä ja vastaavuusehto on (c.Id= o.Id).
- Tulos, joka näyttää yleisiä tietueita molemmissa taulukoissa, tarkistamalla kyselyssä mainitun ehdon
kysely:
SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);
Vasen ulkoliitos:
- Hive-kyselyn kieli LEFT OUTER JOIN palauttaa kaikki rivit vasemmasta taulukosta, vaikka oikeanpuoleisessa taulukossa ei ole osumia
- Jos ON-lause vastaa nollaa oikeanpuoleisen taulukon tietuetta, liitokset palauttavat silti tietueen tulokseen, jonka jokaisessa sarakkeessa oikeanpuoleisesta taulukosta on NULL.
Yllä olevasta kuvakaappauksesta voimme havaita seuraavan
- Tässä suoritamme liitoskyselyn käyttämällä "LEFT OUTER JOIN" -avainsanaa taulukoiden sample_joins ja sample_joins1 välissä ja vastaa ehtoa (c.Id= o.Id).Esimerkiksi tässä käytämme työntekijätunnusta viitteenä, se tarkistaa, onko id yhteinen sekä oikealla että vasemmalla taulukossa vai ei. Se toimii vastaavana ehtona.
- Tulos, joka näyttää molemmissa taulukossa olevat yleiset tietueet tarkistamalla kyselyssä mainitun ehdon. NULL-arvot yllä olevassa lähdössä ovat sarakkeita, joissa ei ole arvoja oikeasta taulukosta, joka on sample_joins1
kysely:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Oikea ulompi liitos:
- Hive-kyselykieli RIGHT OUTER JOIN palauttaa kaikki rivit oikeanpuoleisesta taulukosta, vaikka vasemmassa taulukossa ei ole yhtään osumaa
- Jos ON-lause vastaa nollaa tietuetta vasemmassa taulukossa, liitokset palauttavat silti tietueen tulokseen, jonka jokaisessa sarakkeessa on NULL vasemmasta taulukosta.
- RIGHT-liitokset palauttavat aina tietueet oikeasta taulukosta ja vastaavat tietueet vasemmasta taulukosta. Jos vasemmassa taulukossa ei ole saraketta vastaavia arvoja, se palauttaa NULL-arvot kyseiseen paikkaan.
Yllä olevasta kuvakaappauksesta voimme havaita seuraavan
- Tässä suoritamme liittymiskyselyn käyttämällä "RIGHT OUTER JOIN" -avainsanaa taulukoiden sample_joins ja sample_joins1 välissä ja vastaa ehtoa (c.Id= o.Id).
- Tulos, joka näyttää yleisiä tietueita molemmissa taulukoissa, tarkistamalla kyselyssä mainitun ehdon
Kysymys:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Täysi ulkoinen liitos:
Se yhdistää sekä taulukoiden sample_joins että sample_joins1 tietueet kyselyssä annetun JOIN-ehdon perusteella.
Se palauttaa kaikki tietueet molemmista taulukoista ja täyttää NULL-arvot sarakkeille, joista puuttuu kummankin puolen arvot.
Yllä olevasta kuvakaappauksesta voimme havaita seuraavan:
- Tässä suoritamme liitoskyselyn käyttämällä avainsanaa "FULL OUTER JOIN" taulukoiden sample_joins ja sample_joins1 välissä ja vastaa ehtoa (c.Id= o.Id).
- Tulos, joka näyttää kaikki molemmissa taulukossa olevat tietueet tarkistamalla kyselyssä mainitun ehdon. Nolla-arvot tulosteessa osoittavat puuttuvat arvot molempien taulukoiden sarakkeista.
Kysymys
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Alakyselyt
Kyselyssä oleva kysely tunnetaan alikyselynä. Pääkysely riippuu alikyselyiden palauttamista arvoista.
Alakyselyt voidaan luokitella kahteen tyyppiin
- Alikyselyt FROM-lauseessa
- Alakyselyt WHERE-lauseessa
Milloin käyttää:
- Tietyn arvon yhdistäminen kahdesta sarakearvosta eri taulukoista
- Yhden taulukon arvojen riippuvuus muista taulukoista
- Yhden sarakkeen arvojen vertaileva tarkistus muista taulukoista
Syntaksi:
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);
Esimerkiksi:
SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2
Tässä t1 ja t2 ovat taulukoiden nimiä. Värillinen on taulukolle t1 suoritettu alikysely. Tässä a ja b ovat sarakkeita, jotka lisätään alikyselyyn ja osoitetaan sarakkeelle 1. Col1 on päätaulukon sarakkeen arvo. Tämä alikyselyssä oleva sarake "col1" vastaa päätaulukon kyselyä sarakkeessa col1.
Mukautettujen komentosarjojen upottaminen
Hive tarjoaa mahdollisuuden kirjoittaa käyttäjäkohtaisia komentosarjoja asiakkaan vaatimuksia varten. Käyttäjät voivat kirjoittaa oman karttansa ja vähentää skriptejä tarpeiden mukaan. Näitä kutsutaan upotetuiksi mukautetuiksi komentosarjoiksi. Koodauslogiikka määritellään mukautetuissa skripteissä ja voimme käyttää kyseistä komentosarjaa ETL-ajassa.
Milloin upotetut komentosarjat kannattaa valita:
- Asiakaskohtaisissa vaatimuksissa kehittäjien on kirjoitettava ja otettava käyttöön skriptejä Hivessä
- Missä Hiven sisäänrakennetut toiminnot eivät toimi tietyissä verkkotunnuksen vaatimuksissa
Tätä varten Hivessa se käyttää TRANSFORM-lausetta upotettuihin sekä kartta- että vähennysskripteihin.
Tässä upotetussa mukautetussa komentosarjassa meidän on huomioitava seuraavat seikat
- Sarakkeet muunnetaan merkkijonoksi ja ne erotetaan SARKAIMELLA ennen sen antamista käyttäjän komentosarjalle
- Käyttäjäkomentosarjan vakiotulostusta käsitellään sarkaimilla erotettuina merkkijonosarakkeina
Esimerkki upotetusta skriptistä,
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;
Yllä olevasta skriptistä voimme havaita seuraavan
Tämä on vain esimerkkikäsikirjoitus ymmärtämistä varten
- pv_users on käyttäjätaulukko, jossa on kentät, kuten käyttäjätunnus ja päivämäärä, kuten map_scriptissa mainitaan
- Pv_users-taulukoiden päivämäärän ja lukumäärän mukaan määritetty supistinkomentosarja