Hive Join & SubQuery Tutorial ja esimerkkejä

Liity kyselyihin

Liitoskyselyitä voidaan suorittaa kahdelle taulukolle, jotka ovat läsnä HiveYmmärtääksemme liitosten käsitteet selkeästi, luomme tähän kaksi taulukkoa:

• sample_joins (asiakastietoihin liittyvä)
• sample_joins1 (liittyy työntekijöiden tekemiin tilaustietoihin)

Vaihe 1) Taulukon ”sample_joins” luonti sarakenimillä Id, Name, Age, Address ja Palkka. Alla olevassa kuvakaappauksessa näkyy CREATE TABLE -komento ja sen vahvistus.

Vaihe 2) Tietojen lataaminen ja näyttäminen. Seuraavassa kuvakaappauksessa näkyy latauskomento ja sen jälkeen taulukon sisältö.

Yllä olevasta kuvakaappauksesta:

1. Ladataan tietoja sample_joins -tiedostoon Customers.txt-tiedostosta
2. Näytetään sample_joins -taulukon sisältö

Vaihe 3) sample_joins1-taulukon luominen ja sen tietojen lataaminen ja näyttäminen alla olevan kuvakaappauksen mukaisesti.

Yllä olevasta kuvakaappauksesta voimme havaita seuraavaa:

1. Taulukon sample_joins1 luonti sarakkeilla Orderid, Date1, Id ja Amount
2. Ladataan tietoja tiedoston sample_joins1 tiedostosta orders.txt
3. Näytetään tietueet, jotka ovat kohdassa sample_joins1

Jatkossa tarkastelemme erilaisia ​​liitostyyppejä, joita voidaan suorittaa luomillemme taulukoille. Ennen sitä on otettava huomioon seuraavat liitoksiin liittyvät seikat.

Joitakin huomioitavia seikkoja liitoksissa:

• Liitoksissa sallitaan vain yhtäsuuruusliitokset
• Useampi kuin kaksi taulukkoa voidaan yhdistää samaan kyselyyn
• LEFT-, RIGHT- ja FULL OUTER -liitokset on olemassa, jotta voidaan hallita paremmin ON-lauseketta, jolle ei ole vastinetta.
• Liitokset eivät ole vaihdannaisia
• Liitokset ovat vasen-assosiatiivisia riippumatta siitä, ovatko ne LEFT- vai RIGHT-liitoksia

Tasa-arvorajoitus heijastaa Hiveä sellaisena kuin se oli useiden vuosien ajan. Hive 2.2.0:sta eteenpäin ON-lausekkeen monimutkaisia ​​lausekkeita tuetaan (HIVE-15211), joten ei-tasa-arvoehto hyväksytään nykyisessä versiossa. Vanhemmissa versioissa ehdon on oltava tasa-arvotesti, ja kaikki muu siirretään WHERE-lausekkeeseen.

Erilaiset liitokset

Liitoksia on neljää tyyppiä. Nämä ovat:

• Sisäinen liittyminen
• Vasen ulompi liitos
• Oikea ulompi liitos
• Täysi ulkoliitos

Kutakin tyyppiä esitellään alla samoja kahta taulukkoa vasten, joten esimerkkien välillä muuttuu vain se, mitkä yhteensopimattomat rivit säilyvät.

Sisäinen liittyminen

Molemmille taulukoille yhteiset tietueet noudetaan tällä sisäisellä liitoksella. Alla olevan kuvakaappauksen tulos sisältää vain ne asiakkaat, joilla on vastaava tilaus.

Yllä olevasta kuvakaappauksesta voimme havaita seuraavaa:

1. Tässä suoritamme liitoskyselyn käyttämällä JOIN-avainsanaa taulukoiden sample_joins ja sample_joins1 välillä vastaavuusehdolla (c.Id = o.Id).
2. Tuloste näyttää molemmissa taulukoissa olevat yhteiset tietueet, jotka on valittu kyselyssä mainitun ehdon perusteella.

kysely:

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

Vasen ulompi liittymä

• HiveQL LEFT OUTER JOIN palauttaa kaikki vasemman taulukon rivit, vaikka oikean taulukon rivillä ei olisikaan osumia.
• Jos ON-lauseke ei vastaa oikean taulukon tietueita, liitos palauttaa silti tulokseen tietueen, jonka jokaisessa oikean taulukon sarakkeessa on NULL.

Alla olevassa kuvakaappauksessa näkyy, että kaikki asiakkaat näkyvät, myös ne, joilla ei ole tilausta.

Yllä olevasta kuvakaappauksesta voimme havaita seuraavaa:

1. Tässä suoritamme liitoskyselyn käyttämällä avainsanaa ”LEFT OUTER JOIN” taulukoiden sample_joins ja sample_joins1 välille ja vastaavuusehtoa (c.Id = o.Id). Esimerkiksi tässä käytämme työntekijän tunnusta viitteenä; se tarkistaa, onko tunnus yhteinen sekä oikean että vasemman taulukon kanssa. Se toimii vastaavuusehtona.
2. Tuloste näyttää kyselyssä mainitun ehdon valitsemat tietueet. Yllä olevan tulosteen NULL-arvot ovat sarakkeita, joissa ei ole arvoja oikeanpuoleisesta taulukosta, eli sample_joins1:stä.

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

• HiveQL RIGHT OUTER JOIN palauttaa kaikki rivit oikeasta taulukosta, vaikka vasemmassa taulukossa ei olisikaan osumia
• Jos ON-lauseke ei vastaa vasemmanpuoleisessa taulukossa olevia tietueita, liitos palauttaa silti tulokseen tietueen, jonka jokaisessa vasemmanpuoleisen taulukon sarakkeessa on NULL.
• OIKEAT liitokset palauttavat aina tietueet oikeanpuoleisesta taulukosta ja vastaavat tietueet vasemmanpuoleisesta taulukosta. Jos vasemmanpuoleisessa taulukossa ei ole saraketta vastaavaa arvoa, se palauttaa kyseiseen kohtaan NULL-arvoja.

Alla oleva kuvakaappaus näyttää edellisen tuloksen peilikuvan: jokainen tilaus tulee näkyviin, täsmäämisestä riippumatta.

Yllä olevasta kuvakaappauksesta voimme havaita seuraavaa:

1. Tässä suoritamme liitoskyselyn käyttämällä avainsanaa ”RIGHT OUTER JOIN” taulukoiden sample_joins ja sample_joins1 välillä vastaavuusehdolla (c.Id = o.Id).
2. Tuloste näyttää kyselyssä mainitun ehdon perusteella valitut tietueet.

kysely:

  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 liittyminen

Se yhdistää sekä sample_joins- että sample_joins1-taulukoiden tietueet kyselyssä annetun JOIN-ehdon perusteella.

Se palauttaa kaikki tietueet molemmista taulukoista ja täyttää NULL-arvoilla sarakkeet, joiden vastaavat arvot puuttuvat kummaltakin puolelta, kuten alla oleva kuvakaappaus osoittaa.

Yllä olevasta kuvakaappauksesta voimme havaita seuraavaa:

1. Tässä suoritamme liitoskyselyn käyttämällä avainsanaa ”FULL OUTER JOIN” taulukoiden sample_joins ja sample_joins1 välillä vastaavuusehdolla (c.Id = o.Id).
2. Tuloste näyttää kaikki molemmissa taulukoissa olevat tietueet, jotka on valittu kyselyssä mainitun ehdon perusteella. Tulosteen NULL-arvot osoittavat tässä puuttuvat arvot molempien taulukoiden sarakkeista.

kysely:

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

Liitokset asettavat taulukoita vierekkäin. Alikysely tekee jotain erilaista: se sijoittaa yhden kyselyn toisen sisään, jotta ulompi kysely voi toimia jo lasketun tuloksen pohjalta.

Kyselyn sisällä olevaa kyselyä kutsutaan alikyselyksi. Pääkysely riippuu alikyselyn palauttamista arvoista.

Alikyselyt voidaan luokitella kahteen tyyppiin:

• FROM-lausekkeen alikyselyt
• Alikyselyt WHERE-lausekkeessa

Milloin käyttää:

• Tietyn arvon yhdistäminen kahdesta sarakearvosta eri taulukoista
• Yhden taulukon arvojen riippuvuus muista taulukoista
• Yhden sarakkeen arvojen vertaileva tarkistaminen muihin taulukoihin

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ä. Sisäinen lauseke on taulukolle t1 suoritettava alikysely. Tässä a ja b ovat sarakkeita, jotka lisätään alikyselyyn ja jotka on määritetty sarakkeeseen col1. Col1 on päätaulukossa olevan sarakkeen arvo. Alikyselyssä oleva sarake ”col1” vastaa päätaulukon sarakkeen col1 kyselyä.

Mukautettujen komentosarjojen upottaminen

Kun alikysely muokkaa dataa pelkällä HiveQL:llä, upotettu skripti luovuttaa rivit Hiven ulkopuolella kirjoitetulle koodille.

Hive mahdollistaa käyttäjäkohtaisten skriptien kirjoittamisen asiakasvaatimuksia varten. Käyttäjät voivat kirjoittaa oman map-mapin ja lyhentää skriptejä näitä vaatimuksia varten. Näitä kutsutaan upotetuiksi mukautetuiksi skripteiksi. Koodauslogiikka määritellään mukautetussa skriptissä, ja voimme käyttää kyseistä skriptiä ETL-aikana.

Milloin valita upotetut skriptit:

• Asiakaskohtaiset vaatimukset tarkoittavat, että kehittäjien on kirjoitettava ja otettava käyttöön skriptejä Hivessä
• Missä Hiven sisäänrakennetut funktiot eivät toimi tietyissä verkkotunnusvaatimuksissa

Tätä varten Hive käyttää TRANSFORM-lauseketta sekä map- että reduktoriskriptien upottamiseen.

Näissä upotetuissa mukautetuissa komentosarjoissa meidän on otettava huomioon seuraavat seikat:

• Sarakkeet muunnetaan merkkijonoiksi ja erotetaan sarkaimella ennen kuin ne annetaan käyttäjälle skriptillä.
• Käyttäjän komentosarjan vakiotulostetta käsitellään sarkaimilla eroteltuina merkkijonoina.

Upotetun skriptin esimerkki:

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 seuraavaa. Tämä on vain esimerkkiskripti ymmärryksen helpottamiseksi.

• pv_users on käyttäjät-taulukko, jossa on kenttiä, kuten käyttäjätunnus ja päivämäärä, kuten map_script-tiedostossa mainitaan.
• Reducer-skripti määritellään pv_users-taulukon päivämäärän ja lukumäärän perusteella.

UKK