Hive Join & SubQuery-zelfstudie met voorbeelden

Inhoudsopgave:

Anonim

In deze tutorial leer je-

  • Doe mee met vragen
  • Verschillende soorten joins
  • Subvragen
  • Aangepaste scripts insluiten
  • UDF's (door de gebruiker gedefinieerde functies)

Deelnemen aan vragen:

Join-query's kunnen worden uitgevoerd op twee tabellen die aanwezig zijn in Hive. Om Join Concepts hier duidelijk te begrijpen, maken we hier twee tabellen,

  • Sample_joins (gerelateerd aan klantgegevens)
  • Sample_joins1 (gerelateerd aan orderdetails gedaan door werknemers)

Stap 1) Aanmaken van tabel "sample_joins" met kolomnamen ID, naam, leeftijd, adres en salaris van de werknemers

Stap 2) Gegevens laden en weergeven

Van de bovenstaande schermafbeelding

  1. Gegevens laden in sample_joins vanuit Customers.txt
  2. De inhoud van de tabel sample_joins weergeven

Stap 3) Aanmaken van de sample_joins1-tabel en laden, weergeven van gegevens

Uit de bovenstaande schermafbeelding kunnen we het volgende zien

  1. Aanmaken van tabel sample_joins1 met kolommen Orderid, Date1, Id, Amount
  2. Gegevens laden in sample_joins1 vanuit orders.txt
  3. Records weergeven die aanwezig zijn in sample_joins1

In de toekomst zullen we verschillende soorten joins zien die kunnen worden uitgevoerd op tabellen die we hebben gemaakt, maar daarvoor moet u rekening houden met de volgende punten voor joins.

Enkele aandachtspunten in Joins:

  • Alleen Equality-joins zijn toegestaan ​​in joins
  • In dezelfde query kunnen meer dan twee tabellen worden samengevoegd
  • LINKS, RECHTS, VOLLEDIGE BUITEN-joins bestaan ​​om meer controle te geven over de ON-clausule waarvoor er geen overeenkomst is
  • Joins zijn niet commutatief
  • Joins zijn links-associatief, ongeacht of het LINKS- of RECHTS-joins zijn

Verschillende soorten joins

Joins zijn van 4 soorten, dit zijn

  • Innerlijke verbinding
  • Linker buitenste Join
  • Rechts buitenste verbinding
  • Volledige Outer Join

Innerlijke verbinding:

De records die voor beide tabellen gemeenschappelijk zijn, worden opgehaald door deze Inner Join.

Uit de bovenstaande schermafbeelding kunnen we het volgende zien

  1. Hier voeren we een join-query uit met het trefwoord JOIN tussen de tabellen sample_joins en sample_joins1 met overeenkomende voorwaarde als (c.Id = o.Id).
  2. De uitvoer toont gemeenschappelijke records die in beide tabellen aanwezig zijn door de voorwaarde te controleren die in de query wordt vermeld

Vraag:

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

Linker buitenste verbinding:

  • Hive-querytaal LEFT OUTER JOIN retourneert alle rijen uit de linkertabel, ook al zijn er geen overeenkomsten in de rechtertabel
  • Als ON-clausule overeenkomt met nul records in de rechtertabel, retourneren de joins nog steeds een record in het resultaat met NULL in elke kolom van de rechtertabel

Uit de bovenstaande schermafbeelding kunnen we het volgende zien

  1. Hier voeren we een join-query uit met het trefwoord "LEFT OUTER JOIN" tussen de tabellen sample_joins en sample_joins1 met de overeenkomende voorwaarde als (c.Id = o.Id).

    Hier gebruiken we bijvoorbeeld de werknemer-id als referentie, het controleert of id zowel rechts als links in de tabel voorkomt of niet. Het fungeert als bijpassende conditie.

  2. De uitvoer toont gemeenschappelijke records die in beide tabellen aanwezig zijn door de voorwaarde te controleren die in de query wordt vermeld.

    NULL-waarden in de bovenstaande uitvoer zijn kolommen zonder waarden uit de rechtertabel die sample_joins1 is

Vraag:

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

Rechter buitenste Join:

  • Hive-querytaal RIGHT OUTER JOIN retourneert alle rijen uit de Right-tabel, ook al zijn er geen overeenkomsten in de linkertabel
  • Als ON-clausule overeenkomt met nul records in de linkertabel, retourneren de joins nog steeds een record in het resultaat met NULL in elke kolom van de linkertabel
  • RIGHT joins retourneren altijd records uit een Right-tabel en overeenkomende records uit de linkertabel. Als de linkertabel geen waarden heeft die overeenkomen met de kolom, retourneert deze NULL-waarden op die plaats.

Uit de bovenstaande schermafbeelding kunnen we het volgende zien

  1. Hier voeren we een join-query uit met het sleutelwoord "RIGHT OUTER JOIN" tussen de tabellen sample_joins en sample_joins1 met overeenkomende voorwaarde als (c.Id = o.Id).
  2. De uitvoer toont gemeenschappelijke records die in beide tabellen aanwezig zijn door de voorwaarde te controleren die in de query wordt vermeld

Vraag :

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

Volledige buitenste verbinding:

Het combineert records van zowel de tabellen sample_joins als sample_joins1 op basis van de JOIN-voorwaarde die in de query wordt gegeven.

Het retourneert alle records van beide tabellen en vult NULL-waarden in voor de kolommen met ontbrekende waarden die aan beide zijden overeenkomen.

Uit de bovenstaande schermafbeelding kunnen we het volgende zien:

  1. Hier voeren we een join-query uit met het sleutelwoord "FULL OUTER JOIN" tussen de tabellen sample_joins en sample_joins1 met overeenkomende voorwaarde als (c.Id = o.Id).
  2. De uitvoer toont alle records die in beide tabellen aanwezig zijn door de voorwaarde te controleren die in de query wordt vermeld. Null-waarden die hier worden uitgevoerd, geven de ontbrekende waarden aan in de kolommen van beide tabellen.

Vraag

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

Subvragen:

Een query die in een query aanwezig is, wordt een subquery genoemd. De hoofdquery is afhankelijk van de waarden die door de subquery's worden geretourneerd.

Subquery's kunnen in twee typen worden ingedeeld

  • Subquery's in FROM-component
  • Subquery's in WHERE-component

Wanneer te gebruiken:

  • Om een ​​bepaalde waarde te combineren uit twee kolomwaarden uit verschillende tabellen
  • Afhankelijkheid van de ene tabelwaarden van andere tabellen
  • Vergelijkende controle van één kolomwaarden uit andere tabellen

Syntaxis:

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

Voorbeeld:

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

Hier zijn t1 en t2 tabelnamen. De gekleurde is Subquery uitgevoerd op tabel t1. Hier zijn a en b kolommen die worden toegevoegd in een subquery en toegewezen aan col1. Col1 is de kolomwaarde die aanwezig is in de hoofdtabel. Deze kolom "col1" aanwezig in de subquery is gelijk aan de hoofdtabelquery in kolom col1.

Aangepaste scripts insluiten:

Hive biedt de mogelijkheid om gebruikersspecifieke scripts te schrijven voor de vereisten van de klant. De gebruikers kunnen hun eigen kaart schrijven en scripts verminderen voor de vereisten. Dit worden ingesloten aangepaste scripts genoemd. De coderingslogica wordt gedefinieerd in de aangepaste scripts en we kunnen dat script gebruiken in de ETL-tijd.

Wanneer moet u ingesloten scripts kiezen:

  • In klantspecifieke vereisten moeten ontwikkelaars scripts schrijven en implementeren in Hive
  • Waar de ingebouwde functies van Hive niet werken voor specifieke domeinvereisten

Hiervoor gebruikt het in Hive de TRANSFORM-clausule om zowel map- als reducer-scripts in te sluiten.

In deze ingesloten aangepaste scripts moeten we de volgende punten in acht nemen

  • Kolommen worden omgezet in tekenreeksen en worden gescheiden door TAB voordat ze aan het gebruikersscript worden gegeven
  • Standaarduitvoer van het gebruikersscript wordt behandeld als door TAB gescheiden tekenreekskolommen

Voorbeeld ingesloten script,

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;

Uit het bovenstaande script kunnen we het volgende zien

Dit is alleen het voorbeeldscript om het te begrijpen

  • pv_users is de gebruikerstabel met velden zoals userid en date zoals vermeld in map_script
  • Reducer-script gedefinieerd op datum en aantal van de pv_users-tabellen